* JDBC的基本使用
public class sample {
public static final String url = "jdbc:mysql://localhost:3306/bright?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
public static final String root = "root";
public static final String password = "AI123456";
private static Connection conn = null;
private static PreparedStatement psmt = null;
public static void main(String[] args) {
// 加载并注册JDBC驱动
try {
Class.forName("com.mysql.cj.jdbc.Driver");
// 创建数据库连接
conn = DriverManager.getConnection(url, root, password);
String sql = "select * from employee";
psmt = conn.prepareStatement(sql);
ResultSet rs = psmt.executeQuery();
while (rs.next()) {
Integer eno = rs.getInt("eno");
String ename = rs.getString("ename");
String salary = rs.getString("salary");
String dname = rs.getString("dname");
String hiredate = rs.getString("hiredate");
System.out.println(eno + " " + ename + " " + salary + " " + dname + " " + hiredate);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
* DbUtils工具类
- getConnection()：获取连接
- closeConnection：释放资源
package com.imooc.jdbc.common;
import java.sql.*;
public class DbUtils {
/**
* 创建新的数据库连接
* @return 新的Connection对象
* @throws SQLException
* @throws ClassNotFoundException
*/
public static Connection getConnection() throws SQLException, ClassNotFoundException {
//1. 加载并注册JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 创建数据库连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "AI123456");
return conn;
}
/**
* 关闭连接,释放资源
* @param rs 结果集对象
* @param stmt Statement对象
* @param conn Connection对象
*/
public static void closeConnection(ResultSet rs , Statement stmt , Connection conn){
try {
if(rs != null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(stmt != null){
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null && !conn.isClosed() ) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
* 事务机制案例
- conn.setAutoCommit(false)：设置手动提交事务
- conn.commit()：提交事务
- conn.rollback()：出现异常或者意外进行事务回滚
// 拿到连接
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtils.getConnection();
conn.setAutoCommit(false); // 设置手动提交事务
// 对Sql预编译
pstmt = conn.prepareStatement("INSERT INTO employee VALUES(?,?,?,?,?)");
for(int i = 1000; i < 2000; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "员工" + i);
pstmt.setFloat(3, 4000f);
pstmt.setString(4, "市场部");
pstmt.setDate(5, Date.valueOf("1981-10-19"));
pstmt.executeUpdate(); // 更新操作
}
conn.commit(); // 提交事务
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
if(conn != null && !conn.isClosed()) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} catch (Exception e) {
try {
if(conn != null && !conn.isClosed()) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
DbUtils.closeConnection(null, pstmt, conn);
}
* 分页
- 分页关键sql
- String sql = “SELECT * FROM employee limit ?, 10”;
// 对Sql预编译
pstmt = conn.prepareStatement(sql);
*pstmt.setInt(1,(page-1)10);
- String sql = “SELECT * FROM employee limit ?, 10”;
-
System.out.println("请输入页号："); int page = new Scanner(System.in).nextInt(); Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; List<Employee> empList = new ArrayList<>(); try { // 获取连接 try { conn = DbUtils.getConnection(); } catch (SQLException throwables) { throwables.printStackTrace(); } String sql = "SELECT * FROM employee limit ?, 10"; // 对Sql预编译 pstmt = conn.prepareStatement(sql); ******pstmt.setInt(1,(page-1)*10); rs = pstmt.executeQuery(); while (rs.next()) { Integer eno = rs.getInt("eno"); String ename = rs.getString("ename"); Float salary = rs.getFloat("salary"); String dname = rs.getString("dname"); Date hiredate = rs.getDate("hiredate"); Employee emp = new Employee(); emp.setEno(eno); emp.setEname(ename); emp.setSalary(salary); emp.setDname(dname); emp.setHiredate(hiredate); empList.add(emp); } for(Employee e : empList) { System.out.println(e.getEno() + " " + e.getEname() + " " + e.getSalary() + " " + e.getDname() + " " + e.getHiredate()); } /* 迭代的方式遍历 Iterator<Employee> iterator = empList.iterator(); while(iterator.hasNext()) { Employee e = iterator.next(); System.out.println(e.getEno() + " " + e.getEname() + " " + e.getSalary() + " " + e.getDname() + " " + e.getHiredate()); }*/ } catch (Exception e) { e.printStackTrace(); } finally { DbUtils.closeConnection(rs, pstmt, conn); }
* String 转为java.sql.Date（易错点）
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String strDate = "2021-6-21";
// 将日期字符串转为java.util.Date
java.util.Date utilDate = sdf.parse(strDate);
// 将java.util.Date转为java.sql.Date
long time = utilDate.getTime(); // 获取时间戳
java.sql.Date sqlDate = new java.sql.Date(time);
System.out.println(sqlDate); // 2021-06-21
* 批处理（提交批量处理数据的速度）
- pstmt.addBatch() 每一次循环不执行Sql
- pstmt.executeBatch()：循环结束后统一执行批处理任务
// 拿到连接
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtils.getConnection();
conn.setAutoCommit(false); // 设置手动提交事务
// 对Sql预编译
long start = new java.util.Date().getTime();
pstmt = conn.prepareStatement("INSERT INTO employee VALUES(?,?,?,?,?)");
for(int i = 1000000; i < 2000000; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "员工" + i);
pstmt.setFloat(3, 4000f);
pstmt.setString(4, "市场部");
pstmt.setDate(5, Date.valueOf("1981-10-19"));
******pstmt.addBatch(); // 将参数加入批处理
// pstmt.executeUpdate(); // 更新操作
}
******pstmt.executeBatch(); // 执行批处理任务
conn.commit(); // 提交事务
long end = new java.util.Date().getTime();
System.out.println("total=" + (end - start) + "ms");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
if(conn != null && !conn.isClosed()) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} catch (Exception e) {
try {
if(conn != null && !conn.isClosed()) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
DbUtils.closeConnection(null, pstmt, conn);
}
* Druid 连接池
-
介绍
- Druid 是阿里巴巴开源连接池组件，是最好的连接池之一
- Druid 对数据库连接进行有效管理与重用，最大化程序执行效率
- 在应用程序启动后初始化，连接池负责创建管理连接，程序只负责取用与归还
-
Druid 连接池的配置与使用
- 先去https://github.com/alibaba/druid/releases上下载druid.jar 并将jar导入工程中
// 创建配置文件druid-config.properties driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/bright?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai username=root password=AI123456 # 推荐initial和maxActive设置一样 initialSize=20 maxActive=20 // 基本使用 Properties prop = new Properties(); // 获取到配置文件路径 String propFile = DruidDemo.class.getResource("/druid-config.properties").getPath(); Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { // 加强容错能力, 若路径遇到空格会变成%20 propFile = new URLDecoder().decode(propFile, "UTF-8"); prop.load(new FileInputStream(propFile)); // 获取DataSource数据源对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); // 拿到连接 conn = dataSource.getConnection(); String sql = "SELECT * FROM employee limit ?, 10"; // 对Sql预编译 pstmt = conn.prepareStatement(sql); pstmt.setInt(1, 0); rs = pstmt.executeQuery(); List<Employee> empList = new ArrayList<>(); while (rs.next()) { Integer eno = rs.getInt("eno"); String ename = rs.getString("ename"); Float salary = rs.getFloat("salary"); String dname = rs.getString("dname"); Date hiredate = rs.getDate("hiredate"); Employee emp = new Employee(); emp.setEno(eno); emp.setEname(ename); emp.setSalary(salary); emp.setDname(dname); emp.setHiredate(hiredate); empList.add(emp); } for(Employee e : empList) { System.out.println(e.getEno() + " " + e.getEname() + " " + e.getSalary() + " " + e.getDname() + " " + e.getHiredate()); } } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { // 回收到连接池中，并不是销毁连接 DbUtils.closeConnection(rs, pstmt, conn); }
* C3P0 连接池
- 准备jar包
- C3P0官网下载jar包
- 在工程中引入 c3p0-0.9.5.5.jar 和 mchange-commons-java-0.2.19.jar（所依赖的包）
- 创建配置文件c3p0-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/bright?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai</property>
<property name="user">root</property>
<property name="password">AI123456</property>
<!-- 连接池初始连接数量 -->
<property name="initialPoolSize">10</property>
<!-- 最大连接数量 -->
<property name="maxPoolSize">15</property>
</default-config>
</c3p0-config>
- 使用
// 加载配置文件,创建DataSource
DataSource dataSource = new ComboPooledDataSource();
// 得到数据库连接
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
String sql = "SELECT * FROM employee limit ?, 10";
// 对Sql预编译
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 0);
rs = pstmt.executeQuery();
List<Employee> empList = new ArrayList<>();
while (rs.next()) {
Integer eno = rs.getInt("eno");
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
Date hiredate = rs.getDate("hiredate");
Employee emp = new Employee();
emp.setEno(eno);
emp.setEname(ename);
emp.setSalary(salary);
emp.setDname(dname);
emp.setHiredate(hiredate);
empList.add(emp);
}
for(Employee e : empList) {
System.out.println(e.getEno() + " " + e.getEname() + " " + e.getSalary() +
" " + e.getDname() + " " + e.getHiredate());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 回收到连接池中，并不是销毁连接
DbUtils.closeConnection(rs, pstmt, conn);
}
* Apache Commins DBUtils
-
介绍
- commons-dbutils是Apache提供的开源JDBC工具类库
- 它是对JDBC的简单封装，学习成本极低
- 使用commons-dbutils可以极大的简化JDBC编码工作量
-
使用
- 在工程目录中导入commons-dbutils-1.7.jar
// 查询 Properties prop = new Properties(); String propFile = DbutilsDemo.class.getResource("/druid-config.properties").getPath(); try { propFile = new URLDecoder().decode(propFile, "UTF-8"); prop.load(new FileInputStream(propFile)); DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); // 将dataSource交给QueryRunner QueryRunner qr = new QueryRunner(dataSource); /* 执行查询, 自动获取连接查询并返回结果集并归还连接 第一个参数是sql，第二个参数是将结果集放在List并指定泛型 第三个参数是对？占位符进行填充 */ List<Employee> empList = qr.query("SELECT * FROM employee LIMIT ?, 10", new BeanListHandler<>(Employee.class), new Object[]{0}); for(Employee e : empList) { System.out.println(e.getEname()); } } catch (Exception e) { e.printStackTrace(); } // 更新 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); conn = dataSource.getConnection(); conn.setAutoCommit(false); // 设置手动提交事务 String sql1 = "UPDATE employee SET salary=salary+1000 where eno=?"; String sql2 = "UPDATE employee SET salary=salary-500 where eno=?"; QueryRunner qr = new QueryRunner(); qr.update(conn, sql1, new Object[]{3308}); qr.update(conn, sql2, new Object[]{3420}); conn.commit(); // 提交事务 // 若遇到异常则执行conn.rollback() // 最后在finally中进行 conn.close() 对连接进行回收
683
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
