Yaml to MySQL

So I’ve been steadily porting over my plugins as Sponge gains more features but I’m looking at converting a Yaml storage system to MySQL. I’ve used MySQL a fair bit in PHP though haven’t been able to find anything for Java, even after a bit of Google’ing (possibly bad luck).

How would one get started with MySQL for Java. I have chosen to use @MrMysteri0us’s DBManager if that changes how queries are handled.

Well sadly enough their isn’t a converter thingy. In java mysql gets handled by jdbc. You could go the manual way and make your queries in strings and execute them from a connection. Honestly I don’t think thats handy, it feels a bit like doing ASM with SQL (at least for me it does).

To avoid the usage of full queries, you could use the javax.persistence library (was included in bukkit, not sure for sponge). It makes it possible to make classes like this:

public class MyBook {
  @Column(name = "ID")
  public int myId;
  
  @Column(name = "TITLE")
  public String myTitle;
}

And if you do a insert you could just give the class. What looks already a bit cleaner.

Another possibility is using the jooq library. Well its very unlikely sponge will include it, so you probably have to shade it in your plugin. Using jooq quarries like this are possible:

//note Player is not from the sponge API, Its a wrapped class
public void onPlayerJoin(Player player){
        checkConnect();
        create.insertInto(PLAYERS).
                set(create.newRecord(PLAYERS, player)).
                onDuplicateKeyUpdate().
                set(PLAYERS.USERNAME, player.getUserName()).
                execute();

    }

My database class using jooq (yeah it needs some more refactoring)

The DBmanager from @MrMysteri0us just handles your connection, it makes sure you have the correct mysql information. It also loads the correct drivers.

I might allow for query execution through my lib on a later date.

I would not use the persistence feature of Java.
I tried it, it’s so hard to implement.

Just use JDBC, it’s much easier.

You can find an example in my plugin. It’s not tested:
https://github.com/boformer/Proto/blob/world-name-instead-of-uuid/src/main/java/com/github/boformer/proto/data/DataManager.java

1 Like

Yeah I am also not using it, I just gave it as a possibility.

For the creation of your tables, I would recommend using flyway. So you don’t need to worry about other people their database design if you update your plugin. The only problem with flyway at this, point is that the gradle support is very poor sadly enough (one of the main reasons I am still using maven).

1 Like

Sidenote: There’s an underlying problem of using a MySQL-only solution for your plugin; If MySQL isn’t installed you can’t use the plugin.

All server companies I’ve seen offer a MySQL database with the server. If you are hosting it locally, it’s not hard to set up a database on your own machine.

That still leaves out the servers not having a MySQL database or people who cant afford database servers etc( I know it’s cheap but still there are always different kinds of people out there )

I suggest you to use an embedded database like sqlite or H2 database for data storage, if its for configurations use a flatfile

Just wanted to say: when I saw your post I thought I’d try flyway instead of eventually making my own homebrew solution (again) for migrations. Integrating it with c3p0 (jdbc pooling) was incredibly easy and after a couple glitches it works nicely. Support for multiple database schema types is also pretty straightforward. +1 :smiley:

3 Likes