[JAVA] A story addicted to JDBC Template placeholders

Development environment

Review of JDBC Template

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

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:

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.

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.

Recommended Posts

A story addicted to JDBC Template placeholders
A story I was addicted to in Rails validation settings
A story addicted to toString () of Interface proxied with JdkDynamicAopProxy
How to make a JDBC driver
[Circle CI] A story I was addicted to at Start Building
[rails] How to create a partial template
A story I was addicted to when testing the API using MockMVC
A story that took time to establish a connection
A story about trying to operate JAVA File
A story I was addicted to when getting a key that was automatically tried on MyBatis
How to create a JDBC URL (Oracle Database, Thin)
A story about trying to get along with Mockito
A story about trying hard to decompile JAR files
A story about reducing memory consumption to 1/100 with find_in_batches
A story I was addicted to before building a Ruby and Rails environment using Ubuntu (20.04.1 LTS)
A story I was addicted to with implicit type conversion of ActiveRecord during unit testing
A story about misunderstanding how to use java scanner (memo)
The story I was addicted to when setting up STS