com.landawn.abacus.util.JdbcUtil Maven / Gradle / Ivy

* Equivalent to {@link ResultSet#close()}, except any exceptions will be ignored. * This is typically used in finally blocks. * * @param rs */ public static void closeQuietly(final ResultSet rs) { closeQuietly(rs, null, null); } /** * * @param rs * @param closeStatement * @throws UncheckedSQLException the unchecked SQL exception */ public static void closeQuietly(final ResultSet rs, final boolean closeStatement) throws UncheckedSQLException { closeQuietly(rs, closeStatement, false); } /** * * @param rs * @param closeStatement * @param closeConnection * @throws IllegalArgumentException if {@code closeStatement = false} while {@code closeConnection = true}. */ public static void closeQuietly(final ResultSet rs, final boolean closeStatement, final boolean closeConnection) throws IllegalArgumentException { if (closeConnection && closeStatement == false) { throw new IllegalArgumentException("'closeStatement' can't be false while 'closeConnection' is true"); } if (rs == null) { return; } Connection conn = null; Statement stmt = null; try { if (closeStatement || closeConnection) { stmt = rs.getStatement(); } if (closeConnection && stmt != null) { conn = stmt.getConnection(); } } catch (SQLException e) { logger.error("Failed to get Statement or Connection by ResultSet", e); } finally { closeQuietly(rs, stmt, conn); } } /** * Unconditionally close an Statement. *

* Equivalent to {@link Statement#close()}, except any exceptions will be ignored. * This is typically used in finally blocks. * * @param stmt */ public static void closeQuietly(final Statement stmt) { closeQuietly(null, stmt, null); } /** * Unconditionally close an Connection. *

* Equivalent to {@link Connection#close()}, except any exceptions will be ignored. * This is typically used in finally blocks. * * @param conn * @deprecated consider using {@link #releaseConnection(Connection, javax.sql.DataSource)} */ @Deprecated public static void closeQuietly(final Connection conn) { closeQuietly(null, null, conn); } /** * Unconditionally close the ResultSet, Statement. *

* Equivalent to {@link ResultSet#close()}, {@link Statement#close()}, except any exceptions will be ignored. * This is typically used in finally blocks. * * @param rs * @param stmt */ public static void closeQuietly(final ResultSet rs, final Statement stmt) { closeQuietly(rs, stmt, null); } /** * Unconditionally close the Statement, Connection. *

* Equivalent to {@link Statement#close()}, {@link Connection#close()}, except any exceptions will be ignored. * This is typically used in finally blocks. * * @param stmt * @param conn */ public static void closeQuietly(final Statement stmt, final Connection conn) { closeQuietly(null, stmt, conn); } /** * Unconditionally close the ResultSet, Statement, Connection. *

* Equivalent to {@link ResultSet#close()}, {@link Statement#close()}, {@link Connection#close()}, except any exceptions will be ignored. * This is typically used in finally blocks. * * @param rs * @param stmt * @param conn */ public static void closeQuietly(final ResultSet rs, final Statement stmt, final Connection conn) { if (rs != null) { try { rs.close(); } catch (Exception e) { logger.error("Failed to close ResultSet", e); } } if (stmt != null) { try { stmt.close(); } catch (Exception e) { logger.error("Failed to close Statement", e); } } if (conn != null) { try { conn.close(); } catch (Exception e) { logger.error("Failed to close Connection", e); } } } /** * * @param rs * @param n the count of row to move ahead. * @return * @throws SQLException */ public static int skip(final ResultSet rs, int n) throws SQLException { return skip(rs, (long) n); } /** * * @param rs * @param n the count of row to move ahead. * @return * @throws SQLException * @see {@link ResultSet#absolute(int)} */ public static int skip(final ResultSet rs, long n) throws SQLException { if (n <= 0) { return 0; } else if (n == 1) { return rs.next() == true ? 1 : 0; } else { final int currentRow = rs.getRow(); if (n <= Integer.MAX_VALUE) { try { if (n > Integer.MAX_VALUE - rs.getRow()) { while (n-- > 0L && rs.next()) { } } else { rs.absolute((int) n + rs.getRow()); } } catch (SQLException e) { while (n-- > 0L && rs.next()) { } } } else { while (n-- > 0L && rs.next()) { } } return rs.getRow() - currentRow; } } /** * Gets the column count. * * @param rs * @return * @throws SQLException */ public static int getColumnCount(ResultSet rs) throws SQLException { return rs.getMetaData().getColumnCount(); } /** * Gets the column name list. * * @param conn * @param tableName * @return * @throws SQLException */ public static List getColumnNameList(final Connection conn, final String tableName) throws SQLException { final String query = "SELECT * FROM " + tableName + " WHERE 1 > 2"; PreparedStatement stmt = null; ResultSet rs = null; try { stmt = prepareStatement(conn, query); rs = executeQuery(stmt); final ResultSetMetaData metaData = rs.getMetaData(); final int columnCount = metaData.getColumnCount(); final List columnNameList = new ArrayList<>(columnCount); for (int i = 1, n = columnCount + 1; i < n; i++) { columnNameList.add(metaData.getColumnName(i)); } return columnNameList; } finally { closeQuietly(rs, stmt); } } public static List getColumnLabelList(ResultSet rs) throws SQLException { final ResultSetMetaData metaData = rs.getMetaData(); final int columnCount = metaData.getColumnCount(); final List labelList = new ArrayList<>(columnCount); for (int i = 1, n = columnCount + 1; i < n; i++) { labelList.add(getColumnLabel(metaData, i)); } return labelList; } /** * Gets the column label. * * @param rsmd * @param columnIndex * @return * @throws SQLException */ public static String getColumnLabel(final ResultSetMetaData rsmd, final int columnIndex) throws SQLException { final String result = rsmd.getColumnLabel(columnIndex); return N.isNullOrEmpty(result) ? rsmd.getColumnName(columnIndex) : result; } /** * Returns the column index starts with from 1, not 0. * * @param resultSet * @param columnName * @return * @throws UncheckedSQLException the unchecked SQL exception */ public static int getColumnIndex(final ResultSet resultSet, final String columnName) throws UncheckedSQLException { try { return getColumnIndex(resultSet.getMetaData(), columnName); } catch (SQLException e) { throw new UncheckedSQLException(e); } } /** * Returns the column index starts with from 1, not 0. * * @param rsmd * @param columnName * @return * @throws SQLException */ public static int getColumnIndex(final ResultSetMetaData rsmd, final String columnName) throws SQLException { final int columnCount = rsmd.getColumnCount(); for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) { if (getColumnLabel(rsmd, columnIndex).equalsIgnoreCase(columnName)) { return columnIndex; } } return -1; } /** * Gets the column value. * * @param rs * @param columnIndex starts with 1, not 0. * @return * @throws SQLException */ public static Object getColumnValue(final ResultSet rs, final int columnIndex) throws SQLException { // Copied from JdbcUtils#getResultSetValue(ResultSet, int) in SpringJdbc under Apache License, Version 2.0. Object obj = rs.getObject(columnIndex); if (obj == null || obj instanceof String || obj instanceof Number) { return obj; } final String className = obj.getClass().getName(); if (obj instanceof Blob) { Blob blob = (Blob) obj; obj = blob.getBytes(1, (int) blob.length()); } else if (obj instanceof Clob) { Clob clob = (Clob) obj; obj = clob.getSubString(1, (int) clob.length()); } else if (obj instanceof NClob) { NClob nclob = (NClob) obj; obj = nclob.getSubString(1, (int) nclob.length()); } else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) { obj = rs.getTimestamp(columnIndex); } else if (className != null && className.startsWith("oracle.sql.DATE")) { final String columnClassName = rs.getMetaData().getColumnClassName(columnIndex); if ("java.sql.Timestamp".equals(columnClassName) || "oracle.sql.TIMESTAMP".equals(columnClassName)) { obj = rs.getTimestamp(columnIndex); } else { obj = rs.getDate(columnIndex); } } else if (obj instanceof java.sql.Date) { if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(columnIndex))) { obj = rs.getTimestamp(columnIndex); } } return obj; } /** * Gets the column value. * * @param rs * @param columnLabel * @return * @throws SQLException * @deprecated please consider using {@link #getColumnValue(ResultSet, int)} */ @Deprecated public static Object getColumnValue(final ResultSet rs, final String columnLabel) throws SQLException { // Copied from JdbcUtils#getResultSetValue(ResultSet, int) in SpringJdbc under Apache License, Version 2.0. Object obj = rs.getObject(columnLabel); if (obj == null || obj instanceof String || obj instanceof Number) { return obj; } final String className = obj.getClass().getName(); if (obj instanceof Blob) { Blob blob = (Blob) obj; obj = blob.getBytes(1, (int) blob.length()); } else if (obj instanceof Clob) { Clob clob = (Clob) obj; obj = clob.getSubString(1, (int) clob.length()); } else if (obj instanceof NClob) { NClob nclob = (NClob) obj; obj = nclob.getSubString(1, (int) nclob.length()); } else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) { obj = rs.getTimestamp(columnLabel); } else { final ResultSetMetaData metaData = rs.getMetaData(); final int columnIndex = getColumnIndex(metaData, columnLabel); if (className != null && className.startsWith("oracle.sql.DATE")) { final String metaDataClassName = metaData.getColumnClassName(columnIndex); if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) { obj = rs.getTimestamp(columnLabel); } else { obj = rs.getDate(columnLabel); } } else if (obj instanceof java.sql.Date) { if ("java.sql.Timestamp".equals(metaData.getColumnClassName(columnIndex))) { obj = rs.getTimestamp(columnLabel); } } } return obj; } /** * Gets the column value. * * @param * @param targetClass * @param rs * @param columnIndex * @return * @throws SQLException */ public static T getColumnValue(final Class targetClass, final ResultSet rs, final int columnIndex) throws SQLException { return N. typeOf(targetClass).get(rs, columnIndex); } /** * Gets the column value. * * @param * @param targetClass * @param rs * @param columnLabel * @return * @throws SQLException * @deprecated please consider using {@link #getColumnValue(Class, ResultSet, int)} */ @Deprecated public static T getColumnValue(final Class targetClass, final ResultSet rs, final String columnLabel) throws SQLException { return N. typeOf(targetClass).get(rs, columnLabel); } // /** The Constant column2FieldNameMapPool. */ // private static final Map, ImmutableMap> column2FieldNameMapPool = new ConcurrentHashMap<>(); // // /** // * Gets the column 2 field name map. // * // * @param entityClass // * @return // */ // static ImmutableMap getColumn2FieldNameMap(Class entityClass) { // ImmutableMap result = column2FieldNameMapPool.get(entityClass); // // if (result == null) { // final Map map = new HashMap<>(); // final EntityInfo entityInfo = ParserUtil.getEntityInfo(entityClass); // // for (PropInfo propInfo : entityInfo.propInfoList) { // if (propInfo.columnName.isPresent()) { // map.put(propInfo.columnName.get(), propInfo.name); // map.put(propInfo.columnName.get().toLowerCase(), propInfo.name); // map.put(propInfo.columnName.get().toUpperCase(), propInfo.name); // } // } // // result = ImmutableMap.copyOf(map); // // column2FieldNameMapPool.put(entityClass, result); // } // // return result; // } public static ImmutableMap getColumn2FieldNameMap(Class entityClass) { return QueryUtil.getColumn2PropNameMap(entityClass); } /** * * @param ds * @return */ public static boolean isInTransaction(final javax.sql.DataSource ds) { if (SQLTransaction.getTransaction(ds, CreatedBy.JDBC_UTIL) != null) { return true; } if (isInSpring && !isSpringTransactionalDisabled_TL.get()) { Connection conn = null; try { conn = getConnection(ds); return org.springframework.jdbc.datasource.DataSourceUtils.isConnectionTransactional(conn, ds); } catch (NoClassDefFoundError e) { isInSpring = false; } finally { releaseConnection(conn, ds); } } return false; } /** * Refer to: {@code beginTransaction(javax.sql.DataSource, IsolationLevel, boolean)}. * * @param dataSource * @return * @throws UncheckedSQLException the unchecked SQL exception * @see {@link #beginTransaction(javax.sql.DataSource, IsolationLevel, boolean)} */ public static SQLTransaction beginTransaction(final javax.sql.DataSource dataSource) throws UncheckedSQLException { return beginTransaction(dataSource, IsolationLevel.DEFAULT); } /** * Refer to: {@code beginTransaction(javax.sql.DataSource, IsolationLevel, boolean)}. * * @param dataSource * @param isolationLevel * @return * @throws UncheckedSQLException the unchecked SQL exception * @see {@link #beginTransaction(javax.sql.DataSource, IsolationLevel, boolean)} */ public static SQLTransaction beginTransaction(final javax.sql.DataSource dataSource, final IsolationLevel isolationLevel) throws UncheckedSQLException { return beginTransaction(dataSource, isolationLevel, false); } /** * Starts a global transaction which will be shared by all in-line database query with the same {@code DataSource} in the same thread, * including methods: {@code JdbcUtil.beginTransaction/prepareQuery/prepareNamedQuery/prepareCallableQuery, SQLExecutor(Mapper).beginTransaction/get/insert/batchInsert/update/batchUpdate/query/list/findFirst/...} * *
* Spring Transaction is supported and Integrated. * If this method is called at where a Spring transaction is started with the specified {@code DataSource}, * the {@code Connection} started the Spring Transaction will be used here. * That's to say the Spring transaction will have the final control on commit/roll back over the {@code Connection}. * *
*
* * Here is the general code pattern to work with {@code SQLTransaction}. * *

     * 
     * public void doSomethingA() {
     *     ...
     *     final SQLTransaction tranA = JdbcUtil.beginTransaction(dataSource1, isolation);
     *
     *     try {
     *         ...
     *         doSomethingB(); // Share the same transaction 'tranA' because they're in the same thread and start transaction with same DataSource 'dataSource1'.
     *         ...
     *         doSomethingC(); // won't share the same transaction 'tranA' although they're in the same thread but start transaction with different DataSource 'dataSource2'.
     *         ...
     *         tranA.commit();
     *     } finally {
     *         tranA.rollbackIfNotCommitted();
     *     }
     * }
     *
     * public void doSomethingB() {
     *     ...
     *     final SQLTransaction tranB = JdbcUtil.beginTransaction(dataSource1, isolation);
     *     try {
     *         // do your work with the conn...
     *         ...
     *         tranB.commit();
     *     } finally {
     *         tranB.rollbackIfNotCommitted();
     *     }
     * }
     *
     * public void doSomethingC() {
     *     ...
     *     final SQLTransaction tranC = JdbcUtil.beginTransaction(dataSource2, isolation);
     *     try {
     *         // do your work with the conn...
     *         ...
     *         tranC.commit();
     *     } finally {
     *         tranC.rollbackIfNotCommitted();
     *     }
     * }
     * 

     * 
     * public void doSomethingA() {
     *     ...
     *     final SQLTransaction tranA = JdbcUtil.beginTransaction(dataSource1, isolation);
     *     boolean flagToCommit = false;
     *     try {
     *         // do your work with the conn...
     *         ...
     *         flagToCommit = true;
     *     } finally {
     *         if (flagToCommit) {
     *             tranA.commit();
     *         } else {
     *             tranA.rollbackIfNotCommitted();
     *         }
     *     }
     * }
     * 
     * 

     * 
     * JdbcUtil.prepareQuery(dataSource.getConnection(), sql);
     * 
     * 

     * 
     * JdbcUtil.prepareQuery(dataSource.getConnection(), sql, autoGeneratedKeys);
     * 
     * 

     * 
     * JdbcUtil.prepareQuery(dataSource.getConnection(), sql, returnColumnIndexes);
     * 
     * 

     * 
     * JdbcUtil.prepareQuery(dataSource.getConnection(), sql, returnColumnNames);
     * 
     * 

     * 
     * JdbcUtil.prepareQuery(dataSource.getConnection(), sql, stmtCreator);
     * 
     * 

     * 
     * JdbcUtil.prepareNamedQuery(dataSource.getConnection(), namedSql);
     * 
     * 

     * 
     * JdbcUtil.prepareNamedQuery(dataSource.getConnection(), namedSql, autoGeneratedKeys);
     * 
     * 

     * 
     * JdbcUtil.prepareNamedQuery(dataSource.getConnection(), namedSql);
     * 
     * 

     * 
     * JdbcUtil.prepareNamedQuery(dataSource.getConnection(), namedSql);
     * 
     * 

     * 
     * JdbcUtil.prepareNamedQuery(dataSource.getConnection(), namedSql, stmtCreator);
     * 
     * 

     * 
     * JdbcUtil.prepareNamedQuery(dataSource.getConnection(), namedSql);
     * 
     * 

     * 
     * JdbcUtil.prepareNamedQuery(dataSource.getConnection(), namedSql, autoGeneratedKeys);
     * 
     * 

     * 
     * JdbcUtil.prepareNamedQuery(dataSource.getConnection(), namedSql);
     * 
     * 

     * 
     * JdbcUtil.prepareNamedQuery(dataSource.getConnection(), namedSql);
     * 
     * 

     * 
     * JdbcUtil.prepareNamedQuery(dataSource.getConnection(), namedSql, stmtCreator);
     * 
     * 

     * 
     * JdbcUtil.prepareCallableQuery(dataSource.getConnection(), sql);
     * 
     * 

     * 
     * JdbcUtil.prepareCallableQuery(dataSource.getConnection(), sql, stmtCreator);
     * 
     * 

     * 
     * public static interface UserDao extends JdbcUtil.CrudDao {
     *     @NamedInsert("INSERT INTO user (id, first_name, last_name, email) VALUES (:id, :firstName, :lastName, :email)")
     *     void insertWithId(User user) throws SQLException;
     *
     *     @NamedUpdate("UPDATE user SET first_name = :firstName, last_name = :lastName WHERE id = :id")
     *     int updateFirstAndLastName(@Bind("firstName") String newFirstName, @Bind("lastName") String newLastName, @Bind("id") long id) throws SQLException;
     *
     *     @NamedSelect("SELECT first_name, last_name FROM user WHERE id = :id")
     *     User getFirstAndLastNameBy(@Bind("id") long id) throws SQLException;
     *
     *     @NamedSelect("SELECT id, first_name, last_name, email FROM user")
     *     Stream allUsers() throws SQLException;
     * }
     * 
     * 

     * 
     * static final UserDao userDao = Dao.newInstance(UserDao.class, dataSource);
     * ...
     *
     * final SQLTransaction tran = JdbcUtil.beginTransaction(dataSource, IsolationLevel.READ_COMMITTED);
     *
     * try {
     *      userDao.getById(id);
     *      userDao.update(...);
     *      // more...
     *
     *      tran.commit();
     * } finally {
     *      // The connection will be automatically closed after the transaction is committed or rolled back.
     *      tran.rollbackIfNotCommitted();
     * }
     * 
     * 

        //     * 
        //     *   final SQLTransaction tran = someDao.beginTransaction(IsolationLevel.READ_COMMITTED);
        //     *   try {
        //     *       // sqlExecutor.insert(...);
        //     *       // sqlExecutor.update(...);
        //     *       // sqlExecutor.query(...);
        //     *
        //     *       tran.commit();
        //     *   } finally {
        //     *       // The connection will be automatically closed after the transaction is committed or rolled back.
        //     *       tran.rollbackIfNotCommitted();
        //     *   }
        //     * 
        //     *