Java连接mysql数据库，并在JSP页面实现对数据库表的增删改查 |
您所在的位置：网站首页 › jsp增删改查页面代码 › Java连接mysql数据库，并在JSP页面实现对数据库表的增删改查 |
Java连接mysql数据库，并在JSP页面实现对数据库表的增删改查
|
实现软件 Navicat Premium IntelliJ IDEA 2019.2 1.创建test数据库和表 student2 mysql-connector-java-5.1.9-bin.jar 链接：https://pan.baidu.com/s/1p-e9U2WUr4cPglXpoILOxA 提取码：g829 4.创建Dao类，负责数据库的连接与关闭 package czh;// import java.sql.*; public class Dao { public static Connection getConnection() throws SQLException { //不加useUnicode=true&characterEncoding=UTF-8"会造成中文乱码 String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8"; String username = "root"; String password = "123456"; Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url, username, password); System.out.println("数据库连接成功"); } catch(ClassNotFoundException e) { e.printStackTrace(); } return conn; } //关闭连接 public static void close(ResultSet rs, PreparedStatement ps, Connection conn) throws SQLException { try { rs.close(); ps.close(); conn.close(); } catch(SQLException e) { e.printStackTrace(); } } }5.创建实体类 Students public class Students { int sid; String name; public int getSid() { return sid; } public void setSid(int sid) { this.sid = sid; } public String getName() { return name; } public void setName(String name) { this.name = name; } //无参构造方法（必须有，没有系统会自己创建） public Students() { } //有参构造方法 public Students(int sid, String name) { this.sid = sid; this.name = name; } }6.创建数据库 增删改查 方法类 sqlDao 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 sqlDao { //查 public static String getNameById(int id) { Connection conn; String name = null; try { conn = Dao.getConnection(); PreparedStatement ps = conn.prepareStatement("select * from student2 where id = ?"); ps.setInt(1, id); //第一个参数指的是上面执行的sql语句中的第几个参数，第二个是要设置的值 ResultSet rs = ps.executeQuery(); while (rs.next()) { name = rs.getString("name"); } Dao.close(rs, ps, conn); } catch (SQLException e) { e.printStackTrace(); } return name; } //删 public static String delById(int id) { Connection conn; String whether=null; try { conn = Dao.getConnection(); PreparedStatement ps = conn.prepareStatement("delete from student2 where id = ?"); ps.setInt(1, id); ps.executeUpdate(); whether="删除成功"; } catch (SQLException e) { e.printStackTrace(); whether="删除失败"; } return whether; } //增 public static String insert(int id,String name) { Connection conn; String whether=null; try { conn = Dao.getConnection(); PreparedStatement ps = conn.prepareStatement("insert into student2 values (?,?)"); ps.setInt(1,id); ps.setString(2,name); ps.executeUpdate(); whether="插入成功"; } catch (SQLException e) { e.printStackTrace(); whether="插入失败"; } return whether; } //改 public static String update(int id,String name) { Connection conn; String whether=null; try { conn = Dao.getConnection(); PreparedStatement ps = conn.prepareStatement("update student2 set name=? where id=?"); ps.setString(1,name); ps.setInt(2,id); ps.executeUpdate(); whether="修改成功"; } catch (SQLException e) { e.printStackTrace(); whether="修改失败"; } return whether; } //查表中所有数据 //被static关键字修饰的方法或者变量不需要依赖于对象来进行访问，方便在没有创建对象的情况下来进行调用（方法/变量）。 public static List getAll() throws SQLException { Connection conn; conn = Dao.getConnection(); PreparedStatement ps = conn.prepareStatement("select * from student2"); ResultSet rs = ps.executeQuery(); List studentsList =new ArrayList(); while (rs.next()) { studentsList.add(new Students(rs.getInt("id"),rs.getString("name"))); } //遍历studentsList并输出到控制台 // for(Students student: studentsList){ // System.out.println(student.getSid()+student.getName()); // } Dao.close(rs, ps, conn); return studentsList; } }7.创建一个处理HTTP请求的Servlet类 sqlServlet （此处处理post请求，实现对客户端（浏览器）发送数据的读取，处理，发送） import java.io.IOException; import java.sql.SQLException; import java.util.List; public class sqlServlet extends javax.servlet.http.HttpServlet { public sqlServlet() { super(); } protected void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException { int sid = 0; //若此处不加if判断，当表单输入的值为空时，会报错 // 在使用Integer.parseInt()函数将该变量的值由字符串类型String转为整型int会报错， // 因空值" "是不能转为int类型的，只有包含数字之类的String类型才可以转 if (request.getParameter("id")!=null && !request.getParameter("id").equals("")) { sid = Integer.parseInt(request.getParameter("id")); //getParameter获取参数值 } String username=request.getParameter("name"); String submitButton = request.getParameter("submitButton"); switch (submitButton){ case "query": String name = sqlDao.getNameById(sid); request.setAttribute("name", name); //setAttribute设置属性值，后面NameResult.jsp页面会获取该值 request.getRequestDispatcher("/NameResult.jsp").forward(request, response); // 请求转发 break; case "delete": String whether1= sqlDao.delById(sid); request.setAttribute("whether1",whether1); request.getRequestDispatcher("/NameResult.jsp").forward(request, response); break; case "insert": String whether2= sqlDao.insert(sid,username); request.setAttribute("whether2",whether2); request.getRequestDispatcher("/NameResult.jsp").forward(request, response); break; case "update": String whether3= sqlDao.update(sid,username); request.setAttribute("whether3",whether3); request.getRequestDispatcher("/NameResult.jsp").forward(request, response); break; case "queryAll": try { List students= sqlDao.getAll(); request.setAttribute("studentsList", students); sqlDao.getAll(); } catch (SQLException e) { e.printStackTrace(); } request.getRequestDispatcher("/NameResult.jsp").forward(request, response); break; default: break; } } protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException { this.doPost(request, response); } }在web.xml中配置Servlet SqlS csh.sqlServlet SqlS /mySqls8.创建welcome.jsp页面 welcome 学号： 姓名：9.创建NameResult.jsp页面 NameResult 学号的同学名字为： 学号 姓名10.创建过滤器 CharacterFilter 解决中文乱码问题（可选） import java.io.*; import javax.servlet.*; import javax.servlet.http.*; public class CharacterFilter implements Filter { public void init(FilterConfig filterConfig) throws ServletException { } public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws IOException, ServletException { HttpServletRequest request = (HttpServletRequest) req; HttpServletResponse response = (HttpServletResponse) resp; // 拦截所有的请求 解决全站中文乱码 // 指定 request 和 response 的编码 request.setCharacterEncoding("utf-8"); // 只对消息体有效 response.setContentType("text/html;charset=utf-8"); // 在放行时 应该给目标资源一个request对象 让目标资源调用 // getParameter时调到我们写的getParameter // 对request进行包装 CharacterRequest characterRequest = new CharacterRequest(request); //放行 chain.doFilter(characterRequest, response); } public void destroy() { } } // 针对 request 对象进行包装 // 继承 默认包装类HttpServletRequestWrapper class CharacterRequest extends HttpServletRequestWrapper { public CharacterRequest(HttpServletRequest request) { super(request); } // 子类继承父类一定会覆写一些方法，此处用于重写getParamter()方法 public String getParameter(String name) { // 调用 被包装对象的getParameter()方法 获得请求参数 String value = super.getParameter(name); if (value == null) return null; // 判断请求方式 String method = super.getMethod(); if ("get".equalsIgnoreCase(method)) { try { value = new String(value.getBytes("iso-8859-1"), "utf-8"); } catch (UnsupportedEncodingException e) { throw new RuntimeException(e); } } // 解决乱码后返回结果 return value; } }在web.xml中配置过滤器 CharacterFilter csh.CharacterFilter CharacterFilter /*11.运行截图 必须创建一个package将编写的java文件放入，否则jsp页面无法实现正常访问 |
表 student2结构如下 
3.创建一个 java web 项目并导入 jar 包
【本文地址】
今日新闻 |
推荐新闻 |