pom.xml文件引入
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
数据库链接工具类
import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;
/**
* 数据库连接池对象,单例 Created by yuandl on 2016-12-16.
*/
public class DBConnectionPool {
private static volatile DBConnectionPool dbConnection;
private ComboPooledDataSource cpds;
/**
* 在构造函数初始化的时候获取数据库连接
*/
private DBConnectionPool() {
try {
/** 通过属性文件获取数据库连接的参数值 **/
Properties properties = new Properties();
InputStream inStream = DBConnectionPool.class
.getResourceAsStream("/jdbc-mysql.properties");
properties.load(inStream);
/** 获取属性文件中的值 **/
String driverClassName = properties.getProperty("jdbc.driver");
String url = properties.getProperty("jdbc.url");
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
/** 数据库连接池对象 **/
cpds = new ComboPooledDataSource();
/** 设置数据库连接驱动 **/
cpds.setDriverClass(driverClassName);
/** 设置数据库连接地址 **/
cpds.setJdbcUrl(url);
/** 设置数据库连接用户名 **/
cpds.setUser(username);
/** 设置数据库连接密码 **/
cpds.setPassword(password);
/** 初始化时创建的连接数,应在minPoolSize与maxPoolSize之间取值.默认为3 **/
cpds.setInitialPoolSize(3);
/** 连接池中保留的最大连接数据.默认为15 **/
cpds.setMaxPoolSize(100);
/** 当连接池中的连接用完时,C3PO一次性创建新的连接数目; **/
cpds.setAcquireIncrement(5);
/** 隔多少秒检查所有连接池中的空闲连接,默认为0表示不检查; **/
cpds.setIdleConnectionTestPeriod(60);
/** 最大空闲时间,超过空闲时间的连接将被丢弃.为0或负数据则永不丢弃.默认为0; **/
cpds.setMaxIdleTime(3000);
/**
* 因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的
* 时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable
* 等方法来提升连接测试的性能。Default: false
**/
cpds.setTestConnectionOnCheckout(true);
/** 如果设为true那么在取得连接的同时将校验连接的有效性。Default: false **/
cpds.setTestConnectionOnCheckin(true);
/** 定义在从数据库获取新的连接失败后重复尝试获取的次数,默认为30; **/
cpds.setAcquireRetryAttempts(30);
/** 两次连接中间隔时间默认为1000毫秒 **/
cpds.setAcquireRetryDelay(1000);
/**
* 获取连接失败将会引起所有等待获取连接的线程异常,
* 但是数据源仍有效的保留,并在下次调用getConnection()的时候继续尝试获取连接.如果设为true,
* 那么尝试获取连接失败后该数据源将申明已经断开并永久关闭.默认为false
**/
cpds.setBreakAfterAcquireFailure(true);
} catch (IOException e) {
e.printStackTrace();
} catch (PropertyVetoException e) {
e.printStackTrace();
}
}
/**
* 获取数据库连接对象,单例
*
* @return
*/
public static DBConnectionPool getInstance() {
if (dbConnection == null) {
synchronized (DBConnectionPool.class) {
if (dbConnection == null) {
dbConnection = new DBConnectionPool();
}
}
}
return dbConnection;
}
/**
* 获取数据库连接
*
* @return 数据库连接
*/
public final synchronized Connection getConnection() throws SQLException {
return cpds.getConnection();
}
/**
* finalize()方法是在垃圾收集器删除对象之前对这个对象调用的。
*
* @throws Throwable
*/
protected void finalize() throws Throwable {
DataSources.destroy(cpds);
super.finalize();
}
}
操作数据库工具类
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;
/**
* 数据库JDBC连接工具类 Created by yuandl on 2016-12-16.
*/
public class DBUtil {
/**
* 执行数据库插入操作
*
* @param valueMap
* 插入数据表中key为列名和value为列对应的值的Map对象
* @param tableName
* 要插入的数据库的表名
* @return 影响的行数
* @throws SQLException
* SQL异常
*/
public static int insert(String tableName, Map<String, Object> valueMap) throws SQLException {
/** 获取数据库插入的Map的键值对的值 **/
Set<String> keySet = valueMap.keySet();
Iterator<String> iterator = keySet.iterator();
/** 要插入的字段sql,其实就是用key拼起来的 **/
StringBuilder columnSql = new StringBuilder();
/** 要插入的字段值,其实就是? **/
StringBuilder unknownMarkSql = new StringBuilder();
Object[] bindArgs = new Object[valueMap.size()];
int i = 0;
while (iterator.hasNext()) {
String key = iterator.next();
columnSql.append(i == 0 ? "" : ",");
columnSql.append(key);
unknownMarkSql.append(i == 0 ? "" : ",");
unknownMarkSql.append("?");
bindArgs[i] = valueMap.get(key);
i++;
}
/** 开始拼插入的sql语句 **/
StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO ");
sql.append(tableName);
sql.append(" (");
sql.append(columnSql);
sql.append(" ) VALUES (");
sql.append(unknownMarkSql);
sql.append(" )");
return executeUpdate(sql.toString(), bindArgs);
}
/**
* 执行数据库插入操作
*
* @param datas
* 插入数据表中key为列名和value为列对应的值的Map对象的List集合
* @param tableName
* 要插入的数据库的表名
* @return 影响的行数
* @throws SQLException
* SQL异常
*/
public static int insertAll(String tableName, List<Map<String, Object>> datas) throws SQLException {
/** 影响的行数 **/
int affectRowCount = -1;
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
/** 从数据库连接池中获取数据库连接 **/
connection = DBConnectionPool.getInstance().getConnection();
Map<String, Object> valueMap = datas.get(0);
/** 获取数据库插入的Map的键值对的值 **/
Set<String> keySet = valueMap.keySet();
Iterator<String> iterator = keySet.iterator();
/** 要插入的字段sql,其实就是用key拼起来的 **/
StringBuilder columnSql = new StringBuilder();
/** 要插入的字段值,其实就是? **/
StringBuilder unknownMarkSql = new StringBuilder();
Object[] keys = new Object[valueMap.size()];
int i = 0;
while (iterator.hasNext()) {
String key = iterator.next();
keys[i] = key;
columnSql.append(i == 0 ? "" : ",");
columnSql.append(key);
unknownMarkSql.append(i == 0 ? "" : ",");
unknownMarkSql.append("?");
i++;
}
/** 开始拼插入的sql语句 **/
StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO ");
sql.append(tableName);
sql.append(" (");
sql.append(columnSql);
sql.append(" ) VALUES (");
sql.append(unknownMarkSql);
sql.append(" )");
/** 执行SQL预编译 **/
preparedStatement = connection.prepareStatement(sql.toString());
/** 设置不自动提交,以便于在出现异常的时候数据库回滚 **/
connection.setAutoCommit(false);
// System.out.println(sql.toString());
for (int j = 0; j < datas.size(); j++) {
for (int k = 0; k < keys.length; k++) {
preparedStatement.setObject(k + 1, datas.get(j).get(keys[k]));
}
preparedStatement.addBatch();
}
int[] arr = preparedStatement.executeBatch();
connection.commit();
affectRowCount = arr.length;
// System.out.println("成功了插入了" + affectRowCount + "行");
// System.out.println();
} catch (Exception e) {
if (connection != null) {
connection.rollback();
}
e.printStackTrace();
throw e;
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
}
return affectRowCount;
}
/**
* 执行更新操作
*
* @param tableName
* 表名
* @param valueMap
* 要更改的值
* @param whereMap
* 条件
* @return 影响的行数
* @throws SQLException
* SQL异常
*/
public static int update(String tableName, Map<String, Object> valueMap, Map<String, Object> whereMap)
throws SQLException {
/** 获取数据库插入的Map的键值对的值 **/
Set<String> keySet = valueMap.keySet();
Iterator<String> iterator = keySet.iterator();
/** 开始拼插入的sql语句 **/
StringBuilder sql = new StringBuilder();
sql.append("UPDATE ");
sql.append(tableName);
sql.append(" SET ");
/** 要更改的的字段sql,其实就是用key拼起来的 **/
StringBuilder columnSql = new StringBuilder();
int i = 0;
List<Object> objects = new ArrayList<>();
while (iterator.hasNext()) {
String key = iterator.next();
columnSql.append(i == 0 ? "" : ",");
columnSql.append(key + " = ? ");
objects.add(valueMap.get(key));
i++;
}
sql.append(columnSql);
/** 更新的条件:要更改的的字段sql,其实就是用key拼起来的 **/
StringBuilder whereSql = new StringBuilder();
int j = 0;
if (whereMap != null && whereMap.size() > 0) {
whereSql.append(" WHERE ");
iterator = whereMap.keySet().iterator();
while (iterator.hasNext()) {
String key = iterator.next();
whereSql.append(j == 0 ? "" : " AND ");
whereSql.append(key + " = ? ");
objects.add(whereMap.get(key));
j++;
}
sql.append(whereSql);
}
return executeUpdate(sql.toString(), objects.toArray());
}
/**
* 执行删除操作
*
* @param tableName
* 要删除的表名
* @param whereMap
* 删除的条件
* @return 影响的行数
* @throws SQLException
* SQL执行异常
*/
public static int delete(String tableName, Map<String, Object> whereMap) throws SQLException {
/** 准备删除的sql语句 **/
StringBuilder sql = new StringBuilder();
sql.append("DELETE FROM ");
sql.append(tableName);
/** 更新的条件:要更改的的字段sql,其实就是用key拼起来的 **/
StringBuilder whereSql = new StringBuilder();
Object[] bindArgs = null;
if (whereMap != null && whereMap.size() > 0) {
bindArgs = new Object[whereMap.size()];
whereSql.append(" WHERE ");
/** 获取数据库插入的Map的键值对的值 **/
Set<String> keySet = whereMap.keySet();
Iterator<String> iterator = keySet.iterator();
int i = 0;
while (iterator.hasNext()) {
String key = iterator.next();
whereSql.append(i == 0 ? "" : " AND ");
whereSql.append(key + " = ? ");
bindArgs[i] = whereMap.get(key);
i++;
}
sql.append(whereSql);
}
return executeUpdate(sql.toString(), bindArgs);
}
/**
* 可以执行新增,修改,删除
*
* @param sql
* sql语句
* @param bindArgs
* 绑定参数
* @return 影响的行数
* @throws SQLException
* SQL异常
*/
public static int executeUpdate(String sql, Object[] bindArgs) throws SQLException {
/** 影响的行数 **/
int affectRowCount = -1;
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
/** 从数据库连接池中获取数据库连接 **/
connection = DBConnectionPool.getInstance().getConnection();
/** 执行SQL预编译 **/
// System.err.println(sql);
preparedStatement = connection.prepareStatement(sql.toString());
/** 设置不自动提交,以便于在出现异常的时候数据库回滚 **/
connection.setAutoCommit(false);
// System.out.println(getExecSQL(sql, bindArgs));
if (bindArgs != null) {
/** 绑定参数设置sql占位符中的值 **/
for (int i = 0; i < bindArgs.length; i++) {
preparedStatement.setObject(i + 1, bindArgs[i]);
}
}
/** 执行sql **/
affectRowCount = preparedStatement.executeUpdate();
connection.commit();
String operate;
if (sql.toUpperCase().indexOf("DELETE FROM") != -1) {
operate = "删除";
} else if (sql.toUpperCase().indexOf("INSERT INTO") != -1) {
operate = "新增";
} else {
operate = "修改";
}
// System.out.println("成功" + operate + "了" + affectRowCount + "行");
// System.out.println();
} catch (Exception e) {
if (connection != null) {
connection.rollback();
}
e.printStackTrace();
throw e;
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
}
return affectRowCount;
}
/**
* 通过sql查询数据, 慎用,会有sql注入问题
*
* @param sql
* @return 查询的数据集合
* @throws SQLException
*/
public static List<Map<String, Object>> query(String sql) throws SQLException {
return executeQuery(sql, null);
}
/**
* 执行sql通过 Map<String, Object>限定查询条件查询
*
* @param tableName
* 表名
* @param whereMap
* where条件
* @return List<Map<String, Object>>
* @throws SQLException
*/
public static List<Map<String, Object>> query(String tableName, Map<String, Object> whereMap) throws Exception {
String whereClause = "";
Object[] whereArgs = null;
if (whereMap != null && whereMap.size() > 0) {
Iterator<String> iterator = whereMap.keySet().iterator();
whereArgs = new Object[whereMap.size()];
int i = 0;
while (iterator.hasNext()) {
String key = iterator.next();
whereClause += (i == 0 ? "" : " AND ");
whereClause += (key + " = ? ");
whereArgs[i] = whereMap.get(key);
i++;
}
}
return query(tableName, false, null, whereClause, whereArgs, null, null, null, null);
}
/**
* 执行sql条件参数绑定形式的查询
*
* @param tableName
* 表名
* @param whereClause
* where条件的sql
* @param whereArgs
* where条件中占位符中的值
* @return List<Map<String, Object>>
* @throws SQLException
*/
public static List<Map<String, Object>> query(String tableName, String whereClause, String[] whereArgs)
throws SQLException {
return query(tableName, false, null, whereClause, whereArgs, null, null, null, null);
}
/**
* 执行全部结构的sql查询
*
* @param tableName
* 表名
* @param distinct
* 去重
* @param columns
* 要查询的列名
* @param selection
* where条件
* @param selectionArgs
* where条件中占位符中的值
* @param groupBy
* 分组
* @param having
* 筛选
* @param orderBy
* 排序
* @param limit
* 分页
* @return List<Map<String, Object>>
* @throws SQLException
*/
public static List<Map<String, Object>> query(String tableName, boolean distinct, String[] columns,
String selection, Object[] selectionArgs, String groupBy, String having, String orderBy, String limit)
throws SQLException {
String sql = buildQueryString(distinct, tableName, columns, selection, groupBy, having, orderBy, limit);
return executeQuery(sql, selectionArgs);
}
/**
* 执行查询
*
* @param sql
* 要执行的sql语句
* @param bindArgs
* 绑定的参数
* @return List<Map<String, Object>>结果集对象
* @throws SQLException
* SQL执行异常
*/
public static List<Map<String, Object>> executeQuery(String sql, Object[] bindArgs) throws SQLException {
List<Map<String, Object>> datas = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
/** 获取数据库连接池中的连接 **/
connection = DBConnectionPool.getInstance().getConnection();
preparedStatement = connection.prepareStatement(sql);
if (bindArgs != null) {
/** 设置sql占位符中的值 **/
for (int i = 0; i < bindArgs.length; i++) {
preparedStatement.setObject(i + 1, bindArgs[i]);
}
}
// System.out.println(getExecSQL(sql, bindArgs));
/** 执行sql语句,获取结果集 **/
resultSet = preparedStatement.executeQuery();
datas = getDatas(resultSet);
System.out.println();
} catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
if (resultSet != null) {
resultSet.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
}
return datas;
}
/**
* 将结果集对象封装成List<Map<String, Object>> 对象
*
* @param resultSet
* 结果多想
* @return 结果的封装
* @throws SQLException
*/
private static List<Map<String, Object>> getDatas(ResultSet resultSet) throws SQLException {
List<Map<String, Object>> datas = new ArrayList<>();
/** 获取结果集的数据结构对象 **/
ResultSetMetaData metaData = resultSet.getMetaData();
while (resultSet.next()) {
Map<String, Object> rowMap = new HashMap<>();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
rowMap.put(metaData.getColumnName(i), resultSet.getObject(i));
}
datas.add(rowMap);
}
// System.out.println("成功查询到了" + datas.size() + "行数据");
// for (int i = 0; i < datas.size(); i++) {
// Map<String, Object> map = datas.get(i);
// System.out.println("第" + (i + 1) + "行:" + map);
// }
return datas;
}
/**
* 条件分页
*
* @param distinct
* @param tables
* @param columns
* @param where
* @param groupBy
* @param having
* @param orderBy
* @param limit
* @return
*/
private static String buildQueryString(boolean distinct, String tables, String[] columns, String where,
String groupBy, String having, String orderBy, String limit) {
if (isEmpty(groupBy) && !isEmpty(having)) {
throw new IllegalArgumentException("HAVING clauses are only permitted when using a groupBy clause");
}
if (!isEmpty(limit) && !sLimitPattern.matcher(limit).matches()) {
throw new IllegalArgumentException("invalid LIMIT clauses:" + limit);
}
StringBuilder query = new StringBuilder(120);
query.append("SELECT ");
if (distinct) {
query.append("DISTINCT ");
}
if (columns != null && columns.length != 0) {
appendColumns(query, columns);
} else {
query.append(" * ");
}
query.append("FROM ");
query.append(tables);
appendClause(query, " WHERE ", where);
appendClause(query, " GROUP BY ", groupBy);
appendClause(query, " HAVING ", having);
appendClause(query, " ORDER BY ", orderBy);
appendClause(query, " LIMIT ", limit);
return query.toString();
}
/**
*
* @param s
* @param columns
*/
private static void appendColumns(StringBuilder s, String[] columns) {
int n = columns.length;
for (int i = 0; i < n; i++) {
String column = columns[i];
if (column != null) {
if (i > 0) {
s.append(", ");
}
s.append(column);
}
}
s.append(' ');
}
/**
*
* @param s
* @param name
* @param clause
*/
private static void appendClause(StringBuilder s, String name, String clause) {
if (!isEmpty(clause)) {
s.append(name);
s.append(clause);
}
}
/**
*
* @param str
* @return
*/
private static boolean isEmpty(CharSequence str) {
if (str == null || str.length() == 0)
return true;
else
return false;
}
/**
*
*/
private static final Pattern sLimitPattern = Pattern.compile("\\s*\\d+\\s*(,\\s*\\d+\\s*)?");
/**
*
* @param sql
* @param bindArgs
* @return
*/
private static String getExecSQL(String sql, Object[] bindArgs) {
StringBuilder sb = new StringBuilder(sql);
if (bindArgs != null && bindArgs.length > 0) {
int index = 0;
for (int i = 0; i < bindArgs.length; i++) {
index = sb.indexOf("?", index);
sb.replace(index, index + 1, String.valueOf(bindArgs[i]));
}
}
return sb.toString();
}
}
//使用方法
//一、insert语句
//1、创建一个map集合
Map<String, Object> valueMap = new HashMap<String, Object>();
//2、map的键是数据库的字段名(写的时候注意)
valueMap.put("name", "张三");
valueMap.put("pwd", "123456");
valueMap.put("old", "18");
//3、执行插入命令
DBUtil.insert(表名, valueMap);
//二、update语句
//1、创建两个map集合一个当做update的条件,一个是作为要修改的值
Map<String, Object> whereMap = new HashMap<String, Object>();
whereMap.put("name", 张三);//条件
Map<String, Object> valueMap = new HashMap<String, Object>();
valueMap.put("pwd", "111111");//要修改的字段
valueMap.put("old", "15");
//2、执行修改操作
DBUtil.update(表名, valueMap, whereMap);
//三、select语句
String name = "张三";
String sql1 = "select * from user where name=?";//占位符赋值操作
Object[] objects ={ name };
List<Map<String, Object>> result1 = DBUtil.executeQuery(sql1, objects);
//最后遍历集合取值就好
/**
*DBUtil的方法要自己捕捉异常,可以在catch里面添加日志,发生查询错误的时候可以快速定位问题,
*DBUtil还有许多方法,自己可以动手操作一下!
*/
最后说一下,jdbc读取的配置文件放在项目的根目录下即可!如果嫌麻烦也可以直接在jdbc连接池里面写死,不过不利于后期的维护。