JDBC operatsiyalarini Java ob'ektlari sifatida modellashtirish

org.springframework.jdbc.object paketi, bazaga ko'proq ob'ektga yo'naltirilgan usulda kirish imkonini beradigan sinflarni o'z ichiga oladi. Masalan, so'rovlarni bajarish va ma'lumotlarni biznes-ob'ekt xususiyatlariga mos keladigan ma'lumotlar bilan ro'yxat ko'rinishida olish mumkin. Saqlangan protseduralarni ishga tushirish va yangilash, o'chirish va qo'shish bo'yicha ko'rsatmalarni bajarish ham mumkin.

SqlQuery haqida asosiy ma'lumotlar

SqlQuery – bu ko'p marotaba foydalanish mumkin bo'lgan, oqim xavfsiz sinf bo'lib, SQL so'rovini inkapsulatsiya qiladi. Subclasslar RowMapper ning namunasini taqdim etish uchun newRowMapper(..) metodini amalga oshirishlari kerak, bu har bir yorliqda, so'rov bajarilish paytida hosil bo'lgan ResultSet ni bosib o'tish natijasida yaratiladigan ob'ektni yaratishi mumkin. SqlQuery sinfi bevosita kamdan-kam qo'llaniladi, chunki MappingSqlQuery subklass Java sinflariga bog'lanish uchun ancha qulayroq amalga oshirilishini taqdim etadi. SqlQuery ni kengaytiradigan boshqa amalga oshirilishlar MappingSqlQueryWithParameters va UpdatableSqlQuery dir.

MappingSqlQuery dan foydalanish

MappingSqlQuery - ko'p marotaba foydalanish mumkin bo'lgan so'rov bo'lib, maxsus subklasslar har bir qatorni taqdim etilgan ResultSet dan belgilangan turdagi ob'ektga aylantirish uchun mapRow(..) abstrakt metodini amalga oshirishlari kerak. Quyidagi misolda t_actor munosabatlaridan ma'lumotlarni Actor sinfining nusxasiga mos keladigan maxsus so'rov ko'rsatilgan:

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

Ushbu sinf Actor turi bilan parametrizatsiya qilingan MappingSqlQuery ni kengaytiradi. Ushbu maxsus so'rovning konstruktoriga DataSource yagona parametr sifatida qabul qilinadi. Ushbu konstruktorda DataSource va ResultSet ni hosil qilish uchun bajarilishi kerak bo'lgan SQL bilan superclass konstruktorini chaqirish mumkin. Ushbu SQL PreparedStatement ni yaratish uchun ishlatiladi, shuning uchun u bajarilish vaqtida o'tkaziladigan har qanday parametrlar uchun plasholderlarni o'z ichiga olishi mumkin. Har bir parametrni declareParameter metodini chaqirish orqali e'lon qilish kerak, SqlParameter ni o'tkazish kerak. SqlParameter java.sql.Types da belgilangan JDBC nomi va turini qabul qiladi. Barcha parametrlar e'lon qilinganidan so'ng, ko'rsatmani tayyorlash va keyinroq bajarish uchun compile() metodini chaqirish mumkin. Tayyorlanganidan so'ng, bu sinf oqim xavfsizdir, shuning uchun bu instansiyalar DAO initsializatsiya paytida yaratilsa, ular ob'ekt o'zgaruvchisi sifatida saqlanishi va qayta ishlatilishi mumkin. Quyidagi misolda bunday sinfni qanday aniqlash ko'rsatilgan:

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)

Oldingi misoldagi metod id ni yagona parametr sifatida o'tkazilgan mijozni oladi. Biz faqat bitta ob'ektni qaytarishimiz kerak bo'lgani uchun biz id ni parametr sifatida findObject yordamchi metodini chaqiramiz. Agar, buning o'rniga, qo'shimcha parametrlarni qabul qiladigan va ob'ektlar ro'yxatini qaytaradigan so'rov bo'lsa, biz execute usullaridan birini ishlatgan bo'lar edik, u o'zgaruvchan uzunlikdagi argumentlar sifatida uzatiladigan parametr qiymatlari massivini qabul qiladi. Quyidagi misolda bunday usul ko'rsatilgan:

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 dan foydalanish

SqlUpdate classi SQL yangilanishini inkapsulatsiyalaydi. So'rov kabi, yangilanish ob'ekti ko'p marotaba foydalanish mumkin va barcha RdbmsOperation sinflari kabi, yangilanish parametrlar qo'shishi mumkin va SQL tilida belgilanadi. Bu sinf, so'rov ob'ektlaridagi execute(..) metodlariga o'xshash bir qator update(..) metodlarini taqdim etadi. SqlUpdate class aniq. Uning qo'shimcha yangilash metodini qo'shish uchun masalan, subklasslarga bo'lish mumkin. Biroq, SqlUpdate classni subclass qilish shart emas, chunki uni osonlik bilan SQL ni o'rnatish va parametrlarni e'lon qilish orqali parametrizatsiya qilish mumkin. Quyidagi misolda execute nomli maxsus yangilanish metodi yaratilgan:

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 dan foydalanish

StoredProcedure sinfi - bu RСUBD saqlangan protseduralari uchun ob'ektli abstaktsiyalar uchun abstract superclass.

Meros olingan sql xususiyati - RСUBD dagi saqlangan protseduraning nomi.

StoredProcedure klass uchun parametrni aniqlash uchun SqlParameter yoki uning subklasslaridan birini ishlatishingiz kerak. Parametr nomi va SQL turini quyidagi kod bo'lagida ko'rsatilganidek konstruktor ichida belgilash kerak:

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 turi java.sql.Types da belgilangan konstantalar yordamida belgilanadi.

Birinchi satr ( SqlParameter bilan) IN parametrni e'lon qiladi. IN parametrlardan saqlangan protseduralar chaqiruvi uchun va SqlQuery va uning subklasslari yordamida so'rovlar uchun foydalanish mumkin.

Ikkinchi satr ( SqlOutParameter bilan) saqlangan protsedura chaqiruvida ishlatiladigan out parametrni e'lon qiladi. InOut parametrlari uchun InOut parametrlari (procedura uchun in qiymat beruvchi, shuningdek qiymat qaytaruvchi parametrlar) ham mavjud.

in parametrlari uchun SQL turi va nomidan tashqari, raqamli ma'lumotlar uchun o'lchov yoki baza ma'lumotlari uchun maxsus tur nomi berilishi mumkin. out parametrlari uchun RowMapper ni, qaytarilgan REF kursordan olingan ma'lumotlarni bog'lashni amalga oshirish uchun belgilashingiz mumkin. Yana bir variant - SqlReturnType ni belgilash, bu qaytarilgan qiymatlarni individual qayta ishlashni aniqlash imkonini beradi.

Quyidagi oddiy DAO misolida Oracle bazasidagi har qanday baza bilan birgalikda keladigan StoredProcedure yordamida funksiyani ishga tushirish ko'rsatilgan(sysdate()). StoredProcedure funksiyasidan foydalanish uchun StoredProcedure ni kengaytiradigan sinf yaratish kerak. Ushbu misolda StoredProcedure sinfi ichki sinf bo'lib ishlatilgan. Biroq, StoredProcedure ni qayta foydalanish kerak bo'lsa, uni yuqori darajadagi sinf sifatida e'lon qilishingiz mumkin. Ushbu misolda kirish parametrlari yo'q, lekin chiqish parametri SqlOutParameter sinftasi yordamida sana turi sifatida e'lon qilingan. execute() metodi protsedurani ishga tushiradi va Map natijasida qaytarilgan sanani oladi. Natijadagi Map har bir e'lon qilingan chiqish parametri uchun yozuvni (bu holda faqat bittasi uchun) parametr nomini kalit sifatida ishlatadi. Quyidagi listingda bizning maxsus StoredProcedure sinfimiz ko'rsatilgan:

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" satrida kirish parametrlari yo'q, shuning uchun bo'sh Map qaytadi...
            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" satrida kirish parametrlari yo'q, shuning uchun bo'sh Map qaytadi...
            val results = execute(mutableMapOf<String, Any>())
            return results["date"] as Date
        }
    }
}

Keyingi StoredProcedure misolida ikkita chiqish parametri mavjud (bu holda Oracle dan REF kursors):

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() {
        // yana, ushbu saqlangan protsedurada kirish parametrlari yo'q, shuning uchun bo'sh Map qaytariladi
        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> {
        // yana, ushbu saqlangan protsedurada kirish parametrlari yo'q, shuning uchun bo'sh Map qaytariladi
        return super.execute(HashMap<String, Any>())
    }
}

Quyida declareParameter(..) metodining TitlesAndGenresStoredProcedure konstruktorida ishlatilgan ortiqcha versiyalari RowMapper ni amalga oshiradigan namunalarga o'tkazilganligini ko'rib chiqing. Bu mavjud funksionallikni qayta ishlatishning juda qulay va samarali usuli hisoblanadi. Keyingi ikkita misolda RowMapper ning ikkita amalga oshirilishini keltiradigan kodlar mavjud.

TitleMapper classi quyidagi tarzda berilgan ResultSet dan har bir satrni Title domain ob'ektiga to'g'ri keladi:

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 classi quyidagi tarzda ResultSet dan har bir satrni Genre domain ob'ektiga to'g'ri keladi:

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

RСUBD da bir yoki bir nechta kirish parametrlari bo'lgan saqlangan protseduraga parametrlar uzatish uchun, quyidagi misolda ko'rsatilgandek, superklassdagi execute(Map) netiplangan metodiga delegat bo'ladigan qattiq tiplangan execute(..) metodini yozishingiz mumkin:

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