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

    }

}