db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From arm...@apache.org
Subject cvs commit: db-ojb/src/java/org/apache/ojb/broker/platforms Platform.java PlatformDefaultImpl.java PlatformMsSQLServerImpl.java
Date Wed, 02 Apr 2003 22:56:37 GMT
arminw      2003/04/02 14:56:36

  Modified:    xdocs    sequencemanager.xml
               src/java/org/apache/ojb/broker/util/sequence
                        SequenceManagerStoredProcedureImpl.java
               src/java/org/apache/ojb/broker/platforms Platform.java
                        PlatformDefaultImpl.java
                        PlatformMsSQLServerImpl.java
  Log:
  update new sequence manager implementation,
  fix connection leak (hope), move CallableStatement
  build to Platform classes
  
  Revision  Changes    Path
  1.7       +5 -5      db-ojb/xdocs/sequencemanager.xml
  
  Index: sequencemanager.xml
  ===================================================================
  RCS file: /home/cvs/db-ojb/xdocs/sequencemanager.xml,v
  retrieving revision 1.6
  retrieving revision 1.7
  diff -u -r1.6 -r1.7
  --- sequencemanager.xml	1 Apr 2003 14:06:11 -0000	1.6
  +++ sequencemanager.xml	2 Apr 2003 22:56:36 -0000	1.7
  @@ -340,24 +340,24 @@
   The <code>SequenceManagerStoredProcedureImpl</code> implementation enabled
database
   sequence key generation in a <i>Oracle-style</i> for all databases (e.g. MSSQL,
MySQL, DB2, ...).
   <br/>
  -First add a new table <code>OJB_NEXTVAL</code> to
  +First add a new table <code>OJB_NEXTVAL_SEQ</code> to
   your database.
   <source>
  -CREATE TABLE OJB_NEXTVAL
  +CREATE TABLE OJB_NEXTVAL_SEQ
   (
       SEQ_NAME    VARCHAR(150) NOT NULL,
       MAX_KEY     INTEGER,
       CONSTRAINT SYS_PK_OJB_NEXTVAL PRIMARY KEY(SEQ_NAME)
   )
   </source>
  -You will also need a stored procedure called <code>ojb_nextval</code>
  +You will also need a stored procedure called <code>ojb_nextval_proc</code>
   that will take care of giving you a guaranteed unique
   sequence number.
   <br/>
   Here is an example for the stored procedure you need to
   use sequencing for MSSQL server:
   <source>
  -CREATE PROCEDURE ojb_nextval
  +CREATE PROCEDURE OJB_NEXTVAL_PROC
       @SEQ_NAME varchar(150)
       AS
       declare @MAX_KEY BIGINT
  @@ -365,7 +365,7 @@
       -- so we will know if someone truncates the table
       set @MAX_KEY = 0
   
  -    UPDATE OJB_NEXTVAL
  +    UPDATE OJB_NEXTVAL_SEQ
           SET    @MAX_KEY = MAX_KEY = MAX_KEY + 1
           WHERE  SEQ_NAME = @SEQ_NAME
   
  
  
  
  1.2       +101 -57   db-ojb/src/java/org/apache/ojb/broker/util/sequence/SequenceManagerStoredProcedureImpl.java
  
  Index: SequenceManagerStoredProcedureImpl.java
  ===================================================================
  RCS file: /home/cvs/db-ojb/src/java/org/apache/ojb/broker/util/sequence/SequenceManagerStoredProcedureImpl.java,v
  retrieving revision 1.1
  retrieving revision 1.2
  diff -u -r1.1 -r1.2
  --- SequenceManagerStoredProcedureImpl.java	1 Apr 2003 14:09:39 -0000	1.1
  +++ SequenceManagerStoredProcedureImpl.java	2 Apr 2003 22:56:36 -0000	1.2
  @@ -56,6 +56,7 @@
   
   import org.apache.commons.lang.SystemUtils;
   import org.apache.ojb.broker.PersistenceBroker;
  +import org.apache.ojb.broker.platforms.PlatformException;
   import org.apache.ojb.broker.accesslayer.LookupException;
   import org.apache.ojb.broker.metadata.ClassDescriptor;
   import org.apache.ojb.broker.metadata.FieldDescriptor;
  @@ -67,6 +68,7 @@
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.sql.Types;
  +import java.sql.Connection;
   
   /**
    * This solution will give those seeking an oracle-style
  @@ -76,14 +78,14 @@
    * sequence key generation for all databases (e.g. MSSQL, MySQL, DB2, ...)
    * with a <b>JDBC 2.0</b> compliant driver.
    * <br/>
  - * First add a new table <code>OJB_NEXTVAL</code> to
  + * First add a new table <code>OJB_NEXTVAL_SEQ</code> to
    * your database.
    * <pre>
  - * CREATE TABLE OJB_NEXTVAL
  + * CREATE TABLE OJB_NEXTVAL_SEQ
    * (
    *     SEQ_NAME    VARCHAR(150) NOT NULL,
    *     MAX_KEY     BIGINT,
  - *     CONSTRAINT SYS_PK_OJB_NEXTVAL PRIMARY KEY(SEQ_NAME)
  + *     CONSTRAINT SYS_PK_OJB_NEXTVAL_SEQ PRIMARY KEY(SEQ_NAME)
    * )
    * </pre>
    * You will also need the stored procedure OJB_NEXTVAL
  @@ -91,16 +93,16 @@
    * sequence number, in multi server environments.
    * <br/>
    * <pre>
  - * CREATE PROCEDURE ojb_nextval @SEQ_NAME varchar(100)
  + * CREATE PROCEDURE ojb_nextval_proc @SEQ_NAME varchar(100)
    *              AS
    *		declare @MAX_KEY BIGINT
    *              -- return an error if sequence does not exist
    *              -- so we will know if someone truncates the table
    *              set @MAX_KEY = 0
    *
  - *              UPDATE OJB_NEXTVAL
  + *              UPDATE OJB_NEXTVAL_SEQ
    *              SET    @MAX_KEY = MAX_KEY = MAX_KEY + 1
  - *              WHERE  TABLENAME = @SEQ_NAME
  + *              WHERE  SEQ_NAME = @SEQ_NAME
    *
    *		if @MAX_KEY = 0
    *			select 1/0
  @@ -159,19 +161,26 @@
   public class SequenceManagerStoredProcedureImpl extends AbstractSequenceManager
   {
       private Logger log = LoggerFactory.getLogger(SequenceManagerStoredProcedureImpl.class);
  -    protected static final String PROCEDURE_NAME = "ojb_nextval";
  +    protected static final String PROCEDURE_NAME = "ojb_nextval_proc";
       protected static final String SEQ_NAME_STRING = "SEQ_NAME";
       protected static final String SEQ_ID_STRING = "MAX_KEY";
  -    protected static final String SEQ_TABLE_NAME = "OJB_NEXTVAL";
  +    protected static final String SEQ_TABLE_NAME = "OJB_NEXTVAL_SEQ";
   
       /**
  -     *
  +     * Constructor
  +     * @param broker
        */
       public SequenceManagerStoredProcedureImpl(PersistenceBroker broker)
       {
           super(broker);
       }
   
  +    /**
  +     * Insert syntax for our special table
  +     * @param sequenceName
  +     * @param maxKey
  +     * @return
  +     */
       protected String sp_createSequenceQuery(String sequenceName, long maxKey)
       {
           String result = "insert into " + SEQ_TABLE_NAME + " ("
  @@ -180,77 +189,104 @@
           return result;
       }
   
  -    protected String sp_buildProcedureString(String sequenceName)
  -    {
  -        return "{ call " + PROCEDURE_NAME + " (?, ?)" + "}";
  -    }
  -
  +    /**
  +     * Gets the actual key
  +     *
  +     * Will create a new row with the max key of table if it
  +     * does not exist.
  +     * @param field
  +     * @return
  +     * @throws SequenceManagerException
  +     */
       protected long getUniqueLong(FieldDescriptor field) throws SequenceManagerException
       {
  +        boolean needsCommit = false;
           long result = 0;
           /*
           arminw:
  -        we obtain a new PB instance to make
  +        use the associated broker instance, check if broker was in tx or
  +        we need to commit used connection.
           */
           PersistenceBroker targetBroker = getBrokerForClass();
  -        // lookup sequence name
  -        String sequenceName = SequenceManagerHelper.buildSequenceName(getBrokerForClass(),
field);
  -        try
  +        if(!targetBroker.isInTransaction())
           {
  -            result = buildNextSequence(targetBroker, field.getClassDescriptor(), sequenceName);
  -            /*
  -            if 0 was returned we assume that the stored procedure
  -            did not work properly.
  -            */
  -            if (result == 0)
  -            {
  -                throw new SequenceManagerException("No incremented value retrieved");
  -            }
  +            targetBroker.beginTransaction();
  +            needsCommit = true;
           }
  -        catch (Exception e)
  +        try
           {
  -            // maybe the sequence was not created
  -            log.info("Could not grap next key, message was " + e.getMessage() +
  -                    " - try to write a new sequence entry to database");
  +            // lookup sequence name
  +            String sequenceName = SequenceManagerHelper.buildSequenceName(targetBroker,
field);
               try
               {
  -                // on create, make sure to get the max key for the table first
  -                long maxKey = SequenceManagerHelper.getMaxForExtent(getBrokerForClass(),
field);
  -                createSequence(targetBroker, field, sequenceName, maxKey);
  +                result = buildNextSequence(targetBroker, field.getClassDescriptor(), sequenceName);
  +                /*
  +                if 0 was returned we assume that the stored procedure
  +                did not work properly.
  +                */
  +                if (result == 0)
  +                {
  +                    throw new SequenceManagerException("No incremented value retrieved");
  +                }
               }
  -            catch (Exception e1)
  +            catch (Exception e)
               {
  -                String eol = SystemUtils.LINE_SEPARATOR;
  -                throw new SequenceManagerException(eol + "Could not grab next id, failed
with " + eol +
  -                        e.getMessage() + eol + "Creation of new sequence failed with "
+
  -                        eol + e1.getMessage() + eol, e1);
  +                // maybe the sequence was not created
  +                log.info("Could not grab next key, message was " + e.getMessage() +
  +                        " - try to write a new sequence entry to database");
  +                try
  +                {
  +                    // on create, make sure to get the max key for the table first
  +                    long maxKey = SequenceManagerHelper.getMaxForExtent(targetBroker, field);
  +                    createSequence(targetBroker, field, sequenceName, maxKey);
  +                }
  +                catch (Exception e1)
  +                {
  +                    String eol = SystemUtils.LINE_SEPARATOR;
  +                    throw new SequenceManagerException(eol + "Could not grab next id, failed
with " + eol +
  +                            e.getMessage() + eol + "Creation of new sequence failed with
" +
  +                            eol + e1.getMessage() + eol, e1);
  +                }
  +                try
  +                {
  +                    result = buildNextSequence(targetBroker, field.getClassDescriptor(),
sequenceName);
  +                }
  +                catch (Exception e1)
  +                {
  +                    throw new SequenceManagerException("Could not grab next id although
a sequence seems to exist", e);
  +                }
               }
  -            try
  -            {
  -                result = buildNextSequence(targetBroker, field.getClassDescriptor(), sequenceName);
  -            }
  -            catch (Exception e1)
  +        }
  +        finally
  +        {
  +            if(targetBroker != null && needsCommit)
               {
  -                throw new SequenceManagerException("Could not grab next id, sequence seems
to exist", e);
  +                targetBroker.commitTransaction();
               }
           }
           return result;
       }
   
  +    /**
  +     * Calls the stored procedure stored procedure throws an
  +     * error if it doesn't exist.
  +     * @param broker
  +     * @param cld
  +     * @param sequenceName
  +     * @return
  +     * @throws LookupException
  +     * @throws SQLException
  +     */
       protected long buildNextSequence(PersistenceBroker broker, ClassDescriptor cld, String
sequenceName)
  -            throws LookupException, SQLException
  +            throws LookupException, SQLException, PlatformException
       {
           CallableStatement cs = null;
           try
           {
  -            String sp = "{ call " + PROCEDURE_NAME + " (?, ?)}";
  -            cs = broker.serviceConnectionManager().
  -                    getConnection().prepareCall(sp);
  -            cs.setString(1, sequenceName);
  -            cs.registerOutParameter(2, Types.INTEGER);
  +            Connection con = broker.serviceConnectionManager().getConnection();
  +            cs = getPlatform().prepareNextValProcedureStatement(con, PROCEDURE_NAME, sequenceName);
               cs.executeUpdate();
  -            // cs.getLong(2);
  -            return cs.getLong(2);
  +            return cs.getLong(1);
           }
           finally
           {
  @@ -265,19 +301,27 @@
           }
       }
   
  +    /**
  +     * Creates new row in table
  +     * @param brokerOther
  +     * @param field
  +     * @param sequenceName
  +     * @param maxKey
  +     * @throws Exception
  +     */
       protected void createSequence(PersistenceBroker broker, FieldDescriptor field,
                                     String sequenceName, long maxKey) throws Exception
       {
           Statement stmt = null;
           try
           {
  -            stmt = getBrokerForClass().serviceStatementManager().getGenericStatement(field.getClassDescriptor(),
Query.NOT_SCROLLABLE);
  +            stmt = broker.serviceStatementManager().getGenericStatement(field.getClassDescriptor(),
Query.NOT_SCROLLABLE);
               stmt.execute(sp_createSequenceQuery(sequenceName, maxKey));
           }
           catch (Exception e)
           {
  -            e.printStackTrace();
  -            throw new SequenceManagerException("Could create new row in OJB_HL_SEQ table
- TABLENAME=" +
  +            log.error(e);
  +            throw new SequenceManagerException("Could not create new row in "+SEQ_TABLE_NAME+"
table - TABLENAME=" +
                       sequenceName + " field=" + field.getColumnName(), e);
           }
           finally
  
  
  
  1.13      +20 -1     db-ojb/src/java/org/apache/ojb/broker/platforms/Platform.java
  
  Index: Platform.java
  ===================================================================
  RCS file: /home/cvs/db-ojb/src/java/org/apache/ojb/broker/platforms/Platform.java,v
  retrieving revision 1.12
  retrieving revision 1.13
  diff -u -r1.12 -r1.13
  --- Platform.java	30 Jan 2003 15:12:59 -0000	1.12
  +++ Platform.java	2 Apr 2003 22:56:36 -0000	1.13
  @@ -61,6 +61,7 @@
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
  +import java.sql.CallableStatement;
   
   /**
    * this interface provides callbacks that allow to perform
  @@ -166,4 +167,22 @@
        * @return a sql string to drop a sequence
        */
       public String dropSequenceQuery(String sequenceName);
  +
  +    /**
  +     * Create stored procedure call for a special sequence manager implementation
  +     * {@link org.apache.ojb.broker.util.sequence.SequenceManagerStoredProcedureImpl},
  +     * because it seems that jdbc-driver differ in handling of CallableStatement.
  +     * <br/>
  +     * Note: The out-parameter of the stored procedure must be registered at
  +     * first position, because lookup for new long id in the implementation:
  +     * <br/>
  +     * <pre>
  +     * Connection con = broker.serviceConnectionManager().getConnection();
  +     * cs = getPlatform().prepareNextValProcedureStatement(con, PROCEDURE_NAME, sequenceName);
  +     * cs.executeUpdate();
  +     * return cs.getLong(1);
  +     * </pre>
  +     */
  +    public CallableStatement prepareNextValProcedureStatement(Connection con, String procedureName,
  +                                                              String sequenceName) throws
PlatformException;
   }
  
  
  
  1.13      +8 -0      db-ojb/src/java/org/apache/ojb/broker/platforms/PlatformDefaultImpl.java
  
  Index: PlatformDefaultImpl.java
  ===================================================================
  RCS file: /home/cvs/db-ojb/src/java/org/apache/ojb/broker/platforms/PlatformDefaultImpl.java,v
  retrieving revision 1.12
  retrieving revision 1.13
  diff -u -r1.12 -r1.13
  --- PlatformDefaultImpl.java	9 Jan 2003 17:01:33 -0000	1.12
  +++ PlatformDefaultImpl.java	2 Apr 2003 22:56:36 -0000	1.13
  @@ -68,6 +68,7 @@
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.sql.Types;
  +import java.sql.CallableStatement;
   
   /**
    * This class is a concrete implementation of <code>Platform</code>. Provides
default implementations for all
  @@ -298,5 +299,12 @@
       {
           /*default implementation does not support this*/
           throw new UnsupportedOperationException("This feature is not supported by this
implementation");
  +    }
  +
  +    public CallableStatement prepareNextValProcedureStatement(Connection con, String procedureName,
  +                                                              String sequenceName) throws
PlatformException
  +    {
  +        /*@todo implementation*/
  +        throw new UnsupportedOperationException("Not supported by this implementation");
       }
   }
  
  
  
  1.4       +23 -1     db-ojb/src/java/org/apache/ojb/broker/platforms/PlatformMsSQLServerImpl.java
  
  Index: PlatformMsSQLServerImpl.java
  ===================================================================
  RCS file: /home/cvs/db-ojb/src/java/org/apache/ojb/broker/platforms/PlatformMsSQLServerImpl.java,v
  retrieving revision 1.3
  retrieving revision 1.4
  diff -u -r1.3 -r1.4
  --- PlatformMsSQLServerImpl.java	2 Oct 2002 04:18:46 -0000	1.3
  +++ PlatformMsSQLServerImpl.java	2 Apr 2003 22:56:36 -0000	1.4
  @@ -1,5 +1,10 @@
   package org.apache.ojb.broker.platforms;
   
  +import java.sql.CallableStatement;
  +import java.sql.Connection;
  +import java.sql.Types;
  +import java.sql.SQLException;
  +
   /* ====================================================================
    * The Apache Software License, Version 1.1
    *
  @@ -73,5 +78,22 @@
       public byte getJoinSyntaxType()
       {
           return SQL92_NOPAREN_JOIN_SYNTAX;
  +    }
  +
  +    public CallableStatement prepareNextValProcedureStatement(Connection con, String procedureName,
  +                                                              String sequenceName) throws
PlatformException
  +    {
  +        try
  +        {
  +            String sp = "{?= call " + procedureName + " (?)}";
  +            CallableStatement cs = con.prepareCall(sp);
  +            cs.registerOutParameter(1, Types.INTEGER);
  +            cs.setString(2, sequenceName);
  +            return cs;
  +        }
  +        catch (SQLException e)
  +        {
  +            throw new PlatformException(e);
  +        }
       }
   }
  
  
  

Mime
View raw message