Java Community | Help. Code. Learn.•2y ago•

16 replies

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)
);

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

PreparedStatement

PreparedStatement

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();

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..

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..

JavaBot•3/21/24, 12:13 PM

⌛

⌛

This post has been reserved for your question.

Hey @TomasGnG ! Please use
/close
/close
or the
Close Post
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•3/21/24, 12:15 PM

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();
    }

TomasGnG 😴OP•3/21/24, 12:15 PM

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•3/21/24, 12:55 PM

Are you running the script or the statements?

TomasGnG 😴OP•3/21/24, 12:56 PM

statements

TomasGnG 😴OP•3/21/24, 12:56 PM

wait

Kyo-chan•3/21/24, 12:56 PM

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

TomasGnG 😴OP•3/21/24, 12:56 PM

i run the whole script

TomasGnG 😴OP•3/21/24, 12:56 PM

does that not work?

Kyo-chan•3/21/24, 12:56 PM

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

TomasGnG 😴OP•3/21/24, 12:57 PM

Kyo-chan•3/21/24, 12:57 PM

There might be a statement syntax for running a script file

TomasGnG 😴OP•3/21/24, 1:09 PM

Works perfectly now, thanks

TTomasGnG 😴Works perfectly now, thanks

JavaBot•3/21/24, 1:09 PM

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.

Original message was deleted

JavaBotAPP•3/21/24, 1:09 PM

Post Closed

This post has been closed by @386870647894048768.

SQL Exception when running Minecraft plugin

Similar Threads

Similar Threads

Similar Threads