package oracleFactory; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.Calendar; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class TestWX extends HttpServlet{ private String url; private String user; private String password; private String driverClass; public void init() throws ServletException { driverClass = "oracle.jdbc.driver.OracleDriver"; try { Class.forName(driverClass); } catch (ClassNotFoundException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } url = "jdbc:oracle:thin:@IP:1521:sas"; user = "XXX"; password = "XXX"; } public void doGet (HttpServletRequest req,HttpServletResponse resp) throws IOException,ServletException{ Connection conn = null; Statement stmt = null; ResultSet rs = null; req.setCharacterEncoding("gb2312"); resp.setContentType("text/html;charset=gb2312"); PrintWriter out = resp.getWriter(); Calendar calendar = Calendar.getInstance(); SimpleDateFormat sd1 = new SimpleDateFormat("yyyy-MM-dd 00:00:00"); SimpleDateFormat sd2 = new SimpleDateFormat("yyyy-MM-dd 23:59:59"); String today = sd2.format(calendar.getTime()); calendar.add(Calendar.DATE, -7); String lastMon = sd1.format(calendar.getTime()); System.out.println(lastMon); System.out.println(today); try { conn = DriverManager.getConnection(url, user, password); stmt = conn.createStatement(); String sql = "select * from WX_RUN_USER a,WX_RUN_USER_STATE b where a.openid=b.wx_user_id and a.sex=2 and a.last_mod_time between " + "TO_DATE('"+lastMon+"', 'YYYY-MM-DD HH24:Mi:ss') and TO_DATE('"+today+"', 'YYYY-MM-DD HH24:Mi:ss')"; System.out.println(sql); rs=stmt.executeQuery(sql); out.println("<html><head><title>微信接口信息</title></head><body>"); out.println("<table border=1 align=center><caption>微信接口信息</caption>"); out.println("<tr><th>openid</th><th>昵称</th><th>头像地址</th><th>绑定方式</th><th>绑定号码</th><th>创建时间</th><th>最后修改时间</th><th>账号状态</th></tr>"); while (rs.next()) { out.println("<tr>"); out.println("<td>"+rs.getString("openid")+"</td>"); out.println("<td>"+rs.getString("nickname")+"</td>"); out.println("<td><img src=\""+rs.getString("headimgurl")+"\" width=300/></td>"); String bind_type=rs.getString("bind_type"); int by = Integer.valueOf(bind_type).intValue(); switch (by) { case 0: bind_type="手机号"; break; case 1: bind_type="固话"; break; default: bind_type="宽带账号"; break; } out.println("<td>"+bind_type+"</td>"); out.println("<td>"+rs.getString("nbr")+"</td>"); out.println("<td>"+rs.getString("create_time")+"</td>"); out.println("<td>"+rs.getString("last_mod_time")+"</td>"); String acc_status = rs.getString("acc_status"); int status = Integer.valueOf(acc_status).intValue(); switch (status) { case 0: bind_type="取消关注"; break; case 2: bind_type="关注且绑定"; break; default: bind_type="关注未绑定"; break; } out.println("<td>"+bind_type+"</td>"); out.println("</tr>"); } out.println("</tr></table></body></html>"); out.println("success"); out.close(); rs.close();
stmt.close(); conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }