Solved Need help with an sql query

Discussion in 'Spigot Plugin Development' started by finnbon, Aug 24, 2016.

  1. Hello people!

    I have decided to come to, because I have stumped opon a little problem. For a plugin I'm making, I want to retrieve values from a database for a player, and if the player is not in the database, create a row for them and put standard values in it. Now, I know I can this like:
    Code (Java):

    "SELECT * FROM `whatever_table` WHERE uuid = '" + player.getUniqueId(); + '";
    Then check if that ResultSet has nothing in it, if so create a new row and set the default values (in both the database and the class).

    But this means I'll have to retrieve from the database with a query, and then make changes to it with a second one. Is it possible to do this all in one, as that sounds much more efficient.

    Thanks in advance

    - Finn
  2. There's probably a better solution, but you could make one statement like this:

    Code (Java):
    "INSERT IGNORE INTO `Blah` (foo) VALUES ('bar'); SELECT * FROM `Blah` WHERE Foo='bar';"
    INSERT IGNORE will insert if the primary key of the record doesn't already exist (you would set to your uuid field or whatever in your case)
  3. Thanks, sounds great!

    One of them is an 'update' statement and the other one is a request, would I still send it as a request?
  4. @Finn, you save the UUID string on the database, not the UUID object. Use p.getUniqueId().toString(); instead.
  5. I believe so, but I rarely work with multi statements since it's not actually that bad to sent two separate requests because you have an open connection, takes barely any time.
  6. Another thing is you should really set this as the query

    Code (Text):
    "SELECT * FROM `whatever_table` WHERE uuid=?;
    <PreparedStatement variable>.setString(1, player.getUniqueId().toString());
  7. I think I'll be going with 2 seperate statements then. Should I create this using the same connection object? (I assume yes, I just want a confirmation). Also, I'd like to add your tutorial was very helpful! :p

    I doubt it's gonna matter much.
  8. I know about preparedstatements, and I was told they are only required when getting user input. Each UUID from a player will be, well, a valid UUID. Therefor I don't think that's required.
  9. Oh... Sorry :p

    Btw, before going to minecraft, test it on a mysql script or something like that. If you have phpMyAdmin, just run a custom query to test.
  10. Yeah, I figured I should test the SQL stuff before using it in minecraft. I've written a small application to test queries with etc, so that'll be alright!

    Thanks for all the help everyone. Solved! :)
  11. When including an object in some sort of string literal, the toString() is implied. Since overrides the toString method, it will not print jargon such as the hashcode (default object toString implementation)
    Cheers :)
    You should sanitize all your inputs, it will save you later on down the track. Use PreparedStatements and inject your parameters wherever possible.
    • Like Like x 1
  12. Oh well, if you say so too, I shall do so. :)
  13. Why insert, then update? Why not just set the values on insert?

    Also, you should look into connection pooling for performance reasons.
  14. I'm already using a connection pool ;)
    • Like Like x 1