Java入坑之 数据库编程

r囧r小猫 2024-04-17 11:48 123阅读 0赞

一、基础概念

1.1JDBC 步骤

  1. 导入驱动jar
  2. 注册驱动
  3. 获取数据库连接对象 Connection
  4. DataSource dSource;
  5. dSource.getConnection();
  6. 定义sql语句
  7. String sql = "update account set balance = 500 where id = 1";
  8. 获取执行sql语句的对象 Statement
  9. PreparedStatement stmt = conn. prepareStatement(sql);
  10. 执行sql,接受返回结果
  11. int count = stmt.executeUpdate(sql);
  12. 处理结果 System.out.println(count);
  13. 释放资源 stmt.close(); conn.close();

1c7382ce42f44bef818f648ff820fdc3.png

1.2相关类介绍

7c5b2229fdf64e5f90fa712214604738.png

1.3相关代码

022447da47b74c12824cfc17246ce2e7.png

在Maven中声明MySQL的JDBC实现依赖

0b78545b38854b838b9c52ffee35e572.png

1.3.1查询

  1. package org.example;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. public class SelectTest {
  8. public static void main(String[] args) {
  9. // 注册 MySQL 驱动
  10. try {
  11. Class.forName("com.mysql.cj.jdbc.Driver");
  12. System.out.println("注册驱动成功!");
  13. } catch (ClassNotFoundException e1) {
  14. System.out.println("注册驱动失败!");
  15. e1.printStackTrace();
  16. return;
  17. }
  18. String url = "jdbc:mysql://localhost:3306/t_book?serverTimezone=UTC";
  19. Connection conn = null;
  20. try {
  21. // 构建 Java 和数据库之间的桥梁:URL,用户名,密码
  22. conn = DriverManager.getConnection(url, "root", "b123456");
  23. System.out.println("连接数据库成功!");
  24. // 构建数据库执行者
  25. Statement stmt = conn.createStatement();
  26. System.out.println("创建 Statement 成功!");
  27. // 执行 SQL 语句并返回结果到 ResultSet
  28. ResultSet rs = stmt.executeQuery("select bookid, bookname, price from book order by bookid");
  29. // 开始遍历 ResultSet 数据
  30. while (rs.next()) {
  31. System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));
  32. }
  33. rs.close();
  34. stmt.close();
  35. } catch (SQLException e) {
  36. e.printStackTrace();
  37. } finally {
  38. try {
  39. if (conn != null) {
  40. conn.close();
  41. System.out.println("关闭数据库连接成功!");
  42. }
  43. } catch (SQLException e) {
  44. e.printStackTrace();
  45. }
  46. }
  47. }
  48. }

1.3.2增删改

  1. package org.example;
  2. import java.sql.*;
  3. public class UpdateTest {
  4. public static void main(String[] args) {
  5. executeUpdate("update book set price = 300 where bookid = 1");
  6. executeUpdate("insert into book(bookid, bookname, price) values(4, '编译原理', 90)");
  7. executeUpdate("delete from book where bookid = 4");
  8. }
  9. /**
  10. * 执行数据更新操作
  11. * @param sql 要执行的 SQL 语句
  12. */
  13. public static void executeUpdate(String sql) {
  14. // 注册 MySQL 驱动
  15. try {
  16. Class.forName("com.mysql.cj.jdbc.Driver");
  17. System.out.println("注册驱动成功!");
  18. } catch (ClassNotFoundException e1) {
  19. System.out.println("注册驱动失败!");
  20. e1.printStackTrace();
  21. }
  22. String url = "jdbc:mysql://localhost:3306/t_book?serverTimezone=UTC";
  23. Connection conn = null;
  24. try {
  25. // 构建 Java 和数据库之间的桥梁:URL,用户名,密码
  26. conn = DriverManager.getConnection(url, "root", "b123456");
  27. System.out.println("连接数据库成功!");
  28. // 构建数据库执行者
  29. Statement stmt = conn.createStatement();
  30. System.out.println("创建 Statement 成功!");
  31. int rs = stmt.executeUpdate(sql);
  32. stmt.close();
  33. } catch (SQLException e) {
  34. e.printStackTrace();
  35. } finally {
  36. try {
  37. if (conn != null) {
  38. conn.close();
  39. System.out.println("关闭数据库连接成功!");
  40. }
  41. } catch (SQLException e) {
  42. e.printStackTrace();
  43. }
  44. }
  45. }
  46. }

1.3.3事务逻辑

74a026cfebac44ddb47a4586925c010d.png

" class="reference-link">052bab90a74f4355b4960b52fae15997.png

  1. package org.example;
  2. import java.sql.*;
  3. public class TransactionTest {
  4. public static void main(String[] args) throws Exception {
  5. // 构建Java和数据库之间的桥梁介质
  6. try {
  7. Class.forName("com.mysql.cj.jdbc.Driver");
  8. System.out.println("注册驱动成功!");
  9. } catch (ClassNotFoundException e1) {
  10. System.out.println("注册驱动失败!");
  11. e1.printStackTrace();
  12. }
  13. String url = "jdbc:mysql://localhost:3306/t_book?serverTimezone=UTC";
  14. Connection conn = null;
  15. try {
  16. // 构建Java和数据库之间的桥梁:URL,用户名,密码
  17. conn = DriverManager.getConnection(url, "root", "b123456");
  18. conn.setAutoCommit(false);
  19. insertBook(conn, "insert into book values(101, 'aaaa', 10)");
  20. insertBook(conn, "insert into book values(102, 'bbbb', 10)");
  21. insertBook(conn, "insert into book values(103, 'cccc', 10)");
  22. Savepoint phase1 = conn.setSavepoint(); // 设置一个保存点
  23. insertBook(conn, "insert into book values(104, 'cccc', 10)");
  24. insertBook(conn, "insert into book values(105, 'cccc', 10)");
  25. conn.rollback(phase1); // 回滚到phase1保存点,即上面2行无效
  26. conn.commit();
  27. System.out.println("操作成功");
  28. } catch (SQLException e) {
  29. e.printStackTrace();
  30. conn.rollback();
  31. } finally {
  32. if (null != conn) {
  33. conn.close();
  34. }
  35. }
  36. }
  37. public static void insertBook(Connection conn, String sql) {
  38. try {
  39. // 构建数据库执行者
  40. Statement stmt = conn.createStatement();
  41. // 执行SQL语句
  42. int result = stmt.executeUpdate(sql);
  43. stmt.close();
  44. } catch (SQLException e) {
  45. e.printStackTrace();
  46. }
  47. }
  48. }

1.3.4拼接字符串

297d3f7d78a74646b8c2fd67e9b8c985.png

98355f83e87f4b89a3c67d4d33b1d491.png

  1. package org.example;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.SQLException;
  5. import java.sql.Statement;
  6. public class InsertTest {
  7. public static void main(String[] a) {
  8. //concatInsertBook();
  9. unsafeConcatInsertBook();
  10. }
  11. public static void concatInsertBook() {
  12. // 构建Java和数据库之间的桥梁介质
  13. try {
  14. Class.forName("com.mysql.cj.jdbc.Driver");
  15. System.out.println("注册驱动成功!");
  16. } catch (ClassNotFoundException e1) {
  17. System.out.println("注册驱动失败!");
  18. e1.printStackTrace();
  19. }
  20. String url = "jdbc:mysql://localhost:3306/t_book?serverTimezone=UTC";
  21. Connection conn = null;
  22. try {
  23. // 构建Java和数据库之间的桥梁:URL,用户名,密码
  24. conn = DriverManager.getConnection(url, "root", "b123456");
  25. // 构建数据库执行者
  26. Statement stmt = conn.createStatement();
  27. System.out.println("创建Statement成功!");
  28. // 执行SQL语句
  29. int bookid = 11;
  30. String bookName = "Effective Java";
  31. int price = 50;
  32. String sql = "insert into book(bookid,bookname,price) values("
  33. + bookid + ", '" + bookName + "', " + price + ")";
  34. int result = stmt.executeUpdate(sql);
  35. stmt.close();
  36. System.out.println("操作成功");
  37. } catch (SQLException e) {
  38. e.printStackTrace();
  39. } finally {
  40. try {
  41. if (conn != null) {
  42. conn.close();
  43. }
  44. } catch (SQLException e) {
  45. e.printStackTrace();
  46. }
  47. }
  48. }
  49. public static void unsafeConcatInsertBook() {
  50. // 构建Java和数据库之间的桥梁介质
  51. try {
  52. Class.forName("com.mysql.cj.jdbc.Driver");
  53. System.out.println("注册驱动成功!");
  54. } catch (ClassNotFoundException e1) {
  55. System.out.println("注册驱动失败!");
  56. e1.printStackTrace();
  57. }
  58. String url = "jdbc:mysql://localhost:3306/t_book?serverTimezone=UTC";
  59. Connection conn = null;
  60. try {
  61. // 构建Java和数据库之间的桥梁:URL,用户名,密码
  62. conn = DriverManager.getConnection(url, "root", "b123456");
  63. // 构建数据库执行者
  64. Statement stmt = conn.createStatement();
  65. System.out.println("创建Statement成功!");
  66. // 执行SQL语句
  67. int bookid = 13;
  68. String bookName = "Effective Java',50);delete from t_book;insert into t_book values(101, 'faked book";
  69. int price = 50;
  70. String sql = "insert into book(bookid,bookname,price) values("
  71. + bookid + ", '" + bookName + "', " + price + ");";
  72. System.out.println(sql);
  73. int result = stmt.executeUpdate(sql);
  74. stmt.close();
  75. System.out.println("操作成功");
  76. } catch (SQLException e) {
  77. e.printStackTrace();
  78. } finally {
  79. try {
  80. if (conn != null) {
  81. conn.close();
  82. }
  83. } catch (SQLException e) {
  84. e.printStackTrace();
  85. }
  86. }
  87. }
  88. }

1.3.5batch函数

  1. package org.example;
  2. import java.sql.*;
  3. public class InsertTest1 {
  4. public static void main(String[] a) {
  5. safeInsertBook();
  6. batchInsertBook();
  7. }
  8. public static void safeInsertBook() {
  9. Connection conn = null;
  10. try {
  11. // 注册 MySQL 驱动
  12. Class.forName("com.mysql.jdbc.Driver");
  13. System.out.println("注册驱动成功!");
  14. } catch (ClassNotFoundException e1) {
  15. System.out.println("注册驱动失败!");
  16. e1.printStackTrace();
  17. }
  18. String url = "jdbc:mysql://localhost:3306/t_book?serverTimezone=UTC";
  19. try {
  20. // 构建Java和数据库之间的桥梁:URL,用户名,密码
  21. conn = DriverManager.getConnection(url, "root", "b123456");
  22. String sql = "insert into book(bookid,bookname,price) values (?, ?, ?)";
  23. // 构建数据库执行者
  24. PreparedStatement pstmt = conn.prepareStatement(sql);
  25. // 设置参数
  26. int bookid = 15;
  27. String bookName = "Effective Java',50);delete from t_book;insert into t_book values(101, 'faked book";
  28. int price = 50;
  29. pstmt.setInt(1, bookid);
  30. pstmt.setString(2, bookName);
  31. pstmt.setInt(3, price);
  32. // 执行SQL语句
  33. int result = pstmt.executeUpdate();
  34. pstmt.close();
  35. System.out.println("操作成功");
  36. } catch (SQLException e) {
  37. e.printStackTrace();
  38. } finally {
  39. try {
  40. if (conn != null) {
  41. conn.close();
  42. System.out.println("关闭数据库连接成功!");
  43. }
  44. } catch (SQLException e) {
  45. e.printStackTrace();
  46. }
  47. }
  48. }
  49. public static void batchInsertBook() {
  50. Connection conn = null;
  51. try {
  52. // 注册 MySQL 驱动
  53. Class.forName("com.mysql.jdbc.Driver");
  54. System.out.println("注册驱动成功!");
  55. } catch (ClassNotFoundException e1) {
  56. System.out.println("注册驱动失败!");
  57. e1.printStackTrace();
  58. }
  59. String url = "jdbc:mysql://localhost:3306/t_book?serverTimezone=UTC";
  60. try {
  61. // 构建Java和数据库之间的桥梁:URL,用户名,密码
  62. conn = DriverManager.getConnection(url, "root", "b123456");
  63. String sql = "insert into book(bookid,bookname,price) values (?,?,?)";
  64. // 构建数据库执行者
  65. PreparedStatement pstmt = conn.prepareStatement(sql);
  66. // 设置参数
  67. String bookName = "aaaaaaaaaaaaaaaa";
  68. int price;
  69. // 执行批量插入操作
  70. for (int i = 210; i < 202; i++) {
  71. price = 50;
  72. pstmt.setInt(1, i);
  73. pstmt.setString(2, bookName);
  74. pstmt.setInt(3, price);
  75. pstmt.addBatch();
  76. }
  77. pstmt.executeBatch();
  78. pstmt.close();
  79. System.out.println("操作成功");
  80. } catch (SQLException e) {
  81. e.printStackTrace();
  82. } finally {
  83. try {
  84. if (conn != null) {
  85. conn.close();
  86. System.out.println("关闭数据库连接成功!");
  87. }
  88. } catch (SQLException e) {
  89. e.printStackTrace();
  90. }
  91. }
  92. }
  93. }

如果有大量的sq语句,它们结构相同,仅仅差别在具体数值上,那么可以通过addBatch
方法进行批量操作。这样会提高性能,减少数据库负担。

1.3.6ResultSetMetaData类

b4461c8313a0439cba88cccd6a907f47.png

  1. package org.example;
  2. import java.sql.*;
  3. public class ResultSetMetaDataTest {
  4. public static void main(String[] args) {
  5. // 构建Java和数据库之间的桥梁介质
  6. try {
  7. Class.forName("com.mysql.cj.jdbc.Driver");
  8. System.out.println("注册驱动成功!");
  9. } catch (ClassNotFoundException e1) {
  10. System.out.println("注册驱动失败!");
  11. e1.printStackTrace();
  12. return;
  13. }
  14. String url = "jdbc:mysql://localhost:3306/t_book?serverTimezone=UTC";
  15. Connection conn = null;
  16. try {
  17. // 构建Java和数据库之间的桥梁:URL,用户名,密码
  18. conn = DriverManager.getConnection(url, "root", "b123456");
  19. // 构建数据库执行者
  20. Statement stmt = conn.createStatement();
  21. System.out.println("创建Statement成功!");
  22. // 执行SQL语句并返回结果到ResultSet
  23. ResultSet rs = stmt.executeQuery("select bookid, bookname, price from book order by bookid");
  24. // 获取结果集的元数据
  25. ResultSetMetaData meta = rs.getMetaData();
  26. int cols = meta.getColumnCount();
  27. for (int i = 1; i <= cols; i++) {
  28. System.out.println(meta.getColumnName(i) + "," + meta.getColumnTypeName(i));
  29. }
  30. rs.close();
  31. stmt.close();
  32. } catch (SQLException e) {
  33. e.printStackTrace();
  34. } finally {
  35. try {
  36. if (conn != null) {
  37. conn.close();
  38. }
  39. } catch (SQLException e) {
  40. e.printStackTrace();
  41. }
  42. }
  43. }
  44. }

二、数据库连接池

2.1流程重述

f704166ab8ee4052bfc6f062dfdebe73.png

2.2亨元模式

52e9cad2b8a04842be8bcc3ab5e47f46.png

享元模式, Flyweight Pattern
- 经典23个设计模式的一种,属于结构型模式。
- 一个系统中存在大量的相同的对象,由于这类对象的大量使用,会造成系统内存的耗费,可以使用享元模式来减少系统中对象的数量。

2.3 数据库连接池概念

ec18a43628f74f438052c89cbbc92dfc.png

2.4C3P0连接池

2.4.1配置文件

  1. <!-- https://mvnrepository.com/artifact/com.mchange/c3pθ -->
  2. <dependency>
  3. <groupId>com.mchange</groupId>
  4. <artifactId>c3p0</artifactId>
  5. <version>0.9.5.2</version>
  6. </dependency>
  7. <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
  8. <dependency>
  9. <groupId>com.alibaba</groupId>
  10. <artifactId>druid</artifactId>
  11. <version>1.1.10</version>
  12. </dependency>

2.4.2相关代码

  1. package org.example;
  2. import java.sql.Connection;
  3. import java.sql.ResultSet;
  4. import java.sql.SQLException;
  5. import java.sql.Statement;
  6. public class SelectTest1 {
  7. public static void main(String[] args) {
  8. Connection conn = null;
  9. try {
  10. // 从c3p0获取数据库连接
  11. conn = C3p0Factory1.getConnection();
  12. //conn = C3p0Factory2.getConnection();
  13. Druid获取
  14. conn = DruidFactory1.getConnection();
  15. //conn = DruidFactory2.getConnection();
  16. // 构建数据库执行者
  17. Statement stmt = conn.createStatement();
  18. System.out.println("创建Statement成功!");
  19. // 执行SQL语句并返回结果到ResultSet
  20. ResultSet rs = stmt.executeQuery("select bookid, bookname, price from book order by bookid");
  21. // 开始遍历ResultSet数据
  22. while (rs.next()) {
  23. System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));
  24. }
  25. rs.close();
  26. stmt.close();
  27. } catch (Exception e) {
  28. e.printStackTrace();
  29. } finally {
  30. try {
  31. if (conn != null) {
  32. conn.close();
  33. }
  34. } catch (SQLException e) {
  35. e.printStackTrace();
  36. }
  37. }
  38. }
  39. }

2.4.3相关配置

" class="reference-link">e7996c92fd55410784f86c44da99efc9.png

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <c3p0-config>
  3. <default-config> <!-- 默认配答 -->
  4. <property name="driverClass">com.mysql.jdbc.Driver</property>
  5. <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
  6. <property name="user">root</property>
  7. <property name="password">123456</property>
  8. <property name="initialPoolSize">5</property>
  9. <property name="maxPoolSize">20</property>
  10. </default-config>
  11. </c3p0-config>

4b1277d540134701935dc749f58c73ad.jpeg

  1. package org.example;
  2. import com.mchange.v2.c3p0.ComboPooledDataSource;
  3. import java.sql.Connection;
  4. public class C3p0Factory2 {
  5. private static ComboPooledDataSource dataSource = null;
  6. public static void init() throws Exception {
  7. // 创建 ComboPooledDataSource 实例,会自动加载 c3p0-config.xml 文件的配置
  8. dataSource = new ComboPooledDataSource();
  9. // 此时 dataSource 是一个完全配置好的可用连接池 DataSource
  10. }
  11. public static Connection getConnection() throws Exception {
  12. if (null == dataSource) {
  13. init();
  14. }
  15. // 返回从连接池获取的数据库连接
  16. return dataSource.getConnection();
  17. }
  18. }

2.4.4 DruidData类

  1. package org.example;
  2. import com.alibaba.druid.pool.DruidDataSource;
  3. import java.sql.Connection;
  4. public class DruidFactory1 {
  5. private static DruidDataSource dataSource = null;
  6. public static void init() throws Exception {
  7. dataSource = new DruidDataSource();
  8. dataSource.setDriverClassName("com.mysql.jdbc.Driver");
  9. dataSource.setUsername("root");
  10. dataSource.setPassword("b123456");
  11. dataSource.setUrl("jdbc:mysql://localhost:3306/t_book?serverTimezone=UTC");
  12. dataSource.setInitialSize(5);
  13. dataSource.setMinIdle(1);
  14. dataSource.setMaxActive(10);
  15. // 启用监控统计功能
  16. dataSource.setFilters("stat");
  17. }
  18. public static Connection getConnection() throws Exception {
  19. if (null == dataSource) {
  20. init();
  21. }
  22. return dataSource.getConnection();
  23. }
  24. }

2.4.6配置

  1. driverClassName=com.mysql.jdbc.Driver
  2. url=jdbc:mysql://localhost:3306/t_book?serverTimezone=UTC
  3. username=root
  4. password=b123456
  5. filters=stat
  6. initialSize=2
  7. maxActive=300
  8. maxWait=60000
  9. timeBetweenEvictionRunsMillis=60000
  10. minEvictableIdleTimeMillis=300000
  11. validationQuery=SELECT 1
  12. testWhileIdle=true
  13. testOnBorrow=false
  14. testOnReturn=false
  15. poolPreparedStatements=false
  16. maxPoolPreparedStatementPerConnectionSize=200
  17. package org.example;
  18. import com.alibaba.druid.pool.DruidDataSource;
  19. import com.alibaba.druid.pool.DruidDataSourceFactory;
  20. import java.io.InputStream;
  21. import java.sql.Connection;
  22. import java.util.Properties;
  23. public class DruidFactory2 {
  24. private static DruidDataSource dataSource = null;
  25. public static void init() throws Exception {
  26. Properties properties = new Properties();
  27. // 加载属性文件 druid.properties
  28. InputStream in = DruidFactory2.class.getClassLoader().getResourceAsStream("druid.properties");
  29. properties.load(in);
  30. // 通过属性创建 Druid 数据源
  31. dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
  32. in.close();
  33. }
  34. public static Connection getConnection() throws Exception {
  35. if (null == dataSource) {
  36. init();
  37. }
  38. return dataSource.getConnection();
  39. }
  40. }

三、基于web的数据库开发

3.1配置DataSource

283414d5e47d4961a80c3dc746fe26e3.png

3.2相关类(driverClassName,驱动类)

" class="reference-link">d3f90959eb0e47258e422ab1572d58a8.png

3.3Connection接口" class="reference-link">6a7b47c7e4fb4eb2b21d9ce0cbbd1bba.png3.3Connection接口

java.sql.Connection接口表示与特定数据库的会话。在Connection的上下文中,执行SQL语句并返回结果。Connection接口对于连接到数据库和执行SQL语句至关重要。f2cb8de4f0f6479297a41c52deda6bbe.png

3.4Statement接口

Statement接口是Java执行数据库操作的一个重要接口,用于在已经建立数据库连接的基础上,向数据库发送要执行的SQL语句。Statement接口提供了执行语句和获取结果的基本方法。PreparedStatement接口添加了处理IN参数的方法;而CallableStatement添加了处理OUT参数的方法。

e828a48878724cc49ced7259b5b25dcc.png

3.5PreparedStatement接口

PreparedStatement接口是Java中的一个接口,它是用来执行预编译的SQL语句的。PreparedStatement是Statement的子接口,继承了Statement的所有功能。PreparedStatement接口提供了补充占位符变量的方法

c182b62fa5e04bf2ae82427252c250bc.png

发表评论

表情:
评论列表 (有 0 条评论,123人围观)

还没有评论,来说两句吧...

相关阅读

    相关 java泛型

    一、泛型入门 1.1基础概念 > Java泛型是JDK 5中引入的一个新特性,它提供了编译时类型安全检测机制,该机制允许程序员在编译时检测到非法的类型 ...

    相关 JAVAGUI编程

    一、相关概述 > GUI编程是指通过图形化的方式来实现计算机程序的编写,它可以让用户通过鼠标、键盘等设备来操作计算机,而不是通过命令行来输入指令。在Java中,G...