mySQL Database issues

I have made myself a mySQL database with is working fine when i save the players name in when they join the server, but what i'm also wanting to add the players IP in the database as well. When i add sql.setInt(1, event.getPlayer.getAddress().getHostNAme()); it's a string but i need it to be an int so it keeps telling me to change sql.setInt to sql.setSting which wont work. Here is my code i may have some things wrong as this is my first time saving things to databases.

Code:java

    @EventHandler
    public void onSQLConnection(PlayerJoinEvent event) {
        openConnection();
 
        try {
 
            if(playerDataContainsPlayer(event.getPlayer())) {
                PreparedStatement sql = connection.prepareStatement("SELECT Username FROM `Player_Data` WHERE Username=?;");
                sql.setString(1, event.getPlayer().getName());
 
                ResultSet result = sql.executeQuery();
                result.next();
 
                PreparedStatement IP = connection.prepareStatement("SELECT IP FROM `Player_Data` WHERE IP=?;");
                IP.setInt(1, event.getPlayer().getAddress().getHostName());
 
                result.close();
                sql.close();
 
 
 
 
            } else {
 
                PreparedStatement newPlayer = connection.prepareStatement("INSERT INTO `Player_Data` values(?,?);");
                newPlayer.setString(1, event.getPlayer().getName());
                newPlayer.execute();
                newPlayer.close();
 
            }
 
        } catch (Exception e) {
            e.printStackTrace();
 
        } finally {
            closeConnection();
        }
    }

 

MCCoding
First of all, IP is a String.
And your MySQL Query isn't correct.

 

MineDoubleSpace
Yeah i got it to work, whats wrong with the MySQL Query?

 

@MCCodingn "SELECT IP FROM `Player_Data` WHERE IP=?;" because you are updating the ip where ip == to that ip. You should check for the player

 

MineDoubleSpace
How would I do that I'm not to sure with databases, but I have got it working with no errors

MineDoubleSpace

This is my updated code, It's setting the IP fine also i'm making a item menu where i want to set a sting depending on which item they choose i have started the code of that but it does not work at all here is that code as well. If you can help with any of this its much appreciated!

Updated code:

Code:java

    @EventHandler
    public void onSQLConnection(PlayerJoinEvent event) {
        openConnection();
 
        try {
 
            if(playerDataContainsPlayer(event.getPlayer())) {
                PreparedStatement sql = connection.prepareStatement("SELECT Username FROM `Player_Data` WHERE Username=?;");
                sql.setString(1, event.getPlayer().getName());
 
                ResultSet result = sql.executeQuery();
                result.next();
 
                PreparedStatement IP = connection.prepareStatement("UPDATE `Player_Data` SET IP=? WHERE Username=?");
                IP.setString(1, event.getPlayer().getAddress().getHostName());
                IP.setString(2, event.getPlayer().getName());
                IP.executeUpdate();
 
                IP.close();
                result.close();
                sql.close();
 
            } else {
 
                PreparedStatement newPlayer = connection.prepareStatement("INSERT INTO `Player_Data` values(?,?,?);");
                newPlayer.setString(1, event.getPlayer().getName());
                newPlayer.execute();
                newPlayer.close();
            }
 
        } catch (Exception e) {
            e.printStackTrace();
 
        } finally {
            closeConnection();
        }
    }

Item Menu code:

Code:java

    @EventHandler
    public void onInventoryClick(InventoryClickEvent e) {
        Player player = (Player)e.getWhoClicked();
 
        if (!e.getInventory().getName().equalsIgnoreCase(inv.getName())) return;
        if (e.getCurrentItem().getItemMeta() == null) return;
        if (e.getCurrentItem().getItemMeta().getDisplayName().contains(ChatColor.GREEN+ "Villager")) {
            e.setCancelled(true);
 
            SQL.SQLConnection.openConnection();
 
            try {
 
                if(SQL.SQLConnection.playerDataContainsPlayer(player)) {
                    PreparedStatement sql = SQL.SQLConnection.connection.prepareStatement("SELECT Username FROM `Player_Data` WHERE Class=?;");
                    sql.setString(1, "Villager");
                    sql.close();
 
                } else {
 
                    PreparedStatement newPlayer = SQL.SQLConnection.connection.prepareStatement("INSERT INTO `Player_Data` values(?,?,?);");
                    newPlayer.setString(1, player.getName());
                    newPlayer.execute();
                    newPlayer.close();
                }
 
            } catch (Exception ex) {
                ex.printStackTrace();
 
            } finally {
                SQL.SQLConnection.closeConnection();
            }

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

 

MCCoding

Code:java

PreparedStatement sql = SQL.SQLConnection.connection.prepareStatement("UPDATE `Player_Data` SET Class=? WHERE Username=?");
sql.setString(1, "Villager");
sql.setString(2, event.getPlayer().getName());
sql.executeUpdate();
sql.close();

 

1. PreparedStatement newPlayer = connection.prepareStatement("INSERT INTO `Player_Data` values(?,?,?);");
2. newPlayer.setString(1, event.getPlayer().getName());
   
   You only have 1 setstring when there are 3 '?' characters.

 

Neilnet

I have 3 strings one is the players name another is the players IP and another is the players rank.