Executing Instructions

It doesn't take much code to execute an SQL statement. Requires DataSource and JdbcTemplate, including the helper methods that are provided with JdbcTemplate. The following example shows what should be included in a minimal but fully functional class that creates a new table:

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class ExecuteAStatement {
    private JdbcTemplate jdbcTemplate;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    public void doExecute() {
        this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
    }
}

import javax.sql.DataSource
import org.springframework.jdbc.core.JdbcTemplate
class ExecuteAStatement(dataSource: DataSource) {
    private val jdbcTemplate = JdbcTemplate(dataSource)
    fun doExecute() {
        jdbcTemplate.execute("create table mytable (id integer, name varchar(100))")
    }
}

Running queries

Some query methods return a single value. To get a counter or a specific value from a single line, use queryForObject(..). The latter converts the returned Type from JDBC to the Java class passed as an argument. If the type conversion is invalid, an InvalidDataAccessApiUsageException exception will be thrown. The following example contains two query methods, one for int and the other for String:

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class RunAQuery {
    private JdbcTemplate jdbcTemplate;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    public int getCount() {
        return this.jdbcTemplate.queryForObject("select count(*) from mytable", Integer.class);
    }
    public String getName() {
        return this.jdbcTemplate.queryForObject("select name from mytable", String.class);
    }
}

import javax.sql.DataSource
import org.springframework.jdbc.core.JdbcTemplate
class RunAQuery(dataSource: DataSource) {
    private val jdbcTemplate = JdbcTemplate(dataSource)
    val count: Int
        get() = jdbcTemplate.queryForObject("select count(*) from mytable")!!
    val name: String?
        get() = jdbcTemplate.queryForObject("select name from mytable")
}

In addition to the single-result query methods, several methods return a list with an entry for each row that the query returned. The most typed method is queryForList(..), which returns a List, where each element is a Map containing one entry for each column, using column name as key. If you add a method to the previous example to get a list of all strings, it might look like this:

private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public List<Map<String, Object>> getList() {
    return this.jdbcTemplate.queryForList("select * from mytable");
}

private val jdbcTemplate = JdbcTemplate(dataSource)
fun getList(): List<Map<String, Any>> {
    return jdbcTemplate.queryForList("select * from mytable")
}

The resulting list will look like this:

[{name=Bob, id=1}, {name=Mary, id=2}]

Database update

The following example updates a column for a specific primary key:

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class ExecuteAnUpdate {
    private JdbcTemplate jdbcTemplate;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    public void setName(int id, String name) {
        this.jdbcTemplate.update("update mytable set name = ? where id = ?, name, id);
    }
}

import javax.sql.DataSource
import org.springframework.jdbc.core.JdbcTemplate
class ExecuteAnUpdate(dataSource: DataSource) {
    private val jdbcTemplate = JdbcTemplate(dataSource)
    fun setName(id: Int, name: String) {
        jdbcTemplate.update("update mytable set name = ? where id = ?, name, id)
    }
}

In the previous example, the SQL statement contains placeholders for string parameters. You can pass parameter values as variable length arguments (varargs) or, alternatively, as an array of objects. Thus, you need to explicitly wrap primitive types in primitive type wrapper classes, or use autoboxing.

Getting automatically generated keys

The update() helper method supports retrieving the primary keys generated by the database. This support is part of the JDBC 3.0 standard. For details, see Chapter 13.6 of the specification. The method takes PreparedStatementCreator as the first argument, and this is how the required insertion statement is specified. The other argument is KeyHolder, which contains the generated key when the update returns successfully. There is no single standard way to create a corresponding PreparedStatement (which explains why the method signature is the way it is). The following example works on Oracle, but may not work on other platforms:

final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
    PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] { "id" });
    ps.setString(1, name);
    return ps;
}, keyHolder);
// keyHolder.getKey() now contains the generated key

val INSERT_SQL = "insert into my_test (name) values(?)"
val name = "Rob"
val keyHolder = GeneratedKeyHolder()
jdbcTemplate.update({
    it.prepareStatement(INSERT_SQL, arrayOf("id")).apply { setString(1, name) }
}, keyHolder)
// keyHolder.getKey() now contains the generated key