基于SQLite的信息管理系统

建立一个数据库，它可以方便地记录某个高速站口的人员信息。

一，代码逻辑

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

    }
}

Original: https://blog.csdn.net/m0_62135731/article/details/123744358
Author: @Gloaming@
Title: 基于SQLite的信息管理系统