读后笔记 -- Java核心技术（第11版 卷 II） Chapter5 数据库编程

5.1 JDBC API

1. JDBC (Java Database Connectivity) is an API for interacting with a database in Java.

2. Each database vendor produces a driver that translates JDBC requests to the database-specific protocol.

3. JDBC is based on SQL (Standard Query Lanauge) for database access.

4. JDBC provides classes for issuing SQL statements and for working with the query results.

5. Object-relational mappers provide a higher-level mechanism (Hibernate or JPA) for working with a database, but you should understand SQL/JDBC to use them effectively.

 

---

5.2 Requirements for Using JDBC

1. You need a database, we use Mysql as example

    About how to install Mysql, you can refer to https://www.cnblogs.com/bruce-he/p/10454856.html, it is similar in Windows.

2. You need a database driver JAR, such as mysql-connector-j-8.0.32.jar for Mysql

   Without using Spring MVC or Spring Boot framework, you can follow below steps to add it in IdeaJ.

1. Vist https://mvnrepository.com/, then type "mysql-connector-java" to search
2. In the first "MySQL Connector/J" item, download the latest JDBC driver. Choose "jar" Files to download.
3. In the IdeaJ, follow the detailed step in the picture below. 

3. Config the database connection properties.

jdbc.drivers=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/javatest?userUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=root

4. Use this code to establish a JDBC connection:

// use Properties to parse jdbc.properties。最好将该段代码包含在一个 try-with-resource 中
var props = new Properties();
try (InputStream in = Files.newInputStream(Paths.get("./v2ch05/src/database.properties"))) {
    props.load(in);
}
String drivers = props.getProperty("jdbc.drivers");
if (drivers != null) 
　　System.setProperty("jdbc.drivers", drivers);
String url = props.getProperty("jdbc.url");
String username = props.getProperty("jdbc.username");
String password = props.getProperty("jdbc.password");

Connection conn = DriverManager.getConnection(url, username, password);
return conn;

加载驱动器的几种方式：

// 1. Java 程序中通过 DriverManager
Class.forName("com.mysql.cj.jdbc.Driver");

// 2. Java 程序中设置 jdbc.driver
System.setProperty("jdbc.drivers", "com.mysql.cj.jdbc.Driver");

// 3. 通过 cmd 设置 jdbc.driver
java -Djdbc.drivers=com.mysql.cj.jdbc.Driver ProgramName

需要注意的点：

// 实际测试中，如果注释掉 注册驱动器的部分，也可以连接上数据库

public static Connection getConnection() throws IOException, SQLException {
    var props = new Properties();
    try (InputStream in = Files.newInputStream(Paths.get("./v2ch05copy/src/database.properties"))) {
        props.load(in);
    }
//        String drivers = props.getProperty("jdbc.drivers");
//        if (drivers != null)
//            System.setProperty("jdbc.drivers", drivers);
    System.out.println("jdbc.drivers: " + System.getProperty("jdbc.drivers"));
    String url = props.getProperty("jdbc.url");
    String username = props.getProperty("jdbc.username");
    String password = props.getProperty("jdbc.password");

    return DriverManager.getConnection(url, username, password);
}

// 调用
try (Connection conn = getConnection();
    Statement stat = conn.createStatement()) {

    System.out.println(conn);
// com.mysql.cj.jdbc.ConnectionImpl@4c762604
// 通过此处可以发现，即使注释掉 注册驱动器，系统也会搜索到 类目录下的驱动器进行加载。但是，为了严谨起见，还是需要指定驱动器

5. You use the Connection object to create SQL statement

try (Connection conn = getConnection();
             // Statement 是 Java 执行数据库操作的一个重要接口，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句。Statement对象，用于执行不带参数的简单SQL语句。
            Statement stat = conn.createStatement()) {
            stat.executeUpdate("CREATE TABLE Greetings (Message CHAR(20))");
}

6. Database connections are a limited resource. When you are done, be sure to close the connection.

 

---

5.4 Executing SQL Statements

5.4.1 Executing SQL

1. To execute a SQL statement, create a Statement object:

Statement stat = conn.createStatement();

2. To update the database (INSERT, UPDATE, DELETE), call the executeUpdate method, and the number of affected rows is returned.

String command = "UPDATE Books SET Price = Price - 5.00 WHERE Title NOT LIKE '%Introduction%'";
int rows = stat.executeUpdate(command);

3. Use executeQuery to issue a SELECT query, and an ResultSet type object is returned.

ResultSet result = stat.executeQuery("SELECT * FROM Books");

while (rs.next()) {
    System.out.println(result.getString(1));               // 当前行的第一列的值
    System.out.println(result.getDouble("Price"));         // 当前行列名为"Price"的值
}    

4. Use execute to issue arbitrary SQL commands.

 

5.4.2 Managing JDBC objects

1. A connection object can produce one or more Statement objects.

• Some database drivers only allow one Statement at a time.
• Call DatabaseMetaData.getMaxStatements to find out.

2. You can use the same Statement object for multiple queries.

3. A Statement can have at most one open ResultSet.

• Don't work with multiple result sets at a time. Issue a query that gives you all data in one result set.

4. When you execute another query or close a Statement, an open result set is closed.

• The Statement.closeOnComplete method closes the statement as soon as an open result set is closed.
• Some other person, put the each Connection, Statement and ResultSet in try-with-resources.

5. When you close a Connection, all statements are closed.

 

5.4.3 SQLExceptions and Warnings

1. A SQLException has a chain of SQLException objects. This is in addition to the "cause" chain that all exceptions have.

2. The  SQLException class extends the Iterable<Throwable> interface, you can iterate over all exceptions like this:

for (Throwable t : sqlException) {    // 因为 SQLException 是一个异常链（书P241 图5-5），所以通过 for 循环可以处理相关的异常
     do something with t
}

3. The SQLException.getSQLState method yields a string that is standardized by either X/Open or SQL:2003.

• Call DatabaseMetaData.getSQLStateType to find out which standard is used by your driver.

4. There is also a chain of warnings:

SQLWarning w = stat.getWarning();
while (w != null) {
    do something with w
    w = w.nextWarning();    // it's different from SQLException, each warning has a nextWarning() method
}

 

5.4.4 Result Sets

1. A query yields a ResultSet:

ResultSet rs = stat.exectQuery("SELECT * FROM Books");

2. Use this loop to iterate over the rows:

while (rs.next()) {
    look at a row of the result set
}

3. To get at the columns of a row, use one of the get methods:

String isbn = rs.getString(1);           // The first(!) column, to get the value using column index
double price = rs.getDouble("Price");    // to get the value using column name

---

5.5 Executing Query Operation

5.5.1 Prepared Statements

1. When a query has variable parts, you don't want to formulate it through string concatenation:

String query = "SELECT * FROM Books WHERE Books.Title = " + title;    // Don't - or you may become the victim of SQL injection

2. Instead, use a prepared statement:

String query = "SELECT * FROM Books WHERE Books.Title = ?";
PreparedStatement stat = conn.preparedStatement(query);
stat.setString(1, title);    // 1) setString() for String, setInt() for Integer, SetDouble() for Double;    2) first index is 1
ResultSet rs = stat.executeQuery();

3. A preparedStatement becomes invalid after closing the Connection that created it. But the database will cache the query plan.

 

5.5.3 SQL Escapes 转义

转义主要用于下列场景：

• 1. 日期和时间字面常量
• 2. 调用标量函数
• 3. 调用存储过程
• 4. 外连接
• 5. 在 LIKE 子句中的转义字符

 

The "escape" syntax supports features for which database syntax varies.

1. Specify date and time literals as:

{d '2008-01-24'}                   // date
{t '23:59:59'}                     // time
{ts '2008-01-24 23:59:59.999' }    // timestamp

2. To call a scalar function (that is , a function returning a single value), embed the standard function name and argument like this:

{fn left(?, 20)}
{fn user()}

3. Here is how to call a stored procedure:

{call PROC1(?, ?)}      // 存储过程，有入参，入参通过 () 传递
{call PROC2}            // 存储过程，无入参
{? = call PROC3(?)}     // 存储过程，有入参，且有返回值，通过 = 传递

4. The {oj ...} escape lets you fomulate outer joins in a database-independent way.

SELECT * FROM {oj Books LEFT OUTER JOIN Publishers ON Books.Publisher_ID = Publisher.Publisher_ID}

5. You can specifiy an escape character, for example, to match all titles containg a literal %

... WHERE ? like %!_% {escape '!'}    // ! 定义为转义字符， !_ 表示字面常量下划线，如查找 "javacore_chapter1"

 

5.5.4 Multiple Results

1. It is possible for a query to return multiple result sets or update counts.

• This can happen with stored procedures.
• Some databases allow you to submit multiple SELECT statements together.

2. Use the following loop to analyze the query result:

•   如果由多结果集构成的链中的下一项是结果集，那么 execute() 和  getMoreResults() 将返回 true；
• 如果在链中的下一项不是更新计数， getUpdateCount() 将返回 -1；

boolean isResult = stat.execute(command);
boolean done = false;
while (!done) {
    if (isResult) {
        ResultSet result = stat.getResultSet();
        do something with result
    }
    else {
        int updateCount = stat.getUpdateCount();
        if (updateCount >= 0) {
            do something with updateCount
        }
        else {
            done = true;
        }
    }
    if (!done) {
        isResult = stat.getMoreResults();
    }
}

获取所有结果集的步骤：

• 1. 使用 execute() 执行 SQL 语句；
• 2. 获取第一个结果集或更新计数；
• 3. 重复调用 getMoreResults() 以移动到下一个结果集；
• 4. 当不存在更多的结果集或更新计数时，完成操作

 

5.5.5 Retrieving Autogenerated Values

1. Most databases support some mechanism for automatcally generating values.

• MySQL: Id INTEGER AUTO_INCREMENT
• Derby: Id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)

2. You can retrieve the autogenerated values that were generated by an INSERT statement:

stat.executeUpdate(insertStatement, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stat.getGeneratedKeys();
if (rs.next()) {
    int key = rs.getInt(1);
    Do something with key
}

---

5.6 Scrollable Result Sets

1. The ResultSet.next() iterates over the rows of a result set.

2. A scrollable result set allows you to move the current row by arbitrary amounts and to arbitrary positions.

3. A scrollable result set can be updatable: you can set the column values, and the changes are reflected in the database.

4. Construct a scrollable or updatable result set like this:

Statement stat = conn.createStatement(type, concurrency);                      // 普通语句
PreparedStatement stat = conn.preparedStatement(command, type, concurrency);   // 预备语句

ResultSet rs = stat.executeQuery(query);       // 此时，得到的结果集是可滚动的；

ResultSet 类的值
type 值	TYPE_FORWARD_ONLY	结果集不能滚动（默认值）
	TYPE_SCROLL_INSENSITIVE	结果集可以滚动，但对数据库变化不敏感
	TYPE_SCROLL_SENSITIVE	结果集可以滚动，且对数据库变化敏感
concurrency 值	CONCUR_READ_ONLY	结果集不能用于更新数据库（默认值）
	CONCUR_UPDATABLE	结果集可以用于更新数据库

5. To scroll within the result set, call:

rs.previous()     // 如果游标位于一个实际的行上，则返回 true，如果位于第一行之前，返回 false
rs.relative(n)    // n 为正时向后移动，为负时向前移动
rs.absolute(n)    // 光标移动到指定行

6. The getRow() yields the current row number (starting at 1) or 0 if the cursor is before the first or after the last row.

 

7. If the result set is updatable, call one of the update() to change a column value:

double price = rs.getDouble("Price");
rs.updateDouble("Price", price + increase);
rs.updateRow();    // call updateRow() to store the changes in database

// table 需要有 primary key，才能 update。
Result Set not updatable (referenced table has no primary keys).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE,
 the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.

8. To insert a new row, call

rs.moveToInsertRow();       // 插入行是个特殊的行数，一般情况下，是别的最后。如果要插入，先移动到插入行
call rs.updateXxx(...)
rs.insertRow();
rs.moveToCurrentRow();

9. The ResultSet.deleteRow() removes the row from the result set and the database.

// 指定到最后再删除，即可以删除最后一条数据了
rs.last();
rs.deleteRow();

10. Update result sets are intended for table editing by end users. If you want to update the database programmatically, use SQL queries.

 

---

5.7 Row Sets

5.7.2 Cached Row Sets

1. Updatable result sets require a connection to the database, while connections are scarce resources.

2. A cached row set is like an updatable result set, but it can be disconnected from the database.

 

// 两种方式

// Obtain a cached row set like this:
RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet crs = factory.createCachedRowSet();

// 方式一：
// step1: populate it with a result set:
ResultSet result = ...;
crs.populate(result);
conn.close();    // now OK to close the database connection

// step2: Now you can view and update the row set. When you are done, call crs.acceptChanges(conn), passing a new Connection object.
crs.acceptChanges(conn),

// 方式二：let the cached row set establish a connection automatically as needed.
// step1: set up the connection parameters:
crs.setURL("jdbc:mysql://127.0.0.1:3308/javatest)
crs.setUsername("root");
crs.setPassword("root");

// step2: set the query statement and any parameters, and execute the query:
crs.setCommand("SELECT * FROM Books WHERE Title = ?");
crs.setString(1, title);
crs.execute();

// step3: When you are done updating the row set, you can call crs.acceptChanges() without a Connection parameter.
crs.acceptChanges()

 

---

5.8 Metadata

Metadata are data that describe the database or its parts（描述数据库或其组成的数据）。包含：

• 数据库的元数据：提供有关数据库的数据
• 结果集的元数据：提供结果集的有关信息
• 预备语句参数的元数据

 

// 1. 数据库元数据 DatabaseMetaData
// step1: get a DatabaseMetaData object from a connection:
DatabaseMetaData meta = conn.getMetaData();

// step2: This call yields a result set that lists all tables in the database:
ResultSet mrs = meta.getTables(null, null, null, new String[] {"TABLE"});

// step3: As it happens, the third column is the table name:
while (mrs.next()) {
    System.out.println(mrs.getString(3));
}

 

There are many methods to disclose how the database driver behave under conditions that are not standardized, such as:

meta.supportsCatalogsInPrivilegeDefinitions()
meta.nullPlusNonNullIsNull()

 

2. Result Set Metadata

1). The ResultSetMetaData interface has methods to describe a result set.

2). This is particularly useful for generic tools that work with arbitrary tables.

3). Here we find out column sizes and column labels, useful information for displaying a result:

ResultSet rs = stat.executeQuery("SELECT * FROM " + tableName);
ResultSetMetaData meta = rs.getMetaData();
for (int i = 1; i <= meta.getColumnCount(); i++) {
    String columnName = meta.getColumnLabel(i);
    int columnWidth = meta.getColumnDisplaySize(i);
    ...
}

 

---

5.9 Transactions

5.9.1 Transactions

1. You can group a set of statements to form a transaction.

• When all has gone well, the transaction is committed,
• If something goes wrong, the transaction is rolled back, as if none of its statement has been issued.

2. By default, a connection is in "autocommit" mode: Each statement is committed as it is executed.

3. In order to use tansaction, first turn off autocommit, then execute any number of statements and commit your work.

conn.setAutoCommit(false);
Statement stat = conn.createStatement();
stat.executeUpdate(command1);
stat.executeUpdate(command2);
...
conn.commit();

4. However, if an error occurred, call:

conn.rollback();

 

5.9.3 Batch Updates

Suppose an application populates a database with many INSERT statements. You can improve performance with a batch update instead of calling executeUpdate, call the addBatch method:

boolean autoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);

Statement stat = conn.createStatement();

String command = "CREATE TABLE ...";
stat.addBatch(command);
while (...) {
    command = "INSERT INTO ... VALUES(" + ... + ")";
    stat.addBatch(command);
}

// Finally, submit the entire batch:
int[] counts = stat.executeBatch();    // Yields row counts for all submitted statements

conn.commit();
conn.setAutoCommit(autoCommit);