Javafx集成sqlite数据库

野性酷女 2023-10-15 15:07 28阅读 0赞

> **什么是SQLite**

SQLite是一款非常轻量级的关系数据库系统，支持多数SQL92标准。SQLite在使用前不需要安装设置，不需要进程来启动、停止或配置，而其他大多数SQL数据库引擎是作为一个单独的服务器进程，被程序使用某种内部进程通信(典型的是TCP／IP)，完成发送请求到服务器和接收查询结果的工作，SQLite不采用这种工作方式。使用SQLite时，访问数据库的程序直接从磁盘上的数据库文件读写，没有中间的服务器进程。

> **添加maven依赖**

<dependency>
                <groupId>org.xerial</groupId>
                <artifactId>sqlite-jdbc</artifactId>
                <version>3.43.0.0</version>
            </dependency>

> 添加SQLite配置类

import java.sql.*;
    
    public class SQLiteInitConfig {
        
        public static void main(String[] args) {
        
            try {
        
                // 加载SQLite驱动程序
                Class.forName("org.sqlite.JDBC");
    
                // 创建数据库连接
                ProjectStaticManager.connection = DriverManager.getConnection("jdbc:sqlite:src/main/resources/database.db");
    
                // 创建Statement对象
                ProjectStaticManager.statement = ProjectStaticManager.connection.createStatement();
    
                // 创建表
                String createTableSQL = "CREATE TABLE IF NOT EXISTS hg_bq_img_size (id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        "cardType INTEGER, cutOrder INTEGER, pointX INTEGER, pointY INTEGER, width INTEGER, height INTEGER)";
                ProjectStaticManager.statement.executeUpdate(createTableSQL);
    
                // 插入数据 id是自增，因此新增的时候可以不管id
                String insertSQL = "INSERT INTO hg_bq_img_size (cardType, cutOrder, pointX, pointY, width, height) VALUES (1, 1, 700, 418, 880, 160)";
                int i = ProjectStaticManager.statement.executeUpdate(insertSQL);
    
                System.out.println("数据插入成功！");
    			// 查询数据
                ResultSet rs = ProjectStaticManager.statement.executeQuery("SELECT * FROM hg_bq_img_size;");
                while (rs.next()) {
        
                    System.out.println("id = " + rs.getString("id"));
                    System.out.println("cardType = " + rs.getString("cardType"));
                }
                rs.close();
    
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            // 记得程序结束的时候关闭 
            // ProjectStaticManager.statement.close();
            // ProjectStaticManager.connection.close();
        }
    }

> SQLite语法

import com.huagao.constant.ProjectStaticManager;
    import com.huagao.song.entity.HgBqImgSize;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @Author cpf
     * @Date 2023/9/2
     */
    
    public class HgBqImgSizeSql {
        
    
        //TODO 查询所有数据
        private static final String SQL_queryList = "SELECT * FROM hg_bq_img_size;";
        //TODO 新增数据
        private static final String SQL_saveList = "INSERT INTO hg_bq_img_size (cardType,cutOrder,pointX,pointY,width,height) VALUES (";
        //TODO 根据ID删除数据
        private static final String SQL_deleteList = "DELETE FROM hg_bq_img_size WHERE id = ";
        //TODO 查询
        public static List<HgBqImgSize> queryImgSizeList(){
        
            List<HgBqImgSize> bqImgSizes = new ArrayList<HgBqImgSize>();
            try {
        
                ResultSet rs = ProjectStaticManager.statement.executeQuery(SQL_queryList);
                while (rs.next()) {
        
                    HgBqImgSize hgBqImgSize = new HgBqImgSize(rs.getString("id"), rs.getInt("cardType"),
                            rs.getInt("cutOrder"), rs.getInt("pointX"), rs.getInt("pointY"),
                            rs.getInt("width"), rs.getInt("height"));
                    bqImgSizes.add(hgBqImgSize);
                }
                rs.close();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            return bqImgSizes;
        }
    
        //TODO 根据ID删除数据
        public static int deleteImgSizeById(String id){
        
            int num = 0;
            try {
        
                num = ProjectStaticManager.statement.executeUpdate(SQL_deleteList + id + ";");
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            return num;
        }
    
        //TODO 新增
        public static int saveImgSize(HgBqImgSize bq){
        
            int num = 0;
            try {
        
                num = ProjectStaticManager.statement.executeUpdate(SQL_saveList +
                        bq.getCardType() + ","+
                        bq.getCutOrder() +","+
                        bq.getPointX() + ","+
                        bq.getPointY() + ","+
                        bq.getWidth() + ","+
                        bq.getHeight() +
                        ")"+ ";");
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            return num;
        }
    }

import com.huagao.myThread.thread.MyThreadPoolExecutor;
    import javafx.stage.Stage;
    import java.sql.Connection;
    import java.sql.Statement;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.concurrent.ExecutorService;
    
    /**
     * @title: IndexStageManager 
     * @Author cpf
     * @Date: 2023/9/2
     * @Version 1.0
     */
    public class ProjectStaticManager {
        
        /**
         * SQLite database
         */
        public static Connection connection = null;
        public static Statement statement = null;
    }