Re: FW: Fw: [Springframework-developer] insert functionality in jdbc package

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Hi Dmitry,

Can you "donate" this code to spring? The only thing I'd change is to return a long instead of an int. Seems silly to have to redo basically the same thing. 
I could then provide an implementation something for mySQL.

If the answer is yes, please email me the code, so that I don't have to retype everything.
I'd also move it to the jdbc spring package.

Isabelle

On Wed, Apr 16, 2003 at 12:19:48PM -0400, Kopylenko, Dmitry wrote:
> 
> 
> -----Original Message-----
> From: Rod Johnson [mailto:rod...@in...] 
> Sent: Wednesday, April 16, 2003 11:57 AM
> To: Kopylenko, Dmitry
> Subject: Re: Fw: [Springframework-developer] insert functionality in
> jdbc package
> 
> 
> Dmitry,
> 
> I think you sent this only to me, not the list (the reply-to thing gets me
> often, too).
> 
> Regards,
> Rod
> 
> ----- Original Message -----
> From: "Kopylenko, Dmitry" <dko...@su...>
> To: "'Rod Johnson'" <rod...@in...>
> Sent: Wednesday, April 16, 2003 2:29 PM
> Subject: RE: Fw: [Springframework-developer] insert functionality in jdbc
> package
> 
> 
> > Rod,
> >
> > here is what I've implemented for the purpose of generating key values for
> > our projects here at Rutgers. It's simple, but it works (We're are an
> Oracle
> > shop).
> >
> > 1) Here is the main abstraction:
> >
> > package edu.rutgers.acs.commons.helpers.dao;
> >
> > import com.interface21.dao.DataAccessException;
> >
> > /**
> >  * Interface that defines contract of incrementing
> >  * any data store field's maximum value. Works much like
> >  * sequence number generator. Typical implementations could use
> >  * RDBMS SQL and/or Stored Procedures to do the job.
> >  * @author Dmitriy Kopylenko
> >  * @version $Id: DataFieldMaxValueIncrementer.java,v 1.1 2003/02/28
> 16:32:44
> > dkopylen Exp $
> >  */
> > public interface DataFieldMaxValueIncrementer {
> >
> > /**
> > * Increments data store field's max value as int
> > * @return int next data store value such as <b>max + 1</b>
> > * @throws DataAccessException
> > */
> > public int nextIntValue() throws DataAccessException;
> >
> > /**
> > * Increments data store field's max value as String
> > * @return String next data store value such as <b>max + 1</b>
> > * @throws DataAccessException
> > */
> > public String nextStringValue() throws DataAccessException;
> >
> > }
> >
> > 2) Abstract implementation:
> >
> > package edu.rutgers.acs.commons.helpers.dao;
> >
> > import com.interface21.dao.DataAccessException;
> >
> > /**
> >  * Implementation of {@link DataFieldMaxValueIncrementer}
> >  * Uses <b>Template Method</b> design pattern.
> >  * Subclasses should provide implementations of protected abstract
> methods.
> >  * @author Dmitriy Kopylenko
> >  * @version $Id: AbstractDataFieldMaxValueIncrementer.java,v 1.4
> 2003/03/05
> > 13:44:10 dkopylen Exp $
> >  */
> > public abstract class AbstractDataFieldMaxValueIncrementer implements
> > DataFieldMaxValueIncrementer {
> >
> > /**
> > * Template method
> > * @see
> >
> edu.rutgers.acs.commons.helpers.dao.DataFieldMaxValueIncrementer#nextIntValu
> > e()
> > */
> > public final int nextIntValue() throws DataAccessException {
> > return incrementIntValue();
> > }
> >
> > /**
> > * Template method
> > * @see
> >
> edu.rutgers.acs.commons.helpers.dao.DataFieldMaxValueIncrementer#nextStringV
> > alue()
> > */
> > public final String nextStringValue() throws DataAccessException {
> > return incrementStringValue();
> > }
> >
> > /**
> > * Subclasses should provide implementation
> > * @return int
> > */
> > protected abstract int incrementIntValue();
> >
> > /**
> > * Subclasses should provide implementation.
> > * @return String
> > */
> > protected abstract String incrementStringValue();
> > }
> >
> > 3) Oracle sequence [generic] implementation. It is a JavaBean and it is
> > usually set as a property on the object that
> > requires it through the bean factory:
> >
> > package edu.rutgers.acs.commons.helpers.dao;
> >
> > import javax.sql.DataSource;
> >
> > import com.interface21.beans.factory.InitializingBean;
> > import com.interface21.jdbc.core.DataSourceUtils;
> > import com.interface21.jdbc.object.SqlFunction;
> >
> > /**
> >  * Class to inceremnet maximum value of a given Oracle SEQUENCE
> >  * @author Dmitriy Kopylenko
> >  * @version $Id: OracleSequenceMaxValueIncrementer.java,v 1.3 2003/04/04
> > 18:15:17 dkopylen Exp $
> >  */
> > public class OracleSequenceMaxValueIncrementer
> > extends AbstractDataFieldMaxValueIncrementer
> > implements InitializingBean {
> >
> > //-----------------------------------------------------------------
> > // Instance data
> > //-----------------------------------------------------------------
> > private DataSource ds;
> >
> > private String dsName;
> >
> > private String sequenceName;
> >
> > private boolean prefixWithZero;
> >
> > private NextMaxValueProvider nextMaxValueProvider;
> >
> > //-----------------------------------------------------------------
> > // Constructors
> > //-----------------------------------------------------------------
> > public OracleSequenceMaxValueIncrementer() {
> > this.nextMaxValueProvider = new NextMaxValueProvider();
> > }
> >
> > /**
> > * @see
> >
> edu.rutgers.acs.commons.helpers.dao.AbstractDataFieldMaxValueIncrementer#inc
> > rementIntValue()
> > */
> > protected int incrementIntValue() {
> > return nextMaxValueProvider.getNextIntValue();
> > }
> >
> > /**
> > * @see
> >
> edu.rutgers.acs.commons.helpers.dao.AbstractDataFieldMaxValueIncrementer#inc
> > rementStringValue()
> > */
> > protected String incrementStringValue() {
> > return nextMaxValueProvider.getNextStringValue();
> > }
> >
> > // Private class that does the actual
> > // job of getting the sequence.nextVal value
> > private class NextMaxValueProvider {
> >
> > public String getNextStringValue() {
> > String s = new Integer(nextIntValue()).toString();
> >
> > if (s.length() == 1) {
> > if (prefixWithZero)
> > return "0" + s;
> > }
> >
> > return s;
> > }
> >
> > public int getNextIntValue() {
> > SqlFunction sqlf = new SqlFunction(ds, "SELECT " +
> > sequenceName + ".NEXTVAL FROM DUAL");
> > sqlf.compile();
> > return sqlf.run();
> > }
> > }
> >
> > /**
> > * Sets the dsName.
> > * @param dsName The dsName to set
> > */
> > public void setDsName(String dsName) {
> > this.dsName = dsName;
> > }
> >
> > /**
> > * @see
> > com.interface21.beans.factory.InitializingBean#afterPropertiesSet()
> > */
> > public void afterPropertiesSet() throws Exception {
> > if (dsName == null || sequenceName == null)
> > throw new Exception("dsName, sequenceName properties
> > must be set on " + getClass().getName());
> >
> > this.ds = DataSourceUtils.getDataSourceFromJndi(dsName);
> > }
> >
> > /**
> > * Sets the prefixWithZero.
> > * @param prefixWithZero The prefixWithZero to set
> > */
> > public void setPrefixWithZero(boolean prefixWithZero) {
> > this.prefixWithZero = prefixWithZero;
> > }
> >
> > /**
> > * Sets the sequenceName.
> > * @param sequenceName The sequenceName to set
> > */
> > public void setSequenceName(String sequenceName) {
> > this.sequenceName = sequenceName;
> > }
> > }
> >
> > I guess that there could be different implementations for different DBMS
> > platforms.
> >
> > Regards,
> >
> > Dmitriy.
> >
> > -----Original Message-----
> > From: Rod Johnson [mailto:rod...@in...]
> > Sent: Wednesday, April 16, 2003 09:11 AM
> > To: Isabelle Muszynski; spr...@li...
> > Subject: Re: Fw: [Springframework-developer] insert functionality in
> > jdbc package
> >
> >
> > Isabelle,
> >
> > You make a valid point about not wanting to need another product for just
> > one problem.
> >
> > My view is that
> > - we don't want to get into full-blown O/R mapping
> > - BUT there is scope in building more JDBC-based functionality and
> > abstractions on top of the Spring JDBC way of doing things. To my mind the
> > central value proposition of Spring JDBC is the exception hierarchy and
> way
> > it frees developers of the tedious and error-prone tasks of raw JDBC.
> > Additional features can validly be layered on top.
> >
> > So I think you should propose how you think we should add the insert
> > functionality, perhaps even implement it, so we can discuss it and
> > incorporate it if we're all comfortable with it. I think it's
> > - important that any abstraction shouldn't impose potential inefficiency,
> > and should allow the use of efficient RDBMS functions like Oracle
> sequences
> > - important that it should be compatible with JDBC 3.0 moving forward.
> >
> > Thomas, do you have any thoughts on this?
> >
> > Regards,
> > Rod
> >
> > > Personally, I don't think I'd want to commit to yet another framework
> such
> > as hibernate for ex. in the project at work we're planning to do with
> > spring. Most of what we need for jdbc is in spring, except for the insert
> > functionality. Even with oracle, I don't like the idea of having to write
> a
> > stored procedure for every single insert, I'd probably opt for the
> > additional cost of a select next_val from dual in jdbc code (i.e. instead
> of
> > just doing an insert, first retrieve the key val then do the insert). The
> > main problem is with db's that don't have sequences, and if we agree to
> live
> > with holes in the primary key sequences we can implement an algorithm
> where
> > some sort of key manager per VM reserves a range of key values per table.
> > >
> > > Isabelle
> >
> >
> >
> >
> >
> > -------------------------------------------------------
> > This sf.net email is sponsored by:ThinkGeek
> > Welcome to geek heaven.
> > http://thinkgeek.com/sf
> > _______________________________________________
> > Springframework-developer mailing list
> > Spr...@li...
> > https://lists.sourceforge.net/lists/listinfo/springframework-developer
> 
> 
> 
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> Springframework-developer mailing list
> Spr...@li...
> https://lists.sourceforge.net/lists/listinfo/springframework-developer
> 
> 

-- 
Isabelle Muszynski
Software Engineer
Zandweellaan 4
2660 Antwerpen
Belgium
Tel. 32-(0)3-830 18 54
Mobile: 32-(0)485 49 50 89
Email: isa...@me...
Website: www.meta-logix.com