JavaEE实现数据库的连接（增、删、改、查）笔记

您所在的位置：网站首页 › 华为新款p50有耳机吗 › JavaEE实现数据库的连接（增、删、改、查）笔记

JavaEE实现数据库的连接（增、删、改、查）笔记

2024-01-22 08:59| 来源: 网络整理| 查看: 265

一、实现数据库的连接

package com.mashang.servlet.utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class JdbcUtil { /** * 获取数据库连接对象 * @return Connection */ public static Connection getConnection() { Connection connection = null; try { Class.forName("com.mysql.cj.jdbc.Driver"); connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/text?userSSL=false&serverTimezone=Asia/Shanghai", "root", "123456"); } catch (Exception e) { e.printStackTrace(); } return connection; } /** * 关闭连接 * @param rs * @param preStatement * @param conn */ public static void close(ResultSet rs, PreparedStatement preStatement, Connection conn) { try { // 判断传进来的对象是否存在 if (rs != null) { rs.close(); } if (preStatement != null) { preStatement.close(); } if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } // 这是相对于上面那个有特殊情况的参数 public static void close(PreparedStatement preStatement, Connection conn) { try { if (preStatement != null) { preStatement.close(); } if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } }

二、定义用户实体类

package com.mashang.servlet.entity; public class User { private int id; private String username; private String password; private String name; 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 getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", password='" + password + '\'' + ", name='" + name + '\'' + '}'; } }

三、数据库的增删改查操作接口

package com.mashang.servlet.dao; import com.mashang.servlet.entity.User; import com.mashang.servlet.utils.JdbcUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; //这个是用来对数据库进行一个数据处理的 public class UserDao { /** * 查询用户 * @return * @throws SQLException */ public List getUser() throws SQLException { //获取到连接 Connection connection = JdbcUtil.getConnection(); String sql = "select * from user"; PreparedStatement stmt = connection.prepareStatement(sql); //获取结果 ResultSet resultSet = stmt.executeQuery(); List userList = new ArrayList(); while (resultSet.next()) { User user = new User(); user.setId(resultSet.getInt("id")); user.setName(resultSet.getString("name")); user.setUsername(resultSet.getString("username")); user.setPassword(resultSet.getString("password")); userList.add(user); } //关闭数据库的连接 JdbcUtil.close(resultSet,stmt,connection); return userList; } /** * 新增用户 */ public boolean createUser(User user) { Connection connection = null; String sql = "INSERT INTO user(username,password,name ) VALUES (?,?,?);"; PreparedStatement stmt = null; int result =0; try { connection=JdbcUtil.getConnection(); stmt=connection.prepareStatement(sql); stmt.setString(1, user.getUsername()); stmt.setString(2, user.getPassword()); stmt.setString(3, user.getName()); result= stmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtil.close(stmt,connection); } if (result>0){ return true; }else { return false; } } /** * 更新用户 */ public boolean updateUser(User user) { Connection connection = null; String sql = "update user set username =?,password=?,name=? where id=?"; PreparedStatement stmt = null; int result =0; try { connection=JdbcUtil.getConnection(); stmt=connection.prepareStatement(sql); stmt.setString(1, user.getUsername()); stmt.setString(2, user.getPassword()); stmt.setString(3, user.getName()); stmt.setInt(4, user.getId()); result = stmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtil.close(stmt,connection); } if (result>0){ return true; }else { return false; } } /** * 删除用户 */ public boolean deleteUser(int id){ Connection connection = null; String sql = "delete from user where id=?;" + "alter table user "; PreparedStatement stmt = null; int result =0; try { connection=JdbcUtil.getConnection(); stmt=connection.prepareStatement(sql); stmt.setInt(1, id); result = stmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtil.close(stmt,connection); } if (result>0){ return true; }else { return false; } } }

1、对客户端发来的查询请求

package com.mashang.servlet.servlet; import com.mashang.servlet.dao.UserDao; import com.mashang.servlet.entity.User; import javax.annotation.Resource; 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.SQLException; import java.util.List; @WebServlet("/user") public class UserServlet extends HttpServlet { UserDao userDao = new UserDao(); @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { //编码 resp.setContentType("text/html;charset=utf-8"); try { List list = userDao.getUser(); resp.getWriter().print(list.toString()); } catch (SQLException e) { throw new RuntimeException(e); } } }

2、客户端发来的增加用户请求（JSON格式）

package com.mashang.servlet.servlet; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONObject; import com.mashang.servlet.dao.UserDao; import com.mashang.servlet.entity.User; import javax.servlet.ServletException; import javax.servlet.ServletInputStream; 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.HashMap; import java.util.Map; @WebServlet("/user/add") public class InsertUserServlet extends HttpServlet { UserDao userDao = new UserDao(); @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // super.doPost(req, resp); //使用Postman测试 //如果出现乱码 req.setCharacterEncoding("UTF-8"); // 接收客户端传来的数据 //问题1：当接收客户端的参数为JSON格式时，getParameter不能拿到参数 //解决问题1：第一步获取输入流(二进制) //解决问题1：第一步获取输入流(二进制) ServletInputStream inputStream = req.getInputStream(); //第二步：定义标识 int num = -1; byte[] b = new byte[1024 * 30]; // 第三步：定义可变字符串 StringBuilder stringBuilder = new StringBuilder(); while ((num=inputStream.read(b))!=-1){ //拼接字符串 stringBuilder.append(new String(b,0,num,"UTF-8") ); System.out.println(new String(b,0,num,"UTF-8")); } //把拼接的字符串转为json格式 JSONObject jsonObject = JSON.parseObject(stringBuilder.toString()); String username = (String) jsonObject.get("username"); String password = (String) jsonObject.get("password"); String name = (String) jsonObject.get("name"); // String username = req.getParameter("username"); // String password = req.getParameter("password"); // String name = req.getParameter("name"); User user = new User(); user.setUsername(username); user.setPassword(password); user.setName(name); boolean result = userDao.createUser(user); Mapmap = new HashMap(); resp.setHeader("Content-Type","application/json;charset=utf8"); if (result){ map.put("code",200); map.put("msg","新增成功"); resp.getWriter().print(map); }else { map.put("code",500); map.put("msg","新增失败"); resp.getWriter().print(map); } } }

3、客户端发来的删除用户请求（JSON格式）

package com.mashang.servlet.servlet; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONObject; import com.mashang.servlet.dao.UserDao; import javax.servlet.ServletException; import javax.servlet.ServletInputStream; 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.HashMap; import java.util.Map; @WebServlet("/user/delete") public class DeleteUserServlet extends HttpServlet { UserDao userDao = new UserDao(); @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // super.doPost(req, resp); ServletInputStream inputStream = req.getInputStream(); //第二步：定义标识 int num = -1; byte[] b = new byte[1024 * 30]; // 第三步：定义可变字符串 StringBuilder stringBuilder = new StringBuilder(); while ((num=inputStream.read(b))!=-1){ //拼接字符串 stringBuilder.append(new String(b,0,num,"UTF-8") ); System.out.println(new String(b,0,num,"UTF-8")); } //把拼接的字符串转为json格式 JSONObject jsonObject = JSON.parseObject(stringBuilder.toString()); int id = (int) jsonObject.get("id"); // String username = req.getParameter("username"); // String password = req.getParameter("password"); // String name = req.getParameter("name"); boolean result = userDao.deleteUser(id); Map map = new HashMap(); resp.setHeader("Content-Type","application/json;charset=utf8"); if (result){ map.put("code",200); map.put("msg","删除成功"); resp.getWriter().print(map); }else { map.put("code",500); map.put("msg","删除失败"); resp.getWriter().print(map); } } }

4、客户端发来的修改用户请求（JSON格式）

package com.mashang.servlet.servlet; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONObject; import com.mashang.servlet.dao.UserDao; import com.mashang.servlet.entity.User; import javax.servlet.ServletException; import javax.servlet.ServletInputStream; 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.HashMap; import java.util.Map; @WebServlet("/user/update") public class UpdateUserServlet extends HttpServlet { UserDao userDao = new UserDao(); @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // super.doPost(req, resp); ServletInputStream inputStream = req.getInputStream(); //第二步：定义标识 int num = -1; byte[] b = new byte[1024 * 30]; // 第三步：定义可变字符串 StringBuilder stringBuilder = new StringBuilder(); while ((num=inputStream.read(b))!=-1){ //拼接字符串 stringBuilder.append(new String(b,0,num,"UTF-8") ); System.out.println(new String(b,0,num,"UTF-8")); } //把拼接的字符串转为json格式 JSONObject jsonObject = JSON.parseObject(stringBuilder.toString()); String username = (String) jsonObject.get("username"); String password = (String) jsonObject.get("password"); String name = (String) jsonObject.get("name"); int id = (int) jsonObject.get("id"); // String username = req.getParameter("username"); // String password = req.getParameter("password"); // String name = req.getParameter("name"); User user = new User(); user.setUsername(username); user.setPassword(password); user.setName(name); user.setId(id); boolean result = userDao.updateUser(user); Map map = new HashMap(); resp.setHeader("Content-Type","application/json;charset=utf8"); if (result){ map.put("code",200); map.put("msg","修改成功"); resp.getWriter().print(map); }else { map.put("code",500); map.put("msg","修改失败"); resp.getWriter().print(map); } } }

【本文地址】

JavaEE实现数据库的连接（增、删、改、查）笔记

JavaEE实现数据库的连接（增、删、改、查）笔记

今日新闻

推荐新闻