UserDAO.java

/**
 * 
 */
package br.com.cadastropessoal.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import br.com.cadastropessoal.vo.UserVO;

/**
 * @author Felipe
 *
 */
public class UserDAO {
	
	public UserDAO(){
		
	}
	
	public long insertUser(UserVO userVO){
		
		 CallableStatement cs = null;
		 Connection conn = null;
		 long id = 0;
		 DateFormat fmt = new SimpleDateFormat("dd/MM/yyyy");  
		
		 try {  
	          
			 conn = DatabaseManager.getConnection();
			 
	         String strCmd = "{call sp_insert_user(?,?,?,?,?,?,?,?)}";
             cs = conn.prepareCall(strCmd);
            
             //Setando paramentros para a SP
             cs.setString(1, userVO.getFirstName()); //FIRST_NAME IN
             cs.setString(2, userVO.getMiddleName()); //MIDDLE_NAME IN
             cs.setString(3, userVO.getLastName()); //LAST_NAME IN
             cs.setString(4, userVO.getCpf()); //CPF IN
             cs.setString(5, userVO.getEmail().toLowerCase()); //EMAIL IN
             cs.setString(6, userVO.getPassword()); //PASSWORD IN
             cs.setDate(7, new java.sql.Date(fmt.parse(userVO.getBirthDate()).getTime())); //BIRTH_DATE IN
             cs.registerOutParameter(8, java.sql.Types.NUMERIC); //USER_ID OUT
             
             //Executando a SP
             cs.execute();
             
             //Obtendo o retorno da SP
             id = cs.getInt(8);
	           
	      } catch (SQLException e) {  
	         e.printStackTrace();  
	      } catch (Exception e) {  
		     e.printStackTrace();  
		  }finally{
	    	try {
				cs.close();
				DatabaseManager.closeConnection(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}catch (Exception e) {  
		        e.printStackTrace();  
		    }
	      }
		
		return id;
	}
	
	public UserVO findUserForSession(String email, String password){
		
		PreparedStatement ps = null;
		Connection conn = null;
		ResultSet rs = null;
		UserVO userVO = null;
		
		try{
			conn = DatabaseManager.getConnection();
			
			String sql = " SELECT * FROM tb_user " +
					     " WHERE lower(email) = ? " +
					     " AND password = ? ";
			
			ps = conn.prepareStatement(sql);
			ps.setString(1, email.toLowerCase());
			ps.setString(2, password);
			
			rs = ps.executeQuery();
			
			if(rs.next()){
				userVO = new UserVO();
				userVO.setUserId(rs.getLong("USER_ID"));
				userVO.setFirstName(rs.getString("FIRST_NAME"));
				userVO.setMiddleName(rs.getString("MIDDLE_NAME"));
				userVO.setLastName(rs.getString("LAST_NAME"));
				userVO.setEmail(rs.getString("EMAIL").toLowerCase());
			}
			
		}catch (SQLException e) {  
	         e.printStackTrace();  
	      } catch (Exception e) {  
		     e.printStackTrace();  
		  }finally{
	    	try {
				ps.close();
				rs.close();
				DatabaseManager.closeConnection(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}catch (Exception e) {  
		        e.printStackTrace();  
		    }
	      }
		
		
		return userVO;
	}
	
	public List<UserVO> getAllUsers(){
		PreparedStatement ps = null;
		Connection conn = null;
		ResultSet rs = null;
		UserVO userVO = null;
		List<UserVO> users = new ArrayList<UserVO>();
		
		try{
			conn = DatabaseManager.getConnection();
			
			String sql = " SELECT * FROM tb_user ";
			
			ps = conn.prepareStatement(sql);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				userVO = new UserVO();
				userVO.setUserId(rs.getLong("USER_ID"));
				userVO.setFirstName(rs.getString("FIRST_NAME"));
				userVO.setMiddleName(rs.getString("MIDDLE_NAME"));
				userVO.setLastName(rs.getString("LAST_NAME"));
				userVO.setEmail(rs.getString("EMAIL").toLowerCase());
				userVO.setBirthDate(rs.getDate("BIRTH_DATE").toString());
				userVO.setCpf(rs.getString("CPF"));
				userVO.setRegisterDate(rs.getDate("REGISTER_DATE").toString());
				
				users.add(userVO);
			}
			
		}catch (SQLException e) {  
	         e.printStackTrace();  
	      } catch (Exception e) {  
		     e.printStackTrace();  
		  }finally{
	    	try {
				ps.close();
				rs.close();
				DatabaseManager.closeConnection(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}catch (Exception e) {  
		        e.printStackTrace();  
		    }
	      }
		
		return users;
	}
	
	public boolean insertTel(long userId, String tel){
		
		PreparedStatement ps = null;
		Connection conn = null;
		Boolean inserted = false;
		
		try{
			conn = DatabaseManager.getConnection();
			
			String sql = " INSERT INTO tb_telephone (TELEPHONE_ID,USER_ID,TELEPHONE) VALUES (seq_telephone.nextval,?,?) ";
			
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			ps.setString(2, tel);
			System.out.println("USER ID -> " + userId);
			System.out.println("VAI INSERIR");
			inserted = ps.execute();
			System.out.println("INSERIOU? -> " + inserted);
			
	      } catch (Exception e) {  
		     e.printStackTrace();  
		  }finally{
	    	try {
				DatabaseManager.closeConnection(conn);
			} catch (Exception e) {  
		        e.printStackTrace();  
		    }
	      }
		return inserted;		
	}

}