Solved MySQL - Player Join Event

Hello All,

I have a plugin so that when a player joins the server it posts there display name to the server. However the first player gets inserted once into the MySQL which is what I want. The second person gets inserted twice. And the third person gets inserted 4 times. Why is that?? Here is my code:

Code:java

package me.thomasjcf21.PEWI;
 
import java.sql.*;
 
import org.bukkit.Bukkit;
import org.bukkit.entity.Player;
import org.bukkit.event.EventHandler;
import org.bukkit.event.Listener;
import org.bukkit.event.player.PlayerJoinEvent;
import org.bukkit.event.player.PlayerQuitEvent;
import org.bukkit.plugin.java.JavaPlugin;
 
public class PEWI extends JavaPlugin implements Listener{
 
public void onEnable(){
System.out.println("PEWI Enabled!");
Bukkit.getPluginManager().registerEvents(this, this);
String url = "jdbc:mysql://localhost:3307/";
String dbName = "PEWI";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "";
try {
Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url+dbName,userName,password);
Statement st = conn.createStatement();
int val = st.executeUpdate("INSERT into online VALUE(online)");
if(val==1)
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
 
public void onDisable(){
System.out.println("PEWI Disabled!");
String url = "jdbc:mysql://localhost:3307/";
String dbName = "PEWI";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "";
try {
Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url+dbName,userName,password);
Statement st = conn.createStatement();
int val = st.executeUpdate("TRUNCATE online");
int val1 = st.executeUpdate("TRUNCATE players");
if(val==1 && val1==1){
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
 
@EventHandler
public void onPlayerJoin(PlayerJoinEvent event){
String player = event.getPlayer().getDisplayName();
Bukkit.getPluginManager().registerEvents(this, this);
String url = "jdbc:mysql://localhost:3307/";
String dbName = "PEWI";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "";
try {
Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url+dbName,userName,password);
Statement st = conn.createStatement();
int val = st.executeUpdate("INSERT into players (player) VALUE('"+player+"')");
if(val==1)
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
 

 

Maybe you should add a line like; if the user is already in the database --> cancel or update

Something like this

Code:

IF EXISTS (SELECT * FROM Table WHERE Column='Value')
    UPDATE Table SET (...) WHERE Column='Value'

 

Thanks

Jordymt

Can you put into the code so I can see it context please

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

 

Since I can't test it I'm not 100% sure but you may want to check another topic, in that way you don't need to check if it exists
https://forums.bukkit.org/threads/tutorial-sqlite-setup.92901/

 

I could be completely wrong, but calling registerEvents everytime the event if fired is not needed, and very well could be causing the event to be called/fired more than once per event. So in this case:

fired on first player: 1 entry (re-registeres event)
fired on second player: 2 entries (re-registeres event)
fired on thrid player: 3 entries (etc, etc...)

Remove the Bukkit.getPluginManager().registerEvents(this, this); from the event code, and test it then.

 

Woops, missed that part, you only need to call it in the onEnable :$

 

What do you mean?? Jordymt

tenowg Ok thanks I will try it now

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

 

You only need to put the register events in de onEnable, the others can be removed!

Code:java

Bukkit.getPluginManager().registerEvents(this, this);

 

Jordymt tenowg

Fixed now thanks

 

just as a side note, you might want to look into using ON DUPLICATE UPDATE clause in mysql

I believe off the top of my head, that it would look like this:

INSERT INTO table VALUE (values) ON DUPLICATE UPDATE value = somevalue;

if you make the playername a primary key, and try to add the same player twice it will instead update the record.

thomasjcf21

You should also look into what columns you make unique indexs, as well as what columns you make primary... in your case (if the error wasn't about running the event more than once) if you had made the playername unique (or primary) it would have refused to insert the record altogether a second time.

 

I'm assuming that you read from the post I made previously about this (about 10 minutes before this: http://forums.bukkit.org/threads/mysql-update-exisiting-row.188441/#post-1947950), so I'll clarify.

ON DUPLICATE KEY is called when you attempt to insert a specified value into a column when there is already a key index in place (whether that be the PRIMARY, UNIQUE, KEY, SPATIAL etc...). It is similar to a "catch" in java where you simply attempt a statement and resolve if something goes wrong.

Also, MySQL uses VALUES not VALUE. Overall, a statement might look like so:

Code:sql

INSERT INTO `table` (`column_key`)
VALUES ('example value')
ON DUPLICATE KEY UPDATE `variable` = /* some value */;

Keep in mind that when you do this method (with only inserting the `key_value` or something similar), you should really keep a notice on what values you allow null, and the defaults used when inserting. If you do not specify defaults / not nulls for the columns you'll simply insert a row of null values.

 

Nope, didn't see any post by you... and I was just giving a point of reference to be researched, not a full example. Thanks for expanding on this alittle more for these guys.