Simplificando operaciones JDBC con clases SimpleJdbc

Las clases SimpleJdbcInsert y SimpleJdbcCall proporcionan una configuración simplificada utilizando metadatos de la base de datos que se pueden obtener a través del controlador JDBC. Esto significa menos trabajo de configuración, aunque puede anular o deshabilitar el procesamiento de metadatos si prefiere proporcionar detalles en su código.

Insertar datos usando SimpleJdbcInsert

Vamos Comience mirando la clase SimpleJdbcInsert con un número mínimo de parámetros de configuración. Debe crear una instancia de SimpleJdbcInsert en el método de inicialización de la capa de acceso a datos. En este ejemplo, el método de inicialización es el método setDataSource. No es necesario crear una subclase de la clase SimpleJdbcInsert. En su lugar, puede crear una nueva instancia y establecer el nombre de la tabla usando el método withTableName. Los métodos de configuración para esta clase siguen el estilo fluid, que devuelve una instancia de SimpleJdbcInsert, lo que permite encadenar todos los métodos de configuración. El siguiente ejemplo utiliza solo un método de configuración (más adelante se mostrarán ejemplos de múltiples métodos):

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource 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);
    }
    // ... métodos adicionales
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val insertActor = SimpleJdbcInsert(dataSource).withTableName("t_actor")
    fun add(actor: Actor) {
        val parameters = mutableMapOf<String, Any>()
        parameters["id"] = actor.id
        parameters["first_name"] = actor.firstName
        parameters["last_name"] = actor.lastName
        insertActor.execute(parameters)
    }
    // ... métodos adicionales
}

Usado aquí el método execute toma el habitual java.util.Map como único parámetro. Es importante tener en cuenta que las claves utilizadas para el Map deben coincidir con los nombres de las columnas de la tabla tal como se definen en la base de datos. Esto ocurre porque los metadatos se leen para crear la declaración de inserción real.

Obtener claves generadas automáticamente usando SimpleJdbcInsert

El siguiente ejemplo utiliza la misma inserción que Igual que en el ejemplo anterior, pero en lugar de pasar id, recupera automáticamente la clave generada y luego la configura para un nuevo objeto Actor. Cuando crea un SimpleJdbcInsert, además de especificar el nombre de la tabla, la inserción especifica el nombre de la columna de clave generada utilizando el método usingGeneratedKeyColumns. La siguiente lista muestra cómo se hace esto:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource dataSource) {
        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());
    }
    // ... métodos adicionales
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor").usingGeneratedKeyColumns("id")
    fun add(actor: Actor): Actor {
        val parameters = mapOf(
                "first_name" to actor.firstName,
                "last_name" to actor.lastName)
        val newId = insertActor.executeAndReturnKey(parameters);
        return actor.copy(id = newId.toLong())
    }
// ... métodos adicionales
}

La principal diferencia al realizar una inserción usando el El segundo enfoque es que no agrega id a Map, sino que llama al método executeAndReturnKey. Esto devuelve un objeto java.lang.Number que se puede utilizar para crear una instancia del tipo numérico utilizado en la clase de dominio. Aquí no puede confiar en que todas las bases de datos devuelvan una clase Java específica. java.lang.Number es la clase principal que se puede utilizar. Si hay varias columnas generadas automáticamente o los valores generados no son numéricos, entonces puede usar el KeyHolder devuelto por el método executeAndReturnKeyHolder.

Establezca las columnas para SimpleJdbcInsert

Puede limitar las columnas que se insertarán especificando una lista de nombres de columnas usando el método usingColumns, como se muestra en la siguiente ejemplo:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource 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());
    }
    // ... métodos adicionales
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor")
            .usingColumns("first_name", "last_name")
            .usingGeneratedKeyColumns("id")
    fun add(actor: Actor): Actor {
        val parameters = mapOf(
                "first_name" to actor.firstName,
                "last_name" to actor.lastName)
        val newId = insertActor.executeAndReturnKey(parameters);
        return actor.copy(id = newId.toLong())
    }
    // ... métodos adicionales
}

El proceso de inserción es el mismo que si recurrió a metadatos para determinar qué columnas usar.

Usar SqlParameterSource para especificar valores de parámetros

Usar Map para especificar valores de parámetros Funciona bastante bien, pero no es la clase más fácil de usar. Spring contiene varias implementaciones de la interfaz SqlParameterSource que se pueden usar en su lugar. La primera es BeanPropertySqlParameterSource, que es una clase muy útil si tiene una clase compatible con JavaBean que contenga sus valores. Utiliza el captador apropiado para recuperar los valores de los parámetros. El siguiente ejemplo muestra cómo utilizar BeanPropertySqlParameterSource:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource 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());
    }
    // ... métodos adicionales
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor")
            .usingGeneratedKeyColumns("id")
    fun add(actor: Actor): Actor {
        val parameters = BeanPropertySqlParameterSource(actor)
        val newId = insertActor.executeAndReturnKey(parameters)
        return actor.copy(id = newId.toLong())
    }
    // ... métodos adicionales
}

Otra opción es MapSqlParameterSource, que es similar a Map pero proporciona un método addValue más conveniente que se puede encadenar. El siguiente ejemplo muestra cómo usarlo:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource 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());
    }
    // ... métodos adicionales
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor")
            .usingGeneratedKeyColumns("id")
    fun add(actor: Actor): Actor {
        val parameters = MapSqlParameterSource()
                    .addValue("first_name", actor.firstName)
                    .addValue("last_name", actor.lastName)
        val newId = insertActor.executeAndReturnKey(parameters)
        return actor.copy(id = newId.toLong())
    }
    // ... métodos adicionales
}

Como puede ver, la configuración es la misma. Para utilizar estas clases de entrada alternativas, solo necesita cambiar el código de ejecución.

Llamar a un procedimiento almacenado con SimpleJdbcCall

El La clase SimpleJdbcCall utiliza metadatos en la base de datos para buscar los nombres de los parámetros in y out, por lo que no es necesario declararlos explícitamente. Puede declarar parámetros si prefiere hacerlo o si tiene parámetros (como ARRAY o STRUCT) que no se asignan automáticamente a la clase Java. El primer ejemplo muestra un procedimiento simple que devuelve solo valores escalares en el formato VARCHAR y DATE de una base de datos MySQL. El procedimiento de ejemplo lee el registro de actor especificado y devuelve las columnas first_name, last_name y birth_date como parámetros out. El siguiente listado muestra el último ejemplo:

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;

El parámetro in_id contiene el id del actor que está buscando. Los parámetros out devuelven datos leídos de la tabla.

Puede declarar SimpleJdbcCall de manera similar a SimpleJdbcInsert. Debe crear una instancia y configurar la clase en el método de inicialización de su capa de acceso a datos. En comparación con la clase StoredProcedure, no es necesario crear subclases y declarar parámetros que se pueden buscar en los metadatos de la base de datos. El siguiente ejemplo de configuración SimpleJdbcCall utiliza el procedimiento almacenado anterior (el único parámetro de configuración distinto de DataSource es el nombre del procedimiento almacenado):

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcCall procReadActor;
    public void setDataSource(DataSource 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;
    }
    // ... métodos adicionales
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val procReadActor = SimpleJdbcCall(dataSource)
            .withProcedureName("read_actor")
    fun readActor(id: Long): Actor {
        val source = MapSqlParameterSource().addValue("in_id", id)
        val output = procReadActor.execute(source)
        return Actor(
                id,
                output["out_first_name"] as String,
                output["out_last_name"] as String,
                output["out_birth_date"] as Date)
    }
        // ... métodos adicionales
}

El código que escribe para realizar la llamada implica la creación de un SqlParameterSource que contiene el parámetro IN. Debe hacer coincidir el nombre especificado para el valor de entrada con el nombre del parámetro declarado en el procedimiento almacenado. No es necesario que coincidan las mayúsculas y minúsculas porque se utilizan metadatos para determinar cómo se debe hacer referencia a los objetos de la base de datos en el procedimiento almacenado. Lo que se especifica en el código fuente de un procedimiento almacenado no es necesariamente lo mismo que se almacena en la base de datos. Algunas bases de datos convierten los nombres a mayúsculas, mientras que otras usan minúsculas o usan un caso específico.

El método execute toma parámetros IN y devuelve un Map que contiene cualquier parámetro out, siendo los clave los nombres especificados en el procedimiento almacenado. En este caso, estos son out_first_name, out_last_name y out_birth_date.

La última parte de la execute El método crea una instancia Actor que se utilizará para devolver los datos recibidos. Nuevamente, es importante utilizar los nombres de los parámetros out tal como están declarados en el procedimiento almacenado. Además, el caso de los nombres de los parámetros out almacenados en el mapa resultante es el mismo que el caso de los nombres de los parámetros out en la base de datos, que pueden diferir entre bases de datos. Para hacer que el código sea más independiente de la plataforma, puede realizar búsquedas que no distingan entre mayúsculas y minúsculas o decirle a Spring que use LinkedCaseInSENSENTIMap. Para lograr esto último, puede crear su propio JdbcTemplate y establecer la propiedad setResultsMapCaseInSENSITIVE en true. Luego puede pasar esta instancia JdbcTemplate personalizada al constructor de su SimpleJdbcCall. El siguiente ejemplo muestra esta configuración:

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");
    }
    // ... métodos adicionales
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private var procReadActor = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
        isResultsMapCaseInsensitive = true
    }).withProcedureName("read_actor")
    // ... métodos adicionales
}

Al hacer esto, evitará conflictos en el caso utilizado para los nombres de sus parámetros return.

Declarar explícitamente parámetros para su uso en SimpleJdbcCall

Anteriormente en este capítulo describimos cómo se infieren los parámetros a partir de los metadatos, pero puedes declararlos explícitamente si lo deseas. Puede hacer esto creando y configurando un SimpleJdbcCall usando el método declareParameters, que toma un número variable de objetos SqlParameter como entrada. Para obtener más información sobre cómo definir SqlParameter, consulte la siguiente sección.

Puede declarar explícitamente uno, más o todos los parámetros. Los metadatos de parámetros todavía se utilizan cuando los parámetros no se declaran explícitamente. Para omitir todo el procesamiento de búsqueda de metadatos para parámetros potenciales y usar solo los parámetros declarados, puede llamar al método withoutProcedureColumnMetaDataAccess como parte de la declaración. Supongamos que se declaran dos o más firmas de llamada diferentes para una función de base de datos. En este caso, se llama a useInParameterNames para especificar una lista de nombres de parámetros IN que se incluirán en una firma determinada.

El siguiente ejemplo muestra una llamada a procedimiento completamente declarada y utiliza información del ejemplo anterior:

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)
                );
    }
    // ... métodos adicionales
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
        private val procReadActor = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
            isResultsMapCaseInsensitive = true
        }).withProcedureName("read_actor")
                .withoutProcedureColumnMetaDataAccess()
                .useInParameterNames("in_id")
                .declareParameters(
                        SqlParameter("in_id", Types.NUMERIC),
                        SqlOutParameter("out_first_name", Types.VARCHAR),
                        SqlOutParameter("out_last_name", Types.VARCHAR),
                        SqlOutParameter("out_birth_date", Types.DATE)
    )
        // ... métodos adicionales
}

La ejecución y los resultados finales son los mismos en estos dos ejemplos. En el segundo ejemplo, todos los detalles se especifican explícitamente, en lugar de utilizar metadatos.

Formas de definir SqlParameters

Para defina un parámetro para las clases SimpleJdbc, así como para las clases de operación RDBMS (discutidas en "Modelado Operaciones JDBC como objetos Java ") puede utilizar SqlParameter o una de sus subclases. Para hacer esto, generalmente especifica el nombre del parámetro y el tipo SQL en el constructor. El tipo SQL se especifica utilizando las constantes java.sql.Types. Anteriormente en este capítulo, vimos declaraciones similares a las siguientes:

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

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

La primera línea con SqlParameter declara el parámetro IN. Puede usar parámetros IN tanto para llamadas a procedimientos almacenados como para consultas usando SqlQuery y sus subclases (que se analizan en la sección "Introducción a SqlQuery").

La segunda línea (con SqlOutParameter) declara out parámetro a utilizar en la llamada al procedimiento almacenado. También hay un SqlInOutParameter para los parámetros InOut (parámetros que proporcionan un procedimiento con un valor IN y que también devuelven un valor).

Para los parámetros IN, además del nombre y el tipo de SQL, puede especificar un conjunto para datos numéricos o un nombre de tipo para tipos de bases de datos creados de forma personalizada. Para los parámetros out, puede especificar un RowMapper para manejar la visualización de las filas devueltas por el cursor REF. Otra opción es especificar un SqlReturnType, que le permite definir un manejo personalizado de los valores de retorno.

Llamar a una función almacenada con SimpleJdbcCall

Llamar Puede definir una función almacenada de la misma manera que puede definir un procedimiento almacenado, excepto que especifica el nombre de la función en lugar del nombre del procedimiento. El método withFunctionName se utiliza como parte de la configuración para indicar que es necesario realizar una llamada a la función y luego se genera la cadena adecuada para llamar a la función. Para ejecutar una función, utiliza una llamada (executeFunction) especializada que devuelve el valor de retorno de la función como un objeto del tipo especificado, lo que significa que no necesita obtener el valor de retorno del mapa resultante. Un método auxiliar similar (llamado executeObject) también está disponible para procedimientos almacenados que tienen solo un parámetro out. El siguiente ejemplo (para MySQL) se basa en la función almacenada get_actor_name, que devuelve el nombre completo del actor:

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;

Para llamar a esta función, volvemos a crear un SimpleJdbcCall en el método de inicialización, como se muestra en el siguiente ejemplo:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcCall funcGetActorName;
    public void setDataSource(DataSource 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;
    }
    // ... métodos adicionales
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val jdbcTemplate = JdbcTemplate(dataSource).apply {
        isResultsMapCaseInsensitive = true
    }
    private val funcGetActorName = SimpleJdbcCall(jdbcTemplate)
            .withFunctionName("get_actor_name")
    fun getActorName(id: Long): String {
        val source = MapSqlParameterSource().addValue("in_id", id)
        return funcGetActorName.executeFunction(String::class.java, source)
    }
    // ... métodos adicionales
}

Método utilizado executeFunction devuelve una String que contiene el valor de retorno de la llamada a la función.

Devuelve un ResultSet o un cursor REF desde un Objeto SimpleJdbcCall

Llamar a un procedimiento almacenado o una función que devuelve un conjunto de resultados es una tarea un poco confusa. Algunas bases de datos devuelven conjuntos de resultados durante el procesamiento de resultados JDBC, mientras que otras requieren un parámetro out registrado explícitamente de un tipo específico. Ambos enfoques requieren procesamiento adicional para iterar sobre el conjunto de resultados y procesar las filas devueltas. Con SimpleJdbcCall puede utilizar el método returningResultSet y declarar una implementación RowMapper que se utilizará para un parámetro determinado. Si se devuelve un conjunto de resultados mientras se procesan los resultados, entonces los nombres no se definirán, por lo que los resultados devueltos deben coincidir con el orden en que se declaran las implementaciones de RowMapper. El nombre especificado todavía se usa para almacenar la lista procesada de resultados en el mapa resultante que se devuelve desde la instrucción execute.

El siguiente ejemplo (para MySQL) utiliza un procedimiento almacenado que no acepta ningún parámetro IN y devuelve todas las filas de la tabla t_actor:

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

Para llamar a este procedimiento, puede declarar un RowMapper. Dado que la clase que desea asignar sigue las reglas de JavaBean, puede usar BeanPropertyRowMapper, que se crea pasando la clase requerida para asignar al método newInstance. El siguiente ejemplo muestra cómo hacer esto:

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

class JdbcActorDao(dataSource: DataSource) : ActorDao {
        private val procReadAllActors = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
            isResultsMapCaseInsensitive = true
        }).withProcedureName("read_all_actors")
                .returningResultSet("actors",
                        BeanPropertyRowMapper.newInstance(Actor::class.java))
    fun getActorsList(): List<Actor> {
        val m = procReadAllActors.execute(mapOf<String, Any>())
        return m["actors"] as List<Actor>
    }
    // ... métodos adicionales
}

Llamar a execute pasa un Map vacío porque esto la llamada no toma ningún parámetro. La lista de actores luego se recupera del mapa resultante y se devuelve al código de llamada.