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工具方法实现查询操作
