[MySQL/Lib] FruitSQL - Super simple MySQL database library!

Discussion in 'Resources' started by TheTrixsta, Jul 6, 2013.

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

    TheTrixsta

    Lets revive FruitSQL

    Ive always had some trouble with MySQL, I hate having to clutter my projects with the code to initialize the database, ect. So ive begun writing a small dumb downed MySQL library called FruitSQL, feel free to complete the unfinished methods, add your own code, or do whatever with!

    Download: https://dl.dropboxusercontent.com/u/28230696/Java/FruitSQL.java

    Once you have initialized a FruitSQL variable, if the database doesn't exist, it will create the database and connect to it, from there ive been writing easy to use methods to manage the MySQL database!

    Edit: Updated the code with complete methods, thanks to amitlin14, If you would like to help, support your own code as a reply, Ill look over it and add it the the FruitSQL library! Again big thanks to amitlin14!

    March 16, 2014: Ive updated the project with the latest code, thanks amitlin14. Ive also updated the dropbox link so you can directly download the FruitSQL.java file.

    Dependencies: JDBC

    Code:java
    1.  
    2. package com.jordan.fruitsql;
    3.  
    4. import java.sql.Connection;
    5. import java.sql.DriverManager;
    6. import java.sql.PreparedStatement;
    7. import java.sql.ResultSet;
    8. import java.sql.SQLException;
    9. import java.sql.Statement;
    10.  
    11. /**
    12. *
    13. * @author Jordan Wiggins
    14. * @author amitlin14
    15. *
    16. */
    17.  
    18. /*
    19. * Example use:
    20. *
    21. * FruitSQL sql = new FruitSQL("host", "port", "database", "username",
    22. * "password"); //Connect to database
    23. * sql.createTable("users"); //Create the table 'users'
    24. */
    25.  
    26. public class FruitSQL {
    27.  
    28. private Connection connection = null;
    29. private String host, port, database, username, password;
    30.  
    31. /**
    32. *
    33. * @param host
    34. * @param port
    35. * @param database
    36. * @param username
    37. * @param password
    38. */
    39. public FruitSQL(String host, String port, String database, String username, String password) {
    40. long start = 0;
    41. long end = 0;
    42.  
    43. try
    44. {
    45. start = System.currentTimeMillis();
    46. System.out.println("Attempting to establish a connection the MySQL server!");
    47. Class.forName("com.mysql.jdbc.Driver");
    48. connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
    49. end = System.currentTimeMillis();
    50. System.out.println("Connection to MySQL server established! (" + host + ":" + port + ")");
    51. System.out.println("Connection took " + ((end - start)) + "ms!");
    52. } catch (SQLException e)
    53. {
    54. System.out.println("Could not connect to MySQL server! because: " + e.getMessage());
    55. {
    56. System.out.println("JDBC Driver not found!");
    57. }
    58.  
    59. this.host = host;
    60. this.port = port;
    61. this.database = database;
    62. this.username = username;
    63. this.password = password;
    64. }
    65.  
    66. /**
    67. *
    68. * @return
    69. */
    70. public boolean isConnected() {
    71. if (connection != null) { return true; }
    72. return false;
    73. }
    74.  
    75. /**
    76. *
    77. */
    78. public void closeConnection() {
    79. if (connection == null)
    80. try
    81. {
    82. connection.close();
    83. System.out.println("MySQL Connection closed");
    84. } catch (SQLException e)
    85. {
    86. System.out.println("Couldn't close connection");
    87. }
    88. }
    89.  
    90. /**
    91. *
    92. */
    93. public void refreshConnection() {
    94. ResultSet valid = null;
    95. try
    96. {
    97. st = connection.prepareStatement("SELECT 1 FROM Dual");
    98. valid = st.executeQuery();
    99. if (valid.next())
    100. return;
    101. } catch (SQLException e2)
    102. {
    103. System.out.println("Connection is idle or terminated. Reconnecting...");
    104. } finally
    105. {
    106. this.closeQuietly(valid);
    107. }
    108.  
    109. long start = 0;
    110. long end = 0;
    111.  
    112. try
    113. {
    114. start = System.currentTimeMillis();
    115. System.out.println("Attempting to establish a connection the MySQL server!");
    116. Class.forName("com.mysql.jdbc.Driver");
    117. connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
    118. end = System.currentTimeMillis();
    119. System.out.println("Connection to MySQL server established! (" + host + ":" + port + ")");
    120. System.out.println("Connection took " + ((end - start)) + "ms!");
    121. } catch (SQLException e)
    122. {
    123. System.out.println("Could not connect to MySQL server! because: " + e.getMessage());
    124. {
    125. System.out.println("JDBC Driver not found!");
    126. }
    127. }
    128.  
    129. /**
    130. *
    131. * @param sql
    132. * @return
    133. */
    134. public boolean execute(String sql) {
    135. this.refreshConnection();
    136.  
    137. boolean st = false;
    138. try
    139. {
    140. Statement statement = connection.createStatement();
    141. st = statement.execute(sql);
    142. statement.close();
    143. } catch (SQLException e)
    144. {
    145. e.printStackTrace();
    146. }
    147. return st;
    148. }
    149.  
    150. /**
    151. *
    152. * @param sql
    153. * @return
    154. */
    155. public ResultSet executeQuery(String sql) {
    156. this.refreshConnection();
    157.  
    158. PreparedStatement statement = null;
    159. ResultSet rs = null;
    160.  
    161. try
    162. {
    163. statement = connection.prepareStatement(sql);
    164. rs = statement.executeQuery();
    165. } catch (SQLException e)
    166. {
    167. e.printStackTrace();
    168. }
    169.  
    170. return rs;
    171. }
    172.  
    173. public void closeQuietly(ResultSet rs) {
    174. try
    175. {
    176. ps = (PreparedStatement) rs.getStatement();
    177. rs.close();
    178. rs = null;
    179. ps.close();
    180. ps = null;
    181. } catch (SQLException e)
    182. {
    183. e.printStackTrace();
    184. } finally
    185. {
    186. if (rs != null)
    187. try
    188. {
    189. rs.close();
    190. } catch (SQLException e)
    191. {
    192. // Ignore... nothing we can do about this here
    193. }
    194.  
    195. if (ps != null)
    196. try
    197. {
    198. rs.close();
    199. } catch (SQLException e)
    200. {
    201. // Ignore... nothing we can do about this here
    202. }
    203. }
    204. }
    205.  
    206. /**
    207. *
    208. * @param sql
    209. * @return
    210. */
    211. public int executeUpdate(String sql) {
    212. this.refreshConnection();
    213.  
    214. int st = 0;
    215. try
    216. {
    217. Statement statement = connection.createStatement();
    218. st = statement.executeUpdate(sql);
    219. statement.close();
    220. } catch (SQLException e)
    221. {
    222. e.printStackTrace();
    223. }
    224. return st;
    225. }
    226.  
    227. /**
    228. * @param statements
    229. * - the list of statements
    230. * @param batchSize
    231. * - the size of each batch to avoid an outOfMemory exception,
    232. * set to 0 if you want to ignore
    233. */
    234. public void executeBatch(String[] statements, int batchSize) {
    235.  
    236. int count = 0;
    237.  
    238. Statement statement = null;
    239. try
    240. {
    241. statement = connection.createStatement();
    242. for (String query : statements)
    243. {
    244. statement.addBatch(query);
    245.  
    246. if (batchSize != 0)
    247. if (count % batchSize == 0)
    248. statement.executeBatch(); // execute batch.
    249. // batch size limit has been
    250. // reached
    251. count++;
    252. }
    253.  
    254. statement.executeBatch(); // execute the remaining batches
    255. statement.close();
    256. statement = null;
    257. } catch (SQLException e)
    258. {
    259. e.printStackTrace();
    260. } finally
    261. {
    262. if (statement != null)
    263. try
    264. {
    265. statement.close();
    266. } catch (SQLException e)
    267. {
    268. // Ignore this, nothing we can do about this now
    269. }
    270. }
    271.  
    272. }
    273.  
    274. /**
    275. *
    276. * @param tablename
    277. * @param values
    278. */
    279. public void createTable(String tablename, String[] values) {
    280. this.refreshConnection();
    281.  
    282. String stmt = "CREATE TABLE IF NOT EXISTS " + tablename + "(";
    283.  
    284. for (int i = 0; i < values.length; i++)
    285. if (i == values.length - 1)
    286. stmt = stmt.concat(values[I]);[/I]
    287. [I]else[/I]
    288. [I]stmt = stmt.concat(values + ", ");[/I]
    289.  
    290. [I]stmt = stmt.concat(");");[/I]
    291.  
    292. [I]try[/I]
    293. [I]{[/I]
    294. [I]Statement statement = connection.createStatement();[/I]
    295. [I]statement.executeUpdate(stmt);[/I]
    296. [I]} catch (SQLException e)[/I]
    297. [I]{[/I]
    298. [I]e.printStackTrace();[/I]
    299. [I]}[/I]
    300.  
    301. [I]}[/I]
    302.  
    303. [I]/**[/I]
    304. [I]*[/I]
    305. [I]* @param tablename[/I]
    306. [I]*/[/I]
    307. [I]public void deleteTable(String tablename) {[/I]
    308. [I]this.refreshConnection();[/I]
    309.  
    310. [I]String sql = "DROP TABLE IF EXISTS " + tablename;[/I]
    311.  
    312. [I]try[/I]
    313. [I]{[/I]
    314. [I]Statement statement = connection.createStatement();[/I]
    315. [I]statement.executeUpdate(sql);[/I]
    316. [I]} catch (SQLException e)[/I]
    317. [I]{[/I]
    318. [I]e.printStackTrace();[/I]
    319. [I]}[/I]
    320. [I]}[/I]
    321.  
    322. [I]/**[/I]
    323. [I]*[/I]
    324. [I]* @param tablename[/I]
    325. [I]* @param values[/I]
    326. [I]*/[/I]
    327. [I]public void resetTable(String tablename, String[] values) {[/I]
    328. [I]this.refreshConnection();[/I]
    329.  
    330. [I]deleteTable(tablename);[/I]
    331. [I]createTable(tablename, values);[/I]
    332. [I]}[/I]
    333.  
    334. [I]/**[/I]
    335. [I]*[/I]
    336. [I]* @param column[/I]
    337. [I]* @param value[/I]
    338. [I]*/[/I]
    339. [I]public void insertInto(String table, String[] columns, Object[] values) {[/I]
    340. [I]this.refreshConnection();[/I]
    341.  
    342. [I]String statement = "INSERT INTO " + table;[/I]
    343.  
    344. [I]String c = "(";[/I]
    345. [I]for (int i = 0; i < columns.length; i++)[/I]
    346. [I]{[/I]
    347. [I]if (i == columns.length - 1)[/I]
    348. [I]c = c + columns;[/I]
    349. [I]else[/I]
    350. [I]c = c + columns + ",";[/I]
    351. [I]}[/I]
    352. [I]c = c + ")";[/I]
    353.  
    354. [I]String v = "(";[/I]
    355. [I]for (int i = 0; i < values.length; i++)[/I]
    356. [I]{[/I]
    357.  
    358. [I]if (i == columns.length - 1)[/I]
    359. [I]{[/I]
    360. [I]if (values[I] instanceof String)[/I][/I]
    361. [I]v = v + "'" + values + "'";[/I]
    362. [I]else[/I]
    363. [I]v = v + values;[/I]
    364. [I]} else[/I]
    365. [I]{[/I]
    366. [I]if (values[I] instanceof String)[/I][/I]
    367. [I]v = v + "'" + values + "', ";[/I]
    368. [I]else[/I]
    369. [I]v = v + values + ", ";[/I]
    370. [I]}[/I]
    371. [I]}[/I]
    372. [I]v = v + ")";[/I]
    373.  
    374. [I]statement = statement + c + " VALUES" + v + " ON DUPLICATE KEY UPDATE ";[/I]
    375.  
    376. [I]for (int i = 0; i < columns.length; i++)[/I]
    377. [I]{[/I]
    378. [I]statement = statement + columns + "=";[/I]
    379.  
    380. [I]if (i == columns.length - 1)[/I]
    381. [I]{[/I]
    382. [I]if (values[I] instanceof String)[/I][/I]
    383. [I]statement = statement + "'" + values + "'";[/I]
    384. [I]else[/I]
    385. [I]statement = statement + values;[/I]
    386. [I]} else[/I]
    387. [I]{[/I]
    388. [I]if (values[I] instanceof String)[/I][/I]
    389. [I]statement = statement + "'" + values + "', ";[/I]
    390. [I]else[/I]
    391. [I]statement = statement + values + ", ";[/I]
    392. [I]}[/I]
    393.  
    394. [I]}[/I]
    395. [I]statement = statement + ";";[/I]
    396.  
    397. [I]this.executeUpdate(statement);[/I]
    398. [I]}[/I]
    399.  
    400. [I]public void insertIntoWithoutPrimaryKey(String table, String[] columns, Object[] values) {[/I]
    401. [I]this.refreshConnection();[/I]
    402.  
    403. [I]String statement = "INSERT INTO " + table;[/I]
    404.  
    405. [I]String c = "(";[/I]
    406. [I]for (int i = 0; i < columns.length; i++)[/I]
    407. [I]{[/I]
    408. [I]if (i == columns.length - 1)[/I]
    409. [I]c = c + columns;[/I]
    410. [I]else[/I]
    411. [I]c = c + columns + ",";[/I]
    412. [I]}[/I]
    413. [I]c = c + ")";[/I]
    414.  
    415. [I]String v = "(";[/I]
    416. [I]for (int i = 0; i < values.length; i++)[/I]
    417. [I]{[/I]
    418.  
    419. [I]if (i == columns.length - 1)[/I]
    420. [I]{[/I]
    421. [I]if (values[I] instanceof String)[/I][/I]
    422. [I]v = v + "'" + values + "'";[/I]
    423. [I]else[/I]
    424. [I]v = v + values;[/I]
    425. [I]} else[/I]
    426. [I]{[/I]
    427. [I]if (values[I] instanceof String)[/I][/I]
    428. [I]v = v + "'" + values + "', ";[/I]
    429. [I]else[/I]
    430. [I]v = v + values + ", ";[/I]
    431. [I]}[/I]
    432. [I]}[/I]
    433. [I]v = v + ")";[/I]
    434.  
    435. [I]statement = statement + c + " VALUES" + v + " ON DUPLICATE KEY UPDATE ";[/I]
    436.  
    437. [I]for (int i = 1; i < columns.length; i++)[/I]
    438. [I]{[/I]
    439. [I]statement = statement + columns + "=";[/I]
    440.  
    441. [I]if (i == columns.length - 1)[/I]
    442. [I]{[/I]
    443. [I]if (values[I] instanceof String)[/I][/I]
    444. [I]statement = statement + "'" + values + "'";[/I]
    445. [I]else[/I]
    446. [I]statement = statement + values;[/I]
    447. [I]} else[/I]
    448. [I]{[/I]
    449. [I]if (values[I] instanceof String)[/I][/I]
    450. [I]statement = statement + "'" + values + "', ";[/I]
    451. [I]else[/I]
    452. [I]statement = statement + values + ", ";[/I]
    453. [I]}[/I]
    454.  
    455. [I]}[/I]
    456. [I]statement = statement + ";";[/I]
    457.  
    458. [I]this.executeUpdate(statement);[/I]
    459. [I]}[/I]
    460.  
    461. [I]/**[/I]
    462. [I]*[/I]
    463. [I]* @param column[/I]
    464. [I]* @param value[/I]
    465. [I]* @return[/I]
    466. [I]*/[/I]
    467. [I]public ResultSet getRowByColumn(String table, String column, String value) {[/I]
    468. [I]this.refreshConnection();[/I]
    469.  
    470. [I]ResultSet rs = this.executeQuery("SELECT * FROM " + table + " WHERE " + column + " = " + value);[/I]
    471.  
    472. [I]return rs;[/I]
    473. [I]}[/I]
    474.  
    475. [I]/**[/I]
    476. [I]*[/I]
    477. [I]* @param table[/I]
    478. [I]* @param set[/I]
    479. [I]* @param column[/I]
    480. [I]* @return[/I]
    481. [I]*/[/I]
    482. [I]public String getValueFromRow(String table, ResultSet set, String column) {[/I]
    483. [I]this.refreshConnection();[/I]
    484.  
    485. [I]try[/I]
    486. [I]{[/I]
    487. [I]return set.getString(column);[/I]
    488. [I]} catch (SQLException e)[/I]
    489. [I]{[/I]
    490. [I]e.printStackTrace();[/I]
    491. [I]}[/I]
    492. [I]return null;[/I]
    493. [I]}[/I]
    494.  
    495. [I]/**[/I]
    496. [I]*[/I]
    497. [I]* @param table[/I]
    498. [I]* @return[/I]
    499. [I]*/[/I]
    500. [I]public int getRowCount(String table) {[/I]
    501. [I]this.refreshConnection();[/I]
    502.  
    503. [I]ResultSet rs = this.executeQuery("SELECT * FROM " + table);[/I]
    504. [I]int count = 0;[/I]
    505. [I]try[/I]
    506. [I]{[/I]
    507. [I]while (rs.next())[/I]
    508. [I]count++;[/I]
    509. [I]} catch (SQLException e)[/I]
    510. [I]{[/I]
    511. [I]e.printStackTrace();[/I]
    512. [I]}[/I]
    513.  
    514. [I]return count;[/I]
    515. [I]}[/I]
    516.  
    517. [I]/**[/I]
    518. [I]*[/I]
    519. [I]* @return[/I]
    520. [I]*/[/I]
    521. [I]public Connection getConnection() {[/I]
    522. [I]return connection;[/I]
    523. [I]}[/I]
    524.  
    525. [I]}[/I]
    526. [I][/I]
     
    DjMacmo, SnipsRevival and hawkfalcon like this.
  2. Offline

    hawkfalcon

    It creates the database for you?:0
    Mind = blown.
    I'll be trying this out!
     
    TheTrixsta likes this.
  3. Offline

    amitlin14

    completed the unfinished methods (not too sure about the getValueFromRow method since i haven't tested that one)

    Code:java
    1. /**
    2. *
    3. * @param column
    4. * @param value
    5. */
    6. public void insertInto(String table, String[] columns, String[] values) {
    7.  
    8. String statement = "INSERT INTO " + table;
    9.  
    10. String c = "(";
    11. for (int i = 0; i < columns.length; i++)
    12. {
    13. if (i == columns.length - 1)
    14. c = c + columns[i];
    15. else
    16. c = c + columns[i] + ",";
    17. }
    18. c = c + ")";
    19.  
    20. String v = "(";
    21. for (int i = 0; i < values.length; i++)
    22. {
    23. if (i == columns.length - 1)
    24. v = v + columns[i];
    25. else
    26. v = v + columns[i] + ",";
    27. }
    28. v = v + ")";
    29.  
    30. statement = statement + c + " Values" + v + " ON DUPLICATE KEY ";
    31.  
    32. for (int i = 0; i < columns.length; i++)
    33. statement = statement + columns[i] + "='" + values[i] + "' ";
    34.  
    35. this.executeUpdate("INSERT INTO " + table + c + " VALUES" + v);
    36. }
    37.  
    38. /**
    39. *
    40. * @param column
    41. * @param value
    42. * @return
    43. */
    44. public ResultSet getRowByColumn(String table, String column, String value) {
    45. ResultSet rs = this.executeQuery("SELECT * FROM " + table + " WHERE " + column + " = " + value);
    46.  
    47. return rs;
    48. }
    49.  
    50. /**
    51. *
    52. * @param table
    53. * @param set
    54. * @param column
    55. * @return
    56. */
    57. public String getValueFromRow(String table, ResultSet set, String column) {
    58.  
    59. try
    60. {
    61. return set.getString(column);
    62. } catch (SQLException e)
    63. {
    64. e.printStackTrace();
    65. }
    66. return null;
    67. }
    68.  
    69. /**
    70. *
    71. * @param table
    72. * @return
    73. */
    74. public int getRowCount(String table) {
    75. ResultSet rs = this.executeQuery("SELECT * FROM " + table);
    76. int count = 0;
    77. try
    78. {
    79. while (rs.next())
    80. {
    81. count++;
    82. }
    83. } catch (SQLException e)
    84. {
    85. e.printStackTrace();
    86. }
    87.  
    88. return count;
    89. }[/i][/i][/i][/i][/i][/i]
     
    TheTrixsta likes this.
  4. Offline

    NathanG_

    Ooh yay, this will definitely be useful! Thanks! [diamond]
     
    TheTrixsta likes this.
  5. Offline

    TheTrixsta


    Thanks a lot! Ill add it and credit you! I have been having some trouble with those methods!
     
  6. Offline

    hawkfalcon

    Broken formatting
     
    TheTrixsta likes this.
  7. Offline

    TheTrixsta

    Yea no clue how to fix it or what caused it sadly, but if you know how to fix it let me know!

    Edit: Oh it was the BBCode for italics lol

    Once ive added a good number of new methods, ill update the code here with a changelog of what was added! If you have any suggestions for any methods that would be useful let me know!

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 3, 2016
    hawkfalcon likes this.
  8. Offline

    SnipsRevival

    Hmm very interesting. I will consider using this.
     
    TheTrixsta likes this.
  9. Offline

    hawkfalcon

    I would recommend using preparedstatements.
     
  10. Offline

    TheTrixsta

    Will do, I will migrate to prepared statements soon then.
     
    hawkfalcon likes this.
  11. Offline

    hawkfalcon

    Pretty simple, just make it a PreparedStatement and replace any value with a ?, the do setString/Int(#, value).
     
    TheTrixsta likes this.
  12. Offline

    amitlin14

    a quick question, wouldn't it be better if you make the values arrays an object array, so instead of making every value a string, and forcing mysql to use text saving datatypes such as varchar or char, it could be more dynamic so you could add an Integer type, or a Boolean, etc.

    Also, i made a mistake, i accidently forgot to replace the column array with the values array in the for loop in line 23, could you please replace the 'columns' array with 'values'?

    Code:java
    1. /**
    2. *
    3. * @param column
    4. * @param value
    5. */
    6. public void insertInto(String table, String[] columns, Object[] values) {
    7.  
    8. String statement = "INSERT INTO " + table;
    9.  
    10. String c = "(";
    11. for (int i = 0; i < columns.length; i++)
    12. {
    13. if (i == columns.length - 1)
    14. c = c + columns[i];
    15. else
    16. c = c + columns[i] + ",";
    17. }
    18. c = c + ")";
    19.  
    20. String v = "(";
    21. for (int i = 0; i < values.length; i++)
    22. {
    23.  
    24. if (i == columns.length - 1)
    25. {
    26. if (values[i] instanceof String)
    27. v = v + "'" + values[i] + "'";
    28. else
    29. v = v + values[i];
    30. } else
    31. {
    32. if (values[i] instanceof String)
    33. v = v + "'" + values[i] + "', ";
    34. else
    35. v = v + values[i] + ", ";
    36. }
    37. }
    38. v = v + ")";
    39.  
    40. statement = statement + c + " VALUES" + v + " ON DUPLICATE KEY UPDATE ";
    41.  
    42. for (int i = 0; i < columns.length; i++)
    43. {
    44. statement = statement + columns[i] + "=";
    45.  
    46. if (i == columns.length - 1)
    47. {
    48. if (values[i] instanceof String)
    49. statement = statement + "'" + values[i] + "'";
    50. else
    51. statement = statement + values[i];
    52. } else
    53. {
    54. if (values[i] instanceof String)
    55. statement = statement + "'" + values[i] + "', ";
    56. else
    57. statement = statement + values[i] + ", ";
    58. }
    59.  
    60. }
    61. statement = statement + ";";
    62.  
    63. this.executeUpdate(statement);
    64. }
    65.  
    66. public void insertIntoWithoutPrimaryKey(String table, String[] columns, Object[] values) {
    67.  
    68. String statement = "INSERT INTO " + table;
    69.  
    70. String c = "(";
    71. for (int i = 0; i < columns.length; i++)
    72. {
    73. if (i == columns.length - 1)
    74. c = c + columns[i];
    75. else
    76. c = c + columns[i] + ",";
    77. }
    78. c = c + ")";
    79.  
    80. String v = "(";
    81. for (int i = 0; i < values.length; i++)
    82. {
    83.  
    84. if (i == columns.length - 1)
    85. {
    86. if (values[i] instanceof String)
    87. v = v + "'" + values[i] + "'";
    88. else
    89. v = v + values[i];
    90. } else
    91. {
    92. if (values[i] instanceof String)
    93. v = v + "'" + values[i] + "', ";
    94. else
    95. v = v + values[i] + ", ";
    96. }
    97. }
    98. v = v + ")";
    99.  
    100. statement = statement + c + " VALUES" + v + " ON DUPLICATE KEY UPDATE ";
    101.  
    102. for (int i = 1; i < columns.length; i++)
    103. {
    104. statement = statement + columns[i] + "=";
    105.  
    106. if (i == columns.length - 1)
    107. {
    108. if (values[i] instanceof String)
    109. statement = statement + "'" + values[i] + "'";
    110. else
    111. statement = statement + values[i];
    112. } else
    113. {
    114. if (values[i] instanceof String)
    115. statement = statement + "'" + values[i] + "', ";
    116. else
    117. statement = statement + values[i] + ", ";
    118. }
    119.  
    120. }
    121. statement = statement + ";";
    122.  
    123. this.executeUpdate(statement);
    124. }[I][/I][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i]
    [i][i][i][i][i][i][i][i][i][i][I][/I]

    [I][I]Made the insertInto method be able to insert more data types than just strings, also added another method if you are using a primary key, its insertIntoWithoutPrimaryKey[/I][/I][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i]
     
    TheTrixsta likes this.
  13. Offline

    TheTrixsta

    Agreed

    You are correct, I just don't have a ton of time with the projects, real life stuff :p

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 3, 2016
  14. Offline

    DjMacmo

    Can you please make more examples? If it is possible for all functions?
     
  15. Offline

    TheTrixsta

    I sure will
     
  16. Offline

    DjMacmo

    Thank you, can i send you a pm? I have 2 problems. :/
     
    TheTrixsta likes this.
  17. Offline

    TheTrixsta

    Yes please do :D
     
  18. Offline

    kezz101

    Couple of bugbears:
    1. executeQuery will throw errors when people try to do anything with the returned ResultSet as you closed the Connection
    2. You should really throw the SQLExceptions rather than just logging an error, it could cause massive NPEs when people use the library expecting results
    3. The code in insertInto doesn't work and is logically flawed
    4. The spacing is all messed up
    Other than that. Nice work! Won't be using it in my plugin yet ;) But then again, I do have my own MySQL lib...
     
    TheTrixsta likes this.
  19. Offline

    TheTrixsta

    Appreciate the constructive criticism, I will look into all the problems, but just to state, This was just written recently and as any software needs debugging so will this library, eventually these problems will be ironed out, fixed, and the spacing is all preference, though I like my code organized :)
     
    kezz101 likes this.
  20. Offline

    kezz101

    Okie dokie :)
     
  21. Offline

    TheTrixsta

    Btw thanks, Ive been trying to figure out why I keep getting an error referring to that!

    1. executeQuery will throw errors when people try to do anything with the returned ResultSet as you closed the Connection
     
  22. Offline

    Compressions


    [​IMG]
     
    TheTrixsta likes this.
  23. Offline

    TheTrixsta


    That has made my day hahaha
     
  24. Offline

    amitlin14

    TheTrixsta oh yeah, i completely forgot to share the changes i did to the class, i fixed the resultset being closed thingy, and made a couple other changes that i cant really remember them....

    kezz101 yeah sorry that was me, i posted a fix for that in one of my earlier posts, he just didnt update it into the code in his post.

    heres the final working version, tested:

    Code:java
    1.  
    2. import java.sql.Connection;
    3. import java.sql.DriverManager;
    4. import java.sql.PreparedStatement;
    5. import java.sql.ResultSet;
    6. import java.sql.SQLException;
    7. import java.sql.Statement;
    8.  
    9. /**
    10.  *
    11.  * @author Jordan Wiggins
    12.  *
    13.  */
    14.  
    15. /*
    16.  * Example use:
    17.  *
    18.  * FruitSQL sql = new FruitSQL("host", "port", "database", "username",
    19.  * "password"); //Connect to database
    20.  * sql.createTable("users"); //Create the table 'users'
    21.  */
    22.  
    23. public class SQLUtils {
    24.  
    25. private Connection connection = null;
    26.  
    27. /**
    28. *
    29. * @param host
    30. * @param port
    31. * @param database
    32. * @param username
    33. * @param password
    34. */
    35. public SQLUtils(String host, String port, String database, String username, String password) {
    36. long start = 0;
    37. long end = 0;
    38.  
    39. try
    40. {
    41. start = System.currentTimeMillis();
    42. System.out.println("Attempting to establish a connection the MySQL server!");
    43. Class.forName("com.mysql.jdbc.Driver");
    44. connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
    45. end = System.currentTimeMillis();
    46. System.out.println("Connection to MySQL server established! (" + host + ":" + port + ")");
    47. System.out.println("Connection took " + ((end - start)) + "ms!");
    48. } catch (SQLException e)
    49. {
    50. System.out.println("Could not connect to MySQL server! because: " + e.getMessage());
    51. {
    52. System.out.println("JDBC Driver not found!");
    53. }
    54. }
    55.  
    56. /**
    57. *
    58. * @return
    59. */
    60. public boolean isConnected() {
    61. if (connection != null) { return true; }
    62. return false;
    63. }
    64.  
    65. /**
    66. *
    67. */
    68. public void closeConnection() {
    69. try
    70. {
    71. connection.close();
    72. System.out.println("MySQL Connection closed");
    73. } catch (SQLException e)
    74. {
    75. System.out.println("Couldn't close connection");
    76. }
    77. }
    78.  
    79. /**
    80. *
    81. * @param sql
    82. * @return
    83. */
    84. public boolean execute(String sql) {
    85. boolean st = false;
    86. Statement statement = null;
    87. try
    88. {
    89. statement = connection.createStatement();
    90. st = statement.execute(sql);
    91. } catch (SQLException e)
    92. {
    93. e.printStackTrace();
    94. } finally
    95. {
    96. try
    97. {
    98. statement.close();
    99. } catch (SQLException e)
    100. {
    101. e.printStackTrace();
    102. }
    103. }
    104. return st;
    105. }
    106.  
    107. /**
    108. *
    109. * @param sql
    110. * @return
    111. */
    112. public ResultSet executeQuery(String sql) {
    113.  
    114. PreparedStatement statement = null;
    115. ResultSet rs = null;
    116.  
    117. try
    118. {
    119. statement = connection.prepareStatement(sql);
    120. rs = statement.executeQuery();
    121. } catch (SQLException e)
    122. {
    123. e.printStackTrace();
    124. }
    125.  
    126. return rs;
    127. }
    128.  
    129. public void closeQuietly(ResultSet rs) {
    130. try
    131. {
    132. ps = (PreparedStatement) rs.getStatement();
    133. rs.close();
    134. ps.close();
    135. } catch (SQLException e)
    136. {
    137. e.printStackTrace();
    138. }
    139. }
    140.  
    141. /**
    142. *
    143. * @param sql
    144. * @return
    145. */
    146. public int executeUpdate(String sql) {
    147. int st = 0;
    148.  
    149. Statement statement = null;
    150.  
    151. try
    152. {
    153. statement = connection.createStatement();
    154. st = statement.executeUpdate(sql);
    155. statement.close();
    156. } catch (SQLException e)
    157. {
    158. e.printStackTrace();
    159. } finally
    160. {
    161. try
    162. {
    163. statement.close();
    164. } catch (SQLException e)
    165. {
    166. e.printStackTrace();
    167. }
    168. }
    169.  
    170. return st;
    171. }
    172.  
    173. /**
    174. *
    175. * @param tablename
    176. * @param values
    177. */
    178. public void createTable(String tablename, String[] values) {
    179.  
    180. String stmt = "CREATE TABLE IF NOT EXISTS " + tablename + "(";
    181.  
    182. for (int i = 0; i < values.length; i++)
    183. if (i == values.length - 1) stmt = stmt.concat(values[i]);
    184. else stmt = stmt.concat(values[i] + ", ");
    185.  
    186. stmt = stmt.concat(");");
    187.  
    188. Statement statement = null;
    189.  
    190. try
    191. {
    192. statement = connection.createStatement();
    193. statement.executeUpdate(stmt);
    194. } catch (SQLException e)
    195. {
    196. e.printStackTrace();
    197. } finally
    198. {
    199. try
    200. {
    201. statement.close();
    202. } catch (SQLException e)
    203. {
    204. e.printStackTrace();
    205. }
    206. }
    207.  
    208. }
    209.  
    210. /**
    211. *
    212. * @param tablename
    213. */
    214. public void deleteTable(String tablename) {
    215. String sql = "DROP TABLE IF EXISTS " + tablename;
    216. Statement statement = null;
    217. try
    218. {
    219. statement = connection.createStatement();
    220. statement.executeUpdate(sql);
    221. } catch (SQLException e)
    222. {
    223. e.printStackTrace();
    224. } finally
    225. {
    226. try
    227. {
    228. statement.close();
    229. } catch (SQLException e)
    230. {
    231. e.printStackTrace();
    232. }
    233. }
    234. }
    235.  
    236. /**
    237. *
    238. * @param tablename
    239. * @param values
    240. */
    241. public void resetTable(String tablename, String[] values) {
    242. deleteTable(tablename);
    243. createTable(tablename, values);
    244. }
    245.  
    246. /**
    247. *
    248. * @param column
    249. * @param value
    250. */
    251. public void insertInto(String table, String[] columns, Object[] values) {
    252.  
    253. String statement = "INSERT INTO " + table;
    254.  
    255. String c = "(";
    256. for (int i = 0; i < columns.length; i++)
    257. {
    258. if (i == columns.length - 1) c = c + columns[i];
    259. else c = c + columns[i] + ",";
    260. }
    261. c = c + ")";
    262.  
    263. String v = "(";
    264. for (int i = 0; i < values.length; i++)
    265. {
    266.  
    267. if (i == columns.length - 1)
    268. {
    269. if (values[i] instanceof String) v = v + "'" + values[i] + "'";
    270. else v = v + values[i];
    271. } else
    272. {
    273. if (values[i] instanceof String) v = v + "'" + values[i] + "', ";
    274. else v = v + values[i] + ", ";
    275. }
    276. }
    277. v = v + ")";
    278.  
    279. statement = statement + c + " VALUES" + v + " ON DUPLICATE KEY UPDATE ";
    280.  
    281. for (int i = 0; i < columns.length; i++)
    282. {
    283. statement = statement + columns[i] + "=";
    284.  
    285. if (i == columns.length - 1)
    286. {
    287. if (values[i] instanceof String) statement = statement + "'" + values[i] + "'";
    288. else statement = statement + values[i];
    289. } else
    290. {
    291. if (values[i] instanceof String) statement = statement + "'" + values[i] + "', ";
    292. else statement = statement + values[i] + ", ";
    293. }
    294.  
    295. }
    296. statement = statement + ";";
    297.  
    298. this.executeUpdate(statement);
    299. }
    300.  
    301. public void insertIntoWithoutPrimaryKey(String table, String[] columns, Object[] values) {
    302.  
    303. String statement = "INSERT INTO " + table;
    304.  
    305. String c = "(";
    306. for (int i = 0; i < columns.length; i++)
    307. {
    308. if (i == columns.length - 1) c = c + columns[i];
    309. else c = c + columns[i] + ",";
    310. }
    311. c = c + ")";
    312.  
    313. String v = "(";
    314. for (int i = 0; i < values.length; i++)
    315. {
    316.  
    317. if (i == columns.length - 1)
    318. {
    319. if (values[i] instanceof String) v = v + "'" + values[i] + "'";
    320. else v = v + values[i];
    321. } else
    322. {
    323. if (values[i] instanceof String) v = v + "'" + values[i] + "', ";
    324. else v = v + values[i] + ", ";
    325. }
    326. }
    327. v = v + ")";
    328.  
    329. statement = statement + c + " VALUES" + v + " ON DUPLICATE KEY UPDATE ";
    330.  
    331. for (int i = 1; i < columns.length; i++)
    332. {
    333. statement = statement + columns[i] + "=";
    334.  
    335. if (i == columns.length - 1)
    336. {
    337. if (values[i] instanceof String) statement = statement + "'" + values[i] + "'";
    338. else statement = statement + values[i];
    339. } else
    340. {
    341. if (values[i] instanceof String) statement = statement + "'" + values[i] + "', ";
    342. else statement = statement + values[i] + ", ";
    343. }
    344.  
    345. }
    346. statement = statement + ";";
    347.  
    348. this.executeUpdate(statement);
    349. }
    350.  
    351. /**
    352. *
    353. * @param column
    354. * @param value
    355. * @return
    356. */
    357. public ResultSet getRowByColumn(String table, String column, String value) {
    358. ResultSet rs = this.executeQuery("SELECT * FROM " + table + " WHERE " + column + " = " + value);
    359.  
    360. return rs;
    361. }
    362.  
    363. /**
    364. *
    365. * @param table
    366. * @param set
    367. * @param column
    368. * @return
    369. */
    370. public String getValueFromRow(String table, ResultSet set, String column) {
    371.  
    372. try
    373. {
    374. return set.getString(column);
    375. } catch (SQLException e)
    376. {
    377. e.printStackTrace();
    378. }
    379. return null;
    380. }
    381.  
    382. /**
    383. *
    384. * @param table
    385. * @return
    386. */
    387. public int getRowCount(String table) {
    388. ResultSet rs = this.executeQuery("SELECT * FROM " + table);
    389. int count = 0;
    390. try
    391. {
    392. while (rs.next())
    393. count++;
    394. } catch (SQLException e)
    395. {
    396. e.printStackTrace();
    397. }
    398.  
    399. return count;
    400. }
    401.  
    402. /**
    403. *
    404. * @return
    405. */
    406. public Connection getConnection() {
    407. return connection;
    408. }
    409.  
    410. }
    411. [I][I][/I][/I][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i]
    [i][i][i][i][i][i][i][i][i][i][i][i][I][I][/I][/I]

    [I][I]Added closeQuietly to clean up after you are done with the resultset, that way you avoid the resultset being closed error, just make sure to use it properly, seeing as how open statement and resultsets can cause quite a bit of trouble when left open[/I][/I]

    [I][I]I might have left a few methods without closing their statements, ill get to that tomorrow[/I][/I][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i]
     
    TheTrixsta likes this.
  25. Offline

    TheTrixsta


    Can't thank you enough for putting effort towards this project!
     
  26. Offline

    amitlin14

    More stuff!

    Added a refreshConnection method, since alot of host providers limit connection idle time between 8-24 hours, so if you could get a nasty error when trying to perform a query or an update when the connection has been terminated by the administrator. Sure we can add autoRecconnect=true, but that could cause NASTY problems if not handled correctly, and should only be used as a last resort when the user himself doesnt keep the connection alive, which in this case, we do :)

    Here's the method:
    Code:java
    1. public void refreshConnection() {
    2.  
    3. ResultSet valid = null;
    4. try
    5. {
    6. st = connection.prepareStatement("SELECT 1 FROM Dual");
    7. valid = st.executeQuery();
    8. if (valid.next())
    9. return;
    10. } catch (SQLException e2)
    11. {
    12. System.out.println("Connection is idle or terminated. Reconnecting...");
    13. } finally
    14. {
    15. this.closeQuietly(valid);
    16. }
    17.  
    18. long start = 0;
    19. long end = 0;
    20.  
    21. try
    22. {
    23. start = System.currentTimeMillis();
    24. System.out.println("Attempting to establish a connection the MySQL server!");
    25. Class.forName("com.mysql.jdbc.Driver");
    26. connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
    27. end = System.currentTimeMillis();
    28. System.out.println("Connection to MySQL server established! (" + host + ":" + port + ")");
    29. System.out.println("Connection took " + ((end - start)) + "ms!");
    30. } catch (SQLException e)
    31. {
    32. System.out.println("Could not connect to MySQL server! because: " + e.getMessage());
    33. {
    34. System.out.println("JDBC Driver not found!");
    35. }
    36. }


    All it does is select the value 1 from a dummy default table that comes with every oracle database installation, such as mysql, called the DUAL table, it just checks if the connection is alive, if its dead, it reconnects.

    Also a nifty little thing i found out about while looking on how to improve a plugin i made that does ALOT of inserts, updates and generally queries, is the use of Batch Queries! Instead of using a resource-consuming way of sending queries to a table, which is one by one, the batch takes all those statements, and compresses them into one statement, which will save you a LOT of resources.

    Here's the method:

    Code:java
    1. /**
    2. * @param statements
    3. * - the list of statements
    4. * @param batchSize
    5. * - the size of each batch to avoid an outOfMemory exception,
    6. * set to 0 if you want to ignore
    7. */
    8. public void executeBatch(String[] statements, int batchSize) {
    9.  
    10. int count = 0;
    11.  
    12. Statement statement = null;
    13. try
    14. {
    15. statement = connection.createStatement();
    16. for (String query : statements)
    17. {
    18. statement.addBatch(query);
    19.  
    20. if (batchSize != 0)
    21. if (count % batchSize == 0)
    22. statement.executeBatch(); // execute batch.
    23. // batch size limit has been
    24. // reached
    25. count++;
    26. }
    27.  
    28. statement.executeBatch(); // execute the remaining batches
    29. statement.close();
    30. statement = null;
    31. } catch (SQLException e)
    32. {
    33. e.printStackTrace();
    34. } finally
    35. {
    36. if (statement != null)
    37. try
    38. {
    39. statement.close();
    40. } catch (SQLException e)
    41. {
    42. // Ignore this, nothing we can do about this now
    43. }
    44. }
    45.  
    46. }


    If you are not sure on how to use it, ive made a little tutorial, as it was a bit confusing for me at first aswell:

    Code:java
    1. public static void main(String[] args) {
    2.  
    3. FruitSQL sql = new FruitSQL("localhost", "3306", "test", "root", "");
    4.  
    5. sql.createTable("BatchExample", new String[] {
    6. "id MEDIUMINT PRIMARY KEY NOT NULL AUTO_INCREMENT", "lastLogin CHAR(19)",
    7. "firstLogin CHAR(19)" });
    8.  
    9. String[] statements = new String[200];
    10.  
    11. for (int i = 0; i < statements.length; i++)
    12. {
    13. String sqlStatement = "INSERT INTO BatchExample(id,lastLogin,firstLogin) VALUES(?,?,?) ON DUPLICATE KEY UPDATE lastLogin='?';";
    14. String last_Login = new SimpleDateFormat("yyyy/MM/dd_HH:mm:ss").format(System.currentTimeMillis() + 10000 * i);
    15. String first_Login = new SimpleDateFormat("yyyy/MM/dd_HH:mm:ss").format(System.currentTimeMillis());
    16.  
    17. // the '*' sign shows which value is being replaced
    18. sqlStatement = sqlStatement.replaceFirst("\\?", Integer.toString(i)); // VALUES(*?*,?,?)
    19. sqlStatement = sqlStatement.replaceFirst("\\?", "'" + last_Login + "'"); // VALUES(?,*?*,?)
    20. sqlStatement = sqlStatement.replaceFirst("\\?", "'" + first_Login + "'"); // VALUES(?,?,*?*)
    21. sqlStatement = sqlStatement.replaceFirst("\\?", last_Login); // lastLogin='*?*'
    22. System.out.println(sqlStatement);
    23. statements[I] = sqlStatement;[/I]
    24. [I]}[/I]
    25.  
    26. [I]sql.executeBatch(statements, 50); // the 50 is each batch's size limit, so you wont get a nasty out of memory error[/I]
    27. [I]}[/I]



    And behold! 200 statements exactly!
    [​IMG]


    Here is my whole class, i dont remember if i changed anything else, so ill just throw it out there in-case i have:

    Code:java
    1. import java.sql.Connection;
    2. import java.sql.DriverManager;
    3. import java.sql.PreparedStatement;
    4. import java.sql.ResultSet;
    5. import java.sql.SQLException;
    6. import java.sql.Statement;
    7.  
    8. /**
    9. *
    10. * @author Jordan Wiggins
    11. *
    12. */
    13.  
    14. /*
    15. * Example use:
    16. *
    17. * FruitSQL sql = new FruitSQL("host", "port", "database", "username",
    18. * "password"); //Connect to database
    19. * sql.createTable("users"); //Create the table 'users'
    20. */
    21.  
    22. public class FruitSQL {
    23.  
    24. private Connection connection = null;
    25. private String host, port, database, username, password;
    26.  
    27. /**
    28. *
    29. * @param host
    30. * @param port
    31. * @param database
    32. * @param username
    33. * @param password
    34. */
    35. public FruitSQL(String host, String port, String database, String username, String password) {
    36. long start = 0;
    37. long end = 0;
    38.  
    39. try
    40. {
    41. start = System.currentTimeMillis();
    42. System.out.println("Attempting to establish a connection the MySQL server!");
    43. Class.forName("com.mysql.jdbc.Driver");
    44. connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
    45. end = System.currentTimeMillis();
    46. System.out.println("Connection to MySQL server established! (" + host + ":" + port + ")");
    47. System.out.println("Connection took " + ((end - start)) + "ms!");
    48. } catch (SQLException e)
    49. {
    50. System.out.println("Could not connect to MySQL server! because: " + e.getMessage());
    51. {
    52. System.out.println("JDBC Driver not found!");
    53. }
    54.  
    55. this.host = host;
    56. this.port = port;
    57. this.database = database;
    58. this.username = username;
    59. this.password = password;
    60. }
    61.  
    62. /**
    63. *
    64. * @return
    65. */
    66. public boolean isConnected() {
    67. if (connection != null) { return true; }
    68. return false;
    69. }
    70.  
    71. /**
    72. *
    73. */
    74. public void closeConnection() {
    75. if (connection == null)
    76. try
    77. {
    78. connection.close();
    79. System.out.println("MySQL Connection closed");
    80. } catch (SQLException e)
    81. {
    82. System.out.println("Couldn't close connection");
    83. }
    84. }
    85.  
    86. /**
    87. *
    88. */
    89. public void refreshConnection() {
    90.  
    91. ResultSet valid = null;
    92. try
    93. {
    94. st = connection.prepareStatement("SELECT 1 FROM Dual");
    95. valid = st.executeQuery();
    96. if (valid.next())
    97. return;
    98. } catch (SQLException e2)
    99. {
    100. System.out.println("Connection is idle or terminated. Reconnecting...");
    101. } finally
    102. {
    103. this.closeQuietly(valid);
    104. }
    105.  
    106. long start = 0;
    107. long end = 0;
    108.  
    109. try
    110. {
    111. start = System.currentTimeMillis();
    112. System.out.println("Attempting to establish a connection the MySQL server!");
    113. Class.forName("com.mysql.jdbc.Driver");
    114. connection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
    115. end = System.currentTimeMillis();
    116. System.out.println("Connection to MySQL server established! (" + host + ":" + port + ")");
    117. System.out.println("Connection took " + ((end - start)) + "ms!");
    118. } catch (SQLException e)
    119. {
    120. System.out.println("Could not connect to MySQL server! because: " + e.getMessage());
    121. {
    122. System.out.println("JDBC Driver not found!");
    123. }
    124. }
    125.  
    126. /**
    127. *
    128. * @param sql
    129. * @return
    130. */
    131. public boolean execute(String sql) {
    132. this.refreshConnection();
    133.  
    134. boolean st = false;
    135. try
    136. {
    137. Statement statement = connection.createStatement();
    138. st = statement.execute(sql);
    139. statement.close();
    140. } catch (SQLException e)
    141. {
    142. e.printStackTrace();
    143. }
    144. return st;
    145. }
    146.  
    147. /**
    148. *
    149. * @param sql
    150. * @return
    151. */
    152. public ResultSet executeQuery(String sql) {
    153. this.refreshConnection();
    154.  
    155. PreparedStatement statement = null;
    156. ResultSet rs = null;
    157.  
    158. try
    159. {
    160. statement = connection.prepareStatement(sql);
    161. rs = statement.executeQuery();
    162. } catch (SQLException e)
    163. {
    164. e.printStackTrace();
    165. }
    166.  
    167. return rs;
    168. }
    169.  
    170. public void closeQuietly(ResultSet rs) {
    171. try
    172. {
    173. ps = (PreparedStatement) rs.getStatement();
    174. rs.close();
    175. rs = null;
    176. ps.close();
    177. ps = null;
    178. } catch (SQLException e)
    179. {
    180. e.printStackTrace();
    181. } finally
    182. {
    183. if (rs != null)
    184. try
    185. {
    186. rs.close();
    187. } catch (SQLException e)
    188. {
    189. // Ignore... nothing we can do about this here
    190. }
    191.  
    192. if (ps != null)
    193. try
    194. {
    195. rs.close();
    196. } catch (SQLException e)
    197. {
    198. // Ignore... nothing we can do about this here
    199. }
    200. }
    201. }
    202.  
    203. /**
    204. *
    205. * @param sql
    206. * @return
    207. */
    208. public int executeUpdate(String sql) {
    209. this.refreshConnection();
    210.  
    211. int st = 0;
    212. try
    213. {
    214. Statement statement = connection.createStatement();
    215. st = statement.executeUpdate(sql);
    216. statement.close();
    217. } catch (SQLException e)
    218. {
    219. e.printStackTrace();
    220. }
    221. return st;
    222. }
    223.  
    224. /**
    225. * @param statements
    226. * - the list of statements
    227. * @param batchSize
    228. * - the size of each batch to avoid an outOfMemory exception,
    229. * set to 0 if you want to ignore
    230. */
    231. public void executeBatch(String[] statements, int batchSize) {
    232.  
    233. int count = 0;
    234.  
    235. Statement statement = null;
    236. try
    237. {
    238. statement = connection.createStatement();
    239. for (String query : statements)
    240. {
    241. statement.addBatch(query);
    242.  
    243. if (batchSize != 0)
    244. if (count % batchSize == 0)
    245. statement.executeBatch(); // execute batch.
    246. // batch size limit has been
    247. // reached
    248. count++;
    249. }
    250.  
    251. statement.executeBatch(); // execute the remaining batches
    252. statement.close();
    253. statement = null;
    254. } catch (SQLException e)
    255. {
    256. e.printStackTrace();
    257. } finally
    258. {
    259. if (statement != null)
    260. try
    261. {
    262. statement.close();
    263. } catch (SQLException e)
    264. {
    265. // Ignore this, nothing we can do about this now
    266. }
    267. }
    268.  
    269. }
    270.  
    271. /**
    272. *
    273. * @param tablename
    274. * @param values
    275. */
    276. public void createTable(String tablename, String[] values) {
    277. this.refreshConnection();
    278.  
    279. String stmt = "CREATE TABLE IF NOT EXISTS " + tablename + "(";
    280.  
    281. for (int i = 0; i < values.length; i++)
    282. if (i == values.length - 1)
    283. stmt = stmt.concat(values);
    284. else
    285. stmt = stmt.concat(values + ", ");
    286.  
    287. stmt = stmt.concat(");");
    288.  
    289. try
    290. {
    291. Statement statement = connection.createStatement();
    292. statement.executeUpdate(stmt);
    293. } catch (SQLException e)
    294. {
    295. e.printStackTrace();
    296. }
    297.  
    298. }
    299.  
    300. /**
    301. *
    302. * @param tablename
    303. */
    304. public void deleteTable(String tablename) {
    305. this.refreshConnection();
    306.  
    307. String sql = "DROP TABLE IF EXISTS " + tablename;
    308.  
    309. try
    310. {
    311. Statement statement = connection.createStatement();
    312. statement.executeUpdate(sql);
    313. } catch (SQLException e)
    314. {
    315. e.printStackTrace();
    316. }
    317. }
    318.  
    319. /**
    320. *
    321. * @param tablename
    322. * @param values
    323. */
    324. public void resetTable(String tablename, String[] values) {
    325. this.refreshConnection();
    326.  
    327. deleteTable(tablename);
    328. createTable(tablename, values);
    329. }
    330.  
    331. /**
    332. *
    333. * @param column
    334. * @param value
    335. */
    336. public void insertInto(String table, String[] columns, Object[] values) {
    337. this.refreshConnection();
    338.  
    339. String statement = "INSERT INTO " + table;
    340.  
    341. String c = "(";
    342. for (int i = 0; i < columns.length; i++)
    343. {
    344. if (i == columns.length - 1)
    345. c = c + columns;
    346. else
    347. c = c + columns + ",";
    348. }
    349. c = c + ")";
    350.  
    351. String v = "(";
    352. for (int i = 0; i < values.length; i++)
    353. {
    354.  
    355. if (i == columns.length - 1)
    356. {
    357. if (values instanceof String)
    358. v = v + "'" + values + "'";
    359. else
    360. v = v + values;
    361. } else
    362. {
    363. if (values instanceof String)
    364. v = v + "'" + values + "', ";
    365. else
    366. v = v + values + ", ";
    367. }
    368. }
    369. v = v + ")";
    370.  
    371. statement = statement + c + " VALUES" + v + " ON DUPLICATE KEY UPDATE ";
    372.  
    373. for (int i = 0; i < columns.length; i++)
    374. {
    375. statement = statement + columns + "=";
    376.  
    377. if (i == columns.length - 1)
    378. {
    379. if (values instanceof String)
    380. statement = statement + "'" + values + "'";
    381. else
    382. statement = statement + values;
    383. } else
    384. {
    385. if (values instanceof String)
    386. statement = statement + "'" + values + "', ";
    387. else
    388. statement = statement + values + ", ";
    389. }
    390.  
    391. }
    392. statement = statement + ";";
    393.  
    394. this.executeUpdate(statement);
    395. }
    396.  
    397. public void insertIntoWithoutPrimaryKey(String table, String[] columns, Object[] values) {
    398. this.refreshConnection();
    399.  
    400. String statement = "INSERT INTO " + table;
    401.  
    402. String c = "(";
    403. for (int i = 0; i < columns.length; i++)
    404. {
    405. if (i == columns.length - 1)
    406. c = c + columns;
    407. else
    408. c = c + columns + ",";
    409. }
    410. c = c + ")";
    411.  
    412. String v = "(";
    413. for (int i = 0; i < values.length; i++)
    414. {
    415.  
    416. if (i == columns.length - 1)
    417. {
    418. if (values instanceof String)
    419. v = v + "'" + values + "'";
    420. else
    421. v = v + values;
    422. } else
    423. {
    424. if (values instanceof String)
    425. v = v + "'" + values + "', ";
    426. else
    427. v = v + values + ", ";
    428. }
    429. }
    430. v = v + ")";
    431.  
    432. statement = statement + c + " VALUES" + v + " ON DUPLICATE KEY UPDATE ";
    433.  
    434. for (int i = 1; i < columns.length; i++)
    435. {
    436. statement = statement + columns + "=";
    437.  
    438. if (i == columns.length - 1)
    439. {
    440. if (values instanceof String)
    441. statement = statement + "'" + values + "'";
    442. else
    443. statement = statement + values;
    444. } else
    445. {
    446. if (values instanceof String)
    447. statement = statement + "'" + values + "', ";
    448. else
    449. statement = statement + values + ", ";
    450. }
    451.  
    452. }
    453. statement = statement + ";";
    454.  
    455. this.executeUpdate(statement);
    456. }
    457.  
    458. /**
    459. *
    460. * @param column
    461. * @param value
    462. * @return
    463. */
    464. public ResultSet getRowByColumn(String table, String column, String value) {
    465. this.refreshConnection();
    466.  
    467. ResultSet rs = this.executeQuery("SELECT * FROM " + table + " WHERE " + column + " = " + value);
    468.  
    469. return rs;
    470. }
    471.  
    472. /**
    473. *
    474. * @param table
    475. * @param set
    476. * @param column
    477. * @return
    478. */
    479. public String getValueFromRow(String table, ResultSet set, String column) {
    480. this.refreshConnection();
    481.  
    482. try
    483. {
    484. return set.getString(column);
    485. } catch (SQLException e)
    486. {
    487. e.printStackTrace();
    488. }
    489. return null;
    490. }
    491.  
    492. /**
    493. *
    494. * @param table
    495. * @return
    496. */
    497. public int getRowCount(String table) {
    498. this.refreshConnection();
    499.  
    500. ResultSet rs = this.executeQuery("SELECT * FROM " + table);
    501. int count = 0;
    502. try
    503. {
    504. while (rs.next())
    505. count++;
    506. } catch (SQLException e)
    507. {
    508. e.printStackTrace();
    509. }
    510.  
    511. return count;
    512. }
    513.  
    514. /**
    515. *
    516. * @return
    517. */
    518. public Connection getConnection() {
    519. return connection;
    520. }
    521.  
    522. }
     
  27. Offline

    SoThatsIt

    sorry if i sound stupid, but how would i set one value in the table, i have looked at the insert into method but im not sure how i would use that. maybe adding above how to use this library would help :p
     
  28. Offline

    TheTrixsta

    Update, project inactive now, but that doesnt mean you guys cant use it!
     
  29. Offline

    njb_said

    I have something that is kinda important to be added.. &autoReconnect=true to the connection so it doesnt time out after one hour?
     
  30. Offline

    TheTrixsta

    Code updated to FruitSQL, njb_said thanks to amitlin14, lol, he has added the reconnect method!
     
Thread Status:
Not open for further replies.

Share This Page