empire-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "mailinglist@j-b-s.de" <mailingl...@j-b-s.de>
Subject Re: Batch inserts
Date Wed, 10 Oct 2012 11:51:13 GMT
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