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
@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.
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 ?
@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
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
@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.
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.
@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).
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
I mean string, it'd be saved as: uuuidwajrgjieg, ohwojiofeoigner. qai3q4hf9383qhg3 and I'd split the string to get all the member uuids
@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.
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)
@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)
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