accountmapper.java

use MVC bean with 3-tiers

package usermanager.access.mapper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import usermanager.dto.*;

public class AccountMapper extends MapperDB {
	public AccountMapper() throws Exception {
		super();
	}
	
	public boolean login(String username, String password) throws Exception {
		boolean found = false;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT * FROM j2ee.accounts WHERE ");
			sql.append(" username = '" + username + "'");
			sql.append(" AND password = '" + password + "'");
			
			PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
			ResultSet rs = stmt.executeQuery();
			if ((rs != null) && rs.next())
				found = true;
			
			stmt.close();
		}
		catch (Exception e) {
			throw e;
		}
		
		return found;
	}
	
	public AccountDTO getUserInformation(String username) throws Exception {
		AccountDTO accountDTO = new AccountDTO();
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT * FROM j2ee.accounts WHERE ");
			sql.append(" username = '" + username + "'");
		
			PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
			ResultSet rs = stmt.executeQuery();
			if ((rs != null) && rs.next()) {
				initAccountDTO(accountDTO, rs);
			}
			
			stmt.close();
		}
		catch (Exception e) {
			throw e;
		}
		
		return accountDTO;
	}
	
	public void insertNewUser(AccountDTO account) throws Exception {
		try {
			StringBuffer sql = new StringBuffer();
			PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
			//...
			stmt.execute();
			stmt.close();
		}
		catch (Exception e) {
			throw e;
		}
	}
	
	public ArrayList<AccountDTO> listUser() throws Exception {
		ArrayList<AccountDTO> listOfUsers = new ArrayList<AccountDTO>();
		AccountDTO userDTO = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT * FROM j2ee.accounts");
			PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
			ResultSet rs = stmt.executeQuery();
			while ((rs != null) && rs.next()) {
				userDTO = new AccountDTO();
				initAccountDTO(userDTO, rs);
				listOfUsers.add(userDTO);
			}
			
			stmt.close();
		}
		catch (Exception e) {
			throw e;
		}
		
		return listOfUsers;
	}
	
	public void initAccountDTO(AccountDTO accountDTO, ResultSet rs) throws Exception {
		if ((rs != null) && (accountDTO != null)) {
			accountDTO.setUsername(rs.getString("username"));
			accountDTO.setPassword(rs.getString("password"));
			accountDTO.setFirstname(rs.getString("firstname"));
			accountDTO.setLastname("lastname");
			accountDTO.setSex(rs.getString("sex"));
			accountDTO.setAddress(rs.getString("address"));
			accountDTO.setEmail(rs.getString("email"));
			accountDTO.setMobilephone(rs.getString("mobilephone"));
			accountDTO.setRoleld(rs.getString("roleId"));
		}
	}
}