All Downloads are FREE. Search and download functionalities are using the official Maven repository.

com.databasesandlife.util.jdbc.DbTransaction Maven / Gradle / Ivy

Go to download

Utility classes developed at Adrian Smith Software (A.S.S.)

The newest version!
package com.databasesandlife.util.jdbc;

import com.databasesandlife.util.Timer;
import com.databasesandlife.util.YearMonthDay;
import com.microsoft.sqlserver.jdbc.SQLServerDriver;
import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.TableRecord;
import org.jooq.impl.DSL;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Array;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.Date;
import java.util.*;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Stream;
import java.util.stream.StreamSupport;

import static com.databasesandlife.util.gwtsafe.ConfigurationException.prefixExceptionMessage;

/**
 * Represents a transaction against a database.
 *     

 * MySQL, PostgreSQL and SQL Server and SQLite are supported.
 *     

 * An object is created with the JDBC URL to the database.
 * There is no factory for this type of object, simply store the string JDBC URL as opposed to a DbConnectionFactory.
 *     

 * The following facilities are provided 
 * (i.e. these are the reasons why objects of this class might be preferred over simply using JDBC directly):
 * 
    
 *   
  • SQLExceptions are not thrown. 
 *       This allows checked Exceptions to be used in client code to represent things that might actually happen.
 *       If a SQLException occurs e.g. due to database connectivity, it is assumed there is nothing the program can do apart 
 *          from display an error to the user.
 *   
  • {@link #execute} method prepares statements and does parameter substitution in one line.
 *   
  • {@link #query} method acts like execute, but returns an {@link Iterable} of objects representing rows.
 *       This is more convenient for the java "for" statement than the JDBC ResultSet object.
 *   
  • Various extra data types are supported such as "points in time" stored as GMT date/times using {@link Date java.util.Date}, 
 *       {@link LocalDate}, etc.
 *   
  • {@link #insert} and {@link #update} take Maps of columns as arguments (easier than maintaining SQL strings)
 *   
  • {@link #insertAndFetchNewId} performs an insert and returns the new "auto-increment ID".
 *   
  • {@link #insertIgnoringUniqueConstraintViolations} and {@link #updateIgnoringUniqueConstraintViolations}
 *       perform inserts and updates, but ignore any unique constraint violations.
 *       For example using the "insert then update" pattern, for "just-in-time" creating records, can use these methods.
 *   
  • {@link #attempt(Runnable)} establishes a savepoint before the runnable and rolls back to it on failure,
 *       necessary for any operation that may fail when using PostgreSQL.
    • 
 *   
  • The transaction isolation level is set to REPEATABLE READ. (This is the default in MySQL but not other databases.)
 *   
  • You can register {@link RollbackListener} objects with {@link #addRollbackListener(RollbackListener)}.
 *       When the transaction rolls back, this listener will get called.
 *       This is so that any primary keys which have been assigned and stored in Java objects,
 *       which are now no longer valid due to the rollback, may be removed from the Java objects.
 * 

 *     

 * Upon creating an object, a connection is made to the database, and a transaction is started.
 * Upon executing {@link #commit()} or {@link #rollback()} the connection is closed.
 * Although opening a connection each time is not as efficient as using a connection pool, this class is extremely simple,
 * which has advantages both in terms or reliability, maintainability and also speed. (For example, C3P0 has > 50 KLOC).
 * Opening a connection to MySQL is fast.
 * 

 * 
 * 
DbTransaction objects are not thread safe; do not use them from multiple threads simultaneously.

 * 
 * 
Example usage:
 * 
 *   String jdbc = "jdbc:mysql://hostname/dbName?user=x&password=x&useUnicode=true&characterEncoding=UTF-8";
 *   try (DbTransaction db = new DbTransaction(jdbc)) {
 *      db.execute("DELETE FROM x WHERE id=?", 9);
 *      db.commit();
 *   }
 * 

 * 
 * @author This source is copyright Adrian Smith and licensed under the LGPL 3.
 * @see Project on GitHub
 */

@SuppressWarnings({ "deprecation", "UseOfObsoleteDateTimeApi" })
public class DbTransaction implements DbQueryable, AutoCloseable {
    
    public final DbServerProduct product;
    protected Connection connection;    // null means already committed
    protected final List rollbackListeners = new ArrayList<>();
    protected final Map preparedStatements = new HashMap<>();
    protected final Map>, String> postgresTypeForEnum = new HashMap<>();

    @Override
    public void close() {
        rollbackIfConnectionStillOpen();
    }

    public enum DbServerProduct { mysql, postgres, sqlserver, sqlite };
    
    @FunctionalInterface
    public interface DbTransactionFactory {
        /** Caller must call {@link DbTransaction#commit()} or {@link DbTransaction#rollback()}. */
        public DbTransaction newDbTransaction();
    }
    
    public static class UniqueConstraintViolation extends Exception {
        public final String constraintName;
        public UniqueConstraintViolation(String c) { super(); constraintName=c; }
        public UniqueConstraintViolation(String c, Throwable t) { super(t); constraintName=c; }
    }
    
    public static class ForeignKeyConstraintViolation extends Exception {
        public ForeignKeyConstraintViolation() { super(); }
        public ForeignKeyConstraintViolation(Throwable t) { super(t); }
    }
    
    public static class SqlException extends RuntimeException {
        public SqlException(Throwable x) { super(x); }
        public SqlException(String x) { super(x); }
        public SqlException(String prefix, Throwable t) { super(prefixExceptionMessage(prefix, t), t); }
    }
    
    public static class CannotConnectToDatabaseException extends RuntimeException {
        public CannotConnectToDatabaseException(String x) { super(x); }
        public CannotConnectToDatabaseException(String x, Throwable t) { super(x, t); }
    }
    
    @FunctionalInterface public interface RollbackListener {
        public void transactionHasRolledback();
    }
    
    public static class DbQueryResultRow {
        ResultSet rs;
        DbQueryResultRow(ResultSet rs) { this.rs = rs; }
        
        public boolean hasColumn(String columnName) {
            try {
                var rsmd = rs.getMetaData();
                var columnCount = rsmd.getColumnCount();
                for (var x = 1; x <= columnCount; x++)
                    if (columnName.equals(rsmd.getColumnName(x))) return true;
                return false;
            }
            catch (SQLException e) { throw new RuntimeException(e); }
        }
        
        public List getColumnNames() {
            try {
                var rsmd = rs.getMetaData();
                var columnCount = rsmd.getColumnCount();
                var result = new ArrayList(columnCount);
                for (var i = 1; i <= columnCount; i++) result.add(rsmd.getColumnName(i));
                return result;
            }
            catch (SQLException e) { throw new RuntimeException(e); }
        }

        @SuppressFBWarnings("NP_BOOLEAN_RETURN_NULL") // We want to return null here, this is by design 
        public Boolean getBoolean(String col){
            try { boolean result = rs.getBoolean(col); if (rs.wasNull()) return null; else return result; }
            catch (SQLException e) { throw new RuntimeException(e); }
        }
        
        public String getString(String col) {
            try { return rs.getString(col); }
            catch (SQLException e) { throw new RuntimeException(e); }
        }
        
        public Integer getInt(String col) {
            try { int result = rs.getInt(col); if (rs.wasNull()) return null; else return result; }
            catch (SQLException e) { throw new RuntimeException(e); }
        }
        
        public Long getLong(String col) {
            try { long result = rs.getLong(col); if (rs.wasNull()) return null; else return result; }
            catch (SQLException e) { throw new RuntimeException(e); }
        }
        
        public Double getDouble(String col) {
            try { double result = rs.getDouble(col); if (rs.wasNull()) return null; else return result; }
            catch (SQLException e) { throw new RuntimeException(e); }
        }
        
        /**
         * for the bytea/blob data type - returns the stream, does not convert to byte[]
         * @param col column name
         * @return InputStream
         */
        public InputStream getBinaryStream(String col){
            try { InputStream result = rs.getBinaryStream(col); if (rs.wasNull()) return null; else return result; }
            catch (SQLException e) { throw new RuntimeException(e); }
        }
        
        /**
         * for the bytea/blob data type - returns byte[]
         * @param col column name
         * @return byte[];
         */
        public byte[] getByteArray(String col){
            try {
                byte[] result = rs.getBytes(col);
                if (rs.wasNull())
                    return null;
                else 
                    return result;
            } catch (SQLException e) { 
                throw new RuntimeException(e); 
            }
        }

        public Date getDate(String col) {
            try { 
                var str = rs.getString(col);
                if (str == null) return null;
                var f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                f.setTimeZone(TimeZone.getTimeZone("UTC"));
                return f.parse(str);
            }
            catch (SQLException | ParseException e) { throw new RuntimeException(e); }
        }
        
        public String[] getStringArray(String col) {
            try {
                java.sql.Array x = rs.getArray(col);
                if (x == null) return null;
                Object[] a = (Object[]) x.getArray();
                return Arrays.copyOf(a, a.length, String[].class);
            }
            catch (SQLException e) { throw new RuntimeException(e); }
        }
        
        public Integer[] getIntegerArray(String col) {
            try {
                java.sql.Array x = rs.getArray(col);
                if (x == null) return null;
                Object[] a = (Object[]) x.getArray();
                return Arrays.copyOf(a, a.length, Integer[].class);
            }
            catch (SQLException e) { throw new RuntimeException(e); }
        }
        
        /** Reads column as string and expects "YYYY-MM-DD" format */
        public YearMonthDay getYearMonthDay(String col) {
            try {
                var str = rs.getString(col);
                if (str == null) return null;
                if (str.length() > "YYYY-MM-DD".length()) str = str.substring(0, "YYYY-MM-DD".length()); // e.g. if col is datetime
                return YearMonthDay.newForYYYYMMDD(str);
            }
            catch (SQLException e) { throw new RuntimeException(e); }
        }
        
        public LocalDate getLocalDate(String col) {
            try {                
                var str = rs.getString(col);
                if (str == null) return null;
                
                return LocalDate.parse(str);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        public LocalTime getLocalTime(String col) {
            try {                
                var str = rs.getString(col);
                if (str == null) return null;
                
                return LocalTime.parse(str);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        @SuppressWarnings("unchecked")    
        public > T getEnum(String col, Class clazz) {
            try {
                var str = rs.getString(col);
                if (str == null) return null;
                var valueOfMethod = clazz.getMethod("valueOf", String.class);
                return (T) valueOfMethod.invoke(null, str);
            }
            catch (SQLException | NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
                throw new RuntimeException(e);
            }
        }

        /**
         * The SELECT must supply a VARCHAR[] as PostgreSQL JDBC driver does not implement ENUM[].
         * For example SELECT my_enum_array::VARCHAR[] ....
         */
        @SuppressWarnings("unchecked")
        public > T[] getEnumArray(String col, Class componentClass){
            try {
                java.sql.Array x = rs.getArray(col);
                if (x == null) return null;
                Object[] stringArrayFromDb = (Object[]) x.getArray();
                T[] result = (T[]) Array.newInstance(componentClass, stringArrayFromDb.length);
                var valueOfMethod = componentClass.getMethod("valueOf", String.class);
                for (var i = 0; i < stringArrayFromDb.length; i++)
                    result[i] = (T) valueOfMethod.invoke(null, stringArrayFromDb[i]);
                return result;
            }
            catch (SQLException | NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    public static class DbQueryResultRowIterator implements Iterator {
        enum State { readingData, /** rs is actually one row forward of iterator */ peeked, finished };
        
        ResultSet rs;
        State state = State.readingData;
        
        protected DbQueryResultRowIterator(ResultSet rs) { 
            this.rs = rs; 
            hasNext(); // this forces the statement to really be executed; important for timing
        }
        
        @Override public boolean hasNext() {
            try {
                if (state == State.readingData) { if (rs.next()) state = State.peeked; else state = State.finished; }
                if (state == State.peeked) return true;
                if (state == State.finished) return false;
                throw new RuntimeException();
            }
            catch (SQLException e) { throw new RuntimeException(e); }
        }
        
        @Override public DbQueryResultRow next() {
            hasNext(); // make sure we are peeking or finished
            if (state == State.peeked) { state = State.readingData; return new DbQueryResultRow(rs); }
            if (state == State.finished) throw new NoSuchElementException();
            throw new RuntimeException();
        }
        
        @Override public void remove() {
            throw new UnsupportedOperationException();
        }
    }
    
    public abstract static class DbQueryResultSet implements Iterable {

        public Stream stream() {
            return StreamSupport.stream(spliterator(), false);
        }
        
        /** 
         * Reads all rows in the result set, finds the string column "stringColumnName" and creates objects of type "cl" by
         * calling its constructor taking a single string argument. 
         */
        public  List toObjectList(Class cl, String stringColumnName) {
            try {
                var i = iterator();
                var result = new ArrayList();
                while (i.hasNext()) {
                    var val = i.next().getString(stringColumnName);
                    var obj = cl.getConstructor(String.class).newInstance(val);
                    result.add(obj);
                }
                return result;
            }
            catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException | InstantiationException e) {
                throw new RuntimeException(e);
            }
        }
        
        /** 
         * Reads all rows in the result set, finds the string column "stringColumnName" and creates objects of type "cl" by
         * calling its constructor taking a single string argument. 
         */
        public  Set toObjectSet(Class cl, String stringColumnName) {
            return new HashSet(toObjectList(cl, stringColumnName));
        }

        public Set toIntegerSet(String columnName) {
            var result = new HashSet();
            for (var row : this) result.add(row.getInt(columnName));
            return result;
        }

        public Set toLongSet(String columnName) {
            var result = new HashSet();
            for (var row : this) result.add(row.getLong(columnName));
            return result;
        }
    }
    
    // ---------------------------------------------------------------------------------------------------------------
    // Internal methods
    // ---------------------------------------------------------------------------------------------------------------

    protected void logNewTransaction() {
        LoggerFactory.getLogger(DbTransaction.class.getName() + "." + "newTransaction").info("Starting new transaction...");
    }
    
    protected Connection getConnection() {
        if (connection == null) throw new IllegalStateException("connection already committed or rolledback");
        return connection;
    }
    
    protected PreparedStatement getPreparedStatement(String sql) throws SQLException {
        var c = getConnection(); // throws if already committed/rolledback
        
        var ps = (PreparedStatement) preparedStatements.get(sql);
        if (ps != null) return ps;
        
        ps = c.prepareStatement(sql);
        ps.setFetchSize(50);
        
        preparedStatements.put(sql, ps);
        return ps;
    }
    
    protected PreparedStatement insertParamsToPreparedStatement(String sql, Object... args) throws SQLException {
        var utc = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
        
        var ps = getPreparedStatement(sql);
        for (var i = 0; i < args.length; i++) {
            try {
                if (args[i] == null) 
                    ps.setNull(i+1,Types.NULL);
                else if (args[i] instanceof Boolean b)
                    ps.setBoolean(i+1, b);
                else if (args[i] instanceof String s) // setXx 1st param: first arg is 1 not 0
                    ps.setString(i+1, s);
                else if (args[i] instanceof Integer v)
                    ps.setInt(i+1, v);
                else if (args[i] instanceof Long l)
                    ps.setLong(i+1, l);
                else if (args[i] instanceof Double d)
                    ps.setDouble(i+1, d);
                else if (args[i] instanceof BigDecimal d)
                    ps.setBigDecimal(i+1, d);
                else if (args[i] instanceof java.util.Date date)
                    switch (product) {
                        case mysql:
                            // Can't set Timestamp object directly, see http://bugs.mysql.com/bug.php?id=15604 w.r.t GMT timezone
                            var f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            f.setTimeZone(TimeZone.getTimeZone("UTC"));
                            ps.setString(i+1, f.format(date));
                            break;
                        default:
                            var ts = new java.sql.Timestamp(date.getTime());
                            ps.setTimestamp(i+1, ts, utc);
                    }
                else if (args[i] instanceof YearMonthDay d)
                    switch (product) {
                        case postgres: 
                            ps.setDate(i+1, new java.sql.Date(d.getMidnightUtcAtStart().getTime()), utc);
                            break;
                        default:
                            ps.setString(i+1, ((YearMonthDay) args[i]).toYYYYMMDD()); 
                    }
                else if (args[i] instanceof LocalTime t)
                    ps.setTime(i+1, java.sql.Time.valueOf(t));
                else if (args[i] instanceof LocalDate d)
                    ps.setDate(i+1, java.sql.Date.valueOf(d));
                else if (args[i] instanceof LocalDateTime t)
                    ps.setTimestamp(i+1, java.sql.Timestamp.valueOf(t));
                else if (args[i] instanceof byte[] b)
                    ps.setBytes(i+1, b);
                else if (args[i] instanceof Enum e)
                    ps.setString(i+1, e.name());
                else if (args[i] instanceof String[] arr)
                    ps.setArray(i+1, connection.createArrayOf("varchar", arr));
                else if (args[i] instanceof Integer[] arr)
                    ps.setArray(i+1, connection.createArrayOf("int", arr));
                else if (args[i] instanceof Long[] arr)
                    ps.setArray(i+1, connection.createArrayOf("int", arr));
                else if (args[i] instanceof Enum[] arr)
                    switch (product) {
                        case postgres:
                            ps.setArray(i+1, connection.createArrayOf(postgresTypeForEnum.get(args[i].getClass().getComponentType()), arr));
                            break;
                        default:
                            throw new RuntimeException("Enum Arrays are not supported for: " + product);
                    }
                else 
                    throw new RuntimeException("sql='"+sql+
                        "': unexpected type for argument "+i+": "+args[i].getClass());
            }
            catch (SQLException e) {
                throw new RuntimeException("sql='"+sql+
                    "': unexpected error setting argument "+i+": "+e.getMessage(), e);
            }
        }
        return ps;
    }
    
    protected long fetchNewPkValue() {
        try {
            String sql = switch (product) {
                case mysql -> "SELECT LAST_INSERT_ID() AS id";
                case postgres -> "SELECT lastval() AS id";
                default -> throw new RuntimeException();
            };
            var ps = insertParamsToPreparedStatement(sql);
            var rs = ps.executeQuery();
            if ( ! rs.next()) throw new RuntimeException("SQL to request just-inserted PK value returned no results");
            var result = rs.getLong("id");
            rs.close();
            return result;
        }
        catch (SQLException e) { throw new SqlException(e); }
    }
    
    protected String getQuestionMarkForValue(Object value) {
        if (product == DbServerProduct.postgres) {
            if (value instanceof Enum) {
                var type = postgresTypeForEnum.get(value.getClass());
                if (type == null) throw new RuntimeException("Cannot convert Java Enum '" + value.getClass() + "' to " +
                    "Postgres ENUM type: use addPostgresTypeForEnum method after DbTransaction constructor");
                return "?::" + type;
            }
            if (value instanceof Enum[]) {
                var type = postgresTypeForEnum.get(value.getClass().getComponentType());
                if (type == null) throw new RuntimeException("Cannot convert Java Enum '" + value.getClass() + "' to " +
                    "Postgres ENUM type: use addPostgresTypeForEnum method after DbTransaction constructor");
                return "?::" + type + "[]";
            }
        }
        return "?";
    }
    
    /** @return the name of the unique constraint that was violated, or null if the error was not about a unique constraint violation */
    public static String parseUniqueConstraintViolationOrNull(String msg) {
        { Matcher m = Pattern.compile("Duplicate entry '.*' for key '(.*)'").matcher(msg); if (m.find()) return m.group(1); } // MySQL
        { Matcher m = Pattern.compile("violates unique constraint \"(.*)\"").matcher(msg); if (m.find()) return m.group(1); } // PostgreSQL
        { Matcher m = Pattern.compile("verletzt Unique-Constraint „(.*)“").matcher(msg); if (m.find()) return m.group(1); } // PostgreSQL German
        return null;
    }
    
    public static boolean isForeignKeyConstraintViolation(String msg) {
        if (msg.contains("foreign key constraint")) return true; // MySQL, PostgreSQL 
        if (msg.contains("verletzt Fremdschl")) return true;     // PostgreSQL German
        return false;
    }
    
    /**
     * If "exception" represents a violation exception it is thrown and the connection is rolled back to "initialState",
     * otherwise the original exception is re-thrown.
     */
    protected void rollbackToSavepointAndThrowConstraintViolation(Savepoint initialState, RuntimeException exception) 
    throws UniqueConstraintViolation, ForeignKeyConstraintViolation {
        try {
            var uniqueConstraintViolationOrNull = parseUniqueConstraintViolationOrNull(exception.getMessage());
            var isForeignKeyConstraintViolation = isForeignKeyConstraintViolation(exception.getMessage());
            
            if (uniqueConstraintViolationOrNull!=null || isForeignKeyConstraintViolation) {
                if (initialState != null) {
                    connection.rollback(initialState);
                    connection.releaseSavepoint(initialState);
                }
                if (uniqueConstraintViolationOrNull!=null) throw new UniqueConstraintViolation(uniqueConstraintViolationOrNull, exception);
                else if (isForeignKeyConstraintViolation) throw new ForeignKeyConstraintViolation(exception);
                else throw new RuntimeException();
            }
            else {
                throw exception;
            }
        }
        catch (SQLException e) { throw new RuntimeException(e); }
    }
    
    protected void closeConnection() {
        try {
            for (var p : preparedStatements.values()) p.close();
            connection.close();
            connection = null;
        }
        catch (SQLException ignored) {  }  // ignore errors on closing
    }
    
    // ---------------------------------------------------------------------------------------------------------------
    // Public API
    // ---------------------------------------------------------------------------------------------------------------
    
    public DbTransaction(String jdbcUrl) throws CannotConnectToDatabaseException {
        try {
            logNewTransaction();

            if (jdbcUrl.contains(":mysql")) product = DbServerProduct.mysql;
            else if (jdbcUrl.contains(":postgres")) product = DbServerProduct.postgres;
            else if (jdbcUrl.contains(":sqlserver")) product = DbServerProduct.sqlserver;
            else if (jdbcUrl.contains(":sqlite")) product = DbServerProduct.sqlite;
            else throw new CannotConnectToDatabaseException("Unrecognized server product: " + jdbcUrl);
            
            switch (product) {   // load the classes so that getConnection recognizes the :mysql: etc part of JDBC url
                case mysql: new com.mysql.jdbc.Driver(); break;
                case postgres: new org.postgresql.Driver(); break;
                case sqlserver: new SQLServerDriver(); break;
                case sqlite: break;
                default: throw new RuntimeException("Unreachable");
            }
            
            connection = DriverManager.getConnection(jdbcUrl);
            connection.setAutoCommit(false);

            if (product != DbServerProduct.sqlite) 
                execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ");
        }
        catch (SQLException e) {
            throw new CannotConnectToDatabaseException("cannot connect to database '"+jdbcUrl+"': JBDC driver is OK, "+
                "connection is NOT OK: "+e.getMessage(), e);
        }
    }

    public DbTransaction(DbServerProduct product, Connection connection) {
        this.product = product;
        this.connection = connection;
    }

    public void addPostgresTypeForEnum(Class> enumClass, String postgresType) {
        postgresTypeForEnum.put(enumClass, postgresType);
    }
    
    /**
     * For example, during insert in jOOQ:
     * 
 
     * db.addRollbackListener(() -> {
     *    venue.setVid(null); 
     *    venue.changed(VENUES.VID, false);
     * } );
     * 

     */
    public void addRollbackListener(RollbackListener listener) {
        rollbackListeners.add(listener);
    }
    
    public DSLContext jooq() {
        SQLDialect d = switch (product) {
            case mysql -> SQLDialect.MYSQL;
            case postgres -> SQLDialect.POSTGRES;
            default -> throw new RuntimeException();
        };

        return DSL.using(getConnection(), d);
    }
    
    public static String getSqlForLog(String sql, Object[] args) {
        var result = new StringBuilder();
        var m = Pattern.compile(Pattern.quote("?")).matcher(sql);
        var argIdx = 0;
        while (m.find()) {
            var arg = args[argIdx++];
            var argUnencoded = "" + arg;
            if (arg instanceof java.util.Date) {
                var f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                f.setTimeZone(TimeZone.getTimeZone("UTC"));
                argUnencoded = f.format((java.util.Date) arg);
            }
            var argEncoded = argUnencoded.replace("'", "\\'");
            m.appendReplacement(result, Matcher.quoteReplacement("'" + argEncoded + "'"));
        }
        m.appendTail(result);
        return result.toString();
    }
    
    /** @return Never retuns null (but may return an empty iterable) */
    public DbQueryResultSet query(final String sql, final Object... args) {
        return new DbQueryResultSet() {
            public Iterator iterator() {
                try (var ignored = new Timer("SQL: " + getSqlForLog(sql, args))) {
                    var ps = insertParamsToPreparedStatement(sql, args);
                    var rs = ps.executeQuery();
                    return new DbQueryResultRowIterator(rs);
                }
                catch (SQLException e) { throw new SqlException(getSqlForLog(sql, args), e); }
            }
        };
    }
    
    /** @return Never retuns null (but may return an empty iterable) */
    public DbQueryResultSet query(CharSequence sql, List args) {
        return query(sql.toString(), args.toArray());
    }
    
    public void execute(String sql, Object... args) throws SqlException {
        try { insertParamsToPreparedStatement(sql, args).executeUpdate(); } // returns int = row count processed; we ignore
        catch (SQLException e) { throw new SqlException("database error ("+ getSqlForLog(sql, args)+")", e); }
    }

    /**
     * Sets a savepoint as is necessary on PostgreSQL, runs the code,
     * then rolls back to the savepoint on RuntimeException or discards the savepoint on success.
     */
    public void attempt(Runnable r) {
        try {
            var initialState = connection.setSavepoint();
            try {
                r.run();
            }
            catch (RuntimeException e) {
                connection.rollback(initialState);
                throw e;
            }
            finally {
                connection.releaseSavepoint(initialState);
            }
        }
        catch (SQLException e) { throw new SqlException(e); }
    }

    /** For normal delete where you don't expect a possible foreign key constraint violation, use {@link #execute(String, Object...)} instead */
    public void deleteOrThrowForeignKeyConstraintViolation(String table, String where, Object... args) throws ForeignKeyConstraintViolation {
        try {
            Savepoint initialState = null;
            if (product == DbServerProduct.postgres) initialState = connection.setSavepoint();
            try { 
                execute("DELETE FROM " + table + " WHERE " + where, args);
                if (initialState != null) connection.releaseSavepoint(initialState);
            }
            catch (RuntimeException e) { 
                rollbackToSavepointAndThrowConstraintViolation(initialState, e); 
            }
        }
        catch (UniqueConstraintViolation e) { throw new RuntimeException("Unreachable", e); }
        catch (SQLException e) { throw new SqlException(e); }
    }
    
    public void execute(CharSequence sql, List args) {
        execute(sql.toString(), args.toArray());
    }
    
    protected void appendSetClauses(StringBuilder sql, List params, Map cols) {
        var first = true;
        for (Entry c : cols.entrySet()) {
            if (first) first = false; else sql.append(", ");
            sql.append(getSchemaQuote()).append(c.getKey()).append(getSchemaQuote());
            sql.append(" = ");
            sql.append(getQuestionMarkForValue(c.getValue()));
            params.add(c.getValue());
        }
    }

    protected void appendInsertStatement(StringBuilder sql, List params, String table, Map cols) {
        if (cols.isEmpty() && product == DbServerProduct.postgres) {
            // if no columns:
            //     MySQL:      INSERT INTO mytable () VALUES ();
            //     PostgreSQL: INSERT INTO mytable DEFAULT VALUES;
            sql.append("INSERT INTO ").append(table).append(" DEFAULT VALUES");
        } else if (product == DbServerProduct.mysql) { // statement is easier to read, therefore easier to debug
            sql.append(" INSERT INTO ");
            sql.append(table);
            sql.append(" SET ");
            appendSetClauses(sql, params, cols);
        } else {
            var keys = new StringBuilder();
            var questionMarks = new StringBuilder();
            for (Entry c : cols.entrySet()) {
                if (! keys.isEmpty()) { keys.append(", "); questionMarks.append(", "); }
                keys.append(getSchemaQuote()).append(c.getKey()).append(getSchemaQuote());
                questionMarks.append(getQuestionMarkForValue(c.getValue()));
                params.add(c.getValue());
            }

            sql.append("INSERT INTO ").append(table).append(" (").append(keys).append(") VALUES (").append(questionMarks).append(")");
        }
    }
    
    @SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")
    public void insert(String table, Map cols) {
        var sql = new StringBuilder();
        var params = new ArrayList<>();
        appendInsertStatement(sql, params, table, cols);
        execute(sql, params);
    }

    public void insert(TableRecord record) {
        record.attach(jooq().configuration());
        record.insert();
    }
    
    public void insertOrThrowUniqueConstraintViolation(String table, Map cols)
    throws UniqueConstraintViolation {
        try {
            Savepoint initialState = null;
            if (product == DbServerProduct.postgres) initialState = connection.setSavepoint();
            try { 
                insert(table, cols); 
                if (initialState != null) connection.releaseSavepoint(initialState);
            }
            catch (RuntimeException e) { 
                rollbackToSavepointAndThrowConstraintViolation(initialState, e); 
            }
        }
        catch (ForeignKeyConstraintViolation | SQLException e) { throw new SqlException(e); }
    }
    
    public void insertIgnoringUniqueConstraintViolations(String table, Map cols) {
        try { insertOrThrowUniqueConstraintViolation(table, cols); }
        catch (UniqueConstraintViolation ignored) { } // ignore
    }
    
    public long insertAndFetchNewId(String table, Map cols) {
        insert(table, cols);
        return fetchNewPkValue();
    }
    
    public long insertAndFetchNewIdOrThrowUniqueConstraintViolation(String table, Map cols)
            throws UniqueConstraintViolation {
        insertOrThrowUniqueConstraintViolation(table, cols);
        return fetchNewPkValue();
    }
    
    public void update(String table, Map cols, String where, Object... whereParams) {
        var sql = new StringBuilder();
        var params = new ArrayList<>();
        
        sql.append(" UPDATE ");
        sql.append(table);
        sql.append(" SET ");
        appendSetClauses(sql, params, cols);
        sql.append(" WHERE ");
        sql.append(where);
        params.addAll(Arrays.asList(whereParams));
        
        execute(sql, params);
    }
    
    public void updateOrThrowUniqueConstraintViolation(String table, Map cols, String where, Object... whereParams)
    throws UniqueConstraintViolation {
        try {
            Savepoint initialState = null;
            if (product == DbServerProduct.postgres) initialState = connection.setSavepoint();
            try { 
                update(table, cols, where, whereParams); 
                if (initialState != null) connection.releaseSavepoint(initialState);
            }
            catch (RuntimeException e) { 
                rollbackToSavepointAndThrowConstraintViolation(initialState, e); 
            }
        }
        catch (ForeignKeyConstraintViolation | SQLException e) { throw new SqlException(e); }
    }
    
    public void updateIgnoringUniqueConstraintViolations(String table, Map cols, String where, Object... whereParams) {
        try { updateOrThrowUniqueConstraintViolation(table, cols, where, whereParams); }
        catch (UniqueConstraintViolation ignored) { } // ignore
    }

    /**
     * Inserts (colsToInsert + colsToUpdate) and, if that fails because the row already exists,
     * updates (colsToUpdate) where (primaryKeyColumns out of colsToInsert).
     * @see "Just-in-time" inserting rows into a database (Databases & Life) 
     */
    public void insertOrUpdate(
        String table,
        Map colsToUpdate,
        Map colsToInsert,
        String... primaryKeyColumns
    ) {
        Map newRow = new HashMap<>();
        newRow.putAll(colsToUpdate);
        newRow.putAll(colsToInsert);

        switch (product) {
            case mysql: // Deadlock if multiple sessions attempt the "default" way
                var sql = new StringBuilder();
                var params = new ArrayList<>(primaryKeyColumns.length);
                appendInsertStatement(sql, params, table, newRow);
                sql.append(" ON DUPLICATE KEY UPDATE ");
                appendSetClauses(sql, params, colsToUpdate);
                execute(sql, params);
                break;

            default:
                try { insertOrThrowUniqueConstraintViolation(table, newRow); }
                catch (UniqueConstraintViolation e) {
                    if (colsToUpdate.isEmpty()) return;
                    var where = new StringBuilder();
                    var whereParams = new ArrayList<>(primaryKeyColumns.length);
                    where.append(" TRUE ");
                    for (var col : primaryKeyColumns) {
                        where.append(" AND ");
                        where.append(getSchemaQuote()).append(col).append(getSchemaQuote());
                        where.append(" = ").append(getQuestionMarkForValue(colsToInsert.get(col)));
                        whereParams.add(colsToInsert.get(col));
                    }
                    update(table, colsToUpdate, where.toString(), whereParams.toArray());
                }
        }
    }
    
    public void rollback() {
        try {
            getConnection().rollback();
            for (var l : rollbackListeners) l.transactionHasRolledback();
            closeConnection();
        }
        catch (SQLException e) { throw new SqlException("Can't rollback", e); }
    }
    
    public void commit() {
        try {
            getConnection().commit();
            closeConnection();
        }
        catch (SQLException e) { throw new SqlException("Can't commit", e); }
    }
    
    public void rollbackIfConnectionStillOpen() {
        if (connection != null) rollback();
    }

    public String getFromDual() {
        return switch (product) {
            case sqlserver, postgres, sqlite -> "";
            case mysql -> " FROM dual ";
        };
    }
    
    public String getSchemaQuote() {
        return switch (product) {
            case sqlserver -> "";
            case postgres -> "\"";
            case mysql, sqlite -> "`";
        };
    }
    
    /** Writes "foo IN (?,?,?)" */
    public  void appendIn(Appendable sql, List sqlParams, String field, Collection values) {
        try {
            if (values.isEmpty())
                sql.append("FALSE");
            else {
                var first = true;
                sql.append(field);
                sql.append(" IN (");
                for (var v : values) {
                    if (first) first=false; else sql.append(",");
                    sql.append(getQuestionMarkForValue(v));
                    sqlParams.add(v);
                }
                sql.append(")");
            }
        }
        catch (IOException e) { throw new RuntimeException(e); }
    }
}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy