userdaomysqlimpl.java

这适合利用java开发struts程序的初学者借鉴的代码

package com.struts.usermgr.dao;

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

import com.struts.usermgr.User.User;
import com.struts.usermgr.utils.DB;

public class UserDaoMysqlImpl implements UserDao {

	public void addUser(Connection conn, User user) {
		String sql = "insert into t_user(userId , userName ,password , contactTel ,email , createDate)"
					+"values(?,?,?,?,?,?)" ;
		PreparedStatement ps = null ;
		try{
			ps = conn.prepareStatement(sql) ;
			ps.setString(1,user.getUserId()) ;
			ps.setString(2, user.getUserName()) ;
			ps.setString(3, user.getPassword()) ;
			ps.setString(4, user.getContactTel()) ;
			ps.setString(5, user.getEmail()) ;
			ps.setTimestamp(6, new Timestamp(user.getCreateDate().getTime())); ///获取用户的当前的时间
			ps.executeUpdate() ;
		}catch(SQLException e ){
			e.printStackTrace() ;
		}finally{
			DB.closeStmt(ps) ;
		}

	}

	public void deleteUsers(Connection conn, String[] userIdList) {
		StringBuffer sbfSql = new StringBuffer() ; //线程安全的字符可变序列
		for(int i = 0 ; i <userIdList.length ; i ++){
			sbfSql.append("'")
			.append(userIdList[i])
			.append("'")
			.append(",") ;
		}
		
		String sql = "delete from t_user where userId in(" + sbfSql.substring(0, sbfSql.length()-1)+")" ;
		Statement stmt = null ;
		try {
			stmt = conn.createStatement() ;
			stmt.executeUpdate(sql) ;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DB.closeStmt(stmt) ;
			DB.closeConnection(conn) ;
		//	stmt.close() ;
		//	conn.close() ;
		}
		
	}

	public List findAllUserList() {
		String sql = "select * from t_user where userId <> 'root' order by userId" ;
		Connection conn = null ;
		Statement stmt = null  ;
		ResultSet rs = null ;
		List userList = new ArrayList() ;
	
		try {
			conn = DB.getConnection();
			stmt = conn.createStatement() ;
			rs = stmt.executeQuery(sql) ;
			
			while(rs.next()){
				User user = new User() ;
				user.setUserId(rs.getString("userId")) ;
				user.setUserName(rs.getString("userName"));
				user.setPassword(rs.getString("password")) ;
				user.setContactTel(rs.getString("contactTel") );
				user.setEmail(rs.getString("email")) ;
				user.setCreateDate(rs.getTimestamp("createDate"));
				userList.add(user) ;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DB.closeRs(rs) ;
			DB.closeStmt(stmt) ;
			DB.closeConnection(conn) ;
		}
		return userList;
	}

	public User findUserById(String userId) {
		String sql  = "select * from t_user where userId = ?" ;
		Connection conn = null;
		PreparedStatement ps = null ;
		ResultSet rs =null ;
		User user = new User() ;

		try {
			conn = DB.getConnection() ;
			ps = conn.prepareStatement(sql);
			ps.setString(1, userId) ;
			rs = ps.executeQuery() ;
			if(rs.next()){
				user.setUserId(rs.getString("userId")) ;
				user.setUserName(rs.getString("userName"));
				user.setPassword(rs.getString("password"));
				user.setContactTel(rs.getString("contactTel"));
				user.setEmail(rs.getString("email"));
				user.setCreateDate(rs.getTimestamp("createDate"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DB.closeRs(rs) ;
			DB.closeStmt(ps);
			DB.closeConnection(conn) ;
		}
		
		return user;
	}

	public void modifyUser(Connection conn, User user) {
		String sql = "update t_user set userName=?,password=? , contactTel=?, email= ? where userId =?";
		PreparedStatement pstmt = null ;
		try {
			pstmt = conn.prepareStatement(sql) ;
			pstmt.setString(1, user.getUserName()) ;
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getContactTel());
			pstmt.setString(4, user.getEmail()) ;
			pstmt.setString(5, user.getUserId());
			pstmt.executeUpdate() ;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DB.closeStmt(pstmt);
		}
	}

}