[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

    DrAgonmoray

    I understand that, but I don't want to slow down the main thread with potentially slow operation. I want to so it asynchronously.
     
  2. Offline

    PatPeter

    Sorry, I meant to say asynchronous. If you put the library in a thread initiated in the main function of your program it should not slow operation.
     
  3. Offline

    DrAgonmoray

    Hehe, you're confusing me now. So if I put it in a SYNC thread, it shouldn't slow operation?
    If I put it in an ASYNC thread it wouldn't slow operation. But is it safe to use in an ASYNC thread?
     
  4. Offline

    PatPeter

    Technically if you put it in a sync thread, main would hang until that thread's completion (not slow). So yes, you want to use an async thread and yes, it is completely safe.
     
    DrAgonmoray likes this.
  5. Offline

    Neodork

    Edit: Solved the problem, going to start programming. Btw, Your awesome :) keep on the good work
     
  6. Offline

    jertocvil

    I didn't read the whole thread, excuse me if this question has already been asked :(

    Why don't you pack the library in a jar? I think it's easier to import, and if more than one plugin uses the library you don't have duplicated code.

    I packed it and it seems to work (I don't tested it exhaustively). Of course, I will only release it if PatPeter wants.
     
  7. Offline

    PatPeter

    Thanks.

    When I have a stable version I will upload a zip package to the GitHub with all options available to developers.

    Primarily, the reason not to package the library in a jar is a loading issue. You may have already tested it, but I have no idea if Bukkit properly initializes the library if the library does not extend the JavaPlugin class. Not only that, a developer has to worry about dependencies with a jar. It's just much easier to use the source files themselves.
     
  8. Offline

    jertocvil

    This is what I did: I created this class:

    PHP:
    package lib.PatPeter.SQLibrary;
     
    import java.util.logging.Level;
    import java.util.logging.Logger;
     
    import org.bukkit.plugin.java.JavaPlugin;
     
    public class 
    SQLibrary extends JavaPlugin {
     
        public static final 
    Logger logger Logger.getLogger("Minecraft");
     
        @
    Override
        
    public void onDisable() {
            
    SQLibrary.logger.log(Level.INFO"SQLibrary stopped.");
        }
     
        @
    Override
        
    public void onEnable() {
            
    SQLibrary.logger.log(Level.INFO"SQLibrary loaded.");
        }
    }
    To use it, you only have to import the jar, the rest is the same. Tell me if you want the packed jar.
     
  9. Offline

    PatPeter

    Interesting. I'll think of adding this with your permission. I don't need the packed jar, as I said when I have a stable version on GitHub I'll package it as both source and jar.
     
  10. Offline

    Ezzy

    I'm probably over tired and the solution is staring me in the face but,
    How do I make a new DataBase from within the plugin? So I can pass it to the new MySQL(); method
    Code:
    MySQL sql = new MySQL(log, "EzzXP", "localhost", "3306", DATABASE HERE, "root", "PASSWORD");
     
  11. Offline

    ProjectInfinity

    How would you go about selecting a column in a statement with inner join in SQLite? In navicat everything pops up like it should, and I assume it does in Java as well, but when I try to actually fetch the id of one of the tables, I get "java.sql.SQLException: no such column: 'request.id'"

    The following is my query string
    Code:
    SELECT * FROM reportrts_request as request INNER JOIN reportrts_user as user ON request.user_id = user.id WHERE `status` = 0
    The error shown earlier is displayed when I try to result.getInt("request.id");
    I tried with reportrts_request.id as well, without luck. If someone could shed some light on this, I would much appreciate it.
     
  12. Offline

    dillyg10

    I'm sorry if this comes out rude, but it's been about 6 months, is there documentation/tutorial/example plugin for this yet?
     
  13. Offline

    NullCity

    WHAT I HAVE ALWAYS BEEN LOOKING FOR! :O
    *faints*

    - MintDev Owner
     
  14. Offline

    Neodork

    Ok to connect/check if your database exists. I use this, and I use mySQL for it:

    PHP:
    public class Yourplugin extends JavaPlugin {
     
        public 
    MySQL mysql;
     
        public 
    String host;  //<--- I use a config file to fill these up, if you don't fill them up yourself.
        
    public String port;
        public 
    String dbname;
        public 
    String user;
        public 
    String password;
     
        public 
    void onDisable() {
     
            
    mysql.close();
     
        }
     
        public 
    void onEnable() {
     
            
    sqlConnection();
            
    sqlDoesDatabaseExist();
     
        }
    To check the connection with the database:

    PHP:
    public void sqlConnection() {
    mysql = new MySQL(Your log,
                    
    "[Pluginname]",
                    
    host,
                  
    port,
                  
    dbname,
                  
    user,
                  
    password);
            try {
                
    plugin.mysql.open();
            } catch (
    Exception e) {
                
    plugin.log.info(e.getMessage());
                
    plugin.getPluginLoader().disablePlugin(plugin);
            }
     
    }
    To create a Table and set a test value:

    PHP:
    public void sqlDoesDatabaseExist() {
            if(
    mysql.checkTable("datbasename")){
     
              }else{
     
     
                  
    mysql.query("CREATE TABLE databasename (id INT PRIMARY KEY, playername VARCHAR(50),  registerdate VARCHAR(50));");
     
     
     
                  
    mysql.query("INSERT INTO databasename(id, playername, registerdate,) VALUES('1','1337ElitePlayer', '12/12/12');");
     
                  
    System.out.println("Databasename created");
     
     
              }
     
        }
     
    Serpent36 likes this.
  15. Offline

    dillyg10

    how do you get values?
     
  16. Offline

    Neodork

    Get Int that returns with 1 value(1x1 row):


    PHP:
    public int getPlayerIdFromDatabase(String playername) {
       
            
    ResultSet rs mysql.query("Select id FROM databasename WHERE playername = '"+playername+"'");
            try {
                if (
    rs.first()) {
                    try {
                        return 
    rs.getInt("id");
                    } catch (
    SQLException e) {
                        
    e.printStackTrace();
                    }
                }
            } catch (
    SQLException e) {
           
                
    e.printStackTrace();
            }
            return 
    0;
        }

    Get String that returns with 1 value(1x1 row):

    PHP:
    public String getPlayernameFromDatabase(String playername) {
       
            
    ResultSet rs mysql.query("Select playername FROM databasename WHERE playername = '"+playername+"'");
            try {
                if (
    rs.first()) {
                    try {
                        return 
    rs.getString("playername");
                   
                    } catch (
    SQLException e) {
                        
    e.printStackTrace();
                    }
                }
            } catch (
    SQLException e) {
           
                
    e.printStackTrace();
            }
            return 
    null;
        }
     
    Serpent36 likes this.
  17. Offline

    dillyg10

    Neodork
    Wow! Your amazing thank you :)
     
  18. Offline

    Neodork

    No problem just "like" my post :D
     
  19. Offline

    PatPeter

    You would have to select a different database first and then switch to the other database using USE. I'll change it later to support passing a null database.

    Try result.getInt("id");

    It will be on the next push to GitHub.

    Thank you Neodork, a day I don't have to make sample code is a good day.
     
  20. Offline

    dillyg10

    Neodork

    Ok, so I"m going to be going through a mysql database where there are multiple instances of a username, how can I get each instance?
    For EX:
    Code:
    ID | U-name | Message
    1 dilly I wub you
    2 dilly Where is the salad tongs
    3 dilly MySQL is giving me a headache...
    
    And when a user types, oh idk /mysqlstuff it sends them the three messages that are there :). Thanks
     
  21. Offline

    dillyg10

  22. Offline

    dillyg10

    Neodork I actually managed to figure it out on my own, basicly i did a while(rs.next()) loop, in that I added values to an array, returned the array, and yeah worked well :p.
     
  23. Offline

    Neodork

    Good, I was going to type that but I'm kind of lazy :) When I read your problem troughly I figured the way I showed was wrong ^^.
     
  24. Offline

    ProjectInfinity

    How would you go about supporting more than one database using this library? I've never had the need for anything other than SQLite but now I would like to be able to support MySQL as well without having to rewrite my classes.
     
  25. Offline

    dillyg10

    If you mean like table... you just do the CREATE TABLE thing more than once.. if you mean an entirly new MySQL server... then you just init the variable more than once with different values :).
     
  26. Offline

    Neodork

    If you want to change database you should change the value's you use to connect. So first close your old one. Then change the value's and open a new one, when done with this one close it(But only when you swap SQL database again).
     
  27. Offline

    thommy101

    Can someone explain what I'm doing wrong?
    Code:
    public void addBlock(Location location)
    {
        World world = location.getWorld();
        String query = "INSERT INTO locations (location, world) VALUES (\"" + location + "\", \"" + world + "\");";
        plugin.log.info("addblock: "+ query); //DEBUG
        plugin.SQLite.query(query);
        return;
    }
    When this got called, my whole server won't respond to anything.

    edit:
    the query that will be executed will look like:
    INSERT INTO locations (location, world) VALUES ("Location{world=CraftWorld{name=world},x=82.0,y=64.0,z=240.0,pitch=0.0,yaw=0.0}", "CraftWorld{name=world}");
     
  28. Offline

    PatPeter

    You could try multiple threads with an instance of MySQL in each connected to a separate database. In theory it should work.

    That would be the current bug with SQLite. I partially fixed it but not entirely. To fix it yourself look at the last query you're calling with SQLite and make sure to close it with rs.close().

    EDIT: Forgot to say that I'll try uploading the fix today.

    Attention everyone! The long-awaited fix has been processed. I have updated the GitHub as well as posting our first stable download off of GitHub (yay).

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

    thommy101

    I hope it's fixed... Than I'm sure I do something wrong....

    I get this error in my log:
    the first two rules are debug, which are the commands send to query();
    Code:
    2012-03-12 18:57:37 [INFO] SELECT * FROM locations WHERE location = "Location{world=CraftWorld{name=world},x=82.0,y=64.0,z=240.0,pitch=0.0,yaw=0.0}"
    2012-03-12 18:57:37 [INFO] INSERT INTO locations (location, world) VALUES ("Location{world=CraftWorld{name=world},x=82.0,y=64.0,z=240.0,pitch=0.0,yaw=0.0}", "CraftWorld{name=world}");
    2012-03-12 18:57:40 [WARNING] [PLUGIN] [SQLite] Error in SQL query: cannot commit transaction - SQL statements in progress
    2012-03-12 18:57:40 [SEVERE] Could not pass event PlayerInteractEvent to PLUGIN
    org.bukkit.event.EventException
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:303)
        at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
        at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:441)
        at org.bukkit.craftbukkit.event.CraftEventFactory.callPlayerInteractEvent(CraftEventFactory.java:168)
        at net.minecraft.server.ItemInWorldManager.dig(ItemInWorldManager.java:92)
        at net.minecraft.server.NetServerHandler.a(NetServerHandler.java:526)
        at net.minecraft.server.Packet14BlockDig.handle(SourceFile:43)
        at net.minecraft.server.NetworkManager.b(NetworkManager.java:226)
        at net.minecraft.server.NetServerHandler.a(NetServerHandler.java:111)
        at net.minecraft.server.NetworkListenThread.a(NetworkListenThread.java:78)
        at net.minecraft.server.MinecraftServer.w(MinecraftServer.java:536)
        at net.minecraft.server.MinecraftServer.run(MinecraftServer.java:434)
        at net.minecraft.server.ThreadServerApplication.run(SourceFile:465)
    Caused by: java.lang.NullPointerException
        at PLUGIN.addBlock(SQLite.java:116)
        at PLUGIN.onPlayerInteract(PlayerListener.java:51)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:301)
        ... 12 more
    Hopefully does someone see what I'm doing wrong.

    This are the two parts of my code that are run by my plugin.
    Code:
    public int checkBlock(Location location)
    {
        String query = "SELECT * FROM locations WHERE location = \"" + location + "\"";
        plugin.log.info(query);
        ResultSet rs = plugin.SQLite.query(query);
        int iResult = -1;
     
        try
        {
            if (rs != null && rs.next())
            {
                iResult = 1;
            }
            else
            {
                iResult = 2;
            }
            rs.close();
        }
        catch (SQLException e)
        {
            plugin.log.info(e.getMessage());
            iResult = -1;
        }
        return iResult; 
    }
     
    public void addBlock(Location location)
    {
        World world = location.getWorld();
        String query = "INSERT INTO locations (location, world) VALUES (\"" + location + "\", \"" + world + "\");";
        plugin.log.info(query);
        ResultSet rs = plugin.SQLite.query(query);
        try
        {
            rs.close();
        }
        catch (SQLException e)
        {
            plugin.log.info(e.getMessage());
        }
        return;
    }
     
  30. Offline

    PatPeter

    Close the ResultSet in checkBlock() before running addBlock().
     
Thread Status:
Not open for further replies.

Share This Page