java-web/docs/tasks/chapter06-tasks.md

6. 数据库访问

6.1 JDBC

6.1.3 实现第一个JDBC程序

练习任务:

1. 创建数据库表:

   • 根据PetClinic中的Owner实体模型创建一个表，包含id（整数，主键，自增）、firstName（字符串，长度255，不可为空）、lastName（字符串，长度255，不可为空）、address（字符串，长度255）、city（字符串，长度255）、telephone（字符串，长度255）字段。
   • 示例代码:

     private static void createOwnerTable(Connection conn) throws SQLException {
         String sql = "CREATE TABLE IF NOT EXISTS owners (" +
                      "id INT AUTO_INCREMENT PRIMARY KEY," +
                      "firstName VARCHAR(255) NOT NULL," +
                      "lastName VARCHAR(255) NOT NULL," +
                      "address VARCHAR(255)," +
                      "city VARCHAR(255)," +
                      "telephone VARCHAR(255))";
         try (PreparedStatement stmt = conn.prepareStatement(sql)) {
             stmt.executeUpdate();
         }
     }
     

2. 插入业主记录:

   • 向owners表中插入两条记录。
   • 示例代码:

     private static void insertOwners(Connection conn) throws SQLException {
         String sql = "INSERT INTO owners (firstName, lastName, address, city, telephone) VALUES (?, ?, ?, ?, ?)";
         try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
             pstmt.setString(1, "Alice");
             pstmt.setString(2, "Johnson");
             pstmt.setString(3, "123 Main St");
             pstmt.setString(4, "Springfield");
             pstmt.setString(5, "555-1234");
             pstmt.executeUpdate();
     
             pstmt.setString(1, "Bob");
             pstmt.setString(2, "Smith");
             pstmt.setString(3, "456 Elm St");
             pstmt.setString(4, "Springfield");
             pstmt.setString(5, "555-5678");
             pstmt.executeUpdate();
         }
     }
     

3. 查询业主记录:

   • 查询owners表中的所有记录，并打印出每条记录的信息。
   • 示例代码:

     private static void queryOwners(Connection conn) throws SQLException {
         String sql = "SELECT * FROM owners";
         try (PreparedStatement pstmt = conn.prepareStatement(sql);
              ResultSet rs = pstmt.executeQuery()) {
             while (rs.next()) {
                 int id = rs.getInt("id");
                 String firstName = rs.getString("firstName");
                 String lastName = rs.getString("lastName");
                 String address = rs.getString("address");
                 String city = rs.getString("city");
                 String telephone = rs.getString("telephone");
                 System.out.println("ID: " + id + ", First Name: " + firstName + ", Last Name: " + lastName + ", Address: " + address + ", City: " + city + ", Telephone: " + telephone);
             }
         }
     }
     

4. 更新业主记录:

   • 更新owners表中名字为"Alice Johnson"的业主的电话号码为"555-1111"。
   • 示例代码:

     private static void updateOwner(Connection conn) throws SQLException {
         String sql = "UPDATE owners SET telephone = ? WHERE firstName = ? AND lastName = ?";
         try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
             pstmt.setString(1, "555-1111");
             pstmt.setString(2, "Alice");
             pstmt.setString(3, "Johnson");
             int rowsUpdated = pstmt.executeUpdate();
             System.out.println(rowsUpdated + " row(s) updated.");
         }
     }
     

5. 删除业主记录:

   • 删除owners表中名字为"Bob Smith"的业主记录。
   • 示例代码:

     private static void deleteOwner(Connection conn) throws SQLException {
         String sql = "DELETE FROM owners WHERE firstName = ? AND lastName = ?";
         try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
             pstmt.setString(1, "Bob");
             pstmt.setString(2, "Smith");
             int rowsDeleted = pstmt.executeUpdate();
             System.out.println(rowsDeleted + " row(s) deleted.");
         }
     }
     

6. 完整程序:

   • 将以上步骤整合到一个完整的程序中，确保正确加载MySQL驱动、建立数据库连接，并处理异常。
   • 示例代码:

     import java.sql.Connection;
     import java.sql.DriverManager;
     import java.sql.SQLException;
     
     public class JdbcCrudExample {
         private static final String DB_URL = "jdbc:mysql://localhost:3306/petclinic";
         private static final String USER = "root";
         private static final String PASS = "password";
     
         public static void main(String[] args) {
             Connection conn = null;
             try {
                 // 加载MySQL驱动
                 Class.forName("com.mysql.cj.jdbc.Driver");
     
                 // 获取数据库连接
                 conn = DriverManager.getConnection(DB_URL, USER, PASS);
     
                 // 创建表
                 createOwnerTable(conn);
     
                 // 插入记录
                 insertOwners(conn);
     
                 // 查询记录
                 queryOwners(conn);
     
                 // 更新记录
                 updateOwner(conn);
     
                 // 删除记录
                 deleteOwner(conn);
     
                 // 再次查询记录
                 queryOwners(conn);
     
             } catch (ClassNotFoundException | SQLException e) {
                 e.printStackTrace();
             } finally {
                 if (conn != null) {
                     try {
                         conn.close();
                     } catch (SQLException e) {
                         e.printStackTrace();
                     }
                 }
             }
         }
     
         // 其他方法...
     }
     

6.3 SQL注入的预防措施

练习任务:

1. 使用PreparedStatement防止SQL注入:
   • 修改上述的queryOwners方法，使用PreparedStatement来执行查询。
   • 示例代码:

     private static void queryOwners(Connection conn) throws SQLException {
         String sql = "SELECT * FROM owners WHERE firstName = ? AND lastName = ?";
         try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
             pstmt.setString(1, "Alice");
             pstmt.setString(2, "Johnson");
             ResultSet rs = pstmt.executeQuery();
             while (rs.next()) {
                 int id = rs.getInt("id");
                 String firstName = rs.getString("firstName");
                 String lastName = rs.getString("lastName");
                 String address = rs.getString("address");
                 String city = rs.getString("city");
                 String telephone = rs.getString("telephone");
                 System.out.println("ID: " + id + ", First Name: " + firstName + ", Last Name: " + lastName + ", Address: " + address + ", City: " + city + ", Telephone: " + telephone);
             }
         }
     }
     

6.2 数据库连接池

6.2.4 使用HikariCP实现数据库连接池

练习任务:

1. 配置HikariCP连接池:
   • 使用HikariCP配置一个连接池，并使用它来获取数据库连接。
   • 示例代码:

     import com.zaxxer.hikari.HikariConfig;
     import com.zaxxer.hikari.HikariDataSource;
     import java.sql.Connection;
     import java.sql.SQLException;
     
     public class HikariCpExample {
         public static void main(String[] args) {
             HikariConfig config = new HikariConfig();
             config.setJdbcUrl("jdbc:mysql://localhost:3306/petclinic");
             config.setUsername("root");
             config.setPassword("password");
             config.setMaximumPoolSize(10);
             config.setConnectionTimeout(30000);
     
             HikariDataSource ds = new HikariDataSource(config);
     
             try (Connection conn = ds.getConnection()) {
                 // 使用连接执行数据库操作
                 System.out.println("Database connection established.");
             } catch (SQLException e) {
                 e.printStackTrace();
             }
         }
     }