Simplifying JDBC operations with the SimpleJdbc classes - Java-Springs

The SimpleJdbcInsert and SimpleJdbcCall classes provide a simplified configuration by taking advantage of database metadata that can be retrieved through the JDBC driver. This means there is less to configure up front, although you can override or turn off the metad ata processing if you prefer to provide all the details in your code.

Inserting data using SimpleJdbcInsert

Let's start by looking at the Simple Jdbc Insert class with the minimal amount of configuration options. You should instantiate the Simple Jdbc Insert in the data access layer's initialization method. For this example, the initializing method is the set Data Source method. You do not need to subclass the Simple Jdbc Insert class; simply create a new instance and set the table name using the withTableName method. Configuration methods for this class follow the "fluid" style that returns the instance of the Simple Jdbc Insert, which allows you to chain all configuration methods.This example uses only one configuration method; you will see examples of multiple ones later.

public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
this.insertActor =
new SimpleJdbcInsert(dataSource).withTableName("t_actor");
}
public void add(Actor actor) {
Map<String, Object> parameters = new HashMap<String, Object>(3);
parameters.put("id", actor.getId());
parameters.put("first_name", actor.getFirstName());
parameters.put("last_name", actor.getLastName());
insertActor.execute(parameters);
}
// ... additional methods
}

The execute method used here takes a plain java.utils.Map as its only parameter. The important thing to note here is that the keys used for the Map must match the column names of the table as defined in the database. This is because we read the metadata in order to construct the actual insert statement.

Retrieving auto-generated keys using SimpleJdbcInsert

This example uses the same insert as the preceding, but instead of passing in the id it retrieves the auto-generated key and sets it on the new Actor object. When you create the Simple Jdbc Insert, in addition to specifying the table name, you specify the name of the generated key column with the using Generated KeyC olumns method.

public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
strong>this.insertActor =
new SimpleJdbcInsert(dataSource)
.withTableName("t_actor")
.usingGeneratedKeyColumns("id");
}
public void add(Actor actor) {
Map<String, Object> parameters = new HashMap<String, Object>(2);
parameters.put("first_name", actor.getFirstName());
parameters.put("last_name", actor.getLastName());
Number newId = insertActor.executeAndReturnKey(parameters);
actor.setId(newId.longValue());
}
// ... additional methods
}

The main difference when executing the insert by this second approach is that you do not add the id to the Map and you call the execute Returning Key method.This returns a java. lang. Number object with which you can create an instance of the numerical type that is used in our domain class.You cannot rely on all databases to return a specific Java class here; java. lang. Number is the base class that you can rely on. If you have multiple auto-generated columns, or the generated values are non-numeric, then you can use a KeyHolder that is returned from the execute Returning Key Holder method.

Specifying columns for a Simple Jdbc Insert

You can limit the columns for an insert by specifying a list of column names with the usingColumns method:

public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
this.insertActor =
new SimpleJdbcInsert(dataSource)
.withTableName("t_actor")
.usingColumns("first_name", "last_name")
.usingGeneratedKeyColumns("id");
}
public void add(Actor actor) {
Map<String, Object> parameters = new HashMap<String, Object>(2);
parameters.put("first_name", actor.getFirstName());
parameters.put("last_name", actor.getLastName());
Number newId = insertActor.executeAndReturnKey(parameters);
actor.setId(newId.longValue());
}
// ... additional methods
}

The execution of the insert is the same as if you had relied on the metadata to determine which columns to use.

Using Sql Parameter Source to provide parameter values

Using a Map to provide parameter values works fine, but it's not the most convenient class to use. Spring provides a couple of implementations of the Sql Parameter Source interface that can be used instead.The first one is Bean Property Sql Parameter Source, which is a very convenient class if you have a Java Bean-compliant class that contains your values. It will use the corresponding getter method to extract the parameter values. Here is an example:

public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
this.insertActor =
new SimpleJdbcInsert(dataSource)
.withTableName("t_actor")
.usingGeneratedKeyColumns("id");
}
public void add(Actor actor) {
SqlParameterSource parameters = new BeanPropertySqlParameterSource(actor);
Number newId = insertActor.executeAndReturnKey(parameters);
actor.setId(newId.longValue());
}
// ... additional methods
}

Another option is the Map Sql Parameter Source that resembles a Map but provides a more convenient add Value method that can be chained.

public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
private SimpleJdbcInsert insertActor;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
this.insertActor =
new SimpleJdbcInsert(dataSource)
.withTableName("t_actor")
.usingGeneratedKeyColumns("id");
}
public void add(Actor actor) {
SqlParameterSource parameters = new MapSqlParameterSource()
.addValue("first_name", actor.getFirstName())
.addValue("last_name", actor.getLastName());
Number newId = insertActor.executeAndReturnKey(parameters);
actor.setId(newId.longValue());
}
// ... additional methods
}

As you can see, the configuration is the same; only the executing code has to change to use these alternative input classes.

Calling a stored procedure with SimpleJdbcCall

The SimpleJdbcCall class leverages metadata in the database to look up names of in and out parameters, so that you do not have to declare them explicitly. You can declare parameters if you prefer to do that, or if you have parameters such as ARRAY or STRUCT that do not have an automatic mapping to a Java class. The first example shows a simple procedure that returns only scalar values in VARCHAR and DATE format from a MySQL database. The example procedure reads a specified actor entry and returns first_name, last_name, and birth_date columns in the form of out parameters.

CREATE PROCEDURE read_actor (
IN in_id INTEGER,
OUT out_first_name VARCHAR(100),
OUT out_last_name VARCHAR(100),
OUT out_birth_date DATE)
BEGIN
SELECT first_name, last_name, birth_date
INTO out_first_name, out_last_name, out_birth_date
FROM t_actor where id = in_id;
END;

The in_id parameter contains the id of the actor you are looking up. The out parameters return the data read from the table.

The Simple Jdbc Call is declared in a similar manner to the SimpleJdbcInsert. You should instantiate and configure the class in the initialization method of your data access layer.Compared to the StoredProcedure class, you don't have to create a subclass and you don't have to declare parameters that can be looked up in the data base metadata. Following is an example of a Simple Jdbc Call configuration using the above stored procedure.The only configuration option, in addition to the DataSource, is the name of the stored procedure.

public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
private SimpleJdbcCall procReadActor;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
this.procReadActor =
new SimpleJdbcCall(dataSource)
.withProcedureName("read_actor");
}
public Actor readActor(Long id) {
SqlParameterSource in = new MapSqlParameterSource()
.addValue("in_id", id);
Map out = procReadActor.execute(in);
Actor actor = new Actor();
actor.setId(id);
actor.setFirstName((String) out.get("out_first_name"));
actor.setLastName((String) out.get("out_last_name"));
actor.setBirthDate((Date) out.get("out_birth_date"));
return actor;
}
// ... additional methods
}

The code you write for the execution of the call involves creating an Sql Parameter Source containing the IN parameter. It's important to match the name provided for the input value with that of the parameter name declared in the stored procedure.The case does not have to match because you use metadata to determine how database objects should be referred to in a stored procedure. What is specified in the source for the stored procedure is not necessarily the way it is stored in the database. Some databases transform names to all upper case while others use lower case or use the case as specified.

The execute method takes the IN parameters and returns a Map containing any out parameters keyed by the name as specified in the stored procedure.In this case they are out_first_name, out_last_name and out_birth_date.

The last part of the execute method creates an Actor instance to use to return the data retrieved. Again, it is important to use the names of the out parameters as they are declared in the stored procedure. Also, the case in the names of the out parameters stored in the results map matches that of the out parameter names in the database, which could vary between databases.To make your code more portable you should do a case-insensitive lookup or instruct Spring to use a CaseInsensitive Map from the Jakarta Commons project.To do the latter, you create your own Jdbc Template and set the set Results Map Case Insensitive property to true.Then you pass this customized Jdbc Template instance into the constructor of your SimpleJdbcCall. You must include the commons-collections.jar in your classpath for this to work. Here is an example of this configuration:

public class JdbcActorDao implements ActorDao {
private SimpleJdbcCall procReadActor;
public void setDataSource(DataSource dataSource) {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setResultsMapCaseInsensitive(true);
this.procReadActor =
new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("read_actor");
}
// ... additional methods
}

By taking this action, you avoid conflicts in the case used for the names of your returned out parameters.

Explicitly declaring parameters to use for a SimpleJdbcCall

You have seen how the parameters are deduced based on metadata, but you can declare then explicitly if you wish. You do this by creating and configuring Simple Jdbc Call with the declare Parameters method, which takes a variable number of Sql Parameter objects as input.

You can opt to declare one, some, or all the parameters explicitly.The parameter metadata is still used where you do not declare parameters explicitly.To bypass all processing of metadata lookups for potential parameters and only use the declared parameters, you call the method without Procedure Column Meta Data Access as part of the declaration. Suppose that you have two or more different call signatures declared for a database function. In this case you call the useInParameterNames to specify the list of IN parameter names to include for a given signature.

The following example shows a fully declared procedure call, using the information from the preceding example.

public class JdbcActorDao implements ActorDao {
private SimpleJdbcCall procReadActor;
public void setDataSource(DataSource dataSource) {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setResultsMapCaseInsensitive(true);
this.procReadActor =
new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("read_actor")
.withoutProcedureColumnMetaDataAccess()
.useInParameterNames("in_id")
.declareParameters(
new SqlParameter("in_id", Types.NUMERIC),
new SqlOutParameter("out_first_name", Types.VARCHAR),
new SqlOutParameter("out_last_name", Types.VARCHAR),
new SqlOutParameter("out_birth_date", Types.DATE)
);
}
// ... additional methods
}

The execution and end results of the two examples are the same; this one specifies all details explicitly rather than relying on metadata.

How to define SqlParameters

To define a parameter for the SimpleJdbc classes and also for the RDBMS operations classes, you use an SqlParameter or one of its subclasses. You typically specify the parameter name and SQL type in the constructor. The SQL type is specified using the java.sql.Types constants. We have already seen declarations like:

new SqlParameter("in_id", Types.NUMERIC),new SqlOutParameter("out_first_name", Types.VARCHAR),

The first line with the SqlParameter declares an IN parameter. IN parameters can be used for both stored procedure calls and for queries using the Sql Query and its subclasses.

The second line with the Sql Out Parameter declares an out parameter to be used in a stored procedure call. There is also an Sql In OutP arameter for In Out parameters, parameters that provide an IN value to the procedure and that also return a value.

For IN parameters, in addition to the name and the SQL type, you can specify a scale for numeric data or a type name for custom database types. For out parameters, you can provide a Row Mapper to handle mapping of rows returned from a REF cursor. Another option is to specify an Sql ReturnType that provides an opportunity to define customized handling of the return values.

Calling a stored function using SimpleJdbcCall

You call a stored function in almost the same way as you call a stored procedure, except that you provide a function name rather than a procedure name. You use the withFunctionName method as part of the configuration to indicate that we want to make a call to a function, and the corresponding string for a function call is generated. A specialized execute call, executeFunction, is used to execute the function and it returns the function return value as an object of a specified type, which means you do not have to retrieve the return value from the results map. A similar convenience method named executeObject is also available for stored procedures that only have one out parameter. The following example is based on a stored function named get_actor_name that returns an actor's full name. Here is the MySQL source for this function:

CREATE FUNCTION get_actor_name (in_id INTEGER)
RETURNS VARCHAR(200) READS SQL DATA
BEGIN
DECLARE out_name VARCHAR(200);
SELECT concat(first_name, ' ', last_name)
INTO out_name
FROM t_actor where id = in_id;
RETURN out_name;
END;

To call this function we again create a SimpleJdbcCall in the initialization method.

public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
private SimpleJdbcCall funcGetActorName;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setResultsMapCaseInsensitive(true);
this.funcGetActorName =
new SimpleJdbcCall(jdbcTemplate)
.withFunctionName("get_actor_name");
}
public String getActorName(Long id) {
SqlParameterSource in = new MapSqlParameterSource()
.addValue("in_id", id);
String name = funcGetActorName.executeFunction(String.class, in);
return name;
}
// ... additional methods
}

The execute method used returns a String containing the return value from the function call.

Returning ResultSet/REF Cursor from a SimpleJdbcCall

Calling a stored procedure or function that returns a result set is a bit tricky. Some databases return result sets during the JDBC results processing while others require an explicitly registered out parameter of a specific type. Both approaches need additional processing to loop over the result set and process the returned rows. With the SimpleJdbcCall you use the returningResultSet method and declare a Row Mapper implementation to be used for a specific parameter.

In the case where the result set is returned during the results processing, there are no names defined, so the returned results will have to match the order in which you declare the Row Mapper implementations.The name specified is still used to store the processed list of results in the results map that is returned from the execute statement.The next example uses a stored procedure that takes no IN parameters and returns all rows from the t_actor table. Here is the MySQL source for this procedure:

CREATE PROCEDURE read_all_actors()
BEGIN
SELECT a.id, a.first_name, a.last_name, a.birth_date FROM t_actor a;
END;

To call this procedure you declare the Row Mapper. Because the class you want to map to follows the Java Bean rules, you can use a Parameterized Bean Property Row Mapper that is created by passing in the required class to map to in the new Instance method.

public class JdbcActorDao implements ActorDao {
private SimpleJdbcTemplate simpleJdbcTemplate;
private SimpleJdbcCall procReadAllActors;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.setResultsMapCaseInsensitive(true);
this.procReadAllActors =
new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("read_all_actors")
.returningResultSet("actors",
ParameterizedBeanPropertyRowMapper.newInstance(Actor.class));
}
public List getActorsList() {
Map m = procReadAllActors.execute(new HashMap<String, Object>(0));
return (List) m.get("actors");
}
// ... additional methods
}

The execute call passes in an empty Map because this call does not take any parameters. The list of Actors is then retrieved from the results map and returned to the caller.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Java-Springs Topics