因为如今发现做个小项目都是导入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());
}
}
5012
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
