spring中使用JdbcTemplate对数据库(增删查改)

1.JdbcTemplate概述

它是spring框架中提供的一个对象，是对原始繁琐的Jdbc ApI对象的简单封装。Spring框架为我们提供了很多操作模板类。例如：操作关系型数据的JdbcTemplate和MbernateTemplate,操作nosql数据库的RedisTemplate,操作消息队列的JmsTemplate等等。

2.JdbcTemplate开发步骤

(1)导入spring-jdbc和spring-tx坐标

(2)创建数据库表和实体

(3)创建Jdbc Template对象

(4)执行数据库操作

(1)导入spring-jdbc和spring-tx坐标

以下是在maven项目pom.xml文件中导入坐标
pom.xm

	<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.32</version>
    </dependency>
    <dependency>
        <groupId>c3p0</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.1.2</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.10</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.0.5.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-test</artifactId>
        <version>5.0.5.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.0.5.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>5.0.5.RELEASE</version>
     </dependency>
	 </dependencies>

(2)创建数据库表和实体类

创建一个数据库，数据库名称text,表account,设计表数据(name、money)

创建一个实体类

	package com.wange.domain;

public class Account {
    private String name;
    private double money;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getMoney() {
        return money;
    }

    public void setMoney(double money) {
        this.money = money;
    }
    @Override
    public String toString() {
        return "Account{" +
                "name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

(3)创建Jdbc Template对象

在资源测试文件夹下创建一个xml文件applicationContext.xml,把下面值改成自己的

<!--c3p0数据源对象-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        //
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/text"/>
        <property name="user" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    <!--jdbc模板对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

(4)执行数据库操作

package com.wange.test;

import com.wange.domain.Account;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateCRUDTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    //插入
    @Test
    public void testInsert(){
    int row = jdbcTemplate.update("insert into account values(?,?)", "tom", 5000);
        System.out.println(row);
    }
    //修改
    @Test
    public void testUpdate(){
    int row = jdbcTemplate.update("update account set money=? where name=?",10000,"tom");
        System.out.println(row);
    }
    //删除
    @Test
    public void testDelete(){
    int row = jdbcTemplate.update("delete from account where name=?","tom");
        System.out.println(row);
    }
    //聚合查询
    @Test
    public void testQueryCount(){
        Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
        System.out.println(count);
    }
    //查询一个
    @Test
    public void testQueryOne(){
        Account account = jdbcTemplate.queryForObject("select * from account where name=?", new BeanPropertyRowMapper<Account>(Account.class), "tom");
        System.out.println(account);
    }
    //查询所有
    @Test
    public void testQueryAll(){
        List<Account> accountList = jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
        System.out.println(accountList);
    }
}