DBUtils使用详解

来源：互联网发布：朋友圈视频转发软件编辑：程序博客网时间：2024/06/02 06:29

DBUtils连接数据库代码

import java.sql.Connection;import javax.sql.*;import com.mchange.v2.c3p0.ComboPooledDataSource;import com.mysql.jdbc.ResultSet;import com.mysql.jdbc.Statement;public class C3p0Util {    //得到一个数据源    private static DataSource dataSource=new ComboPooledDataSource();    public static DataSource getDataSource() {        return dataSource;    }    //从数据源中得到一个连接对象    public static Connection getConnection(){        try {            return dataSource.getConnection();        } catch (Exception e) {            e.printStackTrace();        }        return null;    }    //关闭连接资源    public static void closeConn(Connection conn,Statement stmt,ResultSet rs){        if(rs!=null){            try {                rs.close();            } catch (Exception e) {                e.printStackTrace();            }            rs = null;        }        if(stmt!=null){            try {                stmt.close();            } catch (Exception e) {                e.printStackTrace();            }            stmt = null;        }        if(conn!=null){            try {                conn.close();//关闭            } catch (Exception e) {                e.printStackTrace();            }            conn = null;        }    }}

c3p0配置

<?xml version="1.0" encoding="UTF-8"?><c3p0-config>  <default-config>    <property name="driverClass">com.mysql.jdbc.Driver</property>    <property name="jdbcUrl">jdbc:mysql://ip地址:3306/java_review?useUnicode=true&amp;characterEncoding=UTF8    </property>//&的转义字符为&amp;    <property name="user">用户名</property>    <property name="password">密码</property>    <property name="initialPoolSize">10</property>    <property name="maxIdleTime">30</property>//最大等待时间    <property name="maxPoolSize">100</property>//连接池最大连接数    <property name="minPoolSize">10</property>//连接词最小连接数  </default-config></c3p0-config>

DBUtlis的update(增删改)操作代码

import java.sql.SQLException;import java.util.Date;import org.apache.commons.dbutils.QueryRunner;import org.junit.Test;public class TestCRUD {    @Test    //插入数据    public void testInsert() throws SQLException{        //创建一个QueryRunner对象        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        //执行sql语句        qr.update("insert into users(username,password,email,birthday) values(?,?,?,?)", "菜10","123","c10@163.com",new Date());    }    @Test    //修改数据    public void testUpdate() throws SQLException{        //创建一个QueryRunner对象        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        qr.update("update users set username=?,password=? where id=?", "周杰杰","333",15);    }    @Test//删除数据    public void testDelete() throws SQLException{        //创建一个QueryRunner对象        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        qr.update("delete from users where id=?", 15);    }    @Test//批处理，只能执行相同的sql语句    public void testBatch() throws SQLException{        //创建一个QueryRunner对象        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        Object[][] params = new Object[10][];//高维代表执行多少次sql语句        for (int i = 0; i < params.length; i++) {            params[i] = new Object[]{"菜10"+i,"123","c10@163.com",new Date()};//给每次执行的sql语句中的？赋值        }        qr.batch("insert into users(username,password,email,birthday) values(?,?,?,?)", params );    }}

DBUtils的select(查询)操作代码

import java.sql.SQLException;import java.util.List;import java.util.Map;import org.apache.commons.dbutils.QueryRunner;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;import org.junit.Test;import com.itheima.entity.User;public class TestResultSetHandler {    //@Test是测试的，不需要写main函数既可以执行方法    @Test//ArrayHandler:适合取1条记录。把该条记录的每列值封装到一个数组中Object[]    public void tese1() throws SQLException{        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        Object[] arr  = qr.query("select * from users", new ArrayHandler());        for (Object o : arr) {            System.out.println(o);        }    }    @Test//ArrayListHandler:适合取多条记录。把每条记录的每列值封装到一个数组中Object[]，把数组封装到一个List中    public void tese2() throws SQLException{        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        List<Object[]> query = qr.query("select * from users", new ArrayListHandler());        for (Object[] os : query) {            for (Object o : os) {                System.out.println(o);            }            System.out.println("--------------");        }    }    @Test //ColumnListHandler:取某一列的数据。封装到List中。    public void tese3() throws SQLException{        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        List<Object> list = qr.query("select username,password from users", new ColumnListHandler(1));        for (Object o : list) {            System.out.println(o);        }    }    @Test //KeyedHandler:取多条记录，每一条记录封装到一个Map中，再把这个Map封装到另外一个Map中，key为指定的字段值。    public void tese4() throws SQLException{        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        //大Map的key是表中的某列数据，小Map中的key是表的列名，所以大Map的key是Object类型，小Map的key是String类型        Map<Object,Map<String,Object>> map = qr.query("select * from users", new KeyedHandler(1));        for (Map.Entry<Object, Map<String,Object>> m : map.entrySet()) {            System.out.println(m.getKey());//大Map中key值就是id列的值            for (Map.Entry<String, Object> mm : m.getValue().entrySet()) {                System.out.println(mm.getKey()+"\t"+mm.getValue());//取出小Map中的列名和列值            }            System.out.println("---------------------");        }    }    @Test//MapHandler:适合取1条记录。把当前记录的列名和列值放到一个Map中    public void tese5() throws SQLException{        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        Map<String,Object> map = qr.query("select * from users where id=?", new MapHandler(),20);        for (Map.Entry<String, Object> m : map.entrySet()) {            System.out.println(m.getKey()+"\t"+m.getValue());        }    }    @Test//MapListHandler:适合取多条记录。把每条记录封装到一个Map中，再把Map封装到List中    public void tese6() throws SQLException{        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        List<Map<String,Object>> list = qr.query("select * from users", new MapListHandler());        for (Map<String, Object> map : list) {            for (Map.Entry<String, Object> m : map.entrySet()) {                System.out.println(m.getKey()+"\t"+m.getValue());            }            System.out.println("---------------");        }    }    @Test //ScalarHandler:适合取单行单列数据    public void tese7() throws SQLException{        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        Object o = qr.query("select count(*) from users", new ScalarHandler(1));        System.out.println(o.getClass().getName());    }    @Test //BeanHandler:适合取单行单列数据    public void tese8() throws SQLException{        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        User user = qr.query("select * from users where id=?", new BeanHandler<User>(User.class),1);        System.out.println(user);    }    @Test //BeanListHandler     public void tese9() throws SQLException{        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());        List<User> list = qr.query("select * from users where id=?", new BeanListHandler<User>(User.class),1);        System.out.println(list.size());    }}

阅读全文

0 0