You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Srilakshmi Machineni <ma...@yahoo.com> on 2007/02/12 07:29:00 UTC
problems storing complex objects to the database
Hello,
We are currently using OJB 1.0.1 . We have a table that is a child to two different tables. These two parent tables have composite primary keys and they share part of the keys in the child table. The child has a 1:1 relation to both the parents.
The tables look something like this:
CREATE TABLE "CHILD" (
"CHILD_ID" INTEGER NOT NULL PRIMARY KEY,
"PARENT_ID" INTEGER ,
"ABC" INTEGER ,
"PARENT2_ID" INTEGER )
ALTER TABLE "CHILD"
ADD CONSTRAINT "PARENT2_FK" FOREIGN KEY
("PARENT2_ID",
"ABC")
REFERENCES "PARENT2"
("PARENT_ID",
"ABC")
ALTER TABLE "CHILD"
ADD CONSTRAINT "PARENT1_FK" FOREIGN KEY
("PARENT_ID",
"ABC")
REFERENCES "PARENT"
("PARENT_ID",
"ABC")
CREATE TABLE "PARENT" (
"PARENT_ID" INTEGER NOT NULL,
"ABC" INTEGER NOT NULL)
ALTER TABLE "PARENT"
ADD CONSTRAINT "PK1" PRIMARY KEY
("PARENT_ID",
"ABC");
CREATE TABLE "PARENT2" (
"PARENT2_ID" INTEGER NOT NULL,
"ABC" INTEGER NOT NULL)
ALTER TABLE "PARENT2"
ADD CONSTRAINT "PK1" PRIMARY KEY
("PARENT2_ID",
"ABC");
The OJB Classes::
class Child{
private Integer childId;
private Integer parentId;
private Integer abc;
private Integer parent2Id;
//rel
private Parent parent;
private Parent2 parent2;
}
class Parent{
private Integer parentId;
private Integer abc;
//rel
private Child child;
}
class Parent2{
private Integer parent2Id;
private Integer abc;
//rel
private Child child;
}
The repository mapping:
<class-descriptor class="Child"
table="CHILD">
<field-descriptor name="childId" column="CHILD_ID" primarykey="true" jdbc-type="INTEGER"/>
<field-descriptor name="parentId" column="PARENT_ID" jdbc-type="INTEGER"/>
<field-descriptor name="abc" column="ABC" jdbc-type="INTEGER"/>
<field-descriptor name="parent2Id" column="PARENT2_ID" jdbc-type="INTEGER"/>
<reference-descriptor name="parent" class-ref="Parent">
<foreignkey field-ref="parentId" />
<foreignkey field-ref="abc" />
</reference-descriptor>
<reference-descriptor name="parent2" class-ref="Parent2">
<foreignkey field-ref="parent2Id" />
<foreignkey field-ref="abc" />
</reference-descriptor>
</class-descriptor>
<class-descriptor class="Parent" table="PARENT">
<field-descriptor name="parentId" column="PARENT_ID" primarykey="true" jdbc-type="INTEGER"/>
<field-descriptor name="abc" column="ABC" primarykey="true" jdbc-type="INTEGER"/>
<reference-descriptor name="child" class-ref="Child">
<foreignkey field-ref="parentId" />
<foreignkey field-ref="abc" />
</reference-descriptor>
</class-descriptor>
<class-descriptor class="parent2" table="PARENT">
<field-descriptor name="parent2Id" column="PARENT2_ID" primarykey="true" jdbc-type="INTEGER"/>
<field-descriptor name="abc" column="ABC" primarykey="true" jdbc-type="INTEGER"/>
<reference-descriptor name="child" class-ref="child">
<foreignkey field-ref="parent2Id" />
<foreignkey field-ref="abc" />
</reference-descriptor>
</class-descriptor>
When storing the child table, it is possible that data from only one of the parent tables is populated.
When I am trying to store in that database, I dont have a problem when I call the set method on one of the parent objects of the child but when I try to refer the second parent in the same manner, I am getting a sql exception that says that data does not exist in the parent table and the transaction is being rolled back(The parent object is stored in the database first...). I have tried removing the OJB relation and manually setting the foreign key fields for the second parent. In this case I am able to store the part of the key that is not shared but the shared part is being set to null in the database. I have also tried to make one of them a collection-descriptor. I got the same sql exception. I am not sure if there is a problem with how the database is setup or how my OJB mappings are and classes are setup. At this point, short of making a database change, I feel I have exhausted all available option. Am I missing something? Any advice would be appreciated..
Thanks,
-Sri.
____________________________________________________________________________________
Any questions? Get answers on any topic at www.Answers.yahoo.com. Try it now.
Re: error handling in ModelBasedResultSetIterator
Posted by Peter Doyle <de...@rdmf.net>.
sorry, i sent this to the wrong group all together - i intended it to go
to the ddlutils group.
Peter Doyle wrote:
> How do I request less-strict error handling? Specifically I had to
> modify org.apache.ddlutils.platform.ModelBasedResultSetIterator class
> to skip setting bean values for allowable(in mysql 5.x) but unusual
> timestamp values of "0000-00-00 00:00:00" (set by default). If this is
> not the right forum for this request, please redirect me. The existing
> source code in question looks like this:
>
> 45 /**
> 46 * This is an iterator that is specifically targeted at traversing
> result sets.
> 47 * If the query is against a known table, then {@link
> org.apache.ddlutils.dynabean.SqlDynaBean} instances
> 48 * are created from the rows, otherwise normal {@link
> org.apache.commons.beanutils.DynaBean} instances
> 49 * are created.
> 50 * 51 * @version $Revision: 289996 $
> 52 */
>
> ...
>
> 218 /**
> 219 * {@inheritDoc}
> 220 */
> 221 public Object next() throws DatabaseOperationException
> 222 {
> 223 advanceIfNecessary();
> 224 if (_isAtEnd)
> 225 {
> 226 throw new NoSuchElementException("No more elements in
> the resultset");
> 227 }
> 228 else
> 229 {
> 230 try
> 231 {
> 232 DynaBean bean = _dynaClass.newInstance();
> 233 Table table = null;
> 234 235 if (bean instanceof SqlDynaBean)
> 236 {
> 237 SqlDynaClass dynaClass =
> (SqlDynaClass)((SqlDynaBean)bean).getDynaClass();
> 238 239 table = dynaClass.getTable();
> 240 }
> 241 242 for (Iterator it =
> _columnsToProperties.entrySet().iterator(); it.hasNext();)
> 243 {
> 244 Map.Entry entry = (Map.Entry)it.next();
> 245 String columnName = (String)entry.getKey();
> 246 String propName = (String)entry.getValue();
> 247 Table curTable = table;
> 248 249 if (curTable == null)
> 250 {
> 251 curTable =
> (Table)_preparedQueryHints.get(_caseSensitive ? columnName :
> columnName.toLowerCase());
> 252 }
> 253 Object value =
> _platform.getObjectFromResultSet(_resultSet, columnName, curTable);
> 254 255 bean.set(propName, value);
> 256 }
> 257 _needsAdvancing = true;
> 258 return bean;
> 259 }
> 260 catch (Exception ex)
> 261 {
> 262 cleanUp();
> 263 throw new DatabaseOperationException("Exception
> while reading the row from the resultset", ex);
> 264 }
> 265 }
> 266 }
>
>
> The data extraction fails with the existing code with a "Exception
> while reading the row from the resultset" error when it encounters the
> "0000-00-00 00:00:00" timestamp values in the database. I am
> suggesting a more lax error handling around the value setting code
> (lines 253 - 255) as such (using some logging api wrapped with
> commons-logging) to display the actual error message encountered and
> proceed with the extraction rather than just fail. Perhaps this could
> be regulated with an appropriate configuration directive
> "errorHandling" in the ant task that could be checked at execution
> time for a more elegant solution:
>
> 258 Object value=null;
> 259 260 try {
> 261 value =
> _platform.getObjectFromResultSet(_resultSet, columnName, curTable);
> 262 bean.set(propName, value);
> 263 } catch (SQLException ex) {
> 264 log.error(ex.getMessage());
> 265 }
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
error handling in ModelBasedResultSetIterator
Posted by Peter Doyle <de...@rdmf.net>.
How do I request less-strict error handling? Specifically I had to
modify org.apache.ddlutils.platform.ModelBasedResultSetIterator class to
skip setting bean values for allowable(in mysql 5.x) but unusual
timestamp values of "0000-00-00 00:00:00" (set by default). If this is
not the right forum for this request, please redirect me. The existing
source code in question looks like this:
45 /**
46 * This is an iterator that is specifically targeted at traversing result sets.
47 * If the query is against a known table, then {@link org.apache.ddlutils.dynabean.SqlDynaBean} instances
48 * are created from the rows, otherwise normal {@link org.apache.commons.beanutils.DynaBean} instances
49 * are created.
50 *
51 * @version $Revision: 289996 $
52 */
...
218 /**
219 * {@inheritDoc}
220 */
221 public Object next() throws DatabaseOperationException
222 {
223 advanceIfNecessary();
224 if (_isAtEnd)
225 {
226 throw new NoSuchElementException("No more elements in the resultset");
227 }
228 else
229 {
230 try
231 {
232 DynaBean bean = _dynaClass.newInstance();
233 Table table = null;
234
235 if (bean instanceof SqlDynaBean)
236 {
237 SqlDynaClass dynaClass = (SqlDynaClass)((SqlDynaBean)bean).getDynaClass();
238
239 table = dynaClass.getTable();
240 }
241
242 for (Iterator it = _columnsToProperties.entrySet().iterator(); it.hasNext();)
243 {
244 Map.Entry entry = (Map.Entry)it.next();
245 String columnName = (String)entry.getKey();
246 String propName = (String)entry.getValue();
247 Table curTable = table;
248
249 if (curTable == null)
250 {
251 curTable = (Table)_preparedQueryHints.get(_caseSensitive ? columnName : columnName.toLowerCase());
252 }
253 Object value = _platform.getObjectFromResultSet(_resultSet, columnName, curTable);
254
255 bean.set(propName, value);
256 }
257 _needsAdvancing = true;
258 return bean;
259 }
260 catch (Exception ex)
261 {
262 cleanUp();
263 throw new DatabaseOperationException("Exception while reading the row from the resultset", ex);
264 }
265 }
266 }
The data extraction fails with the existing code with a "Exception while
reading the row from the resultset" error when it encounters the
"0000-00-00 00:00:00" timestamp values in the database. I am suggesting
a more lax error handling around the value setting code (lines 253 -
255) as such (using some logging api wrapped with commons-logging) to
display the actual error message encountered and proceed with the
extraction rather than just fail. Perhaps this could be regulated with
an appropriate configuration directive "errorHandling" in the ant task
that could be checked at execution time for a more elegant solution:
258 Object value=null;
259
260 try {
261 value = _platform.getObjectFromResultSet(_resultSet, columnName, curTable);
262 bean.set(propName, value);
263 } catch (SQLException ex) {
264 log.error(ex.getMessage());
265 }
Re: error handling in ModelBasedResultSetIterator
Posted by Thomas Dudziak <to...@gmail.com>.
On 2/14/07, Peter Doyle <de...@rdmf.net> wrote:
> I would like to make a request or offer to contribute something with
> regards error handling? Specifically, with regards to data extraction
> using the org.apache.ddlutils.platform.ModelBasedResultSetIterator
> class. As is, If this Iterators encounters an error when setting a
> Dynabean property it throws a generic
> DatabaseOperationException("Exception while reading the row from the
> resultset"). It occurs, in my case, when encountering an allowable
> (under mysql) but perhaps a bit unusual date value of "0000-00-00". The
> existing source code in question looks like this:
<snip>
> I am suggesting at least more lax error handling around the value
> setting code (lines 253 - 255) as I use below (using some logging api
> wrapped with commons-logging) to just log the actual error message
> encountered ("[ERROR] ModelBasedResultSetIterator - Value '0000-00-00'
> can not be represented as java.sql.Date") and proceed with the
> extraction rather than just fail. An even better solution might be to
> declare a level for error handling (fail | warn | ignore perhaps) that
> could be specified as ant task attribute so the could would know how to
> handle the errors encountered more precisely.
The problem is that the MySql JDBC driver tries to map an illegal
value to a java.sql.Date. Now the question is, how should this value
be represented ? As null or as some Date ?
The error handling is a different thing. I'd like to suggest that you
create a JIRA issue for adding a failOnError=true/false mode for
reading data from a database.
Tom
error handling in ModelBasedResultSetIterator
Posted by Peter Doyle <de...@rdmf.net>.
I would like to make a request or offer to contribute something with
regards error handling? Specifically, with regards to data extraction
using the org.apache.ddlutils.platform.ModelBasedResultSetIterator
class. As is, If this Iterators encounters an error when setting a
Dynabean property it throws a generic
DatabaseOperationException("Exception while reading the row from the
resultset"). It occurs, in my case, when encountering an allowable
(under mysql) but perhaps a bit unusual date value of "0000-00-00". The
existing source code in question looks like this:
45 /**
46 * This is an iterator that is specifically targeted at traversing result sets.
47 * If the query is against a known table, then {@link org.apache.ddlutils.dynabean.SqlDynaBean} instances
48 * are created from the rows, otherwise normal {@link org.apache.commons.beanutils.DynaBean} instances
49 * are created.
50 *
51 * @version $Revision: 289996 $
52 */
...
218 /**
219 * {@inheritDoc}
220 */
221 public Object next() throws DatabaseOperationException
222 {
223 advanceIfNecessary();
224 if (_isAtEnd)
225 {
226 throw new NoSuchElementException("No more elements in the resultset");
227 }
228 else
229 {
230 try
231 {
232 DynaBean bean = _dynaClass.newInstance();
233 Table table = null;
234
235 if (bean instanceof SqlDynaBean)
236 {
237 SqlDynaClass dynaClass = (SqlDynaClass)((SqlDynaBean)bean).getDynaClass();
238
239 table = dynaClass.getTable();
240 }
241
242 for (Iterator it = _columnsToProperties.entrySet().iterator(); it.hasNext();)
243 {
244 Map.Entry entry = (Map.Entry)it.next();
245 String columnName = (String)entry.getKey();
246 String propName = (String)entry.getValue();
247 Table curTable = table;
248
249 if (curTable == null)
250 {
251 curTable = (Table)_preparedQueryHints.get(_caseSensitive ? columnName : columnName.toLowerCase());
252 }
253 Object value = _platform.getObjectFromResultSet(_resultSet, columnName, curTable);
254
255 bean.set(propName, value);
256 }
257 _needsAdvancing = true;
258 return bean;
259 }
260 catch (Exception ex)
261 {
262 cleanUp();
263 throw new DatabaseOperationException("Exception while reading the row from the resultset", ex);
264 }
265 }
266 }
I am suggesting at least more lax error handling around the value
setting code (lines 253 - 255) as I use below (using some logging api
wrapped with commons-logging) to just log the actual error message
encountered ("[ERROR] ModelBasedResultSetIterator - Value '0000-00-00'
can not be represented as java.sql.Date") and proceed with the
extraction rather than just fail. An even better solution might be to
declare a level for error handling (fail | warn | ignore perhaps) that
could be specified as ant task attribute so the could would know how to
handle the errors encountered more precisely.
258 Object value=null;
259
260 try {
261 value = _platform.getObjectFromResultSet(_resultSet, columnName, curTable);
262 bean.set(propName, value);
263 } catch (SQLException ex) {
264 log.error(ex.getMessage());
265 }