MySQL finding if a value exists.

Discussion in 'Spigot Plugin Development' started by avighnash, Jun 17, 2016.

  1. Hello! This may seem like a simple question, but I just learned the basics of SQL recently. For my credits plugin, I want to check if a player is even in the a table. What would be the query for that? I am using HikariCP as my connecting pool.
    Thanks!
     
  2. Well, I think I asked a little too vaguely. I know how to check if a value is a column:
    Code (Text):
    IF EXISTS (SELECT * FROM TABLE WHERE Player=?
    but in java, how would I check if the result equals null?
     
  3. Select the data from the table. Just check if the ResultSet is null.
     
  4. Like this? B is the prepared statement of the query I showed above:

    Code (Text):
                if (b.getResultSet() == null) {
     
     
  5. The resultset shouldn't ever be null (hopefully lol). https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#next()

    Basically, a select statement will return a ResultSet after the query is executed. This result set will contain the results from the SELECT statement. So if the ResultSet is empty( if (!next()) ), then you don't have any results!
     
  6. So if b doesn't have any results, I don't want to execute "a".
    So this would be the snippet if b has results?

    Code (Text):
    if (b.getResultSet().next()) {
                    a.execute();
                }
     
  7. This is my method for getting coins:

    Code (Text):
    public void getCoins(Player p) {
            Connection connection = null;

            String update = "SELECT Amount FROM credits WHERE Player=?;";
            String check = "IF EXISTS (SELECT * FROM TABLE WHERE Player=?)";

            PreparedStatement a = null;
            PreparedStatement b = null;
            try {
                connection = hikari.getConnection();
                a = connection.prepareStatement(update);
                b = connection.prepareStatement(check);

                a.setString(1, p.getUniqueId().toString());
                b.setString(1, p.getUniqueId().toString());

                b.execute();
                if (b.getResultSet().next()) {
                    a.execute();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                if (a != null) {
                    try {
                        a.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                if (b != null) {
                    try {
                        b.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    and that's where the snippet is located.
     
  8. @avighnash Why do you even need to know why the player exists?
     
  9. Just attempt to SELECT the value via the query. Use ResultSet's .next() method and if it returns false then that means the value did not exist.
     
  10. So when I display the count on the scoreboard, and if the table doesn't contain the player, it wouldn't display random chars.
     
  11. Ill try that.
     
  12. Rather than wasting a SQL query on a simple check, whenever you want to load/update, do so. If the ResultSet doesn't have any rows, assume the player has no credits (so 0).
     
  13. So basically this would work

    Code (Text):
    if (statement.getResultSet().next() == true) {
                    statement.execute();
                }
    ?
     
    • Optimistic Optimistic x 1
  14. I have no idea what you're doing. You cannot execute/update the table if you've never even queried. That's what your code is doing, it will always be false.
     
  15. Wait wtf, oops. Let me think this through...
     
  16. You don't need to execute a statement just to check whether or not they have coins. Just execute a statement to get the value and then if (!resultSet.next()) return 0, else return the amount it gave you.
     
  17. Take advantage of Java 7's awesome features. The try-with feature will reduce the lines of code by a lot.

    Also, don't do "execute()" for SELECT statements. Use ResultSet set = statement.executeQuery()
     
    • Agree Agree x 2
  18. Okay, ill fix with the try with feature later. Firstly, I get a class no defound error on this line, in my hikari class:

    Code (Text):
            hikari = new HikariDataSource();
     
    do I have to do something with hikari? And also, I have a private field HikariDataSource too.
     
  19. You don't have to use Hikari unless you plan on doing a lot of Async SQL stuff. JBDC works perfect alone.

    And if you're getting a no class found, i'm sure you didn't compile HikariCP INTO your jar. You can do this using Intellij's features, or Maven shade plugin.

    Edit: Here is a pretty good example if you need any help with setting up HikariCP: https://github.com/bob7l/HawkReload...iwali/HawkEye/database/ConnectionManager.java
     
  20. I have setup hikari already, its just, how do I compile hikari cp into my jar without using maven?