Javafx集成sqlite数据库

什么是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是自增，因此新增的时候可以不管idString 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;
}