使用 Servlet、MySQL 和 Thymeleaf 开发CRUD WEB应用程序

怼烎@ 2023-09-25 12:18 19阅读 0赞

在本教程中，您将学习使用 Servlet、MySQL 和 Thymeleaf 开发CRUD WEB应用程序。

![614e308f53144d53b4714457e50f8ea5.png][]

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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.codecool.shop</groupId>
        <artifactId>crud-servlet-thymeleaf</artifactId>
        <version>1.0-SNAPSHOT</version>
        <packaging>war</packaging>
    
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <jetty.version>9.4.12.v20180830</jetty.version>
        </properties>
    
        <build>
            <finalName>crud-servlet-thymeleaf</finalName>
            <sourceDirectory>src/main/java</sourceDirectory>
    
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.7.0</version>
                    <configuration>
                        <source>1.8</source>
                        <target>1.8</target>
                    </configuration>
                </plugin>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-war-plugin</artifactId>
                    <version>3.2.0</version>
                </plugin>
                <plugin>
                    <groupId>org.eclipse.jetty</groupId>
                    <artifactId>jetty-maven-plugin</artifactId>
                    <version>${jetty.version}</version>
                    <configuration>
                        
                        
                        
                        
                    </configuration>
                </plugin>
            </plugins>
        </build>
    
        <dependencies>
            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>javax.servlet-api</artifactId>
                <version>4.0.0</version>
                <scope>provided</scope>
            </dependency>
            
            
            
            
            
            
            <dependency>
                <groupId>org.thymeleaf</groupId>
                <artifactId>thymeleaf</artifactId>
                <version>3.0.15.RELEASE</version>
            </dependency>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                
                <version>8.0.22</version>
            </dependency>
            <dependency>
                <groupId>org.junit.jupiter</groupId>
                <artifactId>junit-jupiter-api</artifactId>
                <version>5.3.2</version>
                <scope>test</scope>
            </dependency>
        </dependencies>
        <name>crud-servlet-thymeleaf</name>
    </project>

MySQL建表脚本

CREATE TABLE `suppliers`
    (
        `id`          INT(10) NOT NULL AUTO_INCREMENT,
        `name`        VARCHAR(500) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_unicode_ci',
        `description` VARCHAR(500) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_unicode_ci',
        PRIMARY KEY (`id`) USING BTREE
    );
    INSERT INTO suppliers VALUES (1,'Amazon', 'Digital content and services');
    INSERT INTO suppliers VALUES (2, 'Lenovo', 'Computers');

Supplier.java

package com.example.crud.model;
    
    import java.lang.reflect.Field;
    
    public class Supplier {
    
        protected Integer id;
        protected String name;
        protected String description;
    
        public Supplier(Integer id, String name, String description) {
            this.id = id;
            this.name = name;
            this.description = description;
        }
    
        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 String getDescription() {
            return description;
        }
    
        public void setDescription(String description) {
            this.description = description;
        }
    
    }

ConnectionDB.java

package com.example.crud.SQL;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class ConnectionDB {
    
        private static final String DATABASE = "jdbc:mysql://localhost:3306/codecoolshop?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        private static final String DB_USER = "root";
        private static final String DB_PASSWORD = "root";
        private static ConnectionDB instance = null;
    
        public static ConnectionDB getInstance() {
            if (instance == null) {
                instance = new ConnectionDB();
            }
            return instance;
        }
    
    
        public static Connection getConnection() {
            Connection connection = null;
            try {
                try {
                    Class.forName("com.mysql.cj.jdbc.Driver");
                } catch (ClassNotFoundException ex) {
                    Logger.getLogger(ConnectionDB.class.getName()).log(Level.SEVERE, null, ex);
                }
                connection = DriverManager.getConnection(
                        DATABASE,
                        DB_USER,
                        DB_PASSWORD);
                System.out.println("数据库连接成功！");
            } catch (SQLException e) {
                e.printStackTrace();
            }
    
            return connection;
        }
    
        public int executeUpdate(String query, List<Object> parameters) {
            Connection connection = getConnection();
            PreparedStatement statement = null;
            ResultSet res = null;
            int idProduct = 0;
            try {
                statement = connection.prepareStatement(query);
                for (int i = 0; i < parameters.size(); i++) {
                    statement.setObject(i + 1, parameters.get(i));
                }
                statement.execute();
                res = statement.getResultSet();
                if (res.next()) {
                    idProduct = res.getInt(1);
                }
    
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (res != null) {
                        res.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    if (statement != null) {
                        statement.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            return idProduct;
        }
    
        public List<Map<String, Object>> executeQueryWithReturnValue(String query, List<Object> parameters) {
            Connection connection = getConnection();
            PreparedStatement statement = null;
            ResultSet resultSet = null;
            List<Map<String, Object>> resultList = new ArrayList<>();
    
            try {
                statement = connection.prepareStatement(query);
                for (int i = 0; i < parameters.size(); i++) {
                    statement.setObject(i + 1, parameters.get(i));
                }
                statement.executeQuery();
                resultSet = statement.getResultSet();
                int numOfCols = resultSet.getMetaData().getColumnCount();
    
                while (resultSet.next()) {
                    Map<String, Object> data = new HashMap<>();
    
                    for (int i = 0; i < numOfCols; i++) {
                        data.put(resultSet.getMetaData().getColumnName(i + 1),
                                resultSet.getObject(i + 1));
                    }
    
                    resultList.add(data);
                }
    
            } catch (SQLException e) {
                e.printStackTrace();
    
            } finally {
                try {
                    if (resultSet != null) {
                        resultSet.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    if (statement != null) {
                        statement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    
            return resultList;
        }
    
    }

SupplierDao.java

package com.example.crud.dao;
    
    import com.example.crud.model.Supplier;
    
    import java.util.List;
    
    public interface SupplierDao {
    
        void add(Supplier supplier);
    
        Supplier find(int id);
    
        void remove(int id);
    
        Supplier findByName(String name);
    
        List<Supplier> getAll();
    }

SupplierDaoJDBC.java

package com.example.crud.implementation;
    
    import com.example.crud.SQL.ConnectionDB;
    import com.example.crud.dao.SupplierDao;
    import com.example.crud.model.Supplier;
    
    import java.util.*;
    
    public class SupplierDaoJDBC implements SupplierDao {
        private static final ConnectionDB controller = ConnectionDB.getInstance();
        private static SupplierDaoJDBC instance = null;
    
        private SupplierDaoJDBC() {
    
        }
    
        public static SupplierDaoJDBC getInstance() {
            if (instance == null) {
                instance = new SupplierDaoJDBC();
            }
            return instance;
        }
    
        private List<Supplier> createObject(List<Map<String, Object>> resultRowsFromQuery) {
            List<Supplier> suppliers = new ArrayList<>();
    
            for (Map singleRow : resultRowsFromQuery) {
                suppliers.add(new Supplier((Integer) singleRow.get("id"),
                        (String) singleRow.get("name"),
                        (String) singleRow.get("description")));
            }
    
            return suppliers;
        }
    
        @Override
        public void add(Supplier supplier) {
            String query = "INSERT INTO suppliers"
                    + "(name, description)VALUES "
                    + "(?,?) ON CONFLICT DO NOTHING RETURNING id;";
            int idAddedSupplier = controller.executeUpdate(query,
                    Arrays.asList(supplier.getName(), supplier.getDescription()));
            supplier.setId(idAddedSupplier);
        }
    
        @Override
        public Supplier find(int id) {
            String query = "SELECT suppliers.id, suppliers.name, " +
                    "suppliers.description " +
                    "FROM suppliers WHERE id = ?;";
            List<Map<String, Object>> suppliers;
            suppliers = controller.executeQueryWithReturnValue(query, Collections.singletonList(id));
    
            return (suppliers.size() != 0) ? this.createObject(suppliers).get(0) : null;
    
        }
    
    
        @Override
        public void remove(int id) {
            controller.executeUpdate("DELETE FROM suppliers WHERE id=? RETURNING id;",
                    Collections.singletonList(id));
        }
    
        @Override
        public Supplier findByName(String name) {
            String query = "SELECT suppliers.id, suppliers.name, " +
                    "suppliers.description " +
                    "FROM suppliers WHERE name=?";
            List<Map<String, Object>> suppliers;
            suppliers = controller.executeQueryWithReturnValue(query, Collections.singletonList(name));
    
            return (suppliers.size() != 0) ? this.createObject(suppliers).get(0) : null;
    
        }
    
        @Override
        public List<Supplier> getAll() {
            String query = "SELECT suppliers.id, suppliers.name, " +
                    "suppliers.description " +
                    "FROM suppliers";
            List<Map<String, Object>> suppliers;
            suppliers = controller.executeQueryWithReturnValue(query, Collections.emptyList());
            return this.createObject(suppliers);
    
        }
    }

SupplierController.java

package com.example.crud.controller;
    
    import com.example.crud.config.TemplateEngineUtil;
    import com.example.crud.implementation.SupplierDaoJDBC;
    import org.thymeleaf.TemplateEngine;
    import org.thymeleaf.context.WebContext;
    
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.util.HashMap;
    import java.util.Map;
    
    @WebServlet(urlPatterns = {"/"})
    public class SupplierController extends HttpServlet {
    
        public void renderTemplate(HttpServletRequest req, HttpServletResponse resp, String HTMLpath, Map<String, Object> optionalParameters) throws IOException {
            TemplateEngine engine = TemplateEngineUtil.getTemplateEngine(req.getServletContext());
            WebContext context = new WebContext(req, resp, req.getServletContext());
    
            context.setVariables(optionalParameters);
    
            engine.process(HTMLpath, context, resp.getWriter());
        }
    
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("supplierList", SupplierDaoJDBC.getInstance().getAll());
            renderTemplate(req, resp, "supplier.html", params);
        }
    }

ThymeleafConfig.java

package com.example.crud.config;
    
    import org.thymeleaf.TemplateEngine;
    import org.thymeleaf.templatemode.TemplateMode;
    import org.thymeleaf.templateresolver.ITemplateResolver;
    import org.thymeleaf.templateresolver.ServletContextTemplateResolver;
    
    import javax.servlet.ServletContext;
    import javax.servlet.ServletContextEvent;
    import javax.servlet.ServletContextListener;
    import javax.servlet.annotation.WebListener;
    
    /**
     * Thymeleaf configuration.
     */
    @WebListener
    public class ThymeleafConfig implements ServletContextListener {
    
        public void contextInitialized(ServletContextEvent sce) {
            TemplateEngine engine = templateEngine(sce.getServletContext());
            TemplateEngineUtil.storeTemplateEngine(sce.getServletContext(), engine);
        }
    
        public void contextDestroyed(ServletContextEvent sce) {
        }
    
        private TemplateEngine templateEngine(ServletContext servletContext) {
            TemplateEngine engine = new TemplateEngine();
            engine.setTemplateResolver(templateResolver(servletContext));
            return engine;
        }
    
        private ITemplateResolver templateResolver(ServletContext servletContext) {
            ServletContextTemplateResolver resolver = new ServletContextTemplateResolver(servletContext);
            resolver.setPrefix("/templates/");
            resolver.setTemplateMode(TemplateMode.HTML);
            return resolver;
        }
    
    }

TemplateEngineUtil.java

package com.example.crud.config;
    
    import org.thymeleaf.TemplateEngine;
    
    import javax.servlet.ServletContext;
    import javax.servlet.annotation.WebListener;
    
    /**
     * Store and retrieves Thymeleaf TemplateEngine into the application servlet context.
     */
    @WebListener
    public class TemplateEngineUtil {
    
        private static final String TEMPLATE_ENGINE_ATTR = "com.thymeleafexamples.thymeleaf3.TemplateEngineInstance";
    
        public static void storeTemplateEngine(ServletContext context, TemplateEngine engine) {
            context.setAttribute(TEMPLATE_ENGINE_ATTR, engine);
        }
    
        public static TemplateEngine getTemplateEngine(ServletContext context) {
            return (TemplateEngine) context.getAttribute(TEMPLATE_ENGINE_ATTR);
        }
    
    }

supplier.html

<!DOCTYPE html>
    <html lang="en" xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">
    
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="Codecool">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        
    
        <title>E-SHOP HTML Template</title>
    
    
    </head>
    
    <body>
    
    
    <div>
        <table border="1" cellpadding="10">
            <thead>
            <tr>
                <th>ID
                </th>
                <th>Name
                </th>
                <th>Description
                </th>
                <th>Actions</th>
            </tr>
            </thead>
            <tbody>
            <th:block th:each="supplier: ${supplierList}">
                <tr>
                    <td th:text="${supplier.id}">Product ID</td>
                    <td th:text="${supplier.name}">Name</td>
                    <td th:text="${supplier.description}">Description</td>
                    <td><a th:href="@{'/edit/' + ${supplier.id}}">Edit</a>
                            <a th:href="@{'/delete/' + ${supplier.id}}">Delete</a>
                    </td>
                </tr>
            </th:block>
            </tbody>
        </table>
    
    
    </body>
    
    </html>

下载：[GitHub - allwaysoft/crud-servlet-thymeleaf][GitHub - allwaysoft_crud-servlet-thymeleaf]

[614e308f53144d53b4714457e50f8ea5.png]: https://img-blog.csdnimg.cn/614e308f53144d53b4714457e50f8ea5.png
[GitHub - allwaysoft_crud-servlet-thymeleaf]: https://github.com/allwaysoft/crud-servlet-thymeleaf