[Resource] Really Easy Way to Write SQL

Discussion in 'Resources' started by beastman3226, Dec 31, 2013.

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

    beastman3226

    I was tired of typing the same stuff over and over and over again so I wrote a really quick thing that is very dynamic and unbelievably efficient. Please note that at this point in time it only works with MySQL databases.

    The DataHandler class (open)
    Code:java
    1. import java.sql.ResultSet;
    2. import java.sql.SQLException;
    3. import java.sql.Statement;
    4. import java.util.logging.Level;
    5. import java.util.logging.Logger;
    6.  
    7. import net.ultimateminecraft.ustats.uStats;
    8.  
    9. /**
    10.  * Class for handling data stoof
    11.  * @author beastman3226
    12.  *
    13.  */
    14. public class DataHandler {
    15.  
    16. /**
    17. * Creates new row in the specified table with the specified values
    18. * @param t The table to insert the new data
    19. * @param packet The packet of data to be sent to the database
    20. * @return Returns true if the entire method runs seamlessly
    21. */
    22. public static boolean addData(Table t, DataPacket packet) {
    23. if (uStats.instance.getMySQLDatabase().checkConnection()) {
    24. try {
    25. Statement s = uStats.instance.getMySQLDatabase()
    26. .getConnection().createStatement();
    27. String string = "";
    28. for (Object o : packet.getDataPacket().values()) {
    29. string = string.concat("," + "'" + o + "'");
    30. }
    31. s.execute("INSERT INTO " + t + "\n" + "VALUES(" + string + ");");
    32. } catch (SQLException e) {
    33. uStats.instance.getLogger().severe(e.getLocalizedMessage());
    34. return false;
    35. }
    36. }
    37. return true;
    38. }
    39.  
    40. /**
    41. * Updates a row in the database
    42. * @param t Table to edit
    43. * @param data What to edit
    44. * @param conditionColumn What column must equal
    45. * @param condition What the value in the condition column should be
    46. * @return True of runs seamlessly
    47. */
    48. public static boolean updateRecords(Table t, DataPacket data,
    49. String conditionColumn, Object condition) {
    50. if (uStats.instance.getMySQLDatabase().checkConnection()) {
    51. try {
    52. Statement s = uStats.instance.getMySQLDatabase()
    53. .getConnection().createStatement();
    54. String set = "";
    55. int i = 0;
    56. for (String c : data.getDataPacket().keySet()) {
    57. if (i == 0) {
    58. set = set.concat(c + "='" + data.getDataPacket().get(c)
    59. + "'");
    60. } else {
    61. set = set.concat(", " + c + "='"
    62. + data.getDataPacket().get(c) + "'");
    63. }
    64. i++;
    65. }
    66. s.execute("UPDATE " + t + "\n" + "SET " + set + "\n" + "WHERE "
    67. + conditionColumn + "='" + condition + "';");
    68. } catch (SQLException e) {
    69. uStats.instance.getLogger().severe(e.getLocalizedMessage());
    70. return false;
    71. }
    72. }
    73. return true;
    74. }
    75.  
    76. /**
    77. * Takes the name given and tries to search the ID table for the id
    78. * @param name The name of the player you are looking for
    79. * @return -1 if there is no such index or the id of the player
    80. */
    81. public static int getIDOf(String name) {
    82. ResultSet rs = null;
    83. try {
    84. Statement s = uStats.instance.getMySQLDatabase()
    85. .getConnection().createStatement();
    86. rs = s.executeQuery("SELECT PlayerID FROM " + Table.IDS + "WHERE PlayerName='" + name + "';");
    87. } catch (SQLException e) {
    88. // TODO Auto-generated catch block
    89. e.printStackTrace();
    90. }
    91. try {
    92. return rs.getInt("PlayerID");
    93. } catch (SQLException e) {
    94. e.printStackTrace();
    95. }
    96. return -1;
    97. }
    98.  
    99. /**
    100. * Clears the table of all records
    101. * @param t The table
    102. */
    103. public static void clearRecords(Table t) {
    104. if(uStats.instance.getMySQLDatabase().checkConnection()) {
    105. try {
    106. uStats.instance.getMySQLDatabase().getConnection().createStatement().executeQuery("TRUNCATE TABLE " + t);
    107. } catch (SQLException e) {
    108. e.printStackTrace();
    109. }
    110. }
    111. }
    112.  
    113. /**
    114. * Gets the data at in the specified table at in spcific row and column
    115. * @param t Table to search
    116. * @param column Column you want the info for
    117. * @param condition The column that you are checking to make sure it is the right information
    118. * @param conditionData The condition for the data
    119. * @return An object representation of the data
    120. */
    121. public static Object getData(Table t, String column, String condition, Object conditionData) {
    122. Object toReturn = null;
    123. try {
    124. Statement s = uStats.instance.getMySQLDatabase()
    125. .getConnection().createStatement();
    126. ResultSet rs = s.executeQuery("SELECT " + column + " FROM " + t + " WHERE " + condition + "='" + conditionData + "';");
    127. toReturn = rs.getObject(column);
    128. } catch (SQLException e) {
    129. uStats.instance.getLogger().severe(e.getLocalizedMessage());
    130. return false;
    131. }
    132. return toReturn;
    133. }
    134.  
    135.  
    136.  
    137. public enum Table {
    138.  
    139. YOUR_TABLE_HERE("table_name_here", new String[]{"Field VARCHAR(255)", "SomeOtherColumn INTEGER"});
    140.  
    141. String toString;
    142. String[] columns;
    143.  
    144. Table(String string, String[] columns) {
    145. this.toString = string;
    146. if(!uStats.instance.getMySQLDatabase().checkConnection()) {
    147. uStats.instance.getMySQLDatabase().openConnection();
    148. }
    149. Statement s = null;
    150. try {
    151. s = uStats.instance.getMySQLDatabase().getConnection().createStatement();
    152. } catch (SQLException ex) {
    153. Logger.getLogger(Table.class.getName()).log(Level.SEVERE, null, ex);
    154. }
    155. try {
    156. s.execute("IF OBJECT_ID('" + uStats.instance.getMySQLDatabase().getName() +"." + string + "') IS NULL" + "\n" + "CREATE TABLE " + string + "(" + "\n" + concat(columns) + "\n" + ");");
    157. } catch (SQLException ex) {
    158. Logger.getLogger(Table.class.getName()).log(Level.SEVERE, null, ex);
    159. }
    160. }
    161.  
    162. /**
    163. * Turns the string[] into a string for the ability to create the table
    164. * @param strings The strings to parse
    165. * @return String representation with a new line to split
    166. */
    167. private String concat(String[] strings) {
    168. StringBuilder sb = new StringBuilder();
    169. for(String string : strings) {
    170. sb.append(string).append("\n");
    171. }
    172. return sb.toString();
    173. }
    174.  
    175. @Override
    176. public String toString() {
    177. return this.toString;
    178. }
    179. }
    180.  
    181. }

    The DataHandler class is essential. It is the sole reason everything works the way it does. It is a beautifully oiled machine and is really easy to use. I will give an example after I show you the other essential class.

    DataPacket (open)
    Code:java
    1. import java.util.HashMap;
    2.  
    3. public class DataPacket {
    4.  
    5. private HashMap<String, Object> data = new HashMap<String, Object>();
    6.  
    7. /**
    8. * Adds data to the hashmap
    9. * @param column Name of column to add it to
    10. * @param dataObject Object to associate with the column
    11. * @return The current instance of this object
    12. */
    13. public DataPacket addData(String column, Object dataObject) {
    14. data.put(column, dataObject);
    15. return this;
    16. }
    17.  
    18. /**
    19. * Clears the data in the packet
    20. * @return Current instance of the data
    21. */
    22. public DataPacket clearData() {
    23. data.clear();
    24. return this;
    25. }
    26.  
    27. public HashMap<String, Object> getDataPacket() {
    28. return this.data;
    29. }
    30.  
    31. }

    This object holds the information you are sending to the database. It allows the DataHandler to take the information and set it equal to everything else.

    Now that we have that all done let's do and example. NOTE: I used a make believe table with make believe columns
    Code:java
    1. //add Data must be in order (ill change that soon)
    2. DataHandler.addData(Table.AWESOME, new DataPacket().addData("Player", "beastman3226")
    3. .addData("AwesomeLevel", 9001));

    That example added Player beastman3226 with awesome level 9001 to the table. But, wait! Player beastman3226 already has an index, well let's do this:
    Code:java
    1. DataHandler.updateRecords(Table.AWESOME, new DataPacket().addData("AwesomeLevel", 9002), "Player", "beastman3226");

    Woah?!! What just happened there? Well, I changed the AwesomeLevel of beastman3226 to 9002 because the first of the extra parameters is the column we are checking against and the second value is the value we expect to find in that column. Therefore the method creates a SQL statement that changes the data with said condition and data.

    Give me some suggestions! Give me feedback! Ciao!
     
    ArthurMaker likes this.
  2. Offline

    marshmallowz

    What is your uStats.instance.getMSQLDatabase() method look like?
     
  3. Offline

    beastman3226

    marshmallowz
    Oh sorry, it gets the instance of the MySQL database. Checkout the thread "How to use SQL in your plugins".
     
  4. Offline

    marshmallowz

    beastman3226
    I did that but I'm getting an error on this .getMySQLDatabase().getName(), is that some code that you have added to the MySQL file?
     
  5. Offline

    beastman3226

    Yes. I use it to return the instance of the database object. I'll put that up here in a jiffy.
     
  6. Offline

    xTrollxDudex

    beastman3226
    Add some whitespace ti make the code cleaner and use spaces, not tabs to indent your code. Otherwise, looks fine.
     
  7. Offline

    DrJava

    To be honest, I would prefer to write the SQL and methods out rather than this.
     
  8. Offline

    beastman3226

  9. Offline

    DrJava

    Simpler.
     
    glen3b likes this.
  10. Offline

    beastman3226

    Huh?? Writing SQL is easier?? You sir make no sense :p
     
  11. Offline

    DrJava

    Last edited by a moderator: Jun 6, 2016
  12. Offline

    Bart

    Actually it is incredibly bad practise to hardcode all of your SQL statements, what if you wanted to switch to another system later on? I would say the code you linked is much more confusing than if you were to use this resource even though I have come from a background of 4 years of MySQL/PHP work

    EDIT: The whole point of a PreparedStatement is you prepare it before usage, so you would have a variable holding the PS for this https://github.com/MCMedia/gBan/blob/master/src/me/MCMedia/gBan/Default.java#L150 to be as a separate variable. Then you would just inject the values in when the event is fired.
     
  13. Offline

    DrJava

    I know, but I find it incredibly easy.
     
  14. Offline

    glen3b

    beastman3226 When I see people using string concatenation to build SQL queries the first thing that comes to mind is SQL injection. Please use a PreparedStatement.
     
Thread Status:
Not open for further replies.

Share This Page