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); } } }