给大家推荐个靠谱的公众号程序员探索之路，大家一起加油
apache出品的DBUtils 是数据库操作工具类
注意：没有带有注解的情况 需要自己补充
package com.qf.zzh; import java.sql.SQLException; import java.util.List; import java.util.Map; import org..commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.ArrayHandler; import org.apache.commons.dbutils.handlers.ArrayListHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ColumnListHandler; import org.apache.commons.dbutils.handlers.KeyedHandler; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import com.qf.Beans.Student; import com.qf.Beans.User; import com.qf.dbutils.c3p0Utils; import com.sun.org.apache.bcel.internal.generic.NEW; public class TestResultSetHandler { /** * @param args */ // 1.ArrayHandler:适合去一条记录，例如：登录 public static void test1() throws SQLException { // 加载数据源 也就是连接池 qr相当于是获取可以执行sql语句的对象 QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "select id,username,password from user where id=? and username=?"; // 执行完sql语句后 将结果解出来 参数是sql语句，然后返回值类型，后面是不定长参数取决于你的sql语句中有多少问号 Object[] obj = qr.query(sql, new ArrayHandler(), 1, "zhangsan"); for (int i = 0; i < obj.length; i++) { System.out.print(obj[i] + " "); } System.out.println(); } // 2.ArrayListHandler :适合取多条记录，每一条记录都是Object[]，再把Object[]放入到list中 返回list public static void test2() throws SQLException { QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "select * from user"; List<Object[]> list = qr.query(sql, new ArrayListHandler()); for (int i = 0; i < list.size(); i++) { for (int j = 0; j < list.get(i).length; j++) { System.out.print(list.get(i)[j] + " "); } System.out.println(); } } // 3.ColumnListHandler,取某一列的数据，保存到list中 // 这个类的构造方法是列的序号new ColumnListHandler(colIndex),colIndex从1开始，如果没默认是1 // 用这个东西的时候构造方法里面最好传一个数字n，代表了查询结果n这一列没有重复的值 public static void test3() throws SQLException { QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "select tname from teacher"; List<Object> list = qr.query(sql, new ColumnListHandler()); for (int i = 0; i < list.size(); i++) { System.out.println(list.get(i)); } } // 4.keyedHandler它取的是一个查询集合，返回一个map套map，每一条记录封装到 一个map中，每一个字段又在map里的map中 // Map<object,map<string,object>> public static void test4() throws SQLException { QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "select * from teacher"; Map<Object, Map<String, Object>> map = qr .query(sql, new KeyedHandler()); for (Map.Entry<Object, Map<String, Object>> m : map.entrySet()) { System.out.println(m.getKey()); for (Map.Entry<String, Object> mm : m.getValue().entrySet()) { System.out.print(mm.getKey() + "=" + mm.getValue()); } System.out.println("-----------------"); } } // 5.MapHandler用来查询一条记录，多用于登录功能 public static void test5() throws SQLException { QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "select * from user where username=? and password=?"; Map<String, Object> obj = qr.query(sql, new MapHandler(), "lisi", "654321"); for (Map.Entry<String, Object> m : obj.entrySet()) { System.out.print(m.getKey() + "=" + m.getValue()); } System.out.println(); } // 6.MapListHandler 每一行都是一个map，把map放入list里表示多行*** 三颗星重点 public static void test6() throws SQLException { QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "select * from teacher"; List<Map<String, Object>> list = qr.query(sql, new MapListHandler()); for (int i = 0; i < list.size(); i++) { for (Map.Entry<String, Object> m : list.get(i).entrySet()) { System.out.print(m.getKey() + m.getValue() + " "); } System.out.println(); } } // 7.ScalarHandler取单行单列*** 加强记忆 // 他的构造方法的参数默认是1，意思是取第一行的第一列，你可以改为其他列，但都是第一行的 // *****当查询的内容如果是count，那么实际的返回值是long类型 public static void test7() throws SQLException { QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "select * from student"; Object obj = qr.query(sql, new ScalarHandler(4)); System.out.println(obj); } // 8,BeanHandler***** public static void test8() throws SQLException { QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "select * from user where id=?"; User user = qr.query(sql, new BeanHandler<User>(User.class), 1); System.out.println(user); } // 9.BeanListHandler***** public static void test9() throws SQLException { QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "select * from student"; List<Student> list = qr.query(sql, new BeanListHandler<Student>( Student.class)); for (int i = 0; i < list.size(); i++) { System.out.println(list.get(i)); } } // insert,update,delete // 插入 public static void testInsert() throws SQLException { QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "insert into user(id,username,password) values(?,?,?)"; int r = qr.update(sql, 3, "wangwu", "222222"); if (r > 0) { System.out.println("插入成功"); } } // 修改 public static void testUpdate() throws SQLException { QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "update user set username=?,password=? where id=?"; int num = qr.update(sql, "lili", "321654", 3); if (num > 0) { System.out.println("修改成功"); } } // 删除 public static void testDelete() throws SQLException { QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource()); String sql = "delete from user where username=? and password=?"; int num = qr.update(sql, "lili", "321654"); if (num > 0) { System.out.println("删除成功"); } } // 研究ResultSetHandler的子类 public static void main(String[] args) throws SQLException { // TODO Auto-generated method stub // test1(); // test2(); // test3(); // test4(); // test5(); // test6(); // test7(); // test8(); // test9(); // testInsert(); // testUpdate(); // testDelete(); } }
package com.qf.dbutils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class c3p0Utils { private static DataSource dataSource = new ComboPooledDataSource(); public static DataSource getDataSource(){ return dataSource; } // 获取连接的 // 回收 public static Connection getConnection() { try { return dataSource.getConnection(); } catch (SQLException e) { // TODO Auto-generated catch block // e.printStackTrace(); throw new RuntimeException("服务器忙。。。"); } } /** * 表面上是回收方法 实际上执行的是close方法 * * @param rs * @param ps * @param conn */ public static void release(ResultSet rs, Statement ps, Connection conn) { if (rs != null) { try { //放心关 不会真的关闭 c3p0已经实现过了 不会真的关闭 而是回收资源 rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } release(ps, conn); } public static void release(Statement ps, Connection conn) { if (ps != null) { try { ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (conn != null) { try { // 放心关闭，因为不会真的关闭的，而是把close装饰过了，于是close变成了归还。 conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
