DBUtils

DBUtils

DBUtils封装了JDBC的操作,少写代码

建个表之后会用到

CREATE TABLE product(
  pid INT PRIMARY KEY AUTO_INCREMENT,
  pname VARCHAR(20),
  price DOUBLE,
  category_id VARCHAR(32)
);
-- 插入表记录
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
 INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
 INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');

INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
 INSERT INTO product(pid,pname,price,category_id) VALUES(5,' 真 维 斯 ',200,'c002'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
 INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003'); 
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');

INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
 INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

QueryRunner提供连接

工具类,各种连接

package com.dbsy.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class JDBCUtils {
    private static ComboPooledDataSource ds = new ComboPooledDataSource();
    private static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    public static DataSource getDataSource(){
        return ds;
    }
}

QueryRunner的使用步骤(重要):
1.创建QueryRunner对象
2.调用QueryRunner对象中的方法updatelquery执行sql语句,获取结果

3.处理结果

package com.dbsy.test;

import com.dbsy.utils.C3P0UtilsXML;
import com.dbsy.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;

import java.sql.SQLException;

public class Demo01DbUtils {
    public static void main(String[] args) throws SQLException {
//        insert();
//        update();
//        delete();
        delete2();
    }

    private static void delete2() throws SQLException {
            QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

            String sql="delete from product where pid=?;";
            int row = qr.update(C3P0UtilsXML.getConnection(),sql, 13);

            System.out.println(row+"行数据删除成功");
    }

    private static void delete() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

        String sql="delete from product where pid=?;";
        int row = qr.update(sql, 14);

        System.out.println(row+"行数据删除成功");
    }

    private static void update() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

        String sql="update product set pname=?,price=?,category_id=? where pid=?;";
        Object[] params={"大宝",8.8,"c003",13};
        int row = qr.update(sql, params);

        System.out.println(row+"行数据修改成功!");
    }

    private static void insert() throws SQLException {
        QueryRunner qr = new QueryRunner();

        String sql="insert into product(pid,pname,price,category_id) values(?,?,?,?);";
        int row = qr.update(C3P0UtilsXML.getConnection(), sql, 14, "优乐美奶茶", 3, "c005");

        System.out.println(row+"行数据添加成功！");
    }
}

QueryRunner查询操作

package com.dbsy.test;

import com.dbsy.domain.Product;
import com.dbsy.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.sql.SQLException;
import java.util.List;

public class Demo02DbUtils {
    public static void main(String[] args) throws SQLException {
//        arrayHandler();
        
//        arrayListHandler();
        
//        beanHandler();
        
//        beanListHandler();

//        columnListHandler();
        
        scalarHandler();
    }

    private static void scalarHandler() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

        String sql="select pname from product where pid=?";
        Object query = qr.query(sql, new ScalarHandler(), 8);

        System.out.println(query);//查询唯一值    香奈儿
    }

    private static void columnListHandler() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

        String sql="select * from product";
        List<Object> objectList = qr.query(sql, new ColumnListHandler("price"));

        for (Object o : objectList) {
            System.out.println(o);//获取一列的值
                                    /*
                                    5000.0
                                    3000.0
                                    5000.0
                                    800.0
                                    200.0
                                    440.0
                                    2000.0
                                    800.0
                                    200.0
                                    5.0
                                    56.0
                                    1.0
                                     */
        }
    }

    private static void beanListHandler() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

        String sql="select * from product where pid in(?,?,?)";
        List<Product> productList = qr.query(sql, new BeanListHandler<>(Product.class), 1, 2,100);

        for (Product product : productList) {
            System.out.println(product);//Product{pid=1, pname='联想', price=5000.0, category_id='c001'}
                                        //Product{pid=2, pname='海尔', price=3000.0, category_id='c001'}
                                        //超出范围的会返回一行空值

        }
    }

    private static void beanHandler() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

        Product product = qr.query("select * from product", new BeanHandler<>(Product.class));

        System.out.println(product);//Product{pid=1, pname='联想', price=5000.0, category_id='c001'}
    }

    private static void arrayListHandler() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

        String sql="select * from product where pid in (?,?,?,?,?)";
        List<Object[]> list = qr.query(sql, new ArrayListHandler(), 6, 7, 8, 9, 10);

        for (Object[] arr : list) {
            for (Object o : arr) {
                System.out.print(o+"\t");
            }
            System.out.println();// 6	花花公子	440.0	c002
//                                  7	劲霸	2000.0	c002
//                                  8	香奈儿	800.0	c003
//                                  9	相宜本草	200.0	c003
//                                 10	面霸	5.0	c003
        }
    }

    private static void arrayHandler() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

        String sql="select * from product;";
        Object[] arr =qr.query(sql,new ArrayHandler());

        for (Object o : arr) {
            System.out.print(o+"\t");//1	联想	5000.0	c001
        }
    }
}