首先dbcp相关的jar包和MySQL的驱动包导入到项目中。
dbcp.properties配置文件如下,并放到项目根目录下。
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///testdb?useUnicode=true&characterEncoding=UTF8&useOldAliasMetadataBehavior=true
username=root
password=root
maxActive=3
获取数据源工具类:
package com.tpf.utils; import java.io.InputStream; import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; public class DbcpDataSourceUtils { private DbcpDataSourceUtils(){} private static DataSource ds; static{ try{ //读取资源文件 InputStream in = DataSourceUtils.class .getClassLoader().getResourceAsStream("dbcp.properties"); //读取资源对象 Properties prop = new Properties(); prop.load(in); //创建ds ds = new BasicDataSourceFactory().createDataSource(prop); }catch(Exception e){ throw new RuntimeException(e.getMessage(),e); } } //返回整个池对象 public static DataSource getDatasSource(){ return ds; } }
第一步:定义回调接口(回调规范),dbutils中的回调接口(回调规范)是 ResultSetHandler
我们自己的回调接口如下:
package com.tpf.callback.beanlisthandler; import java.sql.ResultSet; /** * 定义回调规范 * <p>Title:MyHandler </p> * <p>Description: </p> * <p>Company: www.vfinance.cn </p> * @author tianpengfei * @param <T> * @date 2016年8月10日 下午1:57:40 */ public interface MyHandler<T> { T handler(ResultSet rs); }
第二步:定义接口实现类,dbutils中实现了如下:
这里我们先实现BeanListHandler
我们自己的MyBeanListHandler如下:
package com.tpf.callback.beanlisthandler.impl; import java.lang.reflect.Method; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.ArrayList; import java.util.List; import com.tpf.callback.beanlisthandler.MyHandler; /** * 返回List<Bean>所有行,都封装到List,适合于查询多个结果 * <p>Title:MyBeanListHandler </p> * <p>Description: </p> * <p>Company: www.vfinance.cn </p> * @author tianpengfei * @date 2016年8月10日 下午2:39:44 * @param <T> */ public class MyBeanListHandler<T> implements MyHandler<List<T>>{ private Class<T> cls; public MyBeanListHandler(Class<T> cls) { this.cls = cls; } @Override public List<T> handler(ResultSet rs) { List<T> list = new ArrayList<T>();//定义返回类型 try { ResultSetMetaData rsmd = rs.getMetaData(); int cols = rsmd.getColumnCount();//列数 while(rs.next()){ T t = cls.newInstance();//没获取一行就实例化 for (int i = 0; i < cols; i++) { String colName = rsmd.getColumnName(i+1); colName = colName.toLowerCase(); String mehtodName = "set" + colName.substring(0,1).toUpperCase() + colName.substring(1).toLowerCase(); String javaType = rsmd.getColumnClassName(i+1); try { Method mm = cls.getMethod(mehtodName, Class.forName(javaType)); Object val = rs.getObject(i+1); mm.invoke(t, val); } catch (Exception e) { throw new RuntimeException(e.getMessage(), e); } } list.add(t); } } catch (Exception e) { throw new RuntimeException(e.getMessage(), e); } return list; } }
第三步:定义调用类
Dbutils中的是QueryRunner
我们自己的MyQueryRunner如下:
package com.tpf.callback.beanlisthandler; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import javax.sql.DataSource; /** * 定义自己的回调函数 “调用类” * <p>Title:MyQueryRunner </p> * <p>Description: </p> * <p>Company: www.vfinance.cn </p> * @author tianpengfei * @date 2016年8月10日 下午2:00:04 */ public class MyQueryRunner { private DataSource ds ; public MyQueryRunner() {} public MyQueryRunner(DataSource ds) { this.ds = ds; } public <T>T query(String sql, MyHandler<T> mh){ T t = null; Connection conn = null; try { conn = ds.getConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql); t = mh.handler(rs); } catch (Exception e) { throw new RuntimeException(e.getMessage(), e); } return t; } }
User实体
package com.tpf.domain; public class User { private String id; private String name; private String pwd; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } @Override public String toString() { return "User [id=" + id + ", name=" + name + ", pwd=" + pwd + "]"; } }
第四部:应用测试
@Test public void test1(){ MyQueryRunner myQueryRunner = new MyQueryRunner(DataSourceUtils.getDateSource()); String sql = "select * from users"; List<User> list = myQueryRunner.query(sql, new MyBeanListHandler<User>(User.class)); for (User user : list) { System.err.println(user); } }
打印结果如下:
User [id=001, name=张三, pwd=111]
User [id=002, name=李四, pwd=222]
User [id=003, name=王五, pwd=333]