Apache DBUtils用法
1.所需的jar包
https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils
2.重点类/接口
- QueryRunner: 实现增删查改操作
- ResultSetHandler:接口;实现类返回结果/结果类型
3.增删查改
/** * 查询单行数据,用数组接收 * 条件有多个满足,例如>?,也只会读取一行 */ QueryRunner qr = new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); Object[] user = qr.query("select * from student where id =?", new ArrayHandler(), 3); System.out.println("查询单行数据,用数组接收:" + user[0] + "---" + user[1]);
/** * 查询单值结果,即条件下列的值 */ QueryRunner qr6 = new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); Integer a = qr.query("SELECT money FROM student WHERE id = ?", new ScalarHandler<Integer>(), 1); System.out.println("查询单值结果,即条件下列的值:"+a);
/** * 查询多行数据,用集合接收 */ QueryRunner qr1 = new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); List<Object[]> user1 = qr.query("select * from student", new ArrayListHandler()); System.out.println("查询多行数据,用集合接收:"); for (Object[] s : user1) System.out.println(s[0] + "---" + s[1]);
/** *查询单行数据,用对象接收 */ QueryRunner qr2 = new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); Student student = qr2.query("select * from student where id > ?", new BeanHandler<Student>(Student.class), 1); System.out.println("查询单行数据:" + student.getId() + "---" + student.getMoney());
/** * 查多行数据,用对象集合接收 */ QueryRunner qr3 = new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); List<Student> students = qr3.query("select * from student where id > ?", new BeanListHandler<Student>(Student.class), 1); System.out.println("查多行数据,用对象集合接收:"); for (Student s : students) { System.out.println(s.getId() + "---" + s.getMoney()); }
/** *查询数据,用map接收 */ QueryRunner qr11 = new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); Map<Integer, Student> id = qr.query("select * from student where id > ?", new BeanMapHandler<Integer, Student>(Student.class, "id"), 1); System.out.println("查询数据,用map接收:"); for (Integer integer : id.keySet()) { Student student1 = id.get(integer); System.out.println(student1.getId()+"---"+student1.getMoney()); }
/** *查询数据,以id为key值,对象为value值 */ QueryRunner qr4 = new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); Map<Integer, Map<String, Object>> s = qr4.query("select * from student where id > ?", new KeyedHandler<Integer>(), 1); System.out.println("student对象取值:" + s + ",key为2的对象值:" + s.get(2));
/** *得到满足条件的列id */ QueryRunner qr5 = new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); List<Integer> ss = qr5.query("select * from student where id > ?", new ColumnListHandler<Integer>("id"), 1); System.out.println("得到满足条件的列id"); for (Integer s0 : ss) { System.out.println(s0); }
/** * 多条件查询,new Object[]{----} */ QueryRunner qr7= new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); Integer b = qr.query("SELECT money FROM student WHERE id = ? and money like ?", new ScalarHandler<Integer>(), new Object[]{1, "%0%"}); System.out.println("多条件查询:"+b);
/** * 增 */ QueryRunner qr8= new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); int count = qr.update("insert into student (money) values (?)", 500); System.out.println("增加了" + count + "条数据");
/** * 改 */ QueryRunner qr9= new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); int count1 = qr.update("update student set money=? where id=?", new Object[]{500,5}); System.out.println("修改了" + count1 + "条数据");
/** * 删 */ QueryRunner qr10= new QueryRunner(ComboPooledDataSourceUtil.getDataSourceByxml()); int count2 = qr.update("delete from student where id=?", 4); System.out.println("删除了" + count2 + "条数据");
注释:数据源 c3p0