MysqlStatement.java

Go to the documentation of this file.

package org.openslx.imagemaster.db;

import java.io.Closeable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class MysqlStatement implements Closeable
{

   private static final QueryCache cache = new QueryCache();

   private final PreparsedQuery query;

   private final PreparedStatement statement;

   private final List<ResultSet> openResultSets = new ArrayList<>();

   MysqlStatement( Connection con, String sql ) throws SQLException
   {
      PreparsedQuery query;
      synchronized ( cache ) {
         query = cache.get( sql );
      }
      if ( query == null ) {
         query = parse( sql );
         synchronized ( cache ) {
            cache.put( sql, query );
         }
      }
      this.query = query;
      this.statement = con.prepareStatement( query.sql, Statement.RETURN_GENERATED_KEYS );
   }

   public String getQuery()
   {
      return query.sql;
   }

   private List<Integer> getIndexes( String name )
   {
      List<Integer> indexes = query.indexMap.get( name );
      if ( indexes == null ) {
         throw new IllegalArgumentException( "Parameter not found: " + name );
      }
      return indexes;
   }

   public void setObject( String name, Object value ) throws SQLException
   {
      List<Integer> indexes = getIndexes( name );
      for ( Integer index : indexes ) {
         statement.setObject( index, value );
      }
   }

   public void setString( String name, String value ) throws SQLException
   {
      List<Integer> indexes = getIndexes( name );
      for ( Integer index : indexes ) {
         statement.setString( index, value );
      }
   }

   public void setInt( String name, int value ) throws SQLException
   {
      List<Integer> indexes = getIndexes( name );
      for ( Integer index : indexes ) {
         statement.setInt( index, value );
      }
   }

   public void setLong( String name, long value ) throws SQLException
   {
      List<Integer> indexes = getIndexes( name );
      for ( Integer index : indexes ) {
         statement.setLong( index, value );
      }
   }

   public void setBoolean( String name, boolean value ) throws SQLException
   {
      List<Integer> indexes = getIndexes( name );
      for ( Integer index : indexes ) {
         statement.setBoolean( index, value );
      }
   }

   public void setBinary( String name, byte[] value ) throws SQLException
   {
      List<Integer> indexes = getIndexes( name );
      for ( Integer index : indexes ) {
         statement.setBytes( index, value );
      }
   }

   public boolean execute() throws SQLException
   {
      return statement.execute();
   }

   public ResultSet executeQuery() throws SQLException
   {
      ResultSet rs = statement.executeQuery();
      openResultSets.add( rs );
      return rs;
   }

   public int executeUpdate() throws SQLException
   {
      return statement.executeUpdate();
   }

   public ResultSet getGeneratedKeys() throws SQLException
   {
      ResultSet rs = statement.getGeneratedKeys();
      openResultSets.add( rs );
      return rs;
   }

   public int lastInsertId() throws SQLException
   {
      int result = -1;
      try ( ResultSet rs = statement.getGeneratedKeys() ) {
         if ( rs.next() ) {
            result = rs.getInt( 1 );
         }
      }
      return result;
   }

   @Override
   public void close()
   {
      for ( ResultSet rs : openResultSets ) {
         try {
            rs.close();
         } catch ( SQLException e ) {
            //
         }
      }
      try {
         statement.cancel();
      } catch ( SQLException e ) {
         // Nothing to do
      }
      try {
         statement.close();
      } catch ( SQLException e ) {
         // Nothing to do
      }
   }

   public void addBatch() throws SQLException
   {
      statement.addBatch();
   }

   public int[] executeBatch() throws SQLException
   {
      return statement.executeBatch();
   }

   // static methods

   private static PreparsedQuery parse( String query )
   {
      int length = query.length();
      StringBuffer parsedQuery = new StringBuffer( length );
      Map<String, List<Integer>> paramMap = new HashMap<>();
      boolean inSingleQuote = false;
      boolean inDoubleQuote = false;
      boolean hasBackslash = false;
      int index = 1;

      for ( int i = 0; i < length; i++ ) {
         char c = query.charAt( i );
         if ( hasBackslash ) {
            // Last char was a backslash, so we ignore the current char
            hasBackslash = false;
         } else if ( c == '\\' ) {
            // This is a backslash, next char will be escaped
            hasBackslash = true;
         } else if ( inSingleQuote ) {
            // End of quoted string
            if ( c == '\'' ) {
               inSingleQuote = false;
            }
         } else if ( inDoubleQuote ) {
            // End of quoted string
            if ( c == '"' ) {
               inDoubleQuote = false;
            }
         } else {
            // Not in string, look for named params
            if ( c == '\'' ) {
               inSingleQuote = true;
            } else if ( c == '"' ) {
               inDoubleQuote = true;
            } else if ( c == ':' && i + 1 < length && Character.isJavaIdentifierStart( query.charAt( i + 1 ) ) ) {
               int j = i + 2;
               while ( j < length && Character.isJavaIdentifierPart( query.charAt( j ) ) ) {
                  j++;
               }
               String name = query.substring( i + 1, j );
               c = '?'; // replace the parameter with a question mark
               i += name.length(); // skip past the end of the parameter

               List<Integer> indexList = paramMap.get( name );
               if ( indexList == null ) {
                  indexList = new ArrayList<>();
                  paramMap.put( name, indexList );
               }
               indexList.add( Integer.valueOf( index ) );

               index++;
            }
         }
         parsedQuery.append( c );
      }

      return new PreparsedQuery( parsedQuery.toString(), paramMap );
   }

   // private helper classes

   private static class PreparsedQuery
   {
      private final Map<String, List<Integer>> indexMap;
      private final String sql;

      public PreparsedQuery( String sql, Map<String, List<Integer>> indexMap )
      {
         this.sql = sql;
         this.indexMap = indexMap;
      }
   }

   private static class QueryCache extends LinkedHashMap<String, PreparsedQuery>
   {
      private static final long serialVersionUID = 1L;

      public QueryCache()
      {
         super( 30, (float)0.75, true );
      }

      @Override
      protected boolean removeEldestEntry( Map.Entry<String, PreparsedQuery> eldest )
      {
         return size() > 40;
      }
   }

}