Java – Using prepared statements with JDBCTemplate

javajdbcjdbctemplateprepared-statementspring

I'm using the JDBC template and want to read from a database using prepared statements. I iterate over many lines in a .csv file, and on every line I execute some SQL select queries with corresponding values.

I want to speed up my reading from the database but I don't know how to get the JDBC template to work with prepared statements.

There is the PreparedStatementCreator and the PreparedStatementSetter. As in this example both of them are created with anonymous inner classes.
But inside the PreparedStatementSetter class I don't have access to the values I want to set in the prepared statement.

Since I'm iterating through a .csv file, I can't hard code them as a String because I don't know them.
I also can't pass them to the PreparedStatementSetter because there are no arguments for the constructor. And setting my values to final would be dumb too.

I was used to the creation of prepared statements being fairly simple. Something like

PreparedStatement updateSales = con.prepareStatement(
    "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75); 
updateSales.setString(2, "Colombian"); 
updateSales.executeUpdate():

as in this Java tutorial.

Best Answer

By default, the JDBCTemplate does its own PreparedStatement internally, if you just use the .update(String sql, Object ... args) form. Spring, and your database, will manage the compiled query for you, so you don't have to worry about opening, closing, resource protection, etc. One of the saving graces of Spring. A link to Spring 2.5's documentation on this. Hope it makes things clearer. Also, statement caching can be done at the JDBC level, as in the case of at least some of Oracle's JDBC drivers. That will go into a lot more detail than I can competently.

Related Solutions

Java – How to create an executable JAR with dependencies using Maven

<build>
  <plugins>
    <plugin>
      <artifactId>maven-assembly-plugin</artifactId>
      <configuration>
        <archive>
          <manifest>
            <mainClass>fully.qualified.MainClass</mainClass>
          </manifest>
        </archive>
        <descriptorRefs>
          <descriptorRef>jar-with-dependencies</descriptorRef>
        </descriptorRefs>
      </configuration>
    </plugin>
  </plugins>
</build>

and you run it with

mvn clean compile assembly:single

Compile goal should be added before assembly:single or otherwise the code on your own project is not included.

See more details in comments.

Commonly this goal is tied to a build phase to execute automatically. This ensures the JAR is built when executing mvn install or performing a deployment/release.

<plugin>
  <artifactId>maven-assembly-plugin</artifactId>
  <configuration>
    <archive>
      <manifest>
        <mainClass>fully.qualified.MainClass</mainClass>
      </manifest>
    </archive>
    <descriptorRefs>
      <descriptorRef>jar-with-dependencies</descriptorRef>
    </descriptorRefs>
  </configuration>
  <executions>
    <execution>
      <id>make-assembly</id> <!-- this is used for inheritance merges -->
      <phase>package</phase> <!-- bind to the packaging phase -->
      <goals>
        <goal>single</goal>
      </goals>
    </execution>
  </executions>
</plugin>

Java – How to mock void methods with Mockito

Take a look at the Mockito API docs. As the linked document mentions (Point # 12) you can use any of the doThrow(),doAnswer(),doNothing(),doReturn() family of methods from Mockito framework to mock void methods.

For example,

Mockito.doThrow(new Exception()).when(instance).methodName();

or if you want to combine it with follow-up behavior,

Mockito.doThrow(new Exception()).doNothing().when(instance).methodName();

Presuming that you are looking at mocking the setter setState(String s) in the class World below is the code uses doAnswer method to mock the setState.

World mockWorld = mock(World.class); 
doAnswer(new Answer<Void>() {
    public Void answer(InvocationOnMock invocation) {
      Object[] args = invocation.getArguments();
      System.out.println("called with arguments: " + Arrays.toString(args));
      return null;
    }
}).when(mockWorld).setState(anyString());

Best Answer

Related Solutions

Java – How to create an executable JAR with dependencies using Maven

Java – How to mock void methods with Mockito

Related Topic