servlet+jdbc实现微信公众号开发 源码下载

package com.eastnet.wechat.utils; 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.mysql.jdbc.StringUtils; public class OperatorUtil { /** * 获取账号绑定状态 * @param fromUserName * @return */ public String getBindStatus(String fromUserName){ Connection conn=null; PreparedStatement ps=null; ResultSet rs=null; conn=new DBCPConnection().getConnection(); String selectSql="select * from crm_stu_wechat where open_id='"+fromUserName+"'"; if(conn==null){ return "连接数据库失败"; }else{ try { ps=conn.prepareStatement(selectSql); rs=ps.executeQuery(); if(rs.next()){ return "用户名已绑定"; }else{ return "请输入\"用户名绑定\"+登录用户名 如:用户名绑定fangw"; } } catch (SQLException e) { return "查询数据库失败"; }finally{ closeConnection(conn,ps,rs); } } } public List getAllUser(){ Connection conn=null; List list=new ArrayList(); PreparedStatement ps=null; ResultSet rs=null; conn=new DBCPConnection().getConnection(); String selectSql="select * from qx_users "; if(conn==null){ return null; }else{ try { ps=conn.prepareStatement(selectSql); rs=ps.executeQuery(); while(rs.next()){ list.add(rs.getString("USERNAME")); } } catch (SQLException e) { return null; }finally{ closeConnection(conn,ps,rs); } } return list; } /** * 绑定账号 * @param fromUserName * @param userName * @return */ public String bindAccount(String fromUserName,String userName){ Connection conn=null; PreparedStatement ps=null; ResultSet rs=null; String result=getBindStatus(fromUserName); if("连接数据库失败".equals(result)||("查询数据库失败".equals(result))){ return "连接数据库失败"; }else if("用户名已绑定".equals(result)){ return "用户名已绑定，无需重复绑定"; } conn = new DBCPConnection().getConnection(); List list=getAllUser(); if(!list.contains(userName)){ return "用户名不存在,绑定失败"; } int i = 0; String sql = "insert into crm_stu_wechat (open_id,user_name) values(?,?)"; try { ps = conn.prepareStatement(sql); ps.setString(1, fromUserName); ps.setString(2, userName); i = ps.executeUpdate(); if(i>0){ return "用户名绑定成功"; }else{ return "用户名绑定失败"; } } catch (SQLException e) { return "用户名绑定失败，未知错误"; }finally{ closeConnection(conn,ps,rs); } } /** * 解除账号绑定 * @param fromUserName * @param userName * @return */ public String unBindAccount(String userName){ Connection conn=null; PreparedStatement ps=null; ResultSet rs=null; conn = new DBCPConnection().getConnection(); int i = 0; String sql=" delete from crm_stu_wechat where user_name= ?"; try { ps = conn.prepareStatement(sql); ps.setString(1, userName); i = ps.executeUpdate(); if(i>0){ return "用户名解除绑定成功"; }else{ return "该用户名不存在"; } } catch (SQLException e) { return "用户名解除绑定失败，未知错误"; }finally{ closeConnection(conn,ps,rs); } } /** * 行程添加 * @param fromUserName * @return */ public String addTravel(String fromUserName){ Connection conn=null; PreparedStatement ps=null; ResultSet rs=null; String sbStr=""; conn = new DBCPConnection().getConnection(); String selectSql="select * from crm_stu_wechat where open_id='"+fromUserName+"'"; if(conn==null){ return "连接数据库失败"; }else{ try { ps=conn.prepareStatement(selectSql); rs=ps.executeQuery(); if(rs.next()){ String userName=rs.getString("user_name"); String selectTravelSql="select * from crm_student where user_id='"+userName+"'"; Connection connTravel= new DBCPConnection().getConnection(); PreparedStatement psTravel=connTravel.prepareStatement(selectTravelSql); ResultSet rsTravel=psTravel.executeQuery(); if(rsTravel.next()){ String name=rsTravel.getString("id"); sbStr="点击添加行程"; return sbStr; }else{ return "您未注册，请在电脑端完成注册"; } }else{ return "用户名未绑定,请输入\"用户名绑定\"+登录用户名 如:用户名绑定fangw"; } } catch (SQLException e) { return "查询数据库失败"; }finally{ closeConnection(conn,ps,rs); } } } /** * 行程查看 * @param fromUserName * @return */ public String viewTravel(String fromUserName){ Connection conn=null; PreparedStatement ps=null; ResultSet rs=null; StringBuffer sb=new StringBuffer(); conn = new DBCPConnection().getConnection(); String selectSql="select * from crm_stu_wechat where open_id='"+fromUserName+"'"; if(conn==null){ return "连接数据库失败"; }else{ try { ps=conn.prepareStatement(selectSql); rs=ps.executeQuery(); if(rs.next()){ String userName=rs.getString("user_name"); String selectTravelSql="select * from crm_student_info where user_id='"+userName+"'"; Connection connTravel= new DBCPConnection().getConnection(); PreparedStatement psTravel=connTravel.prepareStatement(selectTravelSql); ResultSet rsTravel=psTravel.executeQuery(); while(rsTravel.next()){ sb.append("考试时间：").append(rsTravel.getString("exam_time")).append("\n"); sb.append("考试院校：").append(rsTravel.getString("exam_school")).append("\n"); sb.append("考试地点：").append(rsTravel.getString("exam_area")).append("\n"); sb.append("居住城市：").append(rsTravel.getString("live_city")).append("\n"); sb.append("\n"); sb.append("---------------------------------"); sb.append("\n"); } if((sb.toString()==null||("".equals(sb.toString())))){ return "暂无您的行程"; }else{ return sb.toString(); } }else{ return "用户名未绑定,请输入\"用户名绑定\"+登录用户名 如:用户名绑定fangw"; } } catch (SQLException e) { return "查询数据库失败"; }finally{ closeConnection(conn,ps,rs); } } } /** * 查看个人信息 * @return */ public String viewStuInfo(String fromUserName){ Connection conn=null; PreparedStatement ps=null; ResultSet rs=null; StringBuffer sb=new StringBuffer(); conn = new DBCPConnection().getConnection(); String selectSql="select * from crm_stu_wechat where open_id='"+fromUserName+"'"; if(conn==null){ return "连接数据库失败"; }else{ try { ps=conn.prepareStatement(selectSql); rs=ps.executeQuery(); if(rs.next()){ String userName=rs.getString("user_name"); String selectStuSql="select * from crm_student where user_id='"+userName+"'"; Connection connStu= new DBCPConnection().getConnection(); PreparedStatement psStu=connStu.prepareStatement(selectStuSql); ResultSet rsStu=psStu.executeQuery(); if(rsStu.next()){ sb.append("用户名:").append(rsStu.getString("user_id")).append("\t").append("\n"); sb.append("姓名:").append(rsStu.getString("stu_name")).append("\t").append("\n"); if("0".equals(rsStu.getString("stu_sex"))){ sb.append("性别:").append("男").append("\t").append("\n"); }else{ sb.append("性别:").append("女").append("\t").append(