public void actionPerformed(ActionEvent evt) {
String arg = evt.getActionCommand();
if (arg.equals("Query")) { // 用户按下Query按钮
ResultSet rs = null;
try {
String author = (String) authors.getSelectedItem();
String publisher = (String) publishers.getSelectedItem();
if (!author.equals("Any") && !publisher.equals("Any")) {
if (authorPublisherQueryStmt == null) {
// 根据用户选择的出版社名和作者名查询相关的书名和书价
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 = ?";
authorPublisherQueryStmt = con.prepareStatement(authorPublisherQuery);
}
authorPublisherQueryStmt.setString(1, author);
authorPublisherQueryStmt.setString(2, publisher);
rs = authorPublisherQueryStmt.executeQuery();
} else if (!author.equals("Any") && publisher.equals("Any")) {
if (authorQueryStmt == null) {
// 根据用户选择的作者名查询相关的书名和书价
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 = ?";
authorQueryStmt = con.prepareStatement(authorQuery);
}
authorQueryStmt.setString(1, author);
rs = authorQueryStmt.executeQuery();
} else if (author.equals("Any") && !publisher.equals("Any")) {
if (publisherQueryStmt == null) {
// 根据用户选择的出版社名查询相关的书名和书价
String publisherQuery =
"SELECT Books.Price, Books.Title "
+ "FROM Books, Publishers "
+ "WHERE Books.Publisher_Id = Publishers.Publisher_Id AND "
+ "Publishers.Name = ?";
publisherQueryStmt = con.prepareStatement(publisherQuery);
}
publisherQueryStmt.setString(1, publisher);
rs = publisherQueryStmt.executeQuery();
} else {
if (allQueryStmt == null) {
// 若用户未选任何信息，则输出所有的书名和对应的书价
String allQuery = "SELECT Books.Price, Books.Title FROM Books";
allQueryStmt = con.prepareStatement(allQuery);
}
rs = allQueryStmt.executeQuery();
}
result.setText("");
while (rs.next()) result.append(rs.getString(1) + " | " + rs.getString(2) + "\n");
rs.close();
} catch (Exception e) {
result.setText("Error " + e);
}
} else if (arg.equals("Change prices")) { // 用户选择“Change prices”按钮
String publisher = (String) publishers.getSelectedItem();
if (publisher.equals("Any")) result.setText("I am sorry, but I cannot do that.");
else
try {
// 根据用户输入的新的书价更新Books表的数据
String updateStatement =
"UPDATE Books "
+ "SET Price = Price + "
+ priceChange.getText()
+ " WHERE Books.Publisher_Id = "
+ "(SELECT Publisher_Id FROM Publishers WHERE Name = '"
+ publisher
+ "')";
int r = stmt.executeUpdate(updateStatement);
result.setText(r + " records updated.");
} catch (Exception e) {
result.setText("Error " + e);
}
}
}
