Skip to main content
Hint

This article is generated by AI translation.

Advanced Features

This section introduces some advanced features and related parameters of JdbcTemplate:

Print Error Logs and SQL​

Set printStmtError to print the failing SQL when an SQL error occurs.

  • Property: printStmtError.
  • Default: false, meaning logs are not printed.
Example with printStmtError = true
Failed SQL statement [select * from test_user2 where id > 10].
java.sql.SQLSyntaxErrorException: Table 'devtester.test_user2' doesn't exist
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110)
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
	at net.hasor.dbvisitor.jdbc.core.JdbcTemplate.lambda$query$14(JdbcTemplate.java:468)
	at net.hasor.dbvisitor.jdbc.core.JdbcTemplate.lambda$execute$0(JdbcTemplate.java:240)
	at net.hasor.dbvisitor.jdbc.core.JdbcConnection.execute(JdbcConnection.java:162)
	at net.hasor.dbvisitor.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:237)
	at net.hasor.dbvisitor.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:467)
	at net.hasor.dbvisitor.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:581)
	at com.example.demo.jdbc.Execute1SqlMain.main(Execute1SqlMain.java:24)

Database Warning Messages​

By setting ignoreWarnings, dbVisitor will throw an exception on SQL warnings.

  • Property: ignoreWarnings.
  • Default: true, meaning SQL warnings are ignored.
SQL that triggers a MySQL 8.0 warning
create table test_table (
    id   bigint,
    name national char(10)
)
Warning content
NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. 
Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
With ignoreWarnings = false
Exception in thread "main" java.sql.SQLException: Warning not ignored
	at net.hasor.dbvisitor.jdbc.core.JdbcConnection.handleWarnings(JdbcConnection.java:225)
	at net.hasor.dbvisitor.jdbc.core.JdbcConnection.lambda$execute$0(JdbcConnection.java:182)
	at net.hasor.dbvisitor.jdbc.core.JdbcConnection.execute(JdbcConnection.java:162)
	at net.hasor.dbvisitor.jdbc.core.JdbcConnection.execute(JdbcConnection.java:176)
	at net.hasor.dbvisitor.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:229)
	at com.example.demo.jdbc.Execute1SqlMain.main(Execute1SqlMain.java:20)
Caused by: java.sql.SQLWarning: NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.
	at com.mysql.cj.protocol.a.NativeProtocol.convertShowWarningsToSQLWarnings(NativeProtocol.java:2204)
	at com.mysql.cj.jdbc.StatementImpl.getWarnings(StatementImpl.java:1733)
	at com.zaxxer.hikari.pool.HikariProxyStatement.getWarnings(HikariProxyStatement.java)
	at net.hasor.dbvisitor.jdbc.core.JdbcConnection.handleWarnings(JdbcConnection.java:223)
	... 5 more

Query Timeout​

Set queryTimeout to a value greater than zero to set a timeout on the Statement when JdbcTemplate executes a query, e.g., java.sql.Statement.setQueryTimeout.

  • Property: queryTimeout.
  • Default: 0, meaning not set.
  • Unit: seconds.
jdbc.setQueryTimeout(6);// This setting affects all subsequent queries
jdbc.queryForList("select * from users");

Query Result fetchSize​

Set fetchSize to a value greater than zero to provide a hint to the JDBC driver through the Statement when JdbcTemplate executes a query. It hints at the number of rows to fetch from the database when the ResultSet object generated by this Statement needs more rows. The specified row count only affects result sets created by this statement, e.g., java.sql.Statement.setFetchSize.

  • Property: fetchSize.
  • Default: 0, meaning not set.
  • Unit: rows.
jdbc.setFetchSize(256);
jdbc.queryForList("select * from users");
This parameter is usually affected by the database driver
MySQL streaming
PreparedStatement ps = con.prepareStatement(
        "select * from test_user",
        ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_READ_ONLY
    );
ps.setFetchSize(Integer.MIN_VALUE);
Postgres 7.4+
con.setAutoCommit(false);
PreparedStatement ps = con.prepareStatement(
        "select * from test_user",
        ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_READ_ONLY
    );
ps.setFetchSize(255);

maxRows​

Set maxRows to a value greater than zero to provide a hint to the JDBC driver through the Statement when JdbcTemplate executes a query. It limits the maximum number of rows returned from the ResultSet.

For example, setting maxRows to 100 will only fetch the first 100 rows from the ResultSet and ignore the rest.

  • Property: maxRows.
  • Default: 0, meaning not set.
  • Unit: rows.
jdbc.setMaxRows(200);// This setting affects all subsequent queries
jdbc.queryForList("select * from users");
Note

In some database drivers, setting maxRows does not mean that the query will immediately terminate after fetching the specified number of rows.

  • For example, MySQL will still consume the extra rows returned by the server after fetching the maxRows number of result rows, but these data will not be added to the result set.

Dynamic Data Sources​

For some reasons, it may not be possible to provide a DataSource or Connection when creating JdbcTemplate, or JdbcTemplate's methods may operate on different DataSource or Connection when called. In such cases, use the DynamicConnection dynamic data source interface instead.

Custom DynamicConnection
class LocalDynamicConnection implements DynamicConnection {
    @Override
    public Connection getConnection() throws SQLException {
        return ...;// Get Connection for queries
    }

    @Override
    public void releaseConnection(Connection conn) throws SQLException {
        // Release Connection after query
    }
}
Use the dynamic source
JdbcTemplate jdbc = new JdbcTemplate(new LocalDynamicConnection());
Extended Information

SpringDsAdapter uses Spring's DataSourceUtils to obtain and release data source connections as the DynamicConnection implementation logic. This allows dbVisitor and Spring JDBC's transaction manager to work together cooperatively.

Dependencies required (choose as needed):