package com.mysql.demo; import java.sql.SQLException; import java.util.Iterator; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.junit.Test; import com.mysql.bean.user; //增删改查 //用QueryRunner接口和ResultSetHandler //增删改:1:queryRunner()方法获取数据流连接池的连接,2:在执行queryRunner提供的update()方法 //查询方法:1:queryRunner()获取连接。2:调用queryRunner的query()方法。在对结果集Handler,把数据封装为一个list集合 public class dbutil { //使用dbutil完成数据库的crud @Test public void insert() throws SQLException { //获取数据库连接池操作数据库 QueryRunner run=new QueryRunner(sqlpooling.getDataSource()); String sql="insert into login values(?,?)"; Object params[]={"keke","keke"}; run.update(sql, params); } @Test public void update() throws SQLException { //获取数据库连接池操作数据库 QueryRunner run=new QueryRunner(sqlpooling.getDataSource()); String sql="update login set password=? where username=?"; Object params[]={"789","keke"}; run.update(sql, params); } @Test public void delete() throws SQLException { //获取数据库连接池操作数据库 QueryRunner run=new QueryRunner(sqlpooling.getDataSource()); String sql="delete from login where username=?"; //只有一个参数没必要准备参数数组了 run.update(sql, "simant"); } @Test public void find() throws SQLException { //获取数据库连接池操作数据库 QueryRunner run=new QueryRunner(sqlpooling.getDataSource()); String sql="select username,password from login where username=?"; user u=(user) run.query(sql,"keke",new BeanHandler(user.class));//把结果集处理到那个bean中去,创建userbean,这边省略 System.out.println(u.getPassword()); } @Test public void findAll() throws SQLException { //获取数据库连接池操作数据库 QueryRunner run=new QueryRunner(sqlpooling.getDataSource()); String sql="select * from login"; List uu=(List) run.query(sql,new BeanListHandler(user.class)); //多条记录的话用beanlistHandler处理到一个集合中去 Iterator<user> it=uu.iterator(); while(it.hasNext()) { user u=it.next(); System.out.println(u.getUsername()); System.out.println(u.getPassword()); } } @Test public void batch() throws SQLException//批量处理的话要插入多条记录,用二维数组 { //获取数据库连接池操作数据库 QueryRunner run=new QueryRunner(sqlpooling.getDataSource()); String sql="insert into login values(?,?)"; Object param[][]=new Object[3][2];//大小为3的数组,大小为3的数组里面又是大小为2的数组 for(int i=0;i<param.length;i++) { param[i]=new Object[]{"name"+i,"password"+i}; } run.batch(sql, param); } }