Java实现SQLite数据库的添加、删除、修改、查询操作

一、需求表述

先建立一个某单位的员工工资数据库，在此基础上通过编程实现以下功能

1.在数据库中建立一个员工信息表，表名为员工，其结构为：编号、姓名、性别、年龄、职称、工资、是否党员；

2.在表中输入若干数据记录(“职称”可分为高中低三级，其他数据自己设计)；

3.删除年龄超过60岁的员工记录；

4.修改职称为高级且为党员的员工的工资设为8000；

5.在表中查询所有记录显示到屏幕上。

二、环境准备

1.下载SQLite的驱动包，并添加到Modules的Dependencies中

下方链接提供sqlite-jdbc-3.7.2.jar包
链接：https://pan.baidu.com/s/1TYIEC_WiYMEuXfdTIHz1XA 
提取码：yy96
           

2.在C盘中创建sqlite文件夹，用以存放数据库文件

三、Java代码

import netscape.security.UserTarget;

import java.sql.*;

public class EmpSalaryDB {
    private static final String CLASS_NAME = "org.sqlite.JDBC";
    private static final String DB_URL = "jdbc:sqlite://C:/sqlite/empsalary.db";
    private static final String CREATE_SQL = "CREATE TABLE IF NOT EXISTS salary(id int not null primary key," +
            "name varchar(20) not null,sex char(2) not null,age int not null," +
            "rank varchar(30),salary decimal(18,2),party tinyint(1))";
    private static Connection conn;

    public static void insert(int id,String name,String sex,int age,String rank,float salary,int party) throws SQLException {
        if (conn != null) {
            String sql = "INSERT INTO salary VALUES(?,?,?,?,?,?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            pstmt.setString(2,name);
            pstmt.setString(3,sex);
            pstmt.setInt(4,age);
            pstmt.setString(5,rank);
            pstmt.setFloat(6,salary);
            pstmt.setInt(7,party);
            pstmt.executeUpdate();
        }
    }

    public static void delete(String condition) throws SQLException {
        if (conn != null) {
            String sql = "DELETE FROM salary WHERE " + condition;
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        }
    }

    public static void update(String condition,String value) throws SQLException {
        if (conn != null) {
            String sql = "UPDATE salary SET " + value + " WHERE " + condition;
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        }
    }

    public static void query(String condition) throws SQLException {
        if (conn != null) {
            String sql = "SELECT * FROM salary WHERE " + condition;
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                System.out.print("id：" + rs.getInt(1) + " ");
                System.out.print("name：" + rs.getString(2) + " ");
                System.out.print("sex：" + rs.getString(3) + " ");
                System.out.print("age：" + rs.getInt(4) + " ");
                System.out.print("rank：" + rs.getString(5) + " ");
                System.out.print("salary：" + rs.getFloat(6) + " ");
                System.out.println("party：" + rs.getInt(7));
            }
        }
    }


    public static void main(String[] args) {
        try {
            Class.forName(CLASS_NAME);
            conn = DriverManager.getConnection(DB_URL);
            System.out.println(conn);
            Statement stmt = conn.createStatement();
            insert(1,"张三","男",25,"高级",9000,1);
            insert(2,"李四","男",25,"中级",3000,1);
            insert(3,"王五","男",25,"低级",7000,1);
            insert(4,"黄某","男",25,"中级",5000,0);
            insert(5,"冯某","男",25,"高级",6000,0);
            insert(6,"林某","男",25,"中级",4000,0);
            insert(7,"臭某","女",21,"低级",600,0);
            insert(8,"艾总","男",25,"高级",20000,1);
            insert(9,"钱某","男",25,"高级",30000,1);
            insert(10,"Jerry","女",69,"低级",1500,1);
            insert(11,"Sans","女",65,"中级",1000,1);

            System.out.println("查看所有记录：");
            // 查询所有插入记录
            query("1");
            // 删除年龄超过60岁的员工
            delete("age > 60");
            System.out.println("删除年龄超过60岁的员工后，记录如下所示：");
            // 查询剩余员工记录
            query("1");
            // 修改职称为高级且为党员的员工的工资设为8000
            update("rank='高级' and party=1","salary=8000");
            System.out.println("职称为高级且为党员的员工记录如下所示：");
            // 查询职称为高级且为党员的员工的工资
            query("rank='高级' and party=1");
            // 删除所有记录
            delete("1");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}