5.1 JDBC的设计
5.1.1 JDBC驱动程序类型
5.1.2 JDBC的典型用法
5.2 结构化查询语言
5.3 JDBC配置
5.3.1 数据库URL
5.3.2 驱动程序JAR文件
5.3.3 启动数据库
5.3.5 连接到数据库
package test;
import java.nio.file.*;
import java.sql.*;
import java.io.*;
import java.util.*;
/**
* This program tests that the database and the JDBC driver are correctly configured.
* @version 1.02 2012-06-05
* @author Cay Horstmann
*/
public class TestDB
{
public static void main(String args[]) throws IOException
{
try
{
runTest();
}
catch (SQLException ex)
{
for (Throwable t : ex)
t.printStackTrace();
}
}
/**
* Runs a test by creating a table, adding a value, showing the table contents, and removing
* the table.
*/
public static void runTest() throws SQLException, IOException
{
try (Connection conn = getConnection();
Statement stat = conn.createStatement())
{
stat.executeUpdate("CREATE TABLE Greetings (Message CHAR(20))");
stat.executeUpdate("INSERT INTO Greetings VALUES ('Hello, World!')");
try (ResultSet result = stat.executeQuery("SELECT * FROM Greetings"))
{
if (result.next())
System.out.println(result.getString(1));
}
stat.executeUpdate("DROP TABLE Greetings");
}
}
/**
* Gets a connection from the properties specified in the file database.properties.
* @return the database connection
*/
public static Connection getConnection() throws SQLException, IOException
{
Properties props = new Properties();
try (InputStream in = Files.newInputStream(Paths.get("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");
return DriverManager.getConnection(url, username, password);
}
}
5.4 使用JDBC语句
5.4.1 执行SQL语句
5.4.2 管理连接、数据、结果集
5.5 执行查询操作
5.5.1 预备语句
package query;
import java.io.*;
import java.nio.file.*;
import java.sql.*;
import java.util.*;
/**
* This program demonstrates several complex database queries.
* @version 1.30 2012-06-05
* @author Cay Horstmann
*/
public class QueryTest
{
private static final String allQuery = "SELECT Books.Price, Books.Title FROM Books";
private static final String authorPublisherQuery = "SELECT Books.Price, Books.Title"
+ " FROM Books, BooksAuthors, Authors, Publishers"
+ " WHERE Authors.Author_Id = BooksAuthors.Author_Id AND BooksAuthors.ISBN = Books.ISBN"
+ " AND Books.Publisher_Id = Publishers.Publisher_Id AND Authors.Name = ?"
+ " AND Publishers.Name = ?";
private static final String authorQuery
= "SELECT Books.Price, Books.Title FROM Books, BooksAuthors, Authors"
+ " WHERE Authors.Author_Id = BooksAuthors.Author_Id AND BooksAuthors.ISBN = Books.ISBN"
+ " AND Authors.Name = ?";
private static final String publisherQuery
= "SELECT Books.Price, Books.Title FROM Books, Publishers"
+ " WHERE Books.Publisher_Id = Publishers.Publisher_Id AND Publishers.Name = ?";
private static final String priceUpdate = "UPDATE Books " + "SET Price = Price + ? "
+ " WHERE Books.Publisher_Id = (SELECT Publisher_Id FROM Publishers WHERE Name = ?)";
private static Scanner in;
private static ArrayList<String> authors = new ArrayList<>();
private static ArrayList<String> publishers = new ArrayList<>();
public static void main(String[] args) throws IOException
{
try (Connection conn = getConnection())
{
in = new Scanner(System.in);
authors.add("Any");
publishers.add("Any");
try (Statement stat = conn.createStatement())
{
// Fill the authors array list
String query = "SELECT Name FROM Authors";
try (ResultSet rs = stat.executeQuery(query))
{
while (rs.next())
authors.add(rs.getString(1));
}
// Fill the publishers array list
query = "SELECT Name FROM Publishers";
try (ResultSet rs = stat.executeQuery(query))
{
while (rs.next())
publishers.add(rs.getString(1));
}
}
boolean done = false;
while (!done)
{
System.out.print("Q)uery C)hange prices E)xit: ");
String input = in.next().toUpperCase();
if (input.equals("Q"))
executeQuery(conn);
else if (input.equals("C"))
changePrices(conn);
else
done = true;
}
}
catch (SQLException e)
{
for (Throwable t : e)
System.out.println(t.getMessage());
}
}
/**
* Executes the selected query.
* @param conn the database connection
*/
private static void executeQuery(Connection conn) throws SQLException
{
String author = select("Authors:", authors);
String publisher = select("Publishers:", publishers);
PreparedStatement stat;
if (!author.equals("Any") && !publisher.equals("Any"))
{
stat = conn.prepareStatement(authorPublisherQuery);
stat.setString(1, author);
stat.setString(2, publisher);
}
else if (!author.equals("Any") && publisher.equals("Any"))
{
stat = conn.prepareStatement(authorQuery);
stat.setString(1, author);
}
else if (author.equals("Any") && !publisher.equals("Any"))
{
stat = conn.prepareStatement(publisherQuery);
stat.setString(1, publisher);
}
else
stat = conn.prepareStatement(allQuery);
try (ResultSet rs = stat.executeQuery())
{
while (rs.next())
System.out.println(rs.getString(1) + ", " + rs.getString(2));
}
}
/**
* Executes an update statement to change prices.
* @param conn the database connection
*/
public static void changePrices(Connection conn) throws SQLException
{
String publisher = select("Publishers:", publishers.subList(1, publishers.size()));
System.out.print("Change prices by: ");
double priceChange = in.nextDouble();
PreparedStatement stat = conn.prepareStatement(priceUpdate);
stat.setDouble(1, priceChange);
stat.setString(2, publisher);
int r = stat.executeUpdate();
System.out.println(r + " records updated.");
}
/**
* Asks the user to select a string.
* @param prompt the prompt to display
* @param options the options from which the user can choose
* @return the option that the user chose
*/
public static String select(String prompt, List<String> options)
{
while (true)
{
System.out.println(prompt);
for (int i = 0; i < options.size(); i++)
System.out.printf("%2d) %s%n", i + 1, options.get(i));
int sel = in.nextInt();
if (sel > 0 && sel <= options.size())
return options.get(sel - 1);
}
}
/**
* Gets a connection from the properties specified in the file database.properties.
* @return the database connection
*/
public static Connection getConnection() throws SQLException, IOException
{
Properties props = new Properties();
try (InputStream in = Files.newInputStream(Paths.get("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");
return DriverManager.getConnection(url, username, password);
}
}
5.5.2 读写LOB
5.9 事务
5.9.1 用JDBC对事务进行编程
5.9.2 保存点
5.9.3 批量更新