简介
- JdbcTemplate是Spring提供的访问数据库的方式之一,是Spring中最基本、最底层的访问数据库的实现方式。
- 通过使用JdbcTemplate,开发者无需关心数据库连接的创建和关闭细节,只需要专注于实现业务逻辑即可。
- 在使用JdbcTemplate的时候,只需要声明即可,无需自己初始化,因为Spring在初始化数据源datasource的时候会自己创建JdbcTemplate的实例。
准备
首先,我们需要新建一个Spring Boot工程,然后需要引入web、jdbc模块,还需要mysql的java驱动包。在pom文件中表现如下:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
PS:web模块的引入是必须的,否则运行报错。
然后,我们需要配置数据源,在application.properties
中配置如下信息:
#GMT%2B8代表东八区
spring.datasource.url=jdbc:mysql://localhost:3306/test01?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=root
#无需再手动指定驱动类型
#Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
PS:数据库的驱动类无需再人为手动指定,系统会自动加载。
其后,我们需要创建好数据库和表,并插入数据,然后准备一个Java实体类,这部分不是本文重点,此处略过。
最后,在使用JdbcTemplate之前,我们需要在service实现类中声明它:
@Service
public class StudentServiceImpl implements StudentService {
@Autowired
private JdbcTemplate jdbcTemplate;
}
JdbcTemplate的实例化由Spring自动创建,开发人员直接使用即可。
我们使用单元测试来验证程序执行的正确性,为此,需要准备如下的测试类:
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = BootJdbcApplication.class, webEnvironment = SpringBootTest.WebEnvironment.DEFINED_PORT)
public class StudentServiceImplTest {
@Autowired
private StudentService studentService;
}
一、查询
1.1 查询记录数
@Override
public int countStudentByName(String name) {
String sql = "select count(*) from test_student where name = ?";
return this.jdbcTemplate.queryForObject(sql, Integer.class, name);
}
@Test
public void countStudent(){
Integer cnt = studentService.countStudentByName("Jack");
assertEquals(new Integer("2"), cnt);
}
1.2 查询对象
关于如下实例中Row Mapper的使用可以参考下一篇文章《Spring JdbcTemplate中关于RowMapper的使用实例》。
@Override
public Student getStudentByName(String name) {
String sql = "select name, gender from test_student where name = ?";
Student student = this.jdbcTemplate.queryForObject(sql, new Object[]{name}, new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet rs, int i) throws SQLException {
Student s = new Student();
s.setName(rs.getString("name"));
s.setGender(rs.getString("gender"));
return s;
}
});
return student;
}
@Test
public void getStudentByName(){
Student student = studentService.getStudentByName("李白");
assertEquals("李白",student.getName());
assertEquals("男",student.getGender());
}
1.3 查询对象列表
@Override
public List<Student> getStudentsByName(String name) {
String sql = "select name, gender from test_student where name = ?";
List<Student> students = this.jdbcTemplate.query(sql, new Object[]{name}, new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet rs, int i) throws SQLException {
Student s = new Student();
s.setName(rs.getString("name"));
s.setGender(rs.getString("gender"));
return s;
}
});
return students;
}
@Test
public void getStudentsByName(){
List<Student> studentList = studentService.getStudentsByName("Jack");
assertTrue(2 == studentList.size());
}
二、新增
@Override
public int addStudent(Student student) {
String sql = "insert into test_student(name,gender,age,address,email,grade)values(?,?,?,?,?,?)";
return jdbcTemplate.update(sql, student.getName(), student.getGender(), student.getAge(), student.getAddress(), student.getEmail(), student.getGrade());
}
@Test
public void addStudent() {
Student student = new Student();
student.setName("Jack");
student.setGender("男");
student.setAddress("NewYork");
student.setEmail("123@qq.com");
student.setGrade("3");
int num = studentService.addStudent(student);
assertEquals(Integer.parseInt("1"), num);
}
三、更新
@Override
public int updateStudentGenderByName(String gender, String name) {
String sql = "update test_student set gender = ? where name = ?";
return jdbcTemplate.update(sql, gender, name);
}
@Test
public void updateStudentGenderByName() {
Integer cnt = studentService.updateStudentGenderByName("male", "李白");
assertEquals(new Integer("1"), cnt);
}
四、删除
@Override
public int deleteStudentByName(String name) {
String sql = "delete from test_student where name = ?";
return jdbcTemplate.update(sql, name);
}
@Test
public void deleteStudentByName(){
Integer cnt = studentService.deleteStudentByName("Jack");
assertEquals(new Integer("2"), cnt);
}
本文只是简单列举Spring中关于JdbcTemplate的简单使用实例,关于RowMapper和更多的数据库实现方式可以参考后续的其它文章。
网友评论