A class for SQLite and MySQL queries

Discussion in 'Plugin Development' started by Digi, Dec 28, 2011.

Thread Status:
Not open for further replies.
  1. I made this a short long time ago while developing a plugin, got a few headaches at the SQLite part but it generally works.
    I posted it for anyone interested in a single class that can handle both SQLite and MySQL.
    It wasn't widely tested tough, so if you use it and find anything wrong, please post it so I can update it :}

    This still requires you to know MySQL or SQLite syntax for queries, they're kinda the same but if something goes wrong you can always google :p

    Download class: http://forums.bukkit.org/attachments/db-zip.7845/

    Usage:
    Code:
    // for MySQL (plugin, host, database, user, password)
    Db db = new Db(this, "localhost", "storage", "root", "");
    
    db.query("CREATE TABLE IF NOT EXISTS `minecraft` (`player` VARCHAR(16) NOT NULL, `money` INT(10) unsigned NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;");
    
    // for SQLite (plugin, file path)
    Db db = new Db(this, getDataFolder() + "/storage.db");
    
    db.query("CREATE TABLE IF NOT EXISTS `minecraft` (`player` TEXT PRIMARY KEY, `money` INTEGER)");
    
    // ..
    
    db.query("INSERT INTO `minecraft`(`player`,`money`) VALUES('Digi', 10)");
    
    // ...
    
    ResultSet result = db.query("SELECT `player`,`money` FROM `storage` WHERE `player` = 'Digi'");
    
    String name = db.resultString(result, 1);
    int money = db.resultInt(result, 2);
     
  2. Offline

    TheTrixsta

    Really useful Digi :3
     
  3. And totally confusing for anyone who doesn't realize the class needs to be downloaded.
    This was posted before the attachments disappeared from view, I've edited and added the link to the attachment :)
     
  4. Offline

    Kodfod

    LOL thanks mate xD was looking at it earlier and was like... lol wut?
     
  5. I've posted this last year, in december, and it's got noticed just now (Getting likes and replies) ? =)

    Anyway, feel free to post if there are any issues with it, as I've already mentioned, not widely tested.

    And also, be aware of SQL injection (google it if you're not familiar with what it is).
     
  6. Offline

    theguynextdoor

    Does that involve using PreparedStatements instead of using straight MySQL queries? So like:

    Code:
                PreparedStatement prep = mysql
                        .prepare("INSERT INTO tribedata (name, chief, maker, spawnX, spawnY, spawnZ, world, spawnPitch, spawnYaw, joinMsg, bonusChunks, civilisation) VALUES "
                                + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
                prep.setString(1, tribe.getName());
                prep.setString(2, tribe.getChief());
                prep.setString(3, tribe.getMaker());
                prep.setDouble(4, tribe.getSpawn().x);
                prep.setDouble(5, tribe.getSpawn().y);
                prep.setDouble(6, tribe.getSpawn().z);
                prep.setString(7, tribe.getSpawn().world.getName());
                prep.setFloat(8, tribe.getSpawn().pitch);
                prep.setFloat(9, tribe.getSpawn().yaw);
                prep.setString(10, tribe.getJoinMsg());
                prep.setInt(11, tribe.getBonusChunks());
                prep.setString(12, "None");
                prep.executeUpdate();
    As opposed to doing it all in 1 mysql.query("blabla");?
     
  7. theguynextdoor
    Yeah, this class tough is more for quick queries that you know values are safe... but you can always change it to your liking, I guess I should add a method for safe queries... or something :}
     
  8. Offline

    Jeff.Halbert

    I just want to thank you. I've been searching for months on how to implement SQLite, and feel stupid now that I know how simple it is....lol. I've also been doing massive amounts of work building my own MySQL constructor classes.... This makes everything simple and cuts down alot of work. so... Thank You.
     
  9. Offline

    ELCHILEN0

    Could I suggest you allow logging in without passwords like this.
    Code:
    if(password == "")
        url = "jdbc:mysql://" + host + "/" + database + "?user=" + user;
    else
        url = "jdbc:mysql://" + host + "/" + database + "?user=" + user + "&password=" + password;
    Or if you wanted it shorter you could use a ternary.
    Code:
    url = "jdbc:mysql://" + host + "/" + database + "?user=" + user + (password == "" ? "" : "&password=" + password);
    Im using the ternary and have used the if statement perfectly. Other than that great work and I will be using this in my plugins :)
     
  10. I don't think you can compare empty strings with == (apart from non-empty strings which I know for sure you can't compare), have you tested that ?
    And also, does the MySQL server reject the connection if you enter the password parameter blank ? :confused:
     
  11. Offline

    ELCHILEN0

    I guess it would be better practice to do password.equals("") but the == does work. It does reject the connection but it looks like it removes the user for some reason. I was getting this error before I added the ternary.
    Code:
    MySQL Error:
    Database query error: Access denied for user ''@'localhost' to database 'elchilen0'
    Java Code:
    new SQL(this, "localhost", "elchilen0", "elchilen0", "");
     
  12. Offline

    recon88

    Looks like I'll use that for a SQLite db.
    But I got a question because I'm pretty new to SQL.

    I copied your example and I'm getting this:
    Code:
    19:31:27  Database result error: ResultSet closed
    19:31:27 [INFO] Digi 0
    Code:java
    1.  
    2. ResultSet result = db.query("SELECT `player`,`money` FROM `minecraft` WHERE `player` = 'Digi'");
    3.  
    4. String name = db.resultString(result, 1);
    5. int money = db.resultInt(result, 2);
    6.  
    7. System.out.println(name + " " + money);
    8.  
     
  13. Offline

    nostalgicBadger

    Aside from being more secure, prepared statements tend to execute faster, especially if you're using the same query often, so they should probably always be used, unless you have a good reason not to.
     
  14. Offline

    Chlorek

    wesleydeman
    Your 'db' object once created and connected to database is alive all the time (almost, however you know java, yh?). Well, it is enough to call it once in your onEnable(). :)
     
  15. Offline

    iTidez

    How would I check if there is no result to my query? Like if I did a query to get a player's balance but there is no balance, how would I see that it is null? All that prints to my console is a nasty looking database error if it returns null.

    Would a simple: result.wasNull(); do? (Where result is the ResultSet)
     
  16. Offline

    wesleydeman

    Code:
    if (result.next()) {
            // here you know that there is at least one record, which is what your question was about
    } else {
        // here you do whatever needs to be done when there is no record
    }
     
  17. Offline

    iTidez

    @weslydeman

    Sounds good, however whenever I define the result class in the first place it errors for me cause it querys when it defines it or something. Ill try your code out now but when I try to debug it, the SQL error comes from the definition of result. Not another part of code.
     
  18. Offline

    wesleydeman

    Post your SQL query please.
     
  19. Offline

    iTidez

    wesleydeman
    Figured it out, it works fine now. Thank you for your help.

    I was passing a null variable to a non-null field without realizing it.

    Edit: Another error I am getting is from a custom event I am creating.

    I am passing the ID of a sign (the second line) through to my MySQL tables, so when I click a button, I see the target, pass it to a spawn event (custom), listen for it with a custom listener, it spawns a piece of cake 2 blocks above it. Currently when I push the button, after seeing that the target is correctly passed, I get the following error for when I search for my location x,y,z values in my table:[SEVERE] Database result error: Operation not allowed after ResultSet closed
     
  20. Offline

    bfgbfggf

    How to close Connection and Statement in that code?
    if i use only
    base = new Db(this, getDataFolder() + File.separator + "SQLite.db");
     
Thread Status:
Not open for further replies.

Share This Page