建立一个数据库，它可以方便地记录某个高速站口的人员信息。
一，代码逻辑
1，建立一个数据生产类Person；
2，建立一个SQLiteTool类，用来完成与数据库的链接；
3，建立一个可视化界面（GUI编程）类UIDesignDemo;
二，编写过程遇到的问题及解决思路
1，SQL语句错误：一是单词拼写错误，二是语法错误；
2，数据库找不到异常：解决办法是把数据库建立在同一个工程项目下；
3，数据记录错误：原因是把数据的顺序弄错了；
三，在工程项目中导入sqlite数据库
这部分读者自己去查一下吧。
代码演示：
一：在同一个工程项目下建表
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/*
* 这儿的代码只能运行一次，在这个工程项目下不能再执行这儿的代码
* 表名为Person
*/
public class Demo_建表 {
public static void main(String [] args) {
Connection conn = null;
Statement stmt = null;
String sql = "CREATE TABLE PERSON"+"(ID INT PRIMARY KEY NOT NULL,"
+ "NAME CHAR(50) NOT NULL,"+"STARTPLACE CHAR(50) NOT NULL,"
+"ENDPLACE CHAR(50) NOT NULL,"+"DATE INT NOT NULL)";
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:Person.db");
stmt = conn.createStatement();
stmt.executeUpdate(sql);
System.out.println("建表成功");
conn.close();
}catch(Exception e) {
System.exit(0);
}
}
}二：定义数据生产类
public class Person {
private int id;//为了防止数据溢出，身份证也使用日期的形式，如 20210312，长度不得超过8
private String name;
private String startPlace;
private String endPlace;
private int date; //注意日期的写法格式为 20220112 代表2022年1月12日
public Person() {
id = 0;
name = " ";
startPlace = " ";
endPlace = " ";
date = 0;
}
public Person(int id, String name, String startPlace, String endPlace,int date) {
this.id = id;
this.name = name;
this.startPlace = startPlace;
this.endPlace = endPlace;
this.date = date;
}
public int getDate() {
return date;
}
public void setDate(int date) {
this.date = date;
}
public String getEndPlace() {
return endPlace;
}
public void setEndPlace(String endPlace) {
this.endPlace = endPlace;
}
public String getStartPlace() {
return startPlace;
}
public void setStartPlace(String startPlace) {
this.startPlace = startPlace;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
}
三：链接数据库的工具类SQLiteTool类
import java.sql.*;
/*这儿我把自己遇到的问题都记录了下来
* 问题：1,为什么建这个表只有在当前项目下才能被找到，如果把这个表健在其他项目下，就会发生“SQL error,not found table”异常？？？
* 2,为什么查询结果打印出来的信息与建表的格式对不上，而且没有打印出名字？？？ 查询结果：(0 20220322 贵阳 德江 20220322)
* 3，为什么删除不了人员信息？？？
*
* 问题2的原因:原来的insert方法中参数顺序设置错误，这儿是错误的顺序
* stmt.setString(1,person.getName());
stmt.setInt(2,person.getId());
*
* 问题3的原因：SQL语句错误
*
*/
public class SQLiteTool {
private String driver = "org.sqlite.JDBC";
private String name = "jdbc:sqlite:Person.db";
private Connection conn = null;
private PreparedStatement stmt = null;
//链接数据库
public SQLiteTool(){
try{
Class.forName(driver);
conn = DriverManager.getConnection(name);
conn.setAutoCommit(false);//取消自动提交修改
} catch (Exception e) {
e.printStackTrace();
}
}
//增
public boolean insert(Person person){
boolean flag = false;
String sql = "INSERT INTO Person VALUES(?,?,?,?,?)";
try{
stmt = conn.prepareStatement(sql);
stmt.setInt(1,person.getId());
stmt.setString(2,person.getName());
stmt.setString(3,person.getStartPlace());
stmt.setString(4,person.getEndPlace());
stmt.setInt(5,person.getDate());
if (stmt.executeUpdate()!=0){
flag = true ;
System.out.println("添加成功！");
}
stmt.close();
conn.commit();//提交修改
} catch (Exception e) {
e.printStackTrace();
}
return flag;
}
//模糊查询
public void selectById(String name){
ResultSet set = null;
String sql = "SELECT*FROM Person WHERE NAME LIKE ?";
try{
stmt = conn.prepareStatement(sql);
stmt.setString(1,"%"+name+"%"); // %可理解为任意字符
set = stmt.executeQuery();
while(set.next()) {
int result_1 = set.getInt("ID");
String result_2 = set.getString("NAME");
String result_3 = set.getString("STARTPLACE");
String result_4 = set.getString("ENDPLACE");
int result_5 = set.getInt("DATE");
System.out.println(result_1+" "+result_2+" "+result_3+" "+result_4+" "+result_5);
}
set.close();
conn.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
//删
public boolean deleteByID(int id) {
boolean flag = false;
String sql = "DELETE FROM Person WHERE ID LIKE ?";
try {
stmt = conn.prepareStatement(sql);
stmt.setInt(1,id);
if(stmt.executeUpdate()!=0) {
flag = true;
System.out.println("成功删除身份证为"+id+"的人员信息！");
}
stmt.close();
conn.commit();
}catch(Exception e) {
System.out.println(e.getMessage());
}
return flag;
}
}
四：UIDesignDemo类（GUI编程）
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
public class UIDesignDemo {
JFrame frame;
JPanel panel;
JButton insertButton;
JButton queryButton;
JButton deleteButton;
/*
GUI编程中容易出现的bug有：
1，组件无法显示，原因：组件必须先实例后再按主次添加，如先frame。add()然后是其他组件
2，Swing和AWT组件不要混用，如，框架是Frame,但组件是JPanel或其他
3.运行后会发现组件无法显示，要先点击一下最小化按钮再点开就可以了（这个原因与1的不同）
*/
//main方法
public static void main(String [] args) {
new UIDesignDemo();
}
public UIDesignDemo(){
SQLiteTool sqlitetool = new SQLiteTool();
frame = new JFrame();
frame.setSize(400,500);
frame.setLocationRelativeTo(null);
frame.setVisible(true);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setLayout(new BorderLayout(10,10));
frame.setResizable(false);
panel = new JPanel();
panel.setLayout(null); //空白布局
insertButton = new JButton("添加人员信息");
insertButton.setBounds(80, 80, 200, 70);
queryButton = new JButton("查询人员信息");
queryButton.setBounds(80, 160, 200, 70);
deleteButton = new JButton("删除人员信息");
deleteButton.setBounds(80,240,200,70);
JLabel label = new JLabel("疫情期间信息记录系统",JLabel.CENTER);
label.setFont(new Font("楷体",Font.BOLD,20));
frame.add(panel,BorderLayout.CENTER);
frame.add(label, BorderLayout.NORTH);
panel.add(insertButton);
panel.add(queryButton);
panel.add(deleteButton);
//事件监听
insertButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
new insertFrame();
}
});
queryButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
new queryFrame();
}
});
deleteButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
new deleteFrame();
}
});
}
}
class insertFrame {
SQLiteTool sqliteTool;
JFrame frame;
JButton yesButton;
JButton noButton;
public insertFrame(){
sqliteTool = new SQLiteTool();
frame = new JFrame();
frame.setVisible(true);
frame.setSize(400,500);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setLocationRelativeTo(null);
frame.setLayout(new BorderLayout());
frame.setResizable(false);
JPanel textPanel = new JPanel();
textPanel.setLayout(new BorderLayout());
JPanel labelPanel = new JPanel();
labelPanel.setLayout(new GridLayout(5,0,10,20));
JPanel buttonPanel = new JPanel();
buttonPanel.setLayout(new FlowLayout());
JPanel fieldPanel = new JPanel();
fieldPanel.setLayout(new GridLayout(5,0,10,20));
JTextField idText = new JTextField();
JTextField nameText = new JTextField();
JTextField startText = new JTextField();
JTextField endText = new JTextField();
JTextField dateText = new JTextField();
JLabel label = new JLabel("请输入信息",JLabel.CENTER);
label.setFont(new Font("楷体",Font.BOLD,20));
JLabel idLabel = new JLabel("身份证");
idLabel.setFont(new Font("楷体",Font.BOLD,20));
JLabel nameLabel = new JLabel("姓名");
nameLabel.setFont(new Font("楷体",Font.BOLD,20));
JLabel startLabel = new JLabel("出发地");
startLabel.setFont(new Font("楷体",Font.BOLD,20));
JLabel endLabel = new JLabel("目的地");
endLabel.setFont(new Font("楷体",Font.BOLD,20));
JLabel dataLabel = new JLabel("日期");
dataLabel.setFont(new Font("楷体",Font.BOLD,20));
JButton yesButton = new JButton("确认");
JButton noButton = new JButton("取消");
frame.add(textPanel,BorderLayout.CENTER);
frame.add(label,BorderLayout.NORTH);
frame.add(buttonPanel,BorderLayout.SOUTH);
buttonPanel.add(noButton);
buttonPanel.add(yesButton);
textPanel.add(labelPanel,BorderLayout.WEST);
textPanel.add(fieldPanel,BorderLayout.CENTER);
fieldPanel.add(idText);
fieldPanel.add(nameText);
fieldPanel.add(startText);
fieldPanel.add(endText);
fieldPanel.add(dateText);
labelPanel.add(idLabel);
labelPanel.add(nameLabel);
labelPanel.add(startLabel );
labelPanel.add(endLabel);
labelPanel.add(dataLabel);
yesButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
sqliteTool.insert(new Person(
Integer.parseInt(idText.getText()),nameText.getText(),startText.getText(),
endText.getText(),Integer.parseInt(dateText.getText())));
}
});
noButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
frame.setVisible(false);
}
});
}
}
class queryFrame {
SQLiteTool sqliteTool;
JFrame frame;
public queryFrame(){
sqliteTool = new SQLiteTool();
frame = new JFrame();
frame.setVisible(true);
frame.setSize(400,300);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setLayout(new BorderLayout());
frame.setLocationRelativeTo(null);
frame.setResizable(false);
JLabel label = new JLabel("请输入要查询人的全名或名字含有的字",JLabel.CENTER);
label.setFont(new Font("楷体",Font.BOLD,20));
JLabel idLabel = new JLabel("姓名");
idLabel.setFont(new Font("楷体",Font.BOLD,20));
JPanel panel_1 = new JPanel();
panel_1.setLayout(new GridLayout(5,0,10,10));
JPanel panel_2 = new JPanel();
panel_2.setLayout(new GridLayout(5,0,10,10));
JPanel panel_3 = new JPanel();
panel_3.setLayout(new GridLayout(1,2,10,10));
JTextField text = new JTextField();
JButton yesButton = new JButton("确认");
JButton noButton = new JButton("取消");
yesButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
sqliteTool.selectById(text.getText());
}
});
noButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
frame.setVisible(false);
}
});
frame.add(label,BorderLayout.NORTH);
frame.add(panel_1,BorderLayout.WEST);
frame.add(panel_2,BorderLayout.CENTER);
frame.add(panel_3,BorderLayout.SOUTH);
panel_1.add(idLabel);
panel_2.add(text);
panel_3.add(yesButton);
panel_3.add(noButton);
}
}
class deleteFrame{
SQLiteTool sqliteTool;
JFrame frame;
public deleteFrame() {
SQLiteTool sqliteTool = new SQLiteTool();
frame = new JFrame();
frame.setVisible(true);
frame.setSize(400,300);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setLayout(new BorderLayout());
frame.setLocationRelativeTo(null);
frame.setResizable(false);
JLabel label = new JLabel("请输入要删除的人的身份证号",JLabel.CENTER);
label.setFont(new Font("楷体",Font.BOLD,20));
JLabel idLabel = new JLabel("身份证");
idLabel.setFont(new Font("楷体",Font.BOLD,20));
JPanel panel_1 = new JPanel();
panel_1.setLayout(new GridLayout(5,0,10,10));
JPanel panel_2 = new JPanel();
panel_2.setLayout(new GridLayout(5,0,10,10));
JPanel panel_3 = new JPanel();
panel_3.setLayout(new GridLayout(1,2,10,10));
JTextField text = new JTextField();
JButton yesButton = new JButton("确认");
JButton noButton = new JButton("取消");
yesButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
sqliteTool.deleteByID(Integer.parseInt(text.getText()));
}
});
noButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
frame.setVisible(false);
}
});
frame.add(label,BorderLayout.NORTH);
frame.add(panel_1,BorderLayout.WEST);
frame.add(panel_2,BorderLayout.CENTER);
frame.add(panel_3,BorderLayout.SOUTH);
panel_1.add(idLabel);
panel_2.add(text);
panel_3.add(yesButton);
panel_3.add(noButton);
}
}
1676
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
