Упрощение операций JDBC с помощью классов SimpleJdbc

Классы SimpleJdbcInsert и SimpleJdbcCall обеспечивают упрощенную конфигурацию, используя метаданные базы данных, которые могут быть получены через драйвер JDBC. Это означает, что придется меньше заниматься конфигурированием, хотя можно переопределять или отключить обработку метаданных, если вы предпочитаете снабжать подробностями свой код.

Вставка данных с помощью SimpleJdbcInsert

Начнем с рассмотрения класса SimpleJdbcInsert с минимальным количеством параметров конфигурации. Нужно создать экземпляр SimpleJdbcInsert в методе инициализации уровня доступа к данным. В данном примере инициализирующим методом является метод setDataSource. Не требуется создавать подкласс класса SimpleJdbcInsert. Вместо этого можно создать новый экземпляр и задать имя таблицы с помощью метода withTableName. Конфигурационные методы для этого класса следуют стилю fluid, который возвращает экземпляр SimpleJdbcInsert, что позволяет выстроить цепочку всех конфигурационных методов. В следующем примере используется только один конфигурационный метод (примеры нескольких методов будут показаны позже):

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);
    }
    // ... дополнительные методы
}

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)
    }
    // ... дополнительные методы
}

Используемый здесь метод execute принимает в качестве единственного параметра обычный java.util.Map. Важно отметить, что ключи, используемые для Map, должны совпадать с именами столбцов таблицы, как они определены в базе данных. Это происходит потому, что метаданные читаются для создания фактической инструкции вставки.

Получение автоматически сгенерированных ключей с помощью SimpleJdbcInsert

В следующем примере используется та же вставка, что и в предыдущем примере, но вместо передачи id она осуществляет автоматическое получение сгенерированного ключа с последующей его установкой для нового объекта Actor. При создании SimpleJdbcInsert, в дополнение к указанию имени таблицы, эта вставка задает имя сгенерированного ключевого столбца с помощью метода usingGeneratedKeyColumns. В следующем листинге показано, как это делается:

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());
    }
    // ... дополнительные методы
}

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())
    }
    // ... дополнительные методы
}

Основное отличие при выполнении вставки с использованием второго подхода заключается в том, что вы не добавляете id в Map, а вызываете метод executeAndReturnKey. Это возвращает объект java.lang.Number, с помощью которого можно создать экземпляр числового типа, используемого в классе предметной области. Здесь нельзя полагаться на то, что все базы данных вернут определенный класс Java. java.lang.Number – это основной класс, который можно использовать. Если есть несколько автоматически генерируемых столбцов или генерируемые значения не являются числовыми, то можно использовать KeyHolder, возвращаемый методом executeAndReturnKeyHolder.

Задание столбцов для SimpleJdbcInsert

Можно ограничить столбцы для вставки, указав список имен столбцов с помощью метода usingColumns, как показано в следующем примере:

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());
    }
    // ... дополнительные методы
}

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())
    }
    // ... дополнительные методы
}

Выполнение вставки происходит так же, как если бы вы прибегли к метаданным, чтобы определить, какие столбцы использовать.

Использование SqlParameterSource для указания значений параметров

Использование Map для указания значений параметров работает достаточно хорошо, но это не самый удобный в использовании класс. Spring содержит несколько реализаций интерфейса SqlParameterSource, которые можно использовать вместо него. Первая из них - BeanPropertySqlParameterSource, который является очень удобным классом, если у вас есть JavaBean-совместимый класс, который содержит ваши значения. Он использует соответствующий геттер для извлечения значений параметров. В следующем примере показано, как использовать 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());
    }
    // ... дополнительные методы
}

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())
    }
    // ... дополнительные методы
}

Другим вариантом является MapSqlParameterSource, который похож на Map, но предоставляет более удобный метод addValue, который можно объединить в цепочку. В следующем примере показано, как его использовать:

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());
    }
    // ... дополнительные методы
}

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())
    }
    // ... дополнительные методы
}

Как вы можете видеть, конфигурация та же самая. Для использования этих альтернативных классов ввода необходимо изменить только выполняющийся код.

Вызов хранимой процедуры с помощью SimpleJdbcCall

Класс SimpleJdbcCall использует метаданные в базе данных для поиска имен in и out параметров, поэтому не требуется явным образом объявлять их. Можно объявить параметры, если вы предпочитаете делать это или если у вас есть параметры (такие как ARRAY или STRUCT), которые не имеют автоматического отображения на класс Java. В первом примере показана простая процедура, которая возвращает из базы данных MySQL только скалярные значения в формате VARCHAR и DATE. Процедура в примере считывает указанную запись актера и возвращает столбцы first_name, last_name и birth_date в виде параметров out. В следующем листинге показан последний пример:

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;

Параметр in_id содержит id актера, которого вы ищете. Параметры out возвращают данные, считанные из таблицы.

Можно объявить SimpleJdbcCall способом, аналогичным объявлению SimpleJdbcInsert. Необходимо создать экземпляр и сконфигурировать класс в методе инициализации вашего уровня доступа к данным. По сравнению с классом StoredProcedure, не нужно создавать подкласс и объявлять параметры, поиск которых возможно осуществлять в метаданных базы данных. Следующий пример конфигурации SimpleJdbcCall использует предыдущую хранимую процедуру (единственным параметром конфигурации, помимо DataSource, является имя хранимой процедуры):

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;
    }
    // ... дополнительные методы
}

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)
    }
        // ... дополнительные методы
}

Код, который вы пишете для выполнения вызова, включает создание SqlParameterSource, содержащего IN параметр. Нужно сопоставить имя, указанное для входного значения, с именем параметра, объявленного в хранимой процедуре. Регистр не обязательно должен совпадать, поскольку вы используете метаданные, чтобы определить, каким образом на объекты базы данных нужно ссылаться в хранимой процедуре. То, что указано в источнике для хранимой процедуры, не обязательно совпадает с тем, как она хранится в базе данных. Некоторые базы данных преобразуют имена во все верхние регистры, в то время как другие используют нижний регистр или используют заданный регистр.

Метод execute принимает IN параметры и возвращает Map, содержащий любые параметры out, ключевыми из которых являются имена, указанные в хранимой процедуре. В данном случае это out_first_name, out_last_name и out_birth_date.

Последняя часть метода execute создает экземпляр Actor, который будет использоваться для возврата полученных данных. Опять же, важно использовать имена out параметров в том виде, в каком они объявлены в хранимой процедуре. Кроме того, регистр в именах out параметров, хранящихся в результирующей Map, совпадает с регистром имен out параметров в базе данных, который может отличаться в разных базах данных. Чтобы сделать код более платформенно-независимым, нужно выполнять поиск без учета регистра или дать Spring команду использовать LinkedCaseInsensitiveMap. Чтобы осуществить последнее, можно создать свой собственный JdbcTemplate и установить свойство setResultsMapCaseInsensitive в true. Затем можно передать этот настроенный экземпляр JdbcTemplate в конструктор вашего SimpleJdbcCall. В следующем примере показана эта конфигурация:

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");
    }
    // ... дополнительные методы
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private var procReadActor = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
        isResultsMapCaseInsensitive = true
    }).withProcedureName("read_actor")
    // ... дополнительные методы
}

Выполнив это действие, вы избежите конфликтов в регистре, используемом для имен ваших возвращаемых параметров.

Явное объявление параметров для использования в SimpleJdbcCall

Ранее в этой главе мы описывали, как параметры выводятся из метаданных, но вы можете объявить их явно, если захотите. Сделать это можно, создав и сконфигурировав SimpleJdbcCall с помощью метода declareParameters, который принимает переменное количество объектов SqlParameter в качестве входных данных. Подробнее о том, как определить SqlParameter, см. в следующем разделе.

Вы можете явным образом объявить один, несколько или все параметры. Метаданные параметров по-прежнему используются там, где параметры не объявлены явно. Чтобы обойти всю обработку поиска метаданных для потенциальных параметров и использовать только объявленные параметры, можно вызвать метод withoutProcedureColumnMetaDataAccess как часть объявления. Предположим, что для функции базы данных объявлены две или более различных сигнатур вызова. В этом случае useInParameterNames вызывается, чтобы указать список имен IN параметров, которые следует включить для данной сигнатуры.

В следующем примере показан полностью объявленный вызов процедуры и использована информация из предыдущего примера:

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)
                );
    }
    // ... дополнительные методы
}

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)
    )
        // ... дополнительные методы
}

Выполнение и конечные результаты в этих двух примерах одинаковы. Во втором примере все детали указаны явно, а не с использованием метаданных.

Способы определения SqlParameters

Для определения параметра для классов SimpleJdbc, а также для классов операций РСУБД (рассмотрено в разделе "Моделирование операций JDBC как объектов Java") можно использовать SqlParameter или один из его подклассов. Для этого обычно указывают имя параметра и тип SQL в конструкторе. Тип SQL задается с помощью констант java.sql.Types. Ранее в этой главе мы видели объявления, похожие на следующие:

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

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

Первая строка с SqlParameter объявляет IN параметр. Можно использовать IN параметры как для вызовов хранимых процедур, так и для запросов с помощью SqlQuery и его подклассов (рассмотрено в разделе "Основные сведения о SqlQuery").

Вторая строка (с SqlOutParameter) объявляет out параметр для использования в вызове хранимой процедуры. Существует также SqlInOutParameter для InOut параметров (параметры, которые предоставляют процедуре значение IN и которые также возвращают значение).

Для IN параметров, помимо имени и типа SQL, можно указать задать для числовых данных или имя типа для кастомно созданных типов баз данных. Для out параметров можно указать RowMapper, чтобы производить обработку отображения строк, возвращаемых из курсора REF. Другой вариант – задать SqlReturnType, который дает возможность определить индивидуальную обработку возвращаемых значений.

Вызов хранимой функции с помощью SimpleJdbcCall

Вызвать хранимую функцию можно почти так же, как и хранимую процедуру, за исключением того, что указывается имя функции, а не имя процедуры. Метод withFunctionName используется как часть конфигурации, чтобы указать, что необходимо сделать вызов функции, после чего генерируется соответствующая строка для вызова функции. Для выполнения функции используется специализированный вызов(executeFunction), который возвращает возвращаемое значение функции в виде объекта указанного типа, что означает, что не нужно получать возвращаемое значение из результирующей Map. Аналогичный вспомогательный метод (с именем executeObject) также доступен для хранимых процедур, которые имеют только один out параметр. Следующий пример (для MySQL) основан на хранимой функции get_actor_name, которая возвращает полное имя актера:

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;

Чтобы вызвать эту функцию, мы снова создаем SimpleJdbcCall в методе инициализации, как показано в следующем примере:

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;
    }
    // ... дополнительные методы
}

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)
    }
    // ... дополнительные методы
}

Используемый метод executeFunction возвращает String, содержащую возвращаемое значение из вызова функции.

Возврат ResultSet или REF-курсора из объекта SimpleJdbcCall

Вызов хранимой процедуры или функции, которая возвращает результирующий набор, является немного запутанной задачей. Некоторые базы данных возвращают результирующий наборы во время обработки результатов JDBC, в то время как другие требуют явно зарегистрированного out параметра определенного типа. Оба подхода требуют дополнительной обработки для перебора результирующего набора и обработки возвращенных строк. С помощью SimpleJdbcCall можно использовать метод returningResultSet и объявить реализацию RowMapper, которая будет использоваться для заданного параметра. Если результирующий набор возвращается во время обработки результатов, то имена не будут определены, поэтому возвращаемые результаты должны соответствовать порядку, в котором объявляются реализации RowMapper. Указанное имя по-прежнему используется для хранения обработанного списка результатов в результирующей Map, который возвращается из инструкции execute.

В следующем примере (для MySQL) используется хранимая процедура, которая не принимает никаких IN параметров и возвращает все строки из таблицы 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;

Чтобы вызвать эту процедуру, можно объявить RowMapper. Поскольку класс, с которым вы хотите произвести сопоставление, следует правилам JavaBean, можно использовать BeanPropertyRowMapper, который создается путем передачи требуемого класса для отображения в метод newInstance. В следующем примере показано, как это сделать:

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");
    }
    // ... дополнительные методы
}

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>
    }
    // ... дополнительные методы
}

Вызов execute передает пустой Map, поскольку этот вызов не принимает никаких параметров. Затем список актеров извлекается из результирующей Map и возвращается вызывающему коду.