Spring基础学习——5.JdbcTemplate基本使用

2021-02-08

Spring基础学习——5.JdbcTemplate基本使用

5.1、JdbcTemplate概述

image.png

**

image.png

spring-tx 事物

配置pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
​
    <groupId>org.example</groupId>
    <artifactId>xiaobo_spring_jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>
​
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</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>
            <scope>test</scope>
        </dependency>
        <!-- 导入mysql坐标-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>
    </dependencies>
</project>

测试类测试与数据库的连接

package com.xiaobo.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
    //测试JdbcTest
    public void test1() throws PropertyVetoException {
​
        //创建数据源对象
        ComboPooledDataSource dataSource=new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        dataSource.setUser("root");
        dataSource.setPassword("123456");
​
        JdbcTemplate jdabcTemplate=new JdbcTemplate();
        //设置数据源对象
        jdabcTemplate.setDataSource(dataSource);
        jdabcTemplate.execute("select * from account");
        //执行操作
        int update = jdabcTemplate.update("insert into account values(?,?)", "xiaobo", 5000);
        System.out.println(update);
​
    }
}

5.2、Spring产生Jdbc对象

image.png

applicationContext配置文件

<?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 name="jdbcUrl" value="jdbc:mysql://localhost:3306/test"/>
        <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>
​
</beans>

测试类

@Test
//测试Spring 产生jdbc模板对象
public void test2() throws PropertyVetoException {
    ApplicationContext app=new ClassPathXmlApplicationContext("applicationContext.xml");
​
    JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);
    int row = jdbcTemplate.update("insert into account value (?,?)", "yingying", 2000);
    System.out.println(row);
}

将jdbc的配置文件单独提取出来

即在xml中引入外部的配置文件

1、编写jdbc配置文件

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

2、配置命名空间

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

3、引入外部文件

<!--加载jdbc.properties文件-->
<!--先配置上面的命名空间-->
<context:property-placeholder location="classpath:jdbc.properties"/>

4、将数据源的value利用${}进行替换

<!--数据源对象-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="${jdbc.driver}"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="user" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
</bean>

总览

<?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"/>

    <!--数据源对象-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

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

</beans>

5、编写测试代码

@Test
//测试Spring 产生jdbc模板对象
public void test2() throws PropertyVetoException {
    ApplicationContext app=new ClassPathXmlApplicationContext("applicationContext.xml");

    JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);
    int row = jdbcTemplate.update("insert into account value (?,?)", "yingying", 2000);
    System.out.println(row);
}

5.3、Jdbc的常用操作

image.png

回忆让Spring进行Junit集成测试步骤

1. 导入包 spring-test和spring-junit
2. 指定spring做一个驱动内核做操作即添加Runwith注释
   @RunWith(SpringJUnit4ClassRunner.class)
3. 指定配置文件
   @ContextConfiguration("classpath:applicationContext.xml")
4. 编写测试文件 测试谁注入谁

测试更新、删除类代码

package com.xiaobo.test;

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

/*
1. 导入包 spring-test和spring-junit
2. 指定spring做一个驱动内核做操作即添加Runwith注释
@RunWith(SpringJUnit4ClassRunner.class)
3. 指定配置文件
@ContextConfiguration("classpath:applicationContext.xml")
4. 编写测试文件 测试谁注入谁

 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateCRUDTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Test
    public void testUpdate(){
        jdbcTemplate.update("update account set money=? where name =?",10000,"yingying");
    }
    @Test
    public void testDelete(){
        int update = jdbcTemplate.update("delete from account where name =?", "yingying");
        System.out.println(update);
    }
}

测试查询代码

查询全部

@Test
public void testQueryAll(){
    //常用RowMapper<T> rowMapper 返回List<T> 帮助我们封装了结果集
    // BeanPropertyRowMapper 帮助我们实现实体封装类对象,需要我们指定泛型以及对应的字节码
    List<Account> query = jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
    System.out.println(query);
}

查询单个

@Test
public void testQueryOne(){
    Account query = jdbcTemplate.queryForObject("select * from account where name=?", new BeanPropertyRowMapper<Account>(Account.class), "yingying");
    System.out.println(query);
}

查询其他信息

@Test
public void testQueryCount(){
    Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
    System.out.println(count);
}

总览

package com.xiaobo.test;
​
import com.xiaobo.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;
​
/*
1. 导入包 spring-test和spring-junit
2. 指定spring做一个驱动内核做操作即添加Runwith注释
@RunWith(SpringJUnit4ClassRunner.class)
3. 指定配置文件
@ContextConfiguration("classpath:applicationContext.xml")
4. 编写测试文件 测试谁注入谁
​
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateCRUDTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Test
    public void testUpdate(){
        jdbcTemplate.update("update account set money=? where name =?",10000,"yingying");
    }
    @Test
    public void testDelete(){
        int update = jdbcTemplate.update("delete from account where name =?", "yingying");
        System.out.println(update);
    }
    @Test
    public void testQueryAll(){
        //常用RowMapper<T> rowMapper 返回List<T> 帮助我们封装了结果集
        // BeanPropertyRowMapper 帮助我们实现实体封装类对象,需要我们指定泛型以及对应的字节码
        List<Account> query = jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
        System.out.println(query);
    }
    @Test
    public void testQueryOne(){
        Account query = jdbcTemplate.queryForObject("select * from account where name=?", new BeanPropertyRowMapper<Account>(Account.class), "yingying");
        System.out.println(query);
    }
    @Test
    public void testQueryCount(){
        Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
        System.out.println(count);
    }
}

5.4、JdbcTemplate知识点总结

image.png


标题:Spring基础学习——5.JdbcTemplate基本使用
作者:xiaob0
地址:https://xiaobo.net.cn/articles/2021/02/08/1612779727794.html