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 12:02:09 GMT
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