你的位置:首页 > Java教程

[Java教程]java学习笔记—第三方操作数据库包专门接收DataSource


Dbutils

       操作数据第三方包。依赖数据源DataSource(DBCP|C3p0)。

QueryRunner – 接收DataSource|Connection,查询数据删除修改操作。返回结果。

ResultSetHandler – 结果集句柄,将结果数据封装成程序所需要的数据类型Map,List,Bean。

第一步:创建一个新的java项目

第二步:导入包

       Mysql.jar

       Dbcp.jar – 连接池的包。

       Dbtutil.jar

 

第三步:创建dataSource数据源

driverClassName=com.mysql.jdbc.Driver

url=jdbc:mysql:///db909?characterEncoding=UTf8

username=root

password=1234

maxActive=5

工厂类创建一个唯一的dataSorcepackage cn.itcast.utils;import java.io.InputStream;import java.util.Properties;import javax.sql.DataSource;import org.apache.commons.dbcp.BasicDataSourceFactory;public class DataSourceUtils {  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的使用

       Queryrunner.query 只可以执行select语句。

       Queryrunner.update只可以接收update,delte,insert语句。

保存数据:

@Test  public void insert1() throws Exception{    QueryRunner run = new QueryRunner(getDatasSource());    String sql = "insert into users values('U002','李四','888')";    run.update(sql);  }  //接收?  @Test  public void insert2() throws Exception{    QueryRunner run = new QueryRunner(getDatasSource());    String sql = "insert into users values(?,?,?)";    run.update(sql,"U003","王五","7777");  }  //删除  @Test  public void del1() throws Exception{    QueryRunner run = new QueryRunner(getDatasSource());    String sql = "delete from users where name=?";    int len = run.update(sql,"李四");    System.err.println(len);  }  @Test  public void udpate1() throws Exception{    QueryRunner run = new QueryRunner(getDatasSource());    String sql = "update users set name=? where id=?";    run.update(sql,"赵'七","U001");  }

查询:

查询时除了使用queryrunner还要使用别一个核心类resultsethandler:结果集的句本周 封装结果到List,Map,Bean。

ArrayHandler,  - 用于查询一行唯一的数据。Object[]QueryRunner run = new QueryRunner(getDatasSource());    String sql = "select * from users";    Object[] os = run.query(sql,new ArrayHandler());    for(Object o:os){      System.err.println(o);    }

结果是:

U001

赵'七

123

ArrayListHandler, - 将所有的结果封装成List<Object[]>    QueryRunner run = new QueryRunner(getDatasSource());    String sql = "select * from users";    List<Object[]> list = run.query(sql,new ArrayListHandler());    for(Object[] os:list){      for(Object o:os){        System.err.print(o+"\t");      }      System.err.println("\n=====");    }

返回的是:

U001   赵'七   123

=====

U002   AaA 44 

=====

U003   88  777

=====

 BeanHandler,  - 返回一个 Bean,结果封装到Bean。只查询第一行。适合根据id查询唯一的一行。@Test  public void query2() throws Exception{    QueryRunner run = new QueryRunner(getDatasSource());    String sql = "select * from users where id=?";    User user =         run.query(sql,new BeanHandler<User>(User.class),"U003");    System.err.println(user);  }

BeanListHandler ,  - 返回List<Bean>所有行,都封装到List,适合于查询多个结果@Test  public void query3() throws Exception{    QueryRunner run = new QueryRunner(getDatasSource());    String sql = "select * from users";    List<User> us =         run.query(sql,new BeanListHandler<User>(User.class));    for(User u:us){      System.err.println(u);    }      }

结果是:

User [id=U001, name=赵'七, pwd=123]

User [id=U002, name=AaA, pwd=44]

User [id=U003, name=88, pwd=777]

ColumnListHandler, - 查询一个字段时就可以使用这种方式 select name from table;QueryRunner run = new QueryRunner(getDatasSource());    String sql = "select * from users";    List<Object> list = run.query(sql, new ColumnListHandler("pwd"));    for(Object o:list){      System.err.println(o);    }

 

 KeyedHandler -- QueryRunner run = new QueryRunner(getDatasSource());    String sql = "select * from users";    Map<Object,Map<String,Object>> mm =         run.query(sql, new KeyedHandler("id"));    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"));    }

结果是:

{U001={id=U001, pwd=123, name=赵'七}, U002={id=U002, pwd=44, name=AaA}, U003={id=U003, pwd=777, name=88}}

 

, MapHandler ,  与 BeanHandler应,只返回第一行,封装成Map类型 - 也适合于查询一个结果集的  适合于关联查询,且没有匹配的Bean。

 

 

 

 

MapListHandler ,  - List<Map>@Test  public void query6() throws Exception{    QueryRunner run = new QueryRunner(getDatasSource());    String sql = "SELECT u.name as uname,c.name as cname"+           " FROM users u INNER JOIN contacts c ON u.id=c.uid";    System.err.println(sql);    List<Map<String,Object>> mm = run.query(sql,new MapListHandler());    System.err.println(mm);  }SELECT u.name as uname,c.name as cname FROM users u INNER JOIN contacts c ON u.id=c.uid[{uname=Jack, cname=Rose}, {uname=Jack, cname=李四}, {uname=Jack, cname=王五}, {uname=张三, cname=马六}, {uname=张三, cname=赵七}]

 

 

ScalarHandler – 标量- 用于select count(1)|sum|avg|max …一些聚合函数@Test  public void query7() throws Exception{    QueryRunner run = new QueryRunner(getDatasSource());    String sql = "select count(*) from contacts";    Object o = run.query(sql,new ScalarHandler());    Integer ss = Integer.valueOf(o.toString());    System.err.println(ss);  }

总结:

       1:包装HttpServletRequest。是对getParaqmgermap增强。

       2:用dbcp连接数据库,并在一个项目中维护一个唯一的dataSource。

              工厂类,一个datasource的成员静态。

         Static代码块中读取配置文件连接数据库。创建datasource。

    提供一个工厂方法返回整个池对象。

3:dbutils的所有查询方法

       所有ResultSetHandler的子类做一次。

       MapHandler

       MapListHandler

       BeanHandler

        BeanListHandler

    ScalerHandler