check row if not exist mysql and got output to boolean bukkit

Issue

I have tried this so many times but I never did it, this is my code

public static Boolean checkhaveguild(String name) {
    try {
        Statement statement = connection.createStatement();
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM guild");
        System.out.println(statement.execute("SELECT * FROM guild WHERE name = "+name+""));
        System.out.println("----------");
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    return false;
}

I am doing a guild plugin on BungeeCord and getting data from MySQL
The code is about checking if the row does not exist and output to boolean

Solution

I’d suggest you to learn more about the basics of programming in Java! Minecraft is a great way to start into programming, but you should be interested in doing things properly.

public static boolean hasGuild(String name) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement("SELECT COUNT(name) FROM guild WHERE name = ?");
            statement.setString(1, name);

            resultSet = statement.executeQuery();
            if (resultSet.next()) return resultSet.getInt(1) > 0;
        } catch (SQLException e) {
            // TODO properly handle exception
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    // TODO properly handle exception
                }
            }

            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    // TODO properly handle exception
                }
            }
        }
        return false;
    }

Some thoughts on what this code is doing:

  • Asking the database for the number of rows whose name column matches the given string. Always make sure that you only request the data that’s necessary for your purpose. Requesting all columns with their data is overkill if you only want to answer if there are any rows or not.

  • If the number of rows is greater than zero, it’ll return true, because there are rows with a matching name column.

Some thoughts you should make yourself:

  • What is contained in the name column? If it’s the guild’s name, then that’s fine, but if that’s the player’s name you should consider re-thinking your code. Player’s in Minecraft can change their name and hence would lose their guild on your server. Players in Minecraft are uniquely identified by their UUID, which will never change. Maybe consider using the UUID then!

  • In order for the query to be as fast a possible you should set an INDEX on the name column. That will speed up the lookup proccess even if there are plenty of rows!

Nevertheless: Welcome to StackOverflow! I hope that I could help you and I wish lot’s of fun with programming.

Answered By – Linus Groschke

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published