前言
工作中,需要使用java访问数据库,操作一些数据;使用orm框架就显得笨重。所以,对jdbc的基本使用方法进行封装,直接复制代码就可以使用,代码中使用mysql数据和dbcp连接池,默认连接数为8。
使用说明
1.使用maven,引入jar
<!-- dbcp连接池 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.8.0</version>
</dependency>
<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
2.配置并创建数据源
jdbcConfig.properties文件(放在根目录)
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/jiangz?useUnicode=true&characterEncoding=UTF-8
username=root
password=root
JdbcConfig.java代码
/**
* jdbc工具类
*
* @author yangfan
*
*/
public final class JdbcConfig {
private static Logger log = LoggerFactory.getLogger(JdbcConfig.class);
private static DataSource dataSource;
/**
* 数据源创建成功
*/
static {
try {
InputStream in = JdbcConfig.class.getResourceAsStream("/jdbcConfig.properties");
Properties p = new Properties();
p.load(in);
dataSource = BasicDataSourceFactory.createDataSource(p);
log.info("dataSource 创建成功!");
} catch (Exception e) {
log.error("dataSource 创建失败!");
e.printStackTrace();
}
}
/**
* 建立数据库连接
*
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
Connection conn = null;
conn = dataSource.getConnection();
return conn;
}
/**
* 释放连接
* @param conn
*/
private static void freeConnection(Connection conn) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 释放statement
* @param statement
*/
private static void freeStatement(Statement statement) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 释放resultset
* @param rs
*/
private static void freeResultSet(ResultSet rs) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 释放资源
*
* @param conn
* @param statement
* @param rs
*/
public static void free(Connection conn, Statement statement, ResultSet rs) {
if (rs != null) {
freeResultSet(rs);
}
if (statement != null) {
freeStatement(statement);
}
if (conn != null) {
freeConnection(conn);
}
}
}
3.CURD方法封装
JdbcHelper.java
/**
* 数据库访问帮助类
*
* @author yangfan
*
*/
public class JdbcHelper {
private static Connection conn = null;
private static PreparedStatement preparedStatement = null;
private static CallableStatement callableStatement = null;
/**
* 单个对象查询
* @param sql
* @return
* @throws SQLException
*/
public static Map<String,Object> getOne(String sql) throws SQLException {
ResultSet rs = null;
try {
getPreparedStatement(sql);
rs = preparedStatement.executeQuery();
return getResultMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 获取集合数据(多个对象)
* @param sql
* @return
* @throws SQLException
*/
public static List<Map<String,Object>> getList(String sql) throws SQLException {
ResultSet rs = null;
try {
getPreparedStatement(sql);
rs = preparedStatement.executeQuery();
return convertList(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 获取集合数据(多个对象)
* @param sql
* @return
* @throws SQLException
*/
public static List<Map<String,Object>> getList(String sql, Object... paramters)
throws SQLException {
ResultSet rs = null;
try {
getPreparedStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
rs = preparedStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 单个值获取
* 如 select id from test where id =1
* @param sql
* @return
* @throws SQLException
*/
public static Object getSingle(String sql) throws SQLException {
Object result = null;
ResultSet rs = null;
try {
getPreparedStatement(sql);
rs = preparedStatement.executeQuery();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 单个值获取
* 如 select id from test where id =1
* @param sql
* @return
* @throws SQLException
*/
public static Object getSingle(String sql, Object... paramters)
throws SQLException {
Object result = null;
ResultSet rs = null;
try {
getPreparedStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
rs = preparedStatement.executeQuery();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 用于增删改
*
* @param sql
* sql语句
* @return 影响行数
* @throws SQLException
*/
public static int update(String sql) throws SQLException {
try {
getPreparedStatement(sql);
return preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
}
}
/**
* 用于增删改(带参数)
*
* @param sql
* sql语句
* @param paramters
* sql语句
* @return 影响行数
* @throws SQLException
*/
public static int update(String sql, Object... paramters)
throws SQLException {
try {
getPreparedStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
return preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
}
}
/**
* 插入值后返回主键值
*
* @param sql
* 插入sql语句
* @return 返回结果
* @throws Exception
*/
public static Object insertWithReturnPrimeKey(String sql)
throws SQLException {
ResultSet rs = null;
Object result = null;
try {
conn = JdbcConfig.getConnection();
preparedStatement = conn.prepareStatement(sql,
PreparedStatement.RETURN_GENERATED_KEYS);
preparedStatement.execute();
rs = preparedStatement.getGeneratedKeys();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
}finally {
free(rs);
}
}
/**
* 插入值后返回主键值
*
* @param sql
* 插入sql语句
* @param paramters
* 参数列表
* @return 返回结果
* @throws SQLException
*/
public static Object insertWithReturnPrimeKey(String sql,
Object... paramters) throws SQLException {
ResultSet rs = null;
Object result = null;
try {
conn = JdbcConfig.getConnection();
preparedStatement = conn.prepareStatement(sql,
PreparedStatement.RETURN_GENERATED_KEYS);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
preparedStatement.execute();
rs = preparedStatement.getGeneratedKeys();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
}
}
/**
* 调用存储过程执行查询
*
* @param procedureSql
* 存储过程
* @return
* @throws SQLException
*/
public static List callableQuery(String procedureSql) throws SQLException {
ResultSet rs = null;
try {
getCallableStatement(procedureSql);
rs = callableStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 调用存储过程(带参数),执行查询
*
* @param procedureSql
* 存储过程
* @param paramters
* 参数表
* @return
* @throws SQLException
*/
public static List callableQuery(String procedureSql, Object... paramters)
throws SQLException {
ResultSet rs = null;
try {
getCallableStatement(procedureSql);
for (int i = 0; i < paramters.length; i++) {
callableStatement.setObject(i + 1, paramters[i]);
}
rs = callableStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 调用存储过程,查询单个值
*
* @param procedureSql
* @return
* @throws SQLException
*/
public static Object callableGetSingle(String procedureSql)
throws SQLException {
Object result = null;
ResultSet rs = null;
try {
getCallableStatement(procedureSql);
rs = callableStatement.executeQuery();
while (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 调用存储过程(带参数),查询单个值
*
* @param procedureSql
* @return
* @throws SQLException
*/
public static Object callableGetSingle(String procedureSql,
Object... paramters) throws SQLException {
Object result = null;
ResultSet rs = null;
try {
getCallableStatement(procedureSql);
for (int i = 0; i < paramters.length; i++) {
callableStatement.setObject(i + 1, paramters[i]);
}
rs = callableStatement.executeQuery();
while (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
public static Object callableWithParamters(String procedureSql)
throws SQLException {
try {
getCallableStatement(procedureSql);
callableStatement.registerOutParameter(0, Types.OTHER);
callableStatement.execute();
return callableStatement.getObject(0);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
}
}
/**
* 调用存储过程,执行增删改
*
* @param procedureSql
* 存储过程
* @return 影响行数
* @throws SQLException
*/
public static int callableUpdate(String procedureSql) throws SQLException {
try {
getCallableStatement(procedureSql);
return callableStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
}
}
/**
* 调用存储过程(带参数),执行增删改
*
* @param procedureSql
* 存储过程
* @param parameters
* @return 影响行数
* @throws SQLException
*/
public static int callableUpdate(String procedureSql, Object... parameters)
throws SQLException {
try {
getCallableStatement(procedureSql);
for (int i = 0; i < parameters.length; i++) {
callableStatement.setObject(i + 1, parameters[i]);
}
return callableStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
}
}
/**
* 批量更新数据
*
* @param sqlList
* 一组sql
* @return
*/
public static int[] batchUpdate(List<String> sqlList) {
int[] result = new int[] {};
Statement statenent = null;
try {
conn = JdbcConfig.getConnection();
conn.setAutoCommit(false);
statenent = conn.createStatement();
int len = sqlList.size();
for (int i = 0; i < len; i++) {
statenent.addBatch(sqlList.get(i));
if((i != 0 && i % 500 == 0) || i == len-1){//可以设置不同的大小;如50,100,200,500,1000等等
statenent.executeBatch();
//优化插入第三步 提交,批量插入数据库中。
conn.commit();
statenent.clearBatch(); //提交后,Batch清空。
}
}
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
throw new ExceptionInInitializerError(e1);
}
throw new ExceptionInInitializerError(e);
} finally {
free(statenent, null);
}
return result;
}
private static List ResultToListMap(ResultSet rs) throws SQLException {
List list = new ArrayList();
while (rs.next()) {
ResultSetMetaData md = rs.getMetaData();
Map map = new HashMap();
for (int i = 1; i < md.getColumnCount(); i++) {
map.put(md.getColumnLabel(i), rs.getObject(i));
}
list.add(map);
}
return list;
}
/**
* 查询结果转Map
* @param rs
* @return
* @throws SQLException
*/
private static Map<String, Object> getResultMap(ResultSet rs)
throws SQLException {
Map<String, Object> hm = new HashMap<String, Object>();
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();// 获取列的数量
for (int i = 1; i <= count; i++) {
String key = rsmd.getColumnLabel(i);
String value = rs.getString(i);
hm.put(key, value);
}
return hm;
}
/**
* 将查询结果resultset转换为List<Map>
* @param rs
* @return
* @throws SQLException
*/
private static List<Map<String,Object>> convertList(ResultSet rs) throws SQLException{
List<Map<String,Object>> list = new ArrayList<>();
ResultSetMetaData md = rs.getMetaData();//获取键名
int columnCount = md.getColumnCount();//获取列的数量
while (rs.next()) {
Map<String,Object> rowData = new HashMap<>();//声明Map
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), rs.getObject(i));//获取键名及值
}
list.add(rowData);
}
return list;
}
/**
* 获取PreparedStatement
*
* @param sql
* @throws SQLException
*/
private static void getPreparedStatement(String sql) throws SQLException {
conn = JdbcConfig.getConnection();
preparedStatement = conn.prepareStatement(sql);
}
/**
* 获取CallableStatement
*
* @param procedureSql
* @throws SQLException
*/
private static void getCallableStatement(String procedureSql)
throws SQLException {
conn = JdbcConfig.getConnection();
callableStatement = conn.prepareCall(procedureSql);
}
/**
* 释放资源
*
* @param rs
* 结果集
*/
public static void free(ResultSet rs) {
JdbcConfig.free(conn, preparedStatement, rs);
}
/**
* 释放资源
*
* @param statement
* @param rs
*/
public static void free(Statement statement, ResultSet rs) {
JdbcConfig.free(conn, statement, rs);
}
/**
* 释放资源
*/
public static void free() {
free(null);
}
}