SqlUtils.java 4.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175
  1. /*
  2. * This program is free software: you can redistribute it and/or modify it under
  3. * the terms of the GNU General Public License as published by the Free Software
  4. * Foundation, either version 3 of the License, or (at your option) any later
  5. * version.
  6. *
  7. * This program is distributed in the hope that it will be useful, but WITHOUT
  8. * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
  9. * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
  10. * details.
  11. *
  12. * You should have received a copy of the GNU General Public License along with
  13. * this program. If not, see <http://www.gnu.org/licenses/>.
  14. */
  15. package com.l2jserver.util.lib;
  16. import java.sql.Connection;
  17. import java.sql.PreparedStatement;
  18. import java.sql.ResultSet;
  19. import java.util.logging.Level;
  20. import java.util.logging.Logger;
  21. import com.l2jserver.L2DatabaseFactory;
  22. public class SqlUtils
  23. {
  24. private static Logger _log = Logger.getLogger(SqlUtils.class.getName());
  25. private SqlUtils()
  26. {
  27. }
  28. // =========================================================
  29. // Property - Public
  30. public static SqlUtils getInstance()
  31. {
  32. return SingletonHolder._instance;
  33. }
  34. // =========================================================
  35. // Method - Public
  36. public static Integer getIntValue(String resultField, String tableName, String whereClause)
  37. {
  38. String query = "";
  39. Integer res = null;
  40. Connection con = null;
  41. try
  42. {
  43. con = L2DatabaseFactory.getInstance().getConnection();
  44. query = L2DatabaseFactory.getInstance().prepQuerySelect(new String[] { resultField }, tableName, whereClause, true);
  45. PreparedStatement statement = con.prepareStatement(query);
  46. ResultSet rset = statement.executeQuery();
  47. if (rset.next())
  48. res = rset.getInt(1);
  49. rset.close();
  50. statement.close();
  51. }
  52. catch (Exception e)
  53. {
  54. _log.log(Level.WARNING, "Error in query '" + query + "':", e);
  55. }
  56. finally
  57. {
  58. L2DatabaseFactory.close(con);
  59. }
  60. return res;
  61. }
  62. public static Integer[] getIntArray(String resultField, String tableName, String whereClause)
  63. {
  64. String query = "";
  65. Integer[] res = null;
  66. Connection con = null;
  67. try
  68. {
  69. con = L2DatabaseFactory.getInstance().getConnection();
  70. query = L2DatabaseFactory.getInstance().prepQuerySelect(new String[] { resultField }, tableName, whereClause, false);
  71. PreparedStatement statement = con.prepareStatement(query);
  72. ResultSet rset = statement.executeQuery();
  73. int rows = 0;
  74. while (rset.next())
  75. rows++;
  76. if (rows == 0)
  77. return new Integer[0];
  78. res = new Integer[rows - 1];
  79. rset.first();
  80. int row = 0;
  81. while (rset.next())
  82. {
  83. res[row] = rset.getInt(1);
  84. }
  85. rset.close();
  86. statement.close();
  87. }
  88. catch (Exception e)
  89. {
  90. _log.log(Level.WARNING, "mSGI: Error in query '" + query + "':", e);
  91. }
  92. finally
  93. {
  94. L2DatabaseFactory.close(con);
  95. }
  96. return res;
  97. }
  98. public static Integer[][] get2DIntArray(String[] resultFields, String usedTables, String whereClause)
  99. {
  100. long start = System.currentTimeMillis();
  101. String query = "";
  102. Connection con = null;
  103. Integer res[][] = null;
  104. try
  105. {
  106. con = L2DatabaseFactory.getInstance().getConnection();
  107. query = L2DatabaseFactory.getInstance().prepQuerySelect(resultFields, usedTables, whereClause, false);
  108. PreparedStatement statement = con.prepareStatement(query);
  109. ResultSet rset = statement.executeQuery();
  110. int rows = 0;
  111. while (rset.next())
  112. rows++;
  113. res = new Integer[rows - 1][resultFields.length];
  114. rset.first();
  115. int row = 0;
  116. while (rset.next())
  117. {
  118. for (int i = 0; i < resultFields.length; i++)
  119. res[row][i] = rset.getInt(i + 1);
  120. row++;
  121. }
  122. rset.close();
  123. statement.close();
  124. }
  125. catch (Exception e)
  126. {
  127. _log.log(Level.WARNING, "Error in query '" + query + "':", e);
  128. }
  129. finally
  130. {
  131. L2DatabaseFactory.close(con);
  132. }
  133. _log.fine("Get all rows in query '" + query + "' in " + (System.currentTimeMillis() - start) + "ms");
  134. return res;
  135. }
  136. @SuppressWarnings("synthetic-access")
  137. private static class SingletonHolder
  138. {
  139. protected static final SqlUtils _instance = new SqlUtils();
  140. }
  141. }