Hello! This is now my second third post on this forum, and at the same time I'll mark this as my 'welcome to this community' post since I've seen no "welcome thread" or anything like that. I'm currently working on developing a Minecraft plug-in with someone. I haven't actually spoke with this someone about how public we're going to be at this point yet so I'm not going to give up the details of this project just yet. One of the things that we wanted to do in our first ever project was to use SQLite as a database back-end. Another thing I wanted to use was Liquibase, which is a version control system for databases. In this tutorial I will go through the process of embedding the SQLite JDBC to your project and using Liquibase for your database updates. ------------------------------------------------------------------------------------------------------------ Step 1: Downloading the required libraries The first step is to acquire the libraries we're going to need. There are two: The SQLite JDBC library is needed in order to make use of an SQLite database in Java. The version I have used is sqlitejdbc-v056.jar, although newer versions should work fine. Liquibase, except the latest version doesn't work properly with SQLite, so you'll need version 1.9.0, which you can download here. The two .jar files need to be stored in your libraries folder. If you don't have one, then you should create one somewhere on your system. Since that folder is potentially going to contain all of your libraries you'll want to keep it in a central area. Step 2: Adding the libraries to your Bukkit plug-in project The next step is adding the libraries to your project. You can do this by right-clicking your project and clicking "Properties". Then, go to the "Java Build Path" and to the "Libraries" tab. You can add the .jar files by clicking the button "Add External JARs". You can add both of them at once by using Ctrl+Click to select them. This is where you can add external libraries for use in your project.Step 3: Embedding the codeTwo classes are required in order to make this work: the first is a class I wrote to speed things up, and the second is a class needed by Liquibase to load files from the current classpath in Bukkit.Download "Database.java"Download "BukkitClassLoaderFileOpener.java"Step 4: Creating your db-changelog.xmlYou will need to create a file called "db-changelog.xml" which exists somewhere in your project. In my case I placed it in the same package as the main plug-in class.This file will contain all the changes to the database you wish to make.Note: It is possible to split changes up into multiple files (one for each version of your plug-in for instance) but I have not tested the "include" functionality of Liquibase yet. In its current form all of your database changes are going in one file, which shouldn't be a problem for small projects.The following is an example "db-changelog.xml" file: Code: <?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd"> <changeSet id="1" author="[email protected]"> <sql> CREATE TABLE settings ( name character varying(20) NOT NULL, value text, CONSTRAINT settings_pkey PRIMARY KEY (name) ); </sql> </changeSet> </databaseChangeLog> Step 5: Change the location of "db-changelog.xml" in the "Database" class. Now you will need to go to the "Database" class and change the location of the "db-changelog.xml" file. This is located on line 26 (as of 26-11-11) and looks like this: Code: final Liquibase liquibase = new Liquibase("mc/plugin/btp/db-changelog.xml", new BukkitClassLoaderFileLoader(), conn); Replace the "mc/plugin/btp/db-changelog.xml" bit with the location of your changelog file. Note: You should not copy/paste this code for each additional file you want, but instead you should make use of the include functionality. See also: Liquibase Best Practices. Once the proper location has been provided Liquibase should be ready. However this is the point at which I had the most errors (with the file not being found) so if you run into any problems reply to this thread and I'll create a F.A.Q. here. Step 6: Initializing the database We are finally ready to include the Database class in your project. To do this, go to your plug-in class and add a line to your onEnable and onDisable methods. See the snippet below: Code: package mc.plugin.btp; import java.util.logging.Logger; import org.bukkit.plugin.java.JavaPlugin; public class BukkitTutorialProject extends JavaPlugin { private static final Logger log = Logger.getLogger("Minecraft"); @Override public void onEnable() { Database.init(); //you'll need to add this line, which initializes the database log.info("BukkitTutorialProject v" + Constants.VERSION + " loaded."); } @Override public void onDisable() { Database.shutdown(); //you'll also need to add this line, which gracefully shuts it down again log.info("BukkitTutorialProject unloaded."); } } Your project should now be SQLite and Liquibase-ready. Step 7: Testing your project In order to use my approach you will need to use this tutorial to add the libraries to your server. Please note: as of 26-11-11 I have replied to that post with a fix for the start-up script. If the script provided in the tutorial doesn't work you should try my fix. The libraries that will need to go into the "libs" folder are the same libraries that you downloaded in step 1. Once your project starts up it should display something like this: If it does, it means my tutorial has worked and you now have set SQLite/Liquibase up on your project. If there's an error instead, post it here and I'll create a FAQ. And please: if you have a solution to your own problem, post it here so others can benefit from it! This is the end of my tutorial. I hope I haven't been too vague and that the tutorial will work as-is for anyone that wants SQLite/Liquibase support in their projects. ------------------------------------------------------------------------------------------------------------ Download the sample project here. Addendum #1: Using the Database object: Statements You can use Statements to quickly execute an SQL query on the database. Example code: Code: Statement stmt = null; ResultSet rs = null; try { stmt = Database.getConnection().createStatement(); rs = stmt.executeQuery("SELECT * FROM mytable WHERE id = 1"); if(rs.next()) { //Handle the query } } catch (final SQLException e) { throw new RuntimeException(e); } finally { JO.closeResultSetAndStatement(rs, stmt); } Addendum #2: Using the Database object: PreparedStatements While Statements allow you to perform requests on the database this is far less secure as SQL injections are still possible. In order to prevent this you can use PreparedStatements to perform the same query, except with the input taken literally. Example code: Code: final String input = "You can't trust me!"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = Database.getConnection().prepareStatement("SELECT * FROM mytable WHERE myfield = ?"); pstmt.setString(1, input); rs = pstmt.executeQuery(); if(rs.next()) { //Handle the query } } catch (final SQLException e) { throw new RuntimeException(e); } finally { JO.closeResultSetAndStatement(rs, pstmt); } Note: When in doubt, use PreparedStatements as they are more secure. Note: You MUST close your Statements and ResultSets after using them in order to avoid memory leaks. Addendum #3: JO.closeResultSetAndStatement This handy method closes a given ResultSet and Statement (or PreparedStatement) object. It is used above to ensure that the statements are properly closed. The method: Code: public static void closeResultSetAndStatement(final ResultSet rs, final Statement stmt) { if (rs != null) { try { rs.close(); } catch (final SQLException e) { /* do nothing */ } } if (stmt != null) { try { stmt.close(); } catch (final SQLException e) { /* do nothing */ } } } EDIT by Moderator: merged posts, please use the edit button instead of double posting.
