empire-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <doeb...@esteam.de>
Subject re: Batch inserts
Date Thu, 11 Oct 2012 08:41:11 GMT
Oops, I was a bit too quick with my answer:
Adding an additional constructor to DBCommand won't help, as it is an abstract class and gets
instantiated by the driver.

But generally I like the idea of being able to choose on a "per command" basis.
I will have a closer look and think of a solution.

Regards
Rainer


> from: Rainer Döbele
> to: user@empire-db.apache.org
> re: re: Batch inserts
> 
> Hi all,
> 
> Yes, I do think we need both prepared and unprepared statements.
> In fact I am "pro choice" ;-)
> 
> And I have no objection in adding a second ctor to DBCommand as suggested
> by Jens.
> In fact I think this is a very good idea.
> The only thing I would change is removing the underscore in the name of
> private member field as this is not our code style.
> 
> I will create an issue and check this is and add the additional ctor, so it will go
> in our current release, that we want to publish soon.
> Objections from anyone?
> 
> Regards
> Rainer
> 
> 
> > from: Francis De Brabandere [mailto:francisdb@gmail.com]
> > to: user@empire-db.apache.org
> > re: Re: Batch inserts
> >
> > Hi Jens,
> >
> > I agree ont the need for flexibility, was just thinking out loud why
> > you would not want a prepared statement...
> >
> > For my part this change is ok, but I'd like to hear Rainer's opinion.
> > Can you create a ticket with patch at
> > https://issues.apache.org/jira/browse/EMPIREDB ?
> >
> > We plan to make a release in the near future so it would be nice if
> > your modification was in there...
> >
> > Cheers,
> > F
> >
> > On 10 October 2012 14:37, mailinglist@j-b-s.de <mailinglist@j-b-s.de>
> wrote:
> > > Hi F!
> > >
> > > We have different where clauses and frequently changing numbers of
> > parameters thus prepared statements usually will not gain any
> > advantages. I just suggest a more fine grained selection concerning
> > prepared statement usage in contrast to "all or nothing".
> > >
> > > One more thing: from a logical point of view I can argue why is the
> > datebase object responsibe to decide how sql is created (as statement
> > or prepared statement)? To me this is part of the code using a
> > DBCommand's sql. It's perfectly fine to allow setting a default, but
> > why not allowing to override the default when needed without risking
> > side-effects while changing the global flag on DBDatabase?
> > >
> > > But you are perfectly right, it's more about beautifying it's
> > > definitely not a
> > bug or severe issue at all.
> > >
> > >
> > > Jens
> > >
> > >
> > > Von meinem iPad gesendet
> > >
> > > Am 10.10.2012 um 14:02 schrieb Francis De Brabandere
> > <francisdb@gmail.com>:
> > >
> > >> Why not use prepared statements for everything? Is there a
> > drawback/limitation?
> > >>
> > >> http://stackoverflow.com/questions/3385177/best-practices-with-prep
> > >> ar edstatements-when-to-and-when-not-to
> > >>
> > >> Cheers,
> > >> F
> > >>
> > >> On 10 October 2012 13:51, mailinglist@j-b-s.de
> > >> <mailinglist@j-b-s.de>
> > wrote:
> > >>> Hi Guys!
> > >>>
> > >>> Finally I made progress creating batch commands via empire
> > >>> DBCommands in a generic way to feed batchable prepared
> statements.
> > >>> Unfortunately there is a drawback I want to discuss with you guys.
> > >>>
> > >>> Most annoying (to me) is that creation of PreparedStatement sql's
> > >>> depends on a boolean flag set on the database object itself. This
> > >>> either means toggling the flag depending on usage or creating two
> > >>> database instances having same set of tables. I suggest to allow
> > >>> passing a bool param to a second dbcommand ctor to control
> > >>> creation of "normal value based statements" or "questionmark based
> > >>> prepared statements" independently from DBDatabase which won't
> > >>> break any
> > existing code from my understanding.
> > >>>
> > >>>
> > >>> Thus I suggest to add a field in DBCommand class like private
> > >>> final boolean _usePreparedStatement;
> > >>>
> > >>> The existing ctor will look like
> > >>> public DBCommand(final DBDatabase db) {
> > >>>   _usePreparedStatement = db.isPreparedStatement;
> > >>>   ...
> > >>> }
> > >>>
> > >>> Adding a second CTOR like
> > >>> public DBCommand(final DBDatabase db, final boolean
> > >>> usePreparedStatement) {  _usePreparedStatement =
> > >>> usePreparedStatement;  ...
> > >>> }
> > >>>
> > >>> And a last small change is required in method "useCmdParam" to
> > >>> make use of the newly introduced boolean member instead of "db".
> > >>>
> > >>>
> > >>> What you think?
> > >>>
> > >>> Jens
> > >>>
> > >>>
> > >>> Von meinem iPad gesendet
> > >>>
> > >>> Am 31.08.2012 um 18:00 schrieb "mailinglist@j-b-s.de"
> > >>> <mailinglist@j-b-s.de>:
> > >>>
> > >>> Hi chris!
> > >>>
> > >>> I solved it this way
> > >>>
> > >>>
> > >>>
> > >>> final DBSQLScript script =3D new DBSQLScript();
> > >>>
> > >>> loop>>
> > >>>       final DBCommand dbCommand =3D _db.createCommand();
> > >>>       dbCommand.set(new DBSetExpr(column1, value1));
> > >>>       ...
> > >>>       dbCommand.set(new DBSetExpr(columnN, valueN));
> > >>>       script.addStmt(dbCommand.getInsert());
> > >>> <<loop
> > >>>
> > >>> script.run(_db.getDriver(), cDestinationWrite, false);
> > >>> script.clear();
> > >>>
> > >>> What you think?
> > >>>
> > >>> Jens
> > >>>
> > >>>
> > >>>
> > >>> Von meinem iPad gesendet
> > >>>
> > >>> Am 30.08.2012 um 18:38 schrieb Christopher Richmond
> > >>> <crichmond@referentia.com>:
> > >>>
> > >>> I was thinking of doing something very similar to this, since I
> > >>> think one of the most useful things about the library is the SQL
> > >>> generation, sparing other developers from having to deal with
> > >>> string butchering which is error prone, then allow them to pass
> > >>> the objects in to which I will simply extract the query string to execute
> via pure JDBC.
> > >>>
> > >>> I also am taking a look at the source code and if i feel I can
> > >>> modify a patch to allow batch inserts, I will submit it back.
> > >>>
> > >>> Thanks!
> > >>> Chris
> > >>>
> > >>> On Wed, Aug 29, 2012 at 11:43 PM, Rainer Döbele
> > <doebele@esteam.de> wrote:
> > >>>>
> > >>>> Hi Christopher,
> > >>>>
> > >>>>
> > >>>>
> > >>>> currently there is no support for Batch-Inserts in Empire-DB.
> > >>>>
> > >>>>
> > >>>>
> > >>>> However you can combine direct JDBC and Empire-DB and e.g. let
> > >>>> Empire-DB create the SQL Command string for the Prepared
> > statement.
> > >>>>
> > >>>> In order to use Prepared Statement params use
> > >>>> DBCommand.addParam(DataType type, Object value).
> > >>>>
> > >>>> See SampleAdvApp.commandParamsSample(...) method for an
> > example.
> > >>>>
> > >>>>
> > >>>>
> > >>>> If you find a good generic solution for this that is reusable for
> > >>>> other projects, we would be happy to receive your code in order
> > >>>> to provide this feature with an future release of Emprie-db.
> > >>>>
> > >>>>
> > >>>>
> > >>>> Regards
> > >>>>
> > >>>> Rainer
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>> Von: Christopher Richmond [mailto:crichmond@referentia.com]
> > >>>> Gesendet: Donnerstag, 30. August 2012 04:51
> > >>>> An: user
> > >>>> Betreff: Batch inserts
> > >>>>
> > >>>>
> > >>>>
> > >>>> With pure JDBC I can do a PreparedStamement(ps) with batches for
> > >>>> inserting large numbers of rows(millions) with my embedded H2
> > >>>> database.  This works fine(along with setting autocommit OFF on
> > >>>> my
> > >>>> connection)
> > >>>>
> > >>>>        int count = 0;
> > >>>>
> > >>>>
> > >>>>
> > >>>>        for(int x = 1; x <= totalRows; x++){
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>          for(<each item of data I have, up to millions>){
> > >>>>
> > >>>>            pst.setInt(colIndex, rowIndex);
> > >>>>
> > >>>>          }
> > >>>>
> > >>>>
> > >>>>
> > >>>>          pst.addBatch();
> > >>>>
> > >>>>          if(++count % batchSize == 0) {
> > >>>>
> > >>>>            pst.executeBatch();   //execute batches at specified
invervals
> > >>>> (batchSize)
> > >>>>
> > >>>>
> > >>>>
> > >>>>          }
> > >>>>
> > >>>>
> > >>>>
> > >>>>        }
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>        pst.executeBatch(); // insert remaining records
> > >>>>
> > >>>>
> > >>>>
> > >>>>        pst.close();
> > >>>>
> > >>>>
> > >>>>
> > >>>> but I am now trying to use EmpireDB and it is unclear if I can
do
> > >>>> batch inserts against the database usinge the EmpireDB api.  Is
> > >>>> this possible and is there sample code for how to configure or
> > >>>> execute against the API do this?
> > >>>>
> > >>>>
> > >>>>
> > >>>> In summary, I want batch insertion for large sets of
> > >>>> data(millions of rows), executing batches of inserts at regular
> > >>>> intervals like I was doing with pure JDBC above.
> > >>>>
> > >>>>
> > >>>>
> > >>>> Thanks,
> > >>>>
> > >>>> Chris
> > >>>
> > >>>

Mime
View raw message