简单的增删查改

package com.myservlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
* Servlet implementation class CurdServlet
*/
public class CurdServlet extends HttpServlet {
private static final long serialVersionUID = 1L;

/**
* @see HttpServlet#HttpServlet()
*/
public CurdServlet() {
super();
// TODO Auto-generated constructor stub
}

/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
this.doPost(request, response);
}

/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("text/html");
//request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");

String curd=new String(request.getParameter("curd").getBytes("ISO-8859-1"),"utf-8");

if(curd.equals("add")){

String b=new String(request.getParameter("a").getBytes("ISO-8859-1"),"utf-8");
//String b= request.getParameter("a");
System.out.println(b);

//String useradd=new String(request.getParameter("useradd").getBytes("ISO-8859-1"),"utf-8"); 乱码
String useradd= request.getParameter("useradd");
String pwdadd=new String(request.getParameter("pwdadd").getBytes("ISO-8859-1"),"utf-8");
String interestadd=new String(request.getParameter("interestadd").getBytes("ISO-8859-1"),"utf-8");
String data=new String(request.getParameter("dataadd").getBytes("ISO-8859-1"),"utf-8");
int dataadd=Integer.parseInt(data);
System.out.println("%%%%%%%%%%"+useradd);
System.out.println("准备取con的值");
try{
ConnectionGet conget=new ConnectionGet();
Connection con=conget.getConnection();
if(con!=null){
System.out.println("取到con的值");
}
con.setAutoCommit(false);
PreparedStatement ps=null;
String str="select count(*) as Count from logintable where Tuser=?";
ps=con.prepareStatement(str);
ps.setString(1, useradd);
ResultSet rs=ps.executeQuery();
if(rs.next()){
int count=rs.getInt("Count");
System.out.println("count的值为"+count);
String jsonString="";
if(count==0){
String strSQLInsert="INSERT INTO logintable (Tuser,Tpassword,Tinterest,Tdata) VALUES (?,?,?,?)";

ps=con.prepareStatement(strSQLInsert);
//ps.setInt(1,);
ps.setString(1, useradd);
ps.setString(2, pwdadd);
ps.setString(3, interestadd);
ps.setInt(4, dataadd);
ps.executeUpdate();

con.commit();
// System.out.println("*************************"+strSQLInsert);
jsonString="{\"code\":100,\"name\":\"成功插入\"}";
}
else {
jsonString="{\"code\":-100,\"name\":\"插入不成功\"}";
}
PrintWriter out =response.getWriter();
out.write(jsonString);
out.flush();
}
System.out.println("是否要删除con");
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(con!=null){
con.close();
}



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

if(curd.equals("del")){
System.out.println("准备删除");
String tid=new String(request.getParameter("tid").getBytes("ISO-8859-1"),"utf-8");

try{
ConnectionGet conget=new ConnectionGet();
Connection con=conget.getConnection();
con.setAutoCommit(false);
PreparedStatement ps=null;
String sql="DELETE from logintable WHERE id="+tid;
ps=con.prepareStatement(sql);
ps.executeUpdate();
con.commit();
ps.close();
con.close();
RequestDispatcher dispatche =request.getRequestDispatcher("ShowServlet?code=100");
dispatche.forward(request, response);

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

if(curd.equals("update")){
System.out.println("准备修改");
String idud=new String(request.getParameter("idud").getBytes("ISO-8859-1"),"utf-8");
int id= Integer.parseInt(idud);
System.out.println(id);
String userud=new String(request.getParameter("userud").getBytes("ISO-8859-1"),"utf-8");
String pwdud=new String(request.getParameter("pwdud").getBytes("ISO-8859-1"),"utf-8");
String interestud=new String(request.getParameter("interestud").getBytes("ISO-8859-1"),"utf-8");
String data=new String(request.getParameter("dataud").getBytes("ISO-8859-1"),"utf-8");
int dataud=Integer.parseInt(data);

try{
ConnectionGet conget=new ConnectionGet();
Connection con=conget.getConnection();
if(con!=null){
System.out.println("取到con的值");
}
con.setAutoCommit(false);
PreparedStatement ps=null;
String str="select count(*) as Count from logintable where id=?";
ps=con.prepareStatement(str);
ps.setString(1, idud);
ResultSet rs=ps.executeQuery();
if(rs.next()){
int count=rs.getInt("Count");
System.out.println("count的值为"+count);
String jsonString="";
if(count==1){
String strSQLInsert="UPDATE logintable SET Tuser=? ,Tpassword=? ,Tinterest=? " +
",Tdata=? where id=?";

ps=con.prepareStatement(strSQLInsert);
//ps.setInt(1,);
ps.setString(1, userud);
ps.setString(2, pwdud);
ps.setString(3, interestud);
ps.setInt(4, dataud);
ps.setInt(5, id);
ps.executeUpdate();
con.commit();
jsonString="{\"code\":100,\"name\":\"成功插入\"}";
}
else {
jsonString="{\"code\":-100,\"name\":\"出入不成功\"}";
}
PrintWriter out =response.getWriter();
out.write(jsonString);
out.flush();
}
System.out.println("是否要删除con");
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(con!=null){
con.close();
}



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

if(curd.equals("selkey")){
System.out.println("准备关键字");

//String keyid=new String(request.getParameter("keyid").getBytes("ISO-8859-1"),"utf-8");
String keyid=request.getAttribute("keyid").toString();
System.out.println("打印关键字"+keyid);
String str="";
RequestDispatcher dispatche=null;
//String url = "jdbc:mysql://localhost:3306/testdb?user=root&password=root";
List<DataBean> list=new ArrayList<DataBean>();

try {
//Class.forName("com.mysql.jdbc.Driver");
//Connection conn = DriverManager.getConnection(url);
ConnectionGet conget=new ConnectionGet();
Connection conn=conget.getConnection();
// System.out.println(conn.isClosed());

conn.setAutoCommit(false);

PreparedStatement ps = null;
System.out.println(keyid+"***");
String sql1 = "select id,Tuser,Tpassword,Tinterest,Tdata from logintable where Tuser like '%"+keyid+"%' order by id ";
ps = conn.prepareStatement(sql1);
//ps.setString(1, keyid);

ResultSet rs = ps.executeQuery();

while(rs.next()){
DataBean db=new DataBean();
db.setId(rs.getInt("id"));
db.setUser(rs.getString("Tuser"));
db.setPassword(rs.getString("Tpassword"));
db.setInterest(rs.getString("Tinterest"));
db.setData(rs.getInt("Tdata"));
list.add(db);
}

if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

str="登陆成功";
request.setAttribute("end", str);
//request.setAttribute("user", user);
request.setAttribute("listData", list);
dispatche =request.getRequestDispatcher("loginmain.jsp");
dispatche.forward(request, response);

}
}

}