[Tutorial] SQLite Setup

This is a tutorial I wrote up to help people learn how to use SQLite in their plugins. This seems like a pretty vague topic and I didn't see enough tutorials around, so I made one.

Okay, first you're going to need to go to this thread and follow the installation steps. Make sure you download the stable build, not the developer build.

Once the lib is inside your plugin, you're going to need to fix a couple small bugs I discovered in one of the lib files. Open your src folder, and you will see "lib.PatPeter.SQLibrary". Open that, and find SQLite.java. Open SQLite.java, and on line 113 (Pretty sure it's that one. It's the one after "default:") change the function from executeQuery(query); to execute(query);

Now, in the same file, go to lines 166-169 and replace this:

Code:

if (tables.next())
    return true;
else
    return false;

with this:

Code:

if (tables.next())
{
    tables.close();
    return true;
}
else
{
    tables.close();
    return false;
}

Once you have done this, the bugs are fixed. Now you can setup SQLite. To do this, I have written a small sample you may look at. Obviously, fill in the extra parts you want, such as config and event registering.

Code:

public class Yourplugin extends JavaPlugin {
 
    public SQLite sqlite; //This creates a variable you will use a lot. You can call it what you like, but I prefer just sqlite.
 
    public void onDisable() {
 
        sqlite.close(); //This closes the connection.
 
    }
 
    public void onEnable() {
 
        sqlConnection();
        sqlTableCheck();
//These functions are in the next couple code blocks.
 
    }

Now we will add the functions. This is sqlConnection(); It will connect to your SQLite database.

Code:

public void sqlConnection() {
sqlite = new SQLite(plugin.getLogger(),
                "Plugin Name",
                "File Name (Dont include .db)",
                plugin.getDataFolder().getAbsolutePath());
//Make sure sqlite is the same as the variable you specified at the top of the plugin!
try {
sqlite.open();
    } catch (Exception e) {
        plugin.getLogger().info(e.getMessage());
        getPluginLoader().disablePlugin(plugin);
    }
}

That's simple right? No usernames or passwords, nothing for your users to setup, it's wonderful.

Ok, next function. sqlTableCheck(); This checks if the table exists already, so we don't try to make it twice.

Code:

public void sqlTableCheck() {
    if(sqlite.checkTable("table_name")){
  return;
    }else{
  sqlite.query("CREATE TABLE table_name (id INT PRIMARY KEY, playername VARCHAR(50),  registerdate VARCHAR(50));");
 
        sqlite.query("INSERT INTO table_name(playername, registerdate) VALUES('Pew446', '08/09/2012');"); //This is optional. You can do this later if you want.
 
        plugin.getLogger().info("table_name has been created");
    }
}

Obviously this is an example table and you can format it any way you'd like. Just learn some SQL, and have fun! Also remember, if you create a query that returns a ResultSet (SELECT is the only query that returns a ResultSet), you need to make sure you close it before you call another query.

So if you said
ResultSet result = sqlite.query("SELECT * FROM table_name WHERE playername='Pew446';");

You couldn't call another query without first doing result.close(), which means you are no longer able to get data from that query. So make sure you get all the data you need before you close it.

I hope I helped! It was a pain in the butt to figure out, so I hope I helped some people avoid what I had to spend forever learning how to do.


Learn SQL: http://www.w3schools.com/sql/

Thanks to PatPeter for the awesome SQLite libraries.
Thanks to Neodork for some example code he posted that I based this code off of.
And of course, thanks to all the wonderful people on the Bukkit Forums.

 

Good job bro ;> Very helpful.

 

execute(query) spits out an error that it cant convert it for me. Boolean to ResultSet

 

This never happened for me. Do you have an error log? Where are you calling the query? You can't use a query as a boolean, like:
if(sqlite.query("SELECT * FROM table WHERE id=10;"))
return;

 

I keep getting this error during onPlayerChat if my plugin's config is modified while the server is running:

Code:

2012-09-04 18:18:12 [SEVERE] java.sql.SQLException: ResultSet closed
2012-09-04 18:18:12 [SEVERE]at org.sqlite.RS.checkOpen(RS.java:63)
2012-09-04 18:18:12 [SEVERE]at org.sqlite.RS.findColumn(RS.java:108)
2012-09-04 18:18:12 [SEVERE]at org.sqlite.RS.getInt(RS.java:293)
2012-09-04 18:18:12 [SEVERE]at me.angrynerd.PvPRank.PvPRank.onPlayerChat(PvPRank.java:168)
2012-09-04 18:18:12 [SEVERE]at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
2012-09-04 18:18:12 [SEVERE]at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
2012-09-04 18:18:12 [SEVERE]at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
2012-09-04 18:18:12 [SEVERE]at java.lang.reflect.Method.invoke(Unknown Source)
2012-09-04 18:18:12 [SEVERE]at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:330)
2012-09-04 18:18:12 [SEVERE]at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
2012-09-04 18:18:12 [SEVERE]at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:477)
2012-09-04 18:18:12 [SEVERE]at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:459)
2012-09-04 18:18:12 [SEVERE]at net.minecraft.server.NetServerHandler.chat(NetServerHandler.java:830)
2012-09-04 18:18:12 [SEVERE]at net.minecraft.server.NetServerHandler.a(NetServerHandler.java:807)
2012-09-04 18:18:12 [SEVERE]at net.minecraft.server.Packet3Chat.handle(Packet3Chat.java:44)
2012-09-04 18:18:12 [SEVERE]at net.minecraft.server.NetworkManager.i(NetworkManager.java:216)
2012-09-04 18:18:12 [SEVERE]at net.minecraft.server.NetworkManager.c(NetworkManager.java:331)
2012-09-04 18:18:12 [SEVERE]at net.minecraft.server.NetworkReaderThread.run(SourceFile:93)

Source here: http://www.angrynerd.me/plugins/PvPRank/0.2a/src/PvPRank.zip

 

I get this error when i start the server and it tries to enable my plugin

Code:

Error occurred while enabling Apartments v1.0 (Is it up to
date?)
java.lang.NullPointerException
        at com.hotmail.oliven_666.bukkitPlugin.bukkitPlugin.sqlConnection(bukkit
Plugin.java:55)
        at com.hotmail.oliven_666.bukkitPlugin.bukkitPlugin.onEnable(bukkitPlugi
n.java:29)
        at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:217)
        at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader
.java:374)
        at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManage
r.java:381)
        at org.bukkit.craftbukkit.CraftServer.loadPlugin(CraftServer.java:270)
        at org.bukkit.craftbukkit.CraftServer.enablePlugins(CraftServer.java:252
)
        at net.minecraft.server.MinecraftServer.i(MinecraftServer.java:298)
        at net.minecraft.server.MinecraftServer.d(MinecraftServer.java:277)
        at net.minecraft.server.MinecraftServer.a(MinecraftServer.java:227)
        at net.minecraft.server.DedicatedServer.init(DedicatedServer.java:140)
        at net.minecraft.server.MinecraftServer.run(MinecraftServer.java:378)
        at net.minecraft.server.ThreadServerApplication.run(SourceFile:539)

I copied your example methods into my code but it still wont work :/
Any ideas on how to fix this? Thank you in advance

 

I am not an expert, but I believe that this means that you used an older API in your code than the server is running. You may want to redownload the bukkit API, and add it as an external archive. Then it should work. Please let me know!

 

xBlazeTECH look @ the date he posted ;p