package net.titaniclinux.daogen.examples; import java.sql.*; import java.util.*; import java.math.*; /** * Customer Data Access Object (DAO). * This class contains all database handling that is needed to * permanently store and retrieve Customer object instances. */ /** * This sourcecode has been generated by FREE DaoGen generator version 2.2.1. * The usage of generated code is restricted to OpenSource software projects * only. DaoGen is available in http://titaniclinux.net/daogen/ * * DaoGen license: The following DaoGen generated source code is licensed * under the terms of GNU GPL license. The full text for license is available * in GNU project's pages: http://www.gnu.org/copyleft/gpl.html * */ public class CustomerDao { /** * load-method. This will load valueObject contents from database using * Primary-Key as identifier. Upper layer should use this so that valueObject * instance is created and only primary-key should be specified. Then call * this method to complete other persistent information. This method will * overwrite all other fields except primary-key and possible runtime variables. * If load can not find matching row, NotFoundException will be thrown. * * @param conn This method requires working database connection. * @param valueObject This parameter contains the class instance to be loaded. * Primary-key field must be set for this to work properly. */ public void load(Connection conn, Customer valueObject) throws NotFoundException, SQLException { String sql = "SELECT * FROM CUSTOMER WHERE (NUMBER = ? ) "; PreparedStatement stmt = null; try { stmt = conn.prepareStatement(sql); stmt.setInt(1, valueObject.getNumber()); singleQuery(conn, stmt, valueObject); } finally { if (stmt != null) stmt.close(); } } /** * LoadAll-method. This will read all contents from database table and * build an Vector containing valueObjects. Please note, that this method * will consume huge amounts of resources if table has lot's of rows. * This should only be used when target tables have only small amounts * of data. * * @param conn This method requires working database connection. */ public List loadAll(Connection conn) throws SQLException { String sql = "SELECT * FROM CUSTOMER ORDER BY NAME ASC "; List searchResults = listQuery(conn, conn.prepareStatement(sql)); return searchResults; } /** * create-method. This will create new row in database according to supplied * valueObject contents. Make sure that values for all NOT NULL columns are * correctly specified. Also, if this table does not use automatic surrogate-keys * the primary-key must be specified. After INSERT command this method will * read the generated primary-key back to valueObject if automatic surrogate-keys * were used. * * @param conn This method requires working database connection. * @param valueObject This parameter contains the class instance to be created. * If automatic surrogate-keys are not used the Primary-key * field must be set for this to work properly. */ public synchronized void create(Connection conn, Customer valueObject) throws SQLException { String sql = ""; PreparedStatement stmt = null; ResultSet result = null; try { sql = "INSERT INTO CUSTOMER ( NUMBER, NAME, ADDRESS, " + "CREATED, BALANCE) VALUES (?, ?, ?, ?, ?) "; stmt = conn.prepareStatement(sql); stmt.setInt(1, valueObject.getNumber()); stmt.setString(2, valueObject.getName()); stmt.setString(3, valueObject.getAddress()); stmt.setDate(4, valueObject.getCreated()); stmt.setInt(5, valueObject.getBalance()); int rowcount = databaseUpdate(conn, stmt); if (rowcount != 1) { //System.out.println("PrimaryKey Error when updating DB!"); throw new SQLException("PrimaryKey Error when updating DB!"); } } finally { if (stmt != null) stmt.close(); } } /** * save-method. This method will save the current state of valueObject to database. * Save can not be used to create new instances in database, so upper layer must * make sure that the primary-key is correctly specified. Primary-key will indicate * which instance is going to be updated in database. If save can not find matching * row, NotFoundException will be thrown. * * @param conn This method requires working database connection. * @param valueObject This parameter contains the class instance to be saved. * Primary-key field must be set for this to work properly. */ public void save(Connection conn, Customer valueObject) throws NotFoundException, SQLException { String sql = "UPDATE CUSTOMER SET NAME = ?, ADDRESS = ?, CREATED = ?, " + "BALANCE = ? WHERE (NUMBER = ? ) "; PreparedStatement stmt = null; try { stmt = conn.prepareStatement(sql); stmt.setString(1, valueObject.getName()); stmt.setString(2, valueObject.getAddress()); stmt.setDate(3, valueObject.getCreated()); stmt.setInt(4, valueObject.getBalance()); stmt.setInt(5, valueObject.getNumber()); int rowcount = databaseUpdate(conn, stmt); if (rowcount == 0) { //System.out.println("Object could not be saved! (PrimaryKey not found)"); throw new NotFoundException("Object could not be saved! (PrimaryKey not found)"); } if (rowcount > 1) { //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)"); throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)"); } } finally { if (stmt != null) stmt.close(); } } /** * delete-method. This method will remove the information from database as identified by * by primary-key in supplied valueObject. Once valueObject has been deleted it can not * be restored by calling save. Restoring can only be done using create method but if * database is using automatic surrogate-keys, the resulting object will have different * primary-key than what it was in the deleted object. If delete can not find matching row, * NotFoundException will be thrown. * * @param conn This method requires working database connection. * @param valueObject This parameter contains the class instance to be deleted. * Primary-key field must be set for this to work properly. */ public void delete(Connection conn, Customer valueObject) throws NotFoundException, SQLException { String sql = "DELETE FROM CUSTOMER WHERE (NUMBER = ? ) "; PreparedStatement stmt = null; try { stmt = conn.prepareStatement(sql); stmt.setInt(1, valueObject.getNumber()); int rowcount = databaseUpdate(conn, stmt); if (rowcount == 0) { //System.out.println("Object could not be deleted (PrimaryKey not found)"); throw new NotFoundException("Object could not be deleted! (PrimaryKey not found)"); } if (rowcount > 1) { //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)"); throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)"); } } finally { if (stmt != null) stmt.close(); } } /** * deleteAll-method. This method will remove all information from the table that matches * this Dao and ValueObject couple. This should be the most efficient way to clear table. * Once deleteAll has been called, no valueObject that has been created before can be * restored by calling save. Restoring can only be done using create method but if database * is using automatic surrogate-keys, the resulting object will have different primary-key * than what it was in the deleted object. (Note, the implementation of this method should * be different with different DB backends.) * * @param conn This method requires working database connection. */ public void deleteAll(Connection conn) throws SQLException { String sql = "DELETE FROM CUSTOMER"; PreparedStatement stmt = null; try { stmt = conn.prepareStatement(sql); int rowcount = databaseUpdate(conn, stmt); } finally { if (stmt != null) stmt.close(); } } /** * coutAll-method. This method will return the number of all rows from table that matches * this Dao. The implementation will simply execute "select count(primarykey) from table". * If table is empty, the return value is 0. This method should be used before calling * loadAll, to make sure table has not too many rows. * * @param conn This method requires working database connection. */ public int countAll(Connection conn) throws SQLException { String sql = "SELECT count(*) FROM CUSTOMER"; PreparedStatement stmt = null; ResultSet result = null; int allRows = 0; try { stmt = conn.prepareStatement(sql); result = stmt.executeQuery(); if (result.next()) allRows = result.getInt(1); } finally { if (result != null) result.close(); if (stmt != null) stmt.close(); } return allRows; } /** * searchMatching-Method. This method provides searching capability to * get matching valueObjects from database. It works by searching all * objects that match permanent instance variables of given object. * Upper layer should use this by setting some parameters in valueObject * and then call searchMatching. The result will be 0-N objects in vector, * all matching those criteria you specified. Those instance-variables that * have NULL values are excluded in search-criteria. * * @param conn This method requires working database connection. * @param valueObject This parameter contains the class instance where search will be based. * Primary-key field should not be set. */ public List searchMatching(Connection conn, Customer valueObject) throws SQLException { List searchResults; boolean first = true; StringBuffer sql = new StringBuffer("SELECT * FROM CUSTOMER WHERE 1=1 "); if (valueObject.getNumber() != 0) { if (first) { first = false; } sql.append("AND NUMBER = ").append(valueObject.getNumber()).append(" "); } if (valueObject.getName() != null) { if (first) { first = false; } sql.append("AND NAME LIKE '").append(valueObject.getName()).append("%' "); } if (valueObject.getAddress() != null) { if (first) { first = false; } sql.append("AND ADDRESS LIKE '").append(valueObject.getAddress()).append("%' "); } if (valueObject.getCreated() != null) { if (first) { first = false; } sql.append("AND CREATED = '").append(valueObject.getCreated()).append("' "); } if (valueObject.getBalance() != 0) { if (first) { first = false; } sql.append("AND BALANCE = ").append(valueObject.getBalance()).append(" "); } sql.append("ORDER BY NAME ASC "); // Prevent accidential full table results. // Use loadAll if all rows must be returned. if (first) searchResults = new Vector(); else searchResults = listQuery(conn, conn.prepareStatement(sql.toString())); return searchResults; } /** * getDaogenVersion will return information about * generator which created these sources. */ public String getDaogenVersion() { return "DaoGen version 2.2.1"; } /** * databaseUpdate-method. This method is a helper method for internal use. It will execute * all database handling that will change the information in tables. SELECT queries will * not be executed here however. The return value indicates how many rows were affected. * This method will also make sure that if cache is used, it will reset when data changes. * * @param conn This method requires working database connection. * @param stmt This parameter contains the SQL statement to be excuted. */ protected int databaseUpdate(Connection conn, PreparedStatement stmt) throws SQLException { int result = stmt.executeUpdate(); return result; } /** * databaseQuery-method. This method is a helper method for internal use. It will execute * all database queries that will return only one row. The resultset will be converted * to valueObject. If no rows were found, NotFoundException will be thrown. * * @param conn This method requires working database connection. * @param stmt This parameter contains the SQL statement to be excuted. * @param valueObject Class-instance where resulting data will be stored. */ protected void singleQuery(Connection conn, PreparedStatement stmt, Customer valueObject) throws NotFoundException, SQLException { ResultSet result = null; try { result = stmt.executeQuery(); if (result.next()) { valueObject.setNumber(result.getInt("NUMBER")); valueObject.setName(result.getString("NAME")); valueObject.setAddress(result.getString("ADDRESS")); valueObject.setCreated(result.getDate("CREATED")); valueObject.setBalance(result.getInt("BALANCE")); } else { //System.out.println("Customer Object Not Found!"); throw new NotFoundException("Customer Object Not Found!"); } } finally { if (result != null) result.close(); if (stmt != null) stmt.close(); } } /** * databaseQuery-method. This method is a helper method for internal use. It will execute * all database queries that will return multiple rows. The resultset will be converted * to the List of valueObjects. If no rows were found, an empty List will be returned. * * @param conn This method requires working database connection. * @param stmt This parameter contains the SQL statement to be excuted. */ protected List listQuery(Connection conn, PreparedStatement stmt) throws SQLException { Vector searchResults = new Vector(); ResultSet result = null; try { result = stmt.executeQuery(); while (result.next()) { Customer temp = new Customer(); temp.setNumber(result.getInt("NUMBER")); temp.setName(result.getString("NAME")); temp.setAddress(result.getString("ADDRESS")); temp.setCreated(result.getDate("CREATED")); temp.setBalance(result.getInt("BALANCE")); searchResults.add(temp); } } finally { if (result != null) result.close(); if (stmt != null) stmt.close(); } return (List)searchResults; } }