package org.julp;import javax.naming.*;import javax.sql.*;import java.sql.*;import java.util.List;import java.util.Properties;/** * * @author  leonard */public class DBServices extends Object implements java.io.Serializable, Cloneable {        protected transient Context context;    protected transient DataSource dataSource;    protected transient Connection connection;    protected boolean connectionClosed = false;    protected PreparedStatement preparedStatement;    protected transient ResultSet resultSet;    protected Statement statement;    protected boolean useDataSource = true;    protected String dataSourceName = null;    protected Properties connectionProperties = null;        protected boolean escapeProcessing = true;    protected int maxFieldSize = 0;    protected int maxRows = 0;    protected int queryTimeout = 0;    protected int fetchDirection = 0;    protected int fetchSize = 0;    //TYPE_FORWARD_ONLY = 1003, TYPE_SCROLL_INSENSITIVE = 1004, TYPE_SCROLL_SENSITIVE = 1005    protected int resultSetType = ResultSet.TYPE_FORWARD_ONLY;    //CONCUR_READ_ONLY, CONCUR_UPDATABLE    protected int concurrency = ResultSet.CONCUR_READ_ONLY;    protected boolean readOnly = false;    protected boolean closeStatementAfterExecute = true;        /** Holds value of property driverName. */    private String driverName;        /** Holds value of property dbUrl. */    private String dbUrl;        /** Holds value of property userName. */    private String userName;        /** Holds value of property password. */    private String password;        /* How many rows to update/insert/delete in transaction */    protected int maxRowsToProccesInTransaction = 0;    protected int rowsAffectedInTransaction = 0;    protected String connectionId = "";    protected boolean tranInProccess = false;        protected boolean debug = false;            /** Creates new DBServices */    public DBServices() {        if (Boolean.getBoolean("debug-julp") || Boolean.getBoolean("debug-" + getClass().getName())){            debug = true;        }            }        /** Getter for property context.     * @return Value of property context.     *     */    public javax.naming.Context getContext(){        try{            context = new InitialContext();            if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::getContext()::context: " + context + " \n");        }catch (NamingException ne){            throw new RuntimeException(ne);        }        return context;    }        /** Setter for property context.     * @param context New value of property context.     *     */    public void setContext(javax.naming.Context context) {        this.context = context;    }        /** Getter for property connection.     * @return Value of property connection.     *     */    public java.sql.Connection getConnection()  throws SQLException{        if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::getConnection()::connection 1: " + connection + "::isTranInProccess(): " + tranInProccess + " \n");                if (isTranInProccess()){            //if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::getConnection()::isTranInProccess()::connection.toString(): " + connection.toString() + " \n");            return this.connection;        }        //if (connection == null || connection.isClosed()){        if (connection == null || this.isConnectionClosed()){ //Sybase bug workaround            if (isUseDataSource() == true){                dataSource = this.getDataSource();                if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "getConnection()::dataSource 1: " + dataSource + " \n");                connection = dataSource.getConnection();                //connection.rollback();                if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "getConnection()::connection 2: " + connection + " \n");            }else{                try {                    Class.forName(getDriverName());                    if (this.connectionProperties == null || this.connectionProperties.isEmpty()){                        connection = DriverManager.getConnection(getDbUrl(), getUserName(), getPassword());                    }else{                        connection = DriverManager.getConnection(getDbUrl(), this.connectionProperties);                    }                }catch (ClassNotFoundException ex) {                    ex.printStackTrace();                    throw new RuntimeException(ex);                }catch (SQLException sqle) {                    sqle.printStackTrace();                    throw new RuntimeException(sqle);                }catch (Exception e) {                    e.printStackTrace();                    throw new RuntimeException(e);                }            }        }        if (isTranInProccess()){            if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::getConnection()::isTranInProccess(): " + isTranInProccess() + "::connectionId: " + connectionId + "\n");            //if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::connection.toString(): " + connection.toString() + " \n");            //if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::this.connectionId.equals(connection.toString(): " + this.connectionId.equals(connection.toString()) + " \n");            if (!this.connectionId.equals(connection.toString())){                //if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::connectionId: " + connectionId + " \n");                throw new SQLException("Connection was closed while in transaction");            }        }        return connection;    }        /** Setter for property connection.     * @param connection New value of property connection.     *     */    public void setConnection(java.sql.Connection connection) {        this.connection = connection;    }        /** Getter for property dataSource.     * @return Value of property dataSource.     *     */    public javax.sql.DataSource getDataSource() {        try{            dataSource = (DataSource) this.getContext().lookup("java:comp/env/jdbc/" + getDataSourceName());            if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::getDataSource()::dataSource: " + dataSource + " \n");        }catch (NamingException ne){            throw new RuntimeException(ne.getMessage());        }        return dataSource;    }        /** Setter for property dataSource.     * @param dataSource New value of property dataSource.     *     */    public void setDataSource(javax.sql.DataSource dataSource) {        this.dataSource = dataSource;    }        /** Getter for property preparedStatement.     * @return Value of property preparedStatement.     *     */    public java.sql.PreparedStatement getPreparedStatement(String sql, List parms) throws SQLException{        preparedStatement = this.getConnection().prepareStatement(sql, getResultSetType(), getConcurrency());        if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::getPreparedStatement(): " + preparedStatement + "::sql: " + sql + " parms: " + parms + " \n");                preparedStatement.setEscapeProcessing(this.getEscapeProcessing());        preparedStatement.setFetchSize(this.getFetchSize());        preparedStatement.setMaxRows(this.getMaxRows());        preparedStatement.setMaxFieldSize(this.getMaxFieldSize());        preparedStatement.setQueryTimeout(this.getQueryTimeout());        //ParameterMetaData pmd = preparedStatement.getParameterMetaData();        for (int i = 0;i < parms.size();i ++){            int parmIdx = i + 1;            Object param = parms.get(i);            if (param instanceof java.util.Date){                param = new java.sql.Date( ((java.util.Date) param).getTime() );            }            if (param != null){                preparedStatement.setObject(parmIdx, param);            }else{                preparedStatement.setNull(parmIdx, Types.JAVA_OBJECT);                //preparedStatement.setNull(parmIdx, pmd.getParameterType(parmIdx));            }        }        return preparedStatement;    }        /** Setter for property preparedStatement.     * @param preparedStatement New value of property preparedStatement.     *     */    public void setPreparedStatement(java.sql.PreparedStatement preparedStatement) {        this.preparedStatement = preparedStatement;    }        /** Getter for property resultSet.     * @return Value of property resultSet.     *     */    public java.sql.ResultSet getResultSet(String sql) throws SQLException{        resultSet = this.getStatement().executeQuery(sql);        if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::getResultSet()::sql: " + sql + " \n");        return resultSet;    }        public java.sql.ResultSet getResultSet(String sql, List parms) throws SQLException{        resultSet = this.getPreparedStatement(sql, parms).executeQuery();        if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::getResultSet(): " + resultSet + " \n");        return resultSet;    }        public int execute(String sql) throws SQLException{        int rows = -1;        try{            rows = this.getStatement().executeUpdate(sql);        }finally{            if (isCloseStatementAfterExecute()){                if (statement != null){                    statement.close();                    statement = null;                }                            }        }                this.setRowsAffectedInTransaction(rowsAffectedInTransaction + rows);        if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::execute()::sql: " + sql + "::affectedRows: " + rows + "\n");                return rows;    }        public int execute(String sql, List parms) throws SQLException{        int rows = -1;        try{            rows = this.getPreparedStatement(sql, parms).executeUpdate();        }finally{            if (isCloseStatementAfterExecute()){                                if (preparedStatement != null){                    preparedStatement.close();                    preparedStatement = null;                }            }        }        this.setRowsAffectedInTransaction(rowsAffectedInTransaction + rows);        if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::execute()::sql: " + sql + " parms: " + parms + "::affectedRows: " + rows + "\n");                return rows;    }        /** Setter for property resultSet.     * @param resultSet New value of property resultSet.     *     */    public void setResultSet(java.sql.ResultSet resultSet) {        this.resultSet = resultSet;    }        /** Getter for property statement.     * @return Value of property statement.     *     */    public java.sql.Statement getStatement() throws SQLException{        //        statement = this.getConnection().createStatement();        statement = this.getConnection().createStatement(getResultSetType(), getConcurrency());        statement.setEscapeProcessing(this.getEscapeProcessing());        statement.setFetchSize(this.getFetchSize());        statement.setMaxRows(this.getMaxRows());        statement.setMaxFieldSize(this.getMaxFieldSize());        statement.setQueryTimeout(this.getQueryTimeout());        return statement;    }        /** Setter for property statement.     * @param statement New value of property statement.     *     */    public void setStatement(java.sql.Statement statement) {        this.statement = statement;    }        public void release(boolean closeConnection) throws SQLException{        if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::=============== RELEASE ============ closeConnection: " + closeConnection + " \n");        if (resultSet != null){            resultSet.close();            resultSet = null;        }        if (statement != null){            statement.close();            statement = null;        }        if (preparedStatement != null){            preparedStatement.close();            preparedStatement = null;        }        if (connection != null){            if (closeConnection == true){                //connection.rollback();//?? Sybase bug                connection.close();                setConnectionClosed(true);                connection = null;                this.setTranInProccess(false);                                this.setConnectionId("");                            }else{                //do nothing            }        }    }        public void beginTran() throws SQLException{                if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::=============== BEGIN TRAN ============ previous connectionId: " + connectionId + " \n");        this.setRowsAffectedInTransaction(0);        this.getConnection().setAutoCommit(false);        this.setConnectionId(this.connection.toString());        this.setTranInProccess(true);           if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::=============== BEGIN TRAN ============ current connectionId: " + connectionId + " \n");    }        public void commitTran() throws SQLException{        if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::=============== COMMIT TRAN ============= current connectionId: " + connectionId + " \n");        this.getConnection().commit();        if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::=============== COMMITTED ================\n");        //this.getConnection().setAutoCommit(true);//?? Sybase bug        //if (debug) System.out.println("setAutoCommit(true)");        this.setTranInProccess(false);        //if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::=============== COMMIT TRAN ============= after setTranInProccess(false) connectionId: " + connectionId + " \n");        this.setConnectionId("");        //if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::=============== COMMIT TRAN ============= after setConnectionId(\"\") connectionId: " + connectionId + " \n");    }        public void rollbackTran() throws SQLException{        if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::=============== ROLLBACK TRAN ============= connectionId: " + connectionId + " \n");        this.getConnection().rollback();        if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::=============== AFTER ROLLBACK =========== connectionId: " + connectionId + " \n");        this.getConnection().setAutoCommit(true);        this.setTranInProccess(false);        //if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::=============== ROLLBACK TRAN ============= after setTranInProccess(false) connectionId: " + connectionId + " \n");        this.setConnectionId("");        //if (debug) System.out.println("julp ============= " + new java.util.Date() + " " + this.getClass() + "::=============== ROLLBACK TRAN ============= after setConnectionId(\"\") connectionId: " + connectionId + " \n");    }        /** Getter for property useDataSource.     * @return Value of property useDataSource.     *     */