[LIB] SQLibrary - Database wrappers for all database engines

Discussion in 'Resources' started by PatPeter, Aug 26, 2011.

Thread Status:
Not open for further replies.
  1. Offline

    Lolmewn Retired Staff

    Bubelbub glad you fixed it with a simple google :)
     
    Bubelbub likes this.
  2. Offline

    Bubelbub

    I hope anyone could answer this question :)
    I have the same problem and i think there is a better solution as the SELECT 1 method.
     
  3. Offline

    Lolmewn Retired Staff

    if(con.isValid(1000)){
    //yay!
    }else{
    con.close();
    //make a new one
    }
     
  4. Offline

    Bubelbub

    Too easy to be true ^^

    buggy.png
     
  5. Offline

    Lolmewn Retired Staff

    Did it from the top of my mind, there's a method checking if it's valid in there somewhere ;)
     
  6. Offline

    Bubelbub

    Only the "checkConnection" function.
    But these should not work...
     
  7. Offline

    Lolmewn Retired Staff

    And why shouldn't they work? One of them actually fires a 'SELECT 1' or something similar to check if it's up.
     
  8. Offline

    Bubelbub

    There is the description of checkConnection.

    Or is the function changed?
     
  9. Offline

    PatPeter

    SQLibrary
    Version: 4.0 Released!

    Download and view the changelog here: http://dev.bukkit.org/server-mods/sqlibrary/files/2-sqlibrary-4-0/

    For your code it would be:

    Code:
    this.connection.getConnection().isValid(1000);
    However, the documentation says that the parameter represents seconds, so that would actually be 16 minutes and 40 seconds.

    I've added isValid(1) call to checkConnection() for 4.1; I'll have to check how this affects performance.

    I don't believe any of them do.
     
  10. Offline

    Lolmewn Retired Staff

    PatPeter Nice!
    Actually, .isValid does say that it fires a query on the connection.
     
  11. Offline

    ImTheFool

    Is it unsafe to reload my plugins while using this library? I seem to have problems with the previous ResultSet not closed error, but only after I reload my plugins. Also, I'm almost certain I closed all of my ResultSets.

    Edit: Also, for anyone that uses SQLite, when you connect to the database, it's now
    Code:
    public SQLite(Logger log, String prefix, String directory, String filename)
     
  12. Offline

    Bubelbub



    What is better?

    Code:
    this.connection = new MySQL(Logger.getLogger(""), "[SignEvent]", "localhost", 3306, "***", "***", "***");
    this.connection.open();
    this.connection.getConnection().isValid(1000);

    or this?
    (called every time before i execute a query)


    Code:
    if(this.connection == null)
    {
        this.connection = new MySQL(Logger.getLogger(""), "[SignEvent]", "localhost", 3306, "***", "***", "***");
    }
    if(!this.connection.getConnection().isValid(1000))
    {
        this.connection.open();
    }
     
  13. Offline

    sablednah

    Been looking through this lib and it looks really useful.

    What i've love to be able to do tho is something like this (pseudo code follows)
    Code:
    Public DB connection;
    onEnable() {
      if (useMySQL) {
        connection = new MySQL(...);
      } else {
        connection = new SQLite(...);
      }
    }
    So that in code I can use...
    Code:
    connection.query("select * from foo");
    I.e. i just want my plugin users to use SQLite by default - but with an option to setup mysql in the config and have the plugin switch to that.

    Is this feasible with this library? or am i conna just have to stop being lazy and wrap every query and db interaction like this...
    Code:
    if (useMySQL) {
      mysql.query("select * from foo");
    } else {
      sqlite.query("select * from foo");
    }
     
  14. Offline

    Lolmewn Retired Staff

    sablednah If you say it's a... what's the interface again... I think DatabaseConnection, which both classes implement, you can use the abstract functions available by that interface. Should work :)
     
  15. Offline

    sablednah

    Thanks - seams to we working so far...
     
  16. Offline

    XHawk87

    How would I create a new SQLite database rather than opening a connection to an existing one? I tried giving SQLite a non-existent filename to create, but it just created a blank (zero-sized) file which was not in SQLite 3 format and therefore unreadable/unwritable. I also generated a new file with the 100-byte SQLite 3 header to read from, but I am getting errors about it being malformed whenever I try to run a query. Help!

    Code:
            sqlite = new SQLite(getLogger(), "DatabaseTest", getDataFolder().getAbsolutePath(), "DatabaseTest");
            sqlite.open();
    I tried using JDBC directly into SQLite with the following code without issue, using the same sqlite-jdbc-3.7.2.jar that craftbukkit uses, and it works perfectly. I would recommend you take another look at your implementation of SQLite to see why it doesn't work for creating tables.

    Code:
    public static void main(String[] args) throws Exception {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:DatabaseTest.db");
            Statement stat = conn.createStatement();
            stat.executeUpdate("CREATE TABLE players (name VARCHAR PRIMARY KEY, kills INT);");
            PreparedStatement prep = conn.prepareStatement(
                    "INSERT INTO players VALUES (?, ?);");
     
            prep.setString(1, "Player1");
            prep.setInt(2, 0);
            prep.addBatch();
            prep.setString(1, "Player2");
            prep.setInt(2, 0);
            prep.addBatch();
            prep.setString(1, "Player3");
            prep.setInt(2, 0);
            prep.addBatch();
     
            conn.setAutoCommit(false);
            prep.executeBatch();
            conn.setAutoCommit(true);
     
            ResultSet rs = stat.executeQuery("SELECT * FROM players;");
            while (rs.next()) {
                System.out.println("name = " + rs.getString("name"));
                System.out.println("kills = " + rs.getInt("kills"));
            }
            rs.close();
            conn.close();
        }
    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: May 19, 2016
  17. Offline

    PatPeter

    I want you all to know that I replied to all of your posts only for my browser to crash and delete all my replies :mad: .

    I've added it to the next version. checkConnection() is a relic of the first version and frankly doing a null check does not suffice.

    If onDisable() and onEnable() are coded correctly (close any database connections and reopen them) and your functions close the ResultSets in-house, it should work fine. There's a possibility that ResultSets floating in memory aren't closed correctly. I'll look into it.

    And thank you for notifying everyone of the change. I added all the entries to the changelog the day of release, bad idea.

    Neither. In the case of the first the open() should be in an if statement and you should use inValid() before a ResultSet (checking if the connection is open after opening it is redundant).

    In the case of the second, the null check is redundant and the connection in the isValid() statement is null, which will result in a NullPointerException.

    Code:
    this.connection = new MySQL(Logger.getLogger("Minecraft"), "[SignEvent]", "localhost", 3306, "***", "***", "***");
    if (this.connection.open()) {
        log.info("Connected!");
    } else {
        log.severe("Could not connect!"):
    }
    
    public boolean isConnected() {
        return this.connection.getConnection().isValid(1000);
    }
    
    You asked at the perfect time! 4.0 has an enum added that is automatically set for every database:

    Code:
    import lib.PatPeter.SQLibrary.DBMS;
     
    ...
     
    switch (sql.getDBMS()) {
      case MySQL:
        sql.query("SELECT ...");
        break;
      default:
        sql.query("SELECT ...");
    }
    For speed of coding I call all my connection variables sql.

    I am creating a plugin called TimeClock that automatically creates a database when it starts and then four tables. I've deleted the database multiple times for testing purposes and it always restarts correctly. Have you tried running CREATE TABLE on the empty file and if so, what happens? Did you receive any messages about a missing class in console?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: May 19, 2016
    Bubelbub likes this.
  18. Offline

    Lolmewn Retired Staff

    PatPeter
    I just use the interface, works just as fine :) Less lines of code too, since you don't have to check cases in the switch statement.
     
  19. Offline

    ImTheFool

    Will the current 4.0 version work with 1.4.7 or do we need to wait for an update?
     
  20. Offline

    PatPeter

    Awesome :D

    SQLibrary should work all the way through to 1.5 unless Bukkit changes how plugins start, so yes, it does work with the current version of Minecraft.
     
    ImTheFool likes this.
  21. Offline

    sionzee

    Hello PatPeter, sorry for my bad english :) With first version all working good, I now updated this plugin, console write me Error on this line:+
    mysql = new MySQL(lo.....
    Code:java
    1. public static MySQL mysql;
    2.  
    3.  
    4. String hostname = config.getString("MySQL.hostname");
    5. String port = config.getString("MySQL.port");
    6. String database = config.getString("MySQL.database");
    7. String username = config.getString("MySQL.username");
    8. String password = config.getString("MySQL.password");
    9. mysql = new MySQL(log, "[FreeLance_MySQL]", hostname, port, database,username, password);

    What is here changed ?
     
    krisdestruction likes this.
  22. Offline

    PatPeter

    Sorry about that, it seems this is another important change that I missed in the changelog, I'll update it when I can.

    Code:java
    1. protected static Database sql;
    2.  
    3. String hostname = config.getString("MySQL.hostname");
    4. int port = config.getInt("MySQL.port");
    5. String database = config.getString("MySQL.database");
    6. String username = config.getString("MySQL.username");
    7. String password = config.getString("MySQL.password");
    8. mysql = new MySQL(log, "[FreeLance]", hostname, port, database,username, password);


    You don't have to use [FreeLance_MySQL], it will automatically append [MySQL] to errors.

    The problem is that I changed port from a String to an int so that it can be validated as a proper port.
     
  23. Offline

    sionzee

    Code:java
    1. if (mysql.checkTable("FreeLance_VIP")) {
    2.  
    3. log.info("MySQL Tabulka VIP Nalezena");
    4. } else {
    5. mysql.query("CREATE TABLE IF NOT EXISTS FreeLance_VIP (hrac VARCHAR(32), duvod text NOT NULL, admin VARCHAR(32) NOT NULL, datum VARCHAR(32))");
    6. log.info("Tabulka VIP nenalezena, vytvarim novou");
    7. }


    why is mysql.checkTable crossed out?
     
  24. Offline

    Lolmewn Retired Staff

    Means it's deprecated. Your query doesn't require a checkTable check anyway, you can just query it and not care about whether it already exists or not.
     
  25. Offline

    sionzee

    How log without it if table exis't or not ?
    I like:

    if ( .checkTable ("") {
    Table found.
    } else {
    Table not found, I created it.
    }
     
  26. Offline

    Lolmewn Retired Staff

    Check if it returns 0 or 1. 1 Means something changed.
     
  27. Offline

    sionzee

    Now only need:
    Code:java
    1. mysql.query("CREATE TABLE IF NOT EXISTS FreeLance_ANTICHEAT (ip VARCHAR(32))");

    I understand good ?
     
  28. Offline

    Lolmewn Retired Staff

    Yes, that is correct.
     
    sionzee likes this.
  29. Offline

    PatPeter

    checkTable() has been changed to isTable() so that you can type it quicker:

    Code:
    if (sql.isTable("table")) {
    System.out.println("Table found.");
    } else {
    System.out.println("Table not found, I created it.");
    }
     
  30. Offline

    PatPeter

Thread Status:
Not open for further replies.

Share This Page