Servlet连接数据库及日期格式转换

浏览数：19 / 时间：2015年06月12日

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();
            
            stmt.close();
            conn.close();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        
    }
}