Spring Boot Thymeleaf MYSQL数据库原始JDBC CRUD示例

浅浅的花香味﹌ 2023-09-26 09:37 45阅读 0赞

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
    <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>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.6.4</version>
            <relativePath/> 
        </parent>
        <groupId>com.example</groupId>
        <artifactId>studentPreparedStatements</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>studentPreparedStatements</name>
        <description>studentPreparedStatements</description>
        <properties>
            <java.version>1.8</java.version>
        </properties>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jdbc</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-thymeleaf</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/studentManagement?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    spring.datasource.username=root
    spring.datasource.password=root

SQL

-- --------------------------------------------------------
    -- 主机:                           127.0.0.1
    -- 服务器版本:                        8.0.22 - MySQL Community Server - GPL
    -- 服务器操作系统:                      Win64
    -- HeidiSQL 版本:                  11.3.0.6295
    -- --------------------------------------------------------
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET NAMES utf8 */;
    /*!50503 SET NAMES utf8mb4 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    
    -- 导出 studentmanagement 的数据库结构
    CREATE DATABASE IF NOT EXISTS `studentmanagement` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
    USE `studentmanagement`;
    
    -- 导出  表 studentmanagement.students 结构
    CREATE TABLE IF NOT EXISTS `students` (
      `id` int NOT NULL AUTO_INCREMENT,
      `firstname` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
      `lastname` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
      `age` int NOT NULL DEFAULT '0',
      `email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    -- 正在导出表  studentmanagement.students 的数据：~0 rows (大约)
    /*!40000 ALTER TABLE `students` DISABLE KEYS */;
    INSERT IGNORE INTO `students` (`id`, `firstname`, `lastname`, `age`, `email`) VALUES
    	(1, '测试', '测试', 18, 'a@a.com'),
    	(3, '小虎', '马', 33, 'email@email.com');
    /*!40000 ALTER TABLE `students` ENABLE KEYS */;
    
    /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
    /*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

StudentController.java

package com.example.controllers;
    
    import com.example.models.Student;
    import com.example.repository.StudentRepository;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.ui.Model;
    import org.springframework.web.bind.annotation.*;
    
    import java.sql.SQLException;
    
    @Controller
    class StudentController {
    
        @Autowired
        private StudentRepository studentRepository;
    
    
        @GetMapping("/student")
        public String studentList(Model model) throws SQLException {
            model.addAttribute("students", studentRepository.getAllStudents());
            return "students";
        }
    
        // add new student site
        @GetMapping("/students/new")
        public String addStudent(Model model){
            Student student = new Student();
            model.addAttribute("addStudents",student);
            return "addStudent";
        }
        // students gets posted on student site and u get redirected
        @PostMapping("/students")
        public String saveStudent(@ModelAttribute("addStudents")Student student) {
            studentRepository.addStudent(student);
            return "redirect:/student";
        }
    
        // update site
        @GetMapping("/students/update/{id}")
        public String updateStudent(@PathVariable Long id, Model model) {
            model.addAttribute("updateStud", studentRepository.getStudentById(id));
            return "updateStudent";
        }
        // updated students get posted on the redirected site.
        @PostMapping("/students/{id}")
        public String editStudent(@PathVariable Long id, @ModelAttribute("updateStud")Student student) {
    
            Student students = studentRepository.getStudentById(id); // getting the student from the database
            students.setId(id);
            students.setFirstname(student.getFirstname());
            students.setLastname(student.getLastname());
            students.setAge(student.getAge());
            students.setEmail(student.getEmail());
            studentRepository.updateStudent(students);
            return "redirect:/student";
        }
    
        @GetMapping("/student/{id}")
        public String deleteStudent(@PathVariable Long id) throws SQLException {
            studentRepository.deleteStudent(id);
            return "redirect:/student";
        }
    
    }

Student.java

package com.example.models;
    
    public class Student {
    
        private Long id;
        private String firstname;
        private String lastname;
        private int age;
        private String email;
    
        public Student(String firstname, String lastname, int age, String email) {
            this.firstname = firstname;
            this.lastname = lastname;
            this.age = age;
            this.email = email;
        }
    
        public Student(Long id, String firstname, String lastname, int age, String email) {
            this.id = id;
            this.firstname = firstname;
            this.lastname = lastname;
            this.age = age;
            this.email = email;
        }
    
        public Student(Long id, String firstname, String lastname, int age) {
            this.id = id;
            this.firstname = firstname;
            this.lastname = lastname;
            this.age = age;
    
        }
    
        public Student() {
        }
    
        public Long getId() {
            return id;
        }
    
        public void setId(Long id) {
            this.id = id;
        }
    
        public String getFirstname() {
            return firstname;
        }
    
        public void setFirstname(String firstname) {
            this.firstname = firstname;
        }
    
        public String getLastname() {
            return lastname;
        }
    
        public void setLastname(String lastname) {
            this.lastname = lastname;
        }
    
        public int getAge() {
            return age;
        }
    
        public void setAge(int age) {
            this.age = age;
        }
    
        public String getEmail() {
            return email;
        }
    
        public void setEmail(String email) {
            this.email = email;
        }
    }

StudentRepository.java

package com.example.repository;
    
    import com.example.db.DBManager;
    import com.example.models.Student;
    import org.springframework.stereotype.Repository;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    @Repository
    public class StudentRepository {
    
        Connection connection = DBManager.getConnection();
        Student student;
    
    
        public Student addStudent(Student student) {
            String insert = "INSERT INTO students(firstname,lastname,age,email) VALUES(?,?,?,?)";
            try {
                PreparedStatement preparedStatement = connection.prepareStatement(insert);
                preparedStatement.setString(1, student.getFirstname());
                preparedStatement.setString(2, student.getLastname());
                preparedStatement.setInt(3, student.getAge());
                preparedStatement.setString(4, student.getEmail());
                preparedStatement.executeUpdate();
    
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
            return student;
        }
    
        public Student updateStudent(Student student) {
            String sql = "UPDATE students SET firstname = ?,lastname = ?, age = ?, email = ? WHERE id = ?";
            try {
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, student.getFirstname());
                preparedStatement.setString(2, student.getLastname());
                preparedStatement.setInt(3, student.getAge());
                preparedStatement.setString(4, student.getEmail());
                preparedStatement.setLong(5, student.getId());
                preparedStatement.executeUpdate();
    
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
            return student;
        }
    
        public Student getStudentById(Long id) {
            try {
                String sql = "SELECT * FROM students WHERE id=?";
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setLong(1, id);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    student = new Student(
                            resultSet.getLong("id"),
                            resultSet.getString("firstname"),
                            resultSet.getString("lastname"),
                            resultSet.getInt("age"),
                            resultSet.getString("email"));
                }
                resultSet.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
            return student;
        }
    
        public int deleteStudent(Long id) throws SQLException {
            String sql = "DELETE FROM students WHERE id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, id);
            return preparedStatement.executeUpdate();
        }
    
        public List<Student> getAllStudents() throws SQLException {
            String sql = "SELECT * FROM students";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<Student> list = new ArrayList<>();
            while (resultSet.next()) {
                student = new Student(
                        resultSet.getLong("id"),
                        resultSet.getString("firstname"),
                        resultSet.getString("lastname"),
                        resultSet.getInt("age"),
                        resultSet.getString("email"));
                list.add(student);
            }
            resultSet.close();
            return list;
        }
    }

DBManager.java

package com.example.db;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.Properties;
    
    public class DBManager {
    
        private static String user;
        private static String password;
        private static String url;
        private static Connection connection;
    
        private DBManager() {
    
        }
    
        public static Connection getConnection() {
    
    //        url = "jdbc:mysql://localhost:3306/studentManagement?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
    //        user = "root";
    //        password = "root";
            try ( InputStream input = DBManager.class.getClassLoader().getResourceAsStream("application.properties")) {
    
                Properties prop = new Properties();
    
                if (input == null) {
                    System.out.println("Sorry, unable to find application.properties");
                }
    
                //load a properties file from class path, inside static method
                prop.load(input);
    
                //get the property value and print it out
                //System.out.println(prop.getProperty("url"));
                url = prop.getProperty("spring.datasource.url");
                //System.out.println(prop.getProperty("user"));
                user = prop.getProperty("spring.datasource.username");
                //System.out.println(prop.getProperty("password"));
                password = prop.getProperty("spring.datasource.password");
    
            } catch (IOException ex) {
                ex.printStackTrace();
            }
    
            try {
    
                connection = DriverManager.getConnection(url, user, password);
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
            return connection;
        }
    
    }

界面

![a445ef765ac5425199b114148a04bd56.png][]

[a445ef765ac5425199b114148a04bd56.png]: https://img-blog.csdnimg.cn/a445ef765ac5425199b114148a04bd56.png