SpringBoot(十五)-----Springboot配合JDBCTemplate实现增删改查

今天用Springboot配合JDBCTemplate写一个增删改查的小例子

构建数据库

create table Student(
Sid Integer,
Sno char(10),
Sname char(10),
Sgrade Integer,
primary key(Sid)
);

构建项目

加入JDBCTemplate依赖包

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>myspringboot002</groupId>
  <artifactId>myspringboot002</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.3.3.RELEASE</version>
  </parent>
  <dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-freemarker</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.17</version>
    </dependency>
  </dependencies>
</project>　

配置文件application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&useSSL=false&serverTimezone = GMT
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

构建Student实体类

Student.java

package com.zk.Dao;
 
 
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
 
@Entity(name="Student")
@Table(name="student")
public class Student {
    @Id 
    @GeneratedValue
    private Integer Sid;
    @Column
    private String Sname;
    @Column
    private String Sno;
    @Column
    private Integer Sgrade;
     
     
     
    public Integer getSid() {
        return Sid;
    }
    public void setSid(Integer sid) {
        Sid = sid;
    }
    public String getSname() {
        return Sname;
    }
    public void setSname(String sname) {
        Sname = sname;
    }
    public String getSno() {
        return Sno;
    }
    public void setSno(String sno) {
        Sno = sno;
    }
    public Integer getSgrade() {
        return Sgrade;
    }
    public void setSgrade(Integer sgrade) {
        Sgrade = sgrade;
    }
}

　然后是Controller层，

UserController.java

package com.zk.Controller;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
import com.zk.Dao.Student;
import com.zk.Service.UserService;
 
@RestController
public class UserController {
    @Autowired
    private UserService userService;
     
    @RequestMapping("/createUser")
    private String createUser(String Sname,String Sno,Integer Sgrade) {
        userService.createUser(Sname,Sno,Sgrade);
        return "success";
    }
     
    @RequestMapping("/updateUser")
    private String updateUser(String Sname,String Sno,Integer Sgrade,String Sid) {
        userService.updateUser(Sname,Sno,Sgrade,Sid);
        return "success";
    }
     
    @RequestMapping("/deleteUser")
    private String deleteUser(String Sname,String Sno,Integer Sgrade) {
        userService.deleteUser(Sno);
        return "success";
    }
     
    @RequestMapping("/getUserBySno")
    private String getUserBySno(String Sname,String Sno,Integer Sgrade) {
        Student student=userService.getUserBySno(Sno);
        System.out.println(student.getSname());
        System.out.println(student.getSno());
        System.out.println(student.getSgrade());
        return "success";
    }
}

　　然后是Service层

UserService.java

package com.zk.Service;
 
import com.zk.Dao.Student;
 
public interface UserService {
    public void createUser(String Sname,String Sno,Integer Sgrade);
    public void updateUser(String Sname,String Sno,Integer Sgrade,String Sid);
    public void deleteUser(String Sno);
    public Student getUserBySno(String Sno);
}

UserServiceImpl.java

package com.zk.Service;
 
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.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Service;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
 
import com.zk.Dao.Student;
 
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
 
    public void createUser(String Sname, String Sno, Integer Sgrade) {
        System.out.println("createUsers");
        jdbcTemplate.update("insert into student(Sname,Sno,Sgrade) value(?,?,?)", Sname, Sno, Sgrade);
        // jdbcTemplate.execute("Select * from Student");
        System.out.println("创建用户成功");
    }
 
    public void updateUser(String Sname, String Sno, Integer Sgrade, String Sid) {
        System.out.println("updateUsers");
        jdbcTemplate.update("update student set Sname=?,Sno=?,Sgrade=? where Sid=?", Sname, Sno, Sgrade, Sid);
        // jdbcTemplate.execute("Select * from Student");
        System.out.println("创建用户成功");
    }
 
    public void deleteUser(String Sno) {
        System.out.println("deleteUsers");
        jdbcTemplate.update("delete from student where Sno=?", Sno);
        // jdbcTemplate.execute("Select * from Student");
        System.out.println("删除用户成功");
    }
 
    public Student getUserBySno(String Sno) {
        List<Student> list = jdbcTemplate.query("select * from student where Sno = ?", new Object[] { Sno },
                new BeanPropertyRowMapper<>(Student.class));
        if (!list.isEmpty()) {
            Student Student = list.get(0);
            System.out.println("Sname:"+Student.getSname()+"\t"+"Sno:"+Student.getSno()+"\t"+"Sgrade:"+Student.getSgrade());
            return Student;
        }
        return null;
    }
}

　　启动类SpringBootApplicationSixth

package com.zk.myspringboot006;
 
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
 
@ComponentScan(basePackages={"com.zk.Controller","com.zk.Service"})
@EnableAutoConfiguration
public class SpringBootApplicationSixth {
    public static void main(String[]args){
        SpringApplication.run(SpringBootApplicationSixth.class, args);
    }
    public static void run(String...arg0) {
        System.out.println("Hello world from Command Line Runner");
    }
}