Spring的数据库开发之JdbcTemplate

一，在XML文件中配置JDBC

在xml文件中配置数据源和JDBC模板，然后注入需要使用该模板的bean中。配置数据源需要创建一个DriverManagerDataSource的bean，并设置其四个属性值，数据库驱动driverClassName，连接数据库的链接url，连接数据库的用户名username，连接数据库的密码password。配置JDBC模板则需要新建一个JdbcTemplate的bean，并设置其属性dataSource，指向刚才创建的DriverManagerDataSource的bean。
代码如下

<?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="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <bean id="accountDao" class="com.itheima.jdbc.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
</beans>

二，基于JdbcTemplate的增删改查

下面结束关于JdbcTemplate类的一些数据库操作方法，请自行在数据库中新建spring数据库
1. execute()
execute(String sql)方法能够完成执行SQL语句的功能。
2. update()
该方法可以完成插入，更新和删除数据的操作。
常用的方法如下
* int update(String sql)：执行sql语句，返回受影响的行数
* int update(PreparedStatementCreator psc)：执行从PreparedStatementCreator返回的语句，并返回受影响的行数
* int update(String sql, PreparedStatementSetter pss)：通过PreparedStatementSetter设置sql语句中的参数，返回受影响的行数
* int update(String sql, Object… args)：使用Object…设置sql语句中的参数，要求参数不能为NULL，返回受影响的行数
3. query()
* List query(String sql, RowMapper rowMapper)：执行String类型参数提供的sql语句，并通过RowMapper返回一个List类型的结果
* List query(String sql, PreparedStatementSetter pss, RowMapper rowMapper)：根据String类型参数提供的sql语句创建PreparedStatement对象，通过RowMapper将结果返回List中
* List query(String sql, Object[] args, RowMapper rowMapper)：使用Object[]值来设置sql语句中的参数值，采用RowMapper中的回调方法可以直接返回List类型的数据
* Object queryForObject(String sql, RowMapper rowMapper, Object… args)：将args参数绑定到sql语句中，通过RowMapper返回一个Object类型的单行记录
* queryForList(String sql, Object[] args, class elementType)：返回多行数据的结果，但必须是返回列表，elementType参数返回的是List元素类型

下面是使用JdbcTemplate的代码演示

package com.itheima.jdbc;

public class Account {
    private Integer id;
    private String username;
    private Double balance;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Double getBalance() {
        return balance;
    }

    public void setBalance(Double balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account [id=" + id +", "
                + "username=" + username +
                ", balance=" + balance + "]";
    }
}

package com.itheima.jdbc;

import java.util.List;

public interface AccountDao {
    //添加
    public int addAccount(Account account);
    //更新
    public int updateAccount(Account account);
    //删除
    public int deleteAccount(int id);
    //通过id查询
    public Account findAccountById(int id);
    //查询所有账户
    public List<Account> findAllAccount();
}

package com.itheima.jdbc;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.List;

public class AccountDaoImpl implements AccountDao{
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    //添加账号
    @Override
    public int addAccount(Account account) {
        //定义SQL
        String sql = "insert into account(username,balance) value(?,?)";
        //定义数组来存储SQL语句中的参数
        Object[] obj = new Object[] {
                account.getUsername(),
                account.getBalance()
        };
        //执行添加操作，返回的是受SQL语句影响的记录条数
        int num = this.jdbcTemplate.update(sql, obj);
        return num;
    }

    //更新账号
    @Override
    public int updateAccount(Account account) {
        //定义SQL
        String sql = "UPDATE account set username=?, balance=? where id=?";
        //定义数组来存储SQL语句中的参数
        Object[] params = new Object[] {
                account.getUsername(),
                account.getBalance(),
                account.getId()
        };
        //执行更新操作，返回的是受SQL语句影响的记录条数
        int num = this.jdbcTemplate.update(sql, params);
        return num;
    }

    //删除账号
    @Override
    public int deleteAccount(int id) {
        //定义SQL
        String sql = "delete from account where id = ?";
        //执行删除操作，返回的是受SQL语句影响的记录条数
        int num = this.jdbcTemplate.update(sql, id);
        return num;
    }

    //通过id查询账户数据信息
    @Override
    public Account findAccountById(int id) {
        String sql = "select * from account where id = ?";
        RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
        return this.jdbcTemplate.queryForObject(sql, rowMapper, id);
    }

    //查询所有账号信息
    @Override
    public List<Account> findAllAccount() {
        String sql = "select * from account";
        RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
        return this.jdbcTemplate.query(sql, rowMapper);
    }
}

package com.itheima.jdbc;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class JdbcTemplateTest {
    @Test
    public void mainTest() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("com/itheima/jdbc/applicationContext.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        jdbcTemplate.execute("CREATE TABLE account(" +
                "id INT PRIMARY KEY auto_increment," +
                "username VARCHAR (50)," +
                "balance DOUBLE)");
        System.out.println("帐户表account创建成功!");
    }

    @Test
    public void addAccountTest() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("com/itheima/jdbc/applicationContext.xml");
        AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
        Account account = new Account();
        account.setUsername("tom");
        account.setBalance(1000.00);
        int num = accountDao.addAccount(account);
        if(num > 0) {
            System.out.println("成功插入了"+num+"条数据");
        }else {
            System.out.println("插入操作执行失败!");
        }
    }

    @Test
    public void updateAccountTest() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("com/itheima/jdbc/applicationContext.xml");
        AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
        Account account = new Account();
        account.setId(1);
        account.setUsername("tom");
        account.setBalance(2000.00);
        int num = accountDao.updateAccount(account);
        if(num > 0) {
            System.out.println("成功修改了"+num+"条数据!");
        }else {
            System.out.println("修改操作执行失败！");
        }
    }

    @Test
    public void deleteAccountTest() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("com/itheima/jdbc/applicationContext.xml");
        AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
        int num = accountDao.deleteAccount(1);
        if(num > 0) {
            System.out.println("成功删除了"+num+"条数据!");
        }else {
            System.out.println("删除操作执行失败!");
        }
    }

    @Test
    public void findAccountByIdTest() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("com/itheima/jdbc/applicationContext.xml");
        AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
        Account account = accountDao.findAccountById(1);
        System.out.println(account);
    }

    @Test
    public void findAllAccountTest() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("com/itheima/jdbc/applicationContext.xml");
        AccountDao accountDao = (AccountDao) applicationContext.getBean("accountDao");
        List<Account> accounts = accountDao.findAllAccount();
        for(Account c : accounts) {
            System.out.println(c);
        }
    }
}