Efficiency - How to load what you need.
Hello,
I am pondering the scenario of using object oriented programming and filling a report efficiently. I am hoping someone could provide insight or direct me to existing patterns.
Let's say I want to create a report of all employees. Great, my first thought is have a Employee class and Employee Container. Load all the employee objects and all the n attributes. Loop through to display.
What about if I only wanted a select few of the employees in the report. I could loop through all as above looking for the correct criteria, however, I would much rather let the database do that. So what is a good way to specify criteria to load the employees.
Another is what if I only want 2 of the n attributes. How can I get around loading all the attributes of the Employee? Keeping in mind that I don't want to know anything about the database at the business object level.
This is really only part of the problem. I'm sure everyone goes through it so there must be best practices and/or patterns to help.
Thanks,
Tom
[1067 byte] By [
tkrueger76] at [2007-9-27 21:18:50]
Lazy Loading - This is more of a strategy than a pattern.
Here's my 2 cent summarized version of how it works.
- Scenario
Consider that you have an Object and it contains a finder method called findAll() -- which retrieves all employes from the system (database).
Assume Employees have the following data: name, age, department, salary, employee id, job title, current projects, etc.
When the findAll() method is called it would access the database and populate an Employee objects with the fields required to make them unique (employee id & possibly name).
Once you have picked employees (I'm assuming some kind of user-interface is here that displays a list of them all). Once you select the employees to use you can call some method like populateData() that retrieves the rest of the information from the system then it should be able to be accessed through the getters to populate the report.
Hopefully this all makes sense, if you have an electronic or paper copy of the J2EE patterns book it goes through an example on p 317 to 318.
I've actually used the strategy before when storing large amounts of data in a database (binary photo data).
Only populate the name & id of the photo for the user to choose from and once a user selects view on a particular photo it hits the database and loads up the data (stored about 1.4 MB of data per photo in db--really high resolution). This way I don't load up 1000's of photos into memory when a user would typically access only 10, I just load the information on demand (photo info could then be cached at this point in time).
Well that makes it a bit more complicated.
Depending if you want the items that can be queried on (title, salary, etc) to change would modify the solution a bit.
If they are static queries like
SELECT x1, y2, z3
FROM employees
WHERE title = ? AND
salary = ?
Then I would add a new method to the overall interface like
public Collection findEmployeesByTitleAndSalary(String title, String salary) {
// ... do JDBC logic here
}
If it needs to be dynamic that poses an interesting problem since Java doesn't provide for optional parameters like C++ does, you could do something that takes in an array and an array of values (or a Map). Then based on the key give you would construct a SQL query that retrieves the appropriate information.
Let me attempt at a quick example (somewhat peusdo codeish)
public class Person {
public static final PERSON_ID = 0;
public static final NAME = 1;
public static final TITLE = 2;
public static final DATE_OF_BIRTH = 3;
public Collection findAll() {
}
private Connection getConnection() {
// db connection logic here
}
private String hasWhere(boolean found) {
return found ? " AND " : " WHERE ";
}
private String constructQuery(Map attributes) {
StringBuffer query = new StringBuffer("SELECT * FROM person");
boolean found = false;
if(attributes.containsKey(PERSON_ID)) {
sb.append(hasWhere(found));
sb.append(" person = " + attributes.get(PERSON_ID));
}
if(attributes.containsKey(NAME)) {
sb.append(hasWhere(found));
sb.append(" name = " + attributes.get(NAME));
}
// same kind of thing for similar styles
return sb.toString();
}
public Collection find(Map attributes) {
String query = constructQuery(attributes);
Connection conn = getConnection();
Statement s = conn.prepareStatement(s);
ResultSet rs = s.executeQuery();
Collection people = new ArrayList();
while(rs.next()) {
// get attributes here & add to Collection
}
return people;
}
}
There's probably a pattern to apply that gets rid of the if stuff for each attribute, but I need to get break out my pattern book to look--I'll let you know if I find something.
Note, I didn't actually run this in a database to verify that it worked. Only verification I did was to print out on the console what the SQL statement was.
bash-2.03$ java Example
SELECT * person WHERE name = bob AND age >= 21
(You may want to modify it to put a ';' at the end of the query if necessary.)
This is the interface that defines the key to the Map
package db;
public interface Attribute {
String getField();
String getOperator();
String construct(String value);
int hashCode();
boolean equals(Object o);
}
Very flexible implementation of an attribute--should be able to apply to whatever you want.
package db;
public class GenericAttribute implements Attribute {
private String field;
private String operator;
public GenericAttribute(String field) {
this(field, "=");
}
public GenericAttribute(String field, String operator) {
this.field = field;
this.operator = operator;
}
public String getField() {
return this.field;
}
public String getOperator() {
return this.operator;
}
public String construct(String value) {
return this.field + " " + this.operator + " " + value;
}
public int hashCode() {
return field.hashCode() + operator.hashCode();
}
public boolean equals(Object o) {
if(o instanceof GenericAttribute) {
GenericAttribute attrib = (GenericAttribute) o;
return (this.field.equals(attrib.getField()) && this.operator.equals(attrib.getOperator()));
}
return false;
}
}
Pieces all of the parts together.
package db;
import java.util.Collection;
import java.util.Iterator;
import java.util.Map;
import java.sql.ResultSet;
import java.sql.SQLException;
public abstract class SQLComposer {
protected abstract String getTable();
protected String getFields() {
return "*";
}
protected abstract Collection getResults(ResultSet rs) throws SQLException;
public String compose(Map attributes) {
StringBuffer sb = new StringBuffer("SELECT ");
sb.append(getFields() + " " + getTable());
Iterator iter = attributes.keySet().iterator();
boolean first = true;
while(iter.hasNext()) {
Attribute attrib = (Attribute) iter.next();
if(first) {
sb.append(" WHERE ");
first = false;
} else {
sb.append(" AND ");
}
sb.append(attrib.construct((String) attributes.get(attrib)));
}
return sb.toString();
}
}
Example code using the composer and the attribute
import db.Attribute;
import db.GenericAttribute;
import db.SQLComposer;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
public class Example extends SQLComposer {
public static final Attribute NAME = new GenericAttribute("name", "=");
public static final Attribute AGE = new GenericAttribute("age" , ">=");
public static final Attribute TITLE = new GenericAttribute("title", "LIKE");
private String table = null;
public Example(String table) {
this.table = table;
}
protected String getTable() {
return this.table;
}
public Connection getConnection() {
// do JDBC direct connection or DataSource lookup
return null;
}
public Collection process(Map attributes) throws SQLException {
Connection conn = getConnection();
Statement s = conn.createStatement();
return getResults(s.executeQuery(compose(attributes)));
}
protected Collection getResults(ResultSet rs) throws SQLException {
Collection results = new ArrayList();
while(rs.next()) {
// parse out info and append to collection
}
return results;
}
public static void main(String[] args) {
Example e = new Example("person");
Map map = new HashMap();
map.put(Example.NAME, "bob");
map.put(Example.AGE, "21");
try {
e.process(map);
} catch(SQLException sqle) {
sqle.printStackTrace();
System.exit(1);
}
}
}
