实验目的：掌握数据库编程技术。能正确连接数据库，能对数据库中信息进行查询、插入、删除、修改。在数据库中创建一张书目信息表，
包括书名、作者、出版社、出版日期、书号、价格字段。
设计一个GUI界面进行书目管理。
在该界面上有四个选项卡，分别是查询、插入、删除、修改。
1点击查询选项卡，
出现的界面上有
书名、作者、出版社、书号四个文本框，
一个按钮和一个只读文本区。
文本框内容可以为空，输入相应的查询信息后（例如根据书名查询可以仅输入书名），点击界面上的“查询”按钮，可以在界面下方的文本区中显示出符合条件的书目详细信息。
2点击插入选项卡，
出现的界面上有书名、作者、出版社、出版日期、书号、价格文本框，一个按钮。在文本框中输入信息后点击“插入”按钮，该书目信息插入数据库表中。
3点击删除选项卡，
出现的界面上有 书名文本框和一个按钮，输入书名后点击“删除”按钮，该书目信息从数据库表中删除。
4点击修改选项卡，
出现的界面上有书名、作者、出版社、出版日期、书号、价格文本框，一个按钮。输入的书名必须是已存在的，否则会弹出消息框显示出错信息。输入信息后，点击“修改”按钮，数据库表中的相应书目信息被修改为新值。
实验关键代码
import java.awt.Color;
import java.awt.Font;
import java.sql.ResultSet;
public class ManagerSystem {
static String sql = null;
static DBHelper db1 = null;
static ResultSet ret = null;
public ManagerSystem() {
MainUi ui = new MainUi();
ui.getContentPane().setForeground(Color.LIGHT_GRAY);
ui.getContentPane().setFont(new Font("黑体", Font.PLAIN, 16));
ui.getContentPane().setBackground(Color.LIGHT_GRAY);
ui.setBackground(Color.LIGHT_GRAY);
}
public static void main(String[] args) {
ManagerSystem system = new ManagerSystem();
}
}
import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Container;
import java.awt.Font;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTabbedPane;
public class MainUi extends JFrame {
public MainUi() {
super("学生成绩管理系统");
setBounds(500, 500, 900, 700);
addWindowListener(new WindowAdapter() {
public void windowClosing(WindowEvent e) {
super.windowClosing(e);
System.exit(0);
}
});
layoutUI();
setVisible(true);
}
private void layoutUI() {
JTabbedPane jTabbedPane = new JTabbedPane(JTabbedPane.TOP);
jTabbedPane.setFont(new Font("黑体", Font.PLAIN, 13));
jTabbedPane.setBackground(Color.GRAY);
Container container = this.getLayeredPane();
JPanel bookInsert = new BookInsert();
JPanel bookSearch = new BookSearch();
bookSearch.setForeground(Color.GRAY);
bookSearch.setBackground(Color.LIGHT_GRAY);
JPanel bookDelete = new BookDelete();
JPanel bookUpdate = new BookUpdate();
System.out.println("layoutUI()");
jTabbedPane.add(bookSearch, "查询");
jTabbedPane.add(bookInsert, "插入 ");
jTabbedPane.add(bookDelete, "删除");
jTabbedPane.add(bookUpdate, "更新");
JPanel titlePanel = new JPanel();
titlePanel.add(new JLabel("书目信息管理系统"));
container.setLayout(new BorderLayout());
container.add(titlePanel, BorderLayout.NORTH);
container.add(jTabbedPane, BorderLayout.CENTER);
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Statement;
public class DBHelper {
public static final String url = "jdbc:mysql://127.0.0.1/book_manager?characterEncoding=utf8&useSSL=true";
public static final String name = "com.mysql.jdbc.Driver";
public static final String user = "root";
public static final String password = "toor";
public static Connection conn = null;
private Statement statement = null;
ResultSet resultSet = null;
String sql = null;
// 此电脑\HUAWEI GRA-UL10\内部存储\tencent\MobileQQ\diskcache
public DBHelper(String sql) {
try {
conn = DriverManager.getConnection(url, user, password);// 获取连接
System.out.println(sql);
statement = (Statement) conn.createStatement();
this.sql = sql;
// int Res = statement.executeUpdate(sql);
// System.out.println(Res > 0 ? "插入数据成功" : "插入数据失败");
} catch (Exception e) {
e.printStackTrace();
}
}
public ResultSet search() {
try {
resultSet = statement.executeQuery(sql);
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return resultSet;
}
public int insert() {
int res = 0;
try {
res = statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
public void close() {
if (statement != null) {
try {
this.statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
this.conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Font;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.SwingConstants;
public class BookUpdate extends JPanel {
private JTextField bookNameTextField;
private JTextField bookAuthorTextField;
private JTextField bookPublishTextField;
private JTextField bookPublishDataTextField;
private JTextField bookNumberTextField;
private JTextField bookPriceTextField;
private JButton updateButton;
private JTextArea resultTextArea;
String sql = "";
public BookUpdate() {
JLabel bookNameLabel = new JLabel("书名：");
bookNameLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookNameLabel.setFont(new Font("黑体", Font.PLAIN, 13));
bookNameLabel.setBackground(Color.LIGHT_GRAY);
JLabel bookAuthorLabel = new JLabel("作者：");
bookAuthorLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookAuthorLabel.setFont(new Font("黑体", Font.PLAIN, 13));
JLabel bookPublishLabel = new JLabel("出版社：");
bookPublishLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookPublishLabel.setBackground(Color.LIGHT_GRAY);
bookPublishLabel.setFont(new Font("黑体", Font.PLAIN, 13));
JLabel bookPublishDataLabel = new JLabel("出版日期：");
bookPublishDataLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookPublishDataLabel.setBackground(Color.LIGHT_GRAY);
bookPublishDataLabel.setFont(new Font("黑体", Font.PLAIN, 13));
JLabel bookNumberLabel = new JLabel("书号：");
bookNumberLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookNumberLabel.setBackground(Color.LIGHT_GRAY);
bookNumberLabel.setFont(new Font("黑体", Font.PLAIN, 13));
JLabel bookPriceLabel = new JLabel("价钱：");
bookPriceLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookPriceLabel.setBackground(Color.LIGHT_GRAY);
bookPriceLabel.setFont(new Font("黑体", Font.PLAIN, 13));
// JLabel searchResultLabel = new JLabel("添加结果");
updateButton = new JButton("更新");
bookNameTextField = new JTextField(10);
bookAuthorTextField = new JTextField(10);
bookPublishDataTextField = new JTextField(10);
bookPublishTextField = new JTextField(10);
bookNumberTextField = new JTextField(10);
bookPriceTextField = new JTextField(10);
resultTextArea = new JTextArea(23, 40);
resultTextArea.setSelectedTextColor(Color.RED);
resultTextArea.setLineWrap(true); // 激活自动换行功能
resultTextArea.setWrapStyleWord(true); // 激活断行不断字功能
resultTextArea.setEnabled(false);
JPanel updatePanel = new JPanel();
JPanel updatePanel2 = new JPanel();
updatePanel2.setBackground(Color.WHITE);
BorderLayout layout = new BorderLayout();
setLayout(layout);
GridLayout gridlayout = new GridLayout(6, 2);
updatePanel.setLayout(gridlayout);
updatePanel.add(bookNameLabel);
updatePanel.add(bookNameTextField);
updatePanel.add(bookAuthorLabel);
updatePanel.add(bookAuthorTextField);
updatePanel.add(bookPublishLabel);
updatePanel.add(bookPublishTextField);
updatePanel.add(bookPublishDataLabel);
updatePanel.add(bookPublishDataTextField);
updatePanel.add(bookNumberLabel);
updatePanel.add(bookNumberTextField);
updatePanel.add(bookPriceLabel);
updatePanel.add(bookPriceTextField);
updatePanel2.add(updateButton);
add(updatePanel, BorderLayout.NORTH);
add(updatePanel2, BorderLayout.CENTER);
ButtonHandler updateButtonhandel = new ButtonHandler();
updateButton.addActionListener(updateButtonhandel);
}
private class ButtonHandler implements ActionListener {
@Override
public void actionPerformed(ActionEvent e) {
int count = 0;
System.out.println("updateButton");
sql = "update book set ";
if (!bookNameTextField.getText().equals("")) {
if (!bookAuthorTextField.getText().equals("")) {
if (count != 0) {
sql = sql + ", BookAuthor=" + "'" + bookAuthorTextField.getText() + "'";
} else {
sql = sql + " BookAuthor=" + "'" + bookAuthorTextField.getText() + "'";
}
count++;
}
if (!bookPublishTextField.getText().equals("")) {
if (count != 0) {
sql = sql + ", publish=" + "'" + bookPublishTextField.getText() + "'";
} else {
sql = sql + " publish=" + "'" + bookPublishTextField.getText() + "'";
}
count++;
}
if (!bookPublishDataTextField.getText().equals("")) {
if (count != 0) {
sql = sql + ", PublishData=" + "'" + bookPublishDataTextField.getText() + "'";
} else {
sql = sql + " PublishData=" + "'" + bookPublishDataTextField.getText() + "'";
}
count++;
}
if (!bookNumberTextField.getText().equals("")) {
if (count != 0) {
sql = sql + ", BookNumber=" + "'" + bookNumberTextField.getText() + "'";
} else {
sql = sql + " BookNumber=" + "'" + bookNumberTextField.getText() + "'";
}
count++;
}
if (!bookPriceTextField.getText().equals("")) {
if (count != 0) {
sql = sql + ", BookPrice=" + "'" + bookPriceTextField.getText() + "'";
} else {
sql = sql + " BookPrice=" + "'" + bookPriceTextField.getText() + "'";
}
count++;
}
sql = sql + " where BookName='" + bookNameTextField.getText() + "';";
DBHelper db = new DBHelper(sql);
System.out.println(sql);
if (db.insert() == 0) {
JOptionPane.showMessageDialog(null, "更新失败请重试");
} else {
JOptionPane.showMessageDialog(null, "更新成功！");
}
sql = "";
db.close();
count++;
} else {
JOptionPane.showMessageDialog(null, "请输入书名");
}
repaint();
validate();
}
}
}
import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
public class BookSearch extends JPanel {
private JTextField bookNameTextField;
private JTextField bookAuthorTextField;
private JTextField bookPublishTextField;
private JTextField bookNumberTextField;
private JButton searchButton;
private JButton searchAllButton;
private JTextArea resultTextArea;
String sql = "";
String result = "";
DBHelper db;
ResultSet resultSet = null;
JScrollPane showMessagePanel;
public BookSearch() {
JLabel bookNameLabel = new JLabel("书名：");
bookNameLabel.setBackground(Color.LIGHT_GRAY);
bookNameLabel.setFont(new Font("黑体", Font.PLAIN, 13));
JLabel bookAuthorLabel = new JLabel("作者：");
bookAuthorLabel.setBackground(Color.LIGHT_GRAY);
bookAuthorLabel.setFont(new Font("黑体", Font.PLAIN, 13));
JLabel bookPublishLabel = new JLabel("出版社：");
bookPublishLabel.setBackground(Color.LIGHT_GRAY);
bookPublishLabel.setFont(new Font("黑体", Font.PLAIN, 13));
JLabel bookNumberLabel = new JLabel("书号：");
bookNumberLabel.setBackground(Color.LIGHT_GRAY);
bookNumberLabel.setFont(new Font("黑体", Font.PLAIN, 13));
// JLabel searchResultLabel = new JLabel(" 查询结果");
searchButton = new JButton("查找");
searchAllButton = new JButton("查找全部");
bookNameTextField = new JTextField(10);
bookAuthorTextField = new JTextField(10);
bookPublishTextField = new JTextField(10);
bookNumberTextField = new JTextField(10);
resultTextArea = new JTextArea(24, 55);
resultTextArea.setForeground(new Color(255, 0, 0));
resultTextArea.setFont(new Font("黑体", Font.PLAIN, 17));
resultTextArea.setSelectedTextColor(Color.RED);
resultTextArea.setLineWrap(true); // 激活自动换行功能
resultTextArea.setWrapStyleWord(true); // 激活断行不断字功能
resultTextArea.setEnabled(false);
JPanel searchPanel = new JPanel();
searchPanel.setBackground(Color.LIGHT_GRAY);
showMessagePanel = new JScrollPane(resultTextArea);
BorderLayout layout = new BorderLayout();
setLayout(layout);
searchPanel.add(bookNameLabel);
searchPanel.add(bookNameTextField);
searchPanel.add(bookAuthorLabel);
searchPanel.add(bookAuthorTextField);
searchPanel.add(bookPublishLabel);
searchPanel.add(bookPublishTextField);
searchPanel.add(bookNumberLabel);
searchPanel.add(bookNumberTextField);
searchPanel.add(searchButton);
searchPanel.add(searchAllButton);
add(searchPanel, BorderLayout.NORTH);
add(showMessagePanel, BorderLayout.CENTER);
ButtonHandler searchButtonHandler = new ButtonHandler();
searchButton.addActionListener(searchButtonHandler);
searchAllButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
sql = "select * from book;";
result = "********书名******作者******出版社******出版日期******书籍编号******价钱****\n";
db = new DBHelper(sql);
resultSet = db.search();
try {
while (resultSet.next()) {
for (int i = 1; i <= 6; i++) {
result = result + "\t" + resultSet.getObject(i).toString();
}
result += "\n";
}
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
remove(showMessagePanel);
resultTextArea.setText(result);
showMessagePanel = new JScrollPane(resultTextArea);
add(showMessagePanel, BorderLayout.CENTER);
sql = "";
System.out.println("search success");
db.close();
repaint();
validate();
}
});
}
private class ButtonHandler implements ActionListener {
@Override
public void actionPerformed(ActionEvent e) {
int count = 0;
System.out.println("insertButton");
sql = "select * from book where ";
if (!bookNameTextField.getText().equals("")) {
if (count != 0) {
sql = sql + ", BookName=" + "'" + bookNameTextField.getText() + "'";
} else {
sql = sql + " BookName=" + "'" + bookNameTextField.getText() + "'";
}
count++;
}
if (!bookAuthorTextField.getText().equals("")) {
if (count != 0) {
sql = sql + ", BookAuthor=" + "'" + bookAuthorTextField.getText() + "'";
} else {
sql = sql + " BookAuthor=" + "'" + bookAuthorTextField.getText() + "'";
}
count++;
}
if (!bookPublishTextField.getText().equals("")) {
if (count != 0) {
sql = sql + ", publish=" + "'" + bookPublishTextField.getText() + "'";
} else {
sql = sql + " publish=" + "'" + bookPublishTextField.getText() + "'";
}
count++;
}
if (!bookNumberTextField.getText().equals("")) {
if (count != 0) {
sql = sql + ", BookNumber=" + "'" + bookNumberTextField.getText() + "'";
} else {
sql = sql + " BookNumber=" + "'" + bookNumberTextField.getText() + "'";
}
count++;
}
result = "********书名******作者******出版社******出版日期******书籍编号******价钱****\n";
sql += ";";
System.out.println("要执行的sql语句:" + sql);
db = new DBHelper(sql);
resultSet = db.search();
try {
while (resultSet.next()) {
for (int i = 1; i <= 6; i++) {
result = result + "\t" + resultSet.getObject(i).toString();
}
result += "\n";
}
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
remove(showMessagePanel);
resultTextArea.setText(result);
showMessagePanel = new JScrollPane(resultTextArea);
add(showMessagePanel, BorderLayout.CENTER);
sql = "";
System.out.println("search success");
db.close();
repaint();
validate();
}
}
}
import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Font;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.SwingConstants;
public class BookInsert extends JPanel {
private JTextField bookNameTextField;
private JTextField bookAuthorTextField;
private JTextField bookPublishTextField;
private JTextField bookPublishDataTextField;
private JTextField bookNumberTextField;
private JTextField bookPriceTextField;
private JButton insertButton;
private JTextArea resultTextArea;
String sql = "";
public BookInsert() {
JLabel bookNameLabel = new JLabel("书名：");
bookNameLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookNameLabel.setFont(new Font("黑体", Font.PLAIN, 13));
bookNameLabel.setBackground(Color.LIGHT_GRAY);
JLabel bookAuthorLabel = new JLabel("作者：");
bookAuthorLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookAuthorLabel.setFont(new Font("黑体", Font.PLAIN, 13));
bookAuthorLabel.setBackground(Color.LIGHT_GRAY);
JLabel bookPublishLabel = new JLabel("出版社：");
bookPublishLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookPublishLabel.setFont(new Font("黑体", Font.PLAIN, 13));
bookPublishLabel.setBackground(Color.LIGHT_GRAY);
JLabel bookPublishDataLabel = new JLabel("出版社日期：");
bookPublishDataLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookPublishDataLabel.setFont(new Font("黑体", Font.PLAIN, 13));
bookPublishDataLabel.setBackground(Color.LIGHT_GRAY);
JLabel bookNumberLabel = new JLabel("书号：");
bookNumberLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookNumberLabel.setFont(new Font("黑体", Font.PLAIN, 13));
bookNumberLabel.setBackground(Color.LIGHT_GRAY);
JLabel bookPriceLabel = new JLabel("价钱：");
bookPriceLabel.setHorizontalAlignment(SwingConstants.RIGHT);
bookPriceLabel.setFont(new Font("黑体", Font.PLAIN, 13));
bookPriceLabel.setBackground(Color.LIGHT_GRAY);
insertButton = new JButton("添加");
bookNameTextField = new JTextField(10);
bookAuthorTextField = new JTextField(10);
bookPublishTextField = new JTextField(10);
bookPublishDataTextField = new JTextField(10);
bookNumberTextField = new JTextField(10);
bookPriceTextField = new JTextField(10);
resultTextArea = new JTextArea(23, 40);
resultTextArea.setSelectedTextColor(Color.RED);
resultTextArea.setLineWrap(true); // 激活自动换行功能
resultTextArea.setWrapStyleWord(true); // 激活断行不断字功能
resultTextArea.setEnabled(false);
JPanel insertPanel = new JPanel();
JPanel insertPanel2 = new JPanel();
insertPanel2.setBackground(Color.LIGHT_GRAY);
BorderLayout layout = new BorderLayout();
setLayout(layout);
GridLayout gridlayout = new GridLayout(6, 2);
insertPanel.setLayout(gridlayout);
insertPanel.add(bookNameLabel);
insertPanel.add(bookNameTextField);
insertPanel.add(bookAuthorLabel);
insertPanel.add(bookAuthorTextField);
insertPanel.add(bookPublishLabel);
insertPanel.add(bookPublishTextField);
insertPanel.add(bookPublishDataLabel);
insertPanel.add(bookPublishDataTextField);
insertPanel.add(bookNumberLabel);
insertPanel.add(bookNumberTextField);
insertPanel.add(bookPriceLabel);
insertPanel.add(bookPriceTextField);
insertPanel2.add(insertButton);
add(insertPanel, BorderLayout.NORTH);
add(insertPanel2, BorderLayout.CENTER);
ButtonHandler insertButtonhandel = new ButtonHandler();
insertButton.addActionListener(insertButtonhandel);
}
private class ButtonHandler implements ActionListener {
@Override
public void actionPerformed(ActionEvent e) {
System.out.println("insertButton");
sql = "insert book values('" + bookNameTextField.getText() + "','" + bookAuthorTextField.getText() + "','"
+ bookPublishTextField.getText() + "','" + bookPublishDataTextField.getText() + "','"
+ bookNumberTextField.getText() + "','" + bookPriceTextField.getText() + "');";
System.out.println(sql);
if (!bookNameTextField.getText().equals("") && !bookAuthorTextField.getText().equals("")
&& !bookPublishTextField.getText().equals("") && !bookPublishDataTextField.getText().equals("")
&& !bookPriceTextField.getText().equals("") && !bookNumberTextField.getText().equals("")
&& !bookPriceTextField.getText().equals("")) {
DBHelper db = new DBHelper(sql);
if (db.insert() == 0) {
JOptionPane.showMessageDialog(null, "插入失败请重试");
} else {
JOptionPane.showMessageDialog(null, "插入成功！");
}
sql = "";
db.close();
}
repaint();
validate();
}
}
}
import java.awt.Color;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;
public class BookDelete extends JPanel {
private JTextField bookNameTextField;
private JButton deleteButton;
String sql = "";
public BookDelete() {
setForeground(Color.LIGHT_GRAY);
JLabel bookNameLabel = new JLabel("书名：");
bookNameLabel.setFont(new Font("黑体", Font.PLAIN, 13));
bookNameTextField = new JTextField(10);
bookNameTextField.setFont(new Font("黑体", Font.PLAIN, 13));
deleteButton = new JButton("删除");
deleteButton.setFont(new Font("黑体", Font.PLAIN, 13));
add(bookNameLabel);
add(bookNameTextField);
add(deleteButton);
ButtonHandler searchButtonHandler = new ButtonHandler();
deleteButton.addActionListener(searchButtonHandler);
}
private class ButtonHandler implements ActionListener {
@Override
public void actionPerformed(ActionEvent e) {
System.out.println("insertButton");
if (!bookNameTextField.getText().equals("")) {
sql = "delete from book where BookName='" + bookNameTextField.getText() + "'";
System.out.println(sql);
DBHelper db = new DBHelper(sql);
if (db.insert() == 0) {
JOptionPane.showMessageDialog(null, "要删除的书名不存在请重试");
} else {
JOptionPane.showMessageDialog(null, "已删除");
}
sql = "";
db.close();
}
repaint();
validate();
}
}
}
1385
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
