第一步:
配置环境:
- 创建maven工程 选择骨架 webapp
- pom.xml文件中添加3个依赖: MySQL 数据库连接池 Thymeleaf
- 创建文件夹和包, 把两个工具类(DBUtils,ThUtils)放到utils包下, jdbc.properties放到resources目录下
- 配置Tomcat 点灯泡 删路径
- 工程设置页面: 1. 打钩 2. 添加Tomcat相关jar包
6、使用DBUtils读取jdbc.properties文件
7、使用模板引擎的视图页面首行需要添加,否则没有代码提示
分层处理:
- Controller层:用来存放servlet
- Dao层:用来存放操纵数据库的java文件
- entity层:用来存放数据的实体类
- utils层:用来存放工具类
工具类:
DBUtils源代码:
import com.alibaba.druid.pool.DruidDataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
public class DBUtils {
private static DruidDataSource dataSource;
static {
//读取配置文件
Properties p = new Properties();
//获取配置文件的输入流
InputStream ips = DBUtils.class.getClassLoader()
.getResourceAsStream("jdbc.properties");
//让配置对象加载文件
try {
p.load(ips);
} catch (IOException e) {
e.printStackTrace();
}
//读取数据
String url = p.getProperty("db.url");
String driver = p.getProperty("db.driver");
String username = p.getProperty("db.username");
String password = p.getProperty("db.password");
String maxActive = p.getProperty("db.maxActive");
String initalSize = p.getProperty("db.initialSize");
//创建数据库连接池对象
dataSource = new DruidDataSource();
//设置数据库连接信息
dataSource.setDriverClassName(driver);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
//设置初始连接数量
dataSource.setInitialSize(Integer.parseInt(initalSize));
//设置最大连接数量
dataSource.setMaxActive(Integer.parseInt(maxActive));
}
public static Connection getConn() throws Exception {
//从连接池中获取连接
Connection conn = dataSource.getConnection();
return conn;
}
}
ThUtils源代码:
import org.thymeleaf.TemplateEngine;
import org.thymeleaf.context.Context;
import org.thymeleaf.templateresolver.ClassLoaderTemplateResolver;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
public class ThUtils {
private static TemplateEngine te;
static {
//创建模板引擎对象
te = new TemplateEngine();
//创建解析器 该解析器会自动查找src/main/resources目录下的模板页面
ClassLoaderTemplateResolver r =
new ClassLoaderTemplateResolver();
//设置字符集
r.setCharacterEncoding("UTF-8");
//让解析器和模板引擎关联
te.setTemplateResolver(r);
}
//Context导包 org.thymeleaf.Context
public static void print(String fileName,Context context,
HttpServletResponse response) throws IOException {
//将页面和数据整合到一起的到一个新的html字符串
String html = te.process(fileName, context);
//把得到的新的html返回给浏览器 异常抛出
response.setContentType("text/html;charset=utf-8");
PrintWriter pw = response.getWriter();
pw.print(html);
pw.close();
}
}
jdbc.properties配置文件:
db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/newdb3?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
db.username=root
db.password=root
db.maxActive=10
db.initialSize=2
实体类Hero:
public class Hero {
private int id;
private String name;
private String type;
private int money;
@Override
public String toString() {
return "Hero{" +
"id=" + id +
", name='" + name + '\'' +
", type='" + type + '\'' +
", money=" + money +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public int getMoney() {
return money;
}
public void setMoney(int money) {
this.money = money;
}
public Hero(int id, String name, String type, int money) {
this.id = id;
this.name = name;
this.type = type;
this.money = money;
}
}
操控数据库的dao层:
package cn.tedu.dao;
import cn.tedu.entity.Hero;
import cn.tedu.utils.DBUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class HeroDao {
public List<Hero> findAll() {
ArrayList<Hero> list = new ArrayList<>();
try (Connection conn = DBUtils.getConn()){
String sql = "select * from hero";
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery(sql);
while(rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
String type = rs.getString(3);
int money = rs.getInt(4);
list.add(new Hero(id,name,type,money));
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
public void deleteById(String id) {
try (Connection conn = DBUtils.getConn()){
String sql = "delete from hero where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,Integer.parseInt(id));
ps.executeUpdate();
System.out.println("删除完成!");
} catch (Exception e) {
e.printStackTrace();
}
}
public Hero findById(String id) {
try (Connection conn = DBUtils.getConn()){
String sql = "select * from hero where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,Integer.parseInt(id));
ResultSet rs = ps.executeQuery();
//判断是否查询到 并且让游标下移
if (rs.next()){
String name= rs.getString(2);
String type = rs.getString(3);
int money = rs.getInt(4);
return new Hero(Integer.parseInt(id),name,type,money);
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public void update(Hero hero) {
try (Connection conn = DBUtils.getConn()){
String sql = "update hero set name=?,type=?,money=? where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,hero.getName());
ps.setString(2,hero.getType());
ps.setInt(3,hero.getMoney());
ps.setInt(4,hero.getId());
ps.executeUpdate();
System.out.println("修改完成!");
} catch (Exception e) {
e.printStackTrace();
}
}
}
contorll层
删除表中的数据:
package cn.tedu.controller;
import cn.tedu.dao.HeroDao;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet(name = "DeleteServlet",urlPatterns = "/delete")
public class DeleteServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
HeroDao dao = new HeroDao();
dao.deleteById(id);
//重定向到列表页面
response.sendRedirect("/findall");
}
}
查询表中的数据:
package cn.tedu.controller;
import cn.tedu.dao.HeroDao;
import cn.tedu.entity.Hero;
import cn.tedu.utils.ThUtils;
import org.thymeleaf.context.Context;
import javax.servlet.ServletException;
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.List;
@WebServlet(name = "FindAllServlet", urlPatterns = "/findall")
public class FindAllServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//创建Dao并调用findall方法
HeroDao dao = new HeroDao();
List<Hero> list = dao.findAll();
//创建一个Context容器
Context context = new Context();
//把集合装进容器中
context.setVariable("list",list);
//通过工具类将页面和数据整合到一起返回给客户端浏览器
ThUtils.print("list.html",context,response);
}
}
显示数据表中的信息:
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<!--添加了命名空间后, 写th时不报错, 并且能够提示相关内容-->
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<table border="1">
<tr>
<th>id</th><th>英雄名称</th><th>类型</th><th>价格</th>
<th>操作</th>
</tr>
<!--遍历集合 ${}的作用是去Context容器中获取内容-->
<tr th:each="hero:${list}">
<!--th:text替换掉元素的文本内容-->
<td th:text="${hero.id}">1</td>
<td><a th:text="${hero.name}"
th:href="'showupdate?id='+${hero.id}"></a></td>
<td th:text="${hero.type}">打野</td>
<td th:text="${hero.money}">5888</td>
<td><a th:href="'delete?id='+${hero.id}">删除</a></td>
</tr>
</table>
</body>
</html>
修改数据的视图:
package cn.tedu.controller;
import cn.tedu.dao.HeroDao;
import cn.tedu.entity.Hero;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet(name = "UpdateServlet",urlPatterns = "/update")
public class UpdateServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
String name = request.getParameter("name");
String type = request.getParameter("type");
String money = request.getParameter("money");
//把参数封装到Hero对象中
Hero hero = new Hero(Integer.parseInt(id),name,
type,Integer.parseInt(money));
HeroDao dao = new HeroDao();
dao.update(hero);
//重定向到列表页面
response.sendRedirect("/findall");
}
}
显示修改单条数据:
package cn.tedu.controller;
import cn.tedu.dao.HeroDao;
import cn.tedu.entity.Hero;
import cn.tedu.utils.ThUtils;
import org.thymeleaf.context.Context;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet(name = "ShowUpdateServlet",urlPatterns = "/showupdate")
public class ShowUpdateServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
System.out.println("修改id="+id);
HeroDao dao = new HeroDao();
//通过id查询英雄详情
Hero hero = dao.findById(id);
System.out.println(hero);
//通过模板引擎 将页面和数据整合到一起 并返回给客户端
Context context = new Context();
context.setVariable("hero",hero);
ThUtils.print("update.html",context,response);
}
}
修改单条语句的页面(通过id进行绑定):
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<h3>修改页面</h3>
<form action="update">
<input type="text" name="id" th:value="${hero.id}" readonly="readonly">
<input type="text" name="name" th:value="${hero.name}">
<input type="text" name="type" th:value="${hero.type}">
<input type="text" name="money" th:value="${hero.money}">
<input type="submit" value="修改">
</form>
</body>
</html>