Tutorial Using MySQL In your Plugins!

This would be great, but shading in a connection pool increases your plugins size by a lot.

 

I am very new to using MySQL inside a plugin and i need it to add something to a plugin i am developing atm. this is my code atm: (i don't know what the java code tags are so sorry about that...)
i'm making a token or coin system.

Code:

package me.frogawesome.DvZPoints;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.bukkit.ChatColor;
import org.bukkit.command.Command;
import org.bukkit.command.CommandSender;
import org.bukkit.entity.Player;
import org.bukkit.plugin.java.JavaPlugin;
 
public class DvZPointsMain extends JavaPlugin {
public static DvZPointsMain plugin;
MySQL sql = new MySQL(this, "localhost", "3306", "mcd_76", "mcd_76", "3d4bb4102f");
int tokens = 0;
 
public void addToken(int Tokens, String name){
  if(sql.checkConnection() == true){
  try {
    Statement s = sql.openConnection().createStatement();
    ResultSet res = s.executeQuery("SELECT * FROM tokens WHERE PlayerName = '" + name + "';");
            res.next();
            if(res.getString("PlayerName") == null) {
              Tokens = 0;
              } else {
              Tokens = res.getInt("coins")+Tokens;
              }
            s.executeUpdate("INSERT INTO tokens (`PlayerName`, `coins`) VALUES ('" + name + "','" + Tokens + "');");
            System.out.println("Inserted info");
 
 
  } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
  }
  }else {
  System.out.println("No Connection Available With Database!");
  }
}
 
public void removeToken(int Tokens, String name){
  if(sql.checkConnection() == true){
  try {
    Statement s = sql.openConnection().createStatement();
    ResultSet res = s.executeQuery("SELECT * FROM tokens WHERE PlayerName = '" + name + "';");
            res.next();
            if(res.getString("PlayerName") == null) {
              tokens = 0;
              } else {
              tokens = res.getInt("coins")-Tokens;
              }
            s.executeUpdate("INSERT INTO tokens (`PlayerName`, `coins`) VALUES ('" + name + "','" + Tokens + "');");
            System.out.println("Inserted info");
 
 
  } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
  }
  }else {
  System.out.println("No Connection Available With Database!");
  }
 
}
 
public void checkTokens(Player player){
  if(sql.checkConnection() == true){
  try {
    Statement s = sql.openConnection().createStatement();
    ResultSet res = s.executeQuery("SELECT * FROM tokens WHERE PlayerName = '" + player.getName() + "';");
            res.next();
            if(res.getString("PlayerName") == null) {
              player.sendMessage(ChatColor.RED + "You Do Not Have A Account Yet! The System Is Creating One For You please Wait....");
              tokens = 0;
                s.executeUpdate("INSERT INTO tokens (`PlayerName`, `coins`) VALUES ('" + player.getName() + "','" + tokens + "');");
                System.out.println("Inserted info");
              } else {
              tokens = res.getInt("coins");
              player.sendMessage(ChatColor.GREEN + "You Have: "+tokens+" Tokens!");
              }
            System.out.println("Inserted info");
 
 
  } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
  }
  }else {
  System.out.println("No Connection Available With Database!");
  }
}
 
 
@Override
public void onEnable() {
  sql.openConnection();
      System.out.println("Enabled");
}
 
@Override
public void onDisable() {
  // TODO Auto-generated method stub
  super.onDisable();
}
 
    public boolean onCommand(CommandSender sender, Command cmd, String commandLabel, String[] args){
        if(cmd.getName().equalsIgnoreCase("frogcoins")){
            Player player = (Player) sender;
            if(sql.checkConnection() == true){
      try {
        Statement s = sql.openConnection().createStatement();
                s.executeUpdate("CREATE TABLE IF NOT EXISTS `tokens` (mcd_76 VARCHAR(20), `coins` TEXT(100), `PlayerName` TEXT(100), tokens TEXT(100))");
                  System.out.println("Database Table Has Been Created");
       --> line 133 ResultSet res = s.executeQuery("SELECT * FROM `tokens` WHERE `PlayerName` = " + player.getName());
                          if(res.getString("PlayerName") == null) {
                          tokens = 0;
                          s.executeUpdate("INSERT INTO tokens (`PlayerName`, `coins`) VALUES (" + player.getName() + "," + tokens + ");");
                          System.out.println("Inserted info");
                          }else {
            player.sendMessage(ChatColor.GREEN +""+ res.getInt("coins"));
                          }
        PreparedStatement stmt = sql.openConnection().prepareStatement("SELECT * FROM `tokens` WHERE `PlayerName` = ? AND `coins` = ?");
        stmt.setString(1, player.getName());
        stmt.executeUpdate();
                          res.next();
                          PreparedStatement thing = sql.openConnection().prepareStatement("INSERT INTO `tokens` (`PlayerName`, `coins`) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS);
                                thing.setString(1, player.getName());
                                thing.setInt(2, res.getInt("SELECT `coins` FROM `tokens` WHERE `PlayerName` = ?"));
                                thing.executeUpdate();
                          System.out.println("Inserted info");
 
 
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      player.sendMessage(ChatColor.GREEN + "IT WORKS!!!!!");
            }else {
            System.out.println("No Connection Available!");
            }
        }else if(commandLabel.equalsIgnoreCase("tokens")){
        if(sender instanceof Player){
          Player player = (Player) sender;
        checkTokens(player);
        }
        }
            return false;
        }
    }

it is mainly about the command "frogcoins" every time i use something it says

Code:

2013-08-30 12:09:05 [SEVERE] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'frogawesome' in 'where clause'[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at java.lang.reflect.Constructor.newInstance(Unknown Source)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.Util.getInstance(Util.java:382)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2570)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1474)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at me.frogawesome.DvZPoints.DvZPointsMain.onCommand(DvZPointsMain.java:133)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:189)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at org.bukkit.craftbukkit.v1_6_R2.CraftServer.dispatchCommand(CraftServer.java:523)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.PlayerConnection.handleCommand(PlayerConnection.java:964)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.PlayerConnection.chat(PlayerConnection.java:882)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.PlayerConnection.a(PlayerConnection.java:839)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.Packet3Chat.handle(SourceFile:49)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.NetworkManager.b(NetworkManager.java:296)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.PlayerConnection.e(PlayerConnection.java:118)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.ServerConnection.b(SourceFile:37)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.DedicatedServerConnection.b(SourceFile:30)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.MinecraftServer.t(MinecraftServer.java:590)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.DedicatedServer.t(DedicatedServer.java:226)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.MinecraftServer.s(MinecraftServer.java:486)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.MinecraftServer.run(MinecraftServer.java:419)[/SIZE][/SIZE][/SIZE]
[SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.ThreadServerApplication.run(SourceFile:582)

[/SIZE][/CODE][/SIZE][/CODE][/SIZE][/CODE][/SIZE]

 

nvm, fixed

 

How can i check if a row exists or not? my code so far

Code:java

try {
    if (mc.getString("permissions", p.getName(), "permissions", "stomper") == "stomper") {
        p.sendMessage("Stomper Kit Availible!");
        PermissionAttachment attachment = p.addAttachment(EnableMain.getInstance());
        attachment.setPermission("stomper", true);
    } else {
        p.sendMessage("Stomper Kit Not Availible!");
    }
} catch (Exception e) {
    e.printStackTrace();
}

Code:java

public String getString(String item, String table, String where, String whereClause) throws Exception {
    Statement s = EnableMain.getInstance().MySQL.open().createStatement();
    ResultSet rs = s.executeQuery("SELECT " + item + " FROM " + table + " WHERE " + where + "=('" + whereClause + "');");
    rs.next();
    return rs.getString(1);
}

Thank You For Your help

EDIT: mc is just so i can access the getString() easier

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

 

Updated tutorial, now using tips48's awesome overhaul. Thanks again tips48!

 

Pls help me???

See it works if there is a row called stomper but if not... Lots of errors basicly saying no row found. I wan away so it would only do it if the row ixisted

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

 

I'll respond when I get on my computer in the morning, Mang0eZPvP

 

Yay. Wit time is it for you???

 

10:35PM (GMT + 10)

Mang0eZPvP here's some code my friend uses..

Code:java

public boolean checkTable(String table) {
        try {
            Statement statement = connection.createStatement();
 
            ResultSet result = statement.executeQuery("SELECT * FROM " + table);
 
            if (result == null)
                return false;
            if (result != null)
                return true;
        } catch (SQLException e) {
            if (e.getMessage().contains("exist")) {
                return false;
            } else {
                this.writeError("Error in SQL query: " + e.getMessage(), false);
            }
        }
    }

 

-_Husky_-

Maybe you could explain to me statements, or maybe even make a tutorial.

 

Currently got assignments to do, here's something that may help.

http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html

Thanks, Husky.

 

Have you got a link for prepared statements?

 

CeramicTitan

http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html

 

Hahah, I'm so sorry for this, but i meant queries the whole time. Queries are the thing, which i probably have the most trouble understanding.

 

IT will always return true for some reason? here is what i have changed.

Code:java

@SuppressWarnings("unused")
public boolean exists(String table, String item, String i) throws SQLException {
    Statement statement = EnableMain.getInstance().MySQL.open().createStatement();
    ResultSet result = statement.executeQuery("SELECT " + item + " FROM " + table + " WHERE " + item + " = '" + i + "';");
    if (result == null)
        return false;
    if (result != null)
        return true;
 
    return false;
}

 

Change this: ResultSet result = statement.executeQuery("SELECT " + item + " FROM " + table + " WHERE " + item + " = '" + i + "';");
To this; ResultSet result = statement.executeQuery("SELECT * FROM " + table + " WHERE " + item + " = '" + i + "';");

 

still no work

 

Any help will be nice. All i need is a way see if a row exist and if not dont spill a missive error in the cobsol

 

Hi there, thank you very much for this guide. I've been encountering a time out error which I've solved by opening a connection every time I query or update (similar to your querySQL and updateSQL functions). Is this the "best practice" for using MySQL with Bukkit?
Originally, I only had the Connection variable as a field which I would initialize once when the plugin was loaded. This is a register plugin, so the period between commands may be very long. After some time, I would receive this error.

Code:

The last packet sent successfully to the server was XXXXX seconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

I've tried using the suggested autoReconnect=true to no avail.
Thanks!

 

setup a auto restart on the server. as that is best method. else u will be leaving ur mysql server vulnable.

 

Open and close connections as you need them, best to not leave them open if you're not using them.

 

Great! I am working on a cool new mini-game, this simplifies things a good bit.

 

Why the MySQL causes lag? i tried to do it on a TaskAsynchronously for stop the lag but i cant return values.. Can anyone help me

 

were is the help?

 

Just wondering could you use this class for SQLite?

 

Code:java

public void onDisable() {
c = closeConnection();
}
 

Doesn't work for me

 

Is there a class called "SQLite"? Yes. So is SQLite available with this? Yes.

 

-_Husky_- said: ↑

10:35PM (GMT + 10)

@Mang0eZPvP here's some code my friend uses..

Code:java

1. public boolean checkTable(String table) {
2. try {
3. Statement statement = connection.createStatement();
5. ResultSet result = statement.executeQuery("SELECT * FROM " + table);
7. if (result == null)
8. return false;
9. if (result != null)
10. return true;
11. } catch (SQLException e) {
12. if (e.getMessage().contains("exist")) {
13. return false;
14. } else {
15. this.writeError("Error in SQL query: " + e.getMessage(), false);
16. }
17. }
18. }

​

IT will always return true for some reason? here is what i have changed.

Code:java

1. @SuppressWarnings("unused")
2. public boolean exists(String table, String item, String i) throws SQLException {
3. Statement statement = EnableMain.getInstance().MySQL.open().createStatement();
4. ResultSet result = statement.executeQuery("SELECT " + item + " FROM " + table + " WHERE " + item + " = '" + i + "';");
5. if (result == null)
6. return false;
7. if (result != null)
8. return true;
10. return false;
11. }

​

 

Mang0eZPvP

If you're using SQLite I can't help, tips48 -may- be able to, though.

 

no it is mysql...