PageRenderTime 213ms CodeModel.GetById 13ms app.highlight 159ms RepoModel.GetById 2ms 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

Large files files are truncated, but you can click here to view the full file

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

Large files files are truncated, but you can click here to view the full file