Day0303____DbUtil

 

 

 

orcale

查询框架

BeanListHandler
BeanHandler

https://blog.csdn.net/a911711054/article/details/77719685

导入相关jar包 

        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
        </dependency>
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/commons-beanutils/commons-beanutils -->
        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils</artifactId>
            <version>1.9.3</version>
        </dependency>

原始类方法

package com_01_testfan_01_basic;


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

import java.util.UUID;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Day030302_数据库_原始代码_老师数据库 {

    public static final String URL = "jdbc:mysql://118.24.13.38:3308/goods?characterEncoding=utf8&useSSL=false";
    public static final String USER = "zhangsan";
    public static final String PASSWORD = "123123";

    private static Connection getConn() {
//        // 1 获取驱动
//        try {
//            Class.forName("com.mysql.jdbc.Driver");
//            // 2.获得数据库连接
//            Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//            return conn;
//        } catch (ClassNotFoundException | SQLException e) {
//            e.printStackTrace();
//        }
//        return null;
        
        //连接池改造
        try {
            ComboPooledDataSource ds = new ComboPooledDataSource();
            return ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void add(String loginname, String loginpass) {
        System.out.println("add " + loginname + " " + loginpass);
        Connection conn = getConn();
        // sql
        String sql = "INSERT INTO t_user_test(uid, loginname, loginpass) values(?,?,?)";
        // 预编译
        try {
            // 预编译SQL，减少sql执行
            PreparedStatement ptmt = conn.prepareStatement(sql);
            ptmt.setString(1, UUID.randomUUID().toString());
            ptmt.setString(2, loginname);
            ptmt.setString(3, loginpass);
            // 执行
            ptmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    public static void delete() {
        System.out.println("delete ");
         //获取连接
        Connection conn = getConn();
        //sql, 每行加空格
        String sql = "delete from t_user_test";
        //预编译SQL，减少sql执行
        PreparedStatement ptmt;
        try {
            ptmt = conn.prepareStatement(sql);
             //执行
            ptmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

       
    }

    public static void main(String[] args) {
        delete();
        for (int i = 0; i < 10000; i++) {
            Day030302_数据库_原始代码_老师数据库.add("test" + i, "pass" + i);
        }

    }

}

Dbutils工具方法

package com_01_testfan_01_basic;

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

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.testfan.db.DbUser;

public class Day030401_数据库_常规_工具类_老师数据库 {

    public static void main(String[] args) throws Exception{
            //delete();
            //update();
            //query();
            for(int i=0; i<1000; i++) {
                add();
                //System.out.println();
            }

        
//        test("1","2");
//        test();
//        test(new String[] {"1","1"});
    }
    
    
    private static void add() throws SQLException {
        //ComboPooledDataSource ds = new ComboPooledDataSource("mysql_lige");
        // dbutis使用数据源
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        //QueryRunner runner = new QueryRunner(ds);
        // 可变变量  无限 也可以没有 也可以数组
        Object[] objects= new Object[] {"123_test","333_test", "14ba4bd0-a0da-4a2c-b136-de036b54e98a"};
        //runner.update("INSERT t_user_test3 VALUES(?, ?, ?);", "123_dbutils","123_dbutils","14ba4bd0-a0da-4a2c-b136-de036b54e98a");
        runner.update("INSERT t_user_test3 VALUES(?, ?, ?);", objects);
    }
    
    private static void query() {
        // 数据的管理
        ComboPooledDataSource ds = new ComboPooledDataSource("mysql_lige");
        // dbutis使用数据源
        QueryRunner runner = new QueryRunner(ds);
        String sql = "select * from t_user_test3";
        try {
            // 反射
            List<DbUser> list = (List<DbUser>) runner.query(sql, new BeanListHandler(DbUser.class));
            // 删除，修改，add
            System.out.println(list.size());
            for (DbUser object : list) {
                //System.out.println(list);
                //System.out.println(object.g);
                System.out.println(object.getUid());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void update() throws SQLException {
        ComboPooledDataSource ds = new ComboPooledDataSource();
        // dbutis使用数据源
        //QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        QueryRunner runner = new QueryRunner();
        // 可变变量  无限 也可以没有 也可以数组
        Object[] objects= new Object[] {"123_test","333_test", "14ba4bd0-a0da-4a2c-b136-de036b54e98a"};
        runner.update("update t_user_test set loginname=?,loginpass=? where uid=?", "123_dbutils","123_dbutils","14ba4bd0-a0da-4a2c-b136-de036b54e98a");
        //runner.update("update t_user_test set loginname=?,loginpass=? where uid=?", objects);
        //runner.update("delete from t_user_test");
    }
    

    
    private static void delete() throws SQLException {
        ComboPooledDataSource ds = new ComboPooledDataSource();
        // dbutis使用数据源
        //QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        QueryRunner runner = new QueryRunner(ds);
        // 可变变量  无限 也可以没有 也可以数组
        //Object[] objects= new Object[] {"123_test","333_test", "14ba4bd0-a0da-4a2c-b136-de036b54e98a"};
        //runner.update("update t_user_test set loginname=?,loginpass=? where uid=?", "123_dbutils","123_dbutils","14ba4bd0-a0da-4a2c-b136-de036b54e98a");
        //runner.update("update t_user_test set loginname=?,loginpass=? where uid=?", objects);
        runner.update("delete from t_user_test3");
    }
    
    
    
    private static void test(String...o) {
        for (String string : o) {
            System.out.println(string);
        }
    }

}

 将数据源进行单独封装，一次只用调用一次

package com_01_testfan_01_basic;

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

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCUtils {

	// 获得c3p0连接池对象
	private static ComboPooledDataSource ds = new ComboPooledDataSource("mysql_lige");
	
//	private static ComboPooledDataSource ds = new ComboPooledDataSource();

	/**
	 * 获得数据库连接对象
	 *
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		return ds.getConnection();
	}

	/**
	 * 获得c3p0连接池对象
	 * 
	 * @return
	 */
	public static DataSource getDataSource() {
		return ds;
	}

	public static void main(String[] args) {
		System.out.println("----------" + JDBCUtils.getDataSource());
	}
}

　　

Dbutils工具方法实现查询操作