当前位置：首页 > news >正文

jdbc-day03

news 来源：原创 2024/9/20 2:48:40

pool_C3P0Util

package com.jdbc.pool;import com.mchange.v2.c3p0.ComboPooledDataSource;import java.sql.Connection;
import java.sql.SQLException;public class C3P0Util {//提供一个C3p0的连接池属性private static ComboPooledDataSource ds;static{//ds = new ComboPooledDataSource("c3p0-config");}public static Connection getConnection() {Connection conn = null;try {//conn = ds.getConnection();} catch (SQLException e) {throw new RuntimeException(e);}return conn;}public static void closeConnection(Connection conn) {if(conn != null){//还是归还给连接池try {conn.close();}catch (Exception e){e.printStackTrace();}}}public static void main(String[] args) {//获取连接对象Connection conn = getConnection();System.out.println(conn);//关闭连接对象closeConnection(conn);}
}

pool_C3P0UtilTest

package com.jdbc.pool;import com.youcai.emp.vo.Employee;import java.sql.*;
import java.util.ArrayList;
import java.util.List;/*** 使用C3P0Util工具查询* 部门编号为20，奖金是0的员工信息*/
public class C3P0UtilTest {public static void main(String[] args) throws SQLException {//获取连接对象Connection conn =  C3P0Util.getConnection();String sql = "select * from emp where deptno = ? and comm is ?";PreparedStatement ps = conn.prepareStatement(sql);//给问号赋值ps.setInt(1,20);ps.setString(2,null);//再次发送ResultSet set = ps.executeQuery();List<Employee> emps = new ArrayList<>();while(set.next()){int empno = set.getInt("empno");String ename = set.getString("ename");String job = set.getString("job");int mgr = set.getInt("mgr");Date hiredate = set.getDate("hiredate");double salary = set.getDouble("sal");double comm = set.getDouble("comm");int deptno = set.getInt("deptno");System.out.println(empno+","+ename+","+job+","+mgr+","+hiredate+","+salary+","+comm+","+deptno);emps.add(null);}//关闭连接，归还给连接池C3P0Util.closeConnection(conn);}
}

pool_DBCPUtil

package com.jdbc.pool;import org.apache.commons.dbcp2.BasicDataSource;import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;public class DBCPUtil {private static String driver;private static String url;private static String user;private static String password;private static int initialSize;private static int maxTotal;private static int maxIdle;private static int minIdle;private static long maxWaitMillis;//提供一个DBCP的连接池属性private  static BasicDataSource bds;static{try{//先读取配置文件InputStream inputStream = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcp.properties");Properties prop = new Properties();prop.load(inputStream);driver = prop.getProperty("driverClass");url = prop.getProperty("url");user = prop.getProperty("username");password = prop.getProperty("password");initialSize = Integer.parseInt(prop.getProperty("initialSize"));maxTotal = Integer.parseInt(prop.getProperty("maxTotal"));maxIdle = Integer.parseInt(prop.getProperty("maxIdle"));minIdle = Integer.parseInt(prop.getProperty("minIdle"));maxWaitMillis = Long.parseLong(prop.getProperty("maxWaitMillis"));//给连接池变量初始化bds = new BasicDataSource();//将各种配置参数传入连接池对象bds.setDriverClassName(driver);bds.setUrl(url);bds.setUsername(user);bds.setPassword(password);bds.setInitialSize(initialSize);bds.setMaxTotal(maxTotal);bds.setMaxIdle(maxIdle);bds.setMinIdle(minIdle);bds.setMaxWaitMillis(maxWaitMillis);}catch (Exception e){e.printStackTrace();}}/*** 获取连接* @return*/public static Connection getConnection()  {//应该从连接池中获取连接对象Connection conn = null;try {conn = bds.getConnection();} catch (SQLException e) {throw new RuntimeException(e);}return conn;}/*** 关闭连接* @param conn*/public static void closeConnection(Connection conn){if(conn!=null){try {//此时，因为conn这个对象是从连接池中获取的//所以此时的close方法，并没有真正关闭连接，而是归还给连接池conn.close();}catch (Exception e){e.printStackTrace();}}}
}

pool_DBCPUtilTest

package com.jdbc.pool;import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;/***  使用PreparedStatement向emp表中插入一条记录：*   10000,'superman','hero',7369,'2024-08-30',2000,500,40*/
public class DBCPUtilTest {public static void main(String[] args) throws SQLException {//获取连接对象Connection conn = DBCPUtil.getConnection();String sql = "insert into emp values(?,?,?,?,?,?,?,?)";PreparedStatement ps = conn.prepareStatement(sql);//给问号赋值ps.setInt(1, 10000);ps.setString(2, "superman");ps.setString(3, "hero");ps.setInt(4, 7369);ps.setDate(5, Date.valueOf("2024-08-30"));ps.setDouble(6, 2000);ps.setDouble(7, 500);ps.setInt(8, 40);//再次发送ps.executeUpdate();//关闭连接,归还给连接池DBCPUtil.closeConnection(conn);}
}

pool_DruidUtil

package com.jdbc.pool;import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;public class DruidUtil {//定义一个静态变量：关于Druid的连接池的private static DataSource ds;static{try{//先自己读取配置文件，封装到Properties对象里InputStream io = DruidUtil.class.getClassLoader().getResourceAsStream("druid.properties");Properties prop = new Properties();prop.load(io);//Druid提供了一个工厂类，里面提供了一个createDataSource(Properties prop)方法// 会自动解析prop里的各种键值对，进行赋值ds = DruidDataSourceFactory.createDataSource(prop);} catch (Exception e) {throw new RuntimeException(e);}}public static Connection getConnection(){Connection conn = null;try {conn = ds.getConnection();return conn;}catch (Exception e){e.printStackTrace();}return null;}public static void closeConnection(Connection conn){if(conn!=null){try {conn.close();}catch (Exception e){e.printStackTrace();}}}public static void main(String[] args) {Connection conn = getConnection();System.out.println(conn);closeConnection(conn);}
}

pool_DruidUtilTest

package com.jdbc.pool;import java.sql.*;/*** 将empno 为10000的这个员工，入职日期改为‘2024-09-01’,工资改为5000，奖金改为1000*/
public class DruidUtilTest {public static void main(String[] args) throws SQLException {Connection conn = DruidUtil.getConnection();String sql = "update emp set hiredate = ?, sal = ?, comm = ?  where empno = ? ";PreparedStatement ps = conn.prepareStatement(sql);ps.setDate(1, Date.valueOf("2024-09-01"));ps.setDouble(2, 5000);ps.setDouble(3, 1000);ps.setInt(4, 10000);//再次发送ps.executeUpdate();//关闭连接,归还给连接池DruidUtil.closeConnection(conn);}}

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config><default-config><property name="user">root</property><property name="password">111111</property><property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai&amp;useTimezone=true</property><property name="driverClass">com.mysql.jdbc.Driver</property><property name="acquireIncrement">10</property><property name="maxPoolSize">50</property><property name="minPoolSize">2</property><property name="initialPoolSize">5</property><property name="maxIdleTime">600</property></default-config>
</c3p0-config>

dbcp.properties

driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai&useTimezone=true
username=root
password=111111# ???????
initialSize=5
# ???????
maxTotal=50
# ???????
maxIdle=10
# ???????
minIdle=3
# ????
maxWaitMillis=60000

druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai&useTimezone=true
username=root
password=111111
initialSize=5
maxActive=50
maxIdle=10
minIdle=3
maxWait=60000