创建数据库
drop table if exists users;
create table users
(
id bigint(20) not null auto_increment comment '用户id',
username varchar(100) default '' comment '用户名',
phone varchar(100) default '' comment '电话',
email varchar(100) default '' comment '邮箱',
primary key (id)
) engine=innodb comment='用户表';
后端代码
根据模版创建javaweb项目(此步骤省略)
创建实体类
public class User {
private int id;
private String username;
private String phone;
private String email;
public User() {
}
public User(int id, String username, String phone, String email) {
this.id = id;
this.username = username;
this.phone = phone;
this.email = email;
}
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 getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", phone='" + phone + '\'' +
", email='" + email + '\'' +
'}';
}
}
jdbc连接数据库
public class JdbcConnection {
private static Connection conn = null;
private static final String username = "你的账号"; //默认root
private static final String password = "你的密码";
public static Connection dbConnection() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException ce) {
System.out.print(ce);
}
try {
String url = "jdbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException ce) {
System.out.print(ce);
}
return conn;
}
public static void closeConnection(Connection conn, PreparedStatement pstmt, ResultSet rs) {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void closeConnection(Connection conn, PreparedStatement pstmt) {
try {
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
创建Servlet
@WebServlet("/users/*")
public class UserServlet extends HttpServlet {
private UserService userService = new UserService();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 获取用户列表
List<User> users = userService.getAllUsers();
String json = new Gson().toJson(users);
resp.setContentType("application/json");
resp.setCharacterEncoding("UTF-8");
resp.getWriter().write(json);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 添加用户
User user = new GsonBuilder()
.registerTypeAdapter(int.class, new IntTypeAdapter())
.registerTypeAdapter(Integer.class, new IntTypeAdapter()).create()
.fromJson(req.getReader(), User.class);
userService.addUser(user);
System.out.println("添加成功" + user);
}
@Override
protected void doPut(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 更新用户
String pathInfo = req.getPathInfo();
if (pathInfo != null) {
String[] pathParts = pathInfo.split("/");
if (pathParts.length > 1) {
int id = Integer.parseInt(pathParts[1]);
User user = new Gson().fromJson(req.getReader(), User.class);
userService.updateUser(id, user);
System.out.println("编辑成功" + id);
}
}
}
@Override
protected void doDelete(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// 删除用户
String pathInfo = req.getPathInfo();
if (pathInfo != null) {
String[] pathParts = pathInfo.split("/");
if (pathParts.length > 1) {
int id = Integer.parseInt(pathParts[1]);
userService.deleteUser(id);
System.out.println("删除成功" + id);
}
}
}
}
链接: 百度网盘
在主目录下创建lib目录添加jar包 右击jar选择Add as Library 在选择相对应选择即可添加成功
工具类
public class IntTypeAdapter extends TypeAdapter<Number> {
@Override
public void write(JsonWriter out, Number value)
throws IOException {
out.value(value);
}
@Override
public Number read(JsonReader in) throws IOException {
if (in.peek() == JsonToken.NULL) {
in.nextNull();
return null;
}
try {
String result = in.nextString();
if ("".equals(result)) {
return null;
}
return Integer.parseInt(result);
} catch (NumberFormatException e) {
throw new JsonSyntaxException(e);
}
}
}
数据库操作
public class UserService {
// 查询用户
public List<User> getAllUsers() {
List<User> users = new ArrayList<>();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// 连接数据库
conn = JdbcConnection.dbConnection();
// 查询产品信息
String querySql = "SELECT id, username, phone, email FROM users";
pstmt = conn.prepareStatement(querySql);
rs = pstmt.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String username = rs.getString("username");
String phone = rs.getString("phone");
String email = rs.getString("email");
User user = new User(id, username, phone, email);
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
JdbcConnection.closeConnection(conn, pstmt, rs);
}
return users;
}
// 添加用户
public void addUser(User user) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 连接数据库
conn = JdbcConnection.dbConnection();
// 添加用户
String addSql = "INSERT INTO users(username, phone, email) VALUES(?, ?, ?)";
pstmt = conn.prepareStatement(addSql);
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPhone());
pstmt.setString(3, user.getEmail());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
JdbcConnection.closeConnection(conn, pstmt);
}
}
// 修改用户信息
public void updateUser(int id, User user) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 连接数据库
conn = JdbcConnection.dbConnection();
// 更新产品信息
String updateSql = "UPDATE users SET phone = ?, email = ?, username = ? WHERE id = ?";
pstmt = conn.prepareStatement(updateSql);
pstmt.setString(1, user.getPhone());
pstmt.setString(2, user.getEmail());
pstmt.setString(3, user.getUsername());
pstmt.setInt(4, id);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
JdbcConnection.closeConnection(conn, pstmt);
}
}
// 删除用户
public void deleteUser(int id) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 连接数据库
conn = JdbcConnection.dbConnection();
// 删除用户
String deleteSql = "DELETE FROM users WHERE id = ?";
pstmt = conn.prepareStatement(deleteSql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
JdbcConnection.closeConnection(conn, pstmt);
}
}
}
跨域
@WebFilter(filterName = "Filter_CrossOrigin",urlPatterns = "/*")
public class Filter_CrossOrigin implements Filter {
@Override
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws ServletException, IOException {
HttpServletRequest request1 = (HttpServletRequest) request;
HttpServletResponse response1 = (HttpServletResponse) response;
request1.setCharacterEncoding("utf-8");
response1.setHeader("Access-Control-Allow-Origin", request1.getHeader("origin"));
response1.setHeader("Access-Control-Allow-Methods", "POST, GET, PUT, OPTIONS, DELETE");
response1.setHeader("Access-Control-Max-Age", "3600");
response1.setHeader("Access-Control-Allow-Headers", "x-requested-with, Content-Type");
response1.setHeader("Access-Control-Allow-Credentials", "true");
chain.doFilter(request1, response1);
}
}
前端代码
测试时注意后端端口和路径 我的是http://localhost:8080/j_vue_war_exploded/users
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>User Management System</title>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/vue.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
</head>
<body>
<div id="app">
<!-- 用户列表 -->
<table>
<tr>
<th>ID</th>
<th>Username</th>
<th>Phone</th>
<th>Email</th>
<th>Actions</th>
</tr>
<tr v-for="user in users">
<td>{{ user.id }}</td>
<td>{{ user.username }}</td>
<td>{{ user.phone }}</td>
<td>{{ user.email }}</td>
<td>
<button @click="editUser(user)">编辑</button>
<button @click="deleteUser(user.id)">删除</button>
</td>
</tr>
</table>
<!-- 添加/编辑用户表单 -->
<form @submit.prevent="submitForm">
<input type="hidden" v-model="form.id">
<input type="text" v-model="form.username" placeholder="Username">
<input type="text" v-model="form.phone" placeholder="Phone">
<input type="email" v-model="form.email" placeholder="Email">
<button type="submit">添加/修改</button>
</form>
</div>
<script>
new Vue({
el: '#app',
data: {
users: [],
form: {
id: '',
username: '',
phone: '',
email: ''
}
},
methods: {
fetchUsers() {
axios.get('http://localhost:8080/j_vue_war_exploded/users').then(response => {
this.users = response.data;
});
},
editUser(user) {
this.form = {...user};
},
deleteUser(id) {
axios.delete(`http://localhost:8080/j_vue_war_exploded/users/${id}`).then(() => {
this.fetchUsers();
});
},
submitForm() {
const method = this.form.id ? 'put' : 'post';
const url = this.form.id ? `http://localhost:8080/j_vue_war_exploded/users/${this.form.id}` : 'http://localhost:8080/j_vue_war_exploded/users';
axios[method](url, this.form).then(() => {
this.fetchUsers();
this.form = { id: '', username: '', phone: '', email: '' };
});
}
},
mounted() {
this.fetchUsers();
}
});
</script>
</body>
</html>
实现效果图
