package com.ora.jsp.beans.emp;
import java.io.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import javax.sql.*;
import com.ora.jsp.sql.*;
import com.ora.jsp.sql.value.*;
import com.ora.jsp.util.*;
/**
* This class is an example of an application specific interface
* to a database. It contains methods for authenticating a user,
* and retrieving and updating user information.
*
* @author Hans Bergsten, Gefion software <hans@gefionsoftware.com>
* @version 1.0
*/
public class EmployeeRegistryBean implements Serializable {
private DataSource dataSource;
/**
* Sets the dataSource property value.
*/
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
/**
* Returns true if the specified user name and password
* match an employee in the database.
*/
public boolean authenticate(String userName, String password)
throws SQLException {
EmployeeBean empInfo = getEmployee(userName);
if (empInfo != null && empInfo.getPassword().equals(password)) {
return true;
}
return false;
}
/**
* Returns an EmployeeBean initialized with the information
* found in the database for the specified employee, or null if
* not found.
*/
public EmployeeBean getEmployee(String userName) throws SQLException {
// Get the user info from the database
Connection conn = dataSource.getConnection();
Row empRow = null;
Vector projectRows = null;
try {
empRow = getSingleValueProps(userName, conn);
projectRows = getProjects(userName, conn);
}
finally {
try {
conn.close();
}
catch (SQLException e) {} // Ignore
}
// Create a EmployeeBean if the user was found
if (empRow == null) {
// Not found
return null;
}
EmployeeBean empInfo = new EmployeeBean();
try {
empInfo.setDept(empRow.getString("Dept"));
empInfo.setEmpDate(empRow.getString("EmpDate"));
empInfo.setEmailAddr(empRow.getString("EmailAddr"));
empInfo.setFirstName(empRow.getString("FirstName"));
empInfo.setLastName(empRow.getString("LastName"));
empInfo.setPassword(empRow.getString("Password"));
empInfo.setUserName(empRow.getString("UserName"));
empInfo.setProjects(toProjectsArray(projectRows));
}
catch (NoSuchColumnException nsce) {} // Can not happen here
return empInfo;
}
/**
* Inserts the information about the specified employee, or
* updates the information if it's already defined.
*/
public void saveEmployee(EmployeeBean empInfo) throws SQLException {
// Save the user info from the database
Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
Row userRow = null;
Vector interestRows = null;
try {
saveSingleValueProps(empInfo, conn);
saveProjects(empInfo, conn);
conn.commit();
}
finally {
try {
conn.setAutoCommit(true);
conn.close();
}
catch (SQLException e) {} // Ignore
}
}
/**
* Returns a Row with all information about the specified
* employee except the project list, or null if not found.
*/
private Row getSingleValueProps(String userName, Connection conn)
throws SQLException {
if (userName == null) {
return null;
}
SQLCommandBean sqlCommandBean = new SQLCommandBean();
sqlCommandBean.setConnection(conn);
StringBuffer sql = new StringBuffer();
sql.append("SELECT * FROM Employee ")
.append("WHERE UserName = ?");
sqlCommandBean.setSqlValue(sql.toString());
Vector values = new Vector();
values.addElement(new StringValue(userName));
sqlCommandBean.setValues(values);
Vector rows = null;
try {
rows = sqlCommandBean.executeQuery();
}
catch (UnsupportedTypeException e) {} // Can not happen here
if (rows == null || rows.size() == 0) {
// User not found
return null;
}
return (Row) rows.firstElement();
}
/**
* Returns a Vector with Row objects describing all
* projects for the specified employee, or null if not found.
*/
private Vector getProjects(String userName, Connection conn)
throws SQLException {
if (userName == null) {
return null;
}
SQLCommandBean sqlCommandBean = new SQLCommandBean();
sqlCommandBean.setConnection(conn);
StringBuffer sql = new StringBuffer();
sql.append("SELECT * FROM EmployeeProjects ")
.append("WHERE UserName = ?");
sqlCommandBean.setSqlValue(sql.toString());
Vector values = new Vector();
values.addElement(new StringValue(userName));
sqlCommandBean.setValues(values);
Vector rows = null;
try {
rows = sqlCommandBean.executeQuery();
}
catch (UnsupportedTypeException e) {} // Can not happen here
return rows;
}
/**
* Inserts the information about the specified employee (except projects),
* or updates the information if it's already defined.
*/
private void saveSingleValueProps(EmployeeBean empInfo, Connection conn)
throws SQLException {
if (empInfo == null) {
return;
}
StringBuffer sql = new StringBuffer();
EmployeeBean dbInfo = getEmployee(empInfo.getUserName());
if (dbInfo == null) {
// Use INSERT statement
sql.append("INSERT INTO Employee ").
append("(Dept, EmpDate, EmailAddr, FirstName, LastName, ").
append("Password, ModDate, UserName) ").
append("VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
}
else {
// Use UPDATE statement
sql.append("UPDATE Employee SET Dept = ?, EmpDate = ?, ")
.append("EmailAddr = ?, FirstName = ?, LastName = ?,")
.append("Password = ?, ModDate = ? WHERE Username = ?");
}
SQLCommandBean sqlCommandBean = new SQLCommandBean();
sqlCommandBean.setConnection(conn);
sqlCommandBean.setSqlValue(sql.toString());
Vector values = new Vector();
values.addElement(new StringValue(empInfo.getDept()));
values.addElement(new DateValue(toDate(empInfo.getEmpDate())));
values.addElement(new StringValue(empInfo.getEmailAddr()));
values.addElement(new StringValue(empInfo.getFirstName()));
values.addElement(new StringValue(empInfo.getLastName()));
values.addElement(new StringValue(empInfo.getPassword()));
values.addElement(
new TimestampValue(new Timestamp(System.currentTimeMillis())));
values.addElement(new StringValue(empInfo.getUserName()));
sqlCommandBean.setValues(values);
sqlCommandBean.executeUpdate();
}
/**
* Updates or inserts project information for the specified
* employee.
*/
private void saveProjects(EmployeeBean empInfo, Connection conn)
throws SQLException {
if (empInfo == null) {
return;
}
SQLCommandBean sqlCommandBean = new SQLCommandBean();
sqlCommandBean.setConnection(conn);
sqlCommandBean.setSqlValue("DELETE FROM EmployeeProjects WHERE UserName = ?");
Vector values = new Vector();
StringValue userNameValue = new StringValue(empInfo.getUserName());
values.addElement(userNameValue);
sqlCommandBean.setValues(values);
sqlCommandBean.executeUpdate();
String[] projects = empInfo.getProjects();
sqlCommandBean.setSqlValue("INSERT INTO EmployeeProjects VALUES(?, ?)");
for (int i = 0; i < projects.length; i++) {
values.removeAllElements();
values.addElement(userNameValue);
values.addElement(new StringValue(projects[i]));
sqlCommandBean.executeUpdate();
}
}
/**
* Converts a Vector with Row elements containing a ProjectName
* column to a String[] with the project names.
*/
private String[] toProjectsArray(Vector projectRows) {
if (projectRows == null) {
return new String[0];
}
String[] arr = new String[projectRows.size()];
for (int i = 0; i < arr.length; i++) {
Row row = (Row) projectRows.elementAt(i);
try {
arr[i] = row.getString("ProjectName");
}
catch (NoSuchColumnException nsce) {} // Can not happen here
}
return arr;
}
/**
* Converts a date string in the format "yyyy-MM-dd" to a
* Date object.
*/
private java.sql.Date toDate(String dateString) {
java.util.Date date = null;
try {
date = StringFormat.toDate(dateString, "yyyy-MM-dd");
}
catch (ParseException pe) {} // Can not happen here
return new java.sql.Date(date.getTime());
}
} |
