Spring中的Jdbc模板

1.Jdbc模板概述
它是spring框架中提供的一个对象，是对原始Jdbc API对象的简单封装。
spring框架为我们提供了很多的操作模板类

A, helloworld步骤
1.maven引入jar包(注意：需要导入c3p0的jar包)

<!-- jdbc -->
		<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-jdbc</artifactId>
		    <version>4.2.4.RELEASE</version>
		</dependency>
		<!-- 事务 -->
		<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-tx</artifactId>
		    <version>4.2.4.RELEASE</version>
		</dependency>
<dependency>
			<groupId>com.mchange</groupId>
			<artifactId>c3p0</artifactId>
			<version>0.9.5.2</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.0.2</version>
		</dependency>

2.创建测试表

CREATE TABLE account(
	id BIGINT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(40),
	money DOUBLE
)CHARACTER SET utf8 COLLATE utf8_general_ci;

3.将JdbcTemplate交给Spring管理

<!-- 配置数据源 -->
   <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/shop"></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>

4.创建Account实体类

public class Account implements Serializable{

	private static final long serialVersionUID = 1L;
	private Long id;
	private String name;
	private Double money;
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Double getMoney() {
		return money;
	}
	public void setMoney(Double money) {
		this.money = money;
	}
	@Override
	public String toString() {
		return "Account [id=" + id + ", name=" + name + ", money=" + money + "]";
	}
}

5.创建AccountDao接口

public interface AccountDao {
	public void save(Account account);
}

6.创建AccountDaoImpl实现类

public class AccountDaoImpl  implements AccountDao {
	private JdbcTemplate jdbcTemplate;
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	@Override
	public void save(Account account) {
		this.jdbcTemplate.update("insert into account(name,money) values(?,?)",account.getName(),account.getMoney());
	}
}

7.编写测试

/**
	 * 测试保存
	 */
	@Test
	public void test2(){
		Account account = new Account();
		account.setName("JAY");
		account.setMoney(1000.0);
		accountDao.save(account);
	}

8.使用Jdbc模板完成CRUD

新增：

@Test
	public void test1(){
		jdbcTemplate.update("insert into account(name,money) values(?,?)","李四",1000.0);
	}

修改数据:

@Test
	public void test2(){
		jdbcTemplate.update("update account set money = ? where id = ?",1100.0,1);
	}

.删除数据

@Test
	public void test3(){
		jdbcTemplate.update("delete from account where id = ?",2);
	}

.查询某列的值

@Test
	public void test5(){
		double money = jdbcTemplate.queryForObject("select money from account where id = ?", Double.class, 1);
		System.out.println(money);
	}

9.查询一个对象
1.创建实体类 ()

public class Account implements Serializable{

	private static final long serialVersionUID = 1L;
	private Long id;
	private String name;
	private Double money;
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Double getMoney() {
		return money;
	}
	public void setMoney(Double money) {
		this.money = money;
	}
	@Override
	public String toString() {
		return "Account [id=" + id + ", name=" + name + ", money=" + money + "]";
	}
}

2.创建RowMapper

public class AccountRowMapper implements RowMapper<Account>{

/**
	 * 如何把账户表里的一行数据形成账户对象
	 */
	@Override
	public Account mapRow(ResultSet rs, int rownum) throws SQLException {
		Account account = new Account();
		account.setId(rs.getLong("id"));
		account.setName(rs.getString("name"));
		account.setMoney(rs.getDouble("money"));
		return account;
	}

}

3.查询得到一个对象

/**
	 * JdbcTemplate之查询一个对象
	 */
	@Test
	public void test4(){
		AccountRowMapper mapper = new AccountRowMapper();
		Account account = jdbcTemplate.queryForObject("select * from account where id = ?", mapper, 1);
		System.out.println(account);
	}

4.查询一个集合

@Test
	public void test6(){
		AccountRowMapper rowMapper = new AccountRowMapper();
		List<Account> accounts = jdbcTemplate.query("select * from account", rowMapper);
		for(int i = 0;i < accounts.size();i++){
			System.out.println(accounts.get(i));
		}
	}

10 在DAO中使用JdbcTemplate的两种方式

方式一：在DAO中直接注入JdbcTemplate

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

	@Override
	public void save(Account account) {
		jdbcTemplate.update("insert into account(name,money) values(?,?)", account.getName(),account.getMoney());
	}

}

.把DAO配置到Spring中

  <bean id="accountDao" class="cn.icbc.dao.impl.AccountDaoImpl">
       	<property name="jdbcTemplate" ref="jdbcTemplate"></property>
  </bean>

方式二：在DAO中使用JdbcDaoSupport

public class AccountDaoImpl extends JdbcDaoSupport implements AccountDao {
	
	@Override
	public void save(Account account) {
		this.getJdbcTemplate().update("insert into account(name,money) values(?,?)", account.getName(),account.getMoney());
	}

}

给DAO注入DataSource

<bean id="accountDao" class="cn.icbc.dao.impl.AccountDaoImpl">
       <property name="dataSource" ref="dataSource"></property>
</bean>