druid与dbutils集成示例

使用Hibernate的时候觉得它太笨重了,而且还要在学一下HQL,可能不适合一些敏捷项目,小项目.然后就找到了dbutils,觉得还不错.,因此尝试着把druid和dbutils集成在一起.

首先创建一个DBUtilsHelper,用于连接数据库,起到druid和dbutils之间的桥梁作用.

import java.sql.SQLException;import javax.sql.DataSource;import org.apache.commons.dbutils.QueryRunner;public class DBUtilsHelper {private DataSource ds = null;private QueryRunner runner = null;public DBUtilsHelper() {try {this.ds = DbPoolConnection.getInstance().getDataSource();} catch (SQLException e) {e.printStackTrace();}if (this.ds != null) {this.runner = new QueryRunner(this.ds);}}public DBUtilsHelper(DataSource ds) {this.ds = ds;this.runner = new QueryRunner(this.ds);}public QueryRunner getRunner() {return this.runner;}}

再建一个druid初始化类

import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.sql.SQLException;import java.util.Properties;import com.alibaba.druid.pool.DruidDataSource;import com.alibaba.druid.pool.DruidDataSourceFactory;import com.alibaba.druid.pool.DruidPooledConnection;public class DbPoolConnection {private static DbPoolConnection databasePool = null;private static DruidDataSource dds = null;static {Properties properties = loadPropertyFile("db_server.properties");try {dds = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {e.printStackTrace();}}private DbPoolConnection() {}public static synchronized DbPoolConnection getInstance() {if (null == databasePool) {databasePool = new DbPoolConnection();}return databasePool;}public DruidDataSource getDataSource() throws SQLException {return dds;}public DruidPooledConnection getConnection() throws SQLException {return dds.getConnection();}public static Properties loadPropertyFile(String fullFile) {String webRootPath = null;if (null == fullFile || fullFile.equals(""))throw new IllegalArgumentException("Properties file path can not be null : " + fullFile);webRootPath = DbPoolConnection.class.getClassLoader().getResource("\\").getPath();webRootPath = new File(webRootPath).getParent();InputStream inputStream = null;Properties p = null;try {String profilepath = webRootPath + File.separator + fullFile;System.out.println(profilepath);inputStream = new FileInputStream(new File(profilepath));p = new Properties();p.load(inputStream);} catch (FileNotFoundException e) {throw new IllegalArgumentException("Properties file not found: "+ fullFile);} catch (IOException e) {throw new IllegalArgumentException("Properties file can not be loading: " + fullFile);} finally {try {if (inputStream != null)inputStream.close();} catch (IOException e) {e.printStackTrace();}}return p;}}

下面是测试dbutils示例类

import java.sql.SQLException;import java.util.Iterator;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.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;public class Druid_Dbutils_fully_test {public static void main(String[] args) throws SQLException {DBUtilsHelper dbh = new DBUtilsHelper();QueryRunner runner = dbh.getRunner();runner.update("insert into ssq(qishu) values('1883')");runner.update("update ssq set qishu='1890' where qishu=?", "1880");// 返回ArrayHandler结果,第一行结果：Object[]System.out.println("A:返回ArrayHandler结果......");Object[] arrayResult = runner.query("select * from ssq",new ArrayHandler());for (int i = 0; i < arrayResult.length; i++) {System.out.print(arrayResult[i] + "    ");}System.out.println();// 返回ArrayListHandler结果,第一行结果：List<Object[]>System.out.println("B:返回ArrayListHandler结果(仅显示5行).........");List<Object[]> arrayListResult = runner.query("select * from ssq",new ArrayListHandler());for (int i = 0; i < arrayListResult.size() && i < 5; i++) {for (int j = 0; j < arrayListResult.get(i).length; j++) {System.out.print(arrayListResult.get(i)[j] + "    ");}System.out.println();}System.out.println();// 返回beanSystem.out.println("X:单条返回bean结果.");ShuangSeQiu ssq = runner.query("select * from ssq where qishu like ?",new BeanHandler<ShuangSeQiu>(ShuangSeQiu.class), "2009%");System.out.println("bean:" + ssq.getQishu());System.out.println("X1:单条返回bean结果");ResultSetHandler<ShuangSeQiu> h = new BeanHandler<ShuangSeQiu>(ShuangSeQiu.class);ShuangSeQiu p = runner.query("select * from ssq where qishu like ? limit 1", h, "2009%");System.out.println(p.getQishu());// 返回beanlistSystem.out.println("C:返回BeanList结果(仅显示5行)......");List<ShuangSeQiu> beanListResult = runner.query("select * from ssq",new BeanListHandler<ShuangSeQiu>(ShuangSeQiu.class));Iterator<ShuangSeQiu> iter_beanList = beanListResult.iterator();int shownum = 0;while (iter_beanList.hasNext() && shownum < 5) {System.out.println(iter_beanList.next().getQishu());shownum++;}// 返回指定列System.out.println("D:返回ColumnList结果......");List<Object> columnResult = runner.query("select * from ssq",new ColumnListHandler<Object>("qishu"));Iterator<Object> iter = columnResult.iterator();shownum = 0;while (iter.hasNext() && shownum < 5) {System.out.println(iter.next());shownum++;}// 返回KeyedHandler结果：Map<Object,Map<String,Object>>：map的key为KeyedHandler指定System.out.println("E:返回KeyedHandler结果,期数:2003001的a列值.........");Map<Object, Map<String, Object>> keyedResult = runner.query("select * from ssq", new KeyedHandler<Object>("qishu"));System.out.println(keyedResult.get("2003001").get("a"));// MapHandlerSystem.out.println("F:返回MapHandler结果.........");Map<String, Object> mapResult = runner.query("select * from ssq",new MapHandler());Iterator<String> iter_mapResult = mapResult.keySet().iterator();while (iter_mapResult.hasNext()) {System.out.print(mapResult.get(iter_mapResult.next()) + "   ");}System.out.println();// 返回MapListHandler结果System.out.println("G:返回MapListHandler结果.........");List<Map<String, Object>> mapListResult = runner.query("select * from ssq", new MapListHandler());for (int i = 0; i < mapListResult.size() && i < 5; i++) {Iterator<String> values = mapListResult.get(i).keySet().iterator();while (values.hasNext()) {System.out.print(mapListResult.get(i).get(values.next())+ "   ");}System.out.println();}Object increaseId = runner.query("select last_insert_id()",new ScalarHandler<Object>());System.out.println(increaseId);}}

贴了一堆代码,就这么样吧.另外忘记贴druid配置了

下面列出来(db_server.properties)

driverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql://127.0.0.1:3306/shuangseqiuusername=rootpassword=filters=statinitialSize=2maxActive=300maxWait=60000timeBetweenEvictionRunsMillis=60000minEvictableIdleTimeMillis=300000validationQuery=SELECT 1testWhileIdle=truetestOnBorrow=falsetestOnReturn=falsepoolPreparedStatements=falsemaxPoolPreparedStatementPerConnectionSize=200

转自【http://my.oschina.net/muchuanwazi/blog/225127】