DBUtils使用demo示例

查询

package wl.jdbcDemo;

import com.sun.org.apache.bcel.internal.generic.PUSH;
import jdbcUtil.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class Demo3 {
    private static Connection con = JDBCUtils.getConnection();

    public static void main(String[] args) throws SQLException {
//        arrayHandler();
//        ArrayListHandler();
//        BeanHandler();
//        BeanListHandler();
//        ColumnListHandler();
//        ScalarHandler();
//        MapHandler();
        mapListHandler();
    }
    public static void arrayHandler()throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM SORT WHERE SID = 9000";
        Object[] results = qr.query(con,sql,new ArrayHandler());
        System.out.println(results.length);
        for (Object obj : results){
            System.out.print(obj + "\t");
        }
    }

    public static void ArrayListHandler() throws SQLException{
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM SORT";
        List<Object[]> result = qr.query(con, sql, new ArrayListHandler());
        System.out.println(result);
        for(Object[] objs: result){
            for (Object obj : objs){
                System.out.print(obj + "\t");

            }
            System.out.println();
        }
    }

    public static void BeanHandler() throws SQLException{
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM SORT";
        Sort s = qr.query(con, sql, new BeanHandler<Sort>(Sort.class));
        System.out.println(s);

    }

    public static void BeanListHandler() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select * from sort";
        List<Sort> list = qr.query(con, sql, new BeanListHandler<Sort>(Sort.class));
        for(Sort s  : list){
            System.out.println(s);
        }
    }

    public static void ColumnListHandler() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select sname,sprice  from sort";
        List<Object> objs = qr.query(con, sql, new ColumnListHandler<Object>("sname"));
        for (Object obj : objs){
            System.out.println(obj);
        }
    }

    public static void ScalarHandler() throws SQLException{
        QueryRunner qr = new QueryRunner();
        String sql = "select count(*) from sort";
        long num = qr.query(con, sql, new ScalarHandler<Long>());
        System.out.println(num);
    }

    public static void MapHandler() throws SQLException{
        QueryRunner qr = new QueryRunner();
        String sql = "select * from sort";
        Map<String, Object> map = qr.query(con, sql, new MapHandler());
        for(String key : map.keySet()){
            System.out.println(key + "  "  + map.get(key));
        }
    }

    public static void mapListHandler() throws SQLException{
        QueryRunner qr = new QueryRunner();
        String sql = "select * from sort";
        List<Map<String, Object>> list =  qr.query(con, sql, new MapListHandler());
        for (Map<String, Object> map : list){
            System.out.println("###" + map.getClass());
            for (String key : map.keySet()){
                System.out.print(key + "  " + map.get(key));
            }
            System.out.println();
        }
    }
}

修改，增加，删除

package dbUtils;

import jdbcUtil.JDBCUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;

import java.sql.Connection;
import java.sql.SQLException;

public class DBUtilsDemo {
    private static Connection con = JDBCUtils.getConnection();

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

    public static void insert() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "insert into sort (sname,sprice,sdesc) values(?,?,?)";
        Object[] objs = new Object[]{"体育用品",268.33,"购买体育用品"};
        int result = qr.update(con,sql,objs);
        System.out.println(result);
        DbUtils.closeQuietly(con);
    }

    public static void update() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql= "update sort set sname=?,sprice=?,sdesc=? where sid=?";
        Object[] objs = new Object[]{"新家电",3000,"新型家电",1};
        int row = qr.update(con,sql,objs);
        System.out.println(row);
        DbUtils.closeQuietly(con);
    }

    public static void delete() throws SQLException{
        QueryRunner qr = new QueryRunner();
        int row = qr.update(con,"DELETE FROM SORT WHERE SID =?",1);
        System.out.println(row);
        DbUtils.closeQuietly(con);
    }
}

获取数据库连接对象的demo

package jdbcUtil;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    private JDBCUtils(){}
    private static Connection con;
    static {
        try {
            /*FileInputStream fis  = new FileInputStream("src/database.properties");
            System.out.println(fis);*/
            ClassLoader cl = JDBCUtils.class.getClassLoader();
            InputStream is = cl.getResourceAsStream("database.properties");
            System.out.println(is);
            Properties pro = new Properties();
            pro.load(is);
            /*String driverClass = pro.getProperty("driverClass");
            String url = pro.getProperty("url");
            String username = pro.getProperty("username");
            String password = pro.getProperty("password");*/
            Class.forName("com.mysql.jdbc.Connection");
            String url = "jdbc:mysql://localhost:3306/shop?serverTimezone=GMT";
            String username = "root";
            String password = "admin";
            con = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e + "数据库连接失败了");
        }
    }
    /*
    * 定义静态方法 返回数据库连接对象
    * */
    public static Connection getConnection(){
        return con;
    }

    public static void close(Connection con, Statement statement, ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            }catch (Exception e){

            }
        }
        if(statement != null){
            try {
                statement.close();
            }catch (Exception e){

            }
        }
        if(con != null){
            try {
                con.close();
            }catch (Exception e){

            }
        }
    }
    public static void close(Connection con, Statement statement){
        if(statement != null){
            try {
                statement.close();
            }catch (Exception e){

            }
        }
        if(con != null){
            try {
                con.close();
            }catch (Exception e){

            }
        }
    }
}