java - SQLite语法错误但语句完全正确

Question

我想对数据库进行正常插入，或者只选择一个。但是我不能那样做，总是会出错。

Caused by: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "ScientistRick": syntax error)
           at org.sqlite.core.DB.newSQLException(DB.java:941) ~ [spigot.jar:git-Spigot-518206a-a5b9c7b]

我一直在修改我的代码，但我经验不足，因此什么也没有...

下面你可以看到我的三个类(class)。我不知道什么可能很重要，所以我将所有内容都插入此处。

下面是选择和插入的方法:

package de.elektriker_lifestyle.reducedcoffee;

import java.io.File;
import java.sql.*;
import java.util.Date;

public class sqlite {



    static String url = ReducedCoffee.getInstance().pathOfPlayerDataURL;


    /**
     * Connect to a sample database
     */
    public static String createNewDatabase() throws SQLException{

        String result = "";

        String sql = "CREATE TABLE IF NOT EXISTS playerJoins (\n"
                + " primkey integer PRIMARY KEY AUTOINCREMENT,\n"
                + " player_name text NOT NULL,\n"
                + " player_uuid text NOT NULL,\n"
                + " last_login INT NOT NULL,\n"
                + " player_streak integer DEFAULT 0 NOT NULL\n"
                + ");";

        Statement stmt = null;
        Connection connection = DriverManager.getConnection(url);
        stmt = connection.createStatement();
        stmt.executeUpdate(sql);
        stmt.close();

        createInitialTable();

        return result;
    }

    public static void createInitialTable() throws SQLException{

        // SQL statement for creating a new table
        String sql = "CREATE TABLE IF NOT EXISTS playerJoins (\n"
                + " primkey integer PRIMARY KEY AUTOINCREMENT,\n"
                + " player_name text NOT NULL,\n"
                + " player_uuid text NOT NULL,\n"
                + " last_login INT NOT NULL,\n"
                + " player_streak integer DEFAULT 0 NOT NULL\n"
                + ");";

        Statement stmt = null;
        Connection connection = DriverManager.getConnection(url);
        stmt = connection.createStatement();
        stmt.executeUpdate(sql);
        stmt.close();

    }

    /**
     * insert data into a table
     *
     * @param path the database path
     * @param tableName name of the table
     * @param columnNames the column names in a comma separated format like value1, value2, value2
     * @param data the data in a comma separated format, strings must be surrounded by apostrophe like value1, 'string1', value2
     */
    public static void insertData(String path, String tableName, String columnNames, String data) {

        String sql = "INSERT INTO " + tableName + " (" + columnNames + ") VALUES(" + data + ")";
    }

    /**
     * select specific data from a table
     *
     * @param path the database path
     * @param tableName name of the table
     * @param selectedColumns the column names which should be selected like "name, date, age"
     * @param whereStatement the complete where statement like "name = "Peter" AND age = 80"
     */
    public static void selectData(String path, String tableName, String selectedColumns, String whereStatement) throws SQLException {

    }


    public static boolean checkIfPlayerExists(String uuid, String name) throws SQLException {

        String sql = "SELECT COUNT(*) FROM playerJoins WHERE player_uuid = '" + uuid + " AND player_name='" + name + "'";
        Statement stmt = null;
        Connection connection = DriverManager.getConnection(url);
        stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        boolean result = (rs.getInt(1) > 0) ? true : false;
        return result;

    }

    public static void insertPlayer(String uuid, String name) throws SQLException {

        Date currentDate = new Date();
        long unixTime = currentDate.getTime() / 1000;

        // SQL statement for creating a new table
        String sql = "INSERT INTO playerJoins (player_uuid, player_name, last_login) VALUES (\"770e9bb2-ff36-4229-b5c0-ab9f5adb8b2f\",\"ScientistRick\",1111);";

        Statement stmt = null;
        Connection connection = DriverManager.getConnection(url);
        stmt = connection.createStatement();
        stmt.executeUpdate(sql);
        stmt.close();

    }

}

这里我选择的方法是:

package de.elektriker_lifestyle.reducedcoffee;

import org.bukkit.Bukkit;
import org.bukkit.event.Listener;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import org.bukkit.entity.Player;
import org.bukkit.event.EventHandler;
import org.bukkit.event.player.PlayerJoinEvent;

public class joinEvent implements Listener {



    @EventHandler
    public void onPlayerJoin(PlayerJoinEvent event) throws SQLException {
        Player p = event.getPlayer();
        String uuid = p.getUniqueId().toString();
        String name = p.getDisplayName();

        Bukkit.broadcastMessage("§4DEBUG-MESSAGE: " + uuid + " | " + name);

        ReducedCoffee plugin = ReducedCoffee.getInstance();

        if(sqlite.checkIfPlayerExists(uuid, name)) {
            Bukkit.broadcastMessage("§9 Player existiert!");
        } else {
            Bukkit.broadcastMessage("§9 Player existiert noch nicht!");
            sqlite.insertPlayer(uuid, name);
            Bukkit.broadcastMessage("§9 Player wurde nun hinzugefügt!");
        }


    }

}

如果插件类很重要，我不知道......如果不需要请删除!

package de.elektriker_lifestyle.reducedcoffee;


import org.bukkit.Bukkit;
import org.bukkit.plugin.java.JavaPlugin;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


public class ReducedCoffee extends JavaPlugin {

    static boolean debugModeIsEnabled = false;

    // useful plugin information
    String pathOfPlugin = getDataFolder().getAbsolutePath();
    String pathOfPlayerData = pathOfPlugin + "/" + "playerData.db";
    String pathOfPlayerDataURL = "jdbc:sqlite:" + pathOfPlayerData;



    // creates the possibility to access the plugin instance
    public static ReducedCoffee getInstance() {
        return (ReducedCoffee) Bukkit.getPluginManager().getPlugin("ReducedCoffee");
    }

    // happens when the plugin gets loaded during the server-start
    @Override
    public void onEnable(){
        new File(pathOfPlugin).mkdirs();

        try {
            String message = sqlite.createNewDatabase();
        } catch (SQLException e) {
            getLogger().info("SQL-Fehler: " + e);
        }


        getServer().getPluginManager().registerEvents(new joinEvent(), this);

    }

}

我真的不知道为什么会这样，Statemnet 绝对正确。使用 SQLite 数据库管理程序，我的语句可以完美运行...

Answer 1

您的堆栈跟踪应该会告诉您错误发生的位置。我会使用 long 表示时间而不是 int。正如已经提到的，PreparedStatements 也是需要研究的对象。

至于解决这个问题，我注意到对 insertPlayer 的调用传递了 uuid 和名称，但该方法不使用它们。我会尝试的是类似

    public static void insertPlayer(String uuid, String name) throws SQLException {
        String url = ReducedCoffee.getInstance().pathOfPlayerDataURL;
        Connection connection = DriverManager.getConnection(url);

        PreparedStatement prepareStatement = connection.prepareStatement(
                "INSERT INTO playerJoins values (?,?,?);");

        prepareStatement.setString(1, uuid);
        prepareStatement.setString(2, name);
        prepareStatement.setLong(3, System.currentTimeMillis());

        prepareStatement.execute();
    }