Tutorial Using MySQL In your Plugins!

Discussion in 'Resources' started by -_Husky_-, Mar 1, 2013.

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

    Kevin35

    Is there anything I should do on disable? Is there a closeconnection method I should use etc.? Also how many connection, statement etc. objects are necessary ? Can I just create one of each for all of my plugin and re use them for a bunch of different things?
     
  2. Offline

    ratedam

    Hello,

    As a starter myself in the bukkit plugin development i have a doubt. I never a online server but my question is the following:
    Whenever we make any kind of query to a database where is that DB hosted? In our private server or there's a way to host a DB in a server bought by us?

    Thank you all.
     
  3. Offline

    gomeow

    If you buy minecraft hosting, it is unlikely that they will offer a MySQL server. You will need to host it externally on a VPS.
     
  4. Offline

    ratedam

    gomeow do you think that a free vps might be able to held a simple 20 slots server?
     
  5. Offline

    calebbfmv

    ratedam
    Try hosting it locally, not best, but seems within your price range.
     
  6. Offline

    AstramG

    Anybody know how to insert a row into a table if it doesn't exist already? Basically adding a player to a table if they aren't there already. I've been searching for awhile, but couldn't seem to find it and you guys sure seem to know what you're doing in SQL.
     
  7. Offline

    calebbfmv

    if(res.next){
    //sql.UPDATE
    } else {
    //sql.INSERT
     
  8. Offline

    Build_and_Break

    MySQL can't be resolved to a type! :c Help!
     
  9. Offline

    lycano

    i believe he added pseudo code....

    For easy mysql usage use MiniConnectionPoolManager class or start reading about pooled connections and create your own.

    If you use the first aproach checkout the documentation of that class and use it correctly.

    Adding a row to a table: Checkout mysql documentation for UPDATE and INSERT syntax as calebbfmv probably wanted to link you to.

    If you do not know anything about SQL then you need to learn it first because just using the java class is not sufficient.
     
    Relicum likes this.
  10. Offline

    TheLexoPlexx

    -_Husky_-
    Can I use...
    Code:
    if (MySQL.checkConnection() == true) {
    }
    in my Main Class to check if there is a Database and cancel the onEnable if not ?
     
  11. Offline

    -_Husky_-

    Sure can!
     
    TheLexoPlexx and CeramicTitan like this.
  12. Offline

    PratamaJr

    How to make players Statistic,level with SQL?
     
  13. Offline

    Fight_Or_Die

    How could you change table data with MySQL? I've tried pretty much everything here.. and anywhere else..
    This <-- is what I need help with.
     
  14. Offline

    ImDeJay

    -_Husky_-

    Can you use SQLite with this plugin?
     
  15. Offline

    Azubuso

  16. Offline

    WiggPerson

    Hello, this library is cool. But I still have one issue with NullPointerException at MySQL.querySQL(). My IDE says me, that MySQL.open() method doesn't exists. How to fix it?

    Code:java
    1. public ResultSet querySQL(String query) {
    2. Connection c = null;
    3.  
    4. if (checkConnection()) {
    5. c = getConnection();
    6. } else {
    7. c = openConnection();
    8. }
    9.  
    10. Statement s = null;
    11.  
    12. try {
    13. s = c.createStatement();
    14. } catch (SQLException e1) {
    15. e1.printStackTrace();
    16. }
    17.  
    18. ResultSet ret = null;
    19.  
    20. try {
    21. ret = s.executeQuery(query);
    22. } catch (SQLException e) {
    23. e.printStackTrace();
    24. }
    25.  
    26. closeConnection();
    27.  
    28. return ret;
    29. }
     
  17. Offline

    will181

    This is great, but I've got an issue at the moment while trying to use SQLite. Essentially, whenever the server starts, I am getting an error saying that the plugin was "Unable to create database!". I've looked through my code and can't seem to find an issue with it (although I am a complete noob when it comes to SQLite in Java).

    Here is my SQL Manager class:
    Code:java
    1. public class MySQLManager {
    2. private final MyPlot main;
    3. private SQLite db;
    4.  
    5. public MySQLManager(MyPlot h){
    6. this.main = h;
    7. }
    8.  
    9. public void setupDB() throws SQLException{
    10. String FolderName = "MyPlot";
    11. File Path = new File(this.main.getDataFolder()+ File.separator + FolderName);
    12.  
    13. if (!Path.exists()) {
    14. Path.mkdir();
    15. }
    16.  
    17. db = new SQLite(this.main, FolderName + File.separator + "plots.db");
    18. this.db.openConnection();
    19.  
    20. Statement statement = this.db.getConnection().createStatement();
    21. statement.executeUpdate("CREATE TABLE IF NOT EXISTS plots (id int, player varchar(32), position varchar(32), price int);");
    22. }
    23.  
    24. public void closeDB(){
    25. this.db.closeConnection();
    26. }
    27.  
    28. public String getOwner(Player player, int id) throws SQLException{
    29.  
    30. if(!this.db.checkConnection())
    31. this.db.openConnection();
    32.  
    33. Statement statement = this.db.getConnection().createStatement();
    34. ResultSet rs = statement.executeQuery("SELECT * FROM plots WHERE id=" + id + " AND position=owner;");
    35.  
    36. return rs.getString("player");
    37. }
    38.  
    39. public void setOwner(int id, String target) throws SQLException{
    40.  
    41. if(!this.db.checkConnection())
    42. this.db.openConnection();
    43.  
    44. Statement statement = this.db.getConnection().createStatement();
    45. statement.executeUpdate("REMOVE * FROM plots WHERE id=" + id + ";");
    46. statement.executeUpdate("INSERT INTO plots (id, player, position, price) VALUES (" + id + ", " + target + ", owner, -1);");
    47. }
    48. }
    49.  


    Please tell me if you need more of the code.
     
  18. Offline

    DigitalSniperz

    Plugin errors here,

    Code:java
    1. Statement statement;
    2. try {
    3. statement = MySQL.openConnection().createStatement();
    4. statement.executeQuery("INSERT INTO `maintoken`(`PlayersName`, `token`) VALUES ('" + e.getPlayer() + "', 0)");
    5. } catch (SQLException event) {
    6. p.sendMessage("Theres a error with the tokens system, we will try to fix this as soon as possible.");
    7. event.printStackTrace();
    8. }


    I've tried everything, INSERTING/UPDATING dosn't work but selecting does. The plugin connects to database as i made a command which tells if server has connected or not. The user has full permission to the database, i made sure of this, But still nothing happens just a error when i execute the command which this is on.
     
  19. Offline

    18cwatford

    -_Husky_-

    I was using your code, and since I feel like using SQLite for now I was using SQLite. I had noticed, though, that it was creating files in the wrong place, but at the same time also in the right place!

    Upon inspection of SQLite.java, I noticed in `openConnection` that you check if there is a file named `dbLocation` relative to where craftbukkit is being run, but later when you actually connect to the database you get the full location to the plugin's data folder.

    That means you were creating a file in the same directory of craftbukkit, but JDBC was creating one the right place. I changed the file so that way the `file` checks and creates in the right place, and it'd be nice if you did that too.
     
    -_Husky_- likes this.
  20. Offline

    TedTheTechie

    -_Husky_-

    I know this thread is old, but I still use this library and enjoy it.

    The only issue is, whenever I have multiple servers running on the same database, and when I have them fetch a users "gems", they don't sync on servers I added if the table already exists. I have to delete the table, and let it regenerate, then it updates.
    How can I fix that? It's getting annoying having players complain about their gems being reset.
     
  21. Offline

    Iroh

    Moved to resources.
     
    -_Husky_- likes this.
  22. Offline

    -_Husky_-

    Nice to hear you enjoy the library! :)

    PM me and we can discuss? I'll need to see some code so I can pinpoint the issue.

    Thanks, Husky.

    -- For others that need help, please PM me, I'll get notified much quicker and I can be of more assistance!

    Thanks for this! I don't use SQLite, so I hadn't come across this issue! :p

    Hopefully fixed- https://github.com/Huskehhh/MySQL/commit/b4fcdef728e130dfa0a56b7ba8a25368fa245d91

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Dec 20, 2015
  23. Offline

    Quaro

    executeQuery won't work, because this lib automatically closes connection to database, after every command. After closing, you cannot use ResultSet anymore...
     
  24. Offline

    -_Husky_-

  25. Offline

    Relicum

    TedTheTechie
    Can you explain a little more about the db scheme you have set up. While doing through this thread I notice a big recurring theme, just people need to learn about. MySQL is a relational database, meaning you scheme will most likely include many tables in them, with various relationships eg, One to One, One to Many etc. This will require as a couple have pointed out actually learning MySQL it self. I don't mean simple CRUD .

    The most important factors are missing from this thread, or if they are I haven't noticed them. The first is concerning specifying indexing on Tables. Also the data types you use for the index's. Use the wrong type or on a table that has more inserts than look-ups and you slow the DB down. If you response is you don't have enough data to warrant an index, then you shouldn't be using MySQL to start with as well managed flat file storage can handle 10,000 of players. The second which I will guarantee all but the experienced dev's won't even given it a thought and that's actually configuring the MySQL DB itself. Standard installs are errrr shit with any volume of transactions. There is close to 200 settings you can alter, and it will make a HUGE difference.

    Coming from a Web/Networking background I have spent years with DB's and the one thing I have learnt is unless you know what to expect your soon get found out. By that your start blaming MySQl for the lag in your plugin. This may well be the case, but that's pure development lag, poorly written or designed schemes. The point I am trying to make is if you are going to use MySQL make sure you have a valid reason to use it and not because lots of others are. I would estimate 75% of plugins that use MySQL are not gaining any benefit due to the latency, or lack of knowledge concerning MySQL.

    What I find funny is that 2-3 years ago when I started coding in MC my first thought was to hook up MySQL but a few people shared their knowledge that unless you have to avoid mysql, unless its for things like LogBlock, or you need multi MC servers to access the same data. So I followed that advice (thru gritted teeth) and handle ever use MySQL unless there is a valid performance gain.

    By the way this wasn't aimed at anybody, just sharing my knowledge of MySQL, if anybody needs any help with performance issues please just drop me a PM be glad to help.

    Regards
    Relicum
    PS I am Dyslexic so sorry about the errors.
     
  26. Offline

    -_Husky_-

    Relicum

    First of all, thanks for the offer to help with performance issues! :)

    I'd just like to point out that this Thread is about a Library used for connecting to a MySQL database from within Java, not Setting up/Installing a MySQL server (Maybe you should write one if one doesn't exist here already?), however, I truly do agree with you, there are for sure lots of optimizations that can be made to further help with performance, and not every plugin needs a database for storage.

    Thanks, Husky.
     
  27. Offline

    Quaro

    -_Husky_- This will work but... I prefer using my own (private) MySQL lib which closes it, and still makes it usable.
     
  28. Offline

    -_Husky_-

    Quaro

    That's entirely up to you. Regardless, thank you for the feedback. :)
     
  29. Offline

    jebbo

    I get the error "Cannot make a static reference to the non-static method openConnection() from the type MySQL"

    in this: c = MySQL.openConnection();


    Nevermind.
     
  30. Offline

    Neilnet

    Hey, umm this isn't that good for me as whenever I update a row or whenever I insert something (I believe) this opens a new connection hence causing lag and a tps drop to my server. Any solutions?
    None the less, amazing resource for people like me who want to save time.
     
Thread Status:
Not open for further replies.

Share This Page