MySQL database integration with your plugin.
-
Contents
Prerequisites(top)
You need to install JDBC first, it's available from: http://dev.mysql.com/downloads/connector/j/5.1.html#downloads
Basic explanation on JDBC(top)
JDBC is the Java Database Connectivity API which allows working with SQL. Spigot provides the J MySQL Connector which let's you connect to MySQL databases and run MySQL queries in the Java language.
Code examples(top)
We must create a few variables that can let us connect to the database. This is what we need to create. (Note, these are field variables not local variables).
Now we need to connect to the database using the Connection variable we made that is provided by Java. We do this by assigning properties to our connection variable in the onEnable and then trying to connect. This is the code we need to use:Code (Java)://DataBase vars.
final String username="YOUR DB USERNAME"; // Enter in your db username
final String password="YOUR DB PASSWORD"; // Enter your password for the db
final String url = "jdbc:mysql://db4free.net:3306/DataBaseName"; // Enter URL with db name
//Connection vars
static Connection connection; //This is the variable we will use to connect to database
Again, put this code in the onEnable(); method because we're trying to connect as soon as we start the server.
Now it's time to let our plugin close the connection, since we don't want useless connections still lingering around. We're going to do this by closing the connection variable in the onDisable() method. So when the server closes, we don't need the connection to still be there anymore.Code (Java):
try { // try catch to get any SQL errors (for example connections errors)
connection = DriverManager.getConnection(url, username, password);
// with the method getConnection() from DriverManager, we're trying to set
// the connection's url, username, password to the variables we made earlier and
// trying to get a connection at the same time. JDBC allows us to do this.
} catch (SQLException e) { // catching errors
e.printStackTrace(); // prints out SQLException errors to the console (if any)
}
If you can seem to get a connection established, then you can execute queries and updates. Here's a quick tip, whenever you want to modify the database, use Statement#executeUpdate, but if you just want to grab information from the database, simply do Statement#executeQuery.Code (Java):public void onDisable() {
// invoke on disable.
try { // using a try catch to catch connection errors (like wrong sql password...)
if (connection!=null && !connection.isClosed()){ // checking if connection isn't null to
// avoid receiving a nullpointer
connection.close(); // closing the connection field variable.
}
} catch(Exception e) {
e.printStackTrace();
}
}
First, we must create a table.
After that, we are all set to update and query from the database.Code (Java):String sql = "CREATE TABLE IF NOT EXISTS myTable(Something varchar(64));";
// prepare the statement to be executed
try {
PreparedStatement stmt = connection.prepareStatement(sql);
// use executeUpdate() to update the databases table.
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
This code would print out "Failed" because we have no values to select. Lets add some now.Code (Java):String sql = "SELECT * FROM myTable WHERE Something=?"; // Note the question mark as placeholders for input values
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, "Something"); // Set first "?" to query string
ResultSet results = stmt.executeQuery();
if (!results.next()) {
System.out.println("Failed");
} else {
System.out.println("Success");
}
Now if we where to rerun the previous query code we would get "Success" because a value has been inserted and selected for us to view.Code (Java):String sql = "INSERT INTO myTable(Something) VALUES ('?');";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, "Something"); // Set the first "?" to "Something"
stmt.executeUpdate();
If used in a Spigot plugin you should make sure that your database queries don't run on the main server thread (by using BukkitScheduler#runTaskAsynchronously) unless absolutely necessary
Recommended Libraries(top)
There are several libraries of different types available to make working with MySQL databases easier.
Connection Pools(top)
The major design principle that you are probably want to use are connection pools to allow sharing of multiple connections and not have to wait on your current ones. Java's SQL API includes a simple connection pooling design but additional libraries like HikariCP simplify their usage and increase their performance. Just remember to close all your connections directly after you used them so that they can be re-used by the pool.
Object-Relational Mapping (ORM)(top)
ORM is a method to directly map Java objects to their representation in a database without having to write your own queries or table structures, they do that all for you.
Commonly used libraries that offer that are OrmLite (Supports MySQL, Postgres, Microsoft SQL Server, H2, Derby, HSQLDB, and not only Sqlite) as well as Hibernate (an advanced and possible industry standard ORM solution)
Query simplification and general helpers(top)
SansOrm is a library which aims to replace ORMs by allowing you to still have full control over your table scheme as well as your queries while still massively reducing the boilerplate compared to writing traditional SQL queries.
IDB (Intuitive Database Wrapper) aims to simplify the boilerplate of creating SQL queries and integrates with HikariCP as well. - Loading...
- Loading...
XenCarta PRO
© Jason Axelrod from 8WAYRUN.COM