简易高重用的jdbcutils工具封装实现类以及简易连接池实现
因为如今发现做个小项目都是导入n多的依赖包,非常烦琐,仅仅想快点开发完一个个的小需求项目,这个时候真心不想用框架,仅仅能自己写个jdbcutils,尽管网上有非常多有apache的,阿里的,可是感觉用过后都不怎么顺手,然后自己花了点时间写个新的,不喜勿喷
1.我们要写个resultset集合转成bean的回调接口,这个用过spring jdbc的人都知道这玩意
package org.simple.mvc.jdbc.bean;
import java.sql.ResultSet;
import java.sql.SQLException;
public interface RowMapper<T> {
public abstract T mapRow(ResultSet rs) throws SQLException;
}
2.先来个主要的jdbc操作接口吧,这样好规范点
package org.simple.mvc.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.simple.mvc.jdbc.bean.RowMapper;
public interface JdbcOperation {
/**
* update或delete功能
*
* @param sql
* @param params
* @return 变更记录数
* @throws SQLException
*/
public abstract int execute(String sql, Object[] params) throws SQLException;
/**
* update或delete功能
*
* @param sql
* @return 变更记录数
* @throws SQLException
*/
public abstract int execute(String sql) throws SQLException;
/**
* 批处理update或delete功能
*
* @param sql
* @param params
* @return 变更记录数
* @throws SQLException
*/
public abstract int executeBatch(String sql, List<Object[]> params) throws SQLException;
/**
* 批处理update或delete功能
*
* @param sql
* @param params
* @return 变更记录数
* @throws SQLException
*/
public abstract int executeBatch(String sql) throws SQLException;
/**
* select功能
*
* @param sql
* @param params
* @return 原生ResultSet数据集合
* @throws SQLException
*/
public abstract ResultSet queryForResultSet(String sql, Object[] params) throws SQLException;
/**
* select功能
*
* @param sql
* @return 原生ResultSet数据集合
* @throws SQLException
*/
public abstract ResultSet queryForResultSet(String sql) throws SQLException;
/**
* select功能
*
* @param sql
* @param params
* @return List<?>数据集合
* @throws SQLException
*/
public abstract List<?
> queryForBean(String sql, Object[] params, RowMapper<?> mapper) throws SQLException;
/**
* select功能
*
* @param sql
* @param params
* @return List<?
>数据集合
* @throws SQLException
*/
public abstract List<?> queryForBean(String sql, RowMapper<?> mapper) throws SQLException;
/**
* select功能
*
* @param sql
* @param params
* @return List<Map<String, Object>>数据集合
* @throws SQLException
*/
public abstract List<Map<String, Object>> queryForMap(String sql, Object[] params) throws SQLException;
/**
* select功能
*
* @param sql
* @param params
* @return List<Map<String, Object>>数据集合
* @throws SQLException
*/
public abstract List<Map<String, Object>> queryForMap(String sql) throws SQLException;
/**
* select功能
*
* @param sql
* @return 统计单列记录数
* @throws SQLException
*/
public abstract int queryForInt(String sql, Object[] params) throws SQLException;
/**
* select功能
*
* @param sql
* @return 统计单列记录数
* @throws SQLException
*/
public abstract int queryForInt(String sql) throws SQLException;
/**
* 释放Connection资源
*
* @param x
*/
public abstract void free(Connection x);
/**
* 释放Statement资源
*
* @param x
*/
public abstract void free(Statement x);
/**
* 释放PreparedStatement资源
*
* @param x
*/
public abstract void free(PreparedStatement x);
/**
* 释放ResultSet资源
*
* @param x
*/
public abstract void free(ResultSet x);
/**
* 设置数据源
*
* @param dataSource
*/
public abstract void setDataSource(DataSource dataSource);
/**
* 获取数据库链接
*
* @return Connection
*/
public abstract Connection getConnection();
/**
* 获取数据库链接
*
* @param autoCommit
* @return Connection
*/
public Connection getConnection(boolean autoCommit);
}
3.实现我们接口里面的方法
package org.simple.mvc.jdbc.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.simple.mvc.jdbc.JdbcOperation;
import org.simple.mvc.jdbc.bean.RowMapper;
/**
* 简易JDBC实现类
*
* @author shadow
*
*/
public class SimpleJdbc implements JdbcOperation {
private static final boolean AUTO_COMMIT = true;
private DataSource dataSource;
public SimpleJdbc() {
}
public SimpleJdbc(DataSource dataSource) {
this.dataSource = dataSource;
}
public Connection getConnection() {
return getConnection(AUTO_COMMIT);
}
public Connection getConnection(boolean autoCommit) {
try {
Connection conn = dataSource.getConnection();
if (!autoCommit)
conn.setAutoCommit(autoCommit);
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public int execute(String sql, Object[] params) throws SQLException {
Connection conn = getConnection(false);
PreparedStatement stmt = null;
int result = -1;
try {
stmt = createPreparedStatement(conn, sql, params);
result = stmt.executeUpdate();
conn.commit();
} catch (Exception e) {
conn.rollback();
e.printStackTrace();
} finally {
free(stmt);
free(conn);
}
return result;
}
@Override
public int execute(String sql) throws SQLException {
return execute(sql, new Object[] {});
}
@Override
public ResultSet queryForResultSet(String sql, Object[] params) throws SQLException {
Connection conn = getConnection();
PreparedStatement stmt = null;
try {
stmt = createPreparedStatement(conn, sql, params);
return stmt.executeQuery();
} catch (Exception e) {
e.printStackTrace();
} finally {
free(stmt);
free(conn);
}
return null;
}
@Override
public ResultSet queryForResultSet(String sql) throws SQLException {
return queryForResultSet(sql, new Object[] {});
}
@Override
public int queryForInt(String sql, Object[] params) throws SQLException {
Connection conn = getConnection();
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = createPreparedStatement(conn, sql, params);
rs = createResultSet(stmt);
while (rs.next()) {
return rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
free(rs);
free(stmt);
free(conn);
}
return 0;
}
@Override
public int queryForInt(String sql) throws SQLException {
return queryForInt(sql, new Object[] {});
}
@Override
public List<?> queryForBean(String sql, Object[] params, RowMapper<?> mapper) throws SQLException {
Connection conn = getConnection();
PreparedStatement stmt = null;
ResultSet rs = null;
List<Object> list = null;
try {
stmt = createPreparedStatement(conn, sql, params);
rs = createResultSet(stmt);
list = new ArrayList<Object>();
while (rs.next()) {
list.add(mapper.mapRow(rs));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
free(rs);
free(stmt);
free(conn);
}
return list;
}
@Override
public List<?> queryForBean(String sql, RowMapper<?> mapper) throws SQLException {
return queryForBean(sql, new Object[] {}, mapper);
}
@Override
public List<Map<String, Object>> queryForMap(String sql, Object[] params) throws SQLException {
Connection conn = getConnection();
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = createPreparedStatement(conn, sql, params);
rs = createResultSet(stmt);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
Map<String, Object> map = null;
ResultSetMetaData rsd = rs.getMetaData();
int columnCount = rsd.getColumnCount();
while (rs.next()) {
map = new HashMap<String, Object>(columnCount);
for (int i = 1; i < columnCount; i++) {
map.put(rsd.getColumnName(i), rs.getObject(i));
}
list.add(map);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
free(rs);
free(stmt);
free(conn);
}
return null;
}
@Override
public List<Map<String, Object>> queryForMap(String sql) throws SQLException {
return queryForMap(sql, new Object[] {});
}
@Override
public int executeBatch(String sql, List<Object[]> params) throws SQLException {
int result = 0;
Connection conn = getConnection(false);
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
for (int i = 0; i < params.size(); i++) {
Object[] param = params.get(i);
for (int j = 0; j < param.length; j++)
stmt.setObject(j + 1, param[j]);
stmt.addBatch();
if (i % 1000 == 0) {
stmt.executeBatch();
stmt.clearBatch();
}
}
stmt.executeBatch();
conn.commit();
result = params.size();
} catch (Exception e) {
conn.rollback();
e.printStackTrace();
} finally {
free(stmt);
free(conn);
}
return result;
}
@Override
public int executeBatch(String sql) throws SQLException {
return executeBatch(sql, new ArrayList<Object[]>());
}
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
@Override
public void free(Connection x) {
if (x != null)
try {
x.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void free(Statement x) {
if (x != null)
try {
x.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void free(PreparedStatement x) {
if (x != null)
try {
x.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void free(ResultSet x) {
if (x != null)
try {
x.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public PreparedStatement createPreparedStatement(Connection conn, String sql, Object[] params) throws SQLException {
PreparedStatement stmt = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++)
stmt.setObject(i + 1, params[i]);
return stmt;
}
public ResultSet createResultSet(PreparedStatement stmt) throws SQLException {
return stmt.executeQuery();
}
}
4.然后实现我们一个连接池吧,不喜欢dbcp,c3p0的话,那些要依赖包的导致项目重量上去了
package org.simple.mvc.jdbc.source;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
import javax.sql.DataSource;
/**
* 简易连接池实现类
*
* @author shadow
*
*/
public class SimpleDataSource implements DataSource {
private int poolSize = 5;
private LinkedList<Connection> pool = new LinkedList<Connection>();
public SimpleDataSource(String driver, String url, String name, String pwd) {
this(driver, url, name, pwd, 5);
}
public SimpleDataSource(String driver, String url) {
this(driver, url, "", "", 5);
}
public SimpleDataSource(String driver, String url, String name, String pwd, int poolSize) {
try {
Class.forName(driver);
this.poolSize = poolSize;
if (poolSize <= 0) {
throw new RuntimeException("初始化池大小失败: " + poolSize);
}
for (int i = 0; i < poolSize; i++) {
Connection con = DriverManager.getConnection(url, name, pwd);
con = ConnectionProxy.getProxy(con, pool);// 获取被代理的对象
pool.add(con);// 加入被代理的对象
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
}
}
/** 获取池大小 */
public int getPoolSize() {
return poolSize;
}
/** 不支持日志操作 */
public PrintWriter getLogWriter() throws SQLException {
throw new RuntimeException("Unsupport Operation.");
}
public void setLogWriter(PrintWriter out) throws SQLException {
throw new RuntimeException("Unsupport operation.");
}
/** 不支持超时操作 */
public void setLoginTimeout(int seconds) throws SQLException {
throw new RuntimeException("Unsupport operation.");
}
public int getLoginTimeout() throws SQLException {
return 0;
}
@SuppressWarnings("unchecked")
public <T> T unwrap(Class<T> iface) throws SQLException {
return (T) this;
}
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return DataSource.class.equals(iface);
}
/** 从池中取一个连接对象,使用了同步和线程调度 */
public Connection getConnection() throws SQLException {
synchronized (pool) {
if (pool.size() == 0) {
try {
pool.wait();
} catch (InterruptedException e) {
throw new RuntimeException(e.getMessage(), e);
}
return getConnection();
} else {
return pool.removeFirst();
}
}
}
public Connection getConnection(String username, String password) throws SQLException {
throw new RuntimeException("不支持接收username与password的操作");
}
/** 实现对Connection的动态代理 */
static class ConnectionProxy implements InvocationHandler {
private Object obj;
private LinkedList<Connection> pool;
private ConnectionProxy(Object obj, LinkedList<Connection> pool) {
this.obj = obj;
this.pool = pool;
}
public static Connection getProxy(Object o, LinkedList<Connection> pool) {
Object proxed = Proxy.newProxyInstance(o.getClass().getClassLoader(), new Class[] { Connection.class },
new ConnectionProxy(o, pool));
return (Connection) proxed;
}
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if (method.getName().equals("close")) {
synchronized (pool) {
pool.add((Connection) proxy);
pool.notify();
}
return null;
} else {
return method.invoke(obj, args);
}
}
}
}
5.開始測试下我们刚刚写的玩意,simplejdbc主要是有datasource提供给他就能够了,其它的一律无视,測试里面的user对象我就不提供,里面就是两个属性,id和username,然后生成set和get方法就可以,当然这个是演示resultset转成bean的,当然也能够直接使用queryForMap直接查询出map集合,我想接口提供的方法已经足够使用了
public static void main(String[] args) throws SQLException {
SimpleDataSource dataSource = new SimpleDataSource("org.sqlite.JDBC", "jdbc:sqlite:/E:p1010.db");
SimpleJdbc jdbc = new SimpleJdbc(dataSource);
List<User> list = (List<User>) jdbc.queryForBean("select * from t_user", new RowMapper<User>() {
User user = null;
@Override
public User mapRow(ResultSet rs) throws SQLException {
user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
return user;
}
});
for (User user : list) {
System.out.println(user.getId() + "---" + user.getUsername());
}
}
