A story addicted to JDBC Template placeholders

Development environment

• Java 1.8.0
• TERASOLUNA Server Framework for Java (5.4.1.RELEASE)
• Spring Framework 4.3.14.RELEASE

Review of JDBC Template

First, I will briefly explain how to use JDBC Template.

• Unrelated parts are omitted.

Add JDBC namespace to XML

context.xml

<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:jdbc="http://www.springframework.org/schema/jdbc"

  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
   http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
   ">

(Data source definition)

Necessary when defining a table or inputting data at runtime such as test data. In this example, the script for H2 written in /database/schema.sql under the classpath is loaded. For database-name, set the one set in ʻURL. If omitted, the character string set in ʻid is displayed.

context.xml

  <jdbc:embedded-database id="dataSource" type="H2" database-name="testdb">
    <jdbc:script location="classpath:/database/schema.sql" />
  </jdbc:embedded-database>

Reference: Database setting example of Logback. Match the value of this ʻurl tag with the database-name`.

logback.xml

      <dataSource class="org.apache.commons.dbcp2.BasicDataSource">
        <driverClassName>org.h2.Driver</driverClassName>
        <url>jdbc:h2:mem:testdb</url>
        <username>sa</username>
        <password></password>
      </dataSource>

Bean definition of JDBC Template

Just set the data source defined in ↑ in the constructor

context.xml

  <bean class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg ref="dataSource" />
  </bean>

How to use JDBC Template

Call with code

Simply inject

java.test.java

  @Inject
  JdbcTemplate jdbcTemplate;

Execute SQL and get the result

You can receive the result of SQL as List or Map with queryForXXX. The following is an example of getting the message of the log output by Logback.

test.java

    List<String> logginEvents = jdbcTemplate.queryForList(
        "SELECT formatted_message FROM logging_event ORDER BY timestmp", String.class);

You can also use ? To become a placeholder and bind a value. The following is an example of getting the value associated with the key "context" contained in MDC or Context.

test.java

    List<String> loggingEventProperties = jdbcTemplate.queryForList(
        "SELECT mapped_value FROM logging_event_property WHERE mapped_key=?", String.class,
        "context");

A story addicted to JDBC Template placeholders

--If you hard-code SQL, it seems to be a typo, so I want to make it a constant: disappointed_relieved:

• That's it! I also made the column name a placeholder: smiley: --Jaken, let's rewrite it right away: stuck_out_tongue_closed_eyes:

test.java

    private static final String MESSAGE_COLUM_NAME = "formatted_message";

    //Abbreviation

    List<String> logginEvents = jdbcTemplate.queryForList(
    "SELECT ? FROM logging_event ORDER BY timestmp", String.class, MESSAGE_COLUM_NAME);

Execution result……

Why formatted_message: thinking :?

Column names and table names cannot be bound

Because the binding of JDBCTemplate internally calls PreparedStatement and the placeholder of PreparedStatement only supports parameters. It seems that column names and table names cannot be bound.

• Using a prepared statement and variable bind Order By in Java with JDBC driver
• Cannot use a LIKE query in a JDBC PreparedStatement?

I've been looking for documentation and looking at it for a while with breakpoints, but I couldn't find the wording that it shouldn't be used in column names. However, considering that the character string formatted_message was returned in the example of ↑ and that it is bound without escaping? In SQL, it seems that the escape processing of the JDBC driver is related. I will.

Conclusion

If you want to bind a table name or column name, use String # format etc.

Background to the posting

I made an article that I was addicted to using JDBC Template after a long time.