SpringBoot practice | using JdbcTemplates to access Mysql

WeChat public number: an excellent waster
If you have any questions or suggestions, please leave a message in the background. I will try my best to solve your problems.

For example, today we will introduce that springboot accesses relational mysql through jdbc and spring's JdbcTemplate.

• SpringBoot 2.x
• jdk 1.8
• maven 3.0
• idea
• mysql

To build a spring boot project, friends who won't refer to the old article: How to use IDEA to build Spring Boot project

<dependencies>xml <!-- jdbcTemplate rely on --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <!-- open web: --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- mysql Connection class --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!-- https://mvnrepository.com/artifact/com.alibaba/druid --> <!-- druid Connection pool--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.13</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>

spring: datasource: driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=true username: Database user name password: Database password

package com.nasus.domain; /** * Project Name:jdbctemplate_demo <br/> * Package Name:com.nasus.domain <br/> * Date:2019/2/3 10:55 <br/> * <b>Description:</b> TODO: Describe the function of this class < br / > * * @author <a href="[email protected]">nasus</a><br/> * Copyright Notice ========================================================= * This file contains proprietary information of Eastcom Technologies Co. Ltd. * Copying or reproduction without prior written approval is prohibited. * Copyright (c) 2019 ======================================================= */ public class Student { private Integer id; private String name; private Integer age; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + '}'; } }

package com.nasus.dao; import com.nasus.domain.Student; import java.util.List; /** * Project Name:jdbctemplate_demo <br/> * Package Name:com.nasus.dao <br/> * Date:2019/2/3 10:59 <br/> * <b>Description:</b> TODO: Describe the function of this class < br / > * @author <a href="[email protected]">nasus</a><br/> */ public interface IStudentDao { int add(Student student); int update(Student student); int delete(int id); Student findStudentById(int id); List<Student> findStudentList(); }

Specific implementation class:

package com.nasus.dao.impl; import com.nasus.dao.IStudentDao; import com.nasus.domain.Student; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; /** * Project Name:jdbctemplate_demo <br/> * Package Name:com.nasus.dao.impl <br/> * Date:2019/2/3 11:00 <br/> * <b>Description:</b> TODO: Describe the function of this class < br / > * * @author <a href="[email protected]">nasus</a><br/> */ @Repository public class IStudentDaoImpl implements IStudentDao{ @Autowired private JdbcTemplate jdbcTemplate; @Override public int add(Student student) { return jdbcTemplate.update("insert into student(name, age) values(?, ?)", student.getName(),student.getAge()); } @Override public int update(Student student) { return jdbcTemplate.update("UPDATE student SET NAME=? ,age=? WHERE id=?", student.getName(),student.getAge(),student.getId()); } @Override public int delete(int id) { return jdbcTemplate.update("DELETE from TABLE student where id=?",id); } @Override public Student findStudentById(int id) { // BeanPropertyRowMapper automatically maps the database fields and entity classes of the List result List obtained List<Student> list = jdbcTemplate.query("select * from student where id = ?", new Object[], new BeanPropertyRowMapper(Student.class)); if(list!=null && list.size()>0){ Student student = list.get(0); return student; }else{ return null; } } @Override public List<Student> findStudentList() { // Use Spring's JdbcTemplate to query the database, get the List of List results, and automatically correspond the database table fields and entity classes. You can use BeanPropertyRowMapper List<Student> list = jdbcTemplate.query("select * from student", new Object[]{}, new BeanPropertyRowMapper(Student.class)); if(list!=null && list.size()>0){ return list; }else{ return null; } } }

package com.nasus.service; import com.nasus.domain.Student; import java.util.List; /** * Project Name:jdbctemplate_demo <br/> * Package Name:com.nasus.service <br/> * Date:2019/2/3 11:17 <br/> * <b>Description:</b> TODO: Describe the function of this class < br / > * * @author <a href="[email protected]">nasus</a><br/> */ public interface IStudentService { int add(Student student); int update(Student student); int delete(int id); Student findStudentById(int id); List<Student> findStudentList(); }

Implementation class:

package com.nasus.service.impl; import com.nasus.dao.IStudentDao; import com.nasus.domain.Student; import com.nasus.service.IStudentService; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; /** * Project Name:jdbctemplate_demo <br/> * Package Name:com.nasus.service.impl <br/> * Date:2019/2/3 11:18 <br/> * <b>Description:</b> TODO: Describe the function of this class < br / > * * @author <a href="[email protected]">nasus</a><br/> * Copyright Notice ========================================================= * This file contains proprietary information of Eastcom Technologies Co. Ltd. * Copying or reproduction without prior written approval is prohibited. * Copyright (c) 2019 ======================================================= */ @Repository public class IStudentServiceImpl implements IStudentService { @Autowired private IStudentDao iStudentDao; @Override public int add(Student student) { return iStudentDao.add(student); } @Override public int update(Student student) { return iStudentDao.update(student); } @Override public int delete(int id) { return iStudentDao.delete(id); } @Override public Student findStudentById(int id) { return iStudentDao.findStudentById(id); } @Override public List<Student> findStudentList() { return iStudentDao.findStudentList(); } }

package com.nasus.controller; import com.nasus.domain.Student; import com.nasus.service.IStudentService; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.DeleteMapping; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.PutMapping; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; /** * Project Name:jdbctemplate_demo <br/> * Package Name:com.nasus.controller <br/> * Date:2019/2/3 11:21 <br/> * <b>Description:</b> TODO: Describe the function of this class < br / > * * @author <a href="[email protected]">nasus</a><br/> */ @RestController @RequestMapping("/student") public class StudentController { @Autowired private IStudentService iStudentService; @PostMapping("") public int addStudent(@RequestBody Student student){ return iStudentService.add(student); } @PutMapping("/") public String updateStudent(@PathVariable Integer id, @RequestBody Student student){ Student oldStudent = new Student(); oldStudent.setId(id); oldStudent.setName(student.getName()); oldStudent.setAge(student.getAge()); int t = iStudentService.update(oldStudent); if (t == 1){ return student.toString(); }else { return "Error in updating student information"; } } @GetMapping("/") public Student findStudentById(@PathVariable Integer id){ return iStudentService.findStudentById(id); } @GetMapping("/list") public List<Student> findStudentList(){ return iStudentService.findStudentList(); } @DeleteMapping("/") public int deleteStudentById(@PathVariable Integer id){ return iStudentService.delete(id); } }

Other api tests can pass the postman test. I have passed all the tests here. Please feel free to use it.

Source code download: https://github.com/turoDog/De...

The above spring boot uses JdbcTemplates to access Mysql tutorial. Finally, if you are interested in Python and Java, please press QR code for a long time and pay attention to the wave. I will try my best to bring you value. If you think this article can help you even a little, please help me to look good and let more people know.

In addition, you can receive free learning materials after you send 1024. For details, please refer to this old article: Python, C + +, Java, Linux, Go, front end, algorithm data sharing

3 December 2019, 13:27 | Views: 9857