关于这次学校实训,我们做了一个类似于CSDN博客的项目,然后我们小组取名叫SEEK,一个学习论坛,前面ppt有介绍,我主要是负责后端数据库的,与MySQL交互,由我的组长写好js代码,然后将前端所获取的数据post给我 然后我写好Servlet端返回他想要的数据,看下面,这7天我还是写了好多好多的java代码,彻彻底底当了一次后端码农!
本篇文章只是介绍这个项目,需要后端源码的课去看这篇文章哦
SEEK学习论坛-JavaWeb开发实训课题 (数据库MySQL+js+Ajax+Servlet)代码分析篇
https://blog.csdn.net/weixin_42429718/article/details/95355366
这里是我封装好了的关于连接MySQL工具类DButil
可能需要ojdbc工具,这里分享一下
mysql-connector-java-5.1.39-bin.jar
链接：https://pan.baidu.com/s/1XVhHe9dS9O4VpGTI5OjK9w
提取码：vtoo
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* util工具类
* @author yangchaoyi
*
*/
public class DButil {
//声明Connection对象
Connection con;
//驱动程序名
static String driver = "com.mysql.jdbc.Driver";
//URL指向要访问的数据库名mydata
String url = "jdbc:mysql://填入IP地址(一般是localhost):3306/填入数据库名";
//MySQL配置时的用户名
String user = "xxx";
//MySQL配置时的密码
String password = "****";
//1、执行静态方法,加载数据库驱动
static {
try {
System.out.println("正在加载数据库驱动...");
Class.forName(driver);
System.out.println("已加载数据库驱动！！！\n");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//2、创建数据库连接的方法
public Connection getConnection() {
try {
System.out.println("正在连接到数据库...");
con = DriverManager.getConnection(url,user,password);
System.out.println("已连接到MySQL数据库！！！\n");
return con;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//3、关闭数据库连接，释放JDBC资源的方法
public void closeConnection(Connection connection) {
if (connection != null) {
try {
System.out.println("准备释放jdbc资源，断开数据库连接...");
System.out.println("connection.close();");
connection.close();//立即释放jdbc资源，而不是等自动释放
System.out.println("已断开数据库连接并且释放了jdbc资源\n");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
admin_Dto.java
package com.dto;
public class admin_Dto {
String id;
String passw;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPassw() {
return passw;
}
public void setPassw(String passw) {
this.passw = passw;
}
}
article_colect_Dto.java
package com.dto;
public class article_colect_Dto {
String userid;
String arcid;
String col_time;
String uncol_time;
public String getUserid() {
return userid;
}
public void setUserid(String userid) {
this.userid = userid;
}
public String getArcid() {
return arcid;
}
public void setArcid(String arcid) {
this.arcid = arcid;
}
public String getCol_time() {
return col_time;
}
public void setCol_time(String col_time) {
this.col_time = col_time;
}
public String getUncol_time() {
return uncol_time;
}
public void setUncol_time(String uncol_time) {
this.uncol_time = uncol_time;
}
}
article_comment_Dto
package com.dto;
public class article_comment_Dto {
String arcticle_id;
String user_id;
String reback_arctcle_id;
String time;
String comment_id;
String content;
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getArcticle_id() {
return arcticle_id;
}
public void setArcticle_id(String arcticle_id) {
this.arcticle_id = arcticle_id;
}
public String getUser_id() {
return user_id;
}
public void setUser_id(String user_id) {
this.user_id = user_id;
}
public String getReback_arctcle_id() {
return reback_arctcle_id;
}
public void setReback_arctcle_id(String reback_arctcle_id) {
this.reback_arctcle_id = reback_arctcle_id;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
public String getComment_id() {
return comment_id;
}
public void setComment_id(String comment_id) {
this.comment_id = comment_id;
}
}
article_Dto
package com.dto;
public class article_Dto {
String userid;
String arcid;
String arctime;
String arcatatus;
String archtml;
String title;
String content;
String imageurl;
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getImageurl() {
return imageurl;
}
public void setImageurl(String imageurl) {
this.imageurl = imageurl;
}
public String getUserid() {
return userid;
}
public void setUserid(String userid) {
this.userid = userid;
}
public String getArcid() {
return arcid;
}
public void setArcid(String arcid) {
this.arcid = arcid;
}
public String getArctime() {
return arctime;
}
public void setArctime(String arctime) {
this.arctime = arctime;
}
public String getArcatatus() {
return arcatatus;
}
public void setArcatatus(String arcatatus) {
this.arcatatus = arcatatus;
}
public String getArchtml() {
return archtml;
}
public void setArchtml(String archtml) {
this.archtml = archtml;
}
}
admin_model
package com.model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.dto.admin_Dto;
import com.dto.article_Dto;
import com.dto.user_Dto;
import util.DButil;
/*
* 用户表相关方法
*/
public class admin_model {
// 通过id查询 返回一个admin的Dto
public admin_Dto QueryById(String id) {
DButil db = new DButil();
Connection con = db.getConnection();
PreparedStatement pstmt;
ResultSet rs;
admin_Dto dto = new admin_Dto();
try {
String sql = "select * from admin where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1,id);
rs = pstmt.executeQuery();// 执行SQL语句
if (rs.next()) {
dto.setId(rs.getString("id"));
dto.setPassw(rs.getString("passw"));
}
// 关闭，释放资源
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return dto;
}
// 2.从数据库导入数据(全查找admin信息)
public List<admin_Dto> find_admin_Dto() {
DButil db = new DButil();
Connection con = db.getConnection();
PreparedStatement pstmt;
ResultSet rs;
List<admin_Dto> list = new ArrayList<admin_Dto>();
try {
String sql = "select * from admin";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();// 执行SQL语句
while (rs.next()) {
admin_Dto dto = new admin_Dto();
dto.setId(rs.getString("id"));
dto.setPassw(rs.getString("passw"));
list.add(dto);
}
// 关闭，释放资源
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//3.插入数据到admin表中 传入admin_Dto参数
public void add_admin(admin_Dto dto)
{
DButil db = new DButil();
Connection conn = db.getConnection();
PreparedStatement statement = null;
try {
String sql="insert into admin(id, passw) values(?,?)";
statement = conn.prepareStatement(sql);
statement.setString(1, dto.getId());
statement.setString(2, dto.getPassw());
int result=statement.executeUpdate();
if(result>0)
{
System.out.println("插入到admin表成功!");
}
}
catch(Exception e1) {
e1.printStackTrace();
System.out.println(e1.getMessage());
}
finally
{
try {
statement.close();
conn.close();
}
catch(Exception e2) {
System.out.println(e2.getMessage());
}
}
}
}
article_colect_model
package com.model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.dto.admin_Dto;
import com.dto.article_Dto;
import com.dto.article_colect_Dto;
import util.DButil;
/*
* 文章收集表相关方法
*/
public class article_colect_model {
//1.通过useerid查询 返回一个article_colect的Dto
public article_colect_Dto QueryByUserId(String userid) {
DButil db = new DButil();
Connection con = db.getConnection();
PreparedStatement pstmt;
ResultSet rs;
article_colect_Dto dto = new article_colect_Dto();
try {
String sql = "select * from article_colect where userid=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, userid);
rs = pstmt.executeQuery();// 执行SQL语句
if (rs.next()) {
dto.setUserid(rs.getString("userid"));
dto.setArcid(rs.getString("arcid"));
dto.setCol_time(rs.getString("col_time"));
dto.setUncol_time(rs.getString("uncol_time"));
}
// 关闭，释放资源
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return dto;
}
// 2.从数据库导入数据(全查找article_colect信息)
public List<article_colect_Dto> find_article_colect() {
DButil db = new DButil();
Connection con = db.getConnection();
PreparedStatement pstmt;
ResultSet rs;
List<article_colect_Dto> list = new ArrayList<article_colect_Dto>();
try {
String sql = "select * from article_colect";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();// 执行SQL语句
while (rs.next()) {
article_colect_Dto dto = new article_colect_Dto();
dto.setUserid(rs.getString("userid"));
dto.setArcid(rs.getString("arcid"));
dto.setCol_time(rs.getString("col_time"));
dto.setUncol_time(rs.getString("uncol_time"));
list.add(dto);
}
// 关闭，释放资源
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//3.插入数据到article_colect表中 传入article_colect_Dto参数
public void add_article_colect(article_colect_Dto dto)
{
DButil db = new DButil();
Connection conn = db.getConnection();
PreparedStatement statement = null;
try {
String sql="insert into article_colect(userid, arcid, col_time, uncol_time) values(?,?,?,?)";
statement = conn.prepareStatement(sql);
statement.setString(1, dto.getUserid());
statement.setString(2, dto.getArcid());
statement.setString(3, dto.getCol_time());
statement.setString(4, dto.getUncol_time());
int result=statement.executeUpdate();
if(result>0)
{
System.out.println("插入到article_colect表成功!");
}
}
catch(Exception e1) {
e1.printStackTrace();
System.out.println(e1.getMessage());
}
finally
{
try {
statement.close();
conn.close();
}
catch(Exception e2) {
System.out.println(e2.getMessage());
}
}
}
// 4.通过userid从数据库导入数据(全查找article_colect信息)
public List<article_colect_Dto> find_article_colect_DtoByUserId(String userid) {
DButil db = new DButil();
Connection con = db.getConnection();
PreparedStatement pstmt;
ResultSet rs;
List<article_colect_Dto> list = new ArrayList<article_colect_Dto>();
try {
String sql = "select * from article_colect where userid=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, userid);
rs = pstmt.executeQuery();// 执行SQL语句
while (rs.next()) {
article_colect_Dto dto = new article_colect_Dto();
dto.setUserid(rs.getString("userid"));
dto.setArcid(rs.getString("arcid"));
dto.setCol_time(rs.getString("col_time"));
dto.setUncol_time(rs.getString("uncol_time"));
list.add(dto);
}
// 关闭，释放资源
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
}
article_comment_model
package com.model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.dto.article_colect_Dto;
import com.dto.article_comment_Dto;
import util.DButil;
public class article_comment_model {
//1.通过arcticle_id查询 返回一个article_comment的Dto
public article_comment_Dto QueryByArcticleId(String arcticle_id) {
DButil db = new DButil();
Connection con = db.getConnection();
PreparedStatement pstmt;
ResultSet rs;
article_comment_Dto dto = new article_comment_Dto();
try {
String sql = "select * from article_comment where arcticle_id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, arcticle_id);
rs = pstmt.executeQuery();// 执行SQL语句
if (rs.next()) {
dto.setUser_id(rs.getString("user_id"));
dto.setArcticle_id(rs.getString("arcticle_id"));
dto.setReback_arctcle_id(rs.getString("reback_arctcle_id"));
dto.setTime(rs.getString("time"));
dto.setComment_id(rs.getString("comment_id"));
dto.setContent(rs.getString("content"));
}
// 关闭，释放资源
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return dto;
}
// 2.从数据库导入数据(全查找article_comment信息)
public List<article_comment_Dto> find_article_comment_Dto() {
DButil db = new DButil();
Connection con = db.getConnection();
PreparedStatement pstmt;
ResultSet rs;
List<article_comment_Dto> list = new ArrayList<article_comment_Dto>();
try {
String sql = "select * from article_comment";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();// 执行SQL语句
while (rs.next()) {
article_comment_Dto dto = new article_comment_Dto();
dto.setUser_id(rs.getString("user_id"));
dto.setArcticle_id(rs.getString("arcticle_id"));
dto.setReback_arctcle_id(rs.getString("reback_arctcle_id"));
dto.setTime(rs.getString("time"));
dto.setComment_id(rs.getString("comment_id"));
dto.setContent(rs.getString("content"));
list.add(dto);
}
// 关闭，释放资源
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//3.插入数据到article_comment表中 传入article_comment_Dto参数
public void add_article_comment(article_comment_Dto dto)
{
DButil db = new DButil();
Connection conn = db.getConnection();
PreparedStatement statement = null;
try {
String sql="insert into article_comment(arcticle_id, user_id, reback_arctcle_id, time, comment_id,content) values(?,?,?,?,?,?)";
statement = conn.prepareStatement(sql);
statement.setString(1, dto.getArcticle_id());
statement.setString(2, dto.getUser_id());
statement.setString(3, dto.getReback_arctcle_id());
statement.setString(4, dto.getTime());
statement.setString(5, dto.getComment_id());
statement.setString(6, dto.getContent());
int result=statement.executeUpdate();
if(result>0)
{
System.out.println("插入到article_comment表成功!");
}
}
catch(Exception e1) {
e1.printStackTrace();
System.out.println(e1.getMessage());
}
finally
{
try {
statement.close();
conn.close();
}
catch(Exception e2) {
System.out.println(e2.getMessage());
}
}
}
//4.通过arcticle_id查询 返回一个article_comment的List集合
public List<article_comment_Dto> find_QueryByArcticleId(String arcticle_id) {
DButil db = new DButil();
Connection con = db.getConnection();
PreparedStatement pstmt;
ResultSet rs;
List<article_comment_Dto> list = new ArrayList<article_comment_Dto>();
try {
String sql = " select * from article_comment where arcticle_id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, arcticle_id);
rs = pstmt.executeQuery();// 执行SQL语句
while (rs.next()) {
article_comment_Dto dto = new article_comment_Dto();
dto.setUser_id(rs.getString("user_id"));
dto.setArcticle_id(rs.getString("arcticle_id"));
dto.setReback_arctcle_id(rs.getString("reback_arctcle_id"));
dto.setTime(rs.getString("time"));
dto.setComment_id(rs.getString("comment_id"));
dto.setContent(rs.getString("content"));
list.add(dto);
}
// 关闭，释放资源
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
}
article_colectFindByUserIdServlet
package com.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dto.article_Dto;
import com.dto.article_colect_Dto;
import com.model.article_colect_model;
/**
* Servlet implementation class article_colectFindByUserIdServlet
*/
public class article_colectFindByUserIdServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public article_colectFindByUserIdServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html");
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
String article_colectInfo = request.getParameter("articlecommentInfo"); //从前端获取数据first
/*ycy*/
System.out.print(article_colectInfo);
JSONObject jsonObject = JSONObject.parseObject(article_colectInfo);
System.out.println(jsonObject.getString("arcid"));
article_colect_Dto article_colect = jsonObject.toJavaObject(article_colect_Dto.class);
article_colect_model artc=new article_colect_model();
List<article_colect_Dto> list =artc.find_article_colect_DtoByUserId(article_colect.getUserid());
//将list转换为json数组
JSONArray jsonList = JSONArray.parseArray(JSON.toJSONString(list));
System.out.println(jsonList);
String jsonStr = jsonList.toString();
out.print(jsonStr);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
article_colectInsertServlet
package com.servlet;
import java.io.IOException;
import java.io.PrintWriter;
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 com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dto.article_colect_Dto;
import com.model.article_colect_model;
/**
* Servlet implementation class article_colectInsertServlet
*/
@WebServlet("/article_colectInsertServlet")
public class article_colectInsertServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public article_colectInsertServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html");
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
String article_commentInfo = request.getParameter("likearticleInfo"); //从前端获取数据first
/*ycy*/
JSONObject jsonObject = JSONObject.parseObject(article_commentInfo);
article_colect_Dto article_colect = jsonObject.toJavaObject(article_colect_Dto.class);
article_colect_model artc=new article_colect_model();
artc.add_article_colect(article_colect);
String req=JSON.toJSONString(article_colect);
out.print(req);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
findArticleByarticle_colectUserIdServlet
package com.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
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 com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.dto.article_Dto;
import com.dto.article_colect_Dto;
import com.model.article_colect_model;
import com.model.article_model;
/**
* Servlet implementation class findArticleByarticle_colectUserIdServlet
*/
@WebServlet("/findArticleByarticle_colectUserIdServlet")
public class findArticleByarticle_colectUserIdServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public findArticleByarticle_colectUserIdServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html");
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
String article_colectInfo = request.getParameter("findArticleByarticle_colectUserIdServlet"); //从前端获取数据first
/*ycy*/
JSONObject jsonObject = JSONObject.parseObject(article_colectInfo);
article_colect_Dto article_colect = jsonObject.toJavaObject(article_colect_Dto.class);
article_model acm=new article_model();
// List<article_Dto> list =artm.find_articleByArcIdFromArticle_Colect(article_colect);
article_colect_model arcm=new article_colect_model ();
List<article_colect_Dto> arclist=arcm.find_article_colect_DtoByUserId(article_colect.getUserid());
String jsonStr="";
int k=0;
String temp="";
int end=arclist.size();
System.out.println(end);
for(article_colect_Dto acol_dto1:arclist)
{
List<article_Dto> list=acm.find_article_DtoByArcId(acol_dto1.getArcid());
JSONArray jsonList = JSONArray.parseArray(JSON.toJSONString(list));
temp=jsonList.toString();
if(k==0){
// jsonStr+="[";
jsonStr+=temp.substring(0,temp.length() - 1);
jsonStr+=",";
temp="";
k++;
}
else
{
if(k!=end-1)
{
jsonStr+=temp.substring(1,temp.length() - 1);
jsonStr+=",";
temp="";
k++;
}
else
{
jsonStr+=temp.substring(1,temp.length()-1);
jsonStr+="]";
temp="";
k++;
}
}
System.out.println(jsonStr);
}
// System.out.println(jsonStr);
//
//
// //将list转换为json数组
// JSONArray jsonList = JSONArray.parseArray(JSON.toJSONString(list));
// String jsonStr=jsonList.toString();
// System.out.print("str:::::::::::::::::::s"+jsonStr);
out.print(jsonStr);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
MySQL数据库建表 sql语句
/*
Navicat Premium Data Transfer
Source Server : 123
Source Server Type : MySQL
Source Server Version : 50644
Source Host : 119.27.167.223:3306
Source Schema : shixun
Target Server Type : MySQL
Target Server Version : 50644
File Encoding : 65001
Date: 07/07/2019 04:20:05
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NOT NULL COMMENT '管理员id，主键',
`passw` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NOT NULL DEFAULT '123456' COMMENT '密码',
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT NULL,
`adminRight` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_german2_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of admin
-- ----------------------------
INSERT INTO `admin` VALUES ('201701', '123456', NULL, '普通管理员');
INSERT INTO `admin` VALUES ('201702', '000', NULL, '普通管理员');
INSERT INTO `admin` VALUES ('201703', '111', NULL, '普通管理员');
INSERT INTO `admin` VALUES ('201724', '123456', 'ycy', '超级管理员');
-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
`userid` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NOT NULL,
`arcid` bigint(20) NOT NULL AUTO_INCREMENT,
`arctime` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
`arcatatus` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT '公开' COMMENT '文章状态',
`archtml` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '文章主体',
`title` longtext CHARACTER SET utf8 COLLATE utf8_german2_ci NULL,
`content` longtext CHARACTER SET utf8 COLLATE utf8_german2_ci NULL,
`imageurl` longtext CHARACTER SET utf8 COLLATE utf8_german2_ci NULL,
PRIMARY KEY (`arcid`) USING BTREE,
INDEX `userid`(`userid`) USING BTREE,
CONSTRAINT `article_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 56 CHARACTER SET = utf8 COLLATE = utf8_german2_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of article
-- ----------------------------
INSERT INTO `article` VALUES ('123456', 20, '2019-07-05 07:25:35', '0', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/s6BTS8x4IA2Wa7w.js', '哈利波特', 'nvalid or unexpected token ”“”“”“”“\"\"\"\"\"\"\"\"\"\"\"\"第1部《哈利波特与魔法石》Harry Potter and the Sorcerer\'s Stone (', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/dxw3E5xB1xxo43K.jpg');
INSERT INTO `article` VALUES ('123456', 21, '2019-07-05 07:24:50', '0', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/G9ib1IoSCBJEVtx.js', '哈利波特', '1部《哈利波特与魔法石》Harry Potter and the Sorcerer\'s Stone (2001)\n 一岁的哈利波特失去父母后，神秘地出现在姨父姨妈家的门前。哈利在姨父家饱受欺凌，', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/TmXmdnYmLh5Wth2.jpg');
INSERT INTO `article` VALUES ('123456', 22, '2019-07-05 07:24:53', '0', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/jrvYEJPIeoggib1.js', '哈利波特', 'lt;iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/1NmxkGY5fxc\" frameborder=\"0\" ', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/0TMj3CiUwJKCaGN.jpg');
INSERT INTO `article` VALUES ('123456', 23, '2019-07-05 07:24:56', '0', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/003d9NyDBiVHmsu.js', '哈利波特', 'lt;iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/1NmxkGY5fxc\" frameborder=\"0\" ', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/t1SldFJPuZNKPoO.jpg');
INSERT INTO `article` VALUES ('123456', 24, '2019-07-05 07:25:06', '0', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/EEvhmgQO7oqlf0V.js', '哈利波特', '1部《哈利波特与魔法石》Harry Potter and the Sorcerer\'s Stone (2001)\n 一岁的哈利波特失去父母后，神秘地出现在姨父姨妈家的门前。哈利在姨父家饱受欺凌，', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/FbPHRUq4TJbLdD5.jpg');
INSERT INTO `article` VALUES ('123456', 27, '2019-07-05 07:40:28', '0', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/euWPP90PTJincrp.js', '冰与火之歌', '事背景中虚构的世界，分为两片大陆：位于西面的“日落国度”维斯特洛；位于东面的类似亚欧大陆。维斯特洛大陆边境处发现远古传说中早已灭绝的生物开始，危险也渐渐在靠近这里。这片大陆的临冬城主暨北境统领艾德史......', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/xgAM61RB3AYv12d.jpg');
INSERT INTO `article` VALUES ('123456', 53, '2019-07-06 17:42:18', '0', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/4QZPIZBwYwwvt2o.js', '', '爱吃杀杀杀水水水水吖的阿萨吖的阿瑟东......', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/eTCQ5bOwIJiS0Wd.jpg');
INSERT INTO `article` VALUES ('201701020124', 54, '2019-07-06 17:47:08', '0', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/5C0a2peP4FuYCK6.js', '阿斯兰的还哦', '......', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/o86MKJ3KJaaOBIj.jpg');
INSERT INTO `article` VALUES ('缪传鹏', 55, '2019-07-07 02:57:45', '0', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/Nyrevwm03fDQOhU.js', 'wwwwwwwwwwwwwwwwwwwww', 'wdqqqqqqqqqqqqqqqqqqqqq......', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/i2cbgaKAGMHWXah.jpg');
-- ----------------------------
-- Table structure for article_colect
-- ----------------------------
DROP TABLE IF EXISTS `article_colect`;
CREATE TABLE `article_colect` (
`userid` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT NULL,
`arcid` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT NULL,
`col_time` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
`uncol_time` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
INDEX `article_colect_user_id`(`userid`) USING BTREE,
CONSTRAINT `article_colect_user_id` FOREIGN KEY (`userid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_german2_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of article_colect
-- ----------------------------
INSERT INTO `article_colect` VALUES ('123456', '21', '2019-07-06 16:03:33', NULL);
INSERT INTO `article_colect` VALUES ('123456', '21', '2019-07-06 16:14:13', NULL);
INSERT INTO `article_colect` VALUES ('201701020124', '20', '2019-07-06 16:35:17', NULL);
INSERT INTO `article_colect` VALUES ('201701020124', '21', '2019-07-06 18:42:52', NULL);
INSERT INTO `article_colect` VALUES ('201701020124', '22', '2019-07-06 18:44:49', NULL);
INSERT INTO `article_colect` VALUES ('缪传鹏', '24', '2019-07-07 02:56:23', NULL);
INSERT INTO `article_colect` VALUES ('缪传鹏', '22', '2019-07-07 03:01:05', NULL);
-- ----------------------------
-- Table structure for article_comment
-- ----------------------------
DROP TABLE IF EXISTS `article_comment`;
CREATE TABLE `article_comment` (
`arcticle_id` bigint(20) NULL DEFAULT NULL COMMENT '被评论文章ID',
`user_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT NULL COMMENT '发表信息人ID',
`reback_arctcle_id` bigint(20) NULL DEFAULT NULL COMMENT '回复评论ID',
`time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
`comment_id` bigint(20) NOT NULL AUTO_INCREMENT,
`content` longtext CHARACTER SET utf8 COLLATE utf8_german2_ci NULL,
PRIMARY KEY (`comment_id`) USING BTREE,
INDEX `arc_comment_user_id`(`user_id`) USING BTREE,
INDEX `arc_comment_arcticle_id`(`arcticle_id`) USING BTREE,
CONSTRAINT `arc_comment_arcticle_id` FOREIGN KEY (`arcticle_id`) REFERENCES `article` (`arcid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `arc_comment_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_german2_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for lock_user
-- ----------------------------
DROP TABLE IF EXISTS `lock_user`;
CREATE TABLE `lock_user` (
`userid` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NOT NULL,
`locktime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
`res` longtext CHARACTER SET utf8 COLLATE utf8_german2_ci NULL,
PRIMARY KEY (`userid`) USING BTREE,
CONSTRAINT `lock_user_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_german2_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of lock_user
-- ----------------------------
INSERT INTO `lock_user` VALUES ('201701020124', '2019-07-03 23:05:05', NULL);
INSERT INTO `lock_user` VALUES ('201701020135', '2019-07-06 00:00:00', 'xxx');
-- ----------------------------
-- Table structure for notice
-- ----------------------------
DROP TABLE IF EXISTS `notice`;
CREATE TABLE `notice` (
`notice_text` longtext CHARACTER SET utf8 COLLATE utf8_german2_ci NULL,
`time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
`admin_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT NULL,
INDEX `admin_id`(`admin_id`) USING BTREE,
CONSTRAINT `notice_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admin` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_german2_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of notice
-- ----------------------------
INSERT INTO `notice` VALUES ('溜', '2019-07-03 23:05:13', '201701');
INSERT INTO `notice` VALUES ('醉了', '2019-07-06 00:00:00', '201702');
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NOT NULL COMMENT '用户id',
`passw` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NOT NULL COMMENT '密码',
`arctitles` int(255) NULL DEFAULT 0 COMMENT '用户发表文章数量',
`fans` int(255) NOT NULL DEFAULT 0 COMMENT '粉丝数',
`collects` int(255) NULL DEFAULT 0 COMMENT '收藏数',
`integral` int(255) NULL DEFAULT 0 COMMENT '积分',
`email` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT NULL COMMENT '邮箱',
`himgUrl` longtext CHARACTER SET utf8 COLLATE utf8_german2_ci NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_german2_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('111', '1111111111111', 0, 0, 0, 0, '1111111111111@qq.com', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/H6IbFXJU3KMp1jp.jpg');
INSERT INTO `user` VALUES ('123456', '111111111111', 22, 41, 51, 786, '123456@qq.com', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/b9lNfeDCsGIkHbL.jpg');
INSERT INTO `user` VALUES ('201701020108', '123', 33, 46, 108, 1142, '1275948439@qq.com', NULL);
INSERT INTO `user` VALUES ('201701020124', '654321', 99, 100, 99, 1000, '201701020124@qq.com', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/a8081L9MBl3dSeo.jpg');
INSERT INTO `user` VALUES ('201701020135', '123456', 33, 23, 10, 321, '1341312038@qq.com', NULL);
INSERT INTO `user` VALUES ('201701020145', '111', 11, 100, 100, 1200, '2903067812@qq.com', NULL);
INSERT INTO `user` VALUES ('缪传鹏', '1111111111111', 0, 0, 0, 0, '1111111111111@qq.com', 'https://test-1258897694.cos.ap-chengdu.myqcloud.com/GVykvYnfaEMXLb7.jpg');
-- ----------------------------
-- Table structure for user_feedback
-- ----------------------------
DROP TABLE IF EXISTS `user_feedback`;
CREATE TABLE `user_feedback` (
`userid` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT NULL,
`time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
`type` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT NULL,
`feedtext` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
`id` bigint(255) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`) USING BTREE,
INDEX `userid`(`userid`) USING BTREE,
CONSTRAINT `user_feedback_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 201903 CHARACTER SET = utf8 COLLATE = utf8_german2_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of user_feedback
-- ----------------------------
INSERT INTO `user_feedback` VALUES ('201701020124', '2019-07-03 22:59:42', '111', 'tql', 201901);
INSERT INTO `user_feedback` VALUES ('201701020135', '2019-07-06 00:00:00', 'xxx', 'xxx', 201902);
-- ----------------------------
-- Table structure for watched
-- ----------------------------
DROP TABLE IF EXISTS `watched`;
CREATE TABLE `watched` (
`wa_userid` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT NULL,
`wing_userid` varchar(20) CHARACTER SET utf8 COLLATE utf8_german2_ci NULL DEFAULT NULL,
`wa_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
`unwa_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
INDEX `watched_wa_user_id`(`wa_userid`) USING BTREE,
INDEX `watched_wing_user_id`(`wing_userid`) USING BTREE,
CONSTRAINT `watched_wa_user_id` FOREIGN KEY (`wa_userid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `watched_wing_user_id` FOREIGN KEY (`wing_userid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_german2_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of watched
-- ----------------------------
INSERT INTO `watched` VALUES ('201701020124', '201701020124', '2019-07-03 23:05:37', '2019-07-03 23:05:39');
INSERT INTO `watched` VALUES ('201701020145', '201701020135', '2019-07-04 15:57:40', '2019-07-04 15:57:40');
INSERT INTO `watched` VALUES ('201701020135', '201701020145', '2019-07-07 00:00:00', '2019-07-09 00:00:00');
INSERT INTO `watched` VALUES ('123456', '123456', NULL, NULL);
INSERT INTO `watched` VALUES ('201701020124', '123456', '2019-07-06 16:35:19', NULL);
INSERT INTO `watched` VALUES ('201701020124', '123456', '2019-07-06 18:42:49', NULL);
INSERT INTO `watched` VALUES ('201701020124', '123456', '2019-07-06 18:44:50', NULL);
-- ----------------------------
-- Triggers structure for table article
-- ----------------------------
DROP TRIGGER IF EXISTS `uptime`;
delimiter ;;
CREATE TRIGGER `uptime` BEFORE INSERT ON `article` FOR EACH ROW SET new.arctime=NOW()
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table article_colect
-- ----------------------------
DROP TRIGGER IF EXISTS `coltime_update`;
delimiter ;;
CREATE TRIGGER `coltime_update` BEFORE INSERT ON `article_colect` FOR EACH ROW SET new.col_time=NOW()
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table watched
-- ----------------------------
DROP TRIGGER IF EXISTS `warime_update`;
delimiter ;;
CREATE TRIGGER `warime_update` BEFORE INSERT ON `watched` FOR EACH ROW SET new.wa_time=NOW()
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
本篇文章只是介绍这个项目,需要后端源码的课去看这篇文章哦
SEEK学习论坛-JavaWeb开发实训课题 (数据库MySQL+js+Ajax+Servlet)代码分析篇
https://blog.csdn.net/weixin_42429718/article/details/95355366
学如逆水行舟,不进则退
扫一扫
7253
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
