userdao.java

Eclips3.2 + mysql5.5 用Struts2编写的图书管理系统

package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.bean.Tuser;
import com.db.DBHelper;

public class UserDAO {
	/**
	 * 
	 * 取得所有用户
	 */
	public List<Tuser> getAllUser() {
		List<Tuser> list = new ArrayList<Tuser>();
		String sql = "SELECT * FROM T_USER";
		Statement stat = null;
		ResultSet rs = null;
		Connection conn = new DBHelper().getConn();
		try {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				Tuser tuser = new Tuser();
				tuser.setUserid(rs.getInt(1));
				tuser.setUsername(rs.getString(2));
				tuser.setUseraccount(rs.getString(3));
				tuser.setUserpwd(rs.getString(4));
				list.add(tuser);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (stat != null)
					stat.close();
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 
	 * 根据ID取得对应用户
	 */
	public Tuser getUser(int userid) {
		Tuser tuser = new Tuser();
		String sql = "SELECT * FROM T_USER WHERE user_id=?";
		PreparedStatement pstat = null;
		ResultSet rs = null;
		Connection conn = new DBHelper().getConn();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, userid);
			rs = pstat.executeQuery();
			while (rs.next()) {
				tuser.setUserid(rs.getInt(1));
				tuser.setUsername(rs.getString(2));
				tuser.setUseraccount(rs.getString(3));
				tuser.setUserpwd(rs.getString(4));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return tuser;
	}
	/**
	 * 
	 * 验证用户登录
	 */
	public String checkAccount(String account, String password) {
		String sql = "SELECT USER_ID FROM T_USER WHERE USER_ACCOUNT=? AND USER_PWD=?";
		PreparedStatement pstat = null;
		ResultSet rs = null;
		Connection conn = new DBHelper().getConn();
		String userid = null;
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, account);
			pstat.setString(2, password);
			rs = pstat.executeQuery();
			while (rs.next()) {
				userid = rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstat != null)
					pstat.close();
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return userid;

	}

	public static void main(String[] args) {
//		Tuser tuser = new UserDAO().getUser(3);
//		System.out.println("*****************************");
//		System.out.println("Tuserid = :" + tuser.getUserid());
//		System.out.println("Tusername = :" + tuser.getUsername());
//		System.out.println("Tuseraccount = :" + tuser.getUseraccount());
//		System.out.println("Tuserpwd = :" + tuser.getUserpwd());
		UserDAO userDAO = new UserDAO();
		System.out.println("UserDAO.main()" + userDAO.checkAccount("bbbb", "bbbb"));
	}
}