Package net.tirasa.connid.commons.db

Class SQLUtil

java.lang.Object
net.tirasa.connid.commons.db.SQLUtil
public final class SQLUtil extends Object
The SQL helper/util class.

  • Method Details

    • getDatasourceConnection

      public static Connection getDatasourceConnection(String datasourceName, Properties env)
      Get the connection from the datasource.
      Parameters:
      datasourceName - datasource JNDI name
      env - properties
      Returns:
      the connection get from default jndi context

    • getDatasourceConnection

      public static Connection getDatasourceConnection(String datasourceName, String user, GuardedString password, Properties env)
      Get the connection from the dataSource with specified user and password.
      Parameters:
      datasourceName - datasource JNDI name
      user - DB user
      password - DB password
      env - properties
      Returns:
      the connection get from dataSource

    • getDatasourceConnection

      public static Connection getDatasourceConnection(String datasourceName, String user, GuardedString password)
      Get the connection from the dataSource with specified user and password.
      Parameters:
      datasourceName - datasource JNDI name
      user - DB user
      password - DB password
      Returns:
      the connection get from dataSource

    • getDatasourceConnection

      public static Connection getDatasourceConnection(String datasourceName)
      Get the connection from the datasource.
      Parameters:
      datasourceName - datasource JNDI name
      Returns:
      the connection get from default jndi context

    • getDriverMangerConnection

      public static Connection getDriverMangerConnection(String driver, String url, String login, GuardedString password)
      Gets a Connection using the basic driver manager.
      Parameters:
      driver - jdbc driver name
      url - jdbc connection url
      login - jdbc login name
      password - jdbc password
      Returns:
      a valid connection

    • rollbackQuietly

      public static void rollbackQuietly(Connection conn)
      Ignores any exception thrown by the Connection parameter when closed, it also checks for null.
      Parameters:
      conn - JDBC connection to rollback.

    • rollbackQuietly

      public static void rollbackQuietly(DatabaseConnection conn)
      Ignores any exception thrown by the DatabaseConnection parameter when closed, it also checks for null.
      Parameters:
      conn - DatabaseConnection to rollback.

    • closeQuietly

      public static void closeQuietly(Connection conn)
      Ignores any exception thrown by the Connection parameter when closed, it also checks for null.
      Parameters:
      conn - JDBC connection to close.

    • closeQuietly

      public static void closeQuietly(DatabaseConnection conn)
      Ignores any exception thrown by the Connection parameter when closed, it also checks for null.
      Parameters:
      conn - DatabaseConnection to close.

    • closeQuietly

      public static void closeQuietly(Statement stmt)
      Ignores any exception thrown by the Statement.close() method.
      Parameters:
      stmt - Statement to close.

    • closeQuietly

      public static void closeQuietly(ResultSet rset)
      Closes the ResultSet and ignores any Exception that may be thrown by the ResultSet.close() method.
      Parameters:
      rset - ResultSet to close quitely.

    • date2String

      public static String date2String(Date value)
      Date to string.
      Parameters:
      value - Date value
      Returns:
      String value

    • time2String

      public static String time2String(Time value)
      Time to String format.
      Parameters:
      value - Time value
      Returns:
      String value

    • timestamp2String

      public static String timestamp2String(Timestamp value)
      Convert timestamp to string.
      Parameters:
      value - Timestamp
      Returns:
      the string value

    • string2Time

      public static Time string2Time(String param)
      String to Time.
      Parameters:
      param - String
      Returns:
      the Time value

    • string2Date

      public static Date string2Date(String param)
      String to Date.
      Parameters:
      param - the String value
      Returns:
      Date value

    • string2Timestamp

      public static Timestamp string2Timestamp(String param)
      Convert string to Timestamp
      Parameters:
      param - String value
      Returns:
      Timestamp value

    • string2Boolean

      public static Boolean string2Boolean(String val)
      Convert String to boolean.
      Parameters:
      val - string value
      Returns:
      Boolean value

    • normalizeNullValues

      public static String normalizeNullValues(String sql, List<SQLParam> params, List<SQLParam> out)
      The null param vlaue normalizator.
      Parameters:
      sql - SQL query
      params - list
      out - out param list
      Returns:
      the modified string

    • blob2ByteArray

      public static byte[] blob2ByteArray(Blob blobValue) throws SQLException
      Make a blob conversion.
      Parameters:
      blobValue - blob
      Returns:
      a converted value
      Throws:
      SQLException - if anything goes wrong

    • setParams

      public static void setParams(PreparedStatement statement, List<SQLParam> params) throws SQLException
      Binds the "?" markers in SQL statement with the parameters given as values. It concentrates the replacement of all params.GuardedString are handled so the password is never visible.
      Parameters:
      statement - SQL statement
      params - a List of the object arguments
      Throws:
      SQLException - an exception in statement

    • setParams

      public static void setParams(CallableStatement statement, List<SQLParam> params) throws SQLException
      Binds the "?" markers in SQL statement with the parameters given as values. It concentrates the replacement of all params. GuardedString are handled so the password is never visible.
      Parameters:
      statement - SQL statement
      params - a List of the object arguments
      Throws:
      SQLException - an exception in statement

    • getColumnValues

      public static Map<String,SQLParam> getColumnValues(ResultSet resultSet) throws SQLException
      Read one row from database result set and convert a columns to attribute set.
      Parameters:
      resultSet - database data
      Returns:
      The transformed attribute set
      Throws:
      SQLException - if anything goes wrong

    • getSQLParam

      public static SQLParam getSQLParam(ResultSet resultSet, int i, String name, int sqlType) throws SQLException
      Retrieve the SQL value from result set.
      Parameters:
      resultSet - the result set
      i - index
      name - param name
      sqlType - expected SQL type or Types.NULL for generic
      Returns:
      the object return the retrieved object
      Throws:
      SQLException - any SQL error

    • getSQLAttributeType

      public static Class<?> getSQLAttributeType(int sqlType)
      Convert database type to connector supported set of attribute types Can be redefined for different databases.
      Parameters:
      sqlType - #Types
      Returns:
      a connector supported class

    • setSQLParam

      public static void setSQLParam(PreparedStatement stmt, int idx, SQLParam parm) throws SQLException
      Set a parameter to statement. The conversion to required database type is expected to be done.
      Parameters:
      stmt - the statement to set
      idx - index of the parameter
      parm - the SQLParam value
      Throws:
      SQLException - something wrong

    • jdbc2AttributeValue

      public static Object jdbc2AttributeValue(Object value) throws SQLException
      The conversion to required attribute type.
      Parameters:
      value - to be converted to an attribute
      Returns:
      a attribute's supported object
      Throws:
      SQLException - something is not ok

    • attribute2jdbcValue

      public static Object attribute2jdbcValue(Object value, int sqlType) throws SQLException
      Convert the attribute to expected jdbc type using java conversions Some database strategy sets all attributes as string, other convert them first and than set as native.
      Parameters:
      value - the value to be converted
      sqlType - the target sql type
      Returns:
      the converted object value
      Throws:
      SQLException - any SQL error

    • setGuardedStringParam

      public static void setGuardedStringParam(PreparedStatement stmt, int idx, GuardedString guard) throws SQLException
      The helper guardedString bind method.
      Parameters:
      stmt - to bind to
      idx - index of the object
      guard - a GuardedString parameter
      Throws:
      SQLException - any SQL error

    • selectSingleValue

      public static Object selectSingleValue(Connection conn, String sql, SQLParam... params) throws SQLException
      Selects single value (first column) from select. It fetches only first row, does not check whether more rows are returned by select. If no row is returned, returns null
      Parameters:
      conn - JDBC connection
      sql - Select statement with or without parameters
      params - Parameters to use in statement
      Returns:
      first row and first column value
      Throws:
      SQLException - any SQL error

    • selectRows

      public static List<Object[]> selectRows(Connection conn, String sql, SQLParam... params) throws SQLException
      Selects all rows from select. It uses ResultSet.getMetaData() to find columns count and use ResultSet.getObject(int) to retrieve column value.
      Parameters:
      conn - JDBC connection
      sql - SQL select with or without params
      params - SQL parameters
      Returns:
      list of selected rows
      Throws:
      SQLException - any SQL error

    • executeUpdateStatement

      public static int executeUpdateStatement(Connection conn, String sql, SQLParam... params) throws SQLException
      Executes DML sql statement. This can be useful to execute insert/update/delete or some database specific statement in one call
      Parameters:
      conn - connection
      sql - SQL query
      params - SQL parameters
      Returns:
      number of rows affected as defined by PreparedStatement.executeUpdate()
      Throws:
      SQLException - any SQL error