数据库开发四:JDBC数据库开发进阶三(commons-dbutils原理)
程序员文章站
2022-05-28 11:35:25
...
自定义封装泛型工具类
package dbutils;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 封装泛型工具类
* Created by kevin on 2020/4/2.
*/
public class QR<T> {
private DataSource dataSource;
public QR() {
super();
}
public QR(DataSource dataSource) {
this.dataSource = dataSource;
}
/**
* 增删改
* @param sql
* @param parms
* @return
*/
public int update(String sql,Object...parms){
Connection connection = null;
PreparedStatement pstmt = null;
try {
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(sql);
initParams(pstmt,parms);
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
if(pstmt!=null){
pstmt.close();
}
if(connection!=null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private void initParams(PreparedStatement pstmt,Object...parms){
try {
for(int i=0;i<parms.length;i++){
pstmt.setObject(i+1,parms[i]);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 查询单个对象
* @param sql
* @param rh
* @param parms
* @return
*/
public T query(String sql,RsHandler<T> rh,Object...parms){
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(sql);
initParams(pstmt,parms);
rs = pstmt.executeQuery();
return rh.handle(rs);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
if(pstmt!=null){
pstmt.close();
}
if(rs!=null){
rs.close();
}
if(connection!=null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 处理对象映射
* @param <T>
*/
interface RsHandler<T>{
public T handle(ResultSet rs) throws SQLException;
}
自定义调用
package dbutils;
import org.junit.Test;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 仿commons-dbutils
* Created by kevin on 2020/4/2.
*/
public class Demo2 {
@Test
public void fun1(){
// Stu stu = new Stu();
// stu.setNumber("0002");
// stu.setName("王五");
// stu.setAge(30);
// stu.setGender("男");
// stu.setPassword("111111");
// add(stu);
Stu stu2 = load("0002");
System.out.println(stu2.toString());
}
public int add(Stu stu){
QR<Stu> stuQr = new QR<Stu>(JdbcUtils.getDataSource());
String sql="insert into stu values(?,?,?,?,?)";
Object[] params = {stu.getNumber(),stu.getName(),stu.getAge(),stu.getGender(),stu.getPassword()};
return stuQr.update(sql,params);
}
public Stu load(String number){
QR<Stu> stuQr = new QR<Stu>(JdbcUtils.getDataSource());
String sql="select * from stu where number=?";
Object[] params = {number};
RsHandler<Stu> rh = new RsHandler<Stu>() {
@Override
public Stu handle(ResultSet rs) throws SQLException{
if(!rs.next()) return null;
Stu stu = new Stu();
stu.setNumber(rs.getString("number"));
stu.setName(rs.getString("name"));
stu.setAge(rs.getInt("age"));
stu.setGender(rs.getString("gender"));
stu.setPassword(rs.getString("password"));
return stu;
}
};
return stuQr.query(sql,rh,params);
}
}
使用commons-dbutils
package dbutils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* commons dbutils
* Created by kevin on 2020/4/2.
*/
public class Demo3 {
@Test
public void fun1(){
QueryRunner qr= new QueryRunner(JdbcUtils.getDataSource());
String sql="insert into stu values(?,?,?,?,?)";
Object[] params = {"0003","李四",30,"男","111111"};
try {
qr.update(sql,params);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void fun2(){
QueryRunner qr= new QueryRunner(JdbcUtils.getDataSource());
String sql="select * from stu where number=?";
Object[] params = {"0003"};
try {
// ResultSetHandler<Stu> handler = new ResultSetHandler<Stu>() {
// @Override
// public Stu handle(ResultSet rs) throws SQLException {
// if(!rs.next()) return null;
// Stu stu = new Stu();
// stu.setNumber(rs.getString("number"));
// stu.setName(rs.getString("name"));
// stu.setAge(rs.getInt("age"));
// stu.setGender(rs.getString("gender"));
// stu.setPassword(rs.getString("password"));
// return stu;
// }
// };//该部分代码commons-dbutils已经有5个实现类了可以直接调用
//BeanHandler类是ResultSetHandler实现类,BeanHandler类使用需知(数据库列名必须与实体类名称相同)
BeanHandler<Stu> beanHandler = new BeanHandler<Stu>(Stu.class);
Stu stu = qr.query(sql,beanHandler,params);
System.out.println(stu.toString());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}