Using the JDBC Core Classes to Control Basic JDBC Processing and Error Handling

// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void setDataSource(DataSource dataSource) {
	this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}

public int countOfActorsByFirstName(String firstName) {
	String sql = "select count(*) from t_actor where first_name = :first_name";
	SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);
	return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}

private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)

fun countOfActorsByFirstName(firstName: String): Int {
	val sql = "select count(*) from t_actor where first_name = :first_name"
	val namedParameters = MapSqlParameterSource("first_name", firstName)
	return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Int::class.java)!!
}

// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void setDataSource(DataSource dataSource) {
	this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}

public int countOfActorsByFirstName(String firstName) {
	String sql = "select count(*) from t_actor where first_name = :first_name";
	Map<String, String> namedParameters = Collections.singletonMap("first_name", firstName);
	return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}

// some JDBC-backed DAO class...
private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)

fun countOfActorsByFirstName(firstName: String): Int {
	val sql = "select count(*) from t_actor where first_name = :first_name"
	val namedParameters = mapOf("first_name" to firstName)
	return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Int::class.java)!!
}

public class Actor {

	private Long id;
	private String firstName;
	private String lastName;

	public String getFirstName() {
		return this.firstName;
	}

	public String getLastName() {
		return this.lastName;
	}

	public Long getId() {
		return this.id;
	}

	// setters omitted...
}

data class Actor(val id: Long, val firstName: String, val lastName: String)

// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void setDataSource(DataSource dataSource) {
	this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}

public int countOfActors(Actor exampleActor) {
	// notice how the named parameters match the properties of the above 'Actor' class
	String sql = "select count(*) from t_actor where first_name = :firstName and last_name = :lastName";
	SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
	return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}

// some JDBC-backed DAO class...
private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)

private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)

fun countOfActors(exampleActor: Actor): Int {
	// notice how the named parameters match the properties of the above 'Actor' class
	val sql = "select count(*) from t_actor where first_name = :firstName and last_name = :lastName"
	val namedParameters = BeanPropertySqlParameterSource(exampleActor)
	return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Int::class.java)!!
}

public class CustomSQLErrorCodesTranslator extends SQLErrorCodeSQLExceptionTranslator {

	protected DataAccessException customTranslate(String task, String sql, SQLException sqlEx) {
		if (sqlEx.getErrorCode() == -12345) {
			return new DeadlockLoserDataAccessException(task, sqlEx);
		}
		return null;
	}
}

class CustomSQLErrorCodesTranslator : SQLErrorCodeSQLExceptionTranslator() {

	override fun customTranslate(task: String, sql: String?, sqlEx: SQLException): DataAccessException? {
		if (sqlEx.errorCode == -12345) {
			return DeadlockLoserDataAccessException(task, sqlEx)
		}
		return null
	}
}

private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource) {
	// create a JdbcTemplate and set data source
	this.jdbcTemplate = new JdbcTemplate();
	this.jdbcTemplate.setDataSource(dataSource);

	// create a custom translator and set the DataSource for the default translation lookup
	CustomSQLErrorCodesTranslator tr = new CustomSQLErrorCodesTranslator();
	tr.setDataSource(dataSource);
	this.jdbcTemplate.setExceptionTranslator(tr);
}

public void updateShippingCharge(long orderId, long pct) {
	// use the prepared JdbcTemplate for this update
	this.jdbcTemplate.update("update orders" +
		" set shipping_charge = shipping_charge * ? / 100" +
		" where id = ?", pct, orderId);
}

// create a JdbcTemplate and set data source
private val jdbcTemplate = JdbcTemplate(dataSource).apply {
	// create a custom translator and set the DataSource for the default translation lookup
	exceptionTranslator = CustomSQLErrorCodesTranslator().apply {
		this.dataSource = dataSource
	}
}

fun updateShippingCharge(orderId: Long, pct: Long) {
	// use the prepared JdbcTemplate for this update
	this.jdbcTemplate!!.update("update orders" +
			" set shipping_charge = shipping_charge * ? / 100" +
			" where id = ?", pct, orderId)
}

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))")
	}
}

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")
}

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")
}

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)
	}
}

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