userdao.java

一个图书管理系统

package com.DAO;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;

import com.database.DBAccess;
import com.exception.DataBaseException;
import com.VO.UserVO;

/**
 * @author 王军  
 * (逻辑层与界面层)
 */
public class UserDAO {
	/**
	 * @param args
	 */
//	public static void main(String[] args) {
//		UserManager userManager=new UserManager();
//		BufferedReader buff=new BufferedReader(new InputStreamReader(System.in));
//		
//////		//增加记录
////		UserVO user=new UserVO();
////		user[0]=new UserVO();
////		user.setUserName("crdddedc");
////		user.setUserPwd("234");
////		UserVO user1=new UserVO();
//////		user[0]=new UserVO();
////		user1.setUserName("wwwwwwwww");
////		user1.setUserPwd("whgh");
////		user[1]=new UserVO();
////		user[1].setUserName("r");
////		user[1].setUserPwd("dfgg34");
////		user[2]=new UserVO();
////		user[2].setUserName("cddccc");
////		user[2].setUserPwd("df34");
////		//	System.out.println(userManager.insert(user));
////		System.out.println(userManager.insert("useraa","1"));
////		String strYN="n";
////		do{
////			System.out.println("请输入用户名：");
////			String userName;
////			try {
////				userName = buff.readLine();
////				System.out.println("请输入用户密码：");
////				String userPwd=buff.readLine();
//////				UserVO user=new UserVO();
//////				user.setUserName(userName);
//////				user.setUserPwd(userPwd);
////				System.out.println(userManager.insert("wj","123"));
////				System.out.println("是否继续添加用户(Y/N)：");
////				strYN=buff.readLine();
////			} catch (IOException e) {
////			// TODO Auto-generated catch block
////				e.printStackTrace();
////			}			
////		}while(strYN.equalsIgnoreCase("y"));
//		
////		//修改记录
////		try {
////			System.out.println("请输入待修改的用户名：");
////			String strOld=buff.readLine();
////			System.out.println("请输入新的用户名：");
////			String strNew=buff.readLine();
////			System.out.println(userManager.update(user,user1));
////		} catch (IOException e) {
////			// TODO Auto-generated catch block
////			e.printStackTrace();
////		}
//		
//		//删除记录
////		try {
////			System.out.println("请输入待删除的用户名：");
////			String strOld=buff.readLine();
////			userManager.delete(strOld);
////		}catch (IOException e) {
////			// TODO Auto-generated catch block
////			e.printStackTrace();
////		}
//		
////		System.out.println(userManager.deleteAll());
//		
//	   //查找记录
//		ArrayList list=userManager.findAll();
//		for (int i=0;i<list.size();i++){
//			UserVO vo=(UserVO)list.get(i);
//			System.out.println("UserName="+vo.getUserName()+"\tUserPwd="+vo.getUserPwd());
//		}
//		
//	}

	/**
	 * @return   返回一个数组对象
	 * 功能 ：查找所有的记录
	 */
	public ArrayList findAll(){
		String strsql="select * from T_User";
		ArrayList list=new ArrayList();
		DBAccess db;
		try {
			db = DBAccess.getDBAccess();
			db.getConnection();
			ResultSet ret=db.query(strsql);
			try {
				while (ret.next()){
					UserVO user=new UserVO();
					user.setUserName(ret.getString(1));
					user.setUserPwd(ret.getString(2));
					list.add(user);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (DataBaseException e1) {
			e1.printStackTrace();
		}finally{
	//			 db.close();
		}
		return list;
	}
	
	/**
	 * @param name
	 * @return  返回一个对象
	 * 功能： 根据用户名查找满足条件的记录
	 */
	public UserVO find(String name){
		String strsql="select * from T_User where userName='"+name+"'";
		UserVO user=null;
		DBAccess db;
		try {
			db = DBAccess.getDBAccess();
			db.getConnection();
			ResultSet ret=db.query(strsql);
			try {
				while (ret.next()){
					user=new UserVO();
					user.setUserName(ret.getString(1));
					user.setUserPwd(ret.getString(2));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (DataBaseException e1) {
			e1.printStackTrace();
		}finally{
	//			 db.close();
		}
		return user;
	}
	
	/**
	 * @param use
	 * @return
	 * @throws DataBaseException 
	 */
	public UserVO find(UserVO use) throws DataBaseException{
		String strsql="select * from T_User where userName='"+use.getUserName()+"' and userPwd='"+use.getUserPwd()+"'";
		UserVO user=null;
		DBAccess db=null;
		try {
			try {
				db = DBAccess.getDBAccess();
			} catch (DataBaseException e2) {
				// TODO 自动生成 catch 块
				throw new DataBaseException(DataBaseException.CONN_ON_EEROR);
			}
			db.getConnection();
			ResultSet ret=null;
			try {
				ret = db.query(strsql);
			} catch (DataBaseException e1) {
				// TODO 自动生成 catch 块
				e1.printStackTrace();
			}
			try {
				while (ret.next()){
					user=new UserVO();
					user.setUserName(ret.getString(1));
					user.setUserPwd(ret.getString(2));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}finally{
	//			 db.close();
		}
		return user;
	}
	
	/**
	 * @param oldName (待修改的用户名)
	 * @param newName （新用户名）
	 * @return   (修改失败返回0)
	 * 功能： 修改操作 (一次修改一条记录)
	 */
	public int update(String oldName,String newName){
		int flag=0;
		String sql="update T_User set userName='"+newName+"' where userName='"+oldName+"'";
	//	System.out.println(sql);
		DBAccess db;
		try {
			db = DBAccess.getDBAccess();
			db.getConnection();		
			flag=db.update(sql);
		} catch (DataBaseException e) {
			e.printStackTrace();
		}finally{
			//	db.close();
		}
		return flag;
	}
	
	/**
	 * @param userOld  (UserVO老对象)
	 * @param userNew （UserVO新对象）
	 * @return  (修改失败返回0) 
	 * 功能： 修改操作 (一次修改一条记录)
	 */
	public int update(UserVO userOld,UserVO userNew){
		int flag=0;
		String sql="update T_User set userName='"
			+userNew.getUserName()+"',userPwd='"+userNew.getUserPwd()+
			"' where userName='"+userOld.getUserName()+"' and userPwd='"+userOld.getUserPwd()+"'";
//		System.out.println(sql);
		try {
			DBAccess db=DBAccess.getDBAccess();
			flag=db.update(sql);
		} catch (DataBaseException e) {
			e.printStackTrace();
		}		
		return flag;
	}
	
 
	/**
	 * @param user  
	 * @return  int (添加失败返回0)
	 * 功能： 插入操作 (一次插入一条记录)
	 */
	public int insert(UserVO user){
		int flag=0;
		String sql="insert into T_User values('"+user.getUserName()+"','"+user.getUserPwd()+"')";
//		System.out.println(sql);
		DBAccess db;
		try {
			db=DBAccess.getDBAccess();
			db.getConnection();
			flag=db.update(sql);
		} catch (DataBaseException e) {
			e.printStackTrace();
		}finally{
		//	db.close();
		}
		return flag;   
	}
	
	/**
	 * @param name (用户名)
	 * @param pwd  （密码）
	 * @return  int  (添加失败返回0)
	 * 功能： 插入操作  (调用存储过程)
	 * @throws SQLException 
	 */
	public int insert(String name,String pwd) {
		int flag=0;
//		int retunVal=0;
		DBAccess db;
		try {
			db=DBAccess.getDBAccess();
			Connection con=db.getConnection();
			CallableStatement cstmt=con.prepareCall("{?=call pro_Insert(?,?)}");
			//注册返回值类型
			cstmt.registerOutParameter(1,Types.INTEGER);
			cstmt.setString(2,name);
			cstmt.setString(3,pwd);
			cstmt.execute();
			//得到返回值
			flag=cstmt.getInt(1);
			cstmt.close();
//			if(retunVal==0){
//				System.out.println("插入失败，用户名相同或为空！");
//			}else{
//				System.out.println("插入成功");
//				flag=retunVal;
//			}
		} catch (DataBaseException e) {
			e.printStackTrace();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
		//	db.close();
		}
		return flag;
	}

	/**
	 * @param user (数组)
	 * @return   (添加失败返回0)
	 * @throws DataBaseException 
	 * 功能：插入操作 (一次性插入多条记录)
	 */
	public int insertAll(UserVO[] user) throws DataBaseException  {		
		int flag=0;
		//测试执行的时间
//		long exeTime=System.currentTimeMillis();
		try {
			DBAccess db=DBAccess.getDBAccess();
			Connection con = db.getConnection();
			//预编译语句
			PreparedStatement prepSt=con.prepareStatement("insert into T_User values(?,?)");
			for (int i=0;i<user.length;i++){
				//参数设定
				prepSt.setString(1,user[i].getUserName());
				prepSt.setString(2,user[i].getUserPwd());
			//	prepSt.addBatch();
				prepSt.execute();
			}
	//		prepSt.executeBatch();
			prepSt.close();
		} catch(SQLException e){
			throw new DataBaseException(DataBaseException.INSERT_ON_EEROR);
		}
//			exeTime=System.currentTimeMillis() -exeTime;
//			System.out.println("执行时间为："+exeTime+"(ms)");
		return flag;
	}
	

	/**
	 * @return  (删除失败返回0)
	 * 功能：删除操作   (删除所有记录，请慎用！)
	 */
	public int deleteAll(){
		int flag=0;
		String sql="delete from T_User";
//		System.out.println(sql);
		DBAccess db;
		try {
			db = DBAccess.getDBAccess();
			db.getConnection();		
			flag=db.update(sql);
		} catch (DataBaseException e) {
			e.printStackTrace();
		}finally{
			//	db.close();
		}

		return flag;
	}
	
	/**
	 * @param oldName （用户名）
	 * @return  (删除失败返回0)
	 *  功能：删除操作   (删除满足条件的记录)
	 */
	public int delete(String name){
		int flag=0;
		String sql="delete from T_User "+" where userName='"+name+"'";
//		System.out.println(sql);
		DBAccess db;
		try {
			db = DBAccess.getDBAccess();
			db.getConnection();		
			flag=db.update(sql);
		} catch (DataBaseException e) {
			e.printStackTrace();
		}finally{
			//	db.close();
		}
		
		return flag;
	}
}