用jsp和servlet实现增删改查

用jsp和servlet实现增删改查
最终实现的效果如下（并没有加上css修饰，就实现最简单功能）
在这里插入图片描述
先要事先新建一个jsp文件，和4个servler文件，4个是调用ajax方法给予回调函数，一个是sql方法函数我命名为SqlConnection

jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>

Mysql.java(增，全部代码)
package com.lyq;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**

• Servlet implementation class Mysql
  */
  @WebServlet("/Mysql")
  public class Mysql extends HttpServlet {
  private static final long serialVersionUID = -286431665770754787L;

  public void doGet(HttpServletRequest request, HttpServletResponse response)
  throws ServletException, IOException {
  this.doPost(request, response);
  }

  public void doPost(HttpServletRequest request, HttpServletResponse response)
  throws ServletException, IOException {

  PrintWriter out = response.getWriter();
  response.setContentType("text/html");
  response.setCharacterEncoding("UTF-8");

  // 获取表单参数
  request.setCharacterEncoding("UTF-8");
  String increase= new String(request.getParameter("increase").getBytes("utf-8"),"UTF-8");

  String sql2 = new String("INSERT INTO t_student (c_sn) VALUES ('"+increase+"')");
  ResultSet rs=SqlConnection.add(sql2);
  System.out.println(sql2);

  try{
  if(rs.next()) {
  out.println(1); //no
  }

  else {
  out.println(2);//yes

  }
  }catch(Exception e) {
  out.println(3);//no
  }

  out.flush();
  out.close();
  }

}

Mysql2（删，主要代码）
String strikeout= new String(request.getParameter("strikeout").getBytes("utf-8"),"UTF-8");

String sql3 = new String("delete from t_student where c_sn='"+strikeout+"'");
ResultSet rs=SqlConnection.delete(sql3);
1
2
3
4
Mysql3（改，主要代码）
String modify= new String(request.getParameter("modify").getBytes("utf-8"),"UTF-8");

String sql4 = new String("update t_student set c_sn='"+modify+"'where id='448'");
ResultSet rs=SqlConnection.update(sql4);
1
2
3
4
Mysql4（查，主要代码）
String inquire= new String(request.getParameter("inquire").getBytes("utf-8"),"UTF-8");

String sql = new String("select * from t_student where c_sn='"+ inquire +"'");
1
2
3
SqlConnection.java
package com.lyq;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlConnection {

public static final String url = "jdbc:mysql://localhost:3306/jwdb?useUnicode=true&characterEncoding=UTF-8";
public static final String username = "root";
public static final String password = "761027";

public static Connection conn =null;
public static Statement stmt = null;
public static ResultSet rs = null;

//安装驱动

public static void init() {
if(conn==null) {
try {

Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url,username,password);
if(conn!=null){
System.out.println("数据库连接正常");
}else{
System.out.println("数据库连接失败");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}

//查询方法
public static ResultSet query(String sql){
init();
try {
System.out.println(conn);
stmt = conn.createStatement();

rs = stmt.executeQuery(sql);

} catch (SQLException e) {
e.printStackTrace();
}
finally{

//relaseResourse();

}
return rs;
}

//释放资源
public static void relaseResourse(){
try {
if(rs!=null){
rs.close();
rs = null;
}
if(stmt!=null){
stmt.close();
stmt = null;
}
if(conn!=null){
conn.close();
conn=null;
}
} catch (Exception e) {
e.printStackTrace();
}

}
//增加数据操作
public static ResultSet add(String sql2){
init();
try {
stmt = conn.createStatement();

int result = stmt.executeUpdate(sql2);

if(result>0){ System.out.println("添加数据成功");
}else{
System.out.println("添加数据失败"); }

} catch (SQLException e) {
e.printStackTrace();
}finally{
//relaseResourse();
}
return rs;

}
//删除数据操作方法
public static ResultSet delete(String sql3){
init();
try {
stmt = conn.createStatement();
int result = stmt.executeUpdate(sql3);
//执行

if(result>0){
System.out.println("删除成功");
}else{
System.out.println("删除数据失败");

}

} catch (SQLException e) {
e.printStackTrace();
}finally{
//relaseResourse();
}
return rs;

}

//修改方法
public static ResultSet update(String sql4){
init();
try {
stmt = conn.createStatement();
//String sql = "update user set name = 'xiaoxiao' where id='6'";
int result = stmt.executeUpdate(sql4);
if(result>0){
System.out.println("修改成功");
}else{
System.out.println("修改数据失败");

}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//relaseResourse();
}
return rs;

}

}