package com.unis.dao;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.unis.vo.UserVo;
import java.util.ArrayList;
import java.sql.ResultSet;

/**
 *
 * <p>Title: </p>
 *
 * <p>Description: 基于用户管理的DAO层</p>
 *
 * <p>Copyright: Copyright (c) 2011</p>
 *
 * <p>Company: UNIS</p>
 *
 * @author ale
 *
 * @version 1.0
 */
public class UserDao {
    public UserDao() {
    }

    private Connection con = null;
    private Statement st = null;

    /**
     * 连接数据库
     */
    private void initDb() {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con = DriverManager.getConnection("jdbc:odbc:unis_20110421", "", "");
            st = con.createStatement();
        } catch (ClassNotFoundException ex) {
        } catch (SQLException ex) {
            /** @todo Handle this exception */
            ex.printStackTrace();
        }
    }

    /**
     * 关闭数据库
     */
    private void closeDb() {
        try {
            st.close();
            con.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 将用户信息插入数据库中
     * @param uservo UserVo
     * @return boolean
     */
    public int insertUser(UserVo uservo) {
        this.initDb();
        int tempFlag = 0;
        try {
            String sql = "INSERT INTO usertable (user_name,user_password,user_sex,user_describe) VALUES ('"+uservo.getName()+"','"+uservo.getPassword()+"','"+uservo.getSex()+"','"+uservo.getDescribe()+"')";
            tempFlag = st.executeUpdate(sql);
            System.out.println(tempFlag);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            this.closeDb();
        }
        return tempFlag;
    }

    /**
     * 查询数据库中所有用户信息
     * @return ArrayList
     */
    public ArrayList selectUserList() {
        this.initDb();
        ArrayList userList = null;
        try {
            String sql = "SELECT user_id,user_name FROM usertable";
            ResultSet rs = st.executeQuery(sql);
            userList = new ArrayList();
            while(rs.next())
            {
                UserVo uservo = new UserVo();
                uservo.setId(rs.getInt("user_id"));
                uservo.setName(rs.getString("user_name"));
                userList.add(uservo);
            }
        } catch (SQLException ex){
            ex.printStackTrace();
        }finally {
            this.closeDb();
        }
        return userList;
    }
}