GenericDBLoader

Die Tool Klasse GenericDBLoader, dient dazu eine Liste von Objekten, aus einer Rationalen Datenbank zu laden. Es lässt sich über den Konstruktor steuern, ob die Connection vom GenericDBLoader geschlossen werden soll. Weiter besteht die Auswahl, entweder ein SQL Statement oder ein PreparedStatement zu übergeben.

class GenericDBLoader


package de.snowbits.tools;

import static de.snowbits.tools.SQLTools.close;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public abstract class GenericDBLoader<T> {
	
	private final String _statement;
	private boolean _closeConnetion;
	
	/**
	 * @param statement
	 * @param closeConnetion false and the connection remains open, true end the connection is closed
	 */
	public GenericDBLoader(String statement, boolean closeConnetion) {
		_statement = statement;
		_closeConnetion = closeConnetion;
	}

	public GenericDBLoader(String statement) {
		_statement = statement;
	}
	
	public List<T> getAll() throws SQLException {
		return getAll(null);
	}
	
	public List<T> getAll(PreparedStatement preparedStatement) throws SQLException {
		List<T> result = new ArrayList<T>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			
			if (preparedStatement == null)
				stmt = conn.prepareStatement(_statement);
			else
				stmt = preparedStatement;
			
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				result.add(resolve(rs));
			}

		} catch (SQLException e) {
			throw new SQLException("Error at loading see message: " + e.getMessage() + 
					               "\nStatement: \n" + _statement + "\n", e);
		} finally {
			try {
				if (isCloseConnetion()) {
					close(rs, stmt, conn);
				}
				else {
					close(rs, stmt);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	
	public boolean isCloseConnetion() {
		return _closeConnetion;
	}

	protected abstract Connection getConnection() throws SQLException;
	protected abstract T resolve(ResultSet rs) throws SQLException;
		
}
	

Beispiel class LoadPersons


package de.snowbits.db;

import static de.snowbits.tools.SQLTools.close;
import static de.snowbits.tools.SQLTools.getInteger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import de.snowbits.pojo.Person;
import de.snowbits.pojo.Sex;
import de.snowbits.tools.GenericDBLoader;

public class LoadPersons {
	
	private static final String SQL_PERSON = "SELECT id, age, sex, firstname, lastname FROM person";

	private Connection _connection;

	public List<Person> load(Connection connection) throws Exception {
		try {
			_connection = connection;
			
			return load();
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			close(_connection);
			throw new Exception("Error at loading all persons.", e);
		}
	}

	public List<Person> loadBetweenAge(Integer fromAge, Integer toAge, Connection connection) throws Exception {
		try {
			_connection = connection;
			
			return loadBetweenAge(fromAge, toAge);
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			close(_connection);
			throw new Exception("Error at loading persons between: fromAge=" + fromAge + " and toAge=" + toAge, e);
		}
	}	
	
	private List<Person> load() throws SQLException {
		return new GenericDBLoader<Person>(SQL_PERSON) {

			@Override
			protected Connection getConnection() throws SQLException {
				return _connection;
			}

			@Override
			protected Person resolve(ResultSet rs) throws SQLException {
				return getPerson(rs);
			}

		}.getAll();
	}
	
	private List<Person> loadBetweenAge(Integer fromAge, Integer toAge) throws SQLException {
		PreparedStatement stmt = _connection.prepareStatement(SQL_PERSON + " WHERE age BETWEEN ? AND ?");
		
		int index = 1;
		stmt.setInt(index++, fromAge);
		stmt.setInt(index++, toAge);
		
		return new GenericDBLoader<Person>(null) {

			@Override
			protected Connection getConnection() throws SQLException {
				return _connection;
			}

			@Override
			protected Person resolve(ResultSet rs) throws SQLException {
				return getPerson(rs);
			}
			
		}.getAll(stmt);
	}	
	
	private Person getPerson(ResultSet rs) throws SQLException {
		Person result = new Person(getInteger(rs, "id"));
		result.setAge(getInteger(rs, "age"));
		result.setSex(Sex.getValue(rs.getString("sex")));
		result.setFirstname(rs.getString("firstname"));
		result.setLastname(rs.getString("lastname"));
		return result;
	}
	
}
	

class Person


package de.snowbits.pojo;

public class Person {

	private Integer _id;
	private Sex _sex;
	private String _firstname;
	private String _lastname;
	private Integer _age;
	
	public Person(Integer id) {
		_id = id;
	}

	public Integer getId() {
		return _id;
	}

	public Sex getSex() {
		return _sex;
	}

	public void setSex(Sex sex) {
		_sex = sex;
	}

	public String getFirstname() {
		return _firstname;
	}

	public void setFirstname(String firstname) {
		_firstname = firstname;
	}

	public String getLastname() {
		return _lastname;
	}

	public void setLastname(String lastname) {
		_lastname = lastname;
	}

	public Integer getAge() {
		return _age;
	}

	public void setAge(Integer age) {
		_age = age;
	}

	@Override
	public String toString() {
		return new StringBuilder()
		        .append("[id: ").append(getId())
		        .append("; sex: ").append(getSex())
		        .append("; firstname: ").append(getFirstname())
		        .append("; lastname: ").append(getLastname())
		        .append("; age: ").append(getAge())
		        .append("]").toString();
	}

}
	

enum Sex


package de.snowbits.pojo;

public enum Sex {

	MALE("M"), FEMALE("F");

	private String _id;

	private Sex(String id) {
		_id = id;
	}

	public static final Sex getValue(String id) {
		Sex[] result = values();

		for (Sex element : result) {
			if (element.getId().equals(id))
				return element;
		}

		throw new IllegalArgumentException("Error this type is not supported: id=" + id);
	}

	public String getId() {
		return _id;
	}
}
	

Top


Example
Sitemap Kontakt Impressum