Tutorial Using MySQL In your Plugins!

Add a way to UPDATE things because INSERT writes in new rows.

 

Theodossis
Umm, just use what's in the first post? Its already there...
Just change the SQL query...

 

Could you give an example of the best way to use this across multiple class files? For example, say I have a class for a specific command that I want to run a query when used, how would this be most efficiently used if I wanted multiple commands to use the MySQL database?
Thanks!

 

@-_Husky_-
Umm.. Why is there 5 parameters in the MySQL constructor, but in the main class, it's called like this:

Code:java

MySQL MySQL = new MySQL("host.name", "port", "user", "pass");

 

@ shinobuattack

Code:java

MySQL mySequel = new MySQL("host", "port", "db", "username", "password");

Make sure you have the database you want to connect to.

 

And, if you know this, please tell me how what function/command I need to use to create a table, that let's say, tracks player names and how much money they have.. Can you give me an example?

Rockett8855
What is the database? sorry, I have no clue about MySQL.

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

My getters and setter for mysql class, but I must admit I'm moving more towards PreparedStatements.
https://gist.github.com/Rockett8855/6055992

Also for a mysql tutorial:
http://www.w3schools.com/sql/default.asp

 

Rockett8855
Thanks for the examples on that, the examples provided in this post were a bit too tricky for me, it just didn't click with me. Anyways, I appreciate this a lot

 

shinobuattack
Just google MySQL tutorials or go on Youtube, that's how I learned.

 

gomeow
Rockett8855
Is it required that I learn SQL and do I have to actually download the MySQL software? I don't really wanna do that because I have enough on my hands trying to learn Java and Bukkit

 

shinobuattack
Yes, you need to learn SQL
No, but you do need a MySQL server available (MAMP and WAMP are great for local servers)

 

gomeow
Sorry, I'm not familiar with those at all, but basically, WAMP or MAMP are like craftbukkit? Correct me if I am wrong, please.

 

shinobuattack
You should probably work on learning Java and Bukkit and understanding what they are/do before you work on integrating databases.

 

http://www.wampserver.com/en/

 

I've been trying to insert into my table using the following:

Code:

statement.executeUpdate("INSERT INTO players ('name', 'money') VALUES ('" + playername + "', '5')");

However, it is coming up with the following error:

Code:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax

Any solutions? Thanks.

 

Remove " ' " from: ('name', 'money')
EDIT: You need the rows without " ' "

 

Umm. I Have Come Across an error Selecting a players name.

STack Trace:

15:28:35 [INFO] ManG0eZPvP[/127.0.0.1:56328] logged in with entity id 1225 at ([
world] 213.7413499695281, 8.360465714385517, -138.98308304615682)
15:28:35 [SEVERE] Could not pass event PlayerJoinEvent to MySQL v1.0
org.bukkit.event.EventException
at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.ja
va:427)
at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.jav
a:62)
at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.j
ava:477)
at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.j
ava:462)
at net.minecraft.server.v1_6_R2.PlayerList.c(PlayerList.java:206)
at net.minecraft.server.v1_6_R2.PlayerList.a(PlayerList.java:102)
at net.minecraft.server.v1_6_R2.PendingConnection.e(PendingConnection.ja
va:130)
at net.minecraft.server.v1_6_R2.PendingConnection.d(PendingConnection.ja
va:43)
at net.minecraft.server.v1_6_R2.DedicatedServerConnectionThread.a(Dedica
tedServerConnectionThread.java:41)
at net.minecraft.server.v1_6_R2.DedicatedServerConnection.b(SourceFile:2
9)
at net.minecraft.server.v1_6_R2.MinecraftServer.t(MinecraftServer.java:5
90)
at net.minecraft.server.v1_6_R2.DedicatedServer.t(DedicatedServer.java:2
26)
at net.minecraft.server.v1_6_R2.MinecraftServer.s(MinecraftServer.java:4
86)
at net.minecraft.server.v1_6_R2.MinecraftServer.run(MinecraftServer.java
:419)
at net.minecraft.server.v1_6_R2.ThreadServerApplication.run(SourceFile:5
82)
Caused by: java.sql.SQLException: Can not issue SELECT via executeUpdate().
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1621)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)
at com.fruitspvp.Main.onPlayerJoin(Main.java:28)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.ja
va:425)
... 14 more




And The Code:

@EventHandler
public void onPlayerJoin(PlayerJoinEvent e) throws SQLException {
Player p = e.getPlayer();
Statement s = MySQL.open().createStatement();
s.executeUpdate("SELECT players FROM name;");
p.sendMessage("IT DID IT WOOP WOOP");
}

 

Mang0eZPvP

Code:

s.executeUpdate("SELECT players FROM name;");

Should be

Code:

s.executeQuery("SELECT players FROM name;");

As updating is inputting data, where query is retrieving it.

 

what do you thing is it a bad idea to use hibernate?

 

Would this code work, would it add a player with the points and kills value of 0.

Code:java

Connection c = null;
 
 
    public void addPlayer(Player p){
        try {
        MySQL MySql = new MySQL("localhost", "3306", "mch", "mch", "y2T6Si");
        c = MySql.open();
 
        Statement statement = MySql.open().createStatement();
        ResultSet res = statement.executeQuery("SELECT * FROM stats WHERE PlayerName = '" + p + "';");
        res.next();   
 
        if(res.getString("PlayerName") == null){
            statement.executeUpdate("INSERT INTO tokens (`PlayerName`, `points`, `kills`) VALUES ('" + p + "', '" + 0 + "', '" + 0 + "');");
        }
 
        } catch (SQLException e) {
            e.printStackTrace();
        }
 
 
    }

 

hayhaycrusher

That looks fine, try testing it to confirm.

 

And how would i go about creating the table?

 

Hibernate would do this for you
But you could also run a SQL like

""
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
""

Don't know it MySQL supports if not exists

 

-_Husky_- I rewrote it with some changes like Javadocs and SQLite support. I'm just going to test it all out, and if it works then I can upload it if you want.

 

tips48
If you made it into a gist, that'd be great

 

gomeow just finishing testing!

 

If you could PR it to https://github.com/Husky--/MySQL that'd be awesome!

 

Oooh there's a repo
Yeah, I just finished the SQLite implementation and it works like a charm. Next up: Flatfile

https://github.com/Husky--/MySQL/pull/1
Enjoy

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

Thanks for your contribution. Much appreciated.

 

Of course! You should update the front page/README with instructions on how to use it properly. Basically:

Code:

private Database database;
...
public void init(Plugin plugin) {
if (plugin.getDatabaseType() == DatabaseType.MYSQL) {
database = new MySQL(plugin, args, etc);
} else if (plugin.getDatabaseType() == DatabaseType.SQLITE) {
database = new SQLite(plugin, "databaseloc.db");
} else {
plugin.getLogger().log(Level.SEVERE, "Not supported!");
}

And then you go from there using it just how you were. I'll probably be adding FlatFile support soon, so look for another PR when I get around to it

EDIT1: Going to refactor MySQL into code.husky.mysql and same with SQLite. You'll see why
EDIT2: If you want me to write up a tutorial I will

I really just need to get on irc with you lol. Are you in #bukkitdev? btw https://github.com/Husky--/MySQL/pull/2

EDIT by Moderator: merged posts, please use the edit button instead of double posting.