使用common-dbutils进行dao操作
jar:
先引出database工具类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package cn.itcast.utils; public class Stu { private int id; private String sname; private int age; private String gender; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getSname() { return sname; } public void setSname(String sname) { this.sname = sname; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public Stu() { super(); } public Stu(int id, String sname, int age, String gender) { super(); this.id = id; this.sname = sname; this.age = age; this.gender = gender; } @Override public String toString() { return "Stu [id=" + id + ", sname=" + sname + ", age=" + age + ", gender=" + gender + "]"; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package cn.itcast.jdbc; import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class JdbcUtils { /* * 配置文件的恶魔人配置!要求你必须给出c3p0-config。xnl! */ private static ComboPooledDataSource dataSource=new ComboPooledDataSource(); /** * 使用连接池返回一个连接对象 * @return * @throws SQLException */ public static Connection getConnection() throws SQLException{ return dataSource.getConnection(); } /** * 返回连接池对象 * @return */ public static DataSource getDataSource(){ return dataSource; } }
常规操作:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package cn.itcast.utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.junit.Test; import cn.itcast.jdbc.JdbcUtils; public class Demo1 { public int addStu(Stu stu){ Connection con=null; PreparedStatement pstmt=null; try { con=JdbcUtils.getConnection(); String sql="INSERT INTO t_stu VALUES (?,?,?,?)"; pstmt=con.prepareStatement(sql); pstmt.setInt(1, stu.getId()); pstmt.setString(2, stu.getSname()); pstmt.setInt(3, stu.getAge()); pstmt.setString(4, stu.getGender()); return pstmt.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); }finally{ try{ if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); }catch(SQLException e){ e.printStackTrace(); } } } public int updateStu(Stu stu){ Connection con=null; PreparedStatement pstmt=null; try { con=JdbcUtils.getConnection(); String sql="UPDATE t_stu SET sname = ? , age = ? , gender = ? WHERE id=?"; pstmt=con.prepareStatement(sql); pstmt.setInt(4, stu.getId()); pstmt.setString(1, stu.getSname()); pstmt.setInt(2, stu.getAge()); pstmt.setString(3, stu.getGender()); return pstmt.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); }finally{ try{ if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); }catch(SQLException e){ e.printStackTrace(); } } } public int deleteStu(int sid){ Connection con=null; PreparedStatement pstmt=null; try { con=JdbcUtils.getConnection(); String sql="DELETE FROM t_stu WHERE id=?"; pstmt=con.prepareStatement(sql); pstmt.setInt(1, sid); return pstmt.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); }finally{ try{ if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); }catch(SQLException e){ e.printStackTrace(); } } } /** * 我们可以发现增删改具有共性,不同点: * sql语句 * 参数列表 */ public Stu load(int id){ Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; try { con=JdbcUtils.getConnection(); String sql="SELECT * FROM t_stu WHERE id=?"; pstmt=con.prepareStatement(sql); pstmt.setInt(1, id); rs=pstmt.executeQuery(); Stu stu=new Stu(); if(!rs.next()) return null; stu.setId(rs.getInt(1)); stu.setSname(rs.getString(2)); stu.setAge(rs.getInt(3)); stu.setGender(rs.getString(4)); return stu; } catch (SQLException e) { throw new RuntimeException(e); }finally{ try{ if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); }catch(SQLException e){ e.printStackTrace(); } } } /* * 查询的不同点: * sql语句 * 参数列表 * 结果类型 * 可以将查询多条数据和单条数据都当成集合处理 */ @Test public void fun1(){ // Stu stu=new Stu(1001,"zs",21,"man"); // addStu(stu); // Stu stu=new Stu(1001,"zs",22,"man"); // updateStu(stu); // deleteStu(1001); System.out.println(load(1001)); } }
自定义工具类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package cn.itcast.utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.sql.DataSource; public class QR<T> { private DataSource dataSource; public QR(DataSource dataSource){ this.dataSource=dataSource; } public QR(){ super(); } public int update(String sql,Object...params){ Connection conn=null; PreparedStatement pstmt=null; try { conn=dataSource.getConnection(); pstmt=conn.prepareStatement(sql); initParam(pstmt,params); return pstmt.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); }finally{ try{ if(pstmt!=null) pstmt.close(); if(conn!=null) conn.close(); }catch(SQLException e1){ throw new RuntimeException(e1); } } } public T query(String sql,RsHandler<T> rh,Object...params){ Connection conn=null; PreparedStatement pstmt=null; ResultSet rs=null; try { conn=dataSource.getConnection(); pstmt=conn.prepareStatement(sql); initParam(pstmt,params); rs= pstmt.executeQuery(); return rh.handle(rs); } catch (SQLException e) { throw new RuntimeException(e); }finally{ try{ if(pstmt!=null) pstmt.close(); if(conn!=null) conn.close(); }catch(SQLException e1){ throw new RuntimeException(e1); } } } public void initParam(PreparedStatement pstmt,Object...params) throws SQLException{ for(int i=0;i<params.length;i++){ pstmt.setObject(i+1, params[i]); } } } interface RsHandler<T>{ public T handle(ResultSet rs)throws SQLException; }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package cn.itcast.utils; import java.sql.ResultSet; import java.sql.SQLException; import org.junit.Test; import cn.itcast.jdbc.JdbcUtils; @SuppressWarnings({"rawtypes","unchecked"}) public class Demo2 { @Test public void fun1(){ // Stu s=new Stu(1002,"lisi",23,"woman"); // addStu(s); Stu s=load(1002); System.out.println(s); } public void addStu(Stu stu){ QR qr=new QR(JdbcUtils.getDataSource()); String sql="INSERT INTO t_stu VALUES(?,?,?,?)"; Object[] params={stu.getId(),stu.getSname(),stu.getAge(),stu.getGender()}; qr.update(sql, params); } public Stu load(int sid){ QR qr=new QR(JdbcUtils.getDataSource()); String sql="SELECT * FROM t_stu WHERE id=?"; Object[] params={sid}; RsHandler<Stu> rh=new RsHandler<Stu>(){ @Override public Stu handle(ResultSet rs) throws SQLException { if(!rs.next()) return null; Stu stu=new Stu(); stu.setId(rs.getInt(1)); stu.setSname(rs.getString(1)); stu.setAge(rs.getInt(3)); stu.setGender(rs.getString(4)); return stu; } }; return (Stu) qr.query(sql, rh, params); } }
dbUtils:
common-dbutils.jar
QueryRunner
update方法:
*int update(String sql,Object...params)-->可执行增、删、改语句
*int update(Connection con,String sql,Object...params),调用者提供Connection,便于事务
query方法
*T query(String sql,ResultSetHandler rsh,Object...params)-->可执行查询
>它会先得到ResultSet,然后调用rsh的handle()把rs转换成需要的;类型
*T query(Connection con,String sql,ResultSetHandler rsh,Object...params)
ResultSetHandler接口:
*BeanHandler(单行)-->构造器需要一个Class类型的参数,用来将一行结果转换成指定类型的javaBean对象
*BeanListHandler(多行)-->构造器也是需要一个Class类型的参数,用来将一行结果转换成一个javaBean,多行就是转换成list对象,一堆javaBean
*MapHandler(单行)-->将一行结果转换为Map对象
>一行记录:
sid sname age gender
1001 zs 99 male
>一个Map:
{sid:1001,sname:zs,age:99,gender:male}
*MapListHandler(多行)-->把一列记录转换成一个Map,多列记录转换成一个Map为子元素的List集合
*ScalarHandler(单列多行)-->通常用于select count(*) from t_stu语句,结果集是单行单列的,它的类型是Object
例子:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package cn.itcast.utils; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import java.util.Map; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import org.junit.Test; import cn.itcast.jdbc.JdbcUtils; public class Demo3 { @Test public void fun1() throws SQLException{ QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource()); String sql="INSERT INTO t_stu VALUES (?,?,?,?)"; Object[] params={1003,"wangwu",23,"woman"}; qr.update(sql,params); } @Test public void fun2() throws SQLException{ QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource()); String sql="SELECT * FROM t_stu WHERE id=?"; Object[] params={1003}; ResultSetHandler<Stu> rsh=new ResultSetHandler<Stu>(){ @Override public Stu handle(ResultSet rs) throws SQLException { if(!rs.next()) return null; Stu stu=new Stu(); stu.setId(rs.getInt(1)); stu.setSname(rs.getString(2)); stu.setAge(rs.getInt(3)); stu.setGender(rs.getString(4)); return stu; } }; /* *执行query()方法,需要给出结果集处理器,即ResultSetHandler的实现类 对象,完成ResultSet到指定类型的转换 */ Stu stu=qr.query(sql, rsh,params); System.out.println(stu); } /** * BeanHandler 单行 * Bean有关的需要注意内部可能是使用BeanUtils或者内省实现,需要每一个成员字段都有对应的属性 * @throws SQLException */ @Test public void fun3() throws SQLException{ QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource()); String sql="SELECT * FROM t_stu WHERE id=?"; Object[] params={1003}; ResultSetHandler<Stu> rsh=new BeanHandler<Stu>(Stu.class); Stu stu=qr.query(sql,rsh ,params); System.out.println(stu); } /** * BeanListHandler 多行 * @throws SQLException */ @Test public void fun4() throws SQLException{ QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource()); String sql="SELECT * FROM t_stu"; ResultSetHandler<List<Stu>> rsh=new BeanListHandler<Stu>(Stu.class); List<Stu> stus=qr.query(sql,rsh); System.out.println(stus); } /** * MapHandler 单行 * @throws SQLException */ @Test public void fun5() throws SQLException{ QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource()); String sql="SELECT * FROM t_stu WHERE id=?"; Object[] params={1003}; ResultSetHandler<Map<String, Object>> rsh=new MapHandler(); Map<String, Object> stu=qr.query(sql,rsh ,params); System.out.println(stu); } /** * MapListHandler 多行 * @throws SQLException */ @Test public void fun6() throws SQLException{ QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource()); String sql="SELECT * FROM t_stu"; ResultSetHandler<List<Map<String, Object>>> rsh=new MapListHandler(); List<Map<String, Object>> stus=qr.query(sql,rsh); System.out.println(stus); } /** * ScalarHandler 单行单列 * @throws SQLException */ @Test public void fun7() throws SQLException{ QueryRunner qr=new QueryRunner(JdbcUtils.getDataSource()); String sql="SELECT COUNT(*) FROM t_stu"; ResultSetHandler<Object> rsh=new ScalarHandler(); long count=(Long) qr.query(sql,rsh ); System.out.println(count); } }