You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by "Markus Khouri mkhouri_job@comcast.net" <ma...@comcast.net> on 2004/11/18 04:02:25 UTC
[DbUtils] documentation update & overloading BeanHandler constructor
Dear dbutil folks,
I am a newbie to this package. I was really excited because I do not know much about databases and was hoping that this package could hide most of the details from me. (Yes, I know I should understand everything, but I simply do not have the time.)
I spent a pretty good amount of time grappling with how to get this utility to work with my existing database table and my existing JavaBean. The issue I was running into was that the column names did not match the JavaBean property names. I finally found the answer in the forums, but guessing that my situation is not unique, I was wondering if you might possibly add some additional documentation to the Example page, at the end of the "ResultSetHandler Implementations" section. Below I have provided a first draft. Since I do not know what the submission process is, I am willing to work with the editor if need be. (I figured I spent some time with this issue and, if possible, might as well try to save others some time.)
In addition, I am wondering if it might be possible to overload the BeanHandler constructor so that it also takes a boolean argument. This argument would be a flag to indicate if the underscore character ought to be ignored when matching column names to JavaBean properties. This would require several changes, but the biggest change, I think, would occur in the method org.apache.commons.dbutils.BeanProcessor.mapColumnsToProperties(ResultSetMetaData rsmd, PropertyDescriptor[] props). Below I have taken a first cut at the modified implementation.
thanks,
-markus
http://jakarta.apache.org/commons/dbutils/examples.html
--- START draft of additional documentation
Using the BeanHandler potentially adds a new requirement - it requires that the names of the column in the database match the property names defined of the JavaBean. In the above example, that means that if the columns in the database table "Person" were named "PERSON_ID", "NAME", "FIRST_NAME", and "LAST_NAME", then the properties of the JavaBean Person.java must be named "person_id", "name", "first_name", and "last_name". (Note that the case does not have to match.) Such an example is a real world reality in that many DBAs, er, encourage a naming schema that contains only capital letters and where each word is separated by the underscore character.
There are two potential solutions to this issue: (1) re-write the SELECT statement to use the "AS" SQL keyword, or (2) use an overloaded constructor of BeanHandler.
So, given a JavaBean with properties "person_id", "name", "first_name", and "last_name"...
The first solution requires that you change the SQL statement to "SELECT PERSON_ID AS personId, NAME, FIRST_NAME AS firstName, LAST_NAME AS lastName FROM Person WHERE name=?". (Note that the AS keyword is not used with the column named NAME since it matches the property name.)
The second solution requires you to change the line "ResultSetHandler h = new BeanHandler(Person.class);" to "ResultSetHandler h = new BeanHandler(Person.class, true);". (Note passing the boolean value true as the second argument in the constrcutor.)
--- END draft of additional documentation
http://cvs.apache.org/viewcvs.cgi/jakarta-commons/dbutils/src/java/org/apache/commons/dbutils/BeanProcessor.java?rev=1.7&sortdir=down&view=auto
-- START original --
protected int[] mapColumnsToProperties(ResultSetMetaData rsmd,
PropertyDescriptor[] props) throws SQLException {
int cols = rsmd.getColumnCount();
int columnToProperty[] = new int[cols + 1];
Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND);
for (int col = 1; col <= cols; col++) {
String columnName = rsmd.getColumnName(col);
for (int i = 0; i < props.length; i++) {
if (columnName.equalsIgnoreCase(props[i].getName())) {
columnToProperty[col] = i;
break;
}
}
}
return columnToProperty;
}
-- END original --
-- START modified --
protected int[] mapColumnsToProperties(ResultSetMetaData rsmd,
PropertyDescriptor[] props) throws SQLException {
return mapColumnsToProperties(rsmd, props, false);
}
protected int[] mapColumnsToProperties(ResultSetMetaData rsmd,
PropertyDescriptor[] props, boolean ignoreUnderscore) throws SQLException {
int cols = rsmd.getColumnCount();
int columnToProperty[] = new int[cols + 1];
Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND);
for (int col = 1; col <= cols; col++) {
String columnName = rsmd.getColumnName(col);
String columnNameUnderscoreRemoved = columnName.".replace('_', '');
for (int i = 0; i < props.length; i++) {
if (columnName.equalsIgnoreCase(props[i].getName())) {
columnToProperty[col] = i;
break;
} // end of IF
else if (
(ignoreUnderscore) &&
(columnNameUnderscoreRemoved .equalsIgnoreCase(props[i].getName()))
) {
columnToProperty[col] = i;
break;
} // end of ELSE IF
} // end of FOR
}
return columnToProperty;
}
-- END modified --
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [DbUtils] documentation update & overloading BeanHandler constructor
Posted by David Graham <gr...@yahoo.com>.
The reason we made mapColumnsToProperties protected was to allow you to
handle the mapping any way you like including removing underscores so I'm
not to eager to add a boolean parameter to the constructor. I personally
just alias the column names to something reasonable in the sql so I don't
have to mess with the code.
Opening a bugzilla ticket and attaching a cvs diff -u formatted patch for
the documentation is the best way to propose changes.
SQL is case insensitive so you can call the column first_name or
FIRST_name or FIRST_NAME; it doesn't really matter.
David
--- "Markus Khouri (mkhouri_job@comcast.net)" <ma...@comcast.net>
wrote:
> Dear dbutil folks,
>
> I am a newbie to this package. I was really excited because I do not
> know much about databases and was hoping that this package could hide
> most of the details from me. (Yes, I know I should understand
> everything, but I simply do not have the time.)
>
> I spent a pretty good amount of time grappling with how to get this
> utility to work with my existing database table and my existing
> JavaBean. The issue I was running into was that the column names did
> not match the JavaBean property names. I finally found the answer in
> the forums, but guessing that my situation is not unique, I was
> wondering if you might possibly add some additional documentation to the
> Example page, at the end of the "ResultSetHandler Implementations"
> section. Below I have provided a first draft. Since I do not know what
> the submission process is, I am willing to work with the editor if need
> be. (I figured I spent some time with this issue and, if possible,
> might as well try to save others some time.)
>
> In addition, I am wondering if it might be possible to overload the
> BeanHandler constructor so that it also takes a boolean argument. This
> argument would be a flag to indicate if the underscore character ought
> to be ignored when matching column names to JavaBean properties. This
> would require several changes, but the biggest change, I think, would
> occur in the method
>
org.apache.commons.dbutils.BeanProcessor.mapColumnsToProperties(ResultSetMetaData
> rsmd, PropertyDescriptor[] props). Below I have taken a first cut at
> the modified implementation.
>
> thanks,
> -markus
>
> http://jakarta.apache.org/commons/dbutils/examples.html
>
> --- START draft of additional documentation
>
> Using the BeanHandler potentially adds a new requirement - it requires
> that the names of the column in the database match the property names
> defined of the JavaBean. In the above example, that means that if the
> columns in the database table "Person" were named "PERSON_ID", "NAME",
> "FIRST_NAME", and "LAST_NAME", then the properties of the JavaBean
> Person.java must be named "person_id", "name", "first_name", and
> "last_name". (Note that the case does not have to match.) Such an
> example is a real world reality in that many DBAs, er, encourage a
> naming schema that contains only capital letters and where each word is
> separated by the underscore character.
>
> There are two potential solutions to this issue: (1) re-write the
> SELECT statement to use the "AS" SQL keyword, or (2) use an overloaded
> constructor of BeanHandler.
>
> So, given a JavaBean with properties "person_id", "name", "first_name",
> and "last_name"...
> The first solution requires that you change the SQL statement to
> "SELECT PERSON_ID AS personId, NAME, FIRST_NAME AS firstName, LAST_NAME
> AS lastName FROM Person WHERE name=?". (Note that the AS keyword is not
> used with the column named NAME since it matches the property name.)
> The second solution requires you to change the line "ResultSetHandler h
> = new BeanHandler(Person.class);" to "ResultSetHandler h = new
> BeanHandler(Person.class, true);". (Note passing the boolean value true
> as the second argument in the constrcutor.)
>
> --- END draft of additional documentation
>
>
>
http://cvs.apache.org/viewcvs.cgi/jakarta-commons/dbutils/src/java/org/apache/commons/dbutils/BeanProcessor.java?rev=1.7&sortdir=down&view=auto
>
> -- START original --
>
> protected int[] mapColumnsToProperties(ResultSetMetaData rsmd,
> PropertyDescriptor[] props) throws SQLException {
>
> int cols = rsmd.getColumnCount();
> int columnToProperty[] = new int[cols + 1];
> Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND);
>
> for (int col = 1; col <= cols; col++) {
> String columnName = rsmd.getColumnName(col);
> for (int i = 0; i < props.length; i++) {
>
> if (columnName.equalsIgnoreCase(props[i].getName())) {
> columnToProperty[col] = i;
> break;
> }
> }
> }
>
> return columnToProperty;
> }
>
> -- END original --
>
> -- START modified --
>
> protected int[] mapColumnsToProperties(ResultSetMetaData rsmd,
> PropertyDescriptor[] props) throws SQLException {
> return mapColumnsToProperties(rsmd, props, false);
> }
>
>
> protected int[] mapColumnsToProperties(ResultSetMetaData rsmd,
> PropertyDescriptor[] props, boolean ignoreUnderscore) throws
> SQLException {
>
> int cols = rsmd.getColumnCount();
> int columnToProperty[] = new int[cols + 1];
> Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND);
>
> for (int col = 1; col <= cols; col++) {
> String columnName = rsmd.getColumnName(col);
> String columnNameUnderscoreRemoved = columnName.".replace('_', '');
> for (int i = 0; i < props.length; i++) {
>
> if (columnName.equalsIgnoreCase(props[i].getName())) {
> columnToProperty[col] = i;
> break;
> } // end of IF
> else if (
> (ignoreUnderscore) &&
> (columnNameUnderscoreRemoved
> .equalsIgnoreCase(props[i].getName()))
> ) {
> columnToProperty[col] = i;
> break;
> } // end of ELSE IF
> } // end of FOR
> }
>
> return columnToProperty;
> }
>
> -- END modified --
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
__________________________________
Do you Yahoo!?
The all-new My Yahoo! - Get yours free!
http://my.yahoo.com
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org