SpringMVC框架访问数据库的用户表
SpringMVC框架访问数据库的用户表
题目：
利用SpringMVC框架访问数据库的用户表(id,username,sex,age,birthday)， 并实现用户数据的展示；
说明：后台采用JDBC方式，展示采用model、modelMap、modelAndMap三种方式
1.新建项目工程
2.配置Tomcat
3.编写data.sql文件
-- 创建数据库
create database webapp1 charset utf8mb4;
-- 创建用户名、密码
create user'webapp1'@'localhost'identified by'webapp1';
-- 授权
grant all on webapp1.*to'webapp1'@'localhost';
-- 用用户名、密码登录
mysql -uwebapp1 -pwebapp1;
-- 创建表
-- id,username,sex,age,birthday
create table user
(
id int not null primary key auto_increment,
username varchar(12),
sex varchar(2),
age int,
birthday date
);
-- 查看表结构
describe user;
-- 添加数据
insert into user
(`username`,`sex`,`age`,`birthday`)
values
("小向","男",19,"2000-1-1");
-- 添加多条数据
insert into user
(`username`,`sex`,`age`,`birthday`)
values
("小向","男",19,"2000-1-1"),
("小李","男",19,"2000-1-1"),
("小张","男",19,"2000-1-1"),
("小五","男",19,"2000-1-1"),
("小三","男",19,"2000-1-1"),
("小二","男",19,"2000-1-1"),
("小周","男",19,"2000-1-1");
4.配置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>
<groupId>com.xiang</groupId>
<artifactId>mvcText</artifactId>
<version>1.0-SNAPSHOT</version>
<name>mvcText</name>
<packaging>war</packaging>
<!-- 依赖软件包版本 -->
<properties>
<commons-lang.version>2.6</commons-lang.version>
<spring.version>5.3.9</spring.version>
<slf4j.version>2.0.0-alpha5</slf4j.version>
<maven.compiler.target>1.8</maven.compiler.target>
<maven.compiler.source>1.8</maven.compiler.source>
<junit.version>5.7.1</junit.version>
</properties>
<!-- 配置依赖管理包 -->
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-framework-bom</artifactId>
<version>${spring.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<!-- 依赖配置节点-->
<dependencies>
<!--springMVC依赖包-->
<!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.3.9</version>
</dependency>
<!-- 通用依赖包 -->
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>${commons-lang.version}</version>
</dependency>
<!--日志依赖包-->
<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-log4j12 -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>2.0.0-alpha5</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>2.0.0-alpha5</version>
</dependency>
<!-- json依赖包 -->
<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-core -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.13.0-rc2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-annotations -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-annotations</artifactId>
<version>2.13.0-rc2</version>
</dependency>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.13</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-databind -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.13.0-rc2</version>
</dependency>
<!-- MySQL驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<!-- jstl-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>${junit.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-engine</artifactId>
<version>${junit.version}</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-war-plugin</artifactId>
<version>3.3.1</version>
</plugin>
</plugins>
</build>
</project>
5.配置mvc-dispatcher-servlet.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc.xsd">
<!-- 扫描控制器 -->
<context:component-scan base-package="com.xiang">
<context:include-filter type="annotation"
expression="org.springframework.stereotype.Controller" />
</context:component-scan>
<!-- 扩充了注解驱动，可以将请求参数绑定到控制器参数 -->
<mvc:annotation-driven />
<!-- 静态资源处理， css， js， imgs -->
<mvc:resources mapping="/resource/css/**" location="/resource/css/" />
<mvc:resources mapping="/resource/js/**" location="/resource/js/" />
<mvc:resources mapping="/resource/image/**" location="/resource/image/" />
<!-- 配置试图解析器 -->
<bean
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="viewClass"
value="org.springframework.web.servlet.view.JstlView"></property>
<!-- 配置jsp文件前缀及后缀 -->
<property name="prefix" value="/"></property>
<property name="suffix" value=".jsp"></property>
</bean>
</beans>
6. 配置springApplication.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
">
</beans>
7.编写DBTest类，测试连接数据库
package com.xiang;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBTest {
private static String URL = "jdbc:mysql://localhost:3307/webapp1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false";
private static String DriverClass = "com.mysql.cj.jdbc.Driver";
private static String UserName = "webapp1";
private static String PassWord = "webapp1";
private static Connection connection = null;
public static Connection getConnection() {
try {
Class.forName(DriverClass);
connection = DriverManager.getConnection(URL, UserName, PassWord);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
public static void closeResource() {
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void main(String[] args) {
DBTest.getConnection();
if (connection != null) {
System.out.println("数据库连接成功");
} else {
System.out.println("数据库连接失败");
}
closeResource();
}
}
8.编写pojo.User类
package com.xiang.pojo;
import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;
import java.util.Date;
/**
* id int not null primary key auto_increment,
* username varchar(12),
* sex varchar(2),
* age int,
* birthday date
*/
public class User {
private int id;
private String username;
private String sex;
private int age;
@DateTimeFormat(pattern = "yyyy-MM-dd")
@JsonFormat(pattern = "yyyy-MM-dd")
private Date birthday;
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", sex='" + sex + '\'' +
", age=" + age +
", birthday=" + birthday +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
9.编写controller.UserController类
package com.xiang.controller;
import com.xiang.DBTest;
import com.xiang.pojo.User;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
@Controller
@RequestMapping("/user")
public class UserController {
private static Connection connection = null;
private static PreparedStatement statement = null;
private static ResultSet query = null;
private static ArrayList<Object> list = null;
public static void init() throws Exception {
connection = DBTest.getConnection();
statement = connection.prepareStatement("select * from user ");
query = statement.executeQuery();
list = new ArrayList<>();
while (query.next()) {
User user = new User();
int anInt = query.getInt(1);
user.setId(anInt);
String username = query.getString("username");
user.setUsername(username);
String sex = query.getString("sex");
user.setSex(sex);
int age = query.getInt(4);
user.setAge(age);
Date birthday = query.getDate("birthday");
user.setBirthday(birthday);
list.add(user);
}
System.out.println("init()");
}
@RequestMapping("/view1")
public String getVive1(ModelMap modelMap) throws Exception {
init();
modelMap.addAttribute("list", list);
DBTest.closeResource();
return "/user/view1";
}
@RequestMapping("/view2")
public String getVive2(Model model) throws Exception {
connection = DBTest.getConnection();
init();
model.addAttribute("list", list);
DBTest.closeResource();
return "/user/view2";
}
@RequestMapping("/view3")
public ModelAndView getVive3() throws Exception {
init();
ModelAndView andView = new ModelAndView();
andView.setViewName("/user/view3");
andView.addObject("list", list);
return andView;
}
}
10.编写user.view1.jsp文件
<%--
Created by IntelliJ IDEA.
User: Xiang
Date: 2021/9/10
Time: 23:43
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<html>
<head>
<title>view1</title>
</head>
<body>
<h4>
view1
</h4>
<form action="<%=request.getContextPath()%>/view1">
<table border="1">
<tr>
<th scope="col">id</th>
<th scope="col">username</th>
<th scope="col">sex</th>
<th scope="col">age</th>
<th scope="col">birthday</th>
</tr>
<c:forEach items="${list}" var="model">
<tr>
<td>${model.id}</td>
<td>${model.username}</td>
<td>${model.sex}</td>
<td>${model.age}</td>
<td>${model.birthday}</td>
</tr>
</c:forEach>
<%-- <c:forEach items="${list}" var="model">--%>
<%-- <tr>--%>
<%-- <td>${model.byteValue()}</td>--%>
<%-- </tr>--%>
<%-- </c:forEach>--%>
<tr></tr>
<tr></tr>
<tr></tr>
<tr></tr>
<%--<c:forEach items="${id}" var="model">--%>
<%-- <tr>--%>
<%-- --%>
<%-- --%>
<%-- <td><c:out value="${model.username}"/></td>--%>
<%-- <td><c:out value="${model.sex}"/></td>--%>
<%-- <td><c:out value="${model.age}"/></td>--%>
<%-- <td><c:out value="${model.birthday}"/></td>--%>
<%--<%– <td><fmt:formatDate pattern="yyyy-MM-dd HH:mm" value="${model.birthday}"/></td>–%>--%>
<%--</c:forEach>--%>
</table>
</form>
</body>
</html>
11.编写user.view2.jsp文件
<%--
Created by IntelliJ IDEA.
User: Xiang
Date: 2021/9/10
Time: 23:43
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<html>
<head>
<title>view2</title>
</head>
<body>
<h4>
view2
</h4>
<form action="<%=request.getContextPath()%>/view2">
<table border="1">
<tr>
<th scope="col">id</th>
<th scope="col">username</th>
<th scope="col">sex</th>
<th scope="col">age</th>
<th scope="col">birthday</th>
</tr>
<c:forEach items="${list}" var="model">
<tr>
<td>${model.id}</td>
<td>${model.username}</td>
<td>${model.sex}</td>
<td>${model.age}</td>
<td>${model.birthday}</td>
</tr>
</c:forEach>
<%-- <c:forEach items="${list}" var="model">--%>
<%-- <tr>--%>
<%-- <td>${model.byteValue()}</td>--%>
<%-- </tr>--%>
<%-- </c:forEach>--%>
<tr></tr>
<tr></tr>
<tr></tr>
<tr></tr>
<%--<c:forEach items="${id}" var="model">--%>
<%-- <tr>--%>
<%-- --%>
<%-- --%>
<%-- <td><c:out value="${model.username}"/></td>--%>
<%-- <td><c:out value="${model.sex}"/></td>--%>
<%-- <td><c:out value="${model.age}"/></td>--%>
<%-- <td><c:out value="${model.birthday}"/></td>--%>
<%--<%– <td><fmt:formatDate pattern="yyyy-MM-dd HH:mm" value="${model.birthday}"/></td>–%>--%>
<%--</c:forEach>--%>
</table>
</form>
</body>
</html>
12.编写user.view3.jsp文件
<%--
Created by IntelliJ IDEA.
User: Xiang
Date: 2021/9/10
Time: 23:43
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<html>
<head>
<title>view3</title>
</head>
<body>
<h4>
view3
</h4>
<form action="<%=request.getContextPath()%>/view2">
<table border="1">
<tr>
<th scope="col">id</th>
<th scope="col">username</th>
<th scope="col">sex</th>
<th scope="col">age</th>
<th scope="col">birthday</th>
</tr>
<c:forEach items="${list}" var="model">
<tr>
<td>${model.id}</td>
<td>${model.username}</td>
<td>${model.sex}</td>
<td>${model.age}</td>
<td>${model.birthday}</td>
</tr>
</c:forEach>
<%-- <c:forEach items="${list}" var="model">--%>
<%-- <tr>--%>
<%-- <td>${model.byteValue()}</td>--%>
<%-- </tr>--%>
<%-- </c:forEach>--%>
<tr></tr>
<tr></tr>
<tr></tr>
<tr></tr>
<%--<c:forEach items="${id}" var="model">--%>
<%-- <tr>--%>
<%-- --%>
<%-- --%>
<%-- <td><c:out value="${model.username}"/></td>--%>
<%-- <td><c:out value="${model.sex}"/></td>--%>
<%-- <td><c:out value="${model.age}"/></td>--%>
<%-- <td><c:out value="${model.birthday}"/></td>--%>
<%--<%– <td><fmt:formatDate pattern="yyyy-MM-dd HH:mm" value="${model.birthday}"/></td>–%>--%>
<%--</c:forEach>--%>
</table>
</form>
</body>
</html>
13.项目工程结构目录图
14.项目工程运行结果
15.项目工程打包
mvn package -Dmaven.test.skip=true
16.忘记配置web.xml了 不好意思；
<!DOCTYPE web-app>
<web-app>
<display-name>Archetype Created Web Application</display-name>
<!-- 欢迎页面 -->
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<!-- 配置spring 上下文 -->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:springApplication.xml</param-value>
</context-param>
<!-- 配置监听器 -->
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!-- spring mvc的配置上下文 -->
<servlet>
<servlet-name>mvc-dispatcher</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:mvc-dispatcher-servlet.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>mvc-dispatcher</servlet-name>
<!-- 拦截所有请求 -->
<url-pattern>/</url-pattern>
</servlet-mapping>
<!-- 编码过滤器 -->
<filter>
<filter-name>characterEncodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
<init-param>
<param-name>forceEncoding</param-name>
<param-value>true</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>characterEncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
