Package edu.internet2.middleware.grouperClient.jdbc

Class GcDbAccess

java.lang.Object
edu.internet2.middleware.grouperClient.jdbc.GcDbAccess
public class GcDbAccess extends Object

Use this class to get access to the global database connections, create a new connection, and execute sql against them.

Sample call 

 Timestamp lastSuccess = new GcDbAccess().sql("select max(ended_time) from grouper_loader_log where job_name = ?")
   .addBindVar("CHANGE_LOG_consumer_recentMemberships").select(Timestamp.class);

From a database external system 
 Integer theOne = new GcDbAccess().connectionName(externalSystemConfigId).sql("select 1 from dual")
   .select(Integer.class);

  • Constructor Details

    • GcDbAccess

      public GcDbAccess()

  • Method Details

    • isGrouperIsStarted

      public static boolean isGrouperIsStarted()
      if grouper started
      Returns:
      the grouperIsStarted

    • setGrouperIsStarted

      public static void setGrouperIsStarted(boolean theGrouperIsStarted)
      if grouper started
      Parameters:
      theGrouperIsStarted - the grouperIsStarted to set

    • batchSize

      public GcDbAccess batchSize(int theBatchSize)
      batch size
      Parameters:
      theBatchSize -
      Returns:
      this for chaining

    • threadLocalQueryCountReset

      public static void threadLocalQueryCountReset()
      reset the query count

    • threadLocalQueryCountRetrieve

      public static int threadLocalQueryCountRetrieve()
      get the query count
      Returns:
      query count

    • threadLocalQueryCountIncrement

      public static void threadLocalQueryCountIncrement(int queriesToAdd)
      Parameters:
      queriesToAdd -

    • tableName

      public GcDbAccess tableName(String theTableName)
      table name if not from annotation
      Parameters:
      theTableName -
      Returns:
      the table name

    • connectionName

      public GcDbAccess connectionName(String theConnectionName)
      connection name from the config file, or null for default
      Parameters:
      theConnectionName -
      Returns:
      this for chaining

    • transactionEnd

      public static void transactionEnd(GcTransactionEnd transactionEnd, boolean endOnlyIfStarted)
      end a transaction
      Parameters:
      transactionEnd -
      endOnlyIfStarted -

    • transactionEnd

      public static void transactionEnd(GcTransactionEnd transactionEnd, boolean endOnlyIfStarted, String connectionName)
      end a transaction
      Parameters:
      transactionEnd -
      endOnlyIfStarted -
      connectionName -

    • loadBoundDataConversion

      public static void loadBoundDataConversion(GcBoundDataConversion _boundDataConversion)
      This is the helper to convert data to and from Oracle, which has a default of BoundDataConversionImpl. If you encounter errors getting and setting data from oracle to java, you may need to override the default and set your version here. Otherwise, nothing is needed.
      Parameters:
      _boundDataConversion - the boundDataConversion to set.

    • createInString

      public static String createInString(int numberOfBindVariables)
      Create an in statement with the given number of bind variables: createInString(2) returns " (?,?) "
      Parameters:
      numberOfBindVariables - is the number of bind variables to use.
      Returns:
      the string.

    • getBindVars

      public List<Object> getBindVars()

    • bindVars

      public GcDbAccess bindVars(Object... _bindVars)
      /** Set the list of bind variable objects, always replacing any that exist.
      Parameters:
      _bindVars - are the variables to add to the list.
      Returns:
      this.

    • addBindVar

      public GcDbAccess addBindVar(Object _bindVar)
      Add to the list of bind variable objects, leaving any that exist there - if you use this in a transaction callback you will have to clear bindvars between calls or they will accumulate.
      Parameters:
      _bindVar - is the variable to add to the list.
      Returns:
      this.

    • addBindVars

      public GcDbAccess addBindVars(Collection<?> _bindVar)
      Add to the list of bind variable objects, leaving any that exist there - if you use this in a transaction callback you will have to clear bindvars between calls or they will accumulate.
      Parameters:
      _bindVar - is the variable to add to the list.
      Returns:
      this.

    • batchBindVars

      public GcDbAccess batchBindVars(List<List<Object>> _batchBindVars)
      If you are executing sql as a batch statement, set the batch bind variables here.
      Parameters:
      _batchBindVars - are the variables to set.
      Returns:
      this.

    • cacheMinutes

      public GcDbAccess cacheMinutes(Integer _cacheMinutes)
      Cache the results of a SELECT query for the allotted minutes.
 Note that cached objects are not immutable; if you modify them you are modifying them in the cache as well.
      Parameters:
      _cacheMinutes - is how long to persist the object(s) in cache for after the initial selection.
      Returns:
      this.

    • sql

      public GcDbAccess sql(String _sql)
      Set the sql to use.
      Parameters:
      _sql - is the sql to use.
      Returns:
      this.

    • omitNullValuesForExample

      public GcDbAccess omitNullValuesForExample()
      If selecting by example, set this and all column values of the given example object except null values will be used to create a where clause.
      Returns:
      this.

    • example

      public GcDbAccess example(Object _example)
      If selecting by example, set this and all column values will be used to create a where clause.
      Parameters:
      _example - is the example to use.
      Returns:
      this.

    • queryTimeoutSeconds

      public GcDbAccess queryTimeoutSeconds(Integer _queryTimeoutSeconds)
      The amount of seconds that the query can run before being rolled back.
      Parameters:
      _queryTimeoutSeconds - is the amount of seconds to set.
      Returns:
      this.

    • accumulateQueryMillis

      public static void accumulateQueryMillis(boolean _accumulateQueryMillis)
      If true, the map queryAndTime will be populated with the time spent in each unique query (unique by query string, not considering bind variable values) 
 - BE SURE TO TURN THIS OFF when done debugging, this is ONLY for debugging on the desktop!Turning it off CLEARS the stats, so write it off first!
 Example:
 1. DbAccess.accumulateQueryMillis(true);
 2. use application normally
 3. Get the results: Map<String, Long> timeSpentInQueries =
      Parameters:
      _accumulateQueryMillis - is whether to accumulate them or not.

    • reportQueriesAndMillisAndTurnOffAccumulation

      public static void reportQueriesAndMillisAndTurnOffAccumulation(String fileLocation)
      Write the stats of queries and time spent in them to a file at the given location, then stop collection stats. accumulateQueryMillis(true)
 must be called first to turn on debugging.
      Parameters:
      fileLocation - is the location of the file to write.

    • primaryKey

      public GcDbAccess primaryKey(Object... _primaryKey)
      Set the primary key to select by.
      Parameters:
      _primaryKey - is the _primaryKey to use.
      Returns:
      this.

    • isPreviouslyPersisted

      public boolean isPreviouslyPersisted(Object o)
      Whether this class has already been saved to the database, looks for a field(s) with annotation @Persistable(primaryKeyField=true),
 assumes that it is a number, and returns true if it is null or larger than 0.
      Parameters:
      o - is the object to store to the database.
      Returns:
      true if so.

    • deleteFromDatabaseMultiple

      public void deleteFromDatabaseMultiple(Collection<? extends Object> objects)

    • deleteFromDatabase

      public void deleteFromDatabase(Object o)
      Delete the object from the database if it has already been stored - the object should have appropriate annotations from the PersistableX annotations.
      Parameters:
      o - is the object to delete from the database.
      See Also:

    • storeListToDatabase

      public <T> void storeListToDatabase(List<T> objects)
      Store the given objects to the database in one transaction - the object should have appropriate annotations from the PersistableX annotations.
      Type Parameters:
      T - is the type to store.
      Parameters:
      objects - are the object to store to the database.
      See Also:

    • storeToDatabase

      public <T> boolean storeToDatabase(T t)
      Store the given object to the database - the object should have appropriate annotations from the PersistableX annotations.
      Type Parameters:
      T - is the type to store.
      Parameters:
      t - is the object to store to the database.
      Returns:
      true if stored and false if not
      See Also:

    • storeBatchToDatabase

      public <T> int storeBatchToDatabase(List<T> objects, int batchSize)
      Store the given objects to the database in a batch - 
 the objects should have appropriate annotations from the PersistableX annotations.
 You cannot have both inserts and updates in the list of objects to store; they MUST all have the 
 same action (insert or update) being taken against them as jdbc statements supoprt mutliple
 sqls in a batch but do not support bind variables when using this capability.
      Type Parameters:
      T - is the type to store.
      Parameters:
      objects - is the list of objects to store to the database.
      batchSize - is the size of the batch to insert or update in.
      Returns:
      number of changes
      See Also:

    • storeBatchToDatabase

      public <T> int storeBatchToDatabase(Collection<T> objects, int batchSize)
      Store the given objects to the database in a batch - 
 the objects should have appropriate annotations from the PersistableX annotations.
 You cannot have both inserts and updates in the list of objects to store; they MUST all have the 
 same action (insert or update) being taken against them as jdbc statements supoprt mutliple
 sqls in a batch but do not support bind variables when using this capability.
      Type Parameters:
      T - is the type to store.
      Parameters:
      objects - is the list of objects to store to the database.
      batchSize - is the size of the batch to insert or update in. e.g. GrouperClientConfig.retrieveConfig().propertyValueInt("grouperClient.syncTableDefault.maxBindVarsInSelect", 900)
      Returns:
      number of changes
      See Also:

    • storeBatchToDatabase

      public <T> int storeBatchToDatabase(List<T> objects, int batchSize, boolean omitPrimaryKeyPopulation)
      Store the given objects to the database in a batch - 
 the objects should have appropriate annotations from the PersistableX annotations.
 You cannot have both inserts and updates in the list of objects to store; they MUST all have the 
 same action (insert or update) being taken against them as jdbc statements supoprt mutliple
 sqls in a batch but do not support bind variables when using this capability.
      Type Parameters:
      T - is the type being stored.
      Parameters:
      objects - is the list of objects to store to the database.
      batchSize - is the size of the batch to insert or update in.
      omitPrimaryKeyPopulation - if you DON'T need primary keys populated into your objects, you can set this and save some query time since we will just set the primary key population as "some_sequence.nextval" instead of selecting it manually before storing the object.
      Returns:
      number of objects changed
      See Also:

    • callbackEntity

      public <T> void callbackEntity(Class<T> clazz, GcEntityCallback<T> entityCallback)
      For each row of a given resultset, hydrate an object and pass it to the callback.
      Type Parameters:
      T -
      Parameters:
      clazz - is the type of thing passed to the entity callback.
      entityCallback - is the callback object that receives this dbAccess with a session set up.

    • callbackTransaction

      public <T> T callbackTransaction(GcTransactionCallback<T> transactionCallback)
      Use a transaction for all calls that happen within this callback. Upon success with no exceptions thrown,
 commit is called automatically. Upon failure, rollback it called. You may also call dbAccess.setTransactionEnd()
 within the callback block.
      Type Parameters:
      T - is the type of thing being returned.
      Parameters:
      transactionCallback - is the callback object that receives this dbAccess with a session set up.
      Returns:
      the thing that you want to return.

    • selectMap

      public <K, V> Map<K,V> selectMap(Class<K> keyClass, Class<V> valueClass)
      Select a map of something from the database - set sql() before calling - this will return a map with column name and column value - this should only select one row from the database.
      Type Parameters:
      K -
      V -
      Parameters:
      keyClass - is the class of the key.
      valueClass - is the class of the value.
      Returns:
      the map or null if nothing is found..

    • selectMapMultipleRows

      public <K, V> Map<K,V> selectMapMultipleRows(Class<K> keyClass, Class<V> valueClass)
      Select a map of two column values from the database - set sql() before calling - the first column in the sql will be used for the map keys and the second will be used for the map values.
      Type Parameters:
      K -
      V -
      Parameters:
      keyClass - is the class of the key.
      valueClass - is the class of the value.
      Returns:
      the map or null if nothing is found..

    • selectListMap

      public List<GcCaseIgnoreHashMap> selectListMap()
      Select a map of rows from the database with column name as key and valueClass as value (should be Object if types differ) from the database - set sql() before calling Example: select first_name, last_name, middle_name from person where rownum < 3: List(0) Map key Map value first_name Fred last_name Jones middle_name Percival List(1) Map key Map value first_name Jeanette last_name Shawna middle_name Percival
      Returns:
      the map or null if nothing is found..

    • selectMapMultipleColumnsOneRow

      public GcCaseIgnoreHashMap selectMapMultipleColumnsOneRow()
      Select a map of key : column name and value : column value from the database - set sql() before calling.
 Example: select first_name, last_name, middle_name from person:
 Map key      Map value
 first_name   Fred
 last_name    Jones
 middle_name  Percival
      Returns:
      the map or null if nothing is found..

    • select

      public <T> T select(Class<T> clazz)
      Select something from the database - either set sql() before calling or primaryKey()
      Type Parameters:
      T - is the type of object that will be returned.
      Parameters:
      clazz - is the type of object that will be returned.
      Returns:
      anything.

    • selectList

      public <T> List<T> selectList(Class<T> clazz)
      Select something from the database - either set sql() before calling or primaryKey(...)
      Type Parameters:
      T - is the type of object that will be returned.
      Parameters:
      clazz - is the type of object that will be returned.
      Returns:
      anything.

    • connectionCreateNew

      public static Connection connectionCreateNew(String connectionName, String[] url) throws ClassNotFoundException, SQLException
      Parameters:
      connectionName -
      url -
      Returns:
      the connection
      Throws:
      ClassNotFoundException
      SQLException

    • connectionGetFromPool

      public static Connection connectionGetFromPool(String connectionName, String[] url) throws ClassNotFoundException, SQLException
      get a connection from a grouper pool
      Parameters:
      connectionName -
      url -
      Returns:
      the connection
      Throws:
      ClassNotFoundException
      SQLException

    • grouperLoaderDbInstance

      public static Object grouperLoaderDbInstance(String connectionName) throws ClassNotFoundException
      get a connection from a grouper pool
      Parameters:
      connectionName -
      url -
      Returns:
      the connection
      Throws:
      ClassNotFoundException
      SQLException

    • quoteForColumnsInSql

      public static String quoteForColumnsInSql(String connectionName)
      the quote or empty string if none
      Parameters:
      connectionName -
      Returns:

    • callbackCallableStatement

      public <T> T callbackCallableStatement(GcCallableStatementCallback<T> callableStatementCallback)
      Callback to get a callableStatement - commit is called if there is no exception thrown, otherwise rollback is called.
      Type Parameters:
      T - is what you are returning, must be a type but you can return null.
      Parameters:
      callableStatementCallback - is the callback object.
      Returns:
      whatever you return from the connection callback.

    • callbackPreparedStatement

      public <T> T callbackPreparedStatement(GcPreparedStatementCallback<T> preparedStatementCallback)
      Callback to get a preparedStatement - commit is called if there is no exception thrown, otherwise rollback is called.
      Type Parameters:
      T - is what you are returning, must be a type but you can return null.
      Parameters:
      preparedStatementCallback - is the callback object.
      Returns:
      whatever you return from the connection callback.

    • callbackConnection

      public <T> T callbackConnection(GcConnectionCallback<T> connectionCallback)
      Callback to get a connection - commit is called if there is no exception thrown, otherwise rollback is called.
      Type Parameters:
      T - is what you are returning, must be a type but you can return null.
      Parameters:
      connectionCallback - is the callback object.
      Returns:
      whatever you return from the connection callback.

    • callbackResultSet

      public <T> T callbackResultSet(GcResultSetCallback<T> resultSetCallback)
      Callback a resultSet.
      Type Parameters:
      T - is the type of object that will be returned.
      Parameters:
      resultSetCallback - is the object to callback.
      Returns:
      anything return from the callback object.

    • executeSql

      public int executeSql()
      Execute some sql.
      Returns:
      anything return from the callback object.

    • executeBatchSql

      public int[] executeBatchSql()
      Execute some sql as a batch.
      Returns:
      anything return from the callback object.

    • getGcDbQueryCacheMap

      public static Map<MultiKey,GcDbQueryCache> getGcDbQueryCacheMap()
      Cached queries, exposed mostly for testing, you should not need direct access to this.
      Returns:
      the dbQueryCacheMap

    • getQueriesAndMillis

      public static Map<String,GcQueryReport> getQueriesAndMillis()
      The map containing reports if they have been turned on.
      Returns:
      the queriesAndMillis

    • connectionHelper

      public static Connection connectionHelper(String connectionName, String[] url) throws ClassNotFoundException, SQLException
      Parameters:
      connectionName -
      url -
      Returns:
      the connection
      Throws:
      ClassNotFoundException
      SQLException