自定义DbUtils通用类
2024-08-29 09:56:17
本实例使用C3P0连接池做连接，详见https://www.cnblogs.com/qf123/p/10097662.html开源连接池C3P0的使用
DBUtils.java
package com.qf.util; import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException; import com.mchange.v2.c3p0.ComboPooledDataSource; import java.sql.Statement; public class DBUtils {
static ComboPooledDataSource ds ;
static {
ds = new ComboPooledDataSource();//创建c3p0连接池数据源
} public static Connection getConn() {
Connection conn = null;
try {
conn = ds.getConnection();//从连接池获取数据库连接
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
} /*
* 释放资源
*/
public static void releaseResource(ResultSet rs,Statement statement,Connection conn) {
closeConnect(conn);
closeResultSet(rs);
closeStatement(statement);
}
public static void releaseResource(Statement statement,Connection conn) {
closeConnect(conn);
closeStatement(statement);
}
private static void closeResultSet(ResultSet rs) {
try {
if(rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closeStatement(Statement statement) {
try {
if(statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closeConnect(Connection conn) {
try {
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
ResultSetHandler接口，用于定义处理结果集的方法
package com.qf.util.dao;
import java.sql.ResultSet;
public interface ResultSetHandler<T> {
T handle(ResultSet rs);
}
自定义通用DbUtils类MyDbUtils.java
- query方法参数ResultSetHandler就是为了让用户根据实际情况自己定义结果集处理的方法
- 使用T泛型，灵活返回具体对象
package com.qf.util; import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List; import org.junit.Test; import com.qf.pojo.Person;
import com.qf.util.dao.ResultSetHandler; public class MyDbUtils {
@Test
public void test1() {
Person person = query("select * from person where id=?", new ResultSetHandler<Person>() { @Override
public Person handle(ResultSet rs) {
try {
if(rs.next()) {
String address = rs.getString("address");
Date time = rs.getDate("time");
int age = rs.getInt("age");
String name = rs.getString("name");
Person person = new Person(name , age, time, address);
return person;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}, 1);
System.out.println(person);
/**控制台输出结果：
* Person [name=smile, age=12, time=2018-03-06, address=null]
*/
} @Test
public void test2() {
List<Person> list = query("select * from person", new ResultSetHandler<List<Person>>() {
@Override
public List<Person> handle(ResultSet rs) {
try {
List<Person> list = new ArrayList<Person>();
while(rs.next()) {
String address = rs.getString("address");
Date time = rs.getDate("time");
int age = rs.getInt("age");
String name = rs.getString("name");
Person person = new Person(name , age, time, address);
list.add(person);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
});
for (Person person : list) {
System.out.println(person);
} /**控制台输出结果：
* Person [name=smile, age=12, time=2018-03-06, address=null]
* Person [name=wxf, age=13, time=2018-03-07, address=null]
* Person [name=smile, age=24, time=1970-01-01, address=null]
*/
} /**
* 查询
* @param sql
* @param handler 用于处理结果集rs
* @param args sql中？对应的参数值
* @return
*/
public <T> T query(String sql,ResultSetHandler<T> handler, Object ...args){
ResultSet rs = null;
PreparedStatement ps = null;
Connection conn = null;
try {
conn = DBUtils.getConn(); ps = conn.prepareStatement(sql);
ParameterMetaData metaData = ps.getParameterMetaData();
int count = metaData.getParameterCount();
for (int i = 0; i < count; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
T t = handler.handle(rs);
return t;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.releaseResource(ps, conn);
}
return null;
} /**
* 增删改
* @param sql
* @param args sql中？对应的参数值
*/
public void update(String sql,Object ...args) {
PreparedStatement ps = null;
Connection conn = null;
try {
conn = DBUtils.getConn();
ps = conn.prepareStatement(sql);
ParameterMetaData metaData = ps.getParameterMetaData();
int count = metaData.getParameterCount();
for (int i = 0; i < count; i++) {
ps.setObject(i+1, args[i]);
}
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.releaseResource(ps, conn);
}
} }
最新文章
- ztree.js的使用整理
- python_excel
- [SHELL实例] （转）最牛B的 Linux Shell 命令 （一）
- WinForm开发中针对TreeView控件改变当前选择节点的字体与颜色
- Go Slices: usage and internals
- xapian搜索系统存储结构解读
- Wish | IT桔子
- 给图片使用border-radius 图片会变成圆的。
- Robots协议
- Linux中的内核链表
- oracle11g安装教程（注意事项及图文教程）
- linux下查找堆栈信息例子
- unsigned char idata temp[8];
- django 问题总结
- beta阶段性能指标测试
- java 序列化和反序列化的实现原理
- Python基础 --函数的参数
- linux更换shell外壳zsh
- hdu 3415 Max Sum of Max-K-sub-sequence 单调队列。
- 点击input选中文本