Java tutorial
// Description: Java 6 PostgreSQL Jdbc DbIO implementation for Address. /* * MSS Code Factory 1.10 * * Copyright (c) 2012 Mark Sobkow * * This program is available as free software under the GNU GPL v3, or * under a commercial license from Mark Sobkow. For commercial licensing * details, please contact msobkow@sasktel.net. * * Under the terms of the GPL: * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. * * *********************************************************************** * * Code manufactured * by MSS Code Factory version 1.9.3294 * * $Revision: 26 $ */ package net.sourceforge.msscodefactory.v1_10.MSSBamPg8; import java.math.*; import java.sql.*; import java.text.*; import java.util.*; import net.sourceforge.msscodefactory.cflib.v1_9.CFLib.*; import org.apache.commons.codec.binary.Base64; import net.sourceforge.msscodefactory.v1_10.MSSBam.*; /* * MSSBamPg8AddressTable PostgreSQL Jdbc DbIO implementation * for Address. * * Data redaction is the responsibility of another layer. The raw database * interface returns everything regardless of whether the end user is * authorized to see the data. A redaction layer replaces protected/redacted * buffs with default values. They should not be included in client-side * filter sets, and the network redaction layer should actually eliminate them * before transmitting data to the client. The client should never see * redacted data in order to comply with data privacy regulations in Canada * and the US. */ public class MSSBamPg8AddressTable implements IMSSBamAddressTable { private MSSBamPg8Schema schema; public MSSBamPg8AddressTable(MSSBamPg8Schema argSchema) { schema = argSchema; } public void createAddress(MSSBamAuthorization Authorization, MSSBamAddressBuff Buff) { final String S_ProcName = "createAddress "; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName, "Transaction not open"); } try { Connection cnx = schema.getCnx(); long AddressId = schema.nextAddressIdGen(); long ContactId = Buff.getRequiredContactId(); String Description = Buff.getRequiredDescription(); String AddrLine1 = Buff.getOptionalAddrLine1(); String AddrLine2 = Buff.getOptionalAddrLine2(); String City = Buff.getOptionalCity(); String State = Buff.getOptionalState(); String Country = Buff.getOptionalCountry(); String PostalCode = Buff.getOptionalPostalCode(); int Revision = 1; String sql = "INSERT INTO mssbam110.Address( " + "addressid, " + "contactid, " + "description, " + "addrline1, " + "addrline2, " + "city, " + "state, " + "country, " + "postalcode" + ", revision )" + "VALUES ( " + AddressId + ", " + ContactId + ", " + MSSBamPg8Schema.getQuotedString(Description) + ", " + MSSBamPg8Schema.getQuotedString(AddrLine1) + ", " + MSSBamPg8Schema.getQuotedString(AddrLine2) + ", " + MSSBamPg8Schema.getQuotedString(City) + ", " + MSSBamPg8Schema.getQuotedString(State) + ", " + MSSBamPg8Schema.getQuotedString(Country) + ", " + MSSBamPg8Schema.getQuotedString(PostalCode) + ", " + Integer.toString(Revision) + " )"; Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); int rowsAffected = stmt.executeUpdate(sql); if (rowsAffected != 1) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Expected 1 row to be affected by insert, not " + rowsAffected); } Buff.setRequiredAddressId(AddressId); Buff.setRequiredRevision(Revision); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } } public final static String S_sqlSelectAddressDistinctClassCode = "SELECT " + "DISTINCT adr.ClassCode " + "FROM mssbam110.Address AS adr "; public final static String S_sqlSelectAddressBuff = "SELECT " + "adr.AddressId, " + "adr.ContactId, " + "adr.Description, " + "adr.AddrLine1, " + "adr.AddrLine2, " + "adr.City, " + "adr.State, " + "adr.Country, " + "adr.PostalCode, " + "adr.Revision " + "FROM mssbam110.Address AS adr "; protected MSSBamAddressBuff unpackAddressResultSetToBuff(ResultSet resultSet) throws SQLException { final String S_ProcName = "unpackAddressResultSetToBuff"; int idxcol = 1; MSSBamAddressBuff buff = schema.getFactoryAddress().newBuff(); buff.setRequiredAddressId(resultSet.getLong(idxcol)); idxcol++; buff.setRequiredContactId(resultSet.getLong(idxcol)); idxcol++; buff.setRequiredDescription(resultSet.getString(idxcol)); idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalAddrLine1(null); } else { buff.setOptionalAddrLine1(colVal); } } idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalAddrLine2(null); } else { buff.setOptionalAddrLine2(colVal); } } idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalCity(null); } else { buff.setOptionalCity(colVal); } } idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalState(null); } else { buff.setOptionalState(colVal); } } idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalCountry(null); } else { buff.setOptionalCountry(colVal); } } idxcol++; { String colVal = resultSet.getString(idxcol); if (resultSet.wasNull()) { buff.setOptionalPostalCode(null); } else { buff.setOptionalPostalCode(colVal); } } idxcol++; buff.setRequiredRevision(resultSet.getInt(idxcol)); return (buff); } public MSSBamAddressBuff readDerived(MSSBamAuthorization Authorization, MSSBamAddressPKey PKey) { final String S_ProcName = "readDerived()"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName, "Transaction not open"); } MSSBamAddressBuff buff; long AddressId = PKey.getRequiredAddressId(); buff = readBuff(Authorization, PKey); return (buff); } public MSSBamAddressBuff[] readAllDerived(MSSBamAuthorization Authorization) { final String S_ProcName = "readAllDerived"; MSSBamAddressBuff[] buffArray; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName, "Transaction not open"); } buffArray = readAllBuff(Authorization); return (buffArray); } public MSSBamAddressBuff readDerivedByIdIdx(MSSBamAuthorization Authorization, long AddressId) { final String S_ProcName = "MSSBamPg8AddressTable.readDerivedByIdIdx() "; MSSBamAddressBuff buff; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName, "Transaction not open"); } buff = readBuffByIdIdx(Authorization, AddressId); return (buff); } public MSSBamAddressBuff[] readDerivedByContactIdx(MSSBamAuthorization Authorization, long ContactId) { final String S_ProcName = "readDerivedByContactIdx"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName, "Transaction not open"); } MSSBamAddressBuff[] buffList = readBuffByContactIdx(Authorization, ContactId); return (buffList); } public MSSBamAddressBuff readDerivedByUDescrIdx(MSSBamAuthorization Authorization, long ContactId, String Description) { final String S_ProcName = "MSSBamPg8AddressTable.readDerivedByUDescrIdx() "; MSSBamAddressBuff buff; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName, "Transaction not open"); } buff = readBuffByUDescrIdx(Authorization, ContactId, Description); return (buff); } public MSSBamAddressBuff readBuff(MSSBamAuthorization Authorization, MSSBamAddressPKey PKey) { final String S_ProcName = "readBuff"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName, "Transaction not open"); } try { Connection cnx = schema.getCnx(); long AddressId = PKey.getRequiredAddressId(); String sql = S_sqlSelectAddressBuff + "WHERE " + "adr.AddressId = " + Long.toString(AddressId) + " "; Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ResultSet resultSet = stmt.executeQuery(sql); if (resultSet.next()) { MSSBamAddressBuff buff = unpackAddressResultSetToBuff(resultSet); if (resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Did not expect multi-buff response, " + resultSet.getRow() + " rows selected"); } return (buff); } else { return (null); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } } public MSSBamAddressBuff[] readAllBuff(MSSBamAuthorization Authorization) { final String S_ProcName = "readAllBuff"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName, "Transaction not open"); } try { Connection cnx = schema.getCnx(); String sql = S_sqlSelectAddressBuff + "ORDER BY " + "adr.AddressId ASC"; Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ResultSet resultSet = stmt.executeQuery(sql); List<MSSBamAddressBuff> buffList = new ArrayList<MSSBamAddressBuff>(); while (resultSet.next()) { MSSBamAddressBuff buff = unpackAddressResultSetToBuff(resultSet); buffList.add(buff); } return (buffList.toArray(new MSSBamAddressBuff[0])); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } } public MSSBamAddressBuff readBuffByIdIdx(MSSBamAuthorization Authorization, long AddressId) { final String S_ProcName = "readBuffByIdIdx"; try { Connection cnx = schema.getCnx(); String sql = S_sqlSelectAddressBuff + "WHERE " + "adr.AddressId = " + Long.toString(AddressId) + " "; Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ResultSet resultSet = stmt.executeQuery(sql); if (resultSet.next()) { MSSBamAddressBuff buff = unpackAddressResultSetToBuff(resultSet); if (resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Did not expect multi-buff response, " + resultSet.getRow() + " rows selected"); } return (buff); } else { return (null); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } } public MSSBamAddressBuff[] readBuffByContactIdx(MSSBamAuthorization Authorization, long ContactId) { final String S_ProcName = "readBuffByContactIdx"; try { Connection cnx = schema.getCnx(); String sql = S_sqlSelectAddressBuff + "WHERE " + "adr.ContactId = " + Long.toString(ContactId) + " " + "ORDER BY " + "adr.AddressId ASC"; Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ResultSet resultSet = stmt.executeQuery(sql); List<MSSBamAddressBuff> buffList = new ArrayList<MSSBamAddressBuff>(); while (resultSet.next()) { MSSBamAddressBuff buff = unpackAddressResultSetToBuff(resultSet); buffList.add(buff); } return (buffList.toArray(new MSSBamAddressBuff[0])); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } } public MSSBamAddressBuff readBuffByUDescrIdx(MSSBamAuthorization Authorization, long ContactId, String Description) { final String S_ProcName = "readBuffByUDescrIdx"; try { Connection cnx = schema.getCnx(); String sql = S_sqlSelectAddressBuff + "WHERE " + "adr.ContactId = " + Long.toString(ContactId) + " " + "AND " + "adr.Description = " + MSSBamPg8Schema.getQuotedString(Description) + " "; Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ResultSet resultSet = stmt.executeQuery(sql); if (resultSet.next()) { MSSBamAddressBuff buff = unpackAddressResultSetToBuff(resultSet); if (resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Did not expect multi-buff response, " + resultSet.getRow() + " rows selected"); } return (buff); } else { return (null); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } } public void updateAddress(MSSBamAuthorization Authorization, MSSBamAddressBuff Buff) { final String S_ProcName = "updateAddress"; try { Connection cnx = schema.getCnx(); long AddressId = Buff.getRequiredAddressId(); long ContactId = Buff.getRequiredContactId(); String Description = Buff.getRequiredDescription(); String AddrLine1 = Buff.getOptionalAddrLine1(); String AddrLine2 = Buff.getOptionalAddrLine2(); String City = Buff.getOptionalCity(); String State = Buff.getOptionalState(); String Country = Buff.getOptionalCountry(); String PostalCode = Buff.getOptionalPostalCode(); int Revision = Buff.getRequiredRevision(); MSSBamAddressBuff readBuff = readBuffByIdIdx(Authorization, AddressId); int oldRevision = readBuff.getRequiredRevision(); if (oldRevision != Revision) { throw CFLib.getDefaultExceptionFactory().newCollisionDetectedException(getClass(), S_ProcName, Buff); } int newRevision = Revision + 1; String sql = "UPDATE mssbam110.Address " + "SET " + "AddressId = " + MSSBamPg8Schema.getInt64String(AddressId) + ", " + "ContactId = " + MSSBamPg8Schema.getInt64String(ContactId) + ", " + "Description = " + MSSBamPg8Schema.getQuotedString(Description) + ", " + "AddrLine1 = " + ((AddrLine1 != null) ? MSSBamPg8Schema.getQuotedString(AddrLine1) : "null") + ", " + "AddrLine2 = " + ((AddrLine2 != null) ? MSSBamPg8Schema.getQuotedString(AddrLine2) : "null") + ", " + "City = " + ((City != null) ? MSSBamPg8Schema.getQuotedString(City) : "null") + ", " + "State = " + ((State != null) ? MSSBamPg8Schema.getQuotedString(State) : "null") + ", " + "Country = " + ((Country != null) ? MSSBamPg8Schema.getQuotedString(Country) : "null") + ", " + "PostalCode = " + ((PostalCode != null) ? MSSBamPg8Schema.getQuotedString(PostalCode) : "null") + ", " + "Revision = " + Integer.toString(newRevision) + " " + "WHERE " + "AddressId = " + Long.toString(AddressId) + " " + "AND " + "Revision = " + Integer.toString(Revision); Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); int rowsAffected = stmt.executeUpdate(sql); if (rowsAffected != 1) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Expected 1 row to be affected by update, not " + rowsAffected); } Buff.setRequiredRevision(newRevision); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } } public void deleteAddress(MSSBamAuthorization Authorization, MSSBamAddressBuff Buff) { final String S_ProcName = "deleteAddress"; try { Connection cnx = schema.getCnx(); long AddressId = Buff.getRequiredAddressId(); long ContactId = Buff.getRequiredContactId(); String Description = Buff.getRequiredDescription(); String AddrLine1 = Buff.getOptionalAddrLine1(); String AddrLine2 = Buff.getOptionalAddrLine2(); String City = Buff.getOptionalCity(); String State = Buff.getOptionalState(); String Country = Buff.getOptionalCountry(); String PostalCode = Buff.getOptionalPostalCode(); int Revision = Buff.getRequiredRevision(); MSSBamAddressBuff readBuff = readBuffByIdIdx(Authorization, AddressId); int oldRevision = readBuff.getRequiredRevision(); if (oldRevision != Revision) { throw CFLib.getDefaultExceptionFactory().newCollisionDetectedException(getClass(), S_ProcName, Buff); } String sql = "DELETE FROM mssbam110.Address " + "WHERE " + "AddressId = " + Long.toString(AddressId) + " " + "AND " + "Revision = " + Integer.toString(Revision); ; Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); int rowsAffected = stmt.executeUpdate(sql); if (rowsAffected != 1) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "Expected 1 row to be affected by delete, not " + rowsAffected); } } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } } public MSSBamCursor openAddressCursorAll(MSSBamAuthorization Authorization) { String sql = "SELECT " + "adr.AddressId, " + "adr.ContactId, " + "adr.Description, " + "adr.AddrLine1, " + "adr.AddrLine2, " + "adr.City, " + "adr.State, " + "adr.Country, " + "adr.PostalCode, " + "adr.Revision " + "FROM mssbam110.Address AS adr " + "ORDER BY " + "adr.AddressId ASC"; MSSBamCursor cursor = new MSSBamPg8Cursor(Authorization, schema, sql); return (cursor); } public MSSBamCursor openAddressCursorByContactIdx(MSSBamAuthorization Authorization, long ContactId) { String sql = "SELECT " + "adr.AddressId, " + "adr.ContactId, " + "adr.Description, " + "adr.AddrLine1, " + "adr.AddrLine2, " + "adr.City, " + "adr.State, " + "adr.Country, " + "adr.PostalCode, " + "adr.Revision " + "FROM mssbam110.Address AS adr " + "WHERE " + "adr.ContactId = " + Long.toString(ContactId) + " " + "ORDER BY " + "adr.AddressId ASC"; MSSBamCursor cursor = new MSSBamPg8Cursor(Authorization, schema, sql); return (cursor); } public void closeAddressCursor(MSSBamCursor Cursor) { try { Cursor.getResultSet().close(); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), "closeAddressCursor", e); } } public MSSBamAddressBuff nextAddressCursor(MSSBamCursor Cursor) { final String S_ProcName = "nextAddressCursor"; try { ResultSet resultSet = Cursor.getResultSet(); if (!resultSet.next()) { throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName, "No more results available"); } MSSBamAddressBuff buff = unpackAddressResultSetToBuff(resultSet); return (buff); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } } public MSSBamAddressBuff prevAddressCursor(MSSBamCursor Cursor) { int targetRowIdx = (Cursor.getRowIdx() > 1) ? Cursor.getRowIdx() - 1 : 1; MSSBamAddressBuff buff = null; if (Cursor.getRowIdx() >= targetRowIdx) { Cursor.reset(); } while (Cursor.getRowIdx() < targetRowIdx) { buff = nextAddressCursor(Cursor); } return (buff); } public MSSBamAddressBuff firstAddressCursor(MSSBamCursor Cursor) { int targetRowIdx = 1; MSSBamAddressBuff buff = null; Cursor.reset(); while (Cursor.getRowIdx() < targetRowIdx) { buff = nextAddressCursor(Cursor); } return (buff); } public MSSBamAddressBuff lastAddressCursor(MSSBamCursor Cursor) { throw CFLib.getDefaultExceptionFactory().newNotImplementedYetException(getClass(), "lastAddressCursor"); } public MSSBamAddressBuff nthAddressCursor(MSSBamCursor Cursor, int Idx) { int targetRowIdx = Idx; MSSBamAddressBuff buff = null; if (Cursor.getRowIdx() >= targetRowIdx) { Cursor.reset(); } while (Cursor.getRowIdx() < targetRowIdx) { buff = nextAddressCursor(Cursor); } return (buff); } }