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 "[email protected]" <[email protected]>:
> 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
> <[email protected]>:
>
>> 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 <[email protected]> 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:[email protected]]
>> 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
>>
>>
