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

Problems with sql

Mariadb

    @Override
    public ArrayList<String> getMessages(UUID partyUUID, int lastMessageID)
    {
        String sql = "SELECT message FROM PartyChat WHERE partyUUID = ? AND id > ?";
        ArrayList<String> messages = new ArrayList<>();
        try(Connection conn = partyDatabase.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql))
        {
            stmt.setString(1, partyUUID.toString());
            stmt.setInt(2, lastMessageID);
            try(ResultSet resultSet = stmt.executeQuery())
            {
                while(resultSet.next())
                {
                    messages.add(resultSet.getString("message"));
                }
            }
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            Logger.info(CoreModule.getPlugin(), "MySQLDatabase", "There was a problem while getting the messages!");
        }
        return messages;
    }

    @Override
    public ArrayList<String> getMessages(UUID partyUUID, int lastMessageID)
    {
        String sql = "SELECT message FROM PartyChat WHERE partyUUID = ? AND id > ?";
        ArrayList<String> messages = new ArrayList<>();
        try(Connection conn = partyDatabase.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql))
        {
            stmt.setString(1, partyUUID.toString());
            stmt.setInt(2, lastMessageID);
            try(ResultSet resultSet = stmt.executeQuery())
            {
                while(resultSet.next())
                {
                    messages.add(resultSet.getString("message"));
                }
            }
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            Logger.info(CoreModule.getPlugin(), "MySQLDatabase", "There was a problem while getting the messages!");
        }
        return messages;
    }

The Best thing is that it only accous like 1 out of 20 times

23 Replies

JavaBot•7mo ago

⌛ This post has been reserved for your question.

Hey @LinusHuck! 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.

LinusHuckOP•7mo ago

_RotzRohr_[/109.90.180.245:49518] logged in with entity id 19 at ([world]-11.5, 68.0, -84.5)
java.sql.SQLException: No operations allowed after statement closed.
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
       at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:82)
       at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1405)
       at com.mysql.cj.jdbc.ClientPreparedStatement.getCoreParameterIndex(ClientPreparedStatement.java:1409)
       at com.mysql.cj.jdbc.ClientPreparedStatement.setInt(ClientPreparedStatement.java:1597)
       at Hub.jar//de.linushuck.skyblock.coremodule.modules.chatmodule.database.MySQLPartySystemDatabase.getMessages(MySQLPartySystemDatabase.java:265)
       at Hub.jar//de.linushuck.skyblock.coremodule.modules.chatmodule.PartyChat.searchForNewMessages(PartyChat.java:91)
       at Hub.jar//de.linushuck.skyblock.coremodule.modules.chatmodule.PartyChat.update(PartyChat.java:60)

_RotzRohr_[/109.90.180.245:49518] logged in with entity id 19 at ([world]-11.5, 68.0, -84.5)
java.sql.SQLException: No operations allowed after statement closed.
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
       at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:82)
       at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1405)
       at com.mysql.cj.jdbc.ClientPreparedStatement.getCoreParameterIndex(ClientPreparedStatement.java:1409)
       at com.mysql.cj.jdbc.ClientPreparedStatement.setInt(ClientPreparedStatement.java:1597)
       at Hub.jar//de.linushuck.skyblock.coremodule.modules.chatmodule.database.MySQLPartySystemDatabase.getMessages(MySQLPartySystemDatabase.java:265)
       at Hub.jar//de.linushuck.skyblock.coremodule.modules.chatmodule.PartyChat.searchForNewMessages(PartyChat.java:91)
       at Hub.jar//de.linushuck.skyblock.coremodule.modules.chatmodule.PartyChat.update(PartyChat.java:60)

       at Hub.jar//de.linushuck.skyblock.coremodule.modules.chatmodule.ChatModule.lambda$init$0(ChatModule.java:163)
Caused by: com.mysql.cj.exceptions.StatementIsClosedException: No operations allowed after statement closed.
       at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
       at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
       at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
       at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
       at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
       at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
       at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
       at com.mysql.cj.jdbc.StatementImpl.checkClosed(StatementImpl.java:341)
       at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1391)
       ... 12 more
[Hub] [MySQLDatabase] There was a problem while getting the messages!
[Hub] [CoreModule] Received server name

       at Hub.jar//de.linushuck.skyblock.coremodule.modules.chatmodule.ChatModule.lambda$init$0(ChatModule.java:163)
Caused by: com.mysql.cj.exceptions.StatementIsClosedException: No operations allowed after statement closed.
       at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
       at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
       at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
       at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
       at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
       at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
       at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
       at com.mysql.cj.jdbc.StatementImpl.checkClosed(StatementImpl.java:341)
       at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1391)
       ... 12 more
[Hub] [MySQLDatabase] There was a problem while getting the messages!
[Hub] [CoreModule] Received server name

LinusHuckOP•7mo ago

LinusHuckOP•7mo ago

public Connection getConnection()
    {
        try
        {
            //Check if the existing connection is valid
            if(this.connection != null && !this.connection.isClosed() && this.connection.isValid(5))
            {
                return this.connection;
            }

            //Close the existing connection if it's open but invalid
            if(this.connection != null && !this.connection.isClosed())
            {
                this.connection.close();
            }

            //Load database driver class
            if(this.link == null || this.link.isEmpty())
            {
                this.link = "com.mysql.cj.jdbc.Driver";  //Updated driver class name
            }
            Class.forName(this.link);

            //Establish a new connection
            this.connection = DriverManager.getConnection(this.url, this.user, this.password);
            return this.connection;
        }
        catch(SQLException | ClassNotFoundException throwable)
        {
            throwable.printStackTrace();
            Logger.info(CoreModule.getPlugin(), "MySQLDatabase", "There is a problem in the MySQL database connection!");
        }
        return null;
    }

public Connection getConnection()
    {
        try
        {
            //Check if the existing connection is valid
            if(this.connection != null && !this.connection.isClosed() && this.connection.isValid(5))
            {
                return this.connection;
            }

            //Close the existing connection if it's open but invalid
            if(this.connection != null && !this.connection.isClosed())
            {
                this.connection.close();
            }

            //Load database driver class
            if(this.link == null || this.link.isEmpty())
            {
                this.link = "com.mysql.cj.jdbc.Driver";  //Updated driver class name
            }
            Class.forName(this.link);

            //Establish a new connection
            this.connection = DriverManager.getConnection(this.url, this.user, this.password);
            return this.connection;
        }
        catch(SQLException | ClassNotFoundException throwable)
        {
            throwable.printStackTrace();
            Logger.info(CoreModule.getPlugin(), "MySQLDatabase", "There is a problem in the MySQL database connection!");
        }
        return null;
    }

dan1st•7mo ago

Are you using that from multiple threads? Interestingly, it seems to fail at stmt.setInt(2, lastMessageID); which means the setString might should be successful Does that exception always happen?

LinusHuckOP•7mo ago

its runTaskTimerAsynchronously around 1 out of 20

dan1st•7mo ago

yeah that may be the issue that is not thread safe

LinusHuckOP•7mo ago

how can i make it thread save? https://paste.ofcode.org/KUs42E6wXsv494NjYBTJjN

dan1st•7mo ago

it is possible that: - thread 1 calls getConnection(). - thread 2 executes getMessages which calls getConnection() inside. This returns the same connection as thread 1 - thread 1 closes the Connection - thread 2 executes setInt - This fails because the Connection is closed Don't attempt to cache connections

LinusHuckOP•7mo ago

I see thanks i will try it

JavaBot•7mo 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.

dan1st•7mo ago

Do you always close the Connections you get from getConnection?

LinusHuckOP•7mo ago

actually never

dan1st•7mo ago

you are doing it here the try-with-resources closes the connection

LinusHuckOP•7mo ago

oh i thought you mean conection.close() then prob most of the time

public Connection getConnection()
    {
        try
        {
            //Load database driver class
            if(this.link == null || this.link.isEmpty())
            {
                this.link = "com.mysql.cj.jdbc.Driver";  //Updated driver class name
            }
            Class.forName(this.link);

            //Establish a new connection
            return DriverManager.getConnection(this.url, this.user, this.password);
        }
        catch(SQLException | ClassNotFoundException throwable)
        {
            throwable.printStackTrace();
            Logger.info(CoreModule.getPlugin(), "MySQLDatabase", "There is a problem in the MySQL database connection!");
        }
        return null;
    }

public Connection getConnection()
    {
        try
        {
            //Load database driver class
            if(this.link == null || this.link.isEmpty())
            {
                this.link = "com.mysql.cj.jdbc.Driver";  //Updated driver class name
            }
            Class.forName(this.link);

            //Establish a new connection
            return DriverManager.getConnection(this.url, this.user, this.password);
        }
        catch(SQLException | ClassNotFoundException throwable)
        {
            throwable.printStackTrace();
            Logger.info(CoreModule.getPlugin(), "MySQLDatabase", "There is a problem in the MySQL database connection!");
        }
        return null;
    }

so like this?

dan1st•7mo ago

the try-with-resources calls close() at the end Are you always doing it? If yes, you can just do

public Connection getConnection() throws SQLException {
  return DriverManager.getConnection(this.url, this.user, this.password);
}

public Connection getConnection() throws SQLException {
  return DriverManager.getConnection(this.url, this.user, this.password);
}

LinusHuckOP•7mo ago

lets say i dont close it somewhere will it hurt?

dan1st•7mo ago

yes forgetting to close it results in the DB connection staying open even if you cannot use it any more

LinusHuckOP•7mo ago

is there a thing like close after 5sec if not used?

dan1st•7mo ago

which needs memory and resources on both the Java and DB side and if you do it too often, you have many open connections until you reach the connection limit normally not but it might be different in your case Like what if some operations takes more than 5s? Would you want the DB connection be closed during an operation? But you can use your IDE to find all calls to getConnection and then make sure all of them ensure it being closed

LinusHuckOP•7mo ago

Ok thanks

JavaBot•7mo ago

Post Closed

This post has been closed by <@430770874929381386>.

Gaming

Programming

Problems with sql