Spring 中的JdbcTemplate

1.JdbcTemplate

• Jdbc是Spring框架中提供的一个对象，是对原始JDBC API的简单封装。 Spring框架为我们提供了很多的操作模板类。
• 操作关系型数据的：

JdbcTemplate
HibernateTemplate

• 操作nosql数据库的：

RedisTemplate

• 操作消息队列的：

JmsTemplate

• 我们今天的主角在spring-jdbc-5.2.5.RELEASE.jar中，我们在导包的时候，除了要导入这个jar包外，还需要导入spring-tx-5.2.5.RELEASE.jar（和事务相关的）

2.使用jdbcTemplate进行数据库操作

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = SpringConfig.class)
public class Test2 {
    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 最普通的测试
     */
    @Test
    public void test1(){
        String sql = "insert into t_channel(cid,cname,description) " +
                        "   values(222,'教育','教育')";
        jdbcTemplate.execute(sql);
    }

    /**
     * 添加测试
     */
    @Test
    public void test2(){
        String sql = "insert into t_channel(cid,cname,description) " +
                "   values(?,?,?)";
        jdbcTemplate.update(sql,23,"科学","科学");
    }

    /**
     * 更新测试
     */
    @Test
    public void test3(){
        String sql = "update t_channel set cname = ?,description = ? where cid = ?";
        jdbcTemplate.update(sql,"农业","农业",222);
    }

    /**
     * 删除测试
     */
    @Test
    public void test4(){
        String sql = "delete from t_channel where cid = ?";
        jdbcTemplate.update(sql,222);
    }

    /**
     * 查询列表测试
     */
    @Test
    public void test5(){
        class T implements RowMapper<Channel>{
            @Override
            public Channel mapRow(ResultSet rs, int i) throws SQLException {
                Channel c = new Channel();
                c.setCid(rs.getInt("cid"));
                c.setCname(rs.getString("cname"));
                c.setDescription(rs.getString("description"));
                return c;
            }
        }
        String sql = "select * from t_channel";
        List<Channel> list = jdbcTemplate.query(sql,new T());
        System.out.println(list);
    }

    /**
     * 查询列表测试
     */
    @Test
    public void test6(){

        String sql = "select cid,cname,description from t_channel";
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
        System.out.println(list);
        for(Map<String,Object> row : list){
            System.out.println(row.get("cid"));
            System.out.println(row.get("cname"));
            System.out.println(row.get("description"));
        }
    }

    /**
     * 查询一条记录测试
     */
    @Test
    public void test7(){

        String sql = "select cid,cname,description from t_channel where cid = ?";
        Channel c = jdbcTemplate.queryForObject(sql, new RowMapper<Channel>() {
            @Override
            public Channel mapRow(ResultSet rs, int i) throws SQLException {
                Channel c = new Channel();
                c.setCid(rs.getInt("cid"));
                c.setCname(rs.getString("cname"));
                c.setDescription(rs.getString("description"));
                return c;
            }
        },9);

        System.out.println(c);

    }

    /**
     * 查询一条基本类型(Integer,String等)数据测试
     */
    @Test
    public void test8(){

        String sql = "select count(*) from t_channel";
        Integer a = jdbcTemplate.queryForObject(sql,Integer.class);

        System.out.println(a);

    }
}

3.事务管理

3.1编程式事务

spring提供事务管理器，可以进行事务管理

我们通过一个转账示例来说明事务的细节

1.创建一个帐户表

create table t_account(
  accountName varchar(20),
  money int
)

2.添加张三李四两个帐号

insert into t_account values('张三',10000); -- 添加张三帐户
insert into t_account values('李四',10000); -- 添加李四帐户

3.创建帐户实体类

package edu.xalead.entity;

import lombok.Data;
import org.springframework.stereotype.Component;

@Data
public class Account {
    private String accountName;
    private int money;
}

4.创建配置类

package edu.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.*;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
@PropertySource("classpath:application.properties")
@ComponentScan(basePackages = "edu.xalead")
public class Config {
    @Value("${jdbc.driverClass}")
    private String driverClass;
    @Value("${jdbc.url}")
    private String url;
    @Value("${jdbc.username}")
    private String username;
    @Value("${jdbc.password}")
    private String password;

    @Bean
    public DataSource dataSource(){
        DruidDataSource ds = new DruidDataSource();
        ds.setDriverClassName(driverClass);
        ds.setUrl(url);
        ds.setUsername(username);
        ds.setPassword(password);
        return ds;
    }
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource){
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        return jdbcTemplate;
    }
}

5.创建配置类所需要的属性文件application.properties

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/cms
jdbc.username=root
jdbc.password=root

可以有些学者的数据库添加的时候会出现？？，我们只需要在URL后面加上?useUnicode=true&characterEncoding=UTF8，即

jdbc.url=jdbc:mysql://localhost:3306/cms?useUnicode=true&characterEncoding=UTF8

6.创建dao接口及实现类

package edu.xalead.dao;

import edu.xalead.entity.Account;
import edu.xalead.entity.Channel;

public interface ChannelDao {
    /**
     * 根据帐号名查询帐号
     * @param accountName
     * @return
     */
    public Account findByAccountName(String accountName);

    /**
     * 修改帐号
     * @param account
     */
    public void updateAccount(Account account);
}
//实现类
package edu.xalead1.dao;

import edu.xalead.entity.Account;
import edu.xalead.entity.Channel;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;

@Repository("channelDao")
public class ChannelDaoImpl implements ChannelDao {
    @Resource
    private JdbcTemplate jdbcTemplate;
    public Account findByAccountName(String accountName) {
        String sql = "select * from t_account where accountName = ?";

        return jdbcTemplate.queryForObject(sql, new RowMapper<Account>() {
            public Account mapRow(ResultSet resultSet, int i) throws SQLException {
                Account a = new Account();
                a.setAccountName(resultSet.getString("accountName"));
                a.setMoney(resultSet.getInt("money"));
                return a;
            }
        },accountName);
    }

    public void updateAccount(Account account) {
        String sql = "update t_account set money = ? where accountName = ?";
        jdbcTemplate.update(sql,account.getMoney(),account.getAccountName());
    }
}

7.Service接口及实现类

接口

package edu.xalead.service;

import edu.xalead.entity.Channel;

public interface ChannelService {
    public void addChannel(Channel channel);
    /**
     * 转帐
     */
    public void zhuangZhang();
}

实现类

@Service("channelService")
public class ChannelServiceImpl implements ChannelService {
    @Resource
    private DataSource dataSource;
    /**
     * 转帐
     */
    public void zhuangZhang(){
        //默认的事务定义
        TransactionDefinition definition = new DefaultTransactionDefinition();
        //实例化事务管理器，
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSource);


        String account1 = "张三";
        String account2 = "李四";

        Account a1 = channelDao.findByAccountName(account1);

        Account a2 = channelDao.findByAccountName(account2);

        //转帐
        a1.setMoney(a1.getMoney() - 1000);//张三帐号的存款减1000
        a2.setMoney(a2.getMoney() + 1000);//李四帐号的存款加1000
        //开启事务,开启事务时，要指定事务的定义
        TransactionStatus transactionStatus = null;

        try {
            transactionStatus = transactionManager.getTransaction(definition);
            //转帐实际是两个更新操作
            channelDao.updateAccount(a1);//更新张三的帐户
//            if(1==1){
//                throw new RuntimeException("异常！");
//            }
            channelDao.updateAccount(a2);//更新李四的帐户
            //提交，无异常则提交,开启事务后对数据库的更新全部持久化
            transactionManager.commit(transactionStatus);
        } catch (RuntimeException e) {
            e.printStackTrace();
            //回滚，有异常则回滚，回滚意味着开启事务后所有数据库的更新全部取消
            transactionManager.rollback(transactionStatus);
        }
    }
}

8.测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = Config.class)
public class Test1 {
    @Resource
    private ChannelService channelService;
    @Test
    public void test1(){
        channelService.zhuangZhang();
    }
}

若有误，请指教！！！