/*
 * Created on 2007-4-16
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package cn.edu.zucc.news.model;

import java.sql.*;
import java.util.*;


/**
 * @author wengwy
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class WebUserDAO {
    private Connection conn = null;
    private Statement st = null;
    public WebUserDAO() {
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            conn = DriverManager
                    .getConnection(
                            "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=j2ee_news",
                            "sa", "sa");
            st = conn.createStatement();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public synchronized void release() {
        if (st != null) {
            try {
                st.close();
            } catch (Exception e) {
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
            }
        }

    }

    public synchronized List loadAllUsers() throws Exception{
        List result = new ArrayList();
        try {
            ResultSet rs = st.executeQuery("select * from tb_users");
            while (rs.next()) {
                WebUser user=new WebUser();
                user.setPwd(rs.getString("pwd"));
                user.setUserid(rs.getString("userid"));
                user.setUsername(rs.getString("username"));
                user.setUsertype(rs.getString("usertype"));
                result.add(user);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new Exception("數據庫查詢錯誤");
        }
        return result;
    }
    public synchronized void addUser(WebUser user) throws Exception{
        try {
            ResultSet rs = st.executeQuery("select * from tb_users where userid='"+user.getUserid()+"'");
            if (rs.next()) {
                throw new Exception("登錄號不能重復");
            }
            String sql="insert into tb_users(userid,pwd,username,usertype) values('"+user.getUserid()+"','"
            +user.getPwd()+"','"+user.getUsername()+"','"+user.getUsertype()+"')";
            st.execute(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new Exception("數據庫錯誤");
        }
        
    }
    public synchronized void saveUser(WebUser user) throws Exception{
        try {
            ResultSet rs = st.executeQuery("select * from tb_users where userid='"+user.getUserid()+"'");
            if (!rs.next()) {
                throw new Exception("用戶不存在");
            }
            String sql="update tb_users set pwd='"+user.getPwd()
                    +"',username='"+user.getUsername()+"',usertype='"
                    +user.getUsertype()+"' where userid='"+user.getUserid()+"'";
            st.execute(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new Exception("數據庫錯誤");
        }
        
    }
    public synchronized void delUser(String userid) throws Exception{
        try {
            ResultSet rs = st.executeQuery("select * from tb_users where userid='"+userid+"'");
            if (!rs.next()) {
                throw new Exception("用戶不存在");
            }
            String sql="delete from tb_users where userid='"+userid+"'";
            st.execute(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new Exception("數據庫錯誤");
        }
    }
    public synchronized WebUser readUser(String userid) throws Exception{
        WebUser result=null;
        try {
            ResultSet rs = st.executeQuery("select * from tb_users where userid='"+userid+"'");
            if (rs.next()) {
                result=new WebUser();
                result.setPwd(rs.getString("pwd"));
                result.setUserid(rs.getString("userid"));
                result.setUsername(rs.getString("username"));
                result.setUsertype(rs.getString("usertype"));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new Exception("數據庫查詢錯誤");
        }
        
        return result;
        
    }

    /**
     * @param user
     */
    public void checkuser(WebUser user) throws Exception{
        // TODO Auto-generated method stub
        WebUser result=this.readUser(user.getUserid());
        if(result==null) 
            throw new Exception("用戶不存在");
        if(!result.getPwd().equals(user.getPwd()))
            throw new Exception("密碼錯誤");
        user.setUsertype(result.getUsertype());
        user.setUsername(result.getUsername());
    }
    

}