I have a PreparedStatement with alot of values to insert into an
Oracle Database.
Anyway to shorten the setString lines in a loop or any other way to
shorten this??
//Database connection part here
//......
private PreparedStatement stmt;
public void houseMethod(BeanInfo theObject)
{
stmt = connection.prepareStatement("Insert into MainTable
(house, zipcode, city, county, phone, mortgage, tax, insurance, state,
land) values (?,?,?,?,?,?,?,?,?,?)");
stmt.setString(1, theObject.getHouse());
stmt.setString(2, theObject.getZipcode());
stmt.setString(3, theObject.getCity());
stmt.setString(4, theObject.getCounty());
stmt.setString(5, theObject.getPhone());
stmt.setString(6, theObject.getMortgage());
stmt.setString(7, theObject.getTax());
stmt.setString(8, theObject.getInsurance());
stmt.setString(9, theObject.getState());
stmt.setString(10, theObject.getLand());
stmt.executeUpdate();
}
Daniel Pitts - 13 Nov 2007 00:57 GMT
> I have a PreparedStatement with alot of values to insert into an
> Oracle Database.
[quoted text clipped - 22 lines]
> stmt.executeUpdate();
> }
You might look into using some ORM technology, such as Hibernate or
ibatis. Other than that, you don't really have a way to "shorten" it. It
doesn't look to long anyway.

Signature
Daniel Pitts' Tech Blog: <http://virtualinfinity.net/wordpress/>
Arne Vajhøj - 13 Nov 2007 00:58 GMT
> I have a PreparedStatement with alot of values to insert into an
> Oracle Database.
[quoted text clipped - 22 lines]
> stmt.executeUpdate();
> }
It is beautiful easily readable code ! Why change it ?
(if you want another approach you will need to look at an
O/R mapper tool)
Arne
RedGrittyBrick - 13 Nov 2007 11:03 GMT
> I have a PreparedStatement with alot of values to insert into an
> Oracle Database.
[quoted text clipped - 22 lines]
> stmt.executeUpdate();
> }
I suppose if you implemented a `String getIndexedString(int index)` in
theObject's class, you could use a for loop.
It feels a bit brittle to me. I'm sure this idea deserves to be shot
down in flames :-)
Hmmm,
String[] columnNames = { "house", "zipcode" ... };
String sql = "insert into MainTable (" + makeList(coumnNames) + ...
for (int i = 1; i <= columnNames.length; i++) {
stmt.setString(i, theObject.getNamedString(columnNames[i-1]));
}
I'll get my coat.
Chris ( Val ) - 13 Nov 2007 13:46 GMT
On Nov 13, 10:03 pm, RedGrittyBrick <RedGrittyBr...@SpamWeary.foo>
wrote:
> franca...@yahoo.com wrote:
> > I have a PreparedStatement with alot of values to insert into an
[quoted text clipped - 29 lines]
> It feels a bit brittle to me. I'm sure this idea deserves to be shot
> down in flames :-)
Well, let's put it this way:
If we applied the "Occam's Razor" principle and consulted
the person responsible for maintaining that code, I think
that the original piece of code would be a clear winner.
Cleverness can sometimes cause a maintainability nightmare.
I think the first approach is quite clear and concise, though
I would reconsider the names of the identifiers :-)
> Hmmm,
> String[] columnNames = { "house", "zipcode" ... };
[quoted text clipped - 4 lines]
>
> I'll get my coat.
Hey, it's the thought that counts :-)
--
Chris
Roedy Green - 13 Nov 2007 22:25 GMT
> stmt.setString(1, theObject.getHouse());
> stmt.setString(2, theObject.getZipcode());
[quoted text clipped - 7 lines]
> stmt.setString(10, theObject.getLand());
> stmt.executeUpdate();
you could create a method that uses String ... to shorten it to this:
setParm( o.getHouse(), o.getZipcode() etc );
it gets passed a String[] which it then loops through calling
stmt.setString( i+1, s[i] );
You could create a method of theObject that returned an array of all
its fields.
then you could say
setParm( o.everything() );

Signature
Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com