1.创建maven项目（取名为firstServlet）；
2.按上图创建各个文件：
- 创建一个名为java的Directory包；用来存放后端的文件
- 用同样的方法，在webapp包里创建js包，并导入jQuery
3.在 pom.xml 中引⼊ mysql 的依赖
注意：引入与自己相对应的版本号
mysql mysql-connector-java 5.1.45
4.创建数据库, 创建 messages 表
set character_set_database=utf8; set character_set_server=utf8; create database if not exists LovingWall; use LovingWall; drop table if exists messages; create table messages (`from` varchar(255), `to` varchar(255), `message` varchar(2048));
5.创建 DBUtil 类
DBUtil 类主要实现以下功能:
创建 MysqlDataSource 实例, 设置 URL, username, password 等属性.
提供 getConnection ⽅法, 和 MySQL 服务器建⽴连接.
提供 close ⽅法, ⽤来释放必要的资源.
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtil{
private DBUtil() {
}
private static volatile MysqlDataSource mysqlDataSource;
private static volatile Connection connection;
//1.get DataSource
private static MysqlDataSource getMysqlDataSource() {
if (mysqlDataSource == null) {
synchronized (DBUtil.class) {
if (mysqlDataSource == null) {
mysqlDataSource = new MysqlDataSource();
mysqlDataSource.setURL("jdbc:mysql://127.0.0.1:3306/LovingWall?characterEncoding:utf8&useSSL=false");
mysqlDataSource.setUser("root");
mysqlDataSource.setPassword("12345678");
}
}
}
return mysqlDataSource;
}
// 2.get connect
public static Connection getConnection() {
if (connection == null) { // 首次访问
synchronized (DBUtil.class) {
if (connection == null) {
try {
//1.得到数据源
MysqlDataSource dataSource = getMysqlDataSource();
//2.数据库连接
connection = (Connection) dataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
return connection;
}
// 2.提供关闭资源的方法
public static void close(ResultSet resultSet, PreparedStatement statement, Connection connection) throws SQLException {
//得到结果，执行完成，关闭资源
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
}
}
2.创建的loving_wall.html文件
表白墙——连接数据库版本
表白墙
输入后点击提交, 会将信息显示在表格中
谁:
对谁:
说什么:
3.valuable 文件
public class Valuable {
public static boolean haslength(String s){
return (s!=null&&s.length()!=0);
}
}
4.AddLovingMessage2类
import com.mysql.jdbc.Connection;
import lombok.SneakyThrows;
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.sql.PreparedStatement;
@WebServlet("/addmessage2")
public class AddLovingMessage2 extends HttpServlet {
@SneakyThrows
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
int result=0;
//1.得到前端值
String from=req.getParameter("from");
String to=req.getParameter("to");
String message=req.getParameter("message");
if(Valuable.haslength(from)&&Valuable.haslength(to)&&Valuable.haslength(message)){
//2.讲表白信息加到数据库
//2.1
Connection connection = DBUtil.getConnection();
//2.2
String insertSQL="insert into messages(`from`,`to`,`message`) values(?,?,?)";
PreparedStatement statement= connection.prepareStatement(insertSQL);
statement.setString(1,from);
statement.setString(2,to);
statement.setString(3,message);
// 2.3 执行执行器，并返回结果
result=statement.executeUpdate();
// 2.4 关闭资源
DBUtil.close(null, statement, connection);
}
resp.setContentType("text/html;charset=utf-8");
//给前端一个消息提示是否添加成功
resp.getWriter().println(result);
}
}
5.GetLovingMessage2类
import com.fasterxml.jackson.databind.ObjectMapper;
import com.mysql.jdbc.Connection;
import lombok.SneakyThrows;
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.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
@WebServlet("/getmessage2")
public class GetLovingMessage2 extends HttpServlet {
@SneakyThrows
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 查询数据库中的表白列表
List list = new ArrayList<>();
// 1.得到 connection
Connection connection = DBUtil.getConnection();
// 2.拼接SQL，创建执行器
String sql = "select * from messages";
PreparedStatement statement = connection.prepareStatement(sql);
// 3.执行SQL，返回 resultSet 并循环将数据添加到 list 中
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
String from = resultSet.getString("from");
String to = resultSet.getString("to");
String message = resultSet.getString("message");
list.add(new Message(from, to, message));
}
// 4.关闭资源
DBUtil.close(resultSet, statement, connection);
resp.setContentType("application/json; charset=utf-8");
ObjectMapper objectMapper = new ObjectMapper();
resp.getWriter().println(objectMapper.writeValueAsString(list));
}
}
结果：
查看设置路径
