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

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

CodePudding user response：

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.

CodePudding user response：

in the try thing i try sout resultSet and statement before close and it send this to me

resultSet : com.mysql.cj.jdbc.ClientPreparedStatement: SELECT COUNT(name) FROM guild WHERE name = 'a'

statement : com.mysql.cj.jdbc.ClientPreparedStatement: SELECT COUNT(name) FROM guild WHERE name = ** NOT SPECIFIED **

and return false is my test at last it will return true if it have if not it will return false I think like that