Summary of this section
In the previous section, we realized the function of adding books, then this section will mainly realize the function of book maintenance, that is, the query, modification and deletion of book records.
Book maintenance interface
First of all, we need to get out the interface. Other functions of query, deletion and modification are based on the visual interface.
First, add the code design interface in BookManagePanel.java:
package bookManageSystem.view;
import bookManageSystem.bean.BookTypeBean;
import bookManageSystem.dao.BookDao;
import bookManageSystem.dao.BookTypeDao;
import bookManageSystem.tools.ComponentTools;
import bookManageSystem.tools.SimpleTools;
import javax.swing.*;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;
import javax.swing.table.DefaultTableModel;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.List;
public class BookManagePanel extends JPanel implements ActionListener, ListSelectionListener {
private ComponentTools componentTools = new ComponentTools();
private SimpleTools simpleTools = new SimpleTools();
private Box totalVBox, funcationHBox, checkHBox, tableHBox, sexRadioButtonHBox, descriptionHBox, buttonHBox,
showContentHBox1, showContentHBox2;
private JComboBox bookTypeComboBox, bookTypeComboBox2;
private JLabel bookManageFuncationLabel, bookNameLabel, bookTypeLabel, idLabel, priceLabel, bookNameLabel2,
bookAuthorLabel, bookTypeLabel2, bookAuthorLabel2, authorSexLabel, bookDescriptionLabel;
private JTextField bookNameTextField, bookNameTextField2, bookAuthorTextField, bookAuthorTextField2, idTextField,
priceTextField;
private JTextArea bookDescriptionTextArea;
private JButton checkButton, resetButton, resetButton2, alterButton, deleteButton;
private ButtonGroup sexButtonGroup;
private JScrollPane tableScrollPanel;
private JRadioButton femaleRadioButton, maleRadioButton;
private JTable table;
private DefaultTableModel tableModel;
BookManagePanel() {
// Add control content for book maintenance panel
this.add(this.createBookManageBox());
// Book category query SQL
String getBookTypeSQL = "select * from tb_booktype;";
// Get all book category data
List bookTypeList = new BookTypeDao().getRecordsDataBySql(getBookTypeSQL);
// Extract all book category name information
String[] typeNames = new String[bookTypeList.size()];
for (int i = 0; i < bookTypeList.size(); i++) {
BookTypeBean bookTypeBean = (BookTypeBean) bookTypeList.get(i);
typeNames[i] = bookTypeBean.getBookTypeName();
}
// Initialize book category information in the drop-down list box
componentTools.addComboBoxItems(bookTypeComboBox, typeNames);
componentTools.addComboBoxItems(bookTypeComboBox2, typeNames);
}
/**
* Create control content of book maintenance panel
*
* @return Return to a Box
*/
private Box createBookManageBox() {
totalVBox = Box.createVerticalBox();
funcationHBox = Box.createHorizontalBox();
bookManageFuncationLabel = new JLabel("Book maintenance function");
bookManageFuncationLabel.setFont(new Font("Microsoft YaHei", Font.BOLD, 30));
funcationHBox.add(bookManageFuncationLabel);
totalVBox.add(funcationHBox);
totalVBox.add(Box.createVerticalStrut(15));
checkHBox = Box.createHorizontalBox();
bookNameLabel = new JLabel("Book Name:");
bookNameTextField = new JTextField();
bookAuthorLabel = new JLabel("Author:");
bookAuthorTextField = new JTextField();
bookTypeLabel = new JLabel("Book category:");
bookTypeComboBox = new JComboBox();
checkButton = new JButton("query");
resetButton = new JButton("Reset");
checkHBox.add(bookNameLabel);
checkHBox.add(Box.createHorizontalStrut(10));
checkHBox.add(bookNameTextField);
checkHBox.add(Box.createHorizontalStrut(10));
checkHBox.add(bookAuthorLabel);
checkHBox.add(Box.createHorizontalStrut(10));
checkHBox.add(bookAuthorTextField);
checkHBox.add(Box.createHorizontalStrut(10));
checkHBox.add(bookTypeLabel);
checkHBox.add(Box.createHorizontalStrut(10));
checkHBox.add(bookTypeComboBox);
checkHBox.add(Box.createHorizontalStrut(10));
checkHBox.add(checkButton);
checkHBox.add(Box.createHorizontalStrut(10));
checkHBox.add(resetButton);
totalVBox.add(checkHBox);
totalVBox.add(Box.createVerticalStrut(15));
tableHBox = Box.createHorizontalBox();
// Instantiate a scroll panel
tableScrollPanel = new JScrollPane();
// Set predefined size
tableScrollPanel.setPreferredSize(new Dimension(700, 250));
tableHBox.add(tableScrollPanel);
totalVBox.add(tableHBox);
totalVBox.add(Box.createVerticalStrut(15));
showContentHBox1 = Box.createHorizontalBox();
idLabel = new JLabel("Serial number:");
idTextField = new JTextField(5);
idTextField.setEnabled(false);
bookNameLabel2 = new JLabel("Book Name:");
bookNameTextField2 = new JTextField(5);
authorSexLabel = new JLabel("Author gender:");
sexRadioButtonHBox = Box.createHorizontalBox();
femaleRadioButton = new JRadioButton("female");
maleRadioButton = new JRadioButton("male");
sexButtonGroup = new ButtonGroup();
sexButtonGroup.add(femaleRadioButton);
sexButtonGroup.add(maleRadioButton);
sexRadioButtonHBox.add(femaleRadioButton);
sexRadioButtonHBox.add(maleRadioButton);
showContentHBox1.add(idLabel);
showContentHBox1.add(Box.createHorizontalStrut(10));
showContentHBox1.add(idTextField);
showContentHBox1.add(Box.createHorizontalStrut(10));
showContentHBox1.add(bookNameLabel2);
showContentHBox1.add(Box.createHorizontalStrut(10));
showContentHBox1.add(bookNameTextField2);
showContentHBox1.add(Box.createHorizontalStrut(10));
showContentHBox1.add(authorSexLabel);
showContentHBox1.add(Box.createHorizontalStrut(10));
showContentHBox1.add(sexRadioButtonHBox);
totalVBox.add(showContentHBox1);
totalVBox.add(Box.createVerticalStrut(15));
showContentHBox2 = Box.createHorizontalBox();
priceLabel = new JLabel("Price:");
priceTextField = new JTextField(5);
bookAuthorLabel2 = new JLabel("Author:");
bookAuthorTextField2 = new JTextField(5);
bookTypeLabel2 = new JLabel("Book category:");
bookTypeComboBox2 = new JComboBox();
showContentHBox2.add(priceLabel);
showContentHBox2.add(Box.createHorizontalStrut(10));
showContentHBox2.add(priceTextField);
showContentHBox2.add(Box.createHorizontalStrut(10));
showContentHBox2.add(bookAuthorLabel2);
showContentHBox2.add(Box.createHorizontalStrut(10));
showContentHBox2.add(bookAuthorTextField2);
showContentHBox2.add(Box.createHorizontalStrut(10));
showContentHBox2.add(bookTypeLabel2);
showContentHBox2.add(Box.createHorizontalStrut(10));
showContentHBox2.add(bookTypeComboBox2);
totalVBox.add(showContentHBox2);
totalVBox.add(Box.createVerticalStrut(15));
descriptionHBox = Box.createHorizontalBox();
bookDescriptionLabel = new JLabel("Book Description:");
bookDescriptionTextArea = new JTextArea(3, 10);
descriptionHBox.add(bookDescriptionLabel);
descriptionHBox.add(bookDescriptionTextArea);
totalVBox.add(descriptionHBox);
totalVBox.add(Box.createVerticalStrut(15));
buttonHBox = Box.createHorizontalBox();
alterButton = new JButton("modify");
deleteButton = new JButton("delete");
resetButton2 = new JButton("Reset");
buttonHBox.add(alterButton);
buttonHBox.add(Box.createHorizontalStrut(100));
buttonHBox.add(deleteButton);
buttonHBox.add(Box.createHorizontalStrut(100));
buttonHBox.add(resetButton2);
totalVBox.add(buttonHBox);
// Set icons for buttons in batch
componentTools.setIcons(new JButton[]{alterButton, deleteButton, resetButton2}, new String[]{"src/bookManageSystem" +
"/images/edit.png", "src/bookManageSystem/images/delete.png", "src/bookManageSystem/images/reset.png"});
return totalVBox;
}
@Override
public void actionPerformed(ActionEvent e) {
}
@Override
public void valueChanged(ListSelectionEvent e) {
}
}For the operation project, the renderings are as follows:
Initialize table data
After completing all the controls in the interface, you need to display the table data. Because it is the first time to display the table data, you need to query all the table data, that is, initialize the table data.
The entity class fields are designed according to the data table, so the content of the entity class BookBean.java is as follows:
package bookManageSystem.bean;
public class BookBean {
private int bookId;
private String bookName;
private String bookAuthor;
private String bookAuthorSex;
private float bookPrice;
private String bookDescription;
private String bookTypeId;
public BookBean() {
}
public BookBean(int bookId, String bookName, String bookAuthor, String bookAuthorSex, float bookPrice, String bookDescription, String bookTypeId) {
this.bookId = bookId;
this.bookName = bookName;
this.bookAuthor = bookAuthor;
this.bookAuthorSex = bookAuthorSex;
this.bookPrice = bookPrice;
this.bookDescription = bookDescription;
this.bookTypeId = bookTypeId;
}
public int getBookId() {
return bookId;
}
public void setBookId(int bookId) {
this.bookId = bookId;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getBookAuthor() {
return bookAuthor;
}
public void setBookAuthor(String bookAuthor) {
this.bookAuthor = bookAuthor;
}
public String getBookAuthorSex() {
return bookAuthorSex;
}
public void setBookAuthorSex(String bookAuthorSex) {
this.bookAuthorSex = bookAuthorSex;
}
public float getBookPrice() {
return bookPrice;
}
public void setBookPrice(float bookPrice) {
this.bookPrice = bookPrice;
}
public String getBookDescription() {
return bookDescription;
}
public void setBookDescription(String bookDescription) {
this.bookDescription = bookDescription;
}
public String getBookTypeId() {
return bookTypeId;
}
public void setBookTypeId(String bookTypeId) {
this.bookTypeId = bookTypeId;
}
}The next step is to query the data from the database table, so write two methods in BookDao.java:
/**
* Operation result: obtain the database record data according to the parameter sql
*
* @param sql SQL Sentence
* @return List Returns the collection containing the Records object
*/
public List getRecordsDataBySql(String sql) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
List list = new ArrayList();
try {
//Get data connections
conn = JDBCUtils.getConnection();
//Get Statement object
stmt = conn.createStatement();
//Send SQL statement
rs = stmt.executeQuery(sql);
while (rs.next()) {
BookBean bookBean = new BookBean();
// Index starts at 1
bookBean.setBookId(rs.getInt(1));
bookBean.setBookName(rs.getString(2));
bookBean.setBookAuthor(rs.getString(3));
bookBean.setBookAuthorSex(rs.getString(4));
bookBean.setBookPrice(rs.getFloat(5));
bookBean.setBookDescription(rs.getString(6));
bookBean.setBookTypeId(rs.getString(7));
list.add(bookBean);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return list;
}
/**
* Operation result: convert the collection to an array
*
* @param list aggregate
* @return String[][] Two-dimensional array
*/
public String[][] ListToArray(List<BookBean> list) {
String[][] array = new String[list.size()][7];
for (int i = 0; i < list.size(); i++) {
BookBean l = list.get(i);
array[i][0] = String.valueOf(l.getBookId());
array[i][1] = l.getBookName();
array[i][2] = l.getBookAuthor();
array[i][3] = l.getBookAuthorSex();
array[i][4] = String.valueOf(l.getBookPrice());
array[i][5] = l.getBookDescription();
array[i][6] = l.getBookTypeId();
}
return array;
}After getting the data from the database, you need to display it in the JTable control, so add a method in the BookManagePanel.java class:
/**
* Create a table by filling in the table data from SQL query
*
* @param sql SQL Sentence
* @return Returns the filled JTable control
*/
private JTable createTable(String sql) {
// Get all the book records and convert the results into a two-dimensional array
String[][] rowdatas = new BookDao().ListToArray(new BookDao().getRecordsDataBySql(sql));
// Header
String[] headers = {"number", "Title of book", "Book author", "Author gender", "Book price", "Book description", "Book category"};
// Instantiate table control
table = new JTable();
// Set row height
table.setRowHeight(30);
// Populates header and table content data into the table data model
tableModel = new DefaultTableModel(rowdatas, headers);
//Setting up a data model for a table
table.setModel(tableModel);
// Returns the filled table control
return table;
}This method is to fill the data obtained from the database into the table, and then display the table on the interface. Add these lines of code in the createBookManageBox method, so that the table can be displayed on the interface.
// Add table to scroll panel
tableScrollPanel.setViewportView(this.createTable("select bId,bBookName,bAuthor,bSex,bPrice,bBookDescription," +
"btName from tb_book,tb_booktype where tb_book.btId=tb_booktype.btId;"));Add location as follows:
Before running the project, also make sure you have added a third-party add package to connect to the database.
Run the program, the results are as follows:
Inquiry function of books
Then it is to complete the book query function.
First of all, you need to register events for the query button and reset button, and some other buttons to be used in the interface also need to register events.
Add the following code to the last location in the BookManagePanel constructor method to register the event:
// Register event listeners for buttons checkButton.addActionListener(this); resetButton.addActionListener(this); alterButton.addActionListener(this); deleteButton.addActionListener(this); resetButton2.addActionListener(this);
Then write the specific handling code of the event in the actionPerformed method:
// Event handling of query button
if (e.getSource() == checkButton) {
// Query SQL
String sql =
"select bId,bBookName,bAuthor,bSex,bPrice,bBookDescription,btName from tb_book,tb_booktype where "
+ "tb_book.btId=tb_booktype.btId ";
// Judge whether the input is empty
if (simpleTools.isEmpty(bookNameTextField.getText()) && simpleTools.isEmpty(bookAuthorTextField.getText()) && bookTypeComboBox.getSelectedIndex() < 0) {
sql += " and 1=1;";
} else {
// Book name entered by the user
if (bookNameTextField.getText().length() > 0) {
sql += " and bBookName like '%" + bookNameTextField.getText() + "%'";
}
// User entered book author
if (bookAuthorTextField.getText().length() > 0) {
sql += " and bAuthor like '%" + bookAuthorTextField.getText() + "%'";
}
// User selected book category
String booktype = (String) bookTypeComboBox.getModel().getSelectedItem();
if (simpleTools.isEmpty(booktype)) {
sql += " and btName='" + booktype + "';";
}
}
// Get the table data according to the assembled SQL and convert it into a two-dimensional array
String[][] rowdatas = new BookDao().ListToArray(new BookDao().getRecordsDataBySql(sql));
// Header
String[] headers = {"number", "Title of book", "Book author", "Author gender", "Book price", "Book description", "Book category"};
// Repopulate table data model, refresh table
tableModel.setDataVector(rowdatas, headers);
}Run the program, the effect is as follows:
The event handling code of the reset button next to the query button is as follows:
// Event handling of reset button
if (e.getSource() == resetButton) {
// Query SQL
String sql =
"select bId,bBookName,bAuthor,bSex,bPrice,bBookDescription,btName from tb_book,tb_booktype where "
+ "tb_book.btId=tb_booktype.btId ";
String[][] rowdatas = new BookDao().ListToArray(new BookDao().getRecordsDataBySql(sql));
String[] headers = {"number", "Title of book", "Book author", "Author gender", "Book price", "Book description", "Book category"};
tableModel.setDataVector(rowdatas, headers);
componentTools.reset(bookNameTextField, bookAuthorTextField);
componentTools.reset(bookTypeComboBox);
}
Table selected row display data
To make the data in the selected row of the table appear in the text boxes below, first register the listener, and add the following code at the end of the BookManagePanel constructor method:
// Register event listeners for forms table.getSelectionModel().addListSelectionListener(this);
After registering the listener, write a specific event handling method. Change the code of valueChanged to the following:
@Override
public void valueChanged(ListSelectionEvent e) {
// Get the index of the selected row of the table
int getSelectedRowIndex = table.getSelectedRow();
// Judge whether the user selects a row in the table
if (getSelectedRowIndex == -1) {
refreshTable();
} else {
// If a row in the table is selected, the data of the selected row is populated into the following controls
idTextField.setText((String) table.getValueAt(getSelectedRowIndex, 0));
bookNameTextField2.setText((String) table.getValueAt(getSelectedRowIndex, 1));
boolean b = table.getValueAt(getSelectedRowIndex, 3).equals("male");
if (b) {
maleRadioButton.setSelected(true);
} else {
femaleRadioButton.setSelected(true);
}
priceTextField.setText((String) table.getValueAt(getSelectedRowIndex, 4));
bookAuthorTextField2.setText((String) table.getValueAt(getSelectedRowIndex, 2));
bookTypeComboBox2.setSelectedItem(table.getValueAt(getSelectedRowIndex, 6));
bookDescriptionTextArea.setText((String) table.getValueAt(getSelectedRowIndex, 5));
}
}And add the following methods to the BookManagePanel.java class to refresh the table:
/**
* Refresh table
*/
private void refreshTable() {
// Condition query SQL
String sql = "select bId,bBookName,bAuthor,bSex,bPrice,bBookDescription,btName from tb_book,tb_booktype " +
"where tb_book.btId=tb_booktype.btId;";
String[][] rowdatas = new BookDao().ListToArray(new BookDao().getRecordsDataBySql(sql));
String[] headers = {"number", "Title of book", "Book author", "Author gender", "Book price", "Book description", "Book category"};
// Repopulate table data, refresh table
tableModel.setDataVector(rowdatas, headers);
}The results of running the project are as follows:
The modification function of books
Realize the book modification function, update the data modified by the user into the database and refresh the display scrolling on the table, and add the following code in actionPerformed:
// Event handling of Modify button
if (e.getSource() == alterButton) {
String id = idTextField.getText();
String bookName = bookNameTextField2.getText();
String sex = "";
if (femaleRadioButton.isSelected()) {
sex = femaleRadioButton.getText();
} else {
sex = maleRadioButton.getText();
}
Float price = Float.parseFloat(priceTextField.getText());
String bookAuthor = bookAuthorTextField2.getText();
String bookType = (String) bookTypeComboBox2.getModel().getSelectedItem();
String bookDescription = bookDescriptionTextArea.getText();
// Assemble and modify SQL
String alterSQL =
"update tb_booktype,tb_book set bBookName='" + bookName + "',bAuthor='" + bookAuthor + "',bSex='" + sex + "'," + "bPrice" + "=" + price + "," + "tb_booktype.btName='" + bookType + "',bBookDescription='" + bookDescription + "' where " + "tb_book.btId=tb_booktype" + ".btId " + "and" + " bId=" + id + ";";
// Perform modification and return operation result
boolean isOK = new BookTypeDao().dataChange(alterSQL);
// Process the modification result
if (isOK) {
// If the modification is successful, refresh the table, reset the content of the control, and pop up the prompt box
refreshTable();
componentTools.reset(idTextField, bookNameTextField2, priceTextField, bookAuthorTextField2,
bookDescriptionTextArea);
componentTools.reset(sexButtonGroup);
componentTools.reset(bookTypeComboBox2);
JOptionPane.showMessageDialog(null, "Modification succeeded!");
} else {
// If the modification fails, a prompt box will pop up
JOptionPane.showMessageDialog(null, "Modification failed!");
}
}That is, the event processing of the Modify button also completes the modification function.
Run the program and modify the data. The results are as follows:
Deleting function of books
Delete the selected table row and add the following code in the actionPerformed method:
// Event handling of delete button
if (e.getSource() == deleteButton) {
// Get BOOK id to delete
String id = idTextField.getText();
// Delete SQL and reset the primary key of TB book
String sql1 = "set FOREIGN_KEY_CHECKS=0;";
String deleteSQL = "delete from tb_book where bId=" + id + ";";
String sql2 = "set FOREIGN_KEY_CHECKS=1;";
// Pop up the confirmation box to confirm whether the user wants to delete
int isOK = JOptionPane.showConfirmDialog(null, "Are you sure you want to delete?");
// If the user clicks the OK button
if (isOK == JOptionPane.OK_OPTION) {
// Execute delete SQL
new BookDao().dataChange(sql1);
boolean is = new BookDao().dataChange(deleteSQL);
new BookDao().dataChange(sql2);
// Judge the deletion result
if (is) {
// Refresh the table and reset the content of the control if the deletion is successful
refreshTable();
componentTools.reset(idTextField, bookNameTextField2, priceTextField, bookAuthorTextField2,
bookDescriptionTextArea);
componentTools.reset(sexButtonGroup);
componentTools.reset(bookTypeComboBox2);
} else {
// Prompt box will pop up if deletion fails
JOptionPane.showMessageDialog(null, "Delete failed!");
}
} else {
return;
}
}Add the code as follows:
Run the project and delete it. The result is as follows:
And the event handling code of the second reset button is as follows:
// Event handling for the second reset button
if (e.getSource() == resetButton2) {
// Reset control content
componentTools.reset(idTextField, bookNameTextField2, priceTextField, bookAuthorTextField2,
bookDescriptionTextArea);
componentTools.reset(sexButtonGroup);
componentTools.reset(bookTypeComboBox2);
}
You can search the WeChat public number [Java instance program] or scan the underlying two-dimensional code to pay more attention to the public number.
Note: in the background of public address reply [20200208] can get the source code of this section.
430 original articles published, 47 praised, 100000 visitors+