Java的DBUtils工具演示

Java的DBUtils工具演示

掌握 DbUtils工具的关键是:学好2个(类+接口)

1、QueryRunner类 用于执行SQL语句
2、ResultSetHandler接口 用于封装返回结果 具体使用时 是用他的实现类

在演示中我使用的是c3p0连接池。

/* SQL脚本
     * CREATE TABLE person(
        id VARCHAR(32) PRIMARY KEY,
        NAME VARCHAR(32),
        address VARCHAR(30),
        age INT 
        );
        还要建立person的值对象
     */

@Test //不带事务的增,不带事务会自动帮我们关流   Statement
    public void save1() throws Exception {
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        String sql="INSERT INTO person VALUES('A002','Jack','长沙',25);";
        run.update(sql);
    }
    @Test //不带事务的增，PreparedStatement
    public void save2() throws Exception {
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        //可变参数，内部会自动帮我们把参数赋值给对应序号的占位符'?'
        run.update("INSERT INTO person VALUES('A003','Rose',?,?)","湖南益阳",20);
    }
    @Test //带事务的增，PreparedStatement
    public void save3() throws Exception {
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        //事务
        Connection  con=C3p0Pool.getConnection();
        try {
            con.setAutoCommit(false);
            //结果 错误的回滚了，正确的进库了
            run.update("INSERT INTO person VALUES('A004','张三',?,?)","益阳",28);
            run.update("INSERT INTO person VALUES('A003','李四',?,?)","杭州",24);
            con.commit();
        } catch (Exception e) {
            con.rollback();
            System.out.println("事务回滚...");
        }finally{
            con.setAutoCommit(true);
            con.close();
        }
    }
    @Test //带事务的增，PreparedStatement
    public void save4() throws Exception {
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        //事务
        Connection  con=C3p0Pool.getConnection();
        try {
            con.setAutoCommit(false);
            //结果 使用同一个con，都回滚了
            run.update(con,"INSERT INTO person VALUES('A004','张三',?,?)","益阳",28);
            run.update(con,"INSERT INTO person VALUES('A003','李四',?,?)","杭州",24);
            con.commit();
        } catch (Exception e) {
            con.rollback();
            System.out.println("事务回滚...");
        }finally{
            con.setAutoCommit(true);
            con.close();
        }
    }
    @Test //使用DBuUtils 进行删除和修改，原理同新增
    public void update() throws Exception {
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        run.update("update person set age=age-2");

    }

在查询中，重新过in写了一个值对象，其中的arrdess改成addr。

//查询
    @Test //封装成MapList
    public void query() throws Exception {
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        String sql = "select * from person ";
        List<Map<String, Object>> person = run.query(sql, new MapListHandler());
        System.out.println(person);
    }
    @Test //封装成BeanList
    public void query2() throws Exception {
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        String sql = "select * from person ";
        List<Person> person = run.query(sql, new BeanListHandler<Person>(Person.class));
        System.out.println(person);
    }
    @Test //如果出现值对象中属性名和表字段名不相同，则需要别名
    public void query3() throws Exception {
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        //用别名解决不一致的情况
        String sql = "select id,name,address,age from person ";
        List<Person2> person2 = run.query(sql, new BeanListHandler<Person2>(Person2.class));
        System.out.println(person2);
    }
    @Test //带参数
    public void query4() throws Exception {
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        //使用占位符设置参数
        String sql = "select id,name,address,age from person where name like ? and age > ? ";
        List<Person2> person2 = run.query(sql, new BeanListHandler<Person2>(Person2.class),"%j%",18);
        System.out.println(person2);
    }

演示扩展包内容

/以下演示---扩展包和批处理
    //自动生成sql语句，我们必须在JavaBean中添加相应的注解,让我们省去写sql语句
    @Test  //直接让给扩展包内部，帮我们利用Person值对象
    public void query5()  throws Exception{
        //该方式必须给Person类添加注解   @Table(value="person")//对应的数据库表
        ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());
        List<Person> person = run .query(Person.class);
        System.out.println(person);
    }
    @Test   //  O/R  Object/Relation  对象/关系
    public void save5() throws Exception{
        //该方式下  需要在  值对象的属性中加@Column
        Person p = new Person();
        p.setId("A005");
        p.setAge(30);
        p.setName("MMMM");
        ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());

        run.save(p);
    }
    @Test   // 不一致的 添加
    public void save6() throws Exception{
        //该方式下  需要在  值对象的属性中加@Column
        Person2 p = new Person2();
        p.setId("A006");
        p.setAge(30);
        p.setName("ddddd");
        p.setAddr("XXX");
        ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());
        run.save(p);
    }
    //批处理
    @Test   
    public void dom() throws Exception{
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());

        for(int i = 0 ; i <100;i++){
            String sql="insert into stud values(?,?)";
            String [][] values={
                    {"Q001"+i,"Jack"+i},
                    {"Q002"+i,"Tomm"+i}
            };
            run.batch(sql, values);
        }

    }

附录

将获取连接做成工具

public class C3p0Pool {
    private static DataSource ds;// 单例的池
    private static ThreadLocal<Connection> t = new ThreadLocal<Connection>();
    static {
        try {
            ds =new ComboPooledDataSource();
        } catch (Exception e) {
            throw new RuntimeException("数据库连接池创建失败!");
        }
    }

    // 以后会用到这个功能 :
    public static DataSource getDataSource() {
        return ds;
    }

    public static Connection getConnection() {
        Connection con = t.get();
        if (con == null) {
            try {
                con = ds.getConnection();
                t.set(con);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return con;
    }

    public static void cleanConFromThradeLocal() {
        t.set(null);
    }
}

连接池的工具包
http://pan.baidu.com/s/1hrQBXyO