Ko'rsatmalarni bajarish

SQL ko'rsatmalarni bajarish uchun ko'p kod talab etilmaydi. Faqat DataSource va JdbcTemplate kerak, shu jumladan JdbcTemplate bilan birga keladigan yordamchi metodlar. Quyidagi misolda yangi jadval yaratadigan minimal, lekin to'liq funksional sinf nimalardan iborat bo'lishi kerakligi ko'rsatilgan:

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class ExecuteAStatement {
    private JdbcTemplate jdbcTemplate;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    public void doExecute() {
        this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
    }
}

import javax.sql.DataSource
import org.springframework.jdbc.core.JdbcTemplate
class ExecuteAStatement(dataSource: DataSource) {
    private val jdbcTemplate = JdbcTemplate(dataSource)
    fun doExecute() {
        jdbcTemplate.execute("create table mytable (id integer, name varchar(100))")
    }
}

So'rovlarni bajarish

Ba'zi so'rov metodlar yagona qiymatni qaytaradi. Bir qator yoki maxsus qiymatni olish uchun queryForObject(..) dan foydalaning. Bu metod qaytarilgan Type ni JDBC dan Java-klassga o'zgartiradi, bu argument sifatida uzatiladi. Agar tur o'zgartirilishi noto'g'ri bo'lsa, InvalidDataAccessApiUsageException istisnosi hosil bo'ladi. Quyidagi misolda ikki so'rov metodi bor, biri int, ikkinchisi esa String uchun:

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class RunAQuery {
    private JdbcTemplate jdbcTemplate;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    public int getCount() {
        return this.jdbcTemplate.queryForObject("select count(*) from mytable", Integer.class);
    }
    public String getName() {
        return this.jdbcTemplate.queryForObject("select name from mytable", String.class);
    }
}

import javax.sql.DataSource
import org.springframework.jdbc.core.JdbcTemplate
class RunAQuery(dataSource: DataSource) {
    private val jdbcTemplate = JdbcTemplate(dataSource)
    val count: Int
        get() = jdbcTemplate.queryForObject("select count(*) from mytable")!!
    val name: String?
        get() = jdbcTemplate.queryForObject("select name from mytable")
}

Yagona natijaga ega so'rov metodlaridan tashqari, bir nechta metodlar so'rov qaytargan har bir qator uchun yozuv bilan ro'yxat qaytaradi. Eng ko'p turdagi metod queryForList(..) bo'lib, List qaytaradi, har bir element esa Map bo'lib, har bir ustun uchun bitta yozuv beradi, ustun nomini kalit sifatida ishlatadi. Agar avvalgi misolga barcha qatorlarni olish uchun metod qo'shilsa, u quyidagicha bo'lib ko'rinishi mumkin:

private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public List<Map<String, Object>> getList() {
    return this.jdbcTemplate.queryForList("select * from mytable");
}

private val jdbcTemplate = JdbcTemplate(dataSource)
fun getList(): List<Map<String, Any>> {
    return jdbcTemplate.queryForList("select * from mytable")
}

Olingan ro'yxat quyidagicha ko'rinadi:

[{name=Bob, id=1}, {name=Mary, id=2}]

Ma'lumotlar bazasini yangilash

Quyidagi misolda ma'lum bir asosiy kalit uchun ustun yangilanadi:

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class ExecuteAnUpdate {
    private JdbcTemplate jdbcTemplate;
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    public void setName(int id, String name) {
        this.jdbcTemplate.update("update mytable set name = ? where id = ?", name, id);
    }
}

import javax.sql.DataSource
import org.springframework.jdbc.core.JdbcTemplate
class ExecuteAnUpdate(dataSource: DataSource) {
    private val jdbcTemplate = JdbcTemplate(dataSource)
    fun setName(id: Int, name: String) {
        jdbcTemplate.update("update mytable set name = ? where id = ?", name, id)
    }
}

Oldingi misolda SQL ko'rsatmasi parametrlaringiz uchun plaseholderlarni o'z ichiga oladi. Siz parametr qiymatlarini o'zgaruvchi uzunlikdagi argumentlar (varargs) sifatida yoki alternativ tarzda, ob'ektlar massivi sifatida o'tkazishingiz mumkin. Shunday qilib, primitiv turlaringizni primitiv turlar uchun o'rash klasslariga qo'l bilan joylash yoki avtomatik o'rashdan foydalanishingiz kerak.

Avtomatik yaratilgan kalitlarni olish

update() yordamchi metodi ma'lumotlar bazasi tomonidan yaratilgan asosiy kalitlarni olishni qo'llab-quvvatlaydi. Bu qo'llab-quvvatlash JDBC 3.0 standartining bir qismidir. Batafsil ma'lumotni 13.6 bobida topasiz. Metod birinchi argument sifatida PreparedStatementCreator qabul qiladi va u kerakli qo'shish ko'rsatmasini belgilaydi. Boshqa argument KeyHolder bo'lib, muvaffaqiyatli yangilanishdan so'ng ishlab chiqarilgan kalitni o'z ichiga oladi. Tegishli PreparedStatement yaratish uchun yagona standart yo'q (bu metodning imzosi shunday bo'lishini izohlaydi). Quyidagi misol Oracle-da ishlaydi, lekin boshqa platformalarda ishlamasligi mumkin:

final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
    PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] { "id" });
    ps.setString(1, name);
    return ps;
}, keyHolder);
// keyHolder.getKey() endi ishlab chiqarilgan kalitni o'z ichiga oladi

val INSERT_SQL = "insert into my_test (name) values(?)"
val name = "Rob"
val keyHolder = GeneratedKeyHolder()
jdbcTemplate.update({
    it.prepareStatement (INSERT_SQL, arrayOf("id")).apply { setString(1, name) }
}, keyHolder)
// keyHolder.getKey() endi ishlab chiqarilgan kalitni o'z ichiga oladi