The purpose of this article is to master database programming technology through the library management system, be able to correctly connect to the database, and be able to query, insert, delete and modify the information in the database.
Content: Create a bibliographic information table in the database, including the title, author, publishing house, publication date, book number, and price fields. Design a GUI interface for bibliography management. There are four tabs on this interface, namely query, insert, delete, and modify. Click the Query tab, and the interface that appears includes four text boxes: book title, author, publisher, book number, a button and a read-only text area. The content of the text box can be empty. After entering the corresponding query information (for example, you can only enter the title according to the book title), click the "Query" button on the interface to display the detailed information of the bibliography that meets the criteria in the text area below the interface. Click the Insert tab, and the interface that appears will include the book title, author, publishing house, publication date, book number, price text box, and a button. After entering information in the text box, click the "Insert" button and the bibliographic information is inserted into the database table. Click the Delete tab. There is a text box for the title and a button on the interface that appears. After entering the title, click the "Delete" button. The bibliographic information is deleted from the database table. Click the Modify tab, and the interface that appears includes the book title, author, publishing house, publication date, book number, price text box, and a button. The entered book title must exist, otherwise a message box will pop up and display an error message. After entering the information, click the "Modify" button, and the corresponding bibliographic information in the database table is modified to the new value.
Source code:
BookInfo.java
* Project name: Book Management System* Version: 1.0 * Creator: Zhang Junqiang* Creation time: 2016/5/26 * */ package librarySystem; import java.awt.*; import javax.swing.*; import java.awt.event.*; import java.sql.*; @SuppressWarnings("serial") public class BookInfo extends JFrame implements ActionListener{ //Control on the protagonist face private JLabel inputLabel; private JTextField inputText; private JButton searchBut; private JTable bookTable; private JScrollPane bookScroll; private JButton addBut; private JButton modifyBut; private JButton deleteBut; private JButton refreshBut; private BookTableModel bookTableModel; public static void main(String[] args) throws SQLException { // TODO Auto-generated method stub BookInfo bookInfo=new BookInfo(); bookInfo.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); bookInfo.setBounds(350, 150, 600, 400); bookInfo.setVisible(true); // bookInfo.importSQL();//Export data bookInfo.setMinWindowLayout();//Set data} public BookInfo() throws SQLException{ //Create the control on the main interface inputLabel=new JLabel("Please enter the book title:"); inputText=new JTextField(10); searchBut=new JButton("Query"); bookTableModel=new BookTableModel(); bookTable=new JTable(bookTableModel); bookScroll=new JScrollPane(bookTable); addBut=new JButton("Add"); modifyBut=new JButton("Modify"); deleteBut=new JButton("Delete"); refreshBut=new JButton("Refresh"); searchBut.addActionListener(this); addBut.addActionListener(this); refreshBut.addActionListener(this); modifyBut.addActionListener(this); deleteBut.addActionListener(this); } void setMinWindowLayout(){ //Main interface layout Container con1=new Container(); con1.setLayout(new FlowLayout()); con1.add(inputLabel); con1.add(inputText); con1.add(searchBut); con1.add(refreshBut); Container con2=new Container(); con2.setLayout(new FlowLayout()); con2.add(addBut); con2.add(modifyBut); con2.add(deleteBut); this.setLayout(new BorderLayout()); this.add(con1,BorderLayout.NORTH); this.add(bookScroll,BorderLayout.CENTER); this.add(con2,BorderLayout.SOUTH); this.validate(); } @Override public void actionPerformed(ActionEvent e) { // TODO Auto-generated method stub if(e.getSource()==searchBut){ if(!this.inputText.getText().equals("")){ String bookName=this.inputText.getText(); String sql="SELECT * FROM book_info WHERE book_name ='"+bookName+"'"; try { bookTableModel=new BookTableModel(sql); bookTable.setModel(bookTableModel); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } }else{ JOptionPane.showMessageDialog(this,"Input cannot be empty", "Prompt",JOptionPane.PLAIN_MESSAGE); } } else if(e.getSource()==addBut){ @SuppressWarnings("unused") AddBookDialog addWin=new AddBookDialog(this,"Add Book",true); this.refreshTable(); } else if(e.getSource()==refreshBut){ this.refreshTable(); } else if(e.getSource()==deleteBut){ int rowNum=bookTable.getSelectedRow(); if(rowNum<0||rowNum>bookTable.getRowCount()){ JOptionPane.showMessageDialog(this,"Unselected", "Tip",JOptionPane.PLAIN_MESSAGE); } else{ //System.out.print(bookName); int n = JOptionPane.showConfirmDialog(null, "Confirm deletion?", "Confirm deletion box", JOptionPane.YES_NO_OPTION); if (n == JOptionPane.YES_OPTION) { String bookNum=(String) bookTable.getValueAt(rowNum, 0); String sql="DELETE FROM book_info WHERE book_num= '"+bookNum+"'"; bookTableModel.deleteBook(sql); this.refreshTable(); JOptionPane.showMessageDialog(this,"DeleteSuccess", "Tip",JOptionPane.PLAIN_MESSAGE); } else if (n == JOptionPane.NO_OPTION) { return; } } } else if(e.getSource()==modifyBut){ bookTable.setModel(bookTableModel); int rowNum=bookTable.getSelectedRow(); if(rowNum<0||rowNum>bookTable.getRowCount()){ JOptionPane.showMessageDialog(this,"Uncheck", "Prompt",JOptionPane.PLAIN_MESSAGE); } else{ @SuppressWarnings("unused") ModifyBook modifyWin=new ModifyBook(this,"Modify information",true,bookTableModel,rowNum); this.refreshTable(); } } } public void refreshTable(){ BookTableModel searchBook; try { searchBook = new BookTableModel("SELECT * FROM book_info"); bookTable.setModel(searchBook); bookTableModel=searchBook; } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } BookTableModel.java
package librarySystem; import java.sql.*; import java.util.*; /* * Book table model* */ import javax.swing.table.*; @SuppressWarnings("serial") public class BookTableModel extends AbstractTableModel{ //Elements of table private Vector<Vector<String>> rowData; private Vector<String> colName; // Database private PreparedStatement stmt; private ResultSet result; public BookTableModel(String sql) throws SQLException{ this.initData(sql); } public BookTableModel() throws SQLException{ this.initData("SELECT * FROM book_info"); } public void initData(String sql) throws SQLException{ setRowData(new Vector<Vector<String>>()); setColName(new Vector<String>()); getColName().add("Book No."); getColName().add("Book Title"); getColName().add("Book Title"); getColName().add("Author"); getColName().add("Publisher"); getColName().add("Publishing Time"); getColName().add("Price"); /* * Database import* */ try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } String url= "jdbc:mysql://localhost:3306/device"; String user="root"; String password="zjq1314520"; Connection con=DriverManager.getConnection(url,user,password); stmt = con.prepareStatement(sql); result=stmt.executeQuery(); importSQL(); } void importSQL() throws SQLException{ // TODO Auto-generated method stub @SuppressWarnings("unused") boolean signNull=true; while(result.next()){ Vector<String> item=new Vector<String>(); for(int i=1;i<7;i++){ item.add(result.getString(i)); } getRowData().add(item); signNull=false; } result.close(); } @Override public int getColumnCount() {//Get the number of columns// TODO Auto-generated method stub return this.colName.size(); } @Override public int getRowCount() {//Get the number of rows// TODO Auto-generated method stub return this.rowData.size(); } @Override public int getRowCount() {//Get the number of rows// TODO Auto-generated method stub return this.rowData.size(); } @Override public Object getValueAt(int row, int col) {//Get data of a row and a column// TODO Auto-generated method stub return (this.rowData.get(row)).get(col); } @Override public String getColumnName(int column) { // TODO Auto-generated method stub return this.colName.get(column); } public Vector<Vector<String>> getRowData() { return rowData; } public void setRowData(Vector<Vector<String>> rowData) { this.rowData = rowData; } public Vector<String> getColName() { return colName; } public void setColName(Vector<String> colName) { this.colName = colName; } public void addBook(String sql){ try { stmt.executeUpdate(sql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } // initData("SELECT * FROM book_info"); } public void deleteBook(String sql){ try { stmt.executeUpdate(sql); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } AddBookDialog.java
package librarySystem; import java.awt.*; import java.awt.event.*; import java.sql.SQLException; import javax.swing.*; @SuppressWarnings("serial") public class AddBookDialog extends JDialog implements ActionListener{ private JLabel bookNumLabel; private JLabel bookNameLabel; private JLabel bookWriterLabel; private JLabel bookPublishLabel; private JLabel bookPriceLabel; private JLabel bookTimeLabel; private JTextField bookNumText; private JTextField bookNameText; private JTextField bookWriterText; private JTextField bookPublishText; private JTextField bookPriceText; private JTextField bookTimeText; private JButton submitBut; private JButton cancelBut; public AddBookDialog(Frame owner,String title,boolean model){ //The parent window, window name, is it a mode window super(owner, title, model); bookNumLabel=new JLabel("Book number:"); bookNameLabel=new JLabel("Book title:"); bookWriterLabel=new JLabel("Author:"); bookPublishLabel=new JLabel("Publisher:"); bookPriceLabel=new JLabel("Price:"); bookTimeLabel=new JLabel("Publishing time:"); bookNumText=new JTextField(10); bookNameText=new JTextField(10); bookWriterText=new JTextField(10); bookPublishText=new JTextField(10); bookPriceText=new JTextField(10); bookTimeText=new JTextField(9); submitBut=new JButton("Cancel"); cancelBut=new JButton("Cancel"); submitBut.addActionListener(this); cancelBut.addActionListener(this); this.setBounds(350,150,400,260); this.setResizable(false); this.setLayout(new BorderLayout()); initLayout(); } public void initLayout(){ Container[] con1=new Container[6]; for(int i=0;i<6;i++) con1[i]=new Container(); con1[0].setLayout(new FlowLayout()); con1[0].add(bookNumLabel); con1[0].add(bookNumText); con1[1].setLayout(new FlowLayout()); con1[1].add(bookNameLabel); con1[1].add(bookNameText); con1[2].setLayout(new FlowLayout()); con1[2].add(bookWriterLabel); con1[2].add(bookWriterText); con1[3].setLayout(new FlowLayout()); con1[3].add(bookPublishLabel); con1[3].add(bookPublishText); con1[4].setLayout(new FlowLayout()); con1[4].add(bookPriceLabel); con1[4].add(bookPriceText); con1[5].setLayout(new FlowLayout()); con1[5].add(bookTimeLabel); con1[5].add(bookTimeText); Container con2=new Container(); con2.setLayout(new BorderLayout()); con2.add(con1[0],BorderLayout.NORTH); con2.add(con1[1],BorderLayout.CENTER); con2.add(con1[2],BorderLayout.SOUTH); Container con3=new Container(); con3.setLayout(new BorderLayout()); con3.add(con1[3],BorderLayout.NORTH); con3.add(con1[4],BorderLayout.CENTER); con3.add(con1[5],BorderLayout.SOUTH); Container con4=new Container(); con4.setLayout(new FlowLayout()); con4.add(submitBut); con4.add(cancelBut); Container con5=new Container(); con5.setLayout(new BorderLayout()); con5.add(con2,BorderLayout.NORTH); con5.add(con3,BorderLayout.CENTER); con5.add(con4,BorderLayout.SOUTH); this.add(con5,BorderLayout.CENTER); this.validate(); this.setVisible(true); } @Override public void actionPerformed(ActionEvent e) { // TODO Auto-generated method stub if(e.getSource()==submitBut){ if(bookNumText.getText().equals("")||bookNameText.getText().equals("")|| bookWriterText.getText().equals("")|| bookPriceText.getText().equals("")|| bookPriceText.getText().equals("")|| bookTimeText.getText().equals("")){ //System.out.println("Input failed"); JOptionPane.showMessageDialog(this,"Input cannot be empty", "Prompt",JOptionPane.PLAIN_MESSAGE); } else{ //System.out.println("Input successful"); String sql="insert into " + "book_info(book_num,book_name,book_writer,publish_house,book_price,publish_time)" + "values('"+bookNumText.getText()+"','"+bookNameText.getText()+"','"+bookWriterText.getText()+"','"+bookPublishText.getText()+"','"+bookPriceText.getText()+"','"+bookTimeText.getText()+"'); try { BookTableModel book=new BookTableModel(); book.addBook(sql); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } JOptionPane.showMessageDialog(this,"Add Success", "Tip",JOptionPane.PLAIN_MESSAGE); this.setVisible(false); } } if(e.getSource()==cancelBut){ this.setVisible(false); } } } ModifyBook.java
package librarySystem; import java.awt.*; import java.awt.event.*; import java.sql.SQLException; import javax.swing.*; @SuppressWarnings("serial") public class ModifyBook extends JDialog implements ActionListener{ private JLabel bookNumLabel; private JLabel bookNameLabel; private JLabel bookWriterLabel; private JLabel bookPublishLabel; private JLabel bookPriceLabel; private JLabel bookTimeLabel; private JTextField bookNumText; private JTextField bookNameText; private JTextField bookWriterText; private JTextField bookPublishText; private JTextField bookPriceText; private JTextField bookTimeText; private JButton submitBut; private JButton cancelBut; private BookTableModel bookModel; private int rowNum; public ModifyBook(Frame owner,String title,boolean type,BookTableModel model,int row){ super(owner, title,type); bookModel=model; rowNum=row; bookNumLabel=new JLabel("Book number:"); bookNameLabel=new JLabel("Book title:"); bookWriterLabel=new JLabel("Author:"); bookPublishLabel=new JLabel("Publisher:"); bookPriceLabel=new JLabel("Price:"); bookTimeLabel=new JLabel("Publishing time:"); bookNumText=new JTextField(10); bookNameText=new JTextField(10); bookWriterText=new JTextField(10); bookPublishText=new JTextField(10); bookPriceText=new JTextField(10); bookTimeText=new JTextField(9); submitBut=new JButton("Confirm Modification"); cancelBut=new JButton("Cancel"); submitBut.addActionListener(this); cancelBut.addActionListener(this); this.setBounds(350,150,400,260); this.setResizable(false); this.setLayout(new BorderLayout()); this.setValue(); this.initLayout(); } public void initLayout(){ Container[] con1=new Container[6]; for(int i=0;i<6;i++) con1[i]=new Container(); con1[0].setLayout(new FlowLayout()); con1[0].add(bookNumLabel); con1[0].add(bookNumText); con1[1].setLayout(new FlowLayout()); con1[1].add(bookNameLabel); con1[1].add(bookNameText); con1[2].setLayout(new FlowLayout()); con1[2].add(bookWriterLabel); con1[2].add(bookWriterText); con1[3].setLayout(new FlowLayout()); con1[3].add(bookPublishLabel); con1[3].add(bookPublishText); con1[4].setLayout(new FlowLayout()); con1[4].add(bookPriceLabel); con1[4].add(bookPriceText); con1[5].setLayout(new FlowLayout()); con1[5].add(bookTimeLabel); con1[5].add(bookTimeText); Container con2=new Container(); con2.setLayout(new BorderLayout()); con2.add(con1[0],BorderLayout.NORTH); con2.add(con1[1],BorderLayout.CENTER); con2.add(con1[2],BorderLayout.SOUTH); Container con3=new Container(); con3.setLayout(new BorderLayout()); con3.add(con1[3],BorderLayout.NORTH); con3.add(con1[4],BorderLayout.CENTER); con3.add(con1[5],BorderLayout.SOUTH); Container con4=new Container(); con4.setLayout(new FlowLayout()); con4.add(submitBut); con4.add(cancelBut); Container con5=new Container(); con5.setLayout(new BorderLayout()); con5.add(con2,BorderLayout.NORTH); con5.add(con3,BorderLayout.CENTER); con5.add(con4,BorderLayout.SOUTH); this.add(con5,BorderLayout.CENTER); this.validate(); this.setVisible(true); } public void setValue(){ this.bookNumText.setText((String) bookModel.getValueAt(rowNum, 0)); this.bookNumText.setEditable(false); this.bookNameText.setText((String) bookModel.getValueAt(rowNum, 1)); this.bookWriterText.setText((String) bookModel.getValueAt(rowNum, 2)); this.bookPublishText.setText((String) bookModel.getValueAt(rowNum, 3)); this.bookTimeText.setText((String) bookModel.getValueAt(rowNum, 4)); this.bookPriceText.setText((String) bookModel.getValueAt(rowNum, 5)); this.validate(); } @Override public void actionPerformed(ActionEvent e) { // System.out.println(bookPriceText.getText()); // TODO Auto-generated method stub if(e.getSource()==submitBut){ if(bookNumText.getText().equals("")||bookNameText.getText().equals("")|| bookWriterText.getText().equals("")|| bookWriterText.getText().equals("")|| bookPublishText.getText().equals("")|| bookPriceText.getText().equals("Input failed"); JOptionPane.showMessageDialog(this,"Modification cannot be empty", "Prompt",JOptionPane.PLAIN_MESSAGE); } else{ int n = JOptionPane.showConfirmDialog(null, "Confirm modification?", "Confirm modification box", JOptionPane.YES_NO_OPTION); if (n == JOptionPane.YES_OPTION) { String sql="UPDATE book_info SET book_name ='"+bookNameText.getText()+"', book_writer= '"+bookWriterText.getText()+"',publish_house='"+bookPublishText.getText()+"',book_price='"+bookPriceText.getText()+"',publish_time='"+bookTimeText.getText()+"' WHERE book_num = '"+bookNumText.getText()+"' "; try { BookTableModel book=new BookTableModel(); book.addBook(sql); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } JOptionPane.showMessageDialog(this,"Modification succeeded", "Prompt",JOptionPane.PLAIN_MESSAGE); this.setVisible(false); } else if (n == JOptionPane.NO_OPTION) { return; } } } if(e.getSource()==cancelBut){ this.setVisible(false); } } } } Program running results:
Main interface:
Query interface:
Add a book interface:
Modify the interface:
Delete operation:
Database interface:
For more information about the management system, please click "Management System Special Topic" to learn
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.