public class BaseDao {

    protected Connection con = null;
    protected PreparedStatement ps = null;
    protected ResultSet rs = null;
    
    
    private final static String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
       private final static String url="jdbc:sqlserver://192.168.16.19:1433;DatabaseName=BBS_Message";
       private static String user="sa";
       private static String password="";
       public void openConnection(){
           try {
            Class.forName(driver);
             if(con==null||con.isClosed()){
                 con=DriverManager.getConnection(url,user,password);
             }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
       }

    //获取连接
    protected void openConnection2(){
            try {
                Context ctx = new InitialContext();
                DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/Message");
                con = ds.getConnection();
            } catch (NamingException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }    
    
    //更新新据库
    public int executeUpdata(String sql, List<Object> list){        
        openConnection();
        try {
            ps = con.prepareStatement(sql);
            if(list == null)
                return ps.executeUpdate();
            int i = 1;
            for(Object obj:list){
                ps.setObject(i, obj);
                i++;
            }
            return ps.executeUpdate();
        } catch (SQLException e) {            
            e.printStackTrace();
        }finally{
            closeResource();
        }
        return 0;
    }
    
    //关闭流
    protected void closeResource(){        
    try {
        if(rs != null)
            rs.close();
        if(ps != null)
            ps.close();
        if(con != null)
            con.close();
        
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
}

public interface BBSMessageDao {
    //查询短信息
    public List<BBSMessage>  getMessagesByPage(Map<String,Object> elements);
    //更新短信息
    public int executeUpdate(String operate, Map<String,Object> elements, Map<String,Object> conditions);
    //获取短信条数
    public int getMsgsCount(String username);
}

public interface UserinfoDao {
    //查找用户
    public List<UserInfo> getUsers(Map<String,String> contitions);
    //添加或更新用户
    public int executeUpdate(String operate, UserInfo userinfo);
}

public class BBSMessageDaoImpl extends BaseDao implements BBSMessageDao {
    /*
     * 执行update操作
     * @param
     *     operate：执行插入、更新还是删除
     *     elements：需要插入或者更新的值集合，key对应column，value是插入或者更新的值
     *     conditions：update执行的条件集合，key对应column，value是条件值
     */
    public int executeUpdate(String operate, Map <String,Object>elements, Map <String,Object>conditions) {
        List<Object> list = null;
        String sql = null;
        String msgid= null ,username= null,title= null,msgcontent= null,state= null,sendto= null;
        Date msg_create_date=null;
        /*
         * 获取需要变更的值
         */
        if(elements!=null && elements.size()>0){
            msgid = (String)elements.get("msgid");
            username = (String)elements.get("username");
            title = (String)elements.get("title");
            msgcontent = (String)elements.get("msgcontent");
            state = (String)elements.get("state");
            sendto = (String)elements.get("sendto");
            msg_create_date = (Date)elements.get("msg_create_date");        
        }
        
        
        
        /*
         * 更新操作
         */
        if(operate.equals("update")){
            /*
             * 拼写更新sql
             */
            sql = "update msg set ";
            /*
             * 获取条件元素
             */
            String ctn_msgid = (String)conditions.get("msgid");
            String ctn_username = (String)conditions.get("username");
            String ctn_title = (String)conditions.get("title");
            String ctn_msgcontent = (String)conditions.get("msgcontent");
            String ctn_state = (String)conditions.get("state");
            String ctn_sendto = (String)conditions.get("sendto");
            Date ctn_msg_create_date = (Date)conditions.get("msg_create_date");
            /*
             * 写出sql更新语句
             */
            String and = "";
            sql += msgid!=null ? and + " msgid=? ":"";
            and = msgid!=null ? " and ":"";
            sql += username!=null ? and + " username=? ":"";
            and = username!=null ? " and ":"";
            sql += title!=null ? and + " title=‘" + title + "‘ ":"";
            and = title!=null ? " and ":"";
            sql += msgcontent!=null ? and + " msgcontent=? ":"";
            and = msgcontent!=null ? " and ":"";
            sql += sendto!=null ? and + " sendto=? ":"";
            and = sendto!=null ? " and ":"";
            sql += state!=null ? and + " state=? ":"";
            and = state!=null ? " and ":"";
            sql += msg_create_date != null ? and + " msg_create_date=? ":"";
            /*
             * 写出sql条件语句
             */    
            sql = sql + " where 1=1 ";
            sql += ctn_msgid != null ? " and msgid=? ":"";
            sql += ctn_username != null ? " and username=? ":"";
            sql += ctn_title != null ? " and title=‘" + title + "‘ ":"";
            sql += ctn_msgcontent != null ? " and msgcontent=? ":"";
            sql += ctn_sendto != null ? " and sendto=? ":"";
            sql += ctn_state != null ? " and state=? ":"";
            sql += ctn_msg_create_date != null ? " and msg_create_date=? ":"";
            
            list = new ArrayList<Object>();
            /*
             * 设置更新参数
             */
            if(msgid != null)
                list.add(msgid);
            if(username != null)
                list.add(username);
            if(title != null)
                list.add(title);
            if(msgcontent != null)
                list.add(msgcontent);
            if(sendto != null)
                list.add(sendto);
            if(state != null)
                list.add(state);
            if(msg_create_date != null)
                list.add(new Timestamp(msg_create_date.getTime()));
            /*
             * 设置条件参数
             */
            if(ctn_msgid != null)
                list.add(ctn_msgid);
            if(ctn_username != null)
                list.add(ctn_username);
            if(ctn_title != null)
                list.add(ctn_title);
            if(ctn_msgcontent != null)
                list.add(ctn_msgcontent);
            if(ctn_sendto != null)
                list.add(ctn_sendto);
            if(ctn_state != null)
                list.add(ctn_state);
            if(ctn_msg_create_date != null)
                list.add(new Timestamp(ctn_msg_create_date.getTime()));
        }
        /*
         * 添加操作
         */
        else if(operate.equals("insert")){
            sql = "insert into msg values(?,?,?,?,?,?,?)";
            list = new ArrayList<Object>();
            list.add(msgid);
            list.add(username);
            list.add(title);
            list.add(msgcontent);
            list.add(state);
            list.add(sendto);
            list.add(new Timestamp(msg_create_date.getTime()));
        }
        /*
         * 删除操作
         */
        else if(operate.equals("delete")){
            sql = "delete from msg where msgid = ?";
            list = new ArrayList<Object>();
            msgid = (String)conditions.get("msgid");
            list.add(msgid);
        }
        return executeUpdata(sql, list);
    }
    /*
     * 执行查询操作
     * @param
     *     elements：查询条件集合     
     */    
    public List<BBSMessage> getMessagesByPage(Map<String,Object> elements) {        
        /*
         * 获取查询语句中的条件参数
         */        
        Integer msgs_in_one_page = (Integer)elements.get("msgs_in_one_page");
        Integer page_no = (Integer)elements.get("page_no");
        String username = (String)elements.get("username");
        String msgid = (String)elements.get("msgid");
        String title = (String)elements.get("title");
        String msgcontent = (String)elements.get("msgcontent");
        String sendto = (String)elements.get("sendto");
        Integer state = (Integer)elements.get("state");
        Date msg_create_date = (Date)elements.get("msg_create_date");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");        
        /*
         * 拼写sql
         */
        String sql = null;        
        if(page_no == null){
            sql = "select * from msg where 1 = 1 ";
        }else{                    
            sql = "select * from" +
            "(select msg.*,rownum r from msg where 1=1 and " + 
            username + title + msgcontent + sendto + state + msg_create_date + 
            " and rownum <= " + msgs_in_one_page*page_no + ") " +
            "t where t.r >" + msgs_in_one_page*(page_no - 1);
        }
        /*
         * 编写条件语句
         */        
        sql += msgid!=null ? " and msgid=‘" + msgid + "‘ ":"";
        sql += username != null?" and username=‘" + username + "‘ ":"";
        sql += title!=null ? " and title=‘" + title + "‘ ":"";
        sql += msgcontent!=null ? " and msgcontent=‘" + msgcontent + "‘ ":"";
        sql += sendto!=null ? " and sendto=‘" + sendto + "‘ ":"";
        sql += state!=null ? " and state=" + state + " ":"";
        sql += msg_create_date!=null ? " and msg_create_date=‘" + sdf.format(msg_create_date) + "‘ ":"";    
        
        /*
         * 返回结果
         */
        List <BBSMessage>list = null;        
        openConnection();
        list = new ArrayList<BBSMessage>();
        try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                BBSMessage bms = new BBSMessage();
                bms.setMsgid(rs.getString("msgid"));
                bms.setTitle(rs.getString("title"));
                bms.setContent(rs.getString("msgcontent"));
                bms.setSendto(rs.getString("sendto"));
                bms.setState(rs.getInt("state"));
                bms.setDatetime(rs.getDate("msg_create_date"));
                bms.setUsername(rs.getString("username"));
                list.add(bms);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            closeResource();
        }
        return list;
    }

    public int getMsgsCount(String username) {        
        String always_true = " where 1=1 ";
        int count = 0;
        if(username == null){
            username = "";
        }else{
            username = " and username = ‘" + username + "‘";
        }    
        String sql = "select count(*) from msg " + always_true + username;
        
        openConnection();
        try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next())
                count = rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

}

public class UserinfoDaoImpl extends BaseDao implements UserinfoDao {
    
    //添加或更新用户
    public int executeUpdate(String operate, UserInfo userinfo) {
        List<Object> list = null;
        String sql = null;
        if(operate.equals("update")){
            sql = "update msg_userinfo set password=? where username = ‘" + userinfo.getUsername() + "‘";
            list = new ArrayList<Object>();
            list.add(userinfo.getPassword());
        }else if(operate.equals("insert")){
            sql = "insert into msg_userinfo values(?,?,?)";
            list = new ArrayList<Object>();
            list.add(userinfo.getUsername());
            list.add(userinfo.getPassword());
            list.add(userinfo.getEmail());
        }
        return executeUpdata(sql, list);
    }

    //查找用户
    public List<UserInfo> getUsers(Map<String,String> contitions) {
        String username = (String) contitions.get("username");
        String pwd = (String) contitions.get("pwd");
        String operate = (String)contitions.get("operate");
        String sql = "select * from msg_userinfo where 1=1 ";
        if(operate != null && operate.equals("except")){
            sql += username != null ? " and username != ‘" + username + "‘":"";
        }else{
            sql += username != null ? " and username = ‘" + username + "‘":"";
        }
        sql += pwd != null ? " and password = ‘" + pwd + "‘":"";
        openConnection();
        List<UserInfo> users = new ArrayList<UserInfo>();
        try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                UserInfo uf = new UserInfo();
                uf.setUsername(rs.getString("username"));
                uf.setPassword(rs.getString("password"));
                uf.setEmail(rs.getString("email"));
                users.add(uf);                
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            closeResource();
        }        
        return users;
    }
}

public class BBSMessage{

    private String msgid = null;//ID
    private String username = null;//发送方
    private String title = null;//标题
    private String content = null;//内容
    private int state = 0;//用户名
    private String sendto=null;//收件方
    private Date datetime = null;//发送时间
    public String getMsgid() {
        return msgid;
    }
    public void setMsgid(String msgid) {
        this.msgid = msgid;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
    public int getState() {
        return state;
    }
    public void setState(int state) {
        this.state = state;
    }
    public String getSendto() {
        return sendto;
    }
    public void setSendto(String sendto) {
        this.sendto = sendto;
    }
    public Date getDatetime() {
        return datetime;
    }
    public void setDatetime(Date datetime) {
        this.datetime = datetime;
    }
}

public class UserInfo{

    private String username = null;//用户名
    private String password = null;//密码
    private String email = null;//电子邮箱
    
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
}

<script type="text/javascript">
$(function(){
    $(".btn-reg").bind("click", function(){
        window.location.href = "register.jsp";
    });
});

function check(){
    var username = document.getElementById("username");
    var password = document.getElementById("password");
    var error = document.getElementById("error");
    if(username.value =http://www.mamicode.com/= ""){
        error.innerHTML="<font color=\"red\">用户名不能为空！</font>";
        username.focus();
        return false;
    }else if(password.value =http://www.mamicode.com/= ""){
        error.innerHTML="<font color=\"red\">密码不能为空！</font>";
        password.focus();                
        return false;
    }
    return true;
}
</script>
</head>
<body>
   <!--
     
    jsp_Writer(){
   } -->
   <%
     int i=5;
   %>
   <%
    i++;
   %>
   <%=i %>







<div id="loginTitle" class="png"></div>
<div id="loginForm" class="userForm png">
    <form method="post" name="loginform" action="UserServlet?action=login" onsubmit = "return check()">        
        <dl>            
            <div id="error">${error}</div>
            <dt>用户名：</dt>
            <dd><input type="text" name="username" /></dd>
            <dt>密　码：</dt>
            <dd><input type="password" name="password" /></dd>
        </dl>
        <div class="buttons">
            <input class="btn-login png" type="submit" name="submit" value=http://www.mamicode.com/" " /><input class="btn-reg png" type="button" name="register" value=http://www.mamicode.com/" " />
        </div>        

<script type="text/javascript">
$(function(){
    $(".btn-reg").bind("click", function(){
        window.location.href = "register.jsp";
    });
});

function check(){
    var username = document.getElementById("username");
    var password = document.getElementById("password");
    var error = document.getElementById("error");
    if(username.value =http://www.mamicode.com/= ""){
        error.innerHTML="<font color=\"red\">用户名不能为空！</font>";
        username.focus();
        return false;
    }else if(password.value =http://www.mamicode.com/= ""){
        error.innerHTML="<font color=\"red\">密码不能为空！</font>";
        password.focus();                
        return false;
    }
    return true;
}
</script>
</head>
<body>
   <!--
     
    jsp_Writer(){
   } -->
   <%
     int i=5;
   %>
   <%
    i++;
   %>
   <%=i %>







<div id="loginTitle" class="png"></div>
<div id="loginForm" class="userForm png">
    <form method="post" name="loginform" action="UserServlet?action=login" onsubmit = "return check()">        
        <dl>            
            <div id="error">${error}</div>
            <dt>用户名：</dt>
            <dd><input type="text" name="username" /></dd>
            <dt>密　码：</dt>
            <dd><input type="password" name="password" /></dd>
        </dl>
        <div class="buttons">
            <input class="btn-login png" type="submit" name="submit" value=http://www.mamicode.com/" " /><input class="btn-reg png" type="button" name="register" value=http://www.mamicode.com/" " />
        </div>        

 function check(){
            var title = document.getElementById("title");
            var content = document.getElementById("content");
            if(title.value =http://www.mamicode.com/= ""){
                alert("标题不能为空！");
                return false;
            }else if(content.value =http://www.mamicode.com/= ""){
                alert("内容不能为空！");
                return false;
            }
            return true;
    }
</script>
  
 <body>
 <form action="MsgServlet?action=send" method="post" onsubmit="return check()">
    <div id="main">
        <div class="mainbox">            
            <div class="menu">
                <span>当前用户：<a href=http://www.mamicode.com/"MsgServlet?action=list">${sessionScope.loginuser}</a></span>
                <span><a href=http://www.mamicode.com/"UserServlet?action=findUsers">发短消息</a></span>
                <span><a href=http://www.mamicode.com/"UserServlet?action=logout">退出</a></span>
            </div>
            <div class="content">
                <div class="message">
                    
                        <div class="tmenu">
                            <ul class="clearfix">
                                <li>
                                    发送给：<%String to=request.getParameter("sendto");request.setAttribute("sendto",to ); %>
                                    <select name="toUser">
                                    
                                           <c:forEach var="user" items="${users}">
                                           <c:choose>
                                             <c:when test="${user.username eq (sendto)}">
                                               <option selected="selected">${user.username}</option>
                                             </c:when>
                                             <c:when test="${user.username ne (sendto)}">
                                              <option>${user.username}</option>
                                             </c:when>
                                           </c:choose>
                                               
                                           </c:forEach>
                                       </select>
                                </li>                                
                                <li>标题：<input type="text" name="title" id="title"/></li>
                            </ul>
                        </div>
                        <div class="view">
                            <textarea name="content" id="content"></textarea>
                            <div class="send"><input type="submit" name="submit" value=http://www.mamicode.com/" " /></div>
                        </div>
                    
                </div>
            </div>
        </div>
    </div>
</form>
</body>
</html>