Details
-
Improvement
-
Status: Closed
-
Minor
-
Resolution: Fixed
-
Nightly Builds
-
None
-
Operating System: Windows XP
Platform: PC
-
32414
Description
Below is the CVS diff (I can not fiogure out how to do an attachment):
— START —
Index: examples.xml
===================================================================
RCS file: /home/cvspublic/jakarta-commons/dbutils/xdocs/examples.xml,v
retrieving revision 1.6
diff -u -r1.6 examples.xml
— examples.xml 19 Mar 2004 00:25:39 -0000 1.6
+++ examples.xml 27 Nov 2004 20:22:43 -0000
@@ -149,6 +149,39 @@
your application. The provided implementation delegates datatype conversion to
the JDBC driver.
</p>
+
+<p>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
+"PERSON_ID", "NAME", "FIRST_NAME", and "LAST_NAME", then the
+properties of the JavaBean Person.java must be "person_id",
+"name", "first_name", and "last_name". (Note that the case does
+not have to match.) Such a situation is a real world reality in
+that many DBAs, er, <i>encourage</i> a naming schema that contains
+only capital letters and where each word is separated by the
+underscore character.</p>
+
+<p>There are at least three potential solutions to this issue:
+<ol>
+<li>Change the database column names to match the JavaBean property names.</li>
+<li>Change the JavaBean property names to match the database column names.</li>
+<li>Modify the SELECT statement to use the <code>AS</code> SQL keyword</li>
+</ol>
+</p>
+
+<p>The first two options presented are usually not feasible. The
+third option has you change the SQL statement. So, given a JavaBean
+with properties "person_id", "name", "first_name", and "last_name",
+the SELECT statement would need to be changed to <code>SELECT
+PERSON_ID AS personId, NAME, FIRST_NAME AS firstName, LAST_NAME
+AS lastName FROM Person WHERE name=?</code>. (Note that the AS
+keyword is not used with the column named NAME since it matches the
+JavaBean property name.)</p>
+
+<p>A fourth option, as mentioned above, would involve implementing
+a customized <code>RowProcessor</code></p>
+
</section>
</body>
— END —
This is based on the following email exchange:
----Original Message----
From: David Graham grahamdavid1980@yahoo.com
Sent: Thursday, November 18, 2004 10:24 AM
To: Jakarta Commons Users List
Subject: Re: [DbUtils] documentation update & overloading BeanHandler constructor
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" <markuskhouri@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()))
> }
> }
>
> 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()))
> )
// 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
>
>