基于jsp+mysql的JSP家教服务平台

package com.soft.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.soft.Bean.LessionBean; import com.soft.Bean.OrderBean; import com.soft.Bean.TeacherBean; public class TeacherDB { private Connection conn=null; private PreparedStatement ps=null; private ResultSet rs=null; private int pageSize=4;//一页显示几条记录 private int rowCount=0;//共有几条记录(查表) private int pageCount=0;//共有几页(计算) public int getPageCount(){//得到总页数 conn=Conn.getConnect(); String sql="select count(*) from teacher"; try { ps=conn.prepareStatement(sql); rs=ps.executeQuery(); if(rs.next()){ rowCount=rs.getInt(1); } //计算pageCount,这里的算法很多，可以自己设计 if(rowCount%pageSize==0){ pageCount=rowCount/pageSize; }else{ pageCount=rowCount/pageSize+1; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.close(); } return pageCount; } public ArrayList getTeacherPage(int pageNow){//------分页给定页数的数据------------ ArrayList list=new ArrayList(); conn=Conn.getConnect(); String sql="select * from teacher ORDER BY create_time DESC limit ?,?"; try { ps=conn.prepareStatement(sql); ps.setInt(1,4*pageNow-4); ps.setInt(2,pageSize); rs=ps.executeQuery(); while(rs.next()){ TeacherBean bean=new TeacherBean(); bean.setId(rs.getInt("id")); bean.setU_id(rs.getInt("u_id")); bean.setPid(rs.getString("pid")); bean.setImages(rs.getString("images")); bean.setT_name(rs.getString("t_name")); bean.setSex(rs.getString("sex")); bean.setAge(rs.getString("age")); bean.setNationality(rs.getString("nationality")); bean.setTell(rs.getString("tell")); bean.setEmail(rs.getString("email")); bean.setMotto(rs.getString("motto")); bean.setIntroduce(rs.getString("introduce")); bean.setGraduate_school(rs.getString("graduate_school")); bean.setSpecialty(rs.getString("specialty")); bean.setEducation(rs.getString("education")); bean.setAddress(rs.getString("address")); bean.setAuthentication(rs.getString("authentication")); bean.setExperience(rs.getString("experience")); bean.setMoney(rs.getString("money")); bean.setTeach_time(rs.getString("teach_time")); bean.setSubject(rs.getString("subject"));//得到所查询的科目 bean.setArea(rs.getString("area"));//得到授课区域 bean.setCreate_time(rs.getString("create_time")); bean.setImageC(rs.getString("imageC")); bean.setImageE(rs.getString("imageE")); list.add(bean); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.close(); } return list; } //搜索教员 public ArrayList SearchTeacher(String s_where){ ArrayList list=new ArrayList(); conn=Conn.getConnect(); String sql="select * from teacher where id is not null and authentication!='0' "+s_where+" order by create_time DESC"; try { ps=conn.prepareStatement(sql); rs=ps.executeQuery(); while(rs.next()){ TeacherBean bean=new TeacherBean(); bean.setId(rs.getInt("id")); bean.setU_id(rs.getInt("u_id")); bean.setPid(rs.getString("pid")); bean.setImages(rs.getString("images")); bean.setT_name(rs.getString("t_name")); bean.setSex(rs.getString("sex")); bean.setAge(rs.getString("age")); bean.setNationality(rs.getString("nationality")); bean.setTell(rs.getString("tell")); bean.setEmail(rs.getString("email")); bean.setMotto(rs.getString("motto")); bean.setIntroduce(rs.getString("introduce")); bean.setGraduate_school(rs.getString("graduate_school")); bean.setSpecialty(rs.getString("specialty")); bean.setEducation(rs.getString("education")); bean.setAddress(rs.getString("address")); bean.setAuthentication(rs.getString("authentication")); bean.setExperience(rs.getString("experience")); bean.setMoney(rs.getString("money")); bean.setTeach_time(rs.getString("teach_time")); bean.setSubject(rs.getString("subject"));//得到所查询的科目 bean.setArea(rs.getString("area"));//得到授课区域 bean.setCreate_time(rs.getString("create_time")); list.add(bean); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.close(); } return list; } //查询所授科目 public String subject(String tell){ conn=Conn.getConnect(); String tt=""; String sql="select grade,subject from lesson where tell=?"; String grade="";String subject=""; try { ps=conn.prepareStatement(sql); ps.setString(1,tell); rs=ps.executeQuery(); if(rs.next()){ while(rs.next()){ grade=rs.getString(1); subject=rs.getString(2); tt=tt+grade+subject+"|"; } }else{ tt="暂未设置"; } sql="update teacher set subject='"+tt+"' where tell="+tell; ps=conn.prepareStatement(sql); ps.executeUpdate(); System.out.println(tt); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return tt; } //查询教授区域和地址 public List<String> getAddress(String tell){ List<String> teacher = new ArrayList<String>();// 利用ArrayList类实例化List集合 conn=Conn.getConnect(); System.out.println("===========查询授课区域=============="); String sql="select area,address from teacher where tell='"+tell+"'"; try { ps=conn.prepareStatement(sql); rs=ps.executeQuery(); if(rs.next()){ System.out.println(rs.getString("area")); String area=rs.getString("area"); String address=rs.getString("address"); teacher.add(area); teacher.add(address); }else{ return null; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.close(); } return teacher; } //修改教授区域和地址 public boolean UpdateAddress(String area,String address,String tell){ boolean tt=false; conn=Conn.getConnect(); String sql="update teacher set area='"+area+"',address='"+address+"' where tell='"+tell+"'"; try { ps=conn.prepareStatement(sql); int i=ps.executeUpdate(); if(i>0){ tt=true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return tt; } //查询可授课表 public ArrayList getLession(String tell){ conn=Conn.getConnect(); ArrayList lesson=new ArrayList(); String sql="select id,grade,subject from lesson where tell='"+tell+"' ORDER BY id DESC"; try { ps=conn.prepareStatement(sql); rs=ps.executeQuery(); while(rs.next()){ LessionBean lb=new LessionBean(); lb.setId(rs.getInt(1)); lb.setGrade(rs.getString(2)); lb.setSubject(rs.getString(3)); lesson.add(lb); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.close(); } return lesson; } //添加可授课表 public boolean setLession(String tell,String grade,String subject){ boolean tt=false; conn=Conn.getConnect(); String sql="insert into lesson(tell,grade,subject)values('"+tell+"','"+grade+"','"+subject+"')"; try { ps=conn.prepareStatement(sql); int i=ps.executeUpdate(); if(i>0){ tt=true; subject(tell);//添加成功及时插入teacher表 } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return tt; } //删除授课信息 public boolean dellLession(String id,String tell){ boolean tt=false; conn=Conn.getConnect(); String sql="delete from lesson where id="+id; try { ps=conn.prepareStatement(sql); int i=ps.executeUpdate(); if(i>0){ tt=true; subject(tell);//添加成功及时�