jdbc中对mysql数据库操作的简单封装--（仅做备忘记录）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了jdbc中对mysql数据库操作的简单封装--（仅做备忘记录），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含12771字，纯文字阅读大概需要19分钟。
内容图文
本次使用jdbc中的mysql-connector-java-5.1.47-bin.jar的连接包，下载这个jar包放在javaee项目的WEB-INF/lib目录下，再把它作为外包jar包进入到libraries中，这样就可以使用mysql的jdbc接口了。
自己封装的代码中引入了两个自己字义的Exception:
SqlSecureException.java
package com.myproweb.exception;
public class SqlSecureException extends Exception {
/**
*
*/
private static final long serialVersionUID = -185202535331616389L;
}
SqlErrorException.java
package com.myproweb.exception;
public class SqlSecureException extends Exception {
/**
*
*/
private static final long serialVersionUID = -185202535331616389L;
}
最后封装代码如下：
package com.myproweb.utils;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import com.myproweb.exception.SqlErrorException;
import com.myproweb.exception.SqlSecureException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
public class MysqlDatabaseConnection {
private static String mysql_username = "root";
private static String mysql_password = "";
private static String mysql_connection_url = "jdbc:mysql://localhost:3306/javadb";
private static Connection mysql_connection;
private static Boolean check_fileds_safety = true;
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private static Boolean validateValueString(String value_string) {
value_string = value_string.toLowerCase();
String validateString="‘|or|and|;|-|--|+|,|like|//|/|*|%|#";
String[] validateStrings = validateString.split("\\|");
for (int i = 0; i < validateStrings.length; i++) {
if (value_string.indexOf(validateStrings[i]) >= 0) {
return false;
}
}
return true;
}
private static String getSqlValue(Object obj) throws SqlSecureException {
String sql_value_string = null;
if (obj instanceof String) {
if(check_fileds_safety){
if(validateValueString(obj.toString().trim())) {
sql_value_string = "‘" + obj.toString().trim() + "‘";
}else {
throw new SqlSecureException();
}
}else {
sql_value_string = "‘" + obj.toString().trim() + "‘";
}
}
if (obj instanceof Integer) {
sql_value_string = ((Integer) obj).toString();
}
if (obj instanceof Long) {
sql_value_string = ((Long) obj).toString();
}
if (obj instanceof Float) {
sql_value_string = ((Float) obj).toString();
}
if (obj instanceof Double) {
sql_value_string = ((Double) obj).toString();
}
return sql_value_string;
}
private static String getWheresSqlString(Map<String, Object[]> wheres) throws SqlErrorException, SqlSecureException {
String where_string = "";
for (String key : wheres.keySet()) {
Object[] condition = wheres.get(key);
String value_string = getSqlValue(condition[0]);
if (condition.length == 1) {
if (null != value_string) {
if ("".equals(where_string)) {
where_string += " " + key.trim() + "=" + value_string + " ";
} else {
where_string += "and " + key.trim() + "=" + value_string + " ";
}
}
}
if (condition.length == 2) {
if (!(condition[1] instanceof String)) {
throw new SqlErrorException("key words error[0001]!");
}
String judgement_condition = condition[1].toString().trim();
if ("=".equals(judgement_condition) || "<=".equals(judgement_condition)
|| ">=".equals(judgement_condition) || "!=".equals(judgement_condition)
|| "<>".equals(judgement_condition)) {
if (null != value_string) {
if ("".equals(where_string)) {
where_string += " " + key.trim() + judgement_condition + value_string + " ";
} else {
where_string += "and " + key.trim() + judgement_condition + value_string + " ";
}
}
} else {
throw new SqlErrorException("key words error[0002]!");
}
}
if (condition.length == 3) {
if (!(condition[2] instanceof String) || !(condition[1] instanceof String)) {
throw new SqlErrorException("key words error[0001]!");
}
String and_or_string = condition[2].toString().toUpperCase();
String judgement_condition = condition[1].toString().trim();
if (("AND".equals(and_or_string) || "OR".equals(and_or_string)) && ("=".equals(judgement_condition)
|| "<=".equals(judgement_condition) || ">=".equals(judgement_condition)
|| "!=".equals(judgement_condition) || "<>".equals(judgement_condition))) {
if (!"".equals(where_string)) {
where_string += and_or_string + ‘ ‘;
}
if (null != value_string) {
where_string += key.trim() + judgement_condition + value_string + ‘ ‘;
}
} else {
throw new SqlErrorException("key words error[0002]!");
}
}
}
if ("".equals(where_string)) {
throw new SqlErrorException("key words error[0003]!");
}
return where_string;
}
/**
* 是否设置对字符值进行安全检查
* @param is_fields_safety true:进行安全检查 false:不进行安全检查
*/
public static void setFieldsSecure(Boolean is_fields_safety) {
check_fileds_safety=is_fields_safety;
}
// 连接数据库边接
public static Connection getConnection() throws SQLException {
if (null != mysql_connection) {
close(mysql_connection);
}
mysql_connection = (Connection) DriverManager.getConnection(mysql_connection_url, mysql_username,
mysql_password);
return mysql_connection;
}
/**
* 通用的删除、更新、删除函数
* @param sql_string delete or update or insert sql语句
* @return sql操作影响的行数
* @throws SQLException
*/
public static int commonDeleteOrUpdateOrInsert(String sql_string) throws SQLException {
Connection connection = getConnection();
Statement statement = (Statement) connection.createStatement();
int result = statement.executeUpdate(sql_string);
close(connection);
close(statement);
return result;
}
/**
* 通用的查询函数
* @param sql_string select sql语句
* @return 把查询的结果集放到一个二唯数组中
* @throws SQLException
*/
public static ArrayList<ArrayList<Object>> commonQuery(String sql_string) throws SQLException {
Connection connection = getConnection();
Statement statement = (Statement) connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql_string);
ArrayList<ArrayList<Object>> arraylist = new ArrayList<ArrayList<Object>>();
int column_count =resultSet.getMetaData().getColumnCount();
while(resultSet.next()) {
ArrayList<Object> tmp_arraylist = new ArrayList<Object>();
for(int i=1 ;i<=column_count ; i++) {
tmp_arraylist.add(resultSet.getObject(i));
}
arraylist.add(tmp_arraylist);
}
close(resultSet);
close(statement);
close(connection);
return arraylist;
}
/**
* 实现一个简单sql插入的封装
*
* @param table_name 表名
* @param values 要插入的值
* @return 返回Statement执行的结果
* @throws SQLException
* @throws SqlSecureException
*/
public static int simplyInsert(String table_name, Map<String, Object> values) throws SQLException, SqlSecureException {
String sql_string = "insert into " + table_name;
String fields_string = "";
String values_string = "";
for (String key : values.keySet()) {
fields_string += key.trim() + ‘,‘;
Object obj = values.get(key);
String t_value_string = getSqlValue(obj);
if (null != t_value_string) {
values_string += t_value_string + ",";
}
}
fields_string = fields_string.substring(0, fields_string.length() - 1);
values_string = values_string.substring(0, values_string.length() - 1);
sql_string = sql_string + "(" + fields_string + ") value(" + values_string + ")";
return commonDeleteOrUpdateOrInsert(sql_string);
}
/**
* 实现一个简单sql更新封装
* @param table_name 表名
* @param values 更新的值
* @param wheres 更新的条件
* @return 返回受影响的行数
* @throws SqlErrorException
* @throws SQLException
* @throws SqlSecureException
*/
public static int simplyUpdate(String table_name, Map<String, Object> values, Map<String, Object[]> wheres) throws SqlErrorException, SQLException, SqlSecureException {
String sql_string="update "+table_name+" set ";
String update_string ="";
for(String key:values.keySet()) {
update_string+=key+"="+getSqlValue(values.get(key))+",";
}
if("".equals(update_string)) {
throw new SqlErrorException("update string is error!");
}
update_string = update_string.substring(0, update_string.length() - 1);
String where_string = getWheresSqlString(wheres);
sql_string+=update_string+" where "+where_string;
return commonDeleteOrUpdateOrInsert(sql_string);
}
/**
* 实现一个简单sql查询一条结果封装
* @param table_name 表名
* @param fields 字符集
* @param wheres 条件
* @return 返回一行结果
* @throws SqlErrorException
* @throws SQLException
* @throws SqlSecureException
*/
public static Map<String,Object> simplyQueryRow(String table_name, String[] fields, Map<String, Object[]> wheres)
throws SqlErrorException, SQLException, SqlSecureException {
String sql_string = "select ";
String fields_string = "";
if (fields.length == 0) {
throw new SqlErrorException("fileds do not allowed empty!");
}
for (String field : fields) {
fields_string += field + ",";
}
fields_string = fields_string.substring(0, fields_string.length() - 1);
sql_string += fields_string + " from " + table_name + " where ";
String where_string = getWheresSqlString(wheres);
sql_string += where_string+" limit 1";
ArrayList<ArrayList<Object>> resultSet = commonQuery(sql_string);
Map<String,Object> result = new HashMap<String,Object>();
ArrayList<Object> filst_row = resultSet.get(0);
if(null != filst_row) {
int column_size =filst_row.size();
if(column_size==fields.length) {
for(int i=0;i < fields.length ; i++) {
result.put(fields[i], filst_row.get(i));
}
}else {
throw new SqlErrorException("fileds and result is error!");
}
}
return result;
}
/***
* 一个简单的删除封装
* @param table_name 表名
* @param wheres 条件集
* @return 返回受影响的行
* @throws SqlErrorException
* @throws SQLException
* @throws SqlSecureException
*/
public static int simplyDelete(String table_name, Map<String, Object[]> wheres)
throws SqlErrorException, SQLException, SqlSecureException {
String sql_string = "delete from " + table_name + " where ";
String where_string = getWheresSqlString(wheres);
sql_string += where_string;
return commonDeleteOrUpdateOrInsert(sql_string);
}
// 关闭 结果集 sql预声明 连接
public static void close(Object obj) throws SQLException {
if (null == obj) {
return;
}
if (obj instanceof ResultSet) {
((ResultSet) obj).close();
}
if (obj instanceof Statement) {
((Statement) obj).close();
}
if (obj instanceof Connection) {
((Connection) obj).close();
}
obj = null;
}
}
简单使用实例如下
比如操作表：
CREATE TABLE `customer` (
`id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL COMMENT ‘用户名‘,
`password` varchar(20) NOT NULL COMMENT ‘密码‘,
`gender` int(1) NOT NULL DEFAULT ‘0‘ COMMENT ‘性别‘,
PRIMARY KEY (`id`),
UNIQUE KEY `customer_id` (`id`) USING BTREE,
KEY `customer_username` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8 COMMENT=‘用户表‘;MysqlDatabaseConnection.simplyInsert 方法示例：
HashMap<String,Object> customer = new HashMap<String,Object>();
customer.put("username", "admin");
customer.put("password", "123123123");
customer.put("gender", new Integer(1));
try {
int result = MysqlDatabaseConnection.simplyInsert("customer",customer );
System.out.println(result);
} catch (SQLException e) {
e.printStackTrace();
} catch (SqlSecureException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}MysqlDatabaseConnection.simplyDelete 方法示例：
Map<String,Object []> wheres = new HashMap<String,Object []>();
wheres.put("id", new Object[] {new Integer(6),"<=","and"});
wheres.put("username", new Object[] {"jack","=","or"});
try {
int result = MysqlDatabaseConnection.simplyDelete("customer", wheres);
System.out.println(result);
} catch (SqlErrorException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (SqlSecureException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}MysqlDatabaseConnection.simplyQueryRow 方法示例：
Map<String,Object []> wheres = new HashMap<String,Object []>();
wheres.put("id", new Object[] {new Integer(37),"=","and"});
try {
Map<String,Object>rowResult = MysqlDatabaseConnection.simplyQueryRow("customer", new String[] {"id","username","password","gender"}, wheres);
for(String key:rowResult.keySet()) {
System.out.println(key+":"+rowResult.get(key));
}
} catch (SqlErrorException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SqlSecureException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}MysqlDatabaseConnection.simplyUpdate 方法示例：
Map<String,Object> values = new HashMap<String,Object>();
Map<String,Object[]> wheres = new HashMap<String,Object[]>();
values.put("username", "admin1");
values.put("password", "nihao");
wheres.put("id", new Object[] {new Integer(31)});
try {
int result = MysqlDatabaseConnection.simplyUpdate("customer", values, wheres);
System.out.println(result);
} catch (SqlErrorException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (SqlSecureException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}以上内容仅做个人备忘用
jdbc中对mysql数据库操作的简单封装--（仅做备忘记录）
标签：数据库 lock double unsigned erro insert auto host ret
本文系统来源：http://blog.51cto.com/quietnight/2300328
内容总结
以上是互联网集市为您收集整理的jdbc中对mysql数据库操作的简单封装--（仅做备忘记录）全部内容，希望文章能够帮你解决jdbc中对mysql数据库操作的简单封装--（仅做备忘记录）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】