JavaRush /Kurslar /All lectures for UZ purposes /JdbcTemplate foydalanish

JdbcTemplate foydalanish

All lectures for UZ purposes

Daraja, Dars

Mavjud

JdbcTemplate – JDBC asosiy paketidagi markaziy klassi. Bu resurslarni yaratish va ozod qilishni boshqaradi, bu odatda ulanishni yopishni unutish kabi keng tarqalgan xatolardan qochishga yordam beradi. Bu JDBC asosiy ish jarayonidagi asosiy vazifalarni bajaradi (masalan, buyruqlar yaratish va bajarish), dasturning kodiga SQL ni taqdim etish va natijalarni chiqazishni qoldiradi. JdbcTemplate klassi:

SQL-so'rovlarni bajaradi
Statementlarni va saqlangan proseduralarni yangilaydi
ResultSet namunalari bo'yicha iteratsiya va qaytarilgan parametr qiymatlarini olishni amalga oshiradi.
JDBC istisnolarini ushlab qoladi va ularni org.springframework.dao paketida aniqlangan, turlangan, ko'proq ma'lumot beruvchi istisnolar iyerarxiyasiga aylantiradi. (Qo'shimcha ma'lumot uchun Kelishilgan istisnolar iyerarxiyasini ko'ring).

Agar siz шаблон JdbcTemplate shablonini kodingizda ishlatsangiz, qayta qo'ng'iroq qilish interfeyslarini amalga oshirish va ularga aniq belgilangan shartnoma berish kifoya qiladi. JdbcTemplate klassi tomonidan taqdim etilgan Connectionni hisobga olmagan holda, PreparedStatementCreator interfeysi SQL va barcha zarur parametrlarning taqdim etilishi bilan tayyorlangan statementni yaratadi. Xuddi shu usul CallableStatementCreator interfeysiga ham tegishli, u chaqiriladigan instruktsiyalarni yaratadi. RowCallbackHandler interfeysi ResultSetning har bir qatoridan qiymatlarni oladi.

DAOni amalga oshirishda JdbcTemplatedan foydalanishingiz mumkin, bu DataSourcega havola bilan bevosita yaratish orqali amalga oshiriladi yoki uni Spring IoC-konteynerida konfiguratsiya qilib, DAOga bean havola sifatida uzatish orqali.

DataSource doimo Spring IoC-konteynerida bean sifatida konfiguratsiya qilinishi kerak. Birinchi holatda bean to'g'ridan-to'g'ri xizmatga uzatiladi; ikkinchi holatda esa tayyorlangan shablon orqali uzatiladi.

Bu klass tomonidan chiqarilgan barcha SQL DEBUG darajasida, shablon nusxasi klassining to'liq nomiga mos keladigan kategoriyada ro'yxatga olinadi (odatda JdbcTemplate, lekin agar siz JdbcTemplate klassining maxsus subclassini ishlatsangiz, o'zgarishi mumkin).

Quyidagi bo'limlarda JdbcTemplate dan foydalanishning ba'zi misollari keltirilgan. Bu misollar JdbcTemplate tomonidan taqdim etilgan barcha imkoniyatlarning to'liq ro'yxati emas. Qo'shimcha ma'lumotni ilova qilingan javadoc da ko'ring.

So'rovlar yaratish (`SELECT`)

Quyidagi so'rov nisbatan satrlar sonini olish imkonini beradi:

Java

int rowCount = this.jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class);

Kotlin

val rowCount = jdbcTemplate.queryForObject<Int>("select count(*) from t_actor")!!

Quyidagi so'rovda bog'lanish o'zgaruvchisi ishlatiladi:

Java

int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject(
        "select count(*) from t_actor where first_name = ?", Integer.class, "Joe");

Kotlin

val countOfActorsNamedJoe = jdbcTemplate.queryForObject<Int>(
        "select count(*) from t_actor where first_name = ?", arrayOf("Joe"))!!

Quyidagi so'rov Stringni qidiradi:

Java

String lastName = this.jdbcTemplate.queryForObject(
        "select last_name from t_actor where id = ?",
        String.class, 1212L);

Kotlin

val lastName = this.jdbcTemplate.queryForObject<String>(
        "select last_name from t_actor where id = ?",
        arrayOf(1212L))!!

Quyidagi so'rov bir ob'ektni topib to'ldiradi:

Java

Actor actor = jdbcTemplate.queryForObject(
        "select first_name, last_name from t_actor where id = ?",
        (resultSet, rowNum) -> {
            Actor newActor = new Actor();
            newActor.setFirstName(resultSet.getString("first_name"));
            newActor.setLastName(resultSet.getString("last_name"));
            return newActor;
        },
        1212L);

Kotlin

val actor = jdbcTemplate.queryForObject(
            "select first_name, last_name from t_actor where id = ?",
            arrayOf(1212L)) { rs, _ ->
        Actor(rs.getString("first_name"), rs.getString("last_name"))
    }

Quyidagi so'rov bir ro'yxatni topib to'ldiradi:

Java

List<Actor> actors = this.jdbcTemplate.query(
        "select first_name, last_name from t_actor",
        (resultSet, rowNum) -> {
            Actor actor = new Actor();
            actor.setFirstName(resultSet.getString("first_name"));
            actor.setLastName(resultSet.getString("last_name"));
            return actor;
        });

Kotlin

val actors = jdbcTemplate.query("select first_name, last_name from t_actor") { rs, _ ->
        Actor(rs.getString("first_name"), rs.getString("last_name"))

Agar oxirgi ikki kod parchalari haqiqatan ham bir dasturda bo'lganida, ikkita RowMapper lamba ifodalarida mavjud bo'lgan takrorlanishni olib tashlash va ularni qachon kerak bo'lsa DAO metodlariga murojaat qilishi mumkin bo'lgan bitta maydonga chiqarish mantiqan bo'lardi. Masalan, oldingi kod parchasi quyidagi tarzda yaxshiroq yoziladi:

Java

private final RowMapper<Actor> actorRowMapper = (resultSet, rowNum) -> {
    Actor actor = new Actor();
    actor.setFirstName(resultSet.getString("first_name"));
    actor.setLastName(resultSet.getString("last_name"));
    return actor;
};
public List<Actor> findAllActors() {
    return this.jdbcTemplate.query("select first_name, last_name from t_actor", actorRowMapper);
}

Kotlin

val actorMapper = RowMapper<Actor> { rs: ResultSet, rowNum: Int ->
    Actor(rs.getString("first_name"), rs.getString("last_name"))
}
fun findAllActors(): List<Actor> {
    return jdbcTemplate.query("select first_name, last_name from t_actor", actorMapper)
}

Yangilash (`INSERT`, `UPDATE` va `DELETE`) yordamida `JdbcTemplate`

Siz update(..) metodidan foydalanib qo'shish, yangilash va o'chirish operatsiyalarini bajarishingiz mumkin. Parametr qiymatlari odatda o'zgaruvchi argumentlari sifatida yoki boshqa varianti, obyektlar arrayi sifatida taqdim etiladi.

Quyidagi misolda yangi yozuv qo'shiladi:

Java

this.jdbcTemplate.update(
        "insert into t_actor (first_name, last_name) values (?, ?)",
        "Leonor", "Watling");

Kotlin

jdbcTemplate.update(
        "insert into t_actor (first_name, last_name) values (?, ?)",
        "Leonor", "Watling")

Quyidagi misolda mavjud yozuv yangilanadi:

Java

this.jdbcTemplate.update(
        "update t_actor set last_name = ? where id = ?",
        "Banjo", 5276L);

Kotlin

jdbcTemplate.update(
        "update t_actor set last_name = ? where id = ?",
        "Banjo", 5276L)

Quyidagi misolda yozuv o'chiriladi:

Java

this.jdbcTemplate.update(
        "delete from t_actor where id = ?",
        Long.valueOf(actorId));

Kotlin

jdbcTemplate.update("delete from t_actor where id = ?", actorId.toLong())

Boshqa `JdbcTemplate` operatsiyalari

Metod execute(..) dan foydalanib ixtiyoriy SQL ni bajarishingiz mumkin. Shuning uchun, bu metod ko'pincha ma'lumotlar ta'rifi tili (Data Definition Language /DDL) buyruqlari uchun ishlatiladi. Bu juda ko'p yuklangan variantlar, chunki interfeys qayta chaqiruvi, bog'langan arraylar va boshqalarni qabul qiladi. Quyidagi misolda jadval yaratiladi:

Java

this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");

Kotlin

jdbcTemplate.execute("create table mytable (id integer, name varchar(100))")

Quyidagi misolda saqlangan protsedura chaqiriladi:

Java

this.jdbcTemplate.update(
        "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
        Long.valueOf(unionId));

Kotlin

jdbcTemplate.update(
        "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
        unionId.toLong())

Saqlangan proseduralarning ilg'or qo'llab-quvvatlashi kelsa ko'rib chiqiladi.

JdbcTemplate bilan ishning optimal usullari

Konfiguratsiya tugagach, JdbcTemplate klassining nusxalari ipdan xavfsizdir. Bu muhim, chunki bu bitta JdbcTemplate nusxasini konfiguratsiya qilishingiz va keyinchalik bu umumiy havolani bir necha DAO (yoki reppozitoriy) ga xavfsiz kiritishingiz mumkinligini anglatadi. JdbcTemplate holatni saqlaydi, chunki u DataSourcega havolani saqlaydi, lekin bu holat dialog holati emas.

Optimal ish usuli JdbcTemplate klassidan foydalangan holda (va unga tegishli NamedParameterJdbcTemplate klassi) DataSourceni Spring konfiguratsiya faylida konfiguratsiya qilish va keyin bu umumiy DataSource beaniga bo'lgan qaramlikni DAO klasslariga kiritishdir. JdbcTemplate DataSource uchun ko'rsatkichda yaratiladi. Bu taxminan quyidagi ko'rinishdagi DAOga olib keladi:

Java

public class JdbcCorporateEventDao implements CorporateEventDao {
    private JdbcTemplate jdbcTemplate;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    // JDBC qo'llab-quvvatlaydigan CorporateEventDao metodlarining amalga oshirishlari keladi...
}

Kotlin

class JdbcCorporateEventDao(dataSource: DataSource) : CorporateEventDao {
    private val jdbcTemplate = JdbcTemplate(dataSource)
    // JDBC qo'llab-quvvatlaydigan CorporateEventDao metodlarining amalga oshirishlari keladi...
}

Quyidagi misolda mos XML-konfiguratsiya keltirilgan:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        https://www.springframework.org/schema/context/spring-context.xsd">
    <bean id="corporateEventDao" class="com.example.JdbcCorporateEventDao">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    <context:property-placeholder location="jdbc.properties"/>
</beans>

Aniq konfiguratsiya alternativasi komponentlarni skanerlash va qaramlikni kiritish uchun annotatsiyalarni qo'llab-quvvatlashdan foydalanishdir. Bu holda siz sinfni @Repository annotatsiyasi bilan belgilashingiz (bu uni komponentlarni skanerlash uchun nomzod qiladi) va DataSourceni o'rnatish metodini @Autowired annotatsiyasi bilan belgilashingiz mumkin. Quyidagi misolda qanday qilinishini ko'rsatadi:

Java

@Repository 
public class JdbcCorporateEventDao implements CorporateEventDao {
    private JdbcTemplate jdbcTemplate;
    @Autowired 
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource); 
    }
    // JDBC qo'llab-quvvatlaydigan CorporateEventDao metodlarining amalga oshirishlari keladi...
}

Sinfni @Repository bilan annotatsiya qilamiz.
Setter DataSourceni @Autowired bilan annotatsiya qilamiz.
Yangi JdbcTemplateni DataSource ni ishlatib yaratamiz.

Kotlin

@Repository 
class JdbcCorporateEventDao(dataSource: DataSource) : CorporateEventDao { 
    private val jdbcTemplate = JdbcTemplate(dataSource) 
    // JDBC qo'llab-quvvatlaydigan CorporateEventDao metodlarining amalga oshirishlari keladi...
}

Sinfni @Repository bilan annotatsiya qilamiz.
Constructor injection of the DataSource.
Yangi JdbcTemplateni DataSource ni ishlatib yaratamiz.

Quyidagi misolda mos XML-konfiguratsiya keltirilgan:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        https://www.springframework.org/schema/context/spring-context.xsd">
    <!-- Asosiy paketdagi @Component annotatsiyasi bilan sinflarni skanerlash va ularni bean sifatida konfiguratsiya qilish  -->
    <context:component-scan base-package="org.springframework.docs.test" />
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    <context:property-placeholder location="jdbc.properties"/>
</beans>

Agar siz Springdan JdbcDaoSupport klassidan foydalanayotgan bo'lsangiz, va turli DAO klasslari unga yaqin bo'lsa, sizning subclassingiz JdbcDaoSupport klassidan setDataSource(..) metodini meros oladi. Siz bu klassdan meros olishingiz yoki olmasligingiz mumkin. JdbcDaoSupport klassi faqat qulaylik uchun mavjud.

Yuqorida aytib o'tilgan shablonni initsializatsiya qilish uslubidan (yoki ishlatmaslikdan) qaysi biri siz tanlasangiz ham, bu vaqtda SQLni har safar bajarishingiz kerak bo'lganida yangi JdbcTemplate nusxasini yaratish zarur bo'lmaydi. Konfiguratsiya tugagach, bir JdbcTemplate nusxasi ipdan xavfsiz bo'ladi. Agar sizning ilovangiz bir nechta ma'lumotlar bazasiga murojaat qilsa, sizga bir nechta JdbcTemplate nusxalari kerak bo'lishi mumkin, bu bir nechta DataSourcesga va shunga mos ravishda bir nechta turli konfiguratsiyalangan JdbcTemplate nusxalariga ehtiyoj qiladi.

Oldingi Oldingi dars

Keyingisi Keyingi dars

Izohlar

TO VIEW ALL COMMENTS OR TO MAKE A COMMENT,
GO TO FULL VERSION

JdbcTemplate foydalanish

So'rovlar yaratish (SELECT)

Yangilash (INSERT, UPDATE va DELETE) yordamida JdbcTemplate

Boshqa JdbcTemplate operatsiyalari

JdbcTemplate bilan ishning optimal usullari

So'rovlar yaratish (`SELECT`)

Yangilash (`INSERT`, `UPDATE` va `DELETE`) yordamida `JdbcTemplate`

Boshqa `JdbcTemplate` operatsiyalari