簡介
- JdbcTemplate是Spring提供的訪問數(shù)據(jù)庫的方式之一缓升，是Spring中最基本痴奏、最底層的訪問數(shù)據(jù)庫的實現(xiàn)方式。
- 通過使用JdbcTemplate湃鹊，開發(fā)者無需關(guān)心數(shù)據(jù)庫連接的創(chuàng)建和關(guān)閉細(xì)節(jié)冕香，只需要專注于實現(xiàn)業(yè)務(wù)邏輯即可蛹尝。
- 在使用JdbcTemplate的時候，只需要聲明即可悉尾，無需自己初始化突那，因為Spring在初始化數(shù)據(jù)源datasource的時候會自己創(chuàng)建JdbcTemplate的實例。
準(zhǔn)備
首先构眯，我們需要新建一個Spring Boot工程愕难，然后需要引入web、jdbc模塊惫霸，還需要mysql的java驅(qū)動包猫缭。在pom文件中表現(xiàn)如下：
<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模塊的引入是必須的，否則運(yùn)行報錯它褪。
然后饵骨，我們需要配置數(shù)據(jù)源翘悉，在application.properties中配置如下信息：
#GMT%2B8代表東八區(qū)
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
#無需再手動指定驅(qū)動類型
#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:數(shù)據(jù)庫的驅(qū)動類無需再人為手動指定茫打，系統(tǒng)會自動加載。
其后，我們需要創(chuàng)建好數(shù)據(jù)庫和表老赤，并插入數(shù)據(jù)轮洋，然后準(zhǔn)備一個Java實體類，這部分不是本文重點抬旺，此處略過弊予。
最后，在使用JdbcTemplate之前开财，我們需要在service實現(xiàn)類中聲明它：
@Service
public class StudentServiceImpl implements StudentService {
@Autowired
private JdbcTemplate jdbcTemplate;
}
JdbcTemplate的實例化由Spring自動創(chuàng)建汉柒，開發(fā)人員直接使用即可。
我們使用單元測試來驗證程序執(zhí)行的正確性责鳍，為此碾褂，需要準(zhǔn)備如下的測試類：
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = BootJdbcApplication.class, webEnvironment = SpringBootTest.WebEnvironment.DEFINED_PORT)
public class StudentServiceImplTest {
@Autowired
private StudentService studentService;
}
一、查詢
1.1 查詢記錄數(shù)
@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 查詢對象
關(guān)于如下實例中Row Mapper的使用可以參考下一篇文章《Spring JdbcTemplate中關(guān)于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中關(guān)于JdbcTemplate的簡單使用實例乓诽，關(guān)于RowMapper和更多的數(shù)據(jù)庫實現(xiàn)方式可以參考后續(xù)的其它文章。
