OpenConcerto

Dépôt officiel du code source de l'ERP OpenConcerto
sonarqube

svn://code.openconcerto.org/openconcerto

Rev

Rev 142 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
17 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.sql.utils;
15
 
16
import org.openconcerto.sql.model.ConnectionHandler;
17
import org.openconcerto.sql.model.ConnectionHandlerNoSetup;
18
import org.openconcerto.sql.model.DBSystemRoot;
67 ilm 19
import org.openconcerto.sql.model.IResultSetHandler;
17 ilm 20
import org.openconcerto.sql.model.SQLDataSource;
67 ilm 21
import org.openconcerto.sql.model.SQLRequestLog;
142 ilm 22
import org.openconcerto.sql.model.SQLResultSet;
17 ilm 23
import org.openconcerto.sql.model.SQLSystem;
67 ilm 24
import org.openconcerto.utils.RTInterruptedException;
17 ilm 25
 
26
import java.sql.Connection;
142 ilm 27
import java.sql.ResultSet;
17 ilm 28
import java.sql.SQLException;
83 ilm 29
import java.sql.Savepoint;
67 ilm 30
import java.sql.Statement;
17 ilm 31
import java.util.ArrayList;
32
import java.util.List;
33
import java.util.regex.Pattern;
34
 
73 ilm 35
import org.apache.commons.dbcp.DelegatingConnection;
67 ilm 36
import org.apache.commons.dbutils.ResultSetHandler;
37
 
73 ilm 38
import com.mysql.jdbc.ConnectionProperties;
39
 
17 ilm 40
public class SQLUtils {
41
 
67 ilm 42
    /**
43
     * Return the first chained exception with a non null SQL state.
44
     *
45
     * @param exn an exception.
46
     * @return the first SQLException with a non-<code>null</code>
47
     *         {@link SQLException#getSQLState()}, <code>null</code> if not found.
48
     */
49
    static public final SQLException findWithSQLState(final Exception exn) {
50
        Throwable e = exn;
51
        while (e != null) {
52
            if (e instanceof SQLException) {
53
                final SQLException sqlExn = (SQLException) e;
54
                if (sqlExn.getSQLState() != null) {
55
                    return sqlExn;
56
                }
57
            }
58
            e = e.getCause();
59
        }
60
        return null;
61
    }
62
 
17 ilm 63
    public interface SQLFactory<T> {
64
 
65
        public T create() throws SQLException;
66
 
67
    }
68
 
69
    /**
70
     * Use a single transaction to execute <code>f</code> : it is either committed or rollbacked.
71
     *
72
     * @param <T> type of factory
73
     * @param ds the datasource where f should be executed.
74
     * @param f the factory to execute.
75
     * @return what f returns.
76
     * @throws SQLException if a pb occurs.
77
     */
78
    public static <T> T executeAtomic(final SQLDataSource ds, final SQLFactory<T> f) throws SQLException {
79
        return executeAtomic(ds, new ConnectionHandlerNoSetup<T, SQLException>() {
80
            @Override
81
            public T handle(SQLDataSource ds) throws SQLException {
82
                return f.create();
83
            }
84
        });
85
    }
86
 
87
    /**
83 ilm 88
     * Use a single transaction to execute <code>h</code> : it is either committed or rolled back.
17 ilm 89
     *
90
     * @param <T> type of return
91
     * @param <X> type of exception of <code>h</code>
83 ilm 92
     * @param ds the data source where h should be executed.
17 ilm 93
     * @param h the code to execute.
94
     * @return what h returns.
83 ilm 95
     * @throws SQLException if a problem occurs.
17 ilm 96
     * @throws X if <code>h</code> throw it.
97
     */
98
    public static <T, X extends Exception> T executeAtomic(final SQLDataSource ds, final ConnectionHandlerNoSetup<T, X> h) throws SQLException, X {
90 ilm 99
        return executeAtomic(ds, h, false);
83 ilm 100
    }
101
 
102
    /**
103
     * Execute <code>h</code> in a transaction. Only the outer most call to
104
     * <code>executeAtomic()</code> commit or roll back a transaction, for recursive calls if
105
     * <code>continueTx</code> is <code>true</code> then nothing happens, else a save point is
106
     * created and rolled back if an exception occurs (allowing the caller to catch the exception
107
     * without loosing the current transaction).
108
     * <p>
109
     * NOTE : if <code>continueTx</code> is <code>true</code> and an exception is thrown, the
110
     * connection might be aborted. So you should notify the caller, e.g. propagate the exception so
111
     * that he can roll back the transaction.
112
     * </p>
113
     *
114
     * @param <T> type of return
115
     * @param <X> type of exception of <code>h</code>
116
     * @param ds the data source where h should be executed.
117
     * @param h the code to execute.
118
     * @param continueTx only relevant if already in a transaction : if <code>true</code> the
119
     *        handler will just be executed and the connection won't be modified (i.e. the existing
120
     *        transaction will neither be committed nor rolled back) ; if <code>false</code> a save
121
     *        point will be used.
122
     * @return what h returns.
123
     * @throws SQLException if a problem occurs.
124
     * @throws X if <code>h</code> throw it.
125
     */
126
    public static <T, X extends Exception> T executeAtomic(final SQLDataSource ds, final ConnectionHandlerNoSetup<T, X> h, final boolean continueTx) throws SQLException, X {
17 ilm 127
        return ds.useConnection(new ConnectionHandler<T, X>() {
128
 
129
            private Boolean autoCommit = null;
83 ilm 130
            private Savepoint savePoint = null;
17 ilm 131
 
132
            @Override
133
            public boolean canRestoreState() {
134
                return true;
135
            }
136
 
137
            @Override
138
            public void setup(Connection conn) throws SQLException {
139
                this.autoCommit = conn.getAutoCommit();
140
                if (this.autoCommit) {
141
                    conn.setAutoCommit(false);
83 ilm 142
                } else if (!continueTx) {
143
                    this.savePoint = conn.setSavepoint();
17 ilm 144
                }
145
            }
146
 
147
            @Override
148
            public T handle(final SQLDataSource ds) throws X, SQLException {
149
                return h.handle(ds);
150
            }
151
 
152
            @Override
153
            public void restoreState(Connection conn) throws SQLException {
154
                // can be null if getAutoCommit() failed, in that case nothing to do
83 ilm 155
                final boolean hasStoppedAutoCommit = Boolean.TRUE.equals(this.autoCommit);
156
                final boolean hasSavePoint = this.savePoint != null;
157
                // at most one is enough (otherwise change if/else below)
158
                assert !(hasStoppedAutoCommit && hasSavePoint) : "Begun a transaction and created a save point";
159
                if (hasStoppedAutoCommit || hasSavePoint) {
132 ilm 160
                    final boolean hasException = this.hasException();
17 ilm 161
                    try {
132 ilm 162
                        if (hasException) {
163
                            if (hasStoppedAutoCommit) {
164
                                conn.rollback();
165
                                conn.setAutoCommit(true);
166
                            } else {
167
                                conn.rollback(this.savePoint);
168
                            }
169
                        } else {
170
                            if (hasStoppedAutoCommit) {
171
                                conn.setAutoCommit(true);
172
                                // MS SQL cannot release save points
173
                                // http://technet.microsoft.com/en-us/library/ms378791.aspx
174
                            } else if (ds.getSystem() != SQLSystem.MSSQL) {
175
                                conn.releaseSavepoint(this.savePoint);
176
                            }
177
                        }
17 ilm 178
                    } catch (Exception e) {
132 ilm 179
                        final String msg;
180
                        if (hasException)
181
                            msg = "Couldn't " + (hasSavePoint ? "rollback save point" : "rollback");
83 ilm 182
                        else
132 ilm 183
                            msg = "Couldn't " + (hasSavePoint ? "release save point" : "commit");
184
                        throw new SQLException(msg, e);
17 ilm 185
                    }
186
                }
187
            }
188
        });
189
    }
190
 
191
    /**
192
     * If conn is in autocommit, unset it, try to execute f, if an exception is raised rollback
193
     * otherwise commit ; finally set autocommit. Otherwise just execute f as we assume the calling
194
     * method handles transactions.
195
     *
196
     * @param <T> type of factory
197
     *
198
     * @param conn the connection.
199
     * @param f will be executed.
200
     * @return what f returns.
201
     * @throws SQLException if a pb occurs.
202
     */
203
    public static <T> T executeAtomic(final Connection conn, final SQLFactory<T> f) throws SQLException {
204
        // create a transaction if we aren't in any, otherwise do nothing
205
        final boolean autoCommit = conn.getAutoCommit();
206
        final T res;
207
        if (autoCommit) {
208
            conn.setAutoCommit(false);
209
            try {
210
                res = f.create();
211
                conn.commit();
212
            } catch (SQLException e) {
213
                conn.rollback();
214
                throw e;
215
            } catch (RuntimeException e) {
216
                conn.rollback();
217
                throw e;
218
            } finally {
219
                conn.setAutoCommit(true);
220
            }
221
        } else {
222
            res = f.create();
223
        }
224
 
225
        return res;
226
    }
227
 
228
    /**
229
     * Creates a pseudo sequence with an arbitrary type (not just bigint as real sequences). These
230
     * statements create 2 functions : <code>next_<i>seqName</i>()</code> and
231
     * <code>reset_<i>seqName</i>()</code>.
232
     *
233
     * @param seqName the name of the sequence.
234
     * @param sqlType its SQL type, eg "decimal(16,8)".
235
     * @param minVal the starting value, eg "0.123".
236
     * @param inc the increment, eg "3.14".
237
     * @return the SQL statements.
238
     */
239
    public static List<String> createPostgreSQLSeq(String seqName, String sqlType, String minVal, String inc) {
240
        final List<String> res = new ArrayList<String>();
241
        final String genT = seqName + "_generator";
242
        res.add("DROP TABLE if exists " + genT);
243
        res.add("CREATE TABLE " + genT + " ( " + decl(new String[] { "minVal", "inc", "currentVal", "tmpVal" }, sqlType) + ")");
244
 
245
        String body = "UPDATE " + genT + " set tmpVal = currentVal, currentVal = currentVal + inc ;";
246
        body += "SELECT tmpVal from " + genT + ";";
247
        res.addAll(createFunction("next_" + seqName, sqlType, body));
248
 
249
        body = "update " + genT + " set currentVal = minVal ;";
250
        body += "select currentVal from " + genT + ";";
251
        res.addAll(createFunction("reset_" + seqName, sqlType, body));
252
 
253
        res.add("INSERT INTO " + genT + " values(" + minVal + ", (" + inc + ") )");
254
        res.add("SELECT " + "reset_" + seqName + "()");
255
 
256
        return res;
257
    }
258
 
259
    /**
260
     * A list of declaration.
261
     *
262
     * @param cols columns name, eg ["min", "inc"].
263
     * @param type SQL type, eg "int8".
264
     * @return declaration, eg "min int8, inc int8".
265
     */
266
    private static String decl(String[] cols, String type) {
267
        String res = "";
268
        for (String col : cols) {
269
            res += col + " " + type + ",";
270
        }
271
        // remove last ,
272
        return res.substring(0, res.length() - 1);
273
    }
274
 
275
    /**
276
     * Creates an SQL function (dropping it beforehand).
277
     *
278
     * @param name the name of the function.
279
     * @param type the SQL return type.
280
     * @param body the body of the function.
281
     * @return the SQL statements.
282
     */
283
    private static List<String> createFunction(String name, String type, String body) {
284
        final List<String> res = new ArrayList<String>();
285
 
286
        res.add("DROP FUNCTION if exists " + name + "()");
287
        String f = "CREATE FUNCTION " + name + "() RETURNS " + type + " AS $createFunction$ ";
288
        f += body;
289
        f += " $createFunction$ LANGUAGE SQL";
290
        res.add(f);
291
 
292
        return res;
293
    }
294
 
295
    static public final String SPLIT_DELIMITER = "$jdbcDelimiter$";
296
    static private final Pattern splitMySQLQueries = Pattern.compile(";\r?\n");
297
    static public final Pattern SPLIT_PATTERN = Pattern.compile(SPLIT_DELIMITER, Pattern.LITERAL);
298
 
299
    /**
300
     * Split a SQL script so that it can be executed. For MySQL the script is split at ';' for
301
     * others at {@link #SPLIT_DELIMITER}.
302
     *
303
     * @param sql the script to execute.
304
     * @param sysRoot where to execute.
305
     * @throws SQLException if an exception happens.
306
     */
307
    static public void executeScript(final String sql, final DBSystemRoot sysRoot) throws SQLException {
308
        // Bug 1: MySQL jdbc cannot execute what MySQL QueryBrowser can
309
        // ie before 5.1 you could execute a string with multiple CREATE TABLE,
310
        // but in 5.1 each execute must have exactly one query
311
        // Bug 2: MySQL does not have the concept of dollar quoted strings
312
        // so we have to help it and split the query (eg around trigger and functions)
313
        final SQLSystem sys = sysRoot.getServer().getSQLSystem();
314
        final Pattern p = sys == SQLSystem.MYSQL || sys == SQLSystem.MSSQL ? splitMySQLQueries : SPLIT_PATTERN;
315
        executeScript(sql, sysRoot, p);
316
    }
317
 
318
    static public void executeScript(final String sql, final DBSystemRoot sysRoot, final Pattern p) throws SQLException {
319
        try {
320
            for (final String s : p.split(sql)) {
321
                final String trimmed = s.trim();
322
                if (trimmed.length() > 0)
323
                    sysRoot.getDataSource().execute(trimmed, null);
324
            }
325
        } catch (final Exception e) {
326
            throw new SQLException("unable to execute " + sql, e);
327
        }
328
    }
67 ilm 329
 
330
    /**
331
     * Execute all queries at once if possible.
332
     *
333
     * @param sysRoot where to execute.
334
     * @param queries what to execute.
335
     * @param handlers how to process the result sets, items can be <code>null</code>.
336
     * @return the results of the handlers.
337
     * @throws SQLException if an error occur
338
     * @throws RTInterruptedException if the current thread is interrupted.
83 ilm 339
     * @see SQLSystem#isMultipleResultSetsSupported()
67 ilm 340
     */
341
    static public List<?> executeMultiple(final DBSystemRoot sysRoot, final List<String> queries, final List<? extends ResultSetHandler> handlers) throws SQLException, RTInterruptedException {
342
        final int size = handlers.size();
343
        if (queries.size() != size)
344
            throw new IllegalArgumentException("Size mismatch " + queries + " / " + handlers);
345
        final List<Object> results = new ArrayList<Object>(size);
346
 
73 ilm 347
        final SQLSystem system = sysRoot.getServer().getSQLSystem();
348
        if (system.isMultipleResultSetsSupported()) {
67 ilm 349
            final long timeMs = System.currentTimeMillis();
350
            final long time = System.nanoTime();
351
            final long afterCache = time;
352
 
353
            final StringBuilder sb = new StringBuilder(256 * size);
354
            for (final String q : queries) {
355
                sb.append(q);
356
                if (!q.trim().endsWith(";"))
357
                    sb.append(';');
358
                sb.append('\n');
359
            }
360
            final String query = sb.toString();
361
            sysRoot.getDataSource().useConnection(new ConnectionHandlerNoSetup<Object, SQLException>() {
362
                @Override
363
                public Object handle(SQLDataSource ds) throws SQLException {
364
                    final Connection conn = ds.getConnection();
73 ilm 365
 
366
                    if (system == SQLSystem.MYSQL) {
367
                        final ConnectionProperties connectionProperties = (ConnectionProperties) ((DelegatingConnection) conn).getInnermostDelegate();
368
                        if (!connectionProperties.getAllowMultiQueries()) {
369
                            throw new IllegalStateException("Multi queries not allowed and the setting can only be set before connecting");
370
                        }
371
                    }
372
 
67 ilm 373
                    final long afterQueryInfo = System.nanoTime();
374
                    final long afterExecute, afterHandle;
142 ilm 375
                    int count = 0;
174 ilm 376
                    try (final Statement stmt = conn.createStatement()){
67 ilm 377
                        if (Thread.currentThread().isInterrupted())
378
                            throw new RTInterruptedException("Interrupted before executing : " + query);
379
                        stmt.execute(query);
380
                        afterExecute = System.nanoTime();
381
                        for (final ResultSetHandler h : handlers) {
382
                            if (Thread.currentThread().isInterrupted())
383
                                throw new RTInterruptedException("Interrupted while handling results : " + query);
142 ilm 384
                            if (h == null) {
385
                                results.add(null);
386
                            } else {
387
                                final ResultSet resultSet = stmt.getResultSet();
388
                                results.add(h.handle(resultSet));
389
                                count += SQLResultSet.getRowProcessedCount(resultSet);
390
                            }
67 ilm 391
                            stmt.getMoreResults();
392
                        }
393
                        afterHandle = System.nanoTime();
394
                    }
142 ilm 395
                    SQLRequestLog.log(query, "executeMultiple", conn, timeMs, time, afterCache, afterQueryInfo, afterExecute, afterHandle, System.nanoTime(), count);
67 ilm 396
                    return null;
397
                }
398
            });
399
        } else {
400
            // use the same connection to allow some insert/update followed by a select
401
            sysRoot.getDataSource().useConnection(new ConnectionHandlerNoSetup<Object, SQLException>() {
402
                @Override
403
                public Object handle(SQLDataSource ds) throws SQLException {
404
                    for (int i = 0; i < size; i++) {
405
                        final ResultSetHandler rsh = handlers.get(i);
406
                        // since the other if clause cannot support cache and this clause doesn't
407
                        // have any table to fire, don't use cache
408
                        results.add(sysRoot.getDataSource().execute(queries.get(i), rsh == null ? null : new IResultSetHandler(rsh, false)));
409
                    }
410
                    return null;
411
                }
412
            });
413
        }
414
        return results;
415
    }
17 ilm 416
}