Chat-MC-Sqlite笔记

所用到的sql语句

创建一个表

CREATE TABLE IF NOT EXISTS player_data (player_id VARCHAR(36), player_qq BIGINT, qq_group BIGINT, command VARCHAR(255), binding_code VARCHAR(255))

插入语句

INSERT INTO player_data (player_id, player_qq, qq_group, command, binding_code) VALUES (?, ?, ?, ?, ?)

删除语句

SELECT command FROM player_data WHERE player_id = ?

代码实现

基础代码

创建类,连接,表等

public class SQLiteDataManager {
    private Connection connection;

    public SQLiteDataManager() {
    }

    // 连接到SQLite数据库
    public void connect() {
        try {
            if (connection != null && !connection.isClosed()) {
                return;
            }

            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:" + Until.plugin.getDataFolder().getAbsolutePath() + "/database.db");
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 关闭数据库连接
    public void close() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 创建表格
    public void createTable() {
        try {
            PreparedStatement statement = connection.prepareStatement("CREATE TABLE IF NOT EXISTS player_data (player_id VARCHAR(36), player_qq BIGINT, qq_group BIGINT, command VARCHAR(255), binding_code VARCHAR(255))");
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
}

插入,删除数据

// 根据玩家ID删除数据
public void deleteDataByPlayerID(String playerID) {
    try {
        PreparedStatement statement = connection.prepareStatement("DELETE FROM player_data WHERE player_id = ?");
        statement.setString(1, playerID);
        statement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
// 插入数据
public void insertData(String playerID, long playerQQ, long qqGroup, String command, String bindingCode) {
    try {
        PreparedStatement statement = connection.prepareStatement("INSERT INTO player_data (player_id, player_qq, qq_group, command, binding_code) VALUES (?, ?, ?, ?, ?)");
        statement.setString(1, playerID);
        statement.setLong(2, playerQQ);
        statement.setLong(3, qqGroup);
        statement.setString(4, command);
        statement.setString(5, bindingCode);
        statement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

特殊方法

创建PlayerData类,用于返回获取到的数据

package org.backrer.yiguang.sql;

public class PlayerData {
    public enum PlayerDataField {
        PLAYER_QQ("player_qq"),
        QQ_GROUP("qq_group"),
        COMMAND("command"),
        BINDING_CODE("binding_code");

        private final String fieldName;

        PlayerDataField(String fieldName) {
            this.fieldName = fieldName;
        }

        public String getFieldName() {
            return fieldName;
        }
    }
    private long playerQQ;
    private long qqGroup;
    private String command;
    private String bindingCode;

    public PlayerData(long playerQQ, long qqGroup, String command, String bindingCode) {
        this.playerQQ = playerQQ;
        this.qqGroup = qqGroup;
        this.command = command;
        this.bindingCode = bindingCode;
    }

    // 添加访问器方法（getter）
    public long getPlayerQQ() {
        return playerQQ;
    }

    public long getQQGroup() {
        return qqGroup;
    }

    public String getCommand() {
        return command;
    }

    public String getBindingCode() {
        return bindingCode;
    }
}

获取玩家信息,返回玩家数据类

// 根据玩家ID查询多个字段
    public PlayerData getPlayerDataByPlayerID(String playerID) {
        try {
            PreparedStatement statement = connection.prepareStatement("SELECT player_qq, qq_group, command, binding_code FROM player_data WHERE player_id = ?");
            statement.setString(1, playerID);
            ResultSet result = statement.executeQuery();

            if (result.next()) {
                long playerQQ = result.getLong(PlayerData.PlayerDataField.PLAYER_QQ.getFieldName());
                long qqGroup = result.getLong(PlayerData.PlayerDataField.QQ_GROUP.getFieldName());
                String command = result.getString(PlayerData.PlayerDataField.COMMAND.getFieldName());
                String bindingCode = result.getString(PlayerData.PlayerDataField.BINDING_CODE.getFieldName());
                return new PlayerData(playerQQ, qqGroup, command, bindingCode);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null; // 如果没有找到数据，返回null或适当的默认值
    }

更新指定信息

// 根据玩家ID修改特定字段
    public void updatePlayerData(String playerID, PlayerData.PlayerDataField fieldName, String value) {
        try {
            String query = "UPDATE player_data SET " + fieldName.getFieldName() + " = ? WHERE player_id = ?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setString(1, value);
            statement.setString(2, playerID);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }