Spring JdbcTemplate

在spring中为了更加方便的操作JDBC，在JDBC的基础之上定义了一个抽象层，此设计的目的是为不同类型的JDBC操作提供模板方法，每个模板方法都能控制整个过程，并允许覆盖过程中的特定任务，通过这种方式，可以尽可能保留灵活性，将数据库存取的工作量降到最低。

1、配置并测试数据源

pom.xml

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

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>5.2.6.RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.9.5</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>5.2.6.RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.2.6.RELEASE</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

db.properties

mysql.username=root
mysql.password=a123456
mysql.url=jdbc:mysql://localhost:3306/fztx
mysql.driverClassName=com.mysql.jdbc.Driver

spring.xml

<!--配置第三方bean-->
    <bean class="com.alibaba.druid.pool.DruidDataSource" id="dataSource">
        <property name="username" value="${mysql.username}"></property>
        <property name="password" value="${mysql.password}"></property>
        <property name="url"  value="${mysql.url}"></property>
        <property name="driverClassName" value="${mysql.driverClassName}"></property>
    </bean>

    <!--引入外部属性资源文件-->
    <context:property-placeholder location="db.properties"></context:property-placeholder>

JdbcTest.java

ClassPathXmlApplicationContext ioc;
    @Before
    public void before(){
        ioc = new ClassPathXmlApplicationContext("spring.xml");
    }

    @Test
    public void test01(){
        DruidDataSource bean = ioc.getBean(DruidDataSource.class);
        System.out.println(bean);
    }

2、给spring容器添加JdbcTemplate

spring容器提供了一个JdbcTemplate类，用来方便操作数据库。

 spring.xml

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

测试

@Test
    public void test02(){
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
        Long along = jdbcTemplate.queryForObject("select count(*) from t_user",Long.class);
        System.out.println(along);
    }

3.查询某个值，并以对象的方式返回

    /**
     * 查询实体
     */
    @Test
    public void test04(){
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
        // 数据库字段名和属性名一样 利用BeanPropertyRowMapper
        User user = jdbcTemplate.queryForObject("select * from t_user where id=1", new BeanPropertyRowMapper<User>(User.class));


        /*User user = jdbcTemplate.queryForObject("select * from t_user where id=1",
                (resultSet, i) -> {
                    User o=new User();
                    o.setId(resultSet.getInt("id"));
                    o.setRealname(resultSet.getString("realname"));
                    return o;
                });*/
        System.out.println(user);
    }

 

4.查询返回集合对象

/**
     * 查询List<实体>
     */
    @Test
    public void test05(){
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
        // 数据库字段名和属性名一样 利用BeanPropertyRowMapper
        List<User> user = jdbcTemplate.query("select * from t_user where id > 1", new BeanPropertyRowMapper<User>(User.class));


        /*User user = jdbcTemplate.queryForObject("select * from t_user where id=1",
                (resultSet, i) -> {
                    User o=new User();
                    o.setId(resultSet.getInt("id"));
                    o.setRealname(resultSet.getString("realname"));
                    return o;
                });*/
        System.out.println(user);
    }

5.新增

    /**
     * 新增
     */
    @Test
    public void test06(){
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
        // 数据库字段名和属性名一样 利用BeanPropertyRowMapper
        int result =
                jdbcTemplate.update("insert into t_user(realname,cardno,balance) values (?,?,?)",
                        "王麻子","12345677",800);

        System.out.println(result);
    }

 

6.修改

    /**
     * 修改
     */
    @Test
    public void test07(){
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
        // 数据库字段名和属性名一样 利用BeanPropertyRowMapper
        int result =
                jdbcTemplate.update("update t_user set balance=balance+200 where id=?",
                        4);

        System.out.println(result);
    }

 

 

 

7.删除

    /**
     * 删除
     */
    @Test
    public void test08(){
        JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
        // 数据库字段名和属性名一样 利用BeanPropertyRowMapper
        int result =
                jdbcTemplate.update("delete from t_user where id=?",
                        4);

        System.out.println(result);
    }

 

8、使用具备具名函数的JdbcTemplate 

spring.xml

<bean class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate" id="namedParameterJdbcTemplate">
        <constructor-arg type="javax.sql.DataSource" ref="dataSource"/>
    </bean>

    /**
     * 具名参数处理NamedParameterJdbcTemplate
     */
    @Test
    public void test09(){
        NamedParameterJdbcTemplate jdbcTemplate = ioc.getBean(NamedParameterJdbcTemplate.class);

        Map<String,Object> map=new HashMap<String,Object>();
        map.put("id",4);
        
        int result =
                jdbcTemplate.update("delete from t_user where id=:id",map);

        System.out.println(result);
    }

9、整合Dao、service

spring.xml

<!--扫描包-->
    <context:component-scan base-package="cn.tulingxueyuan"></context:component-scan>

UserDaoImpl.java

package com.tuling.dao.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.tuling.dao.IUserDao;
import com.tuling.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * @author fanzitianxing
 * @title: UserDaoImpl
 * @projectName spring_transaction
 * @description: TODO
 * @date 2021/7/1914:34
 */
@Repository
public class UserDaoImpl implements IUserDao {
    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setDataSource(DruidDataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }


    public User getUser() {
        return jdbcTemplate.queryForObject("select * from t_user where id=1",new BeanPropertyRowMapper<User>(User.class));
    }
}

UserServiceImpl.java

package com.tuling.service.impl;

import com.tuling.dao.IUserDao;
import com.tuling.entity.User;
import com.tuling.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * @author fanzitianxing
 * @title: UserServiceImpl
 * @projectName spring_transaction
 * @description: TODO
 * @date 2021/7/1914:31
 */
@Service
public class UserServiceImpl implements IUserService {
    @Autowired
    IUserDao userDao;
    public User getUser() {
        return userDao.getUser();
    }
}

测试：

    @Test
    public void test10(){
        IUserService userService = ioc.getBean(IUserService.class);
        User user = userService.getUser();
        System.out.println(user);
    }