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