1

I've got a problem with my SQLite Database. To bring it to the point: Everything is working (executes / create table / ...) except the queries.

Important: autoCommit = False (But it isn't required for the query function i use)

Here's the code:

---=> Database.class <=---

package me.Syloh.Core;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import me.Syloh.Core.Customized.LOG;

public class Database {

    public static Connection connection;
    public static String path = "plugins/Core.db";

    public static void setup() throws SQLException, ClassNotFoundException {

        Class.forName("org.sqlite.JDBC");
        connection = DriverManager.getConnection("jdbc:sqlite:" + path);
        connection.setAutoCommit(false);

        DatabaseMetaData metadata = connection.getMetaData();
        ResultSet resultset = metadata.getTables(null, null, "%", null);

        List<String> tables = new ArrayList<String>();      
        while(resultset.next()) {
            tables.add(resultset.getString(3));
            LOG.info("Table '" + resultset.getString(3) + "' has been found.");
        }

        if(!tables.contains("Bans")) {
            execute("CREATE TABLE Bans (player TEXT, date TEXT, sender TEXT, reason TEXT, ip TEXT, time DOUBLE)");
            LOG.info("Table 'Bans' has been created.");
        }

    }

    public static void execute(String execute) {    
        try {

            Statement statement = connection.createStatement();
            statement.execute(execute); 
            statement.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void execute(List<String> execute) {  
        try {

            Statement statement = connection.createStatement();
            for(String e : execute) { statement.execute(e); }
            statement.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static ResultSet query(String query) {
        try {

            Statement statement = connection.createStatement();
            ResultSet resultset = statement.executeQuery(query);
            statement.close();
            return resultset;

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    public static void commit() {
        try {
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}


---=> Main.class <=---

package me.Syloh.Core;


import java.sql.ResultSet;
import java.sql.SQLException;

import org.bukkit.Bukkit;
import org.bukkit.event.Listener;
import org.bukkit.plugin.Plugin;
import org.bukkit.plugin.java.JavaPlugin;


public class Main extends JavaPlugin implements Listener {

    Plugin Core = this;

    @Override
    public void onDisable() {
        Database.commit();
    }

    @Override
    public void onEnable() {

        Bukkit.getServer().getPluginManager().registerEvents(this, this);
        try {
            Database.setup();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Bukkit.getServer().getScheduler().scheduleSyncRepeatingTask(Core, new Runnable() {      
            public void run() {
                Database.commit();
           }
        }, 60L, 4L);

        Database.execute("INSERT INTO Bans VALUES ('hI', 'a', 'b', 'c', 'd', 1)");
        ResultSet rs = Database.query("SELECT * FROM Bans WHERE player = 'hi'");
        try {
            while(rs.next()) {
                System.out.println(rs.getString("player"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

I've checked out my Database using a SQL Database editor and all inserts were made. But when iam trying to display them, nothing gets displayed at all.

Please help me! Thanks in advance.

Sincerely, Max

1 Answer 1

2

The problem is, you are closing the statement, which will automatically close the associated ResultSet:

Statement statement = connection.createStatement();
ResultSet resultset = statement.executeQuery(query);
statement.close();  // This is the problem
return resultset;

From Statement#close() javadoc:

When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

Having said that, you should never pass around your ResultSet object around your methods. This way it would be very difficult to manage resources, as you would have to keep your Statement and ResultSet objects opened accross methods.

You should rather build some data structure in the same method you are getting the ResultSet in, and return that data structure instead.

Also, you should close the resources like, ResultSet, Statement, and Connection in a finally block, so as to ensure they are always closed, even in case of exception.

Sign up to request clarification or add additional context in comments.

13 Comments

Oh my god thank you SO MUCH! Worked perfectly, but i realised that SQL is not case sensitive, anyway of adding support for that in your code? (Case Sensitive SQL Queries) because i only saw database side modifications! THX
"but i realised that SQL is not case sensitive" SQL is neither case-sensitive nor case-insensitive in terms of the values in columns. It's down to the RDBMS you're using and the settings being used (sometimes on a per-column basis).
Also, the more important question is: If i don't close the statement, does it mean that the statement is idling arround somewhere?
Well sorry, it's no problem that it isn't case sensitive by default. But how do i close the statement if i have to return the resultset first? :/ Thanks for helping!
@user2671258. First of all, you should not have ResultSet as a return value. In fact, you should not pass around ResultSet, or Statement, around methods. Because, then it would be very difficult to manage resources.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.