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:29:06 GMT
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-prepar
> >> 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