Getting Strings from mySQL

I'm wanting to get a sting from my data base that i have set this is as far as i got until i got confused and was not sure on what i was doing, also if you could give a brief explanation on what the playing is doing so i'm not just copying and pasting code i would really appreciate it.

So exactly what i'm wanting to is make a method that will get the playrs rank and check it so for example if the players rank is intermediate send them a message You are in the intermediate rank etc.


Database:

+------------+-----------+---------------------+
| Username | IP | RANK | |
+------------+-----------+---------------------+
| MCCoding | 0.0.0.0 | Noob | |
+------------+-----------+---------------------+

Current code:

Code:java

public static void rankIntermediate(Player player) {
 
try {
 
PreparedStatement sql = SQL.SQLConnection.connection.prepareStatement("SELECT * FROM `Player_Data` WHERE Username = '" + player.getName() + "';");
ResultSet result = sql.executeQuery();
result.next();
 
if(result.getString("Rank") != null) {
 
}
 
 
sql.close();
result.close();
 
} catch(Exception e) {
e.printStackTrace();
} finally {
SQL.SQLConnection.closeConnection();
}
}

 

What's your problem? You almost got it.
Just insert your if() statement to compare if the string form the result set equals your rank to check against.

BTW: If you only need the rank here, better do not use "*". State the wanted column name directly in your SELECT statement, so you only get this column entry and not a bunch of unnecessary data in your result set.

 

CubieX
That's the thing i don't know how to compare it to see if it equals the rank name.

 

I guess you know how to compare two strings.
So what you really mean is, you do not know how to get the current player rank, to be precise?
If so, you could use the API of your permission plugin (PermissionsEx for example) or you can use Vault.
The latter has the advantage that your plugin will work with every common permissions plugin.
Because you cannot know which plugin other server owners use.
(only applies, if you are going to release this plugin later)

PEX API documentation is here.
Vault is here (with example how to hook into it)

 

CubieX
I made my own ranking system which saves the players rank to the database, but what I'm wanting to do is make it so you can't re-rank someone so I had to add a check if they are in the rank which what I'm trying to do. So this method I'm trying to make will check the players rank if it is the intermideate rank, if they are when I set a players rank it won't reset it as they are alreay in a rank.

 

Then I do not understand what your problem is.
You can already read the current player rank from your DB and you can compare this rank (which is a string) to any other rank using .equals().
So what exactly troubles you?

 

CubieX
So the code posted works? I must be getting errors off something else then I will show you u other code to see of you can find something wrong but in not on my computer to post it in will add
It later

 

I think the way you get your rank from DB should work.
If there are errors, post the whole stack trace and the method or class where the error occurs.

 

This is why whole class on where i must be getting the error from here is the code i have,

Class:

Code:java

public class SQLConnection implements Listener {
 
    public static Connection connection;
 
    public synchronized static void openConnection() {
        try {
            connection = DriverManager.getConnection("Dont need to know this");
        } catch(Exception e) {
            e.printStackTrace();
        }
    }
 
    public synchronized static void closeConnection() {
        try {
            connection.close();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }
 
    public synchronized static boolean playerDataContainsPlayer(Player player) {
        try {
            PreparedStatement sql = connection.prepareStatement("SELECT * FROM `Player_Data` WHERE Username=?;");
            sql.setString(1, player.getName());
            ResultSet restultSet = sql.executeQuery();
 
            boolean containsPlayer = restultSet.next();
 
            sql.close();
            restultSet.close();
 
            return containsPlayer;
 
        } catch(Exception e) {
            e.printStackTrace();
            return false;
        }
    }
 
    @EventHandler
    public void onSQLConnection(PlayerJoinEvent event) {
        openConnection();
        try {
 
            if(playerDataContainsPlayer(event.getPlayer())) {
                PreparedStatement sql = connection.prepareStatement("SELECT IP FROM `Player_Data` WHERE Username=?;");
                sql.setString(1, event.getPlayer().getName());
 
                ResultSet result = sql.executeQuery();
                result.next();
 
                result.getString("IP");
 
                PreparedStatement ipUpdate = connection.prepareStatement("UPDATE `Player_Data` SET IP=? WHERE Username=?");
                ipUpdate.setString(1, event.getPlayer().getAddress().getHostName());
                ipUpdate.setString(2, event.getPlayer().getName());
                ipUpdate.executeUpdate();
 
                ipUpdate.close();
                sql.close();
                result.close();
 
            } else {
 
                PreparedStatement newPlayer = connection.prepareStatement("INSERT INTO `Player_Data` VALUES (?,?,?);");
                newPlayer.setString(1, event.getPlayer().getName());
                newPlayer.setString(2, event.getPlayer().getAddress().getHostName());
                newPlayer.setString(3, "Beginner");
                newPlayer.execute();
                newPlayer.close();
            }
 
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Error setting mySQL data.");
 
        } finally {
            closeConnection();
        }
    }
}
 

Error:

Code:

[SEVERE] com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'IP' at row 1 

If you need more of the error let me know its just that in the server.txt its messy and complicated to follow along with.

CubieX
Also when i try and use the rank check method i made it keeps saying to change void to boolean here is how i'm getting the method as well.

Code:

if(SQL.SQLMethods.rankIntermediate(player)) {

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

 

How did you define the column "IP"?
Perharps you used the wrong type or the length is too short for storing an IP adress. A v4 IP can be upt to 15 characters long.

I guess .rankIntermediate() does not return a boolean.
Can you show us this method, please?

 

CubieX

here is the method

Code:java

public static void rankIntermediate(Player player) {
SQL.SQLConnection.openConnection();
 
try {
 
PreparedStatement sql = SQL.SQLConnection.connection.prepareStatement("SELECT RankFROM `Player_Data` WHERE Username = '" + player.getName() + "';");
ResultSet result = sql.executeQuery();
result.next();
 
if(result.getString("Class") != null && result.equals(Intermediate")) {
 
   }
 
 
sql.close();
result.close();
 
} catch(Exception e) {
e.printStackTrace();
} finally {
SQL.SQLConnection.closeConnection();
   }
}

 

.rankIntermediate() does not return a boolean. So your if() statement will not work. (as eclipse says)
Just call .rankIntermediate().

And in this method in line 10 you forgot a " before the word "Intermediate".

 

I've been working on the same problem all weekend and finally got a solution. Try this.
My changes are the added lines: line 6, line 12, and line 14.

Code:java

public static void rankIntermediate(Player player) {
SQL.SQLConnection.openConnection();
 
try {
 
Object rank = 0;
 
PreparedStatement sql = SQL.SQLConnection.connection.prepareStatement("SELECT RankFROM `Player_Data` WHERE Username = '" + player.getName() + "';");
ResultSet result = sql.executeQuery();
result.next();
 
rank = result.getString("Rank");
 
if(rank.equals("Intermediate")) {
 
  }
 
 
sql.close();
result.close();
 
} catch(Exception e) {
e.printStackTrace();
} finally {
SQL.SQLConnection.closeConnection();
  }
}

 

d49s7
Ah okay thanks, just one question what does this do

Code:java

Object rank = 0;

? Also it's still wanting me to change the the return type on rankIntermediate(..) to `Boolean`

 

The Object rank = 0; just has to be there to make lines 12 and 14 work. I couldn't really tell you more as I'm just learning this stuff. It wont work without that though. When I pasted the code it didnt ask me to change the return type to Boolean so I don't know on that. Try changing it and seeing if it works I guess.

 

d49s7
It's sort of working here is when i check if the player is in the rankIntermediate code, In the Database i made it if the player has no rank it will set it to noob in the Rank column but when i do the check it says that yo already have a rank if it does not equal Intermediate for some reason.

Code:java

 
 
    SQL.SQLConnection.openConnection();
 
            try {
 
                if(SQL.SQLMethods.rankIntermediate (player)) {
                    player.sendMessage(ChatColor.RED + "You already have a rank.");
 
                } else {
 
                    if(SQL.SQLConnection.playerDataContainsPlayer(player)) {
                        PreparedStatement sql = SQL.SQLConnection.connection.prepareStatement("UPDATE `Player_Data` SET Rank=? WHERE Username = '" + player.getName() + "';");
                        sql.setString(1, "Intermediate ");
 
                        sql.executeUpdate();
                        sql.close();
 
                    } else {
 
                        PreparedStatement newPlayer = SQL.SQLConnection.connection.prepareStatement("INSERT INTO `Player_Data` VALUES (?,?,?);");
                        newPlayer.setString(1, player.getName());
                        newPlayer.setString(2, player.getAddress().getHostName());
                        newPlayer.setString(3, "noob");
                        newPlayer.execute();
                        newPlayer.close();
                    }
                }
 
            } catch (Exception exc) {
                exc.printStackTrace();
                System.out.println("Error setting mySQL data.");
 
            } finally {
                SQL.SQLConnection.closeConnection();
            }

 

it looks like you insert the players into the DB when they first login, and set their rank to "beginner". So whenever you check to see if a player has any rank it is always going to say the "You already have a rank". This coming from your code on post #9

 

d49s7
So how would i fix? so i have to make another method to check if is beginner then set it?

 

You'll probably need to be able to check which rank anybody is for each rank I think. What are you checking the rank for? and what lets players get a higher rank? I believe you would only need one method to check what the players rank is. then depending on the result you'd have different actions.

 

d49s7
I'm checking the players rank so they can do certain things in Events, i have made multiple methods that check for all of the ranks but how would i make it set over the Beginner? as it keeps saying You already have a rank

 

Whenever you want to change a players rank you just need to make an UPDATE line to change the rank in the DB under whatever conditions call for changing the rank. It looks like you're trying to Update the player rank to intermediate if they don't already have a rank? But since you assign the beginner rank on login, no matter what everytime you do the check for if there is a rank, it will always say You already have a rank.

 

d49s7
So don't set the rank on the login? and then when they select the rank thats when i update

 

Or set the rank as null instead of beginner on login. So whenever you want to change a players rank just check if rank == null. If it is then update to the rank you want. Then if the rank isn't null then the rank wont be changed.

 

d49s7
Thanks for the help, bu how would i check if its null? i have made it so when a player joins it sets the Rank to null, and also i'm getting a error when a players signs in it says Data too long for for column `IP` at row 1

 

Same way you would have checked if the rank was intermediate. In your database, you might need to change the length of the IP column. The column type should to int(x) where x is some number. you can change that number to be larger which means it will be able to fit more data into the column.

Basically, instead of having a bunch of methods to check what a players rank is, you just need 1 method which checks if the rank is null or not. If the rank is null then you can change it to intermediate or any other rank. If the check says the rank is not null, then you wont be able to change the rank. If thats what you want.

If you still need some help tomorrow pm me your skype info and we can chat there. Should be able to fix it quicker that way. I'll check back in tomorrow evening. Or we can just use an IRC if you'd rather not use skype

 

FYI, you should be using SQL like this:

Code:

Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
  connection = ...
  statement = connection.prepareStatement("SELECT 1 as blah FROM otherstuff WHERE 1 ORDER BY blah ASEC LIMIT 1;");
  resultSet = statement.executeQuery();
  if(resultSet.next()) {
    return resultSet.getInt("blah");
  }
} catch(Exception exception) {
  exception.printStackTrace();
} finally {
  if(connection != null) {
    try {
      ...
    } catch(Exception exception) {
      // ignore
    }
  }
  if(statement != null) {
    try {
      statement.close();
    } catch(Exception exception) {
      // ignore
    }
  }
  if(resultSet != null) {
    try {
      resultSet.close();
    } catch(Exception exception) {
      // ignore
    }
  }
}
return -1;

EDIT: Error handling, gotta love it.