SQLibrary
I would highly suggest using Bukkit's plugin databases API instead of this plugin.
SQLibrary
SQLibrary was taken over from alta189 by PatPeter. So far it has support for 13 databases, a factory, and one query builder.
Tutorial
First, add these elements to your pom.xml if you're using Maven:
<repository> <id>dakani</id> <name>Dakani Nexus Repo</name> <url>http://repo.dakanilabs.com/content/repositories/public</url> </repository> <dependency> <groupId>lib.PatPeter.SQLibrary</groupId> <artifactId>SQLibrary</artifactId> <version>7.1</version> </dependency>
You should always save implementations to a superclass, unless that child class has more functionality. So, start off by creating your variable:
private Database sql;
Constructing your database object can be either hardcoded into the definition, or assigned in the method. The constructor does not automatically open the database connection so that it does not have to throw an SQLException (though this was considered for quite some time). Below are the constructors for the most popular databases: MySQL, SQLite, and H2.
sql = new MySQL(Logger.getLogger("Minecraft"), "[MyPlugin] ", "localhost", 3306, "myplugin", "minecraft", "password1"); sql = new SQLite(Logger.getLogger("Minecraft"), "[MyPlugin] ", this.getDataFolder().getAbsolutePath(), "MyPlugin", ".sqlite"); sql = new H2(Logger.getLogger("Minecraft"), "[MyPlugin] ", this.getDataFolder().getAbsolutePath(), "MyPlugin", ".h2");
For SQLite and H2, the extension is optional but will default to ".db". For MySQL, the hostname and port number are optional, but will default to those values. Next, we need to open our database connection:
if (sql.open()) { // ... }
If you know that your plugin will only use the database intermittently, you should check to see if it is open first:
if (!sql.isOpen()) { sql.open(); }
If any errors occur while opening the database connection, they will print to console.
Supported Databases
- Firebird
- FrontBase
- DB2
- H2
- Informix
- Ingres
- MaxDB
- MicrosoftSQL
- MySQL
- Mongo
- mSQL
- Oracle
- Ovrimos
- PostgreSQL
- SQLite
Installation
- Download the jar.
- In Eclipse, go to Project Properties ==> Java Build Path ==> Add External Jar and add SQLibrary.jar.
- Tell your users to install SQLibrary.jar along with your plugin
Do not install from source!
If you install SQLibrary directly into your plugin, the following bad things will occur:
- You cannot run your plugin alongside SQLibrary.jar plugins. Bukkit will (usually) find the SQLibrary classes in your plugin first, and block the SQLibrary.jar dependency.
- You cannot run your plugin alongside other plugins using SQLibrary directly from source. Same issue.
- Updating will definitely cause these problems and cause you much pain.
Download SQLibrary from your plugin
You can now place this code in your plugin to download and load SQLibrary from your plugin at runtime:
http://dev.bukkit.org/bukkit-plugins/sqlibrary/pages/soft-dependency-download/
Special Thanks
- alta189, for starting this library.
- Belphemur, for adding the first version of our factory package.
- omwah, for adding Maven to SQLibrary.
- moose51789, for adding Spout support.
- Mitsugaru, for hosting our Maven repository, finding bugs, and making fixes.
- max9403, for supplying the code to download SQLibrary at runtime.
Also, I really need a tutorial on how to write database queries IN JAVA. For some reason my queries return a NullPointerException!
Is there a link to a JavaDoc?
Thank you for the comment bgsteiner! I didn't even know that that page existed (it's fairly new). I'll edit that content out this weekend.
I just want to say using the code to download the library is against the guidelines for submission and this code should not be offered. You cannot automatically download dependencies as per regulations.
"You should not automatically download plugin dependencies. If your plugin has a dependency on another plugin hosted on BukkitDev, provide a link to that plugin and state it is a dependency." http://wiki.bukkit.org/BukkitDev:Project_Submission_Guidelines
@mcbuild
Statement validation worked! The exception is telling you that the plugin is trying to execute the SHOW command on SQLite, which does not exist on SQLite. Unless the plugin uses some crazy version of SQLite that uses non-standard SQLite statements, it is a bug in the plugin.
Also, if you ever need to post a stack trace, or are seeking extended help, please use the Help forum:
http://dev.bukkit.org/bukkit-plugins/sqlibrary/forum/
@mcbuild
Then you should be asking questions and posting stack traces on the plugin's project page, not the library it's using.
Also, it could be that they haven't updated for 7.x compatibility,
considering the plugin you mention last posted a release in Oct 2013. So, try using SQLibrary 6.1 instead and see if it works. And if that's the case, get the author to update. Or at the very least put a compatibility notice on their downloads page in the plugin release notes.Correction, the Bukkit project page hasn't been updated with releases. Needless to say, this is a plugin specific issue, not a library issue. You need to seek help with the plugin author(s).
The plugin I want to use has an SQLite option, but when I set it to use SQLite I get an error message.
Error Message: [21:14:41 WARN]: java.sql.SQLException: Unknown statement: "SHOW". [21:14:41 WARN]: at lib.PatPeter.SQLibrary.SQLite.getStatement(SQLite.java:152) [21:14:41 WARN]: at lib.PatPeter.SQLibrary.SQLite.getStatement(SQLite.java:1) [21:14:41 WARN]: at lib.PatPeter.SQLibrary.Database.prepare(Database.java:290) [21:14:41 WARN]: at de.ftbastler.bukkitgames.h.j.c(SQL.java:138) [21:14:41 WARN]: at de.ftbastler.bukkitgames.main.BukkitGames.onEnable(BukkitGames.java:507) [21:14:41 WARN]: at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:218) [21:14:41 WARN]: at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:457) [21:14:41 WARN]: at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:385) [21:14:41 WARN]: at org.bukkit.craftbukkit.v1_7_R1.CraftServer.loadPlugin(CraftServer.java:302) [21:14:41 WARN]: at org.bukkit.craftbukkit.v1_7_R1.CraftServer.enablePlugins(CraftServer.java:284) [21:14:41 WARN]: at net.minecraft.server.v1_7_R1.MinecraftServer.m(MinecraftServer.java:348) [21:14:41 WARN]: at net.minecraft.server.v1_7_R1.MinecraftServer.g(MinecraftServer.java:325) [21:14:41 WARN]: at net.minecraft.server.v1_7_R1.MinecraftServer.a(MinecraftServer.java:281) [21:14:41 WARN]: at net.minecraft.server.v1_7_R1.DedicatedServer.init(DedicatedServer.java:186) [21:14:41 WARN]: at net.minecraft.server.v1_7_R1.MinecraftServer.run(MinecraftServer.java:430) [21:14:41 WARN]: at net.minecraft.server.v1_7_R1.ThreadServerApplication.run(SourceFile:617) [21:14:41 ERROR]: Error occurred while enabling BukkitGames v2.1.6 (Is it up to date?) java.lang.NullPointerException at de.ftbastler.bukkitgames.main.BukkitGames.onEnable(BukkitGames.java:507) [?:?] at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:218) [craftbukkit.jar:git-Spigot-1239] at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:457) [craftbukkit.jar:git-Spigot-1239] at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:385) [craftbukkit.jar:git-Spigot-1239] at org.bukkit.craftbukkit.v1_7_R1.CraftServer.loadPlugin(CraftServer.java:302) [craftbukkit.jar:git-Spigot-1239] at org.bukkit.craftbukkit.v1_7_R1.CraftServer.enablePlugins(CraftServer.java:284) [craftbukkit.jar:git-Spigot-1239] at net.minecraft.server.v1_7_R1.MinecraftServer.m(MinecraftServer.java:348) [craftbukkit.jar:git-Spigot-1239] at net.minecraft.server.v1_7_R1.MinecraftServer.g(MinecraftServer.java:325) [craftbukkit.jar:git-Spigot-1239] at net.minecraft.server.v1_7_R1.MinecraftServer.a(MinecraftServer.java:281) [craftbukkit.jar:git-Spigot-1239] at net.minecraft.server.v1_7_R1.DedicatedServer.init(DedicatedServer.java:186) [craftbukkit.jar:git-Spigot-1239] at net.minecraft.server.v1_7_R1.MinecraftServer.run(MinecraftServer.java:430) [craftbukkit.jar:git-Spigot-1239] at net.minecraft.server.v1_7_R1.ThreadServerApplication.run(SourceFile:617) [craftbukkit.jar:git-Spigot-1239] [21:14:41 INFO]: [EssentialsProtect] Enabling EssentialsProtect v2.12.1 [21:14:41 INFO]: [EssentialsSpawn] Enabling EssentialsSpawn v2.12.1 [21:14:41 INFO]: [EssentialsAntiBuild] Enabling EssentialsAntiBuild v2.12.1 [21:14:41 INFO]: Server permissions file permissions.yml is empty, ignoring it [21:14:41 INFO]: Done (20.537s)! For help, type "help" or "?" [21:14:41 INFO]: [Skript] Checking for the latest version of Skript... [21:14:42 INFO]: [Skript] All scripts loaded without errors! [21:14:42 WARN]: [Skript] No scripts were found, maybe you should write some ;) [21:14:42 INFO]: [Skript] Skript finished loading! [21:14:42 WARN]: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such table: bg_plays) [21:14:42 WARN]: at org.sqlite.DB.newSQLException(DB.java:383) [21:14:42 WARN]: at org.sqlite.DB.newSQLException(DB.java:387) [21:14:42 WARN]: at org.sqlite.DB.throwex(DB.java:374) [21:14:42 WARN]: at org.sqlite.NestedDB.prepare(NestedDB.java:134) [21:14:42 WARN]: at org.sqlite.DB.prepare(DB.java:123) [21:14:42 WARN]: at org.sqlite.PrepStmt.<init>(PrepStmt.java:42) [21:14:42 WARN]: at org.sqlite.Conn.prepareStatement(Conn.java:404) [21:14:42 WARN]: at org.sqlite.Conn.prepareStatement(Conn.java:399) [21:14:42 WARN]: at org.sqlite.Conn.prepareStatement(Conn.java:383) [21:14:42 WARN]: at lib.PatPeter.SQLibrary.Database.prepare(Database.java:291) [21:14:42 WARN]: at de.ftbastler.bukkitgames.h.j.c(SQL.java:138) [21:14:42 WARN]: at de.ftbastler.bukkitgames.h.c$5.run(Game.java:549) [21:14:42 WARN]: at org.bukkit.craftbukkit.v1_7_R1.scheduler.CraftTask.run(CraftTask.java:58) [21:14:42 WARN]: at org.bukkit.craftbukkit.v1_7_R1.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:53) [21:14:42 WARN]: at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [21:14:42 WARN]: at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [21:14:42 WARN]: at java.lang.Thread.run(Thread.java:744) [21:14:42 WARN]: [BukkitGames] Error while loading stats from the database! [21:14:42 WARN]: java.lang.NullPointerException [21:14:42 WARN]: at de.ftbastler.bukkitgames.h.c$5.run(Game.java:552) [21:14:42 WARN]: at org.bukkit.craftbukkit.v1_7_R1.scheduler.CraftTask.run(CraftTask.java:58) [21:14:42 WARN]: at org.bukkit.craftbukkit.v1_7_R1.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:53) [21:14:42 WARN]: at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [21:14:42 WARN]: at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [21:14:42 WARN]: at java.lang.Thread.run(Thread.java:744)
@mcbuild
Bukkit has a JDBC driver to SQLite and MySQL embedded in it. You should be able to plug and play SQLibrary.
Can someone post the link to SQLite? I cant find it...
@mcbuild
Stop trying to use MySQL and use SQLite. Usually it requires no configuration and you just specify where you want the database to be saved.
@Jdon278
Thank you for using SQLibrary jonneystechcheck!
When you say it will automatically close, what do you mean? If you manually close the ResultSet, do you still get this error? Try closing all of your ResultSets before the UPDATE.
Hey, just started using this, I am currently making a ticket plugin and have made it when you view a ticket it will automatically close it, but I keep getting the same error "java.sql.SQLException: [SQLITE_BUSY] The database file is locked (database is locked)" and the stacktrace points back to this line of code,
prior to closing the ticket, I am running a query to get data from the sqlite database.As you said in your post, I am checking whether the connection is open and if it is not it opens it. I am currently running the latest version 7.1 but I have also tried 6.1
I pray nothing is broken! 7.1 still needs approval on Bukkit. Also, max9403 wrote a method to load SQLibrary at runtime. I've added this to the description as well as a Special Thanks section. Hopefully I didn't miss anyone.
I really want to get back to connection pooling this weekend. I don't know how many people would need/use it, but it would be nice to have.
I'm also going to modify max9403's method to download the JDBC drivers of other databases. This has been a project of mine for a while as evidenced by config.yml in the repository.
Finally, I'm thinking of adding named databases, so that a user defines each database in the config.yml of SQLibrary and then plugins can load databases by a user-given name. This way you wouldn't have to copy and paste your database username and password all over your plugins directory.
Sorry for the delay, SQLibrary 7.1 available on the Maven repo now.
Im trying to use it with bukkit games. http://www.spigotmc.org/resources/bukkitgames.279/ I did setup a SQL database http://www.freesqldatabase.com and it says its live.. But I get the console message saying no connection could be established. Also what is this mystical sqlite I keep hearing about? I can't find any sqlite plugins as it sounds like it requires less work.. I have the credentials setup I believe correctly,
Dammit! I forgot to fix SQLibrary yesterday. I'm going to see if I can get a quick release out tonight!
@MGbeenieboy
Could you please elaborate? It might be part of a current bug.
@mcbuild
The others are correct. What plugin(s) are you trying to use it with?
@mcbuild
It sounds like you didn't configure your plugins.
There is no configuration necessary for SQLibrary, much like Vault. Instead, the configuration is handled per plugin that requires database access. So, you should look to those plugins config files and setup the correct credentials there.
@mcbuild
Soo, this is just a link from your own plugin (or a plugin from s.o. else) to a SQL-Server. You have to set up a mysql server by your own, google it, there are good tutorials. If you're useing this in your own plugin, you have to add depend: [SQLibrary] in your plugin.yml before the commands:
Hope i could help you
How do I set this thing up? The instructions above were no help and when I downloaded it and put it in my plugins folder, then when I load my server I get messages from the plugins that require this telling me they couldn't establish a connection to the data base so they don't work.
fuuuuck.the 7.0 update killed my data