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

一、实现数据库的连接

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;}@Overridepublic 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();@Overrideprotected 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();@Overrideprotected 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();@Overrideprotected 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();@Overrideprotected 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);}}}