Моделирование операций JDBC как объектов Java

Пакет org.springframework.jdbc.object содержит классы, которые позволяют получить доступ к базе данных более объектно-ориентированным способом. Например, можно выполнять запросы и получать результаты в виде списка, содержащего бизнес-объекты с данными реляционных колонок, сопоставленными со свойствами бизнес-объекта. Также можно запускать хранимые процедуры и выполнять инструкции обновления, удаления и вставки.

Основные сведения о SqlQuery

SqlQuery – это многократно используемый, потокобезопасный класс, который инкапсулирует SQL-запрос. Подклассы должны реализовать метод newRowMapper(..) для предоставления экземпляра RowMapper, который может создавать один объект для каждой строки, полученной в результате обхода ResultSet, который создается во время выполнения запроса. Класс SqlQuery редко используется напрямую, поскольку подкласс MappingSqlQuery предоставляет гораздо более удобную реализацию для отображения строк на классы Java. Другими реализациями, расширяющими SqlQuery, являются MappingSqlQueryWithParameters и UpdatableSqlQuery.

Использование MappingSqlQuery

MappingSqlQuery – это многократно используемый запрос, в котором конкретные подклассы должны реализовать абстрактный метод mapRow(..) для преобразования каждой строки предоставляемого ResultSet в объект указанного типа. В следующем примере показан кастомный запрос, который сопоставляет данные из отношения t_actor с экземпляром класса Actor:

public class ActorMappingQuery extends MappingSqlQuery<Actor> {
    public ActorMappingQuery(DataSource ds) {
        super(ds, "select id, first_name, last_name from t_actor where id = ?");
        declareParameter(new SqlParameter("id", Types.INTEGER));
        compile();
    }
    @Override
    protected Actor mapRow(ResultSet rs, int rowNumber) throws SQLException {
        Actor actor = new Actor();
        actor.setId(rs.getLong("id"));
        actor.setFirstName(rs.getString("first_name"));
        actor.setLastName(rs.getString("last_name"));
        return actor;
    }
}

class ActorMappingQuery(ds: DataSource) : MappingSqlQuery<Actor>(ds, "select id, first_name, last_name from t_actor where id = ?") {
    init {
        declareParameter(SqlParameter("id", Types.INTEGER))
        compile()
    }
    override fun mapRow(rs: ResultSet, rowNumber: Int) = Actor(
            rs.getLong("id"),
            rs.getString("first_name"),
            rs.getString("last_name")
    )
}

Класс расширяет MappingSqlQuery, параметризованный типом Actor. Конструктор для этого кастомного запроса принимает DataSource в качестве единственного параметра. В этом конструкторе можно вызвать конструктор суперкласса с DataSource и SQL, который должен быть выполнен, чтобы получить строки по этому запросу. Этот SQL используется для создания PreparedStatement, поэтому он может содержать плейсхолдеры для любых параметров, которые будут переданы во время выполнения. Необходимо объявить каждый параметр с помощью метода declareParameter, передав ему SqlParameter. SqlParameter принимает имя и тип JDBC, определенный в java.sql.Types. После определения всех параметров можно вызвать метод compile(), чтобы подготовить и впоследствии выполнить инструкцию. После компиляции этот класс является потокобезопасным, поэтому, пока эти экземпляры создаются при инициализации DAO, их можно хранить как переменные экземпляра и использовать повторно. В следующем примере показано, как определить такой класс:

private ActorMappingQuery actorMappingQuery;
@Autowired
public void setDataSource(DataSource dataSource) {
    this.actorMappingQuery = new ActorMappingQuery(dataSource);
}
public Customer getCustomer(Long id) {
    return actorMappingQuery.findObject(id);
}

private val actorMappingQuery = ActorMappingQuery(dataSource)
fun getCustomer(id: Long) = actorMappingQuery.findObject(id)

Метод в предыдущем примере получает клиента с id, переданным в качестве единственного параметра. Поскольку нам нужно вернуть только один объект, мы вызываем вспомогательный метод findObject с id в качестве параметра. Если бы вместо этого был запрос, который возвращает список объектов и принимает дополнительные параметры, мы бы использовали один из методов execute, который принимает массив значений параметров, переданных в качестве аргументов переменной длины. В следующем примере показан такой метод:

public List<Actor> searchForActors(int age, String namePattern) {
    List<Actor> actors = actorSearchMappingQuery.execute(age, namePattern);
    return actors;
}

fun searchForActors(age: Int, namePattern: String) =
            actorSearchMappingQuery.execute(age, namePattern)

Использование SqlUpdate

Класс SqlUpdate инкапсулирует обновление SQL. Как и запрос, объект обновления является многоразовым, и, как и все классы RdbmsOperation, обновление может иметь параметры и определяется на языке SQL. Этот класс предоставляет ряд методов update(..), аналогичных методам execute(..) объектов-запросов. Класс SqlUpdate является конкретным. Его можно разбить на подклассы – например, чтобы добавить кастомный метод обновления. Однако вам не обязательно разбивать на подклассы класс SqlUpdate, поскольку его можно легко параметризовать, задав SQL и объявив параметры. В следующем примере создается пользовательский метод обновления с именем execute:

import java.sql.Types;
import javax.sql.DataSource;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;
public class UpdateCreditRating extends SqlUpdate {
    public UpdateCreditRating(DataSource ds) {
        setDataSource(ds);
        setSql("update customer set credit_rating = ? where id = ?");
        declareParameter(new SqlParameter("creditRating", Types.NUMERIC));
        declareParameter(new SqlParameter("id", Types.NUMERIC));
        compile();
    }
    /**
     * @param id для обновляемого Customer
     * @param rating новое значение кредитного рейтинга
     * @return количество обновленных строк
     */
    public int execute(int id, int rating) {
        return update(rating, id);
    }
}

import java.sql.Types
import javax.sql.DataSource
import org.springframework.jdbc.core.SqlParameter
import org.springframework.jdbc.object.SqlUpdate
class UpdateCreditRating(ds: DataSource) : SqlUpdate() {
    init {
        setDataSource(ds)
        sql = "update customer set credit_rating = ? where id = ?"
        declareParameter(SqlParameter("creditRating", Types.NUMERIC))
        declareParameter(SqlParameter("id", Types.NUMERIC))
        compile()
    }
    /**
     * @param id для обновляемого Customer
     * @param rating новое значение кредитного рейтинга
     * @return количество обновленных строк
     */
    fun execute(id: Int, rating: Int): Int {
        return update(rating, id)
    }
}

Использование StoredProcedure

Класс StoredProcedure является abstract суперклассом для объектных абстракций хранимых процедур РСУБД.

Наследуемое свойство sql – это имя хранимой процедуры в РСУБД.

Чтобы определить параметр для класса StoredProcedure, можно использовать SqlParameter или один из его подклассов. Необходимо задать имя параметра и тип SQL в конструкторе, как показано в следующем фрагменте кода:

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

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

Тип SQL задается с помощью констант java.sql.Types.

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

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

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

В следующем примере простого DAO используется StoredProcedure для вызова функции(sysdate()), которая поставляется с любой базой данных Oracle. Чтобы использовать функциональность хранимой процедуры, необходимо создать класс, расширяющий StoredProcedure. В этом примере класс StoredProcedure является внутренним классом. Однако если нужно повторно использовать StoredProcedure, то можете объявить ее как класс верхнего уровня. В этом примере нет входных параметров, но выходной параметр объявлен как тип даты с помощью класса SqlOutParameter. Метод execute() запускает процедуру и извлекает возвращаемую дату из результирующего Map. Результирующий Map имеет запись для каждого объявленного выходного параметра (в данном случае только одного), используя имя параметра в качестве ключа. В следующем листинге показан наш кастомный класс StoredProcedure:

import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;
public class StoredProcedureDao {
    private GetSysdateProcedure getSysdate;
    @Autowired
    public void init(DataSource dataSource) {
        this.getSysdate = new GetSysdateProcedure(dataSource);
    }
    public Date getSysdate() {
        return getSysdate.execute();
    }
    private class GetSysdateProcedure extends StoredProcedure {
        private static final String SQL = "sysdate";
        public GetSysdateProcedure(DataSource dataSource) {
            setDataSource(dataSource);
            setFunction(true);
            setSql(SQL);
            declareParameter(new SqlOutParameter("date", Types.DATE));
            compile();
        }
        public Date execute() {
            // Строка "sysdate" не имеет входных параметров, поэтому выдается пустой Map...
            Map<String, Object> results = execute(new HashMap<String, Object>());
            Date sysdate = (Date) results.get("date");
            return sysdate;
        }
    }
}

import java.sql.Types
import java.util.Date
import java.util.Map
import javax.sql.DataSource
import org.springframework.jdbc.core.SqlOutParameter
import org.springframework.jdbc.object.StoredProcedure
class StoredProcedureDao(dataSource: DataSource) {
    private val SQL = "sysdate"
    private val getSysdate = GetSysdateProcedure(dataSource)
    val sysdate: Date
        get() = getSysdate.execute()
    private inner class GetSysdateProcedure(dataSource: DataSource) : StoredProcedure() {
        init {
            setDataSource(dataSource)
            isFunction = true
            sql = SQL
            declareParameter(SqlOutParameter("date", Types.DATE))
            compile()
        }
        fun execute(): Date {
            // Строка "sysdate" не имеет входных параметров, поэтому выдается пустой Map...
            val results = execute(mutableMapOf<String, Any>())
            return results["date"] as Date
        }
    }
}

Следующий пример StoredProcedure имеет два выходных параметра (в данном случае REF-курсоры из Oracle):

import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;
public class TitlesAndGenresStoredProcedure extends StoredProcedure {
    private static final String SPROC_NAME = "AllTitlesAndGenres";
    public TitlesAndGenresStoredProcedure(DataSource dataSource) {
        super(dataSource, SPROC_NAME);
        declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));
        declareParameter(new SqlOutParameter("genres", OracleTypes.CURSOR, new GenreMapper()));
        compile();
    }
    public Map<String, Object> execute() {
        // опять же, у этой хранимой процедуры нет входных параметров, поэтому выдается пустой Map
        return super.execute(new HashMap<String, Object>());
    }
}

import java.util.HashMap
import javax.sql.DataSource
import oracle.jdbc.OracleTypes
import org.springframework.jdbc.core.SqlOutParameter
import org.springframework.jdbc.object.StoredProcedure
class TitlesAndGenresStoredProcedure(dataSource: DataSource) : StoredProcedure(dataSource, SPROC_NAME) {
    companion object {
        private const val SPROC_NAME = "AllTitlesAndGenres"
    }
    init {
        declareParameter(SqlOutParameter("titles", OracleTypes.CURSOR, TitleMapper()))
        declareParameter(SqlOutParameter("genres", OracleTypes.CURSOR, GenreMapper()))
        compile()
    }
    fun execute(): Map<String, Any> {
        // опять же, у этой хранимой процедуры нет входных параметров, поэтому выдается пустой Map
        return super.execute(HashMap<String, Any>())
    }
}

Обратите внимание, как перегруженные варианты метода declareParameter(..), которые были использованы в конструкторе TitlesAndGenresStoredProcedure, передаются экземплярам реализации RowMapper. Это очень удобный и эффективный способ повторного использования существующей функциональности. Следующие два примера содержат код для двух реализаций RowMapper.

Класс TitleMapper сопоставляет ResultSet с объектом предметной области Title для каждой строки в предоставленном ResultSet следующим образом:

import java.sql.ResultSet;
import java.sql.SQLException;
import com.foo.domain.Title;
import org.springframework.jdbc.core.RowMapper;
public final class TitleMapper implements RowMapper<Title> {
    public Title mapRow(ResultSet rs, int rowNum) throws SQLException {
        Title title = new Title();
        title.setId(rs.getLong("id"));
        title.setName(rs.getString("name"));
        return title;
    }
}

import java.sql.ResultSet
import com.foo.domain.Title
import org.springframework.jdbc.core.RowMapper
class TitleMapper : RowMapper<Title> {
    override fun mapRow(rs: ResultSet, rowNum: Int) =
            Title(rs.getLong("id"), rs.getString("name"))
}

Класс GenreMapper сопоставляет ResultSet с объектом предметной области Genre для каждой строки в предоставленном ResultSet следующим образом:

import java.sql.ResultSet;
import java.sql.SQLException;
import com.foo.domain.Genre;
import org.springframework.jdbc.core.RowMapper;
public final class GenreMapper implements RowMapper<Genre> {
    public Genre mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new Genre(rs.getString("name"));
    }
}

import java.sql.ResultSet
import com.foo.domain.Genre
import org.springframework.jdbc.core.RowMapper
class GenreMapper : RowMapper<Genre> {
    override fun mapRow(rs: ResultSet, rowNum: Int): Genre {
        return Genre(rs.getString("name"))
    }
}

Чтобы передать параметры в хранимую процедуру, которая содержит один или несколько входных параметров в своем определении в РСУБД, можно написать строго типизированный метод execute(..), который будет делегирован нетипизированному методу execute(Map) в суперклассе, как показано в следующем примере:

import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;
public class TitlesAfterDateStoredProcedure extends StoredProcedure {
    private static final String SPROC_NAME = "TitlesAfterDate";
    private static final String CUTOFF_DATE_PARAM = "cutoffDate";
    public TitlesAfterDateStoredProcedure(DataSource dataSource) {
        super(dataSource, SPROC_NAME);
        declareParameter(new SqlParameter(CUTOFF_DATE_PARAM, Types.DATE);
        declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));
        compile();
    }
    public Map<String, Object> execute(Date cutoffDate) {
        Map<String, Object> inputs = new HashMap<String, Object>();
        inputs.put(CUTOFF_DATE_PARAM, cutoffDate);
        return super.execute(inputs);
    }
}

import java.sql.Types
import java.util.Date
import javax.sql.DataSource
import oracle.jdbc.OracleTypes
import org.springframework.jdbc.core.SqlOutParameter
import org.springframework.jdbc.core.SqlParameter
import org.springframework.jdbc.object.StoredProcedure
class TitlesAfterDateStoredProcedure(dataSource: DataSource) : StoredProcedure(dataSource, SPROC_NAME) {
    companion object {
        private const val SPROC_NAME = "TitlesAfterDate"
        private const val CUTOFF_DATE_PARAM = "cutoffDate"
    }
    init {
        declareParameter(SqlParameter(CUTOFF_DATE_PARAM, Types.DATE))
        declareParameter(SqlOutParameter("titles", OracleTypes.CURSOR, TitleMapper()))
        compile()
    }
    fun execute(cutoffDate: Date) = super.execute(
            mapOf<String, Any>(CUTOFF_DATE_PARAM to cutoffDate))
}