empire-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Francis De Brabandere <franci...@gmail.com>
Subject Re: Batch inserts
Date Wed, 10 Oct 2012 13:33:38 GMT
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-preparedstatements-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