Jdbc Template的增删改查

Spring中提供了JDBC模板jdbcTemplate，对数据库进行CRUD操作。
1.JDBCTemplate提供的增删改查的方法：
(1) update:执行增加、修改、删除等语句
update(String sql, Object args[ ])

(2) query:查询多个对象
query(String sql, RowMapper rowMapper)

(3) queryForObject:查询单个对象
queryForObject(String sql, RowMapper rowMapper, Object args[ ])

BeanPropertyRowMapper：

一个类，接口是RowMapper。能将查询到的结果集的每一行都转化成我们需要的类型的java对象。
查询时使用BeanPropertyRowMapper做映射可以查询一个具体类对象

JDBCTemplate实现增删改查的步骤：
将有数据库连接池的资源文件dataSource.properties注入到JdbcTemplate对象中,向DAO层注入JdbcTemplate对象,最后DAO层注入到Service层

实例：

导入JAR包：
数据库连接池JAR包，Oracle数据库JAR包，Spring框架5个基础JAR包，Spring JDBC的JAR包，Spring事务处理的JAR包（spring-tx），测试包

建表：
create table STUDENT
(
id NUMBER not null,
name VARCHAR2(255),
age NUMBER
)

方法一：配置类实现

创建实体类：

@Data
public class Student {
	private int id;
	private String name;
	private int age;
}

先创建druid连接池配置信息的资源文件dataSource.properties

jdbc.driverClass=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1522:xe
jdbc.username=jy
jdbc.password=jy
initialSize=5
maxActive=10

创建读取上述资源文件的类：

@PropertySource("classpath:datasource.properties")
public class PropertiesConfig {

}

创建配置类，配置数据源和JDBC模板

@Configuration
@ComponentScan("com.spring.jdbc")
@Import(PropertiesConfig.class)
public class StudentConfig {
	@Value("${jdbc.driverClass}")
	private String driverClassName;
	@Value("${jdbc.url}")
	private String url;
	@Value("${jdbc.username}")
	private String username;
	@Value("${jdbc.password}")
	private String password;
	@Bean
	public DataSource dataSource() {
		//创建数据库连接池对象
		DruidDataSource dds = new DruidDataSource();
		//设置数据源
		dds.setDriverClassName(driverClassName);
		dds.setUrl(url);
		dds.setUsername(username);
		dds.setPassword(password);
		return dds;
	}
	@Bean
	public JdbcTemplate jdbcTemplate() {
		//创建jdbc模板对象
		JdbcTemplate jdbcTemplate = new JdbcTemplate();
		//设置该模板对象的数据库连接池，从而使jdbc知道要操作哪个数据库
		jdbcTemplate.setDataSource(dataSource());
		return jdbcTemplate;
	}
}

DAO层接口：

public interface IStudentDao {
	public void add(Student student);
	public void delete(int id);
	public void update(int id,Student student); 
	public Student queryById(int id);
	public List<Student> queryAll();
}

DAO层实现类：

@Repository
public class StudentDaoImpl implements IStudentDao{
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Override
	public void add(Student student) {
		String sql = "insert into student values(?,?,?)";
		//count:数据条数
		int count = jdbcTemplate.update(sql,student.getId(),student.getName(),student.getAge());	
		if(count>0) {
			System.out.println("添加成功");
		}else {
			System.out.println("添加失败");
		}
	}
	@Override
	public void delete(int id) {
		String sql = "delete from student where id = ?";
		int count = jdbcTemplate.update(sql, id);
		if(count>0) {
			System.out.println("删除成功");
		}else {
			System.out.println("删除失败");
		}	
	}
	@Override
	public void update(int id, Student student) {
		String sql = "update student set name=?,age=? where id = ?";
		int count = jdbcTemplate.update(sql,student.getName(),student.getAge(),id);	
		if(count>0) {
			System.out.println("修改成功");
		}else {
			System.out.println("修改失败");
		}
	}
	@Override
	public Student queryById(int id) {
		String sql = "select * from student where id = ?";
		//自动映射
		RowMapper<Student> rowMapper = new BeanPropertyRowMapper<Student>(Student.class);
		Student student = jdbcTemplate.queryForObject(sql, rowMapper, id);
		return student;
	}

	@Override
	public List<Student> queryAll() {
		String sql = "select * from student";
		RowMapper<Student> rowMapper = new BeanPropertyRowMapper<Student>(Student.class);
		List<Student> students = jdbcTemplate.query(sql, rowMapper);
		return students;
	}
}

业务层接口：

public interface IStudentService {
	public void add(Student student);
	public void delete(int id);
	public void update(int id,Student student);
	public Student queryById(int id);
	public List<Student> queryAll();
}

业务层实现类：

@Service
public class StudentServiceImpl implements IStudentService{
	@Autowired
	private IStudentDao studentDao;
	@Override
	public void add(Student student) {
		studentDao.add(student);		
	}
	@Override
	public void update(int id, Student student) {
		studentDao.update(id, student);	
	}
	@Override
	public void delete(int id) {
		studentDao.delete(id);
	}
	@Override
	public Student queryById(int id) {
		Student student = studentDao.queryById(id);
		return student;
	}
	@Override
	public List<Student> queryAll() {
		List<Student> students = studentDao.queryAll();
		return students;
	}
}

测试类：

@ContextConfiguration：Spring整合JUnit4测试时，使用注解引入配置文件或配置类
引入配置类时：@ContextConfiguration(classes =配置类名.class)
引入配置文件时：@ContextConfiguration(locations = {“classpath:配置文件名.xml”})

@RunWith(SpringRunner.class)
@ContextConfiguration(classes = StudentConfig.class)
public class StudentTest {
	@Autowired
	private IStudentService studentService;
	@Test
	public void testAdd() {
		Student student = new Student();
		student.setId(4);
		student.setName("刘雪");
		student.setAge(21);
		studentService.add(student);
	}
	@Test
	public void testDelete() {
		studentService.delete(4);
	}
	@Test
	public void testUpdate() {
		Student student = new Student();
		student.setName("张涛");
		student.setAge(23);
		studentService.update(2, student);
	}
	@Test
	public void testQueryById() {
		Student student = studentService.queryById(3);
		System.out.println(student);
	}
	@Test
	public void testQueryAll() {
		List<Student> students = studentService.queryAll();
		for(Student student:students) {
			System.out.println(student);
		}
	}
}

方法二：配置文件实现

实体层、数据访问层、业务层同上
配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	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">
	<!-- 指定需要扫描的包，使注解生效 -->
	<context:component-scan base-package="com.spring.jdbc"/>
	<!-- 导入dataSource.properties文件 -->
	<context:property-placeholder location="classpath:dataSource.properties"/>
	<!-- 创建数据源并配置 -->
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
		<!-- 数据库驱动 -->
		<property name="driverClassName" value="${jdbc.driverClass}"/>
		<!-- 连接数据库的URL -->
		<property name="url" value="${jdbc.url}"/>
		<!-- 连接数据库的用户名 -->
		<property name="username" 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>

测试类将@ContextConfiguration注解括号内的内容改为如下：
@ContextConfiguration(locations = {“classpath:applicationContext.xml”})