SQL

Discussion in 'Plugin Development' started by Areoace, Jan 22, 2016.

Thread Status:
Not open for further replies.
  1. Offline

    Areoace

    I'm having some trouble kicking my feet off the ground trying to figure out how to use SQL with my plugins. Does anyone have a good place to learn/gather information about how to use SQL and gather data to use in my plugin, and ways to read/write data efficiently? Thanks
     
  2. Offline

    mythbusterma

    @Areoace

    Well what, specifically, is tripping you up? If you need to learn SQL: sqlzoo.org

    If you're having issues connecting to an SQL database: http://dev.mysql.com/doc/connector-...connector-j-examples-connection-drivermanager (this is very similar for SQLite as well)

    What data are you storing the SQL server?

    Make sure that you cache any connections to the server you have, and don't execute SQL queries on the main thread of the server. If you have a lot of information to send, consider deferring it.
     
    TheGamesHawk2001 likes this.
  3. Offline

    Caedus

    Do you mean sqlzoo.net?
     
  4. Offline

    mythbusterma

    Yes, yes I did.
     
  5. Offline

    Areoace

    I know how to use SQL and make queries and what not, I just don't know how to apply it to my code, e.g get a string and use it, or check if the SQL contains something. What do you mean by

    ?
     
  6. Offline

    Caedus

    @mythbusterma
    Ah, figured as much. One of my lecturers owns that website :p
     
  7. Offline

    mythbusterma

    @Caedus

    Well tell him some random guy on the internet thinks it's pretty cool and thanks him.


    @Areoace

    Well you can get a connection to the server using the code in the example I linked in my previous post. Simply run the connection initialization from anywhere in your code (although it would make the most sense to do it on the call stack from onEnable()).

    Then simply run queries by preparing statements (via the PreparedStatement class) and exucting them, then examine the ResultSet that you get from executing the query.

    If you want a more practical example, look through my "Kingdoms" repository. github.com/mythbusterma/kingdoms
     
  8. Offline

    Areoace

    Code:
     
            String sql = "SELECT group_members FROM `groups` WHERE group_name= 'Test'";
    
            try {
                s = conn.createStatement();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                ResultSet members = s.executeQuery(sql);
                plugin.getServer().broadcastMessage(members here <--- + "");
            } catch (SQLException e) {
    
                e.printStackTrace();
            }
    
    @mythbusterma Would you be able to show me how to get a list of UUID strings from a column? I've got this I just don't know how to retrieve it from the resultset
     
  9. Offline

    mythbusterma

    @Areoace

    You would probably want them to be stored in the database as strings, and then parse them as they come out of the database.

    Note: if you're using anything other than a hard coded string like that, please use prepared statements.
     
  10. Offline

    Areoace

    Yeah I've gotta start doing that, just thought I'd mess it up if I tried though. How would I parse the String from the results?

    Code:
                PreparedStatement sql = conn.prepareStatement("SELECT clan_members FROM `clans` WHERE clan_name= 'Test'");
    
                ResultSet members = sql.executeQuery();
    
                    plugin.getServer().broadcastMessage(members here <-- + "");
    
                    sql.close();
    Updated it for you @mythbusterma sorry for the delay. What do I use to get the stringlist?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jan 25, 2016
  11. Offline

    mythbusterma

    @Areoace

    You never, ever store lists as fields in SQL. Multiplicity in fields is handled by creating more records.

    In this case, you would store clans and UUIDs in their own table of only two columns. The UUID would be a primary key and the clan would be a foriegn key referencing some sort of identifying information about the clan in the clan record (usually an ID).

    You iterate over the result set, as it is an iterable object, using the hasNext() and next() methods. These return "rows" of the result, and you can pull fields out by getting basic Java types out of them (e.g. getString(1) to get the first field as a string).
     
  12. Offline

    WolfMage1

    2 things

    1. Close your connections or result sets in a finally block, because if an error occurs before you close them they never get closed.
    2. When you understand how to use SQL properly in java doing queries retrieving data and what not, use something like HikariCP (Connection pooling) Explanation of connection pooling here
     
  13. Offline

    mythbusterma

    @WolfMage1

    Honestly, I wouldn't bother with pooling if you're not accessing often.
     
    Areoace likes this.
  14. Offline

    WolfMage1

    I know but just a suggestion.
     
    mythbusterma likes this.
  15. Offline

    Areoace

    I mean string, it'd be saved as:

    uuuidwajrgjieg, ohwojiofeoigner. qai3q4hf9383qhg3 and I'd split the string to get all the member uuids
     
  16. Offline

    mythbusterma

    @Areoace

    Yea. I understand. And you have to understand that's wrong. Again, never have any sort of list structure in a field in SQL, use row duplicity instead.
     
  17. Offline

    Areoace

    I see, ty, but I'm still stuck on iterating through the strings. I'll post if I figure it out but idk why I get an error when I try members.getString(1)
     
  18. Offline

    mythbusterma

  19. Offline

    Areoace

    @mythbusterma
    Code:
    [15:04:02] [Server thread/WARN]: java.sql.SQLException: Before start of result set
    [15:04:02] [Server thread/WARN]:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
    [15:04:02] [Server thread/WARN]:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    [15:04:02] [Server thread/WARN]:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    [15:04:02] [Server thread/WARN]:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
    [15:04:02] [Server thread/WARN]:     at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841)
    [15:04:02] [Server thread/WARN]:     at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5656)
    [15:04:02] [Server thread/WARN]:     at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5576)
    [15:04:02] [Server thread/WARN]:     at me.areoace.SQL.MySQL.getMembers(MySQL.java:93)
    [15:04:02] [Server thread/WARN]:     at me.areoace.Clans.Listeners.interactListener.playerJoin(interactListener.java:28)
    [15:04:02] [Server thread/WARN]:     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [15:04:02] [Server thread/WARN]:     at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    [15:04:02] [Server thread/WARN]:     at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    [15:04:02] [Server thread/WARN]:     at java.lang.reflect.Method.invoke(Unknown Source)
    [15:04:02] [Server thread/WARN]:     at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:306)
    [15:04:02] [Server thread/WARN]:     at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
    [15:04:02] [Server thread/WARN]:     at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:502)
    [15:04:02] [Server thread/WARN]:     at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:487)
    [15:04:02] [Server thread/WARN]:     at net.minecraft.server.v1_8_R3.PlayerList.onPlayerJoin(PlayerList.java:298)
    [15:04:02] [Server thread/WARN]:     at net.minecraft.server.v1_8_R3.PlayerList.a(PlayerList.java:157)
    [15:04:02] [Server thread/WARN]:     at net.minecraft.server.v1_8_R3.LoginListener.b(LoginListener.java:144)
    [15:04:02] [Server thread/WARN]:     at net.minecraft.server.v1_8_R3.LoginListener.c(LoginListener.java:54)
    [15:04:02] [Server thread/WARN]:     at net.minecraft.server.v1_8_R3.NetworkManager.a(NetworkManager.java:231)
    [15:04:02] [Server thread/WARN]:     at net.minecraft.server.v1_8_R3.ServerConnection.c(ServerConnection.java:148)
    [15:04:02] [Server thread/WARN]:     at net.minecraft.server.v1_8_R3.MinecraftServer.B(MinecraftServer.java:814)
    [15:04:02] [Server thread/WARN]:     at net.minecraft.server.v1_8_R3.DedicatedServer.B(DedicatedServer.java:374)
    [15:04:02] [Server thread/WARN]:     at net.minecraft.server.v1_8_R3.MinecraftServer.A(MinecraftServer.java:654)
    [15:04:02] [Server thread/WARN]:     at net.minecraft.server.v1_8_R3.MinecraftServer.run(MinecraftServer.java:557)
    [15:04:02] [Server thread/WARN]:     at java.lang.Thread.run(Unknown Source)
     
  20. Offline

    Redrield

    I'm just going to give you some basic sql etiquette. If you are handling a string that a user had created, never never never use quotation marks as you did in this example, that leaves you open for SQL Injection attacks (for more info on these, here is a lovely video to explain them right there.) But when you're using sql, and you're working with strings like clan names, always do something like this: SELECT * FROM Clans WHERE ClanName=?. Then before executing the query use the lign preparedStatement.setString(1, "test");. Always do it that way otherwise you leave yourself open to someone wiping the database with one maliciously named clan. Also sorry in advance for not using code tags, tapatalk doesn't give me them, so yeah
     
Thread Status:
Not open for further replies.

Share This Page