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]

我一直在修补我的代码，但我非常缺乏经验，因此在那里什么也没得到......

下面你可以看到我的三个类。我不知道什么是重要的，所以我在这里插入了所有内容。

以下是选择和插入的方法：

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 NULLn"
                + ");";
        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 NULLn"
                + ");";
        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 for time 而不是 int。如前所述，PreparedStatement也将是值得研究的事情。

至于解决这个问题，我注意到对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();
    }