1 package com.oracle.demo01;
 2 
 3 import javax.sql.DataSource;
 4 
 5 import org.apache.commons.dbcp.BasicDataSource;
 6 
 7 public class DBUTils {
 8         public static final String DRIVER = "com.mysql.jdbc.Driver";
 9         public static final String URL = "jdbc:mysql://localhost:3306/goods?characterEncoding=utf-8";
10         public static final String USERNAME = "root";
11         public static final String PASSWORD = "1";
12         /*
13          * 创建连接池BasicDataSource
14          */
15         public static BasicDataSource dataSource = new BasicDataSource();
16         //静态代码块
17         static {
18             //对连接池对象 进行基本的配置
19             dataSource.setDriverClassName(DRIVER); // 这是要连接的数据库的驱动
20             dataSource.setUrl(URL); //指定要连接的数据库地址
21             dataSource.setUsername(USERNAME); //指定要连接数据的用户名
22             dataSource.setPassword(PASSWORD); //指定要连接数据的密码
23         }
24         /*
25          * 返回连接池对象
26          */
27         public static DataSource getDataSource(){
28             return dataSource;
29         }
30     }
31     

 1     //添加
 2     public static void insert() throws SQLException{
 3         //获取一个执行sql语句的对象
 4         QueryRunner qr = new    QueryRunner(DBUTils.getDataSource());
 5         //sql语句
 6         String sql = "insert into user(uid,username,password) values(?,?,?)";
 7         //把要添加的数据写到obj数组里去，此处写可以传值
 8         Object[] obj = {17,"wrc","123"};
 9         //接收结果
10         int count = qr.update(sql,obj);
11         System.out.println(count);
12     }

 1     //修改
 2     public static void insert() throws SQLException{
 3         //获取一个执行sql语句的对象
 4         QueryRunner qr = new QueryRunner(DBUTils.getDataSource());
 5         //sql语句
 6         String sql = "update user set username=? where uid='17'";
 7         //把要添加的数据写到obj数组里去，此处写可以传值
 8         Object[] obj = {"wang"};
 9         //接收结果
10         int count = qr.update(sql,obj);
11         System.out.println(count);
12     }

 1 //封装查询的第一条数据
 2     public static void insert() throws SQLException{
 3         //获取一个执行sql语句的对象
 4         QueryRunner qr = new QueryRunner(DBUTils.getDataSource());
 5         //sql语句
 6         String sql = "select * from user";
 7         //把查询的数据返回一个数组
 8         Object[] obj = qr.query(sql, new ArrayHandler());
 9         //遍历
10         for(Object o:obj){
11             System.out.println(o);
12         }
13     }

    //查询每一条数据都存进去
    public static void insert() throws SQLException{
        //获取一个执行sql语句的对象
        QueryRunner qr = new QueryRunner(DBUTils.getDataSource());
        //sql语句
        String sql = "select * from user";
        //把查询的数据返回一个数组
        List<Object[]> obj = qr.query(sql,new ArrayListHandler());
        //遍历
        for(Object[] o:obj){
            for(Object i:o){
                System.out.println(i+"\t");
            }
            System.out.println("\t");
        }
    }

    //把对象一个对象存进去然后获取对象的每一条数据，常用

    public static void insert() throws SQLException{
        //获取一个执行sql语句的对象
        QueryRunner qr = new QueryRunner(DBUTils.getDataSource());
        //sql语句
        String sql = "select * from user";
        //把对象存进去
        List<Sort> sork = qr.query(sql, new BeanListHandler<Sort>(Sort.class));
        //遍历对象
        for(Sort o:sork){
            System.out.println(o+"\t");
        }
    }

    //查询具体的哪个字段数据
    public static void find5() throws SQLException{
        QueryRunner qr = new QueryRunner(DBUTils.getDataSource());
        String sql ="select * from sort";
        List<String> list = qr.query( sql, new ColumnListHandler<>("sname"));
        for(String arr:list){
            System.out.println(arr);
        }
    }

 1 //获得第一条数据用键值对的形式
 2     public static void find7() throws SQLException{
 3         QueryRunner qr = new QueryRunner(DBUTils.getDataSource());
 4         String sql ="select * from sort";
 5         //第一条数据
 6         Map<String,Object> map = qr.query( sql,new MapHandler());
 7         //遍历
 8         for(String key:map.keySet()){
 9             System.out.println(key+"\t"+map.get(key));
10         }
11     }

 1     //以键值对的形式存数据
 2     public static void find8() throws SQLException{
 3         QueryRunner qr = new QueryRunner(DBUTils.getDataSource());
 4         String sql ="select * from sort";
 5         List<Map<String,Object>> map = qr.query(sql,new MapListHandler());
 6         //先遍历map，再遍历string
 7         for(Map<String,Object> m:map){
 8             for(String str:m.keySet()){
 9                 System.out.println(str+"\t"+m.get(str));    
10             }
11         }
12     }