Estou precisando de opnioes e se possivel ajuda…
Bom, minha aplicaçao esta estruturada assim:
MVC(Struts) --> BusinessDelegate -->EJB - Session Facade --> BusinessObject -->DAOFactory – > DAO(JDBC)
Meu application server é o WebLogic 8.1, tenho dois DataSources um para Oracle e outro para DB2. OK?
Estou tendo problemas com as conexoes dos DAOs,
Criei uma classe BaseDAO que sever como superclasse dos meus DAO mais especificos, exemplo:
TransportadoraDAO extends BaseDAO
Segue o codigo do BaseDAO e do TransportadoraDAO:
// BaseDAO
package br.empresa.wap.base;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import br.empresa.wap.exception.ConnectionFailureException;
import br.empresa.wap.exception.DAOException;
import br.empresa.wap.exception.DataIntegrityViolationException;
import br.empresa.wap.exception.SQLTransactionException;
import br.empresa.wap.locator.Locator;
import br.empresa.wap.locator.LocatorService;
import br.empresa.wap.util.Logging;
import br.empresa.wap.util.Util;
/**
* @author cjunior1
*
*/
public class BaseDAO {
private Connection conn;
public Logging logging;
public BaseDAO(){
logging = Logging.getInstance();
}
/**
* Faz a conversao dos tipos dos paramatros e atribui ao PreparedStatement
* @param pstmt PreparedStatement
* @param params Object[]
* @throws SQLException
* @author cjunior1
*/
private void setParams(final PreparedStatement pstmt,final Object[] params) throws SQLException{
for(int i=0;i<params.length;i++) {
int n = i + 1;
if(params[i] == null) {
pstmt.setNull(n,Types.VARCHAR);
}
else if(params[i] instanceof Byte) {
pstmt.setByte(n,((Byte)params[i]).byteValue());
}
else if(params[i] instanceof Short) {
pstmt.setShort(n,((Short)params[i]).shortValue());
}
else if(params[i] instanceof Integer) {
pstmt.setInt(n,((Integer)params[i]).intValue());
}
else if(params[i] instanceof Long) {
pstmt.setLong(n,((Long)params[i]).longValue());
}
else if(params[i] instanceof Float) {
pstmt.setFloat(n,((Float)params[i]).floatValue());
}
else if(params[i] instanceof Double) {
pstmt.setDouble(n,((Double)params[i]).doubleValue());
}
else if(params[i] instanceof BigDecimal) {
pstmt.setBigDecimal(n,(BigDecimal)params[i]);
}
else if(params[i] instanceof BigInteger) {
pstmt.setBigDecimal(n,new BigDecimal((BigInteger)params[i]));
}
else if(params[i] instanceof Boolean) {
pstmt.setBoolean(n,((Boolean)params[i]).booleanValue());
}
else if(params[i] instanceof Date) {
pstmt.setDate(n,new java.sql.Date(((Date)params[i]).getTime()));
}
else if(params[i] instanceof Time) {
pstmt.setTime(n,(Time)params[i]);
}
else if(params[i] instanceof Timestamp) {
pstmt.setTimestamp(n,(Timestamp)params[i]);
}
else {
pstmt.setString(n,params[i].toString());
}
}
}
/**
* Retorna uma List obtida atraves de um ResultSet
* @param rs ResultSet
* @return List
* @throws SQLException
* @author cjunior1
*/
private List getList(ResultSet rs) throws SQLException {
ArrayList list = new ArrayList();
int columnCount = rs.getMetaData().getColumnCount();
while(rs.next()) {
Object[] row = new Object[columnCount];
for(int i=0;i<columnCount;i++)
row[i] = rs.getObject(i+1);
list.add(row);
}
return list;
}
/**
* Retorna uma conexao disponivel no DataSource, usado para transacoes autocommit == false
* @param dsName Nome JNDI do DataSource
* @return Connection
* @throws SQLException
* @author cjunior1
* @throws ConnectionFailureException
*/
private Connection getConnection(String dsName) {
try {
conn = Locator.getInstance().getDataSource(dsName).getConnection();
conn.setAutoCommit(false);
return conn;
}
catch(SQLException sqle) {
logging.logError("BaseDAO.getConnection() : Problemas para obter conexão do DataSource " + dsName,sqle);
throw new ConnectionFailureException(sqle);
}
}
/**
* Fecha a conexao caso ela exista ou esteja aberta, usado para autocommit == true
* @throws SQLException
* @author cjunior1
* @throws ConnectionFailureException
*/
private void closeConnection(Connection cnx) {
try {
if(cnx != null)
cnx.close();
}
catch(SQLException sqle) {
logging.logError("BaseDAO.closeConnection(conn) : Problemas para fechar a conexão",sqle);
throw new ConnectionFailureException(sqle);
}
}
/**
* Fecha a conexao caso ela exista ou esteja aberta
* @throws SQLException
* @author cjunior1
* @throws ConnectionFailureException
*/
public void closeConnection() {
try {
if(this.conn != null)
this.conn.close();
}
catch(SQLException sqle) {
logging.logError("BaseDAO.closeConnection() : Problemas para fechar a conexão",sqle);
throw new ConnectionFailureException(sqle);
}
}
/**
* Realiza o commit da transacao
* @throws SQLException
* @author cjunior1
* @throws SQLTransactionException
*/
public void setCommit() {
try {
if(conn != null)
conn.commit();
}
catch(SQLException sqle) {
logging.logError("BaseDAO.setCommit() : Problemas no commit da transação",sqle);
throw new SQLTransactionException(sqle);
}
}
/**
* Realiza o rollback da transacao
* @throws SQLException
* @author cjunior1
* @throws SQLTransactionException
*/
public void setRollback() {
try {
if(conn != null)
conn.rollback();
}
catch(SQLException sqle) {
logging.logError("BaseDAO.setRollback() : Problemas no rollback da transação",sqle);
throw new SQLTransactionException(sqle);
}
}
/**
* Executa um comando SQL-DML na base dados
* @param cmd Comando SQL
* @param params Paramatros do comando SQL
* @param dsName Nome JNDI do DataSource
* @param autocommit Ativa Autocommit
* @throws DAOException
* @author cjunior1
*/
private void execute(String cmd, Object[] params, String dsName, boolean autocommit) throws DataIntegrityViolationException {
try {
conn = getConnection(dsName);
//conn.setAutoCommit(autocommit);
try {
// verifica se o SQL contem parametros (?)
if(cmd.indexOf("?") < 0 || params == null || params.length == 0) {
Statement stmt = conn.createStatement();
try {
int rows = stmt.executeUpdate(cmd);
if(autocommit)
conn.commit();
}
finally {
stmt.close();
stmt = null;
}
}
else {
PreparedStatement pstmt = conn.prepareStatement(cmd);
try {
setParams(pstmt,params);
int rows = pstmt.executeUpdate();
if(autocommit)
conn.commit();
}
finally {
pstmt.close();
pstmt = null;
}
}
}
finally {
if(autocommit){
closeConnection(conn);
//releaseConnection();
}
//closeConnection(conn);
}
}
catch(SQLException sqle) {
if(Util.isDataIntegrityViolationException(sqle))
throw new DataIntegrityViolationException();
logging.logError("BaseDAO.execute() : Erro ao executar o comando SQL : " + cmd,sqle);
throw new DAOException(sqle);
}
}
/**
* Executa um comando SQL-DML na base dados IBM-DB2
* @param cmd Comando SQL
* @param params Parametros do comando SQL
* @param autocommit Ativa Autocommit
* @throws DAOException
* @author cjunior1
*/
public void executeDB2(String cmd, Object[] params, boolean autocommit) throws DataIntegrityViolationException {
execute(cmd,params,LocatorService.DB2_DS_NAME,autocommit);
}
/**
* Executa um comando SQL-DML na base dados Oracle
* @param cmd Comando SQL
* @param params Parametros do comando SQL
* @param autocommit Ativa Autocommit
* @throws DAOException
* @author cjunior1
*/
public void executeOracle(String cmd, Object[] params, boolean autocommit) throws DataIntegrityViolationException {
execute(cmd,params,LocatorService.DATASOURCE_NAME,autocommit);
}
/**
* Retorna uma lista com resultado do comando SELECT
* @param cmd Comando SELECT
* @param params Parametros do SELECT
* @param dsName Nome JNDI do DataSource
* @return List
* @throws DAOException
* @author cjunior1
*/
private List select(String cmd, Object[] params, String dsName) throws DAOException {
try {
Connection cnx = getConnection(dsName);
try {
if(cmd.indexOf("?") < 0 || params == null || params.length == 0) {
Statement stmt = cnx.createStatement();
try {
ResultSet rs = stmt.executeQuery(cmd);
try {
return getList(rs);
}
finally {
rs.close();
rs = null;
}
}
finally {
stmt.close();
stmt = null;
}
}
else {
PreparedStatement pstmt = cnx.prepareStatement(cmd);
try {
setParams(pstmt,params);
ResultSet rs = pstmt.executeQuery();
try {
return getList(rs);
}
finally {
rs.close();
rs = null;
}
}
finally {
pstmt.close();
pstmt = null;
}
}
}
finally {
closeConnection(cnx);
//cnx = null;
}
}
catch(Exception e) {
logging.logError("BaseDAO.select() : Erro ao executar o comando SELECT : " + cmd,e);
throw new DAOException(e);
}
}
/**
* Retorna uma lista com resultado do comando SELECT do banco de dados Oracle
* @param cmd Comando SELECT
* @param params Parametros do SELECT
* @return List
* @throws DAOException
* @author cjunior1
*/
public List selectOracle(String cmd, Object[] params) throws DAOException {
return select(cmd,params,LocatorService.DATASOURCE_NAME);
}
/**
* Retorna uma lista com resultado do comando SELECT do banco de dados IBM-DB2
* @param cmd Comando SELECT
* @param params Parametros SELECT
* @return List
* @throws DAOException
* @author cjunior1
*/
public List selectDB2(String cmd, Object[] params) throws DAOException {
return select(cmd,params,LocatorService.DB2_DS_NAME);
}
/**
* Libera a conn para Garbage Collection
*
* @author cjunior1
*/
public void releaseConnection() {
conn = null;
}
}
// transportationDAO
package br.empresa.wap.dao;
import br.empresa.wap.base.BaseDAO;
import br.empresa.wap.exception.DAOException;
import br.empresa.wap.exception.DataIntegrityViolationException;
import br.empresa.wap.model.TranspUserTO;
import br.empresa.wap.model.TransportationTO;
import br.empresa.wap.model.TransportationVL;
import br.empresa.wap.util.Cast;
import java.util.Iterator;
import java.util.List;
/**
* Classe no padrao DAO para operacoes na tabela WAP012TB_TRANSPORTATIONS
* @author CJUNIOR1 - Carlos Alberto
*/
public class TransportationDAO extends BaseDAO {
public static final String INSERT_CMD = "INSERT INTO WAP012TB_TRANSPORTATIONS(PKNI_012TRANSPORTATION_ID,PKSF_012COUNTRY,ATSV_012NAME,ATND_012RECEIVABLE_VALUE, " +
"ATDT_012FORD_INFO,ATSF_012FORD_USER,ATSF_012INSERT_USER,ATDT_012INSERT_DATE,ATSF_012APPROVED_USER,ATDT_012APPROVED_DATE) " +
"VALUES(?,trim(?),?,?,?,?,?,SYSDATE,?,SYSDATE) ";
public static final String UPDATE_CMD = "UPDATE WAP012TB_TRANSPORTATIONS " +
"SET ATSV_012NAME = ? " +
" ,ATND_012RECEIVABLE_VALUE = ? " +
" ,ATDT_012FORD_INFO = ? " +
" ,ATSF_012FORD_USER = ? " +
" ,ATSF_012INSERT_USER = ? " +
" ,ATDT_012INSERT_DATE = SYSDATE " +
"WHERE PKNI_012TRANSPORTATION_ID = ? " +
"AND trim(PKSF_012COUNTRY) = trim(?) ";
public static final String DELETE_CMD = "DELETE WAP012TB_TRANSPORTATIONS WHERE PKNI_012TRANSPORTATION_ID = ? AND trim(PKSF_012COUNTRY) = trim(?) ";
public static final String SELECT_FILTER_CMD = "SELECT PKNI_012TRANSPORTATION_ID,PKSF_012COUNTRY,upper(ATSV_012NAME),ATND_012RECEIVABLE_VALUE, " +
"trunc(ATDT_012FORD_INFO),ATSF_012FORD_USER,ATSF_012INSERT_USER,trunc(ATDT_012INSERT_DATE),ATSF_012APPROVED_USER, " +
"trunc(ATDT_012APPROVED_DATE) " +
"FROM WAP012TB_TRANSPORTATIONS " +
"WHERE PKNI_012TRANSPORTATION_ID = NVL(?,PKNI_012TRANSPORTATION_ID) " +
"AND ATSV_012NAME = NVL(?,ATSV_012NAME)";
public static final String SELECT_ID_CMD = "SELECT PKNI_012TRANSPORTATION_ID,PKSF_012COUNTRY,upper(ATSV_012NAME),ATND_012RECEIVABLE_VALUE, " +
"ATDT_012FORD_INFO,ATSF_012FORD_USER,ATSF_012INSERT_USER,ATDT_012INSERT_DATE,ATSF_012APPROVED_USER, " +
"ATDT_012APPROVED_DATE " +
"FROM WAP012TB_TRANSPORTATIONS " +
"WHERE PKNI_012TRANSPORTATION_ID = ? " +
"AND trim(PKSF_012COUNTRY) = trim(?) ";
public static final String SELECT_LIST_CMD = "SELECT PKNI_012TRANSPORTATION_ID,PKSF_012COUNTRY,upper(ATSV_012NAME),ATND_012RECEIVABLE_VALUE, " +
"trunc(ATDT_012FORD_INFO),ATSF_012FORD_USER,ATSF_012INSERT_USER,trunc(ATDT_012INSERT_DATE),ATSF_012APPROVED_USER, " +
"trunc(ATDT_012APPROVED_DATE) " +
"FROM WAP012TB_TRANSPORTATIONS ";
public static final String SELECT_USER_CMD = "SELECT PKNI_012TRANSPORTATION_ID,PKSF_012COUNTRY,upper(ATSV_012NAME),ATND_012RECEIVABLE_VALUE, " +
"ATDT_012FORD_INFO,ATSF_012FORD_USER,ATSF_012INSERT_USER,ATDT_012INSERT_DATE,ATSF_012APPROVED_USER, " +
"ATDT_012APPROVED_DATE " +
"FROM WAP012TB_TRANSPORTATIONS, WAP011TB_TRANSPS_USERS " +
"WHERE PKNI_012TRANSPORTATION_ID = FKNI_011TRANSPORTATION_ID " +
"AND upper(PKSF_011USER) = upper(?) " +
"AND trim(PKSF_012COUNTRY) = trim(?) ";
public static final String SELECT_TRANSP_ID_USER = "SELECT WAP012.PKNI_012TRANSPORTATION_ID, WAP012.PKSF_012COUNTRY, upper(WAP012.ATSV_012NAME) " +
"FROM WAP011TB_TRANSPS_USERS WAP011, " +
" WAP012TB_TRANSPORTATIONS WAP012 " +
"WHERE WAP011.FKNI_011TRANSPORTATION_ID = WAP012.PKNI_012TRANSPORTATION_ID " +
"AND WAP011.FKSF_011COUNTRY = WAP012.PKSF_012COUNTRY " +
"AND TRIM(UPPER(WAP011.FKSF_011COUNTRY)) = TRIM(UPPER(?)) " +
"AND TRIM(UPPER(WAP011.PKSF_011USER)) = TRIM(UPPER(?))" ;
public static final String ORDER_SELECT = " ORDER BY 1, 3 ";
/**
* Construtor padrao
*/
public TransportationDAO(){}
/**
* Persiste o objeto TOTransportation no repositorio de dados
* @param to TOTransportation
* @return TOTransportation com seu status
* @throws DataIntegrityViolationException
*/
public void insert(TransportationTO to,boolean commit ) throws DataIntegrityViolationException {
try {
executeOracle(INSERT_CMD,new Object[]{
to.getTranspId(),
to.getCountry(),
to.getName(),
to.getValue(),
to.getFordInfoDate(),
to.getFordCdsid(),
to.getInsertUser(),
to.getApprovedUser() }
,commit);
}
catch(DAOException daoe) {
logging.logError("TransportationDAO.insert() : Problemas para inserir uma Transportadora aprovada",daoe);
throw new DAOException(daoe);
}
}
/**
* Altera os dados persistidos do TOTransportation na base de dados
* @param to TOTransportation
* @return TOTransportaion com seu status
* @throws DataIntegrityViolationException
*/
public void update(TransportationTO to, boolean commit) throws DataIntegrityViolationException {
try {
executeOracle(UPDATE_CMD,new Object[]{
to.getName(),
to.getValue(),
to.getFordInfoDate(),
to.getFordCdsid(),
to.getInsertUser(),
to.getTranspId(),
to.getCountry()}
,commit);
}
catch(DAOException daoe) {
logging.logError("TransportationDAO.update() : Problemas para atualizar uma Transportadora aprovada ",daoe);
throw new DAOException(daoe);
}
}
/**
* Delete os dados persistidos do TOTransportation na base de dados
* @param to TOTranspotation
* @return TOTransporation com seu status
* @throws DataIntegrityViolationException
*/
public void remove(TransportationTO to, boolean commit) throws DataIntegrityViolationException {
try {
executeOracle(DELETE_CMD, new Object[]{to.getTranspId(), to.getCountry()},commit);
}
catch(DAOException daoe) {
logging.logError("TransportationDAO.remove() : Problemas para remover uma Transportadora aprovada",daoe);
throw new DAOException(daoe);
}
}
/**
* Retorna um value list conforme o filtro selecionado
* @param to TransportationTO
* @return TransportationVL
*/
public TransportationVL search(TransportationTO to) {
TransportationVL vl = new TransportationVL();
StringBuffer selectCmd = new StringBuffer(SELECT_FILTER_CMD);
selectCmd.append(ORDER_SELECT);
Object[] params = {to.getTranspId(),to.getName()};
List list = selectOracle(selectCmd.toString(),params);
Iterator i = list.iterator();
while(i.hasNext()) {
Object[] row = (Object[])i.next();
TransportationTO resultTO = new TransportationTO();
resultTO.setTranspId(Cast.toString(row[0]));
resultTO.setCountry(Cast.toString(row[1]));
resultTO.setName(Cast.toString(row[2]));
resultTO.setValue(Cast.toDoubleObj(row[3]));
resultTO.setFordInfoDate(Cast.toDate(row[4]));
resultTO.setFordCdsid(Cast.toString(row[5]));
resultTO.setInsertUser(Cast.toString(row[6]));
resultTO.setInsertDate(Cast.toDate(row[7]));
resultTO.setApprovedUser(Cast.toString(row[8]));
resultTO.setApprovedDate(Cast.toDate(row[9]));
vl.add(resultTO);
}
return vl;
}
/**
* Reatorna uma Value List com a lista completa das transportatoras, um full table scan
* @param to TOTransporation
* @return VLTransportation
*/
public TransportationVL list() {
TransportationVL vl = new TransportationVL();
StringBuffer selectCmd = new StringBuffer(SELECT_LIST_CMD);
selectCmd.append(ORDER_SELECT);
List list = selectOracle(selectCmd.toString(),null);
Iterator i = list.iterator();
while(i.hasNext()) {
Object[] row = (Object[])i.next();
TransportationTO resultTO = new TransportationTO();
resultTO.setTranspId(Cast.toString(row[0]));
resultTO.setCountry(Cast.toString(row[1]));
resultTO.setName(Cast.toString(row[2]));
resultTO.setValue(Cast.toDoubleObj(row[3]));
resultTO.setFordInfoDate(Cast.toDate(row[4]));
resultTO.setFordCdsid(Cast.toString(row[5]));
resultTO.setInsertUser(Cast.toString(row[6]));
resultTO.setInsertDate(Cast.toDate(row[7]));
resultTO.setApprovedUser(Cast.toString(row[8]));
resultTO.setApprovedDate(Cast.toDate(row[9]));
vl.add(resultTO);
}
return vl;
}
/**
* Retorna uma Transfer Object com o resultado da busca pelo ID da transportadora
* @param to TOTransporation
* @return TOTransportation
*/
public TransportationTO searchByID(TransportationTO to) {
TransportationTO resultTO = new TransportationTO();
StringBuffer selectCmd = new StringBuffer(SELECT_ID_CMD);
selectCmd.append(ORDER_SELECT);
Object[] params = {to.getTranspId(), to.getCountry()};
List list = selectOracle(selectCmd.toString(),params);
if(!Cast.isEmpty(list)) {
Object[] row = (Object[])list.get(0);
resultTO.setTranspId(Cast.toString(row[0]));
resultTO.setCountry(Cast.toString(row[1]));
resultTO.setName(Cast.toString(row[2]));
resultTO.setValue(Cast.toDoubleObj(row[3]));
resultTO.setFordInfoDate(Cast.toDate(row[4]));
resultTO.setFordCdsid(Cast.toString(row[5]));
resultTO.setInsertUser(Cast.toString(row[6]));
resultTO.setInsertDate(Cast.toDate(row[7]));
resultTO.setApprovedUser(Cast.toString(row[8]));
resultTO.setApprovedDate(Cast.toDate(row[9]));
}
return resultTO;
}
/**
* Retorna uma Transfer Object com o resultado da busca pelo Usuario da transportadora
* @param to TranspUserTO
* @return TransportationTO
* @author CJUNIOR1
*/
public TransportationTO searchByUser(TranspUserTO to) {
TransportationTO resultTO = new TransportationTO();
StringBuffer selectCmd = new StringBuffer(SELECT_USER_CMD);
selectCmd.append(ORDER_SELECT);
Object[] params = {to.getCdsid(), to.getCountry()};
List list = selectOracle(selectCmd.toString(),params);
if(!Cast.isEmpty(list)) {
Object[] row = (Object[])list.get(0);
resultTO.setTranspId(Cast.toString(row[0]));
resultTO.setCountry(Cast.toString(row[1]));
resultTO.setName(Cast.toString(row[2]));
resultTO.setValue(Cast.toDoubleObj(row[3]));
resultTO.setFordInfoDate(Cast.toDate(row[4]));
resultTO.setFordCdsid(Cast.toString(row[5]));
resultTO.setInsertUser(Cast.toString(row[6]));
resultTO.setInsertDate(Cast.toDate(row[7]));
resultTO.setApprovedUser(Cast.toString(row[8]));
resultTO.setApprovedDate(Cast.toDate(row[9]));
}
return resultTO;
}
/**
*
* @param to
* @return
* @author cyano
*/
public TransportationTO searchByUserID(TranspUserTO to) {
TransportationTO transp = new TransportationTO();
StringBuffer selectCmd = new StringBuffer(SELECT_TRANSP_ID_USER);
selectCmd.append(ORDER_SELECT);
String[] params = {to.getCountry(), to.getCdsid()};
List list = selectOracle(selectCmd.toString(),params);
if(!Cast.isEmpty(list)) {
Object[] row = (Object[])list.get(0);
transp.setTranspId(Cast.toString(row[0]));
transp.setCountry(Cast.toString(row[1]));
transp.setName(Cast.toString(row[2]));
}
return transp;
}
}
Alguns DAOs acessam Oracle e outros DB2, estou tendo probelmas de “vazamento de pool” ou “conection leak”???
Quando eu pego um DAO via DAOFactory e executo um metodo ele pega a conexao do pool, e se for autocommit ele fecha ao final da execucao do metodo, caso contrario e tenho que fechar explicitamente
no BusinessObject, bem como executar o commit.
Neste caso eu não teria como controlar transaçoes com eficiencia, pois cada vez que criar um DAO criarei uma conexao nova e qdo preciso trabalhar com varios DAO em transação em um BO nao estaria comitando corretamente, correto? O que eu devo fazer ou me preocupar???
Devo fornecer a conexao para os DAO quando implemento o metodo do BO?
No caso de transação que envolva 2 DS, devo usar JTA???
Alguem tem alguma opnião/melhoria/correçao a respeito da estrutura BaseDAO <-- XXXDAO???
Sei que poderia ter usado hibernate, spring, JDO, etc…
mas minha realidade é esta, infelismente…
Por favor emitam sua opnião/ajuda/critica/experiencia a respeito, desde já agradeço a todos.
Valeu!
