Java Community | Help. Code. Learn.•9mo ago

SQL Exception when running Minecraft plugin

So I have this sql script

CREATE TABLE IF NOT EXISTS players (
    id INT AUTO_INCREMENT,
    name VARCHAR(16) NOT NULL,
    uuid VARCHAR(36) NOT NULL,
    PRIMARY KEY (id)
);
                
CREATE TABLE IF NOT EXISTS friendsystemSettings (
    id INT AUTO_INCREMENT,
    playerID INT NOT NULL,
    friendrequests BOOLEAN NOT NULL DEFAULT '1',
    messagesfromstrangers BOOLEAN NOT NULL DEFAULT '1',
    PRIMARY KEY (id)
);
                
CREATE TABLE IF NOT EXISTS friendsystemPlayertofriend (
    id INT AUTO_INCREMENT,
    playerID INT NOT NULL,
    friendID INT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS players (
    id INT AUTO_INCREMENT,
    name VARCHAR(16) NOT NULL,
    uuid VARCHAR(36) NOT NULL,
    PRIMARY KEY (id)
);
                
CREATE TABLE IF NOT EXISTS friendsystemSettings (
    id INT AUTO_INCREMENT,
    playerID INT NOT NULL,
    friendrequests BOOLEAN NOT NULL DEFAULT '1',
    messagesfromstrangers BOOLEAN NOT NULL DEFAULT '1',
    PRIMARY KEY (id)
);
                
CREATE TABLE IF NOT EXISTS friendsystemPlayertofriend (
    id INT AUTO_INCREMENT,
    playerID INT NOT NULL,
    friendID INT NOT NULL,
    PRIMARY KEY (id)
);

When I use the IntelliJ SQL console and execute it there, everything works fine. But when I try to execute it via PreparedStatement then it shows an error in the console..

String sql = "See SQL above";
PreparedStatement stm = getConnection().prepareStatement(sql);
stm.executeUpdate();
stm.close();

String sql = "See SQL above";
PreparedStatement stm = getConnection().prepareStatement(sql);
stm.executeUpdate();
stm.close();

Error

Caused by: java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: (conn=943) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS friendsystemSettings (
                           ...' at line 8
        at de.tomasgng.b..

Caused by: java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: (conn=943) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS friendsystemSettings (
                           ...' at line 8
        at de.tomasgng.b..

12 Replies

JavaBot•9mo ago

⌛ This post has been reserved for your question.

Hey @TomasGnG 😴! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically closed after 300 minutes of inactivity.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

TomasGnG 😴OP•9mo ago

This is my whole DataSource class

public class DataSource {

    private static HikariConfig config = new HikariConfig();
    private static HikariDataSource ds;

    static {
        MySQLManager manager = BungeeSystem.getInstance().getMySQLManager();

        String user = manager.getUsername();
        String password = manager.getPassword();
        String host = manager.getHostname();
        String database = manager.getDatabase();
        int port = manager.getPort();

        String url = "jdbc:mariadb://" + host + ":" + port + "/" + database;

        config.setJdbcUrl(url);
        config.setUsername(user);
        config.setPassword(password);
        config.setDriverClassName("org.mariadb.jdbc.Driver");
        config.addDataSourceProperty("cachePrepStmts" , "true");
        config.addDataSourceProperty("prepStmtCacheSize" , "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit" , "2048");
        ds = new HikariDataSource(config);

        init();
    }

public class DataSource {

    private static HikariConfig config = new HikariConfig();
    private static HikariDataSource ds;

    static {
        MySQLManager manager = BungeeSystem.getInstance().getMySQLManager();

        String user = manager.getUsername();
        String password = manager.getPassword();
        String host = manager.getHostname();
        String database = manager.getDatabase();
        int port = manager.getPort();

        String url = "jdbc:mariadb://" + host + ":" + port + "/" + database;

        config.setJdbcUrl(url);
        config.setUsername(user);
        config.setPassword(password);
        config.setDriverClassName("org.mariadb.jdbc.Driver");
        config.addDataSourceProperty("cachePrepStmts" , "true");
        config.addDataSourceProperty("prepStmtCacheSize" , "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit" , "2048");
        ds = new HikariDataSource(config);

        init();
    }

private static void init() {
        try {
            if(getConnection() == null || getConnection().isClosed())
                return;

            String sql = "See above..";

            PreparedStatement stm = getConnection().prepareStatement(sql);
            stm.executeUpdate();
            stm.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private DataSource() {}

    public static Connection getConnection() {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

private static void init() {
        try {
            if(getConnection() == null || getConnection().isClosed())
                return;

            String sql = "See above..";

            PreparedStatement stm = getConnection().prepareStatement(sql);
            stm.executeUpdate();
            stm.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private DataSource() {}

    public static Connection getConnection() {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

Kyo-chan•9mo ago

Are you running the script or the statements?

TomasGnG 😴OP•9mo ago

statements wait

Kyo-chan•9mo ago

so, your init() does only one of them?

TomasGnG 😴OP•9mo ago

i run the whole script does that not work?

Kyo-chan•9mo ago

PreparedStatements can't do that. It's for one statement

TomasGnG 😴OP•9mo ago

Kyo-chan•9mo ago

There might be a statement syntax for running a script file

TomasGnG 😴OP•9mo ago

Works perfectly now, thanks

JavaBot•9mo ago

If you are finished with your post, please close it. If you are not, please ignore this message. Note that you will not be able to send further messages here after this post have been closed but you will be able to create new posts.

JavaBot•9mo ago

Post Closed

This post has been closed by <@386870647894048768>.

Gaming

Programming

SQL Exception when running Minecraft plugin