PageRenderTime 64ms CodeModel.GetById 26ms RepoModel.GetById 0ms app.codeStats 1ms

/src/mpv5/db/common/QueryHandler.java

http://mp-rechnungs-und-kundenverwaltung.googlecode.com/
Java | 1785 lines | 1083 code | 154 blank | 548 comment | 265 complexity | ce72914a43803835d57e37dd48643605 MD5 | raw file
Possible License(s): LGPL-3.0, Apache-2.0, GPL-3.0, GPL-2.0, AGPL-3.0, JSON, BSD-3-Clause
  1. package mpv5.db.common;
  2. import java.awt.Color;
  3. import java.awt.Cursor;
  4. import java.beans.PropertyChangeEvent;
  5. import java.beans.PropertyChangeListener;
  6. import java.io.BufferedInputStream;
  7. import java.io.ByteArrayOutputStream;
  8. import java.io.File;
  9. import java.io.FileNotFoundException;
  10. import java.io.FileOutputStream;
  11. import java.io.IOException;
  12. import java.io.InputStream;
  13. import java.io.OutputStreamWriter;
  14. import java.io.Reader;
  15. import java.io.Writer;
  16. import java.net.URI;
  17. import java.sql.Blob;
  18. import java.sql.Connection;
  19. import java.sql.PreparedStatement;
  20. import java.sql.ResultSet;
  21. import java.sql.ResultSetMetaData;
  22. import java.sql.SQLException;
  23. import java.sql.Statement;
  24. import java.util.ArrayList;
  25. import java.util.Collections;
  26. import java.util.Comparator;
  27. import java.util.Date;
  28. import java.util.HashMap;
  29. import java.util.Iterator;
  30. import java.util.LinkedList;
  31. import java.util.List;
  32. import java.util.Map;
  33. import java.util.concurrent.ExecutionException;
  34. import java.util.logging.Level;
  35. import java.util.logging.Logger;
  36. import javax.swing.JFrame;
  37. import javax.swing.JProgressBar;
  38. import javax.swing.JTextArea;
  39. import javax.swing.JTextField;
  40. import javax.swing.SwingUtilities;
  41. import javax.swing.SwingWorker;
  42. import mpv5.db.objects.User;
  43. import mpv5.globals.Messages;
  44. import mpv5.globals.Constants;
  45. import mpv5.globals.LocalSettings;
  46. import mpv5.logging.Log;
  47. import mpv5.ui.dialogs.Popup;
  48. import mpv5.ui.panels.DataPanel;
  49. import mpv5.usermanagement.MPSecurityManager;
  50. import mpv5.utils.arrays.ArrayUtilities;
  51. import mpv5.utils.date.DateConverter;
  52. import mpv5.utils.date.vTimeframe;
  53. import mpv5.utils.files.FileDirectoryHandler;
  54. import mpv5.utils.text.RandomText;
  55. import mpv5.utils.text.TypeConversion;
  56. import mpv5.utils.ui.TextFieldUtils;
  57. /**
  58. *
  59. * Use this class to access the Yabs database.
  60. *
  61. * @see QueryHandler#instanceOf()
  62. * @see QueryHandler#getConnection()
  63. * @see QueryHandler#clone(mpv5.db.common.Context)
  64. */
  65. public class QueryHandler implements Cloneable {
  66. private static QueryHandler instance;
  67. private static JProgressBar progressbar = new JProgressBar();
  68. private static class SQLWatch extends Thread {
  69. private final long start;
  70. private boolean done;
  71. private final String watchedQuery;
  72. private long minTime = 1000l;
  73. public SQLWatch(String query) {
  74. start = new Date().getTime();
  75. watchedQuery = query;
  76. }
  77. public void done() {
  78. done = true;
  79. }
  80. @Override
  81. @SuppressWarnings("SleepWhileInLoop")
  82. public void run() {
  83. while (!done) {
  84. try {
  85. Thread.sleep(minTime);
  86. } catch (InterruptedException ex) {
  87. Log.Debug(ex);
  88. }
  89. Log.Debug(this, "SQLWatch " + this + " ["
  90. + (new Date().getTime() - start)
  91. + "]ms for " + watchedQuery);
  92. }
  93. }
  94. }
  95. private DatabaseConnection conn = null;
  96. private Connection sqlConn = null;
  97. private String table = "NOTABLE";
  98. private static JFrame comp = new JFrame();
  99. private Context context;
  100. /**
  101. * !Use "Clone" method before actually do anything!
  102. *
  103. * @return The one and only instance of the database connection
  104. */
  105. public static synchronized QueryHandler instanceOf() {
  106. //Explicitely instantiated at first connection attempt
  107. if (instance == null) {
  108. instance = new QueryHandler();
  109. }
  110. return instance;
  111. }
  112. /**
  113. * Convenience method which calls clone and returns the cloned QH
  114. * @param c
  115. * @return
  116. */
  117. public static synchronized QueryHandler instanceOf(Context c) {
  118. return instanceOf().clone(c);
  119. }
  120. private DataPanel viewToBeNotified = null;
  121. private static Integer ROW_LIMIT = null;
  122. private volatile int limit = 0;
  123. private boolean runInBackground = false;
  124. private static PreparedStatement ivpps;
  125. private static PreparedStatement uvpps;
  126. private QueryHandler() {
  127. try {
  128. conn = DatabaseConnection.instanceOf();
  129. sqlConn = conn.getConnection();
  130. versionCheck();
  131. runFixes();
  132. createPs();
  133. } catch (Exception ex) {
  134. Log.Debug(ex);
  135. Popup.error(ex);
  136. }
  137. }
  138. private void versionCheck() {
  139. try {
  140. Statement versionCheck = sqlConn.createStatement();
  141. Log.Debug(this, "Checking database version..");
  142. ResultSet versionData = versionCheck.executeQuery("SELECT value FROM globalsettings WHERE cname = 'yabs_dbversion'");
  143. if (versionData.next()) {
  144. double dbversion = Double.valueOf(versionData.getString(1));
  145. Log.Debug(this, "Database version found: " + dbversion);
  146. if (dbversion >= Constants.DATABASE_MAX_VERSION.doubleValue()) {
  147. throw new UnsupportedOperationException("Database version is too high! Required min version: " + Constants.DATABASE_VERSION + " Required max version: " + Constants.DATABASE_MAX_VERSION);
  148. } else if (dbversion < Constants.DATABASE_VERSION.doubleValue()) {
  149. new DatabaseUpdater().updateFrom(dbversion);
  150. }
  151. } else {
  152. Log.Debug(this, "Database version info can not be found.");
  153. throw new UnsupportedOperationException("Database version cannot be validated! Required version: " + Constants.DATABASE_VERSION + "\n\n"
  154. + "To solve this issue, you maybe want to run Yabs with the parameter -finstall");
  155. }
  156. } catch (Exception ex) {
  157. Log.Debug(ex);
  158. Popup.error(ex);
  159. }
  160. }
  161. private void runFixes() {
  162. try {
  163. Statement runfixes = sqlConn.createStatement();
  164. //Issue #239////////////////////////////////////////////////////////
  165. runfixes.setMaxRows(1);
  166. ResultSet firstgroup = runfixes.executeQuery("SELECT groupsids, ids FROM groups ORDER BY ids ASC");
  167. if (firstgroup.next()) {
  168. int gids = firstgroup.getInt(1);
  169. if (gids != 0) {
  170. runfixes.execute("update groups set groupsids = 0 where ids = " + firstgroup.getInt(2));
  171. Log.Debug(this, "Corrected group 1 to fix Issue #239");
  172. }
  173. }
  174. ResultSet firstpgroup = runfixes.executeQuery("SELECT productgroupsids, ids FROM productgroups ORDER BY ids ASC");
  175. if (firstpgroup.next()) {
  176. int gids = firstpgroup.getInt(1);
  177. if (gids != 0) {
  178. runfixes.execute("update productgroups set productgroupsids = 0 where ids = " +firstpgroup.getInt(2));
  179. Log.Debug(this, "Corrected productgroup 1 to fix Issue #239");
  180. }
  181. }
  182. ResultSet firstaccount = runfixes.executeQuery("SELECT intparentaccount, ids FROM accounts ORDER BY ids ASC");
  183. if (firstaccount.next()) {
  184. int gids = firstaccount.getInt(1);
  185. if (gids != 0) {
  186. runfixes.execute("update accounts set intparentaccount = 0 where ids = " + firstaccount.getInt(2));
  187. Log.Debug(this, "Corrected account 1 to fix Issue #239");
  188. }
  189. }
  190. ////////////////////////////////////////////////////////////////////
  191. } catch (Exception ex) {
  192. Log.Debug(this,ex.getMessage().toString());
  193. Popup.error(ex);
  194. }
  195. }
  196. /**
  197. * Set the global row limit for select queries. 0 is unlimited.
  198. *
  199. * @param limit
  200. */
  201. public static synchronized void setRowLimit(int limit) {
  202. if (ROW_LIMIT == null || limit > ROW_LIMIT.intValue() || limit < ROW_LIMIT.intValue()) {
  203. Log.Debug(QueryHandler.class, "Setting global row limit to: " + limit);
  204. ROW_LIMIT = limit;
  205. }
  206. }
  207. /**
  208. * <b>Do not use this during 'normal' program operation.</b>
  209. *
  210. * @see QueryHandler#instanceOf() instead
  211. * @param c
  212. */
  213. public QueryHandler(DatabaseConnection c) {
  214. try {
  215. conn = c;
  216. sqlConn = conn.getConnection();
  217. } catch (Exception ex) {
  218. Log.Debug(ex);
  219. Popup.error(ex);
  220. }
  221. }
  222. protected void setLimit(int limit) {
  223. if (limit > this.limit || limit < this.limit) {
  224. Log.Debug(QueryHandler.class, "Setting row limit for this connection to: " + limit);
  225. this.limit = limit;
  226. }
  227. }
  228. /**
  229. * Builds a select query which selects all fields from all rows where the
  230. * fields match the given value.
  231. *
  232. * @param value
  233. * @param fields
  234. * @return A query String select ids, bla
  235. */
  236. public String buildQuery(Object value, String... fields) {
  237. return buildQuery(fields, fields, "cname", value, "OR");
  238. }
  239. /**
  240. * Builds a select query which selects ids from all rows where the fields
  241. * match the given value.
  242. *
  243. * @param value
  244. * @param fields
  245. * @return A query String select ids, bla
  246. */
  247. public String buildIdQuery(Object value, String... fields) {
  248. return buildQuery(new String[]{"ids"}, fields, "cname", value, "OR");
  249. }
  250. /**
  251. * selct ids, columns
  252. *
  253. * @param columns
  254. * @param conditionColumns
  255. * @param order
  256. * @param value
  257. * @param command
  258. * @return
  259. */
  260. public String buildQuery(String[] columns, String[] conditionColumns, String order, Object value, String command) {
  261. String cols = "";
  262. if (columns != null && columns.length > 0) {
  263. for (int i = 0; i < columns.length; i++) {
  264. String column = columns[i];
  265. cols += "," + column;
  266. }
  267. cols = cols.substring(1);
  268. } else {
  269. cols = "*";
  270. }
  271. String conds = "";
  272. if (conditionColumns != null && conditionColumns.length > 0) {
  273. for (int i = 0; i < conditionColumns.length; i++) {
  274. String string = conditionColumns[i];
  275. if (value instanceof String) {
  276. conds += " UPPER(" + string + ") LIKE '%" + String.valueOf(value).toUpperCase() + "%' " + command + " ";
  277. } else if (value instanceof Date) {
  278. conds += string + " = '" + DateConverter.getSQLDateString((Date) value) + "' " + command + " ";
  279. } else {
  280. conds += string + " = " + value + " " + command + " ";
  281. }
  282. }
  283. conds = " WHERE (" + conds.substring(0, conds.length() - 4) + ")";
  284. if (context.getGroupRestrictionSQLString() != null) {
  285. conds += " AND " + context.getGroupRestrictionSQLString();
  286. }
  287. if (context.getNoTrashSQLString() != null) {
  288. conds += " AND " + context.getNoTrashSQLString();
  289. }
  290. }
  291. String query = "SELECT ids," + cols + " FROM " + table + conds;
  292. if (order != null) {
  293. query += " ORDER BY " + order;
  294. }
  295. return query;
  296. }
  297. /**
  298. * Checks the uniqueness of a unique constraint Works only for columns with
  299. * equal data type
  300. *
  301. * @param constraint {"column1","column2"}
  302. * @param values {"value1",value2<any/>}
  303. * @return true if the key constraint is not existing yet
  304. */
  305. public boolean checkConstraint(String[] constraint, Object[] values) {
  306. for (int i = 0; i < values.length; i++) {
  307. Object object = values[i];
  308. if (!(object instanceof Number)) {
  309. values[i] = "'" + object.toString() + "'";
  310. }
  311. }
  312. Object[][] val = select(context.getDbIdentity() + ".ids", constraint, values, false);
  313. if (val != null && val.length > 0) {
  314. Log.Debug(this, "Uniqueness check failed!");
  315. return false;
  316. } else {
  317. return true;
  318. }
  319. }
  320. /**
  321. * This is a convenience bridge between views and unique constraint checks.
  322. * If the given objects is from type JTextField or LabeledTextField, the
  323. * TextFields background will flash red<br/> if the uniqueness check fails,
  324. * nothing will happen otherwise
  325. *
  326. * @param uniqueColumns to be separated with a comma
  327. * @param object An array of textfields
  328. * @return true if no uniqueness failure has been hidden
  329. */
  330. public boolean checkUniqueness(String uniqueColumns, JTextField[] object) {
  331. boolean returnv = true;
  332. for (int i = 0; i < object.length; i++) {
  333. if (!checkUniqueness(uniqueColumns.split(",")[i], (object[i]).getText())) {
  334. TextFieldUtils.blinkerRed(object[i]);
  335. returnv = false;
  336. }
  337. }
  338. return returnv;
  339. }
  340. /**
  341. * Returns a full column
  342. *
  343. * @param columnName column1
  344. * @param maximumRowCount If >0 , this is the row count limit
  345. * @param q
  346. * @return The column
  347. * @throws NodataFoundException
  348. */
  349. public Object[] getColumn(String columnName, int maximumRowCount, QueryCriteria2 q) throws NodataFoundException {
  350. return ArrayUtilities.ObjectToSingleColumnArray(getColumns(new String[]{columnName}, maximumRowCount, q));
  351. }
  352. /**
  353. * Select multiple columns
  354. *
  355. * @param columnNames column1, column2, column3...
  356. * @param maximumRowCount
  357. * @param q
  358. * @return
  359. * @throws NodataFoundException <i><b>Omits trashed datasets
  360. * implicitly</b></i>
  361. */
  362. public Object[][] getColumns(String[] columnNames, int maximumRowCount, QueryCriteria2 q) throws NodataFoundException {
  363. ReturnValue data = null;
  364. String columnName = "";
  365. for (int i = 0; i < columnNames.length; i++) {
  366. String string = columnNames[i];
  367. if (i < columnNames.length - 1) {
  368. columnName += string + ",";
  369. } else {
  370. columnName += string;
  371. }
  372. }
  373. if (maximumRowCount > 0) {
  374. data = freeSelectQuery("SELECT TOP(" + maximumRowCount + ") "
  375. + columnName + " FROM " + table + " " + context.getConditions(false) + " AND (" + q.getQuery() + ")", mpv5.usermanagement.MPSecurityManager.VIEW, null);
  376. } else {
  377. data = freeSelectQuery("SELECT "
  378. + columnName + " FROM " + table + " " + context.getConditions(false) + " AND (" + q.getQuery() + ")", mpv5.usermanagement.MPSecurityManager.VIEW, null);
  379. }
  380. if (data.getData().length == 0) {
  381. throw new NodataFoundException();
  382. } else {
  383. return data.getData();
  384. }
  385. }
  386. /**
  387. * Set the context for this connection, usually not used as the Context is
  388. * set on Clone
  389. *
  390. * @param context
  391. * @return
  392. */
  393. public QueryHandler setContext(Context context) {
  394. table = context.getDbIdentity();
  395. if (DatabaseConnection.getPrefix() != null && DatabaseConnection.getPrefix().equals("null")) {
  396. table = DatabaseConnection.getPrefix() + table;
  397. }
  398. this.context = context;
  399. return this;
  400. }
  401. /**
  402. * Select the row with this IDS
  403. *
  404. * @param id
  405. * @return
  406. * @throws NodataFoundException If no such row exists
  407. */
  408. protected ReturnValue select(int id) throws NodataFoundException {
  409. return select(id, true);
  410. }
  411. /**
  412. * Select the row with this IDS
  413. *
  414. * @param id
  415. * @param noConditions
  416. * @return
  417. * @throws NodataFoundException If no such row exists <i><b>Omits trashed
  418. * datasets implicitly</b></i>
  419. */
  420. protected ReturnValue select(int id, boolean noConditions) throws NodataFoundException {
  421. ReturnValue data;
  422. if (noConditions) {
  423. data = freeSelectQuery("SELECT * FROM " + table + " WHERE " + table + ".ids = " + id, mpv5.usermanagement.MPSecurityManager.VIEW, null);
  424. } else {
  425. data = freeSelectQuery("SELECT * FROM " + table + " WHERE " + table + ".ids = " + id + " AND " + context.getConditions(false).substring(6, context.getConditions(false).length()), mpv5.usermanagement.MPSecurityManager.VIEW, null);
  426. }
  427. if (data.getData().length == 0) {
  428. throw new NodataFoundException(context, id);
  429. } else {
  430. return data;
  431. }
  432. }
  433. /**
  434. * Selects one or more columns from the current {@link Context}- No
  435. * condition checking!
  436. *
  437. * @param columns column1, column2, column3...
  438. * @return
  439. * @throws NodataFoundException
  440. */
  441. public ReturnValue freeSelect(String columns) throws NodataFoundException {
  442. ReturnValue data = freeSelectQuery("SELECT " + columns + " FROM " + table, mpv5.usermanagement.MPSecurityManager.VIEW, null);
  443. if (data.getData().length == 0) {
  444. throw new NodataFoundException(context);
  445. } else {
  446. return data;
  447. }
  448. }
  449. /**
  450. * This is a convenience method to retrieve data such as
  451. * <code>select("*", criterias.getKeys(), criterias.getValues())<code/>
  452. *
  453. * @param columns
  454. * @param criterias
  455. * @return
  456. * @throws NodataFoundException
  457. */
  458. public Object[][] select(String columns, QueryCriteria criterias) throws NodataFoundException {
  459. if (criterias.getKeys().length > 0) {
  460. return select(columns, criterias.getKeys(), criterias.getValues(), criterias.getIncludeInvisible());
  461. } else {
  462. return select(columns, criterias.getIncludeInvisible());
  463. }
  464. }
  465. /**
  466. * This is a convenience method to retrieve data such as
  467. * <code>select("*", criterias.getKeys(), criterias.getValues())<code/>
  468. *
  469. * @param columns
  470. * @return
  471. * @throws NodataFoundException
  472. */
  473. public Object[][] select(String columns, boolean withDeleted) throws NodataFoundException {
  474. return select(columns, new String[0], new Object[0], withDeleted);
  475. }
  476. /**
  477. * 0
  478. * Select data from a timeframe
  479. *
  480. * @param columns column1, column2, column3...
  481. * @param criterias
  482. * @param time
  483. * @param timeCol The column containing the date
  484. * @return
  485. * @throws NodataFoundException <i><b>Omits trashed datasets
  486. * implicitly</b></i>
  487. */
  488. public Object[][] select(String columns, QueryCriteria criterias, vTimeframe time, String timeCol) throws NodataFoundException {
  489. String dateCriterium = table + "." + timeCol + " >= '" + DateConverter.getSQLDateString(time.getStart()) + "' AND " + table + "." + timeCol + " <= '" + DateConverter.getSQLDateString(time.getEnd()) + "'";
  490. String query = "SELECT " + columns + " FROM " + table + " " + context.getReferences() + (criterias.getKeys().length > 0 ? " WHERE " : "");
  491. for (int i = 0; i < criterias.getKeys().length; i++) {
  492. Object object = criterias.getValues()[i];
  493. String column = criterias.getKeys()[i];
  494. query += table + "." + column + "=" + String.valueOf(object);
  495. if ((i + 1) != criterias.getValues().length) {
  496. query += " AND ";
  497. }
  498. }
  499. if (criterias.getKeys().length > 0 && !query.endsWith("AND ")) {
  500. query += " AND ";
  501. }
  502. query += context.getConditions(false).substring(6, context.getConditions(false).length()) + " AND ";
  503. query += dateCriterium;
  504. query += criterias.getOrder();
  505. ReturnValue p = freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null);
  506. if (p.hasData()) {
  507. return p.getData();
  508. } else {
  509. throw new NodataFoundException(context);
  510. }
  511. }
  512. /**
  513. *
  514. * @param columns column1, column2, column3...
  515. * @param criterias
  516. * @param time
  517. * @param timeCol
  518. * @return
  519. * @throws NodataFoundException
  520. */
  521. public ReturnValue select(String columns, QueryCriteria2 criterias, vTimeframe time, String timeCol) throws NodataFoundException {
  522. String dateCriterium = table + "." + timeCol + " >= '" + DateConverter.getSQLDateString(time.getStart()) + "' AND " + table + "." + timeCol + " <= '" + DateConverter.getSQLDateString(time.getEnd()) + "'";
  523. String query = "SELECT " + columns + " FROM " + table + " " + context.getReferences() + " WHERE ";
  524. if (criterias.getQuery().length() > 6) {
  525. query += criterias.getQuery() + " AND ";
  526. }
  527. query += context.getConditions(criterias.getIncludeInvisible()).substring(6, context.getConditions(criterias.getIncludeInvisible()).length()) + " AND ";
  528. query += dateCriterium;
  529. query += criterias.getOrder();
  530. ReturnValue p = freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null);
  531. if (p.hasData()) {
  532. return p;
  533. } else {
  534. throw new NodataFoundException(context);
  535. }
  536. }
  537. /**
  538. *
  539. * @param columns column1, column2, column3...
  540. * @param criterias
  541. * @return
  542. * @throws NodataFoundException
  543. */
  544. public ReturnValue select(String columns, QueryCriteria2 criterias) throws NodataFoundException {
  545. String query = "SELECT " + columns + " FROM " + table + " " + context.getReferences() + " WHERE ";
  546. if (criterias.getQuery().length() > 6) {
  547. query += criterias.getQuery() + " AND ";
  548. }
  549. query += context.getConditions(criterias.getIncludeInvisible()).substring(6, context.getConditions(criterias.getIncludeInvisible()).length());
  550. query += criterias.getOrder();
  551. ReturnValue p = freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null);
  552. if (p.hasData()) {
  553. return p;
  554. } else {
  555. throw new NodataFoundException(context);
  556. }
  557. }
  558. /**
  559. *
  560. * @param columns column1, column2, column3...
  561. * @param criterias
  562. * @return
  563. * @throws NodataFoundException
  564. */
  565. public List<Integer> selectIds(QueryCriteria2 criterias) throws NodataFoundException {
  566. ReturnValue data = select("ids", criterias);
  567. if (data.getData().length == 0) {
  568. throw new NodataFoundException(context);
  569. } else {
  570. List<Integer> l = new ArrayList<Integer>();
  571. List<Object[]> d = data.getDataAsList();
  572. try {
  573. for (int i = 0; i < d.size(); i++) {
  574. l.add(Integer.valueOf(d.get(i)[0].toString()));
  575. }
  576. } catch (Exception exception) {
  577. Log.Debug(exception);
  578. } finally {
  579. return l;
  580. }
  581. }
  582. }
  583. /**
  584. *
  585. * @param columns column1, column2, column3...
  586. * @param criterias
  587. * @return
  588. * @throws NodataFoundException
  589. */
  590. public List<Integer> selectIds(QueryCriteria criterias) throws NodataFoundException {
  591. Object[][] data = select("ids", criterias);
  592. if (data.length == 0) {
  593. throw new NodataFoundException(context);
  594. } else {
  595. List<Integer> l = new ArrayList<Integer>();
  596. try {
  597. for (int i = 0; i < data.length; i++) {
  598. l.add(Integer.valueOf(data[i][0].toString()));
  599. }
  600. } catch (Exception exception) {
  601. Log.Debug(exception);
  602. } finally {
  603. return l;
  604. }
  605. }
  606. }
  607. /**
  608. * Requires 'dateadded' column
  609. *
  610. * @param columns column1, column2, column3...
  611. * @param criterias
  612. * @param time
  613. * @return
  614. * @throws NodataFoundException
  615. */
  616. public Object[][] select(String columns, QueryCriteria criterias, vTimeframe time) throws NodataFoundException {
  617. return select(columns, criterias, time, "dateadded");
  618. }
  619. /**
  620. * Requires 'dateadded' column
  621. *
  622. * @param columns
  623. * @param criterias
  624. * @param time
  625. * @return
  626. * @throws NodataFoundException
  627. */
  628. public ReturnValue select(String columns, QueryCriteria2 criterias, vTimeframe time) throws NodataFoundException {
  629. return select(columns, criterias, time, "dateadded");
  630. }
  631. /**
  632. * Convenience method to retrieve * from where the criterias match
  633. *
  634. * @param criterias
  635. * @return
  636. * @throws mpv5.db.common.NodataFoundException
  637. */
  638. public ReturnValue select(QueryCriteria criterias) throws NodataFoundException {
  639. String query = "SELECT * FROM " + table + " " + context.getReferences() + " WHERE ";
  640. for (int i = 0; i < criterias.getValues().length; i++) {
  641. Object object = criterias.getValues()[i];
  642. String column = criterias.getKeys()[i];
  643. query += column + "=" + String.valueOf(object);
  644. if ((i + 1) != criterias.getValues().length) {
  645. query += " AND ";
  646. } else {
  647. query += " AND " + context.getConditions(criterias.getIncludeInvisible()).substring(6, context.getConditions(criterias.getIncludeInvisible()).length());
  648. }
  649. }
  650. query += criterias.getOrder();
  651. ReturnValue data = freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null);
  652. if (!data.hasData()) {
  653. throw new NodataFoundException(context);
  654. } else {
  655. return data;
  656. }
  657. }
  658. /**
  659. * This is a convenience method to retrieve data such as "SELECT * FROM
  660. * table"
  661. *
  662. * @return All rows in the current context
  663. * @throws NodataFoundException
  664. */
  665. public ReturnValue select(boolean includeDeleted) throws NodataFoundException {
  666. ReturnValue data = freeSelectQuery("SELECT * FROM " + table + " " + context.getConditions(includeDeleted), mpv5.usermanagement.MPSecurityManager.VIEW, null);
  667. if (data.getData().length == 0) {
  668. throw new NodataFoundException(context);
  669. } else {
  670. return data;
  671. }
  672. }
  673. /**
  674. * This is a convenience method to retrieve data such as "SELECT ids FROM
  675. * table"
  676. *
  677. * @return All rows in the current context
  678. * @throws NodataFoundException
  679. */
  680. public List<Integer> selectIds(boolean includeDeleted) throws NodataFoundException {
  681. ReturnValue data = freeSelectQuery("SELECT ids FROM " + table + " " + context.getConditions(includeDeleted), mpv5.usermanagement.MPSecurityManager.VIEW, null);
  682. if (data.getData().length == 0) {
  683. throw new NodataFoundException(context);
  684. } else {
  685. List<Integer> l = new ArrayList<Integer>();
  686. List<Object[]> d = data.getDataAsList();
  687. try {
  688. for (int i = 0; i < d.size(); i++) {
  689. l.add(Integer.valueOf(d.get(i)[0].toString()));
  690. }
  691. } catch (Exception exception) {
  692. Log.Debug(exception);
  693. } finally {
  694. return l;
  695. }
  696. }
  697. }
  698. /**
  699. *
  700. * @param columns If null, the column specified with "needle" is returned
  701. * @param needle
  702. * @param value
  703. * @param exactMatch
  704. * @return <i><b>Omits trashed datasets implicitly</b></i>
  705. */
  706. public Object[] getValuesFor(String[] columns, String needle, String value, boolean exactMatch) {
  707. String cols = needle;
  708. if (columns != null) {
  709. cols = "";
  710. for (int i = 0; i < columns.length; i++) {
  711. String string = columns[i];
  712. cols += string + ",";
  713. }
  714. cols = cols.substring(0, cols.length() - 1);
  715. }
  716. String f = " = '";
  717. String g = "'";
  718. if (!exactMatch) {
  719. f = " LIKE '%";
  720. g = "%'";
  721. }
  722. if (context != null) {
  723. if (value == null) {
  724. return ArrayUtilities.ObjectToSingleColumnArray(freeSelectQuery("SELECT " + cols + " FROM " + table + " " + context.getReferences() + " WHERE " + context.getConditions(false).substring(6, context.getConditions(false).length()), mpv5.usermanagement.MPSecurityManager.VIEW, null).getData());
  725. } else {
  726. return ArrayUtilities.ObjectToSingleColumnArray(freeSelectQuery("SELECT " + cols + " FROM " + table + " " + context.getReferences() + " WHERE " + needle + f + value + g + " AND " + context.getConditions(false).substring(6, context.getConditions(false).length()), mpv5.usermanagement.MPSecurityManager.VIEW, null).getData());
  727. }
  728. } else if (value == null) {
  729. return ArrayUtilities.ObjectToSingleColumnArray(freeSelectQuery("SELECT " + cols + " FROM " + table + " " + context.getReferences(), mpv5.usermanagement.MPSecurityManager.VIEW, null).getData());
  730. } else {
  731. return ArrayUtilities.ObjectToSingleColumnArray(freeSelectQuery("SELECT " + cols + " FROM " + table + " " + context.getReferences() + " WHERE " + needle + f + value + g, mpv5.usermanagement.MPSecurityManager.VIEW, null).getData());
  732. }
  733. }
  734. /**
  735. *
  736. * @param what column1, column2, column3...
  737. * @param where
  738. * @param datecolumn
  739. * @param zeitraum
  740. * @return
  741. */
  742. public Object[][] select(String what, String[] where, String datecolumn, vTimeframe zeitraum) {
  743. String dateCriterium = datecolumn + " >= '" + DateConverter.getSQLDateString(zeitraum.getStart()) + "' AND " + datecolumn + " <= '" + DateConverter.getSQLDateString(zeitraum.getEnd()) + "'";
  744. String query;
  745. if (where != null) {
  746. query = "SELECT " + what + " FROM " + table + " WHERE " + where[0] + " = " + where[2] + where[1] + where[2] + " AND " + dateCriterium;
  747. } else {
  748. query = "SELECT " + what + " FROM " + table + " WHERE " + dateCriterium;
  749. }
  750. return freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null).getData();
  751. }
  752. /**
  753. *
  754. * @param what column1, column2, column3...
  755. * @param where
  756. * @param zeitraum
  757. * @param additionalCondition
  758. * @return
  759. */
  760. public ArrayList<Double> selectYearlySums(String what, String[] where, vTimeframe zeitraum, String additionalCondition) {
  761. Date temdate = zeitraum.getStart();
  762. ArrayList<Double> values = new ArrayList<java.lang.Double>();
  763. String query;
  764. do {
  765. String str = "AND datum BETWEEN '" + DateConverter.getSQLDateString(temdate) + "' AND '" + DateConverter.getSQLDateString(DateConverter.addYear(temdate)) + "'";
  766. if (where != null) {
  767. query = "SELECT SUM(" + what + ") FROM " + table + " WHERE " + where[0] + " = " + where[2] + where[1] + where[2] + " " + " " + str + " " + additionalCondition;
  768. } else {
  769. query = "SELECT SUM(" + what + ") FROM " + table + " " + str + " " + additionalCondition;
  770. }
  771. Object[][] o = freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null).getData();
  772. if (o != null && o[0][0] != null && !o[0][0].equals("null")) {
  773. values.add(Double.valueOf(String.valueOf(o[0][0])));
  774. } else {
  775. values.add(0d);
  776. }
  777. temdate = DateConverter.addMonth(temdate);
  778. } while (temdate.before(zeitraum.getEnd()));
  779. return values;
  780. }
  781. /**
  782. *
  783. * @param what column1, column2, column3...
  784. * @param where
  785. * @param zeitraum
  786. * @param additionalCondition
  787. * @return
  788. */
  789. public ArrayList<Double> selectMonthlySums(String what, String[] where, vTimeframe zeitraum, String additionalCondition) {
  790. Date temdate = zeitraum.getStart();
  791. ArrayList<Double> values = new ArrayList<java.lang.Double>();
  792. String query;
  793. do {
  794. String str = "AND datum BETWEEN '" + DateConverter.getSQLDateString(temdate) + "' AND '" + DateConverter.getSQLDateString(DateConverter.addMonth(temdate)) + "'";
  795. if (where != null) {
  796. query = "SELECT SUM(" + what + ") FROM " + table + " WHERE " + where[0] + " = " + where[2] + where[1] + where[2] + " " + " " + str + " " + additionalCondition;
  797. } else {
  798. query = "SELECT SUM(" + what + ") FROM " + table + " " + str + " " + additionalCondition;
  799. }
  800. Object[][] o = freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null).getData();
  801. if (o != null && o[0][0] != null && !o[0][0].equals("null")) {
  802. Log.Debug(this, "Summe: " + o[0][0]);
  803. values.add(Double.valueOf(String.valueOf(o[0][0])));
  804. } else {
  805. Log.Debug(this, "Summe: " + 0);
  806. values.add(0d);
  807. }
  808. temdate = DateConverter.addMonth(temdate);
  809. } while (temdate.before(zeitraum.getEnd()));
  810. return values;
  811. }
  812. /**
  813. *
  814. * @param what column1, column2, column3...
  815. * @param where
  816. * @param leftJoinTable
  817. * @param leftJoinKey
  818. * @param order
  819. * @param like
  820. * @return
  821. */
  822. public Object[][] select(String what, String[] where, String leftJoinTable, String leftJoinKey, String order, Boolean like) {
  823. // start();
  824. String query;
  825. String l1 = "";
  826. String l2 = "";
  827. String k = " = ";
  828. String j = "";
  829. String wher = "";
  830. java.util.Date date;
  831. if (like != null) {
  832. if (like) {
  833. if (where != null && where[0].endsWith("datum")) {
  834. k = " BETWEEN ";
  835. date = DateConverter.getDate(where[1]);
  836. where[1] = "'" + DateConverter.getSQLDateString(date) + "'" + " AND " + "'" + DateConverter.getSQLDateString(DateConverter.addMonth(date)) + "'";
  837. where[2] = " ";
  838. } else {
  839. l1 = "%";
  840. l2 = "%";
  841. k = " LIKE ";
  842. }
  843. }
  844. }
  845. if (where != null) {
  846. query = "SELECT " + what + " FROM " + table
  847. + " LEFT OUTER JOIN " + leftJoinTable + " ON " + table + "." + leftJoinKey + " = " + leftJoinTable + ".ids"
  848. + " WHERE " + table + "." + where[0] + " " + k + " " + where[2] + l1 + where[1] + l2 + where[2] + " ORDER BY " + table + "." + order;
  849. } else {
  850. query = "SELECT " + what + " FROM " + table
  851. + " LEFT OUTER JOIN " + leftJoinTable + " ON "
  852. + table + "." + leftJoinKey + " = " + leftJoinTable + ".ids "
  853. + " ORDER BY " + table + "." + order;
  854. }
  855. return freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null).getData();
  856. }
  857. /**
  858. * Count rows in a time frame
  859. *
  860. * @param date1
  861. * @param date2
  862. * @return
  863. */
  864. public int selectCountBetween(java.util.Date date1, java.util.Date date2) throws SQLException {
  865. return selectCount("dateadded", "BETWEEN '" + DateConverter.getSQLDateString(date1) + "' AND '" + DateConverter.getSQLDateString(date2) + "'");
  866. }
  867. /**
  868. * Set the table for the current connection. Usually only used inside the
  869. * <code>Clone</code> method of {@link QueryHandler}
  870. *
  871. * @param newTable
  872. */
  873. protected void setTable(String newTable) {
  874. this.table = newTable;
  875. if (DatabaseConnection.getPrefix() != null && DatabaseConnection.getPrefix().equals("null")) {
  876. this.table = DatabaseConnection.getPrefix() + table;
  877. }
  878. }
  879. /**
  880. * Sets the table and the context for the current connection. Don't use
  881. * this.
  882. *
  883. * @param newTable
  884. */
  885. public void setTable2(String newTable) {
  886. this.table = newTable;
  887. if (DatabaseConnection.getPrefix() != null && DatabaseConnection.getPrefix().equals("null")) {
  888. this.table = DatabaseConnection.getPrefix() + table;
  889. }
  890. this.context = Context.getMatchingContext(newTable);
  891. if (this.context == null) {
  892. this.context = Context.DEFAULT;
  893. this.context.setDbIdentity(newTable);
  894. }
  895. }
  896. /**
  897. * The current table name as specified by the current {@link Context}, or {@link #setTable(java.lang.String)
  898. * }, {@link #setTable2(java.lang.String) }
  899. *
  900. * @return The current table name
  901. */
  902. public String getTable() {
  903. return table;
  904. }
  905. /**
  906. * Set a wait-cursor during database transactions (if not cloned for silent
  907. * transactions)
  908. *
  909. * @param main A frame window
  910. */
  911. public static void setWaitCursorFor(JFrame main) {
  912. comp = main;
  913. }
  914. /**
  915. * Set a progressbar during database transactions
  916. *
  917. * @param progressBar
  918. */
  919. public static void setProgressbar(JProgressBar progressBar) {
  920. progressbar = progressBar;
  921. }
  922. /**
  923. * This will flush the table of the current context, be careful! This should
  924. * never be triggered by a user, the user right will not be checked!
  925. *
  926. * @param dbIdentity
  927. */
  928. public void truncate(String dbIdentity) {
  929. freeQuery(table, MPSecurityManager.SYSTEM_RIGHT, "Truncating table: " + table);
  930. }
  931. /**
  932. * Checks the uniqueness of the data
  933. *
  934. * @param vals
  935. * @param uniquecols
  936. * @return
  937. */
  938. public boolean checkUniqueness(QueryData vals, int[] uniquecols) {
  939. String[] values = vals.getKeys();
  940. if (uniquecols != null) {
  941. for (int i = 0; i < uniquecols.length; i++) {
  942. int j = uniquecols[i];
  943. Object[][] val = select(values[j], new String[]{values[j], vals.getValue(values[j]).toString(), vals.getValue(values[j]).getWrapper()});
  944. if (val != null && val.length > 0) {
  945. mpv5.YabsViewProxy.instance().addMessage(Messages.VALUE_ALREADY_EXISTS + vals.getValue(values[j]).toString(), Color.RED);
  946. return false;
  947. }
  948. }
  949. }
  950. return true;
  951. }
  952. /**
  953. * Checks the uniqueness of STRING data
  954. *
  955. * @param column
  956. * @param value
  957. * @return
  958. */
  959. public boolean checkUniqueness(String column, String value) {
  960. QueryData t = new QueryData();
  961. t.add(column, value);
  962. return checkUniqueness(t, new int[]{0});
  963. }
  964. private static volatile int RUNNING_JOBS = 0;
  965. private static Thread JOB_WATCHDOG;
  966. /**
  967. * Invoked after running a database query (usually done automatically)
  968. */
  969. protected synchronized void stop() {
  970. if (!runInBackground) {
  971. Runnable runnable = new Runnable() {
  972. @Override
  973. public void run() {
  974. try {//Avoid Cursor flickering
  975. Thread.sleep(10);
  976. } catch (InterruptedException ex) {
  977. mpv5.logging.Log.Debug(ex);//Logger.getLogger(QueryHandler.class.getName()).log(Level.SEVERE, null, ex);
  978. }
  979. if (RUNNING_JOBS <= 1) {
  980. comp.setCursor(new Cursor(Cursor.DEFAULT_CURSOR));
  981. progressbar.setValue(0);
  982. progressbar.setIndeterminate(false);
  983. }
  984. RUNNING_JOBS--;
  985. }
  986. };
  987. SwingUtilities.invokeLater(runnable);
  988. }
  989. }
  990. private synchronized void start() {
  991. if (JOB_WATCHDOG == null) {
  992. JOB_WATCHDOG = new Thread(new Watchdog());
  993. JOB_WATCHDOG.start();
  994. }
  995. if (!runInBackground) {
  996. RUNNING_JOBS++;
  997. if (RUNNING_JOBS > 5) {
  998. comp.setCursor(new Cursor(Cursor.WAIT_CURSOR));
  999. }
  1000. progressbar.setMaximum(RUNNING_JOBS);
  1001. }
  1002. }
  1003. /**
  1004. * Returns exactly one value from a column
  1005. *
  1006. * @param columnName The column where to take the result from
  1007. * @param compareColumn The column to compare
  1008. * @param compareValue The value to compare to
  1009. * @return A value
  1010. * @throws NodataFoundException
  1011. */
  1012. public Object getValue(String columnName, String compareColumn, Object compareValue) throws NodataFoundException {
  1013. String quote = "";
  1014. if (compareValue instanceof String) {
  1015. quote = "'";
  1016. } else if (compareValue instanceof Date) {
  1017. quote = "'";
  1018. compareValue = DateConverter.getSQLDateString((Date) compareValue);
  1019. }
  1020. return selectLast(columnName, new String[]{compareColumn, compareValue.toString(), quote})[0];
  1021. }
  1022. /**
  1023. * Returns map view of the found {@link DatabaseObject} with the given ID in
  1024. * the current {@link Context}
  1025. *
  1026. * @param id
  1027. * @return A HashMap
  1028. * @throws NodataFoundException If no Object with the given ID was found in
  1029. * the current Context
  1030. */
  1031. public Map<String, String> getValuesFor(int id) throws NodataFoundException {
  1032. ReturnValue rv = select(id);
  1033. String[] cols = rv.getColumnnames();
  1034. Object[][] data = rv.getData();
  1035. HashMap<String, String> map = new HashMap<String, String>(data[0].length);
  1036. for (int i = 0; i < data[0].length; i++) {
  1037. map.put(cols[i], String.valueOf(data[0][i]));
  1038. }
  1039. return map;
  1040. }
  1041. /**
  1042. * This string is used to replace backslashes in sql queries (if escaping is
  1043. * enabled)
  1044. */
  1045. public static String BACKSLASH_REPLACEMENT_STRING = "<removedbackslash>";
  1046. private synchronized String escapeBackslashes(String query) {
  1047. return query.replace("\\", BACKSLASH_REPLACEMENT_STRING);
  1048. }
  1049. private synchronized String rescapeBackslashes(String query) {
  1050. return query.replace(BACKSLASH_REPLACEMENT_STRING, "\\");
  1051. }
  1052. /**
  1053. * Checks if any data which would match the given criteria is existing in
  1054. * the database
  1055. *
  1056. * @param qc
  1057. * @return true if matching data was found
  1058. */
  1059. public boolean checkExistance(QueryCriteria2 qc) {
  1060. try {
  1061. return select("ids", qc, new vTimeframe(new Date(0), new Date())).hasData();
  1062. } catch (NodataFoundException ex) {
  1063. return false;
  1064. }
  1065. }
  1066. private synchronized byte[] blobToByteArray(final Reader characterStream) throws SQLException, IOException {
  1067. //byte[] is for BLOB data (or char[]?)
  1068. ByteArrayOutputStream baos = new ByteArrayOutputStream();
  1069. Writer writer = new OutputStreamWriter(baos, "utf-8");
  1070. char[] buffer = new char[4096];
  1071. for (int count = 0; (count = characterStream.read(buffer)) != -1;) {
  1072. writer.write(buffer, 0, count);
  1073. }
  1074. writer.close();
  1075. return baos.toByteArray();
  1076. }
  1077. private static String ivpquery = "INSERT INTO " + Context.getValueProperties().getDbIdentity()
  1078. + "(value, cname, classname, objectids, contextids, intaddedby, dateadded, groupsids )"
  1079. + " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
  1080. private static String uvpquery = "UPDATE " + Context.getValueProperties().getDbIdentity() + " SET "
  1081. + "value = ?, "
  1082. + "cname= ?, "
  1083. + "classname= ?, "
  1084. + "objectids= ?, "
  1085. + "contextids= ?, "
  1086. + "intaddedby= ?, "
  1087. + "dateadded= ?, "
  1088. + "groupsids= ? "
  1089. + "WHERE " + Context.getValueProperties().getDbIdentity() + ".ids = ?";
  1090. private void createPs() {
  1091. try {
  1092. ivpps = sqlConn.prepareStatement(ivpquery, PreparedStatement.RETURN_GENERATED_KEYS);
  1093. uvpps = sqlConn.prepareStatement(uvpquery, PreparedStatement.RETURN_GENERATED_KEYS);
  1094. } catch (SQLException ex) {
  1095. Log.Debug(this, ex.getMessage());
  1096. }
  1097. }
  1098. class Watchdog implements Runnable {
  1099. @Override
  1100. @SuppressWarnings("SleepWhileInLoop")
  1101. public void run() {
  1102. int oldValue = 0;
  1103. while (true) {
  1104. if (RUNNING_JOBS != oldValue) {
  1105. progressbar.setValue(RUNNING_JOBS);
  1106. oldValue = RUNNING_JOBS;
  1107. }
  1108. try {
  1109. Thread.sleep(33);
  1110. } catch (InterruptedException ex) {
  1111. mpv5.logging.Log.Debug(ex);
  1112. }
  1113. }
  1114. }
  1115. }
  1116. /**
  1117. * Count the rows of the current table
  1118. *
  1119. * @return
  1120. * @throws SQLException
  1121. */
  1122. public Integer getCount() throws SQLException {
  1123. int i = selectCount(null, null);
  1124. i = (i < 0) ? -i : i;
  1125. return i;
  1126. }
  1127. /**
  1128. * Insert values to db
  1129. *
  1130. * @param what : {set, value, "'"} this.insert("name,wert", "'Sprache
  1131. * (Waehrung, z.B. Schweiz: de_CH' ,'de_DE'");
  1132. * @param jobmessage The message to be displayed after a successful run
  1133. * @return id of inserted row
  1134. */
  1135. public int insert(QueryData what, String jobmessage) {
  1136. String query = query = "INSERT INTO " + table + " (" + what.getKeysString() + " ) VALUES (" + what.getValuesString() + ") ";
  1137. return freeUpdateQuery(query, mpv5.usermanagement.MPSecurityManager.CREATE_OR_DELETE, jobmessage).getId();
  1138. }
  1139. /**
  1140. * Does an insert
  1141. *
  1142. * @param blobData [columnName, blobData]
  1143. * @param data
  1144. * @param jobmessage
  1145. * @return The id of the inserted row
  1146. */
  1147. public int insertValueProperty(InputStream blobData, QueryData data, String jobmessage) {
  1148. if (ivpps == null) {
  1149. createPs();
  1150. }
  1151. ResultSet keys;
  1152. int id = -1;
  1153. try {
  1154. start();
  1155. ivpps.setBlob(1, blobData);
  1156. ivpps.setString(2, data.getValue("cname").toString());
  1157. ivpps.setString(3, data.getValue("classname").toString());
  1158. ivpps.setInt(4, Integer.valueOf(data.getValue("objectids").toString()));
  1159. ivpps.setInt(5, Integer.valueOf(data.getValue("contextids").toString()));
  1160. ivpps.setInt(6, mpv5.db.objects.User.getCurrentUser().getID());
  1161. ivpps.setDate(7, new java.sql.Date(new Date().getTime()));
  1162. ivpps.setInt(8, Integer.valueOf(data.getValue("groupsids").toString()));
  1163. ivpps.execute();
  1164. if (!sqlConn.getAutoCommit()) {
  1165. sqlConn.commit();
  1166. }
  1167. try {
  1168. keys = ivpps.getGeneratedKeys();
  1169. if (keys != null && keys.next()) {
  1170. id = keys.getInt(1);
  1171. }
  1172. } catch (SQLException sQLException) {
  1173. Log.Debug(sQLException);
  1174. }
  1175. } catch (Exception ex) {
  1176. Log.Debug(this, "Datenbankfehler: " + ivpquery);
  1177. Log.Debug(this, ex);
  1178. Popup.error(ex);
  1179. jobmessage = Messages.ERROR_OCCURED.toString();
  1180. } finally {
  1181. stop();
  1182. }
  1183. if (viewToBeNotified != null) {
  1184. viewToBeNotified.refresh();
  1185. }
  1186. if (jobmessage != null) {
  1187. mpv5.YabsViewProxy.instance().addMessage(jobmessage);
  1188. }
  1189. return id;
  1190. }
  1191. /**
  1192. * Does an insert
  1193. *
  1194. * @param ids
  1195. * @param blobData [columnName, blobData]
  1196. * @param data
  1197. * @param jobmessage
  1198. */
  1199. public void updateValueProperty(int ids, InputStream blobData, QueryData data, String jobmessage) {
  1200. if (uvpps == null) {
  1201. createPs();
  1202. }
  1203. try {
  1204. start();
  1205. uvpps.setBlob(1, blobData);
  1206. uvpps.setString(2, data.getValue("cname").toString());
  1207. uvpps.setString(3, data.getValue("classname").toString());
  1208. uvpps.setInt(4, Integer.valueOf(data.getValue("objectids").toString()));
  1209. uvpps.setInt(5, Integer.valueOf(data.getValue("contextids").toString()));
  1210. uvpps.setInt(6, mpv5.db.objects.User.getCurrentUser().getID());
  1211. uvpps.setDate(7, new java.sql.Date(new Date().getTime()));
  1212. uvpps.setInt(8, Integer.valueOf(data.getValue("groupsids").toString()));
  1213. uvpps.setInt(9, ids);
  1214. uvpps.execute();
  1215. if (!sqlConn.getAutoCommit()) {
  1216. sqlConn.commit();
  1217. }
  1218. } catch (Exception ex) {
  1219. Log.Debug(this, "Datenbankfehler: " + uvpquery);
  1220. Log.Debug(this, ex);
  1221. Popup.error(ex);
  1222. jobmessage = Messages.ERROR_OCCURED.toString();
  1223. } finally {
  1224. stop();
  1225. }
  1226. if (viewToBeNotified != null) {
  1227. viewToBeNotified.refresh();
  1228. }
  1229. if (jobmessage != null) {
  1230. mpv5.YabsViewProxy.instance().addMessage(jobmessage);
  1231. }
  1232. }
  1233. /**
  1234. * This is a special insert method for the History feature
  1235. *
  1236. * @param message
  1237. * @param username
  1238. * @param dbidentity
  1239. * @param item
  1240. * @param groupid
  1241. */
  1242. public synchronized void insertHistoryItem(String message, String username, String dbidentity, int item, int groupid) {
  1243. try {
  1244. if (psHistory == null) {
  1245. try {
  1246. String query = "INSERT INTO " + Context.getHistory().getDbIdentity() + " (cname, username, dbidentity, intitem, groupsids, dateadded) VALUES (?, ?, ?, ?, ?, ?)";
  1247. psHistory = sqlConn.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
  1248. } catch (SQLException ex) {
  1249. mpv5.logging.Log.Debug(ex);//Logger.getLogger(QueryHandler.class.getName()).log(Level.SEVERE, null, ex);
  1250. }
  1251. }
  1252. psHistory.setString(1, message);
  1253. psHistory.setString(2, username);
  1254. psHistory.setString(3, dbidentity);
  1255. psHistory.setInt(4, item);
  1256. psHistory.setInt(5, groupid);
  1257. psHistory.setDate(6, new java.sql.Date(new java.util.Date().getTime()));
  1258. psHistory.execute();
  1259. } catch (SQLException ex) {
  1260. mpv5.logging.Log.Debug(ex);//Logger.getLogger(QueryHandler.class.getName()).log(Level.SEVERE, null, ex);
  1261. }
  1262. }
  1263. private static PreparedStatement psHistory;
  1264. /**
  1265. * This is a special insert method for the Lock feature
  1266. *
  1267. * @param context
  1268. * @param id
  1269. * @param user
  1270. * @return
  1271. * @throws UnableToLockException
  1272. */
  1273. protected synchronized boolean insertLock(Context context, int id, User user) throws UnableToLockException {
  1274. try {
  1275. if (psLock == null) {
  1276. try {
  1277. String query = "INSERT INTO " + Context.getLock().getDbIdentity() + " (cname, rowid, usersids) VALUES (?, ?, ?)";
  1278. psLock = sqlConn.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
  1279. } catch (SQLException ex) {
  1280. mpv5.logging.Log.Debug(ex);//Logger.getLogger(QueryHandler.class.getName()).log(Level.SEVERE, null, ex);
  1281. }
  1282. }
  1283. psLock.setString(1, context.getDbIdentity());
  1284. psLock.setInt(2, id);
  1285. psLock.setInt(3, user.__getIDS());
  1286. return psLock.execute();
  1287. } catch (SQLException ex) {
  1288. throw new UnableToLockException(context, id, user);
  1289. }
  1290. }
  1291. private static PreparedStatement psLock;
  1292. protected void removeLock(Context context, int id, User user) {
  1293. try {
  1294. if (psUnLock == null) {
  1295. try {
  1296. String query = "DELETE FROM " + Context.getLock().getDbIdentity() + " WHERE cname = ? AND rowid = ? AND usersids = ?";
  1297. psUnLock = sqlConn.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
  1298. } catch (SQLException ex) {
  1299. mpv5.logging.Log.Debug(ex);//Logger.getLogger(QueryHandler.class.getName()).log(Level.SEVERE, null, ex);
  1300. }
  1301. }
  1302. psUnLock.setString(1, context.getDbIdentity());
  1303. psUnLock.setInt(2, id);
  1304. psUnLock.setInt(3, user.__getIDS());
  1305. psUnLock.execute();
  1306. } catch (SQLException ex) {
  1307. Log.Debug(ex);
  1308. }
  1309. }
  1310. private static PreparedStatement psUnLock;
  1311. /**
  1312. * Updates the given column at the row with the given id
  1313. *
  1314. * @param columnName
  1315. * @param id
  1316. * @param value
  1317. */
  1318. public void update(String columnName, Integer id, Object value) {
  1319. QueryData d = new QueryData();
  1320. d.add(columnName, value);
  1321. update(d, new String[]{"ids", id.toString(), ""}, null);
  1322. }
  1323. /**
  1324. *
  1325. * @param what : {set, values}
  1326. * @param where : {value, comparison, "'"}
  1327. * @param jobmessage
  1328. */
  1329. public void update(QueryData what, String[] where, String jobmessage) {
  1330. String query;
  1331. String[] a = what.getKeys();
  1332. String c = "";
  1333. for (int i = 0; i < a.length; i++) {
  1334. c += a[i] + " = " + what.getValue(a[i]).getWrapper() + what.getValue(a[i]).toString() + what.getValue(a[i]).getWrapper() + ", ";
  1335. }
  1336. if (c.length() > 2) {
  1337. c = c.substring(0, c.length() - 2);
  1338. }
  1339. query = "UPDATE " + table + " SET " + c + " WHERE " + table + "." + where[0] + " = " + where[2] + where[1] + where[2];
  1340. freeUpdateQuery(query, mpv5.usermanagement.MPSecurityManager.EDIT, jobmessage);
  1341. }
  1342. /**
  1343. * Will throw an exception if the desired row to update doesnt exist.
  1344. *
  1345. * @param what The data
  1346. * @param criteria
  1347. * @param jobmessage
  1348. * @throws NodataFoundException
  1349. */
  1350. public void update(QueryData what, QueryCriteria2 criteria, String jobmessage) throws NodataFoundException {
  1351. String checkquery = "select ids from " + table + " WHERE " + criteria.getQuery();
  1352. String query = "UPDATE " + table + " SET " + what + " WHERE " + criteria.getQuery();
  1353. if (freeSelectQuery(checkquery, mpv5.usermanagement.MPSecurityManager.VIEW, jobmessage).hasData()) {
  1354. freeUpdateQuery(query, mpv5.usermanagement.MPSecurityManager.EDIT, jobmessage);
  1355. } else {
  1356. Log.Debug(this, "No data for : " + checkquery);
  1357. throw new NodataFoundException(context);
  1358. }
  1359. }
  1360. /**
  1361. * Will create the row if the desired row to update doesnt exist.
  1362. *
  1363. * @param what The data
  1364. * @param criteria
  1365. * @param jobmessage
  1366. */
  1367. public void updateOrCreate(QueryData what, QueryCriteria2 criteria, String jobmessage) {
  1368. String query = "UPDATE " + table + " SET " + what + " WHERE " + criteria.getQuery();
  1369. if (freeUpdateQuery(query, mpv5.usermanagement.MPSecurityManager.EDIT, jobmessage).getUpdateCount() > 0) {
  1370. } else {
  1371. Log.Debug(this, "Need to create " + what + " on " + criteria.getQuery());
  1372. what.add(criteria);
  1373. insert(what, jobmessage);
  1374. }
  1375. }
  1376. /**
  1377. *
  1378. * @param q The data
  1379. * @param criteria Only the "ids" criterium will be used
  1380. * @param jobmessage
  1381. */
  1382. public void update(QueryData q, QueryCriteria criteria, String jobmessage) {
  1383. update(q, Integer.valueOf(criteria.getValue("ids").toString()), jobmessage);
  1384. }
  1385. /**
  1386. *
  1387. * @param q The data
  1388. * @param doId The row id
  1389. * @param jobmessage
  1390. */
  1391. public void update(QueryData q, int doId, String jobmessage) {
  1392. update(q, new String[]{"ids", String.valueOf(doId), ""}, jobmessage);
  1393. }
  1394. /**
  1395. *
  1396. * @param what
  1397. * @param where : {value, comparison, "'"}
  1398. * @return last matching result as string array
  1399. * @throws NodataFoundException
  1400. */
  1401. @SuppressWarnings("unchecked")
  1402. public Object[] selectLast(String what, String[] where) throws NodataFoundException {
  1403. Object[][] data = select(what, where, what, false);
  1404. if (data == null || data.length == 0) {
  1405. throw new NodataFoundException();
  1406. } else {
  1407. return data[0];
  1408. }
  1409. }
  1410. /**
  1411. * if "where" is "null", everything is selected (without "where" -clause)
  1412. *
  1413. * @param what
  1414. * @param where : {value, comparison, "'"}
  1415. * @return first matching result as string array
  1416. * @throws NodataFoundException
  1417. */
  1418. @SuppressWarnings("unchecked")
  1419. public Object[] selectFirst(String what, String[] where) throws NodataFoundException {
  1420. Object[][] data = select(what, where, what, false);
  1421. if (data == null || data.length == 0) {
  1422. throw new NodataFoundException();
  1423. } else {
  1424. return data[data.length - 1];
  1425. }
  1426. }
  1427. /**
  1428. * if "where" is "null", everything is selected (without "where" -clause)
  1429. *
  1430. * @param what
  1431. * @param where : {value, comparison, "'"}
  1432. * @param searchFoLike
  1433. * @return first matching result as string array
  1434. * @throws NodataFoundException
  1435. */
  1436. @SuppressWarnings("unchecked")
  1437. public Object[] selectFirst(String what, String[] where, boolean searchFoLike) throws NodataFoundException {
  1438. Object[][] data = select(what, where, what, searchFoLike);
  1439. if (data == null || data.length == 0) {
  1440. throw new NodataFoundException();
  1441. } else {
  1442. return data[data.length - 1];
  1443. }
  1444. }
  1445. /**
  1446. * if "where" is "null", everything is selected (without "where" -clause)
  1447. *
  1448. * @param what
  1449. * @param where : {value, comparison, "'"}
  1450. * @param searchFoLike
  1451. * @return last matching result as string array
  1452. * @throws NodataFoundException
  1453. */
  1454. @SuppressWarnings("unchecked")
  1455. public Object[] selectLast(String what, String[] where, boolean searchFoLike) throws NodataFoundException {
  1456. Object[][] data = select(what, where, what, searchFoLike);
  1457. if (data == null || data.length == 0) {
  1458. throw new NodataFoundException();
  1459. } else {
  1460. return data[0];
  1461. }
  1462. }
  1463. /**
  1464. *
  1465. * @param what
  1466. * @param where
  1467. * @param leftJoinTable
  1468. * @param leftJoinKey
  1469. * @param order
  1470. * @return results as multidimensional string array
  1471. */
  1472. @SuppressWarnings("unchecked")
  1473. public Object[][] select(String what, String[] where, String leftJoinTable, String leftJoinKey, String order) {
  1474. return select(what, where, leftJoinTable, leftJoinKey, order, null);
  1475. }
  1476. /**
  1477. *
  1478. * @param what
  1479. * @param where
  1480. * @param leftJoinTable
  1481. * @param leftJoinKey
  1482. * @return results as multidimensional string array
  1483. */
  1484. @SuppressWarnings({"unchecked", "unchecked"})
  1485. public Object[][] select(String what, String[] where, String leftJoinTable, String leftJoinKey) {
  1486. return select(what, where, leftJoinTable, leftJoinKey, "id", null);
  1487. }
  1488. /**
  1489. *
  1490. * @param what
  1491. * @param where : {value, comparison, "'"}
  1492. * @return results as multidimensional string array <i><b>Omits trashed
  1493. * datasets implicitly</b></i>
  1494. */
  1495. @SuppressWarnings("unchecked")
  1496. public Object[][] select(String what, String[] where) {
  1497. // start();
  1498. String query;
  1499. if (where != null && where[0] != null && where[1] != null) {
  1500. query = "SELECT " + what + " FROM " + table + " " + context.getReferences() + " WHERE " + table + "." + where[0] + " = " + where[2] + where[1] + where[2] + " AND " + context.getConditions(false).substring(6, context.getConditions(false).length());
  1501. } else {
  1502. query = "SELECT " + what + " FROM " + table + " " + context.getReferences() + " WHERE " + context.getConditions(false).substring(6, context.getConditions(false).length());
  1503. }
  1504. return freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null).getData();
  1505. }
  1506. /**
  1507. *
  1508. * @param what
  1509. * @param where : {value, comparison, "'"}
  1510. * @param order
  1511. * @param limit
  1512. * @return results as multidimensional string array <i><b>Omits trashed
  1513. * datasets implicitly</b></i>
  1514. */
  1515. @SuppressWarnings("unchecked")
  1516. public Object[][] select(String what, String[] where, String order, int limit) {
  1517. if (limit > 0) {
  1518. setLimit(limit);
  1519. }
  1520. String query;
  1521. if (where != null && where[0] != null && where[1] != null) {
  1522. query = "SELECT " + what + " FROM " + table + " " + context.getReferences() + " WHERE " + table + "." + where[0] + " = " + where[2] + where[1] + where[2] + " AND " + context.getConditions(false).substring(6, context.getConditions(false).length()) + (order != null ? " ORDER BY " + order : "");
  1523. } else {
  1524. query = "SELECT " + what + " FROM " + table + " " + context.getReferences() + " WHERE " + context.getConditions(false).substring(6, context.getConditions(false).length()) + (order != null ? " ORDER BY " + order : "");
  1525. }
  1526. return freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null).getData();
  1527. }
  1528. /**
  1529. *
  1530. * @param what
  1531. * @param whereColumns {"column1","column2"}
  1532. * @param haveValues {"value1",value2<any/>}
  1533. * @return <i><b>Omits trashed datasets implicitly</b></i>
  1534. */
  1535. public Object[][] select(String what, String[] whereColumns, Object[] haveValues, boolean withDeleted) {
  1536. String query = "SELECT " + what + " FROM " + table + " " + context.getReferences() + (whereColumns.length > 0 ? " WHERE " : "");
  1537. for (int i = 0; i < haveValues.length; i++) {
  1538. Object object = haveValues[i];
  1539. String column = whereColumns[i];
  1540. query += table + "." + column + "=" + String.valueOf(object);
  1541. if ((i + 1) != haveValues.length) {
  1542. query += " AND ";
  1543. } else {
  1544. query += " AND " + context.getConditions(false).substring(6, context.getConditions(withDeleted).length());
  1545. }
  1546. }
  1547. return freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null).getData();
  1548. }
  1549. /**
  1550. *
  1551. * @param what
  1552. * @param where : {value, comparison, "'"}
  1553. * @param order
  1554. * @param like - datum will be returned between given and given + 1 month
  1555. * @return results as multidimensional string array <i><b>Omits trashed
  1556. * datasets implicitly</b></i>
  1557. */
  1558. @SuppressWarnings("unchecked")
  1559. public Object[][] select(String what, String[] where, String order, boolean like) {
  1560. // start();
  1561. String l1 = "";
  1562. String l2 = "";
  1563. String condition = " = ";
  1564. String j = "";
  1565. if (order == null) {
  1566. order = "ids ";
  1567. }
  1568. String ord = " ORDER BY " + table + "." + order;
  1569. String wher = "";
  1570. java.util.Date date;
  1571. if (like) {
  1572. if (where != null && where[0].endsWith("datum")) {
  1573. condition = " BETWEEN ";
  1574. date = DateConverter.getDate(where[1]);
  1575. where[1] = "'" + DateConverter.getSQLDateString(date) + "'" + " AND " + "'" + DateConverter.getSQLDateString(DateConverter.addMonth(date)) + "'";
  1576. where[2] = " ";
  1577. } else {
  1578. l1 = "%";
  1579. l2 = "%";
  1580. condition = " LIKE ";
  1581. }
  1582. }
  1583. if (where == null) {
  1584. wher = " " + context.getConditions(false);
  1585. } else {
  1586. if (!like) {
  1587. wher = " WHERE " + table + "." + where[0] + " " + condition + " " + where[2] + l1 + where[1] + l2 + where[2] + " AND " + context.getConditions(false).substring(6, context.getConditions(false).length()) + " ";
  1588. } else {
  1589. wher = " WHERE UPPER(" + table + "." + where[0] + ") " + condition + " " + where[2] + l1 + where[1].toUpperCase() + l2 + where[2] + " AND " + context.getConditions(false).substring(6, context.getConditions(false).length()) + " ";
  1590. }
  1591. }
  1592. String query = "SELECT " + what + " FROM " + table + " " + context.getReferences() + wher + ord;
  1593. return freeSelectQuery(query, mpv5.usermanagement.MPSecurityManager.VIEW, null).getData();
  1594. }
  1595. /**
  1596. * Creates a {@link PreparedStatement}
  1597. *
  1598. * @param columns
  1599. * @param conditionColumns
  1600. * @param order
  1601. * @param like
  1602. * @return A {@link PreparedStatement}
  1603. * @throws SQLException
  1604. */
  1605. public PreparedStatement buildPreparedSelectStatement(String columns[], String[] conditionColumns, String order, boolean like) throws SQLException {
  1606. return sqlConn.prepareStatement(buildQuery(columns, conditionColumns, order, like, "OR"), PreparedStatement.RETURN_GENERATED_KEYS);
  1607. }
  1608. /**
  1609. * Executes the given statement
  1610. *
  1611. * @param statement
  1612. * @param values Length must match the conditionColumns argument of the
  1613. * build call of the statement
  1614. * @return
  1615. * @throws java.sql.SQLException <i><b>Omits trashed datasets
  1616. * implicitly</b></i>possible not returning the desired results yet
  1617. */
  1618. @SuppressWarnings("unchecked")
  1619. @Deprecated
  1620. public ReturnValue executeStatement(PreparedStatement statement, Object[] values) throws SQLException {
  1621. if (values != null) {
  1622. for (int i = 0; i < values.length; i++) {
  1623. Object object = values[i];
  1624. statement.setObject(i + 1, object);
  1625. }
  1626. }
  1627. ResultSet set = statement.executeQuery();
  1628. ReturnValue val = new ReturnValue();
  1629. ArrayList spalten = new ArrayList();
  1630. ArrayList zeilen = new ArrayList