userinfodao.java

模拟的土豆网视频网站

package cn.myvideosite.data.model.dao;

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 cn.myvideosite.data.model.bean.UserInfo;

public class UserInfoDAO extends BaseDAO{
	   /**
	    * 用户信息表的保存方法
	    * @param userinfo
	    * @return
	    */
       public static UserInfo save(UserInfo userinfo){
    	  PreparedStatement pstmt=null;
    	  String sql="insert into userinfo(nickName,sex,birthday,place,age,vocation,personalSpace,lastTime,scoreNub,totalPop) values (?,?,?,?,?,?,?,?,?,?)";
    	  try {
			pstmt=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
							
			pstmt.setString(1, userinfo.getNickName());						
			pstmt.setString(2, userinfo.getSex());
			pstmt.setString(3, userinfo.getBirthday());
			pstmt.setString(4, userinfo.getPlace());
			pstmt.setInt(5, userinfo.getAge());
			pstmt.setString(6, userinfo.getVocation());
			pstmt.setString(7, userinfo.getPersonalSpace());			
			pstmt.setTimestamp(8,new Timestamp(userinfo.getLastTime().getTime()));
			pstmt.setInt(9, userinfo.getScoreNub());
			pstmt.setLong(10, userinfo.getTotalPop());	
			if(pstmt.executeUpdate()==1){
				ResultSet rs=pstmt.getGeneratedKeys();				
				if(rs!=null){
					rs.next();
					userinfo.setUserId(rs.getInt(1));	
				}
			}
		} catch (SQLException e) {			
			e.printStackTrace();
		}finally{
			if(pstmt!=null){
				try {
					pstmt.close();
					pstmt=null;
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
			
			}
		}   	    	  
		return userinfo;    	   
       }
       /**
        * 用户信息表的删除方法
        * @param id
        */
       public static void delete(int userId){
    	   PreparedStatement pstmt=null;
    	   String sql="delete from userinfo where userId=?";
    	   try {
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1, userId);
			
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				if(pstmt!=null){
					pstmt.close();
					pstmt=null;
				}			
			} catch (SQLException e) {				
				e.printStackTrace();
			}
		}    
    	   
       }
       /**
        * 用户信息表的更新方法
        * @param userinfo
        */
       public static void update(UserInfo userinfo){
    	     PreparedStatement pstmt=null;
    	     String sql="update userinfo set nickName=?,sex=?,birthday=?,place=?,age=?,vocation=?,personalSpace=?,lastTime=?,scoreNub=?,totalPop=? where userId=?";
    	     try {
				pstmt=connection.prepareStatement(sql);
								
				pstmt.setString(1, userinfo.getNickName());						
				pstmt.setString(2, userinfo.getSex());
				pstmt.setString(3, userinfo.getBirthday());
				pstmt.setString(4, userinfo.getPlace());
				pstmt.setInt(5, userinfo.getAge());
				pstmt.setString(6, userinfo.getVocation());
				pstmt.setString(7, userinfo.getPersonalSpace());			
				pstmt.setTimestamp(8,new Timestamp(userinfo.getLastTime().getTime()));
				pstmt.setInt(9, userinfo.getScoreNub());
				pstmt.setLong(10, userinfo.getTotalPop());	
				pstmt.setInt(11, userinfo.getUserId());
				
				pstmt.executeUpdate();				
			} catch (SQLException e) {
			
				e.printStackTrace();
			} finally{
				try {
					if(pstmt!=null){
					pstmt.close();
					pstmt=null;
					}
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
			}		  	        	        	        	     
       }
      /**
       *  用户信息表的单条查找方法
       * @param id
       * @return
       */
       public static UserInfo findById(int userId){
    	   PreparedStatement pstmt=null;
    	   ResultSet rs=null;
    	   UserInfo userinfo=null;
    	   String sql="select * from userinfo where userId=?";
    	   try {
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1,userId);
			rs=pstmt.executeQuery();
			userinfo=new UserInfo();
			while(rs.next()){
								
				userinfo.setUserId(rs.getInt(1));
				userinfo.setNickName(rs.getString(2));								
				userinfo.setSex(rs.getString(3));
				userinfo.setBirthday(rs.getString(4));
				userinfo.setPlace(rs.getString(5));
				userinfo.setAge(rs.getInt(6));
				userinfo.setVocation(rs.getString(7));
				userinfo.setPersonalSpace(rs.getString(8));				
				userinfo.setLastTime(rs.getTimestamp(9));
				userinfo.setScoreNub(rs.getInt(10));
				userinfo.setTotalPop(rs.getLong(11));
			}						
			
		} catch (SQLException e) {			
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
				rs.close();
				rs=null;
				}
				if(pstmt!=null){
				pstmt.close();
				pstmt=null;
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}    	       	        	   
		return userinfo;   	   
       }
      /**
       *  用户信息表的查找全部的方法
       * @return
       */
       public static List<UserInfo> findAll(){
    	   PreparedStatement pstmt=null;
    	   List<UserInfo> rtnList=null;
    	   ResultSet rs=null;
    	   String sql="select * from userinfo";
    	   try {
			pstmt=connection.prepareStatement(sql);
			rs=pstmt.executeQuery();
			rtnList=new ArrayList<UserInfo>();
			while(rs.next()){
				UserInfo userinfo=new UserInfo();
				
				userinfo.setUserId(rs.getInt(1));
				userinfo.setNickName(rs.getString(2));								
				userinfo.setSex(rs.getString(3));
				userinfo.setBirthday(rs.getString(4));
				userinfo.setPlace(rs.getString(5));
				userinfo.setAge(rs.getInt(6));
				userinfo.setVocation(rs.getString(7));
				userinfo.setPersonalSpace(rs.getString(8));				
				userinfo.setLastTime(rs.getTimestamp(9));
				userinfo.setScoreNub(rs.getInt(10));
				userinfo.setTotalPop(rs.getLong(11));
				
				rtnList.add(userinfo);
			}
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
				rs.close();
				rs=null;
				}
				if(pstmt!=null){
				pstmt.close();
				pstmt=null;
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}       	     	   
		return rtnList;   	   
       }
       /**
        *  用户信息表的查找全部的方法 有参数
        * @return
        */
       public static List<UserInfo> findAll(int first ,int max){
    	   PreparedStatement pstmt=null;
    	   List<UserInfo> rtnList=null;
    	   ResultSet rs=null;
    	   String sql="select * from userinfo limit ?,?";
    	   try {
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1, first);
			pstmt.setInt(2, max);
			rs=pstmt.executeQuery();
			rtnList=new ArrayList<UserInfo>();
			while(rs.next()){
				UserInfo userinfo=new UserInfo();
				
				userinfo.setUserId(rs.getInt(1));
				userinfo.setNickName(rs.getString(2));								
				userinfo.setSex(rs.getString(3));
				userinfo.setBirthday(rs.getString(4));
				userinfo.setPlace(rs.getString(5));
				userinfo.setAge(rs.getInt(6));
				userinfo.setVocation(rs.getString(7));
				userinfo.setPersonalSpace(rs.getString(8));				
				userinfo.setLastTime(rs.getTimestamp(9));
				userinfo.setScoreNub(rs.getInt(10));
				userinfo.setTotalPop(rs.getLong(11));
				
				rtnList.add(userinfo);
			}			
		} catch (SQLException e) {			
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
				rs.close();
				rs=null;
				}
				if(pstmt!=null){
				pstmt.close();
				pstmt=null;
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}       	     	   
		return rtnList;   	      		      	   
       } 
       public static UserInfo findByPerSpace(String personalSpace){
    	   PreparedStatement pstmt=null;
    	   ResultSet rs=null;
    	   String sql="select * from userinfo where personalSpace=?";
    	   UserInfo userinfo=null;
    	   try {
			pstmt=connection.prepareStatement(sql);
			pstmt.setString(1,personalSpace );
			rs=pstmt.executeQuery();
			if(rs.next()){
                userinfo=new UserInfo();
				
				userinfo.setUserId(rs.getInt(1));
				userinfo.setNickName(rs.getString(2));								
				userinfo.setSex(rs.getString(3));
				userinfo.setBirthday(rs.getString(4));
				userinfo.setPlace(rs.getString(5));
				userinfo.setAge(rs.getInt(6));
				userinfo.setVocation(rs.getString(7));
				userinfo.setPersonalSpace(rs.getString(8));				
				userinfo.setLastTime(rs.getTimestamp(9));
				userinfo.setScoreNub(rs.getInt(10));
				userinfo.setTotalPop(rs.getLong(11));
				
			}			
		} catch (SQLException e) {			
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
				rs.close();
				rs=null;
				}
				if(pstmt!=null){
				pstmt.close();
				pstmt=null;
				}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		} 
		return userinfo;	   
       }
}