Java/JavaWeb连接数据库完成增删改查(胎教级教程)

目录

项目展示：（增删改查）环境：Tomcat 8.5

1.数据库结构

1.1 创建数据库（source_db）

1.2创建数据表（tb_source），结构如下。

2.项目文件结构

3.jar包导入

4.创建JDBC数据库访问类：JDBCutil

5.创建实体类：Source

6.创建数据访问层：SourceDao

7.创建业务逻辑层：SourceService

8.HTTP响应：SourceServlet

9.index.jsp

10.add.jsp

11.update.jsp

12.web.xml

整体项目文件：

项目展示：（增删改查）环境：Tomcat 8.5

1.数据库结构

1.1 创建数据库（source_db）

1.2创建数据表（tb_source），结构如下。

2.项目文件结构

3.jar包导入

下载链接：

https://download.csdn.net/download/kai212/87724319https://download.csdn.net/download/kai212/87724319

4.创建JDBC数据库访问类：JDBCutil

package com.ydhl.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class JDBCutil {private static final String QD = "com.mysql.cj.jdbc.Driver";private static final String URL = "jdbc:mysql://localhost:3306/source_db?serverTimezone=GMT%2B8";private static final String USE = "root";private static final String PAS = "root";static {try {Class.forName(QD);} catch (ClassNotFoundException e) {e.printStackTrace();}}public static Connection getConnection() {Connection con = null;try {con = DriverManager.getConnection(URL, USE, PAS);} catch (SQLException e) {e.printStackTrace();}return con;}public static void close(ResultSet rs, PreparedStatement st, Connection con) {try {if (rs != null) {rs.close();}if (st != null) {st.cancel();}if (con != null) {con.close();}} catch (Exception e) {e.printStackTrace();}}}

5.创建实体类：Source

package com.ydhl.entity;public class Source {private int id;private String name;private String type;private String uploadDate;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 String getUploadDate() {return uploadDate;}public void setUploadDate(String uploadDate) {this.uploadDate = uploadDate;}public Source() {}public Source(int id, String name, String type, String uploadDate) {super();this.id = id;this.name = name;this.type = type;this.uploadDate = uploadDate;}}

6.创建数据访问层：SourceDao

package com.ydhl.dao;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.ydhl.entity.Source;import com.ydhl.util.JDBCutil;public class SourceDao {// 创建connection对象private Connection con = null;// 创建PreparedStatement对象private PreparedStatement st = null;// 创建resultSet对象private ResultSet rs = null;//查询public List Query(String name) {// 创建一个arraylist集合 填充数据库表数据ArrayList list = new ArrayList();try {// 获取数据库连接对象con = JDBCutil.getConnection();String sq = "SELECT * FROM tb_source WHERE name LIKE ?";st = con.prepareStatement(sq);// 将sql存放入小汽车中搬运st.setString(1, "%" + name + "%");rs = st.executeQuery();// 获取结果// 使用while循环读取resultset数据while (rs.next()) {Source sr = new Source();sr.setId(rs.getInt("id"));sr.setName(rs.getString("name"));sr.setType(rs.getString("type"));sr.setUploadDate(rs.getString("uploadDate"));// 将对象保存到对应arraylistlist.add(sr);}} catch (SQLException e) {// 异常抛出e.printStackTrace();} finally {// 使用之后关闭连接JDBCutil.close(rs, st, con);}// 返回最终结果return list;}// 删除public boolean del(int id) {try {// 获取对应数据库connection对象con = JDBCutil.getConnection();// 删除数据库sqlString sql = "delete from tb_source where id=?";st = con.prepareStatement(sql);// 设置删除IDst.setInt(1, id);// 执行sql 并且返回结果return st.executeUpdate() > 0;} catch (SQLException e) {// 抛出异常e.printStackTrace();} finally {// 关闭连接JDBCutil.close(rs, st, con);}// 如果异常默认返回falsereturn false;}// 添加public boolean add(Source source) {try {// 根据对应数据库获取对应连接con = JDBCutil.getConnection();// 新增sqlString sql = "INSERT INTO tb_source(name, type, uploadDate) VALUES (?, ?, ?)";// 传入sql值st = con.prepareStatement(sql);st.setString(1, source.getName());st.setString(2, source.getType());st.setString(3, source.getUploadDate());// 执行sql并且返回结果return st.executeUpdate() > 0;} catch (SQLException e) {// 抛出异常e.printStackTrace();} finally {// 关闭连接JDBCutil.close(rs, st, con);}// 如果异常则默认返回falsereturn false;}// 根据id查询对应数据public Source Modifysel(int id) {try {con = JDBCutil.getConnection();// 根据id查询sqlString sql = "SELECT * FROM tb_source where id=?";st = con.prepareStatement(sql);// 将sql存放入小汽车中搬运// 替换？值st.setInt(1, id);// 执行查询结果rs = st.executeQuery();// 获取结果// 绑定对象while (rs.next()) {Source sr = new Source();sr.setId(rs.getInt("id"));sr.setName(rs.getString("name"));sr.setType(rs.getString("type"));sr.setUploadDate(rs.getString("uploadDate"));return sr;}} catch (SQLException e) {// 抛出异常e.printStackTrace();} finally {// 关闭连接JDBCutil.close(rs, st, con);}// 如果出现异常则默认抛出nullreturn null;}// 修改方法public boolean modify(Source source) {try {con = JDBCutil.getConnection();// 修改sqlString sql = "UPDATE tb_source SET name=?, type=?, uploadDate=? WHERE id=?";// 填充sqlst = con.prepareStatement(sql);// 替换参数st.setString(1, source.getName());st.setString(2, source.getType());st.setString(3, source.getUploadDate());st.setInt(4, source.getId());// 返回结果return st.executeUpdate() > 0;} catch (SQLException e) {// 抛出异常e.printStackTrace();} finally {// 关闭连接JDBCutil.close(rs, st, con);}// 如果异常则默认返回falsereturn false;}}

7.创建业务逻辑层：SourceService

package com.ydhl.service;import java.util.List;import com.ydhl.dao.SourceDao;import com.ydhl.entity.Source;public class SourceService {// 查询-模糊查询public List Query(String name) {// 调用数据访问层SourceDao dao = new SourceDao();return dao.Query(name);}// 删除方法public boolean del(int id) {SourceDao dao = new SourceDao();return dao.del(id);}// 新增方法public boolean add(Source source) {SourceDao dao = new SourceDao();return dao.add(source);}//根据id查询对应数据 方面后续修改public Source Modifysel(int id) {SourceDao dao = new SourceDao();return dao.Modifysel(id);}//修改方法public boolean Modify(Source source) {SourceDao dao = new SourceDao();return dao.modify(source);}}

8.HTTP响应：SourceServlet

package com.ydhl.servlet;import java.io.IOException;import java.util.ArrayList;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.ydhl.entity.Source;import com.ydhl.service.SourceService;@WebServlet("/SourceServlet")public class SourceServlet extends HttpServlet {private static final long serialVersionUID = 1L;protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {// 也是可以直接获取参数的// 设置编码字符集req.setCharacterEncoding("utf-8");// 假设我们的参数请求中都有一个标记String action = req.getParameter("action");if (action == null) {action = "";// 这是查询请求中没有action参数}switch (action) {case "":Query(req, resp);break;case "del":// 如果标记是del 代表删除del(req, resp);break;case "add":// 如果标记是add 代表新增add(req, resp);break;case "Modifysel":// 如果标记是Modifysel 代表根据id查询对应对象Modifysel(req, resp);break;case "modify":// 获取页面参数直接修改对应数据Modify(req, resp);break;}}protected void Query(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {SourceService service = new SourceService();// 创建业务逻辑层对象String name = request.getParameter("name");if (name == null) {name = "";}ArrayList list = (ArrayList) service.Query(name);// 调用业务逻辑层方法// 将数据保存到request作用域中 稍后配合转发请求携带数据到页面request.setAttribute("list", list);// 使用转发请求 携带查询到的数据到页面当中以方便获取request.getRequestDispatcher("/index.jsp").forward(request, response);// 转发跳转页面}// 根据id删除对应数据protected void del(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// 获取唯一的idint id = Integer.parseInt(request.getParameter("id"));SourceService service = new SourceService();// 创建业务逻辑层对象// 执行删除方法service.del(id);// 重定向到对应查询工作 删除之后需要重新查询response.sendRedirect("source");}// 新增方法protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// 获取所有的参数String name = request.getParameter("name");String type = request.getParameter("type");String uploadDate = request.getParameter("uploadDate");// 将参数保存到对应person对象中Source person = new Source(0, name, type, uploadDate);SourceService service = new SourceService();// 创建业务逻辑层对象// 执行对应新增操作service.add(person);// 重定向 重新查询数据 并且跳转首页response.sendRedirect("source");}// 根据id查询对应数据protected void Modifysel(HttpServletRequest request, HttpServletResponseresponse)throws ServletException, IOException {// 获取所有的参数int id = Integer.parseInt(request.getParameter("id"));SourceService service = new SourceService();// 创建业务逻辑层对象// 根据id查询到单个对应数据Source sr = service.Modifysel(id);// 将数据保存到对应的request域中request.setAttribute("Source", sr);// 转发数据request.getRequestDispatcher("update.jsp").forward(request, response);//转发跳转页面}// 获取用户修改后的数据protected void Modify(HttpServletRequest request, HttpServletResponseresponse)throws ServletException, IOException {// 获取所有的参数 然后传入修改方法int id = Integer.parseInt(request.getParameter("id"));String name = request.getParameter("name");String type = request.getParameter("type");String uploadDate = request.getParameter("uploadDate");//和新增一样 保存到对应方法中Source sr = new Source(id, name, type, uploadDate);// 创建业务逻辑层对象SourceService service = new SourceService();// 调用修改方法service.Modify(sr);// 重定向 重新查询数据response.sendRedirect("source");}}

9.index.jsp

搜索
名称：
编号
名称
类型
上传时间
操作
${Source.id }
${Source.name }
${Source.type }
${Source.uploadDate }
删除修改
新增共条数据

10.add.jsp

名称
类型
上传时间

11.update.jsp

名称:
类型:
上传时间:

12.web.xml

SourceServletcom.ydhl.servlet.SourceServletSourceServlet/sourcesource

整体项目文件：

https://download.csdn.net/download/kai212/87724913https://download.csdn.net/download/kai212/87724913