servlet.java

用servlet做的多表的简单增删改查,数据库是用sql2

package webtier;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
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;

import bean.Bean;

import util.Tochinese;

public class Servlet extends HttpServlet{
	
	private int Mode;
    public void doPost(HttpServletRequest request,HttpServletResponse response){
    	Mode=Integer.parseInt(request.getParameter("Mode"));
    	switch(Mode){
    	case 1:this.insert(request,response);
    	       break;
    	case 2:this.query(request,response);
               break;
    	case 3:this.hobby_query(request,response);
               break;
    	case 4:this.delete(request,response);
               break;
    	case 5:this.queryone(request,response);
               break;
    	case 6:this.update(request,response);
               break;
    	}
    }
    
    public void doGet(HttpServletRequest request,HttpServletResponse response){
    	doPost(request,response);
    }
    public void insert(HttpServletRequest request,HttpServletResponse response){
    	String name=Tochinese.toChinese(request.getParameter("name"));
    	String password=Tochinese.toChinese(request.getParameter("password"));
    	String phone=request.getParameter("phone");
    	String sex=request.getParameter("sex");
    	String hobby[]=request.getParameterValues("hobby");
    	String sql1="insert into userinfo values('"+name+"','"+password+"','"+phone+"')";
    	String sql2="select max(id) as id from userinfo";
    	Bean b=new Bean();
    	b.executeUpdate(sql1);
    	HashMap hm=b.queryMap(sql2);
    	String user_id=(String)hm.get("id");
    	String sql3="insert into baseinfo values('"+sex+"','"+phone+"','"+user_id+"')";
    	b.executeUpdate(sql3);
    	List list=new ArrayList();
    	for(int i=0;i<hobby.length;++i){
    		String sql4="insert into relation values ('"+hobby[i]+"','"+user_id+"')";
    		list.add(sql4);
    	}
    	b.insertRelation(list);
    	RequestDispatcher dis=request.getRequestDispatcher("Hobby_servlet?Mode=2");
    	try {
			dis.forward(request, response);
		} catch (ServletException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    public void query(HttpServletRequest request,HttpServletResponse response){
    	String query_sql="select a.id,a.name,a.password,a.phone,b.sex " +
    			"from userinfo a,baseinfo b where a.id=b.user_id";
    	Bean b=new Bean();
    	List list=b.queryList(query_sql);
    	request.setAttribute("list", list);
    	RequestDispatcher dis=request.getRequestDispatcher("query.jsp");
    	try {
			dis.forward(request, response);
		} catch (ServletException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    public void hobby_query(HttpServletRequest request,HttpServletResponse response){
    	String id=request.getParameter("id");
    	String id_query_sql="select name from userinfo where id="+id;
    	String hobby_query_sql="select d.hobby from userinfo a,relation c,hobbyinfo d " +
    			        "where a.id=c.user_id and d.id=c.user_hobby_id and a.id="+id;
    	Bean b=new Bean();
    	List list=b.queryList(hobby_query_sql);
    	HashMap hm=b.queryMap(id_query_sql);
    	request.setAttribute("hm", hm);
    	request.setAttribute("list", list);
    	RequestDispatcher dis=request.getRequestDispatcher("hobby.jsp");
    	try {
			dis.forward(request, response);
		} catch (ServletException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    public void delete(HttpServletRequest request,HttpServletResponse response){
    	String id=request.getParameter("id");
    	String delete_sql1="delete from userinfo where id="+id;
    	String delete_sql2="delete from baseinfo where user_id="+id;
    	String delete_sql3="delete from relation where user_id="+id;
    	Bean b=new Bean();
    	b.executeUpdate(delete_sql1);
    	b.executeUpdate(delete_sql2);
    	b.executeUpdate(delete_sql3);
    	try {
			response.sendRedirect("Hobby_servlet?Mode=2");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    public void queryone(HttpServletRequest request,HttpServletResponse response){
    	String id=request.getParameter("id");
    	String query_sql="select a.id,a.name,a.password,a.phone,b.sex " +
		                "from userinfo a,baseinfo b where a.id=b.user_id  and b.user_id="+id;
    	String hobby_query_sql="select c.user_hobby_id from relation c,userinfo a where a.id=c.user_id and a.id="+id;
    	Bean b=new Bean();
    	List list=b.queryList(query_sql);
    	List list1=b.queryList(hobby_query_sql);
    	request.setAttribute("list", list);
    	request.setAttribute("list1", list1);
    	RequestDispatcher dis=request.getRequestDispatcher("queryone.jsp");
    	try {
			dis.forward(request, response);
		} catch (ServletException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    public void update(HttpServletRequest request,HttpServletResponse response){
    	String id=request.getParameter("id");
    	String name=request.getParameter("name");
    	String password=request.getParameter("password");
    	String phone=request.getParameter("phone");
    	String sex=request.getParameter("sex");
    	String hobby[]=request.getParameterValues("hobby");
    	String update_sql="update userinfo set name='"+name+"',password='"+password+"',phone='"+phone+"'where id="+id;
    	String update_sql2="update baseinfo set sex='"+sex+"',phone='"+phone+"' where user_id="+id;
    	String delete_sql="delete from relation where user_id="+id;
    	Bean b=new Bean();
    	b.executeUpdate(update_sql);
    	b.executeUpdate(update_sql2);
    	b.executeUpdate(delete_sql);
    	List list=new ArrayList();
		if(hobby!=null){
		for(int i=0;i<hobby.length;++i){
			String sql4="insert into relation values("+hobby[i]+","+id+")";
			list.add(sql4);
		 }
		}
		b.insertRelation(list);
		try {
			response.sendRedirect("Hobby_servlet?Mode=2");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
}