Spring JdbcTemplate基本使用

1.开发步骤

①导入spring-jdbc和spring-tx依赖
②创建数据库表和实体
③创建JdbcTemplate对象
④执行数据库操作

2.导入依赖

//引入spring-jdbc、spring-tx依赖
implementation 'org.springframework:spring-jdbc:5.3.14'
implementation 'org.springframework:spring-tx:5.3.14'

3.创建数据库表和实体

data class Account(var name:String? = null, var money: Double = 0.0)

4.创建JdbcTemplate对象

	@Test
    // 测试JdbcTemplate开发步骤
    fun test1(){
        // 设置数据源对象
        val app = ClassPathXmlApplicationContext("applicationContext.xml")
        val dataSource = app.getBean("dataSource") as DruidDataSource

        val jdbcTemplate = JdbcTemplate()
        // 设置数据源对象 连接数据库
        jdbcTemplate.dataSource = dataSource
        // 执行数据
        val row = jdbcTemplate.update("insert into account values (?,?)", "zhangsan", 123.2)
        println(row)
    }

使用模板创建

	<!-- 自动配置数据源  -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <!-- JdbcTemplate  -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

	@Test
    fun test2(){
        // 设置数据源对象
        val app = ClassPathXmlApplicationContext("applicationContext.xml")
        val jdbcTemplate = app.getBean("jdbcTemplate") as JdbcTemplate

        // 执行数据
        val row = jdbcTemplate.update("insert into account values (?,?)", "lisi", 200)
        println(row)
    }

5.增删改查

增

	@Test
    fun test2(){
        // 设置数据源对象
        val app = ClassPathXmlApplicationContext("applicationContext.xml")
        val jdbcTemplate = app.getBean("jdbcTemplate") as JdbcTemplate

        // 执行数据
        val row = jdbcTemplate.update("insert into account values (?,?)", "lisi", 200)
        println(row)
    }

删

	@Test
    fun test3(){
        val app = ClassPathXmlApplicationContext("applicationContext.xml")
        val jdbcTemplate = app.getBean("jdbcTemplate") as JdbcTemplate

        // 删除数据
        val row = jdbcTemplate.update("delete from account where name=?","wangwu")
        println(row)
    }

改

	@Test
    fun test4(){
        val app = ClassPathXmlApplicationContext("applicationContext.xml")
        val jdbcTemplate = app.getBean("jdbcTemplate") as JdbcTemplate

        // 修改数据
        val row = jdbcTemplate.update("update account set money=? where name=?", 10000, "lisi")
        println(row)
    }

查（全部）

	@Test
    fun test5() {
        val app = ClassPathXmlApplicationContext("applicationContext.xml")
        val jdbcTemplate = app.getBean("jdbcTemplate") as JdbcTemplate

        // 查询全部
        val query:List<Account> = jdbcTemplate.query("select * from account", BeanPropertyRowMapper<Account>(Account::class.java))
        
        println(query)
        // 输出：[Account(name=zhangsan, money=123.2), Account(name=lisi, money=10000.0)]

    }

查（单条）

	@Test
    fun test6() {
        val app = ClassPathXmlApplicationContext("applicationContext.xml")
        val jdbcTemplate = app.getBean("jdbcTemplate") as JdbcTemplate

        // 查询单条
        val query:Account = jdbcTemplate.queryForObject("select * from account where name=?",BeanPropertyRowMapper<Account>(Account::class.java),"lisi")

        println(query)
        // 输出：Account(name=lisi, money=10000.0)


    }

聚合查询

	@Test
    fun test7() {
        val app = ClassPathXmlApplicationContext("applicationContext.xml")
        val jdbcTemplate = app.getBean("jdbcTemplate") as JdbcTemplate

        // 聚合查询
        val query:Long = jdbcTemplate.queryForObject("select count(*) from account ", Long::class.java)

        println(query)
        // 输出：2

    }