【Spring】JdbcTemplate基本使用

1、JdbcTemplate基本使用

依赖包：

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

---

2、全手动：

实体类

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

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

    public String getName() {
        return name;
    }

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

    public int getMoney() {
        return money;
    }

    public void setMoney(int money) {
        this.money = money;
    }
}

在数据库中创建对应该实体的表

测试类

package com.yq.test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;

import java.beans.PropertyVetoException;

public class JdbcTemplateTest {

    @Test
    //测试JdbcTemplate开发步骤
    public void test1() throws PropertyVetoException {
        //设置数据源
        ComboPooledDataSource comboPooledDataSource=new ComboPooledDataSource();
        comboPooledDataSource.setDriverClass("com.mysql.jdbc.Driver");
        comboPooledDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        comboPooledDataSource.setUser("root");
        comboPooledDataSource.setPassword("root");


        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(comboPooledDataSource);

        int i = jdbcTemplate.update("INSERT INTO account VALUES (?,?)", "zhangshan", 5000);
        System.out.println(i);
    }
}

3、数据源可以用配置文件配置并注入

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test"></property>
        <property name="user" value="root"></property>
        <property name="password" value="root"></property>
    </bean>

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

</beans>

测试

 @Test
    public void test2(){
        ApplicationContext app= new ClassPathXmlApplicationContext("applicationContext.xml");
        //JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);
        JdbcTemplate jdbcTemplate = (JdbcTemplate) app.getBean("jdbcTemplate");

        int row = jdbcTemplate.update("INSERT INTO account VALUES (?,?)", "lisi", 6000);
        System.out.println(row);
    }

4、加载jdbc.properties配置文件

context命名空间
xmlns:context=“http://www.springframework.org/schema/context”
和http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd

<?xml version="1.0" encoding="UTF-8"?>
<beans
        xmlns="http://www.springframework.org/schema/beans"
        xmlns:context="http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="
       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
">

    <!--加载jdbc.properties配置文件-->
    <context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>



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

</beans>

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.user=root
jdbc.password=root

5、集成Junit测试

导包

<dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.0.5.RELEASE</version>
        </dependency>

增删改查的语句

@Test
//    查询条数
    public void testQueryCount(){
        //如果返回是简单对象，比如数目什么的int,long类型，用Long.class 接收即可
        Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
        System.out.println(count);
    }
    @Test
//    查询某个对象
    public void testQueryOne(){
        //如果返回是复杂对象(javaBean)，则new一个实体属性行映射类进行封装new BeanPropertyRowMapper<Account>(Account.class)
        Account account = jdbcTemplate.queryForObject("select * from account where money=?", new BeanPropertyRowMapper<Account>(Account.class), 6666);
        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);
    }
    @Test
//    删除
    public void testDelete(){
        int row = jdbcTemplate.update("DELETE FROM account WHERE name=?","asdasd");
        System.out.println(row);
    }
    @Test
//    更新
    public void testUpdate(){
        int row = jdbcTemplate.update("UPDATE account SET money=? WHERE name=?",6666,"lisi");
        System.out.println(row);
    }

7、知识要点

1. 导入spring-jdbc和spring-tx坐标
2. 创建数据库表和实体
3. 创建JdbcTemplate对象

JdbcTemplate jdbcTemplate = new JdbcTemplate();

jdbcTemplate.setDataSource(dataSource);

4. 执行数据库操作

• 更新操作
  jdbcTemplate.update(sql,params)

• 查询操作
  jdbcTemplate.query(sql,Mapper,params)

  jdbcTemplate.queryForObject(sql,Mapper,params)