第三方mysql连接池_第三方数据库连接池包

packagecn.demo;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.Iterator;importjava.util.List;importjava.util.Map;importjavax.sql.DataSource;importorg.apache.commons.dbutils.QueryRunner;importorg.apache.commons.dbutils.ResultSetHandler;importorg.apache.commons.dbutils.handlers.ArrayHandler;importorg.apache.commons.dbutils.handlers.ArrayListHandler;importorg.apache.commons.dbutils.handlers.BeanHandler;importorg.apache.commons.dbutils.handlers.BeanListHandler;importorg.apache.commons.dbutils.handlers.ColumnListHandler;importorg.apache.commons.dbutils.handlers.KeyedHandler;importorg.apache.commons.dbutils.handlers.MapHandler;importorg.apache.commons.dbutils.handlers.MapListHandler;importorg.apache.commons.dbutils.handlers.ScalarHandler;importorg.junit.Test;importcn.domain.Contact;importcn.domain.User;import static cn.dbutils.DataSourceUtils.*;public classDemo1 {

@Testpublic void query1() throwsException {

QueryRunner run= newQueryRunner(getDataSource());

String sql= "select * from users";

Object[] o= run.query(sql, newArrayHandler());for(Object s : o) {

System.err.println(s);

}

}

@Testpublic void query2() throwsException {

QueryRunner run= newQueryRunner(getDataSource());

String sql= "select * from users";

List o = run.query(sql, newArrayListHandler());for(Object[] s : o) {for(Object os : s) {

System.err.println(os);

}

}

}

@Testpublic void queryBean() throwsException {

QueryRunner run= newQueryRunner(getDataSource());

String sql= "select * from users ";

User user= run.query(sql, new BeanHandler(User.class));

System.err.println(user);

}

@Testpublic void queryBean1() throwsException {

QueryRunner run= newQueryRunner(getDataSource());

String sql= "select * from users where id=?";

User user= run.query(sql, new BeanHandler(User.class), "U003");

System.err.println(user);

}

@Testpublic void queryBeanList() throwsException {

QueryRunner run= newQueryRunner(getDataSource());

String sql= "select * from users ";

List user = run.query(sql, new BeanListHandler(User.class));for(User u : user) {

System.err.println(u);

}

}

@Testpublic void queryColumnList() throwsException {

QueryRunner run= newQueryRunner(getDataSource());

String sql= "select name from users ";

List obj = run.query(sql, newColumnListHandler());for(Object o : obj) {

System.err.println(o);

}

}

@Testpublic void querykeyed() throwsException {

QueryRunner run= newQueryRunner(getDataSource());

String sql= "select * from users";

Map> mm = run.query(sql, newKeyedHandler("id"));

System.err.println(mm);

Iterator it=mm.keySet().iterator();while(it.hasNext()) {

Map m1=mm.get(it.next());

System.err.println(m1.get("id") + "," + m1.get("name") + ","

+ m1.get("pwd"));

}

}

@Testpublic void queryMap() throwsException{

QueryRunner run= newQueryRunner(getDataSource());

String sql= "SELECT u.name as uname,c.name as cname"+

" FROM users u INNER JOIN contacts c ON u.id=c.uid where u.id='U001'";

System.err.println(sql);

Map mm = run.query(sql,newMapHandler());

System.err.println(mm);

}

@Testpublic void queryMapList() throwsException{

QueryRunner run= newQueryRunner(getDataSource());

String sql= "SELECT u.name as uname,c.name as cname"+

" FROM users u INNER JOIN contacts c ON u.id=c.uid where u.id='U001'";

System.err.println(sql);

List> mm = run.query(sql,newMapListHandler());

System.err.println(mm);

}

@Testpublic void queryScalar() throwsException{

QueryRunner run= newQueryRunner(getDataSource());

String sql= "select count(*) from users ";

System.err.println(sql);

Object mm= run.query(sql,newScalarHandler());

System.err.println(mm);

}

@Testpublic void queryValidBean() throwsException{

QueryRunner run= newQueryRunner(getDataSource());

String sql= "select id as cid,name as cname,sex from contacts";

List cs = run.query(sql,new BeanListHandler(Contact.class));

System.err.println(cs);

}//手工封装

@Testpublic void queryValidBean1() throwsException{

QueryRunner run= newQueryRunner(getDataSource());

String sql= "select * from contacts";

List cs =run.query(sql,new ResultSetHandler>(){

@Overridepublic Listhandle(ResultSet rs)throwsSQLException {

List list = new ArrayList();while(rs.next()){

Contact c= newContact();

c.setCid(rs.getString("name"));

c.setCname(rs.getString("id"));

c.setSex(rs.getString("sex"));

list.add(c);

}returnlist;

}

});

System.err.println(">>>:"+cs);

}

@Testpublic void Insert1() throwsException {

QueryRunner run= newQueryRunner(getDataSource());

run.update("insert into users values('U003','张三','888')");

}

@Testpublic void Insert2() throwsException {

QueryRunner run= newQueryRunner(getDataSource());

run.update("insert into users values(?,?,?)", "u004", "王武", "999");

}

@Testpublic void del() throwsException {

QueryRunner run= newQueryRunner(getDataSource());

String sql= "delete from users where name ='王武'";int len =run.update(sql);

System.err.println(len);

}

@Testpublic void update() throwsException {

QueryRunner run= newQueryRunner(getDataSource());

String sql= "update users set name='李四' where id='U003'";int len =run.update(sql);

System.err.println(len);

}

}