You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Brian Parkinson <pa...@avaning.com> on 2007/12/05 22:57:41 UTC
A couple newbie questions
Hello!
I'm new to iBATIS, but loving it so far - decided to go with iBATIS as
opposed to Hibernate for simplicity, and to have arguably more control
over the SQL. I have a couple questions (apologies if these are covered
in hthe FAQ, but I couldn't find):
1. I have a pretty complex join, and so am using the groupBy pattern
(whereby the ResultMap for the top level object references child
resultMaps, with groupBy, and using LEFT OUTER joins) - basically
straight from the docs. It looks something like:
<select id="Foo.select_by_id" parameterClass="string"
resultMap="FooResultMap">
SELECT
f.name, f.address, f.phone,
t.colour, t.shape, t.size
FROM Foo f
LEFT OUTER JOIN Bar b on f.ID = b.ID
WHERE f.name = #value#;
</select>
This is cool - my real code has many more JOINs, but you get the idea.
The problem is, if both the Foo table *and* the Bar table contain a
column with the *same* name, then the JOIN doesn't work, even when
distinguished as f.bork and b.bork.
Is there a workaround for this?
2. Due to the problem above, I'm running a number of select queries, and
building up the result (ie. in the example above, I'd make one query for
the Bar object(s) and then the Foo object and call foo.setBar(bar) to
build things up.
The question - if I put this in a batch statement, can I expect a good
performance boost?
Any help or tips on how best to structure this stuff is appreciated -
I"m comfortable with Java, but not much of a SQL expert (at all).
Any help is greatly appreciated.
Cheers,
parki...
ps. iBATIS is great so far. :)
RE: MySQL related question - constraint violation
Posted by Poitras Christian <Ch...@ircm.qc.ca>.
My guess is that something changed between those version.
Either LAST_INSERT_ID() changed or how foreign keys are managed. In
either case, you'll find better information on MySQL list or release
notes.
It could also be something not directly linked to the message like
triggers.
Christian
-----Original Message-----
From: Brian Parkinson [mailto:parki@avaning.com]
Sent: Thursday, December 06, 2007 4:36 PM
To: user-java@ibatis.apache.org
Subject: RE: MySQL related question - constraint violation
HI Toby:
Yes - the exact same SQL script is used to set up both databases (and
yes - InnoDB).
Same SQL script to set up the databases and tables (and created from
scratch each time). Same Java code unit tests - the only difference is
Debian (5.0.32) as opposed to Windows/Mac (5.0.45).
Weird, huh?!
parki...
-----Original Message-----
From: Toby Thain [mailto:toby@smartgames.ca]
Sent: Thursday, December 06, 2007 3:15 PM
To: user-java@ibatis.apache.org
Subject: Re: MySQL related question - constraint violation
On 6-Dec-07, at 11:46 AM, Brian Parkinson wrote:
> Hello -
>
> Wondering if someone out there has some MySQL experience - running
> into
> a weird issue, and I'm stumped. This is perhaps not exactly an iBATIS
> issue, but I received no response over at the MySQL forums, so it's
> worth a shot:
>
> I have a very simple data model working in iBATIS (getting familiar
> with
> this, and enjoying how it's put together). It works on Windows and Mac
> OS X, with MySQL version 5.0.45.
>
> For our deployment, we're using Debian 5.0.32.
>
> When I run my unit tests on the Debian box, I get foreign key
> constraint
> violations which I do not get on the Windoze or Mac box.
Are they all using the same storage engine (differing defaults)?
InnoDB checks such constraints, other engines generally do not.
--Toby
> To wit:
>
> [junit] ------------- Standard Output ---------------
> [junit] --- The error occurred in
> com/ecobee/foundation/ibatis/SqlMapThermostat.xml.
> [junit] --- The error occurred while applying a parameter map.
> [junit] --- Check the Thermostat.LocationParameterMap.
> [junit] --- Check the statement (update failed).
> [junit] --- Cause:
> com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:
> Cannot add or update a child row: a foreign key constraint fails
RE: MySQL related question - constraint violation
Posted by Brian Parkinson <pa...@avaning.com>.
HI Toby:
Yes - the exact same SQL script is used to set up both databases (and
yes - InnoDB).
Same SQL script to set up the databases and tables (and created from
scratch each time). Same Java code unit tests - the only difference is
Debian (5.0.32) as opposed to Windows/Mac (5.0.45).
Weird, huh?!
parki...
-----Original Message-----
From: Toby Thain [mailto:toby@smartgames.ca]
Sent: Thursday, December 06, 2007 3:15 PM
To: user-java@ibatis.apache.org
Subject: Re: MySQL related question - constraint violation
On 6-Dec-07, at 11:46 AM, Brian Parkinson wrote:
> Hello -
>
> Wondering if someone out there has some MySQL experience - running
> into
> a weird issue, and I'm stumped. This is perhaps not exactly an iBATIS
> issue, but I received no response over at the MySQL forums, so it's
> worth a shot:
>
> I have a very simple data model working in iBATIS (getting familiar
> with
> this, and enjoying how it's put together). It works on Windows and Mac
> OS X, with MySQL version 5.0.45.
>
> For our deployment, we're using Debian 5.0.32.
>
> When I run my unit tests on the Debian box, I get foreign key
> constraint
> violations which I do not get on the Windoze or Mac box.
Are they all using the same storage engine (differing defaults)?
InnoDB checks such constraints, other engines generally do not.
--Toby
> To wit:
>
> [junit] ------------- Standard Output ---------------
> [junit] --- The error occurred in
> com/ecobee/foundation/ibatis/SqlMapThermostat.xml.
> [junit] --- The error occurred while applying a parameter map.
> [junit] --- Check the Thermostat.LocationParameterMap.
> [junit] --- Check the statement (update failed).
> [junit] --- Cause:
> com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:
> Cannot add or update a child row: a foreign key constraint fails
Re: MySQL related question - constraint violation
Posted by Toby Thain <to...@smartgames.ca>.
On 6-Dec-07, at 11:46 AM, Brian Parkinson wrote:
> Hello -
>
> Wondering if someone out there has some MySQL experience - running
> into
> a weird issue, and I'm stumped. This is perhaps not exactly an iBATIS
> issue, but I received no response over at the MySQL forums, so it's
> worth a shot:
>
> I have a very simple data model working in iBATIS (getting familiar
> with
> this, and enjoying how it's put together). It works on Windows and Mac
> OS X, with MySQL version 5.0.45.
>
> For our deployment, we're using Debian 5.0.32.
>
> When I run my unit tests on the Debian box, I get foreign key
> constraint
> violations which I do not get on the Windoze or Mac box.
Are they all using the same storage engine (differing defaults)?
InnoDB checks such constraints, other engines generally do not.
--Toby
> To wit:
>
> [junit] ------------- Standard Output ---------------
> [junit] --- The error occurred in
> com/ecobee/foundation/ibatis/SqlMapThermostat.xml.
> [junit] --- The error occurred while applying a parameter map.
> [junit] --- Check the Thermostat.LocationParameterMap.
> [junit] --- Check the statement (update failed).
> [junit] --- Cause:
> com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:
> Cannot add or update a child row: a foreign key constraint fails
MySQL related question - constraint violation
Posted by Brian Parkinson <pa...@avaning.com>.
Hello -
Wondering if someone out there has some MySQL experience - running into
a weird issue, and I'm stumped. This is perhaps not exactly an iBATIS
issue, but I received no response over at the MySQL forums, so it's
worth a shot:
I have a very simple data model working in iBATIS (getting familiar with
this, and enjoying how it's put together). It works on Windows and Mac
OS X, with MySQL version 5.0.45.
For our deployment, we're using Debian 5.0.32.
When I run my unit tests on the Debian box, I get foreign key constraint
violations which I do not get on the Windoze or Mac box. To wit:
[junit] ------------- Standard Output ---------------
[junit] --- The error occurred in
com/ecobee/foundation/ibatis/SqlMapThermostat.xml.
[junit] --- The error occurred while applying a parameter map.
[junit] --- Check the Thermostat.LocationParameterMap.
[junit] --- Check the statement (update failed).
[junit] --- Cause:
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:
Cannot add or update a child row: a foreign key constraint fails
(`ecobee_test/Location`, CONSTRAINT `FK07` FOREIGN KEY (`ThermostatID`)
REFERENCES `thermostat` (`ThermostatID`) ON DELETE NO ACTION ON UPDATE
NO ACTION); nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException:
[junit] --- The error occurred in
com/ecobee/foundation/ibatis/SqlMapThermostat.xml.
[junit] --- The error occurred while applying a parameter map.
[junit] --- Check the Thermostat.LocationParameterMap.
[junit] --- Check the statement (update failed).
...
I've double checked the code, and I know it works as the unit tests on
Windoze and Mac are checking that everything works. The insert code
where the problem is:
getSqlMapClient().startTransaction();
getSqlMapClient().startBatch();
int thermostatId = (Integer)
getSqlMapClientTemplate().insert("Thermostat.insert", thermostat);
Location location = thermostat.getLocation();
location.setThermostatId(thermostatId);
getSqlMapClientTemplate().insert("Location.insert", location);
So, the location is certainly getting set with the proper thermostat
identifier, but the constraint is violated.
I've tried moving the Thermostat.insert out of the transaction/batch,
and a couple other things, but nothing works.
I guess the next step is to upgrade the Debian box, but it's a pretty
huge undertaking (a number of databases being used) and this seems that
there's something else going on.
Any ideas?
Appreciated, and thanks.
parki....
RE: A couple newbie questions
Posted by Brian Parkinson <pa...@avaning.com>.
Hi Larry:
I wasn't going to use column indexes (fragile) but this solution is
perfect.
Thanks.
parki...
-----Original Message-----
From: larry.meadors@gmail.com [mailto:larry.meadors@gmail.com] On Behalf
Of Larry Meadors
Sent: Wednesday, December 05, 2007 11:06 PM
To: user-java@ibatis.apache.org
Subject: Re: A couple newbie questions
In my experience, column indexes are kind of fragile - I prefer to
alias the fields like this:
select customer.name as "customerName", product.name as "productName"
from....
Larry
On Dec 5, 2007 3:54 PM, Jeff Butler <je...@gmail.com> wrote:
> It's not magic, but you could also use columnIndex in your result map
> instead of column - then you wouldn't need to do any renaming in the
SQL
> statement.
>
> Jeff Butler
Re: A couple newbie questions
Posted by Larry Meadors <lm...@apache.org>.
In my experience, column indexes are kind of fragile - I prefer to
alias the fields like this:
select customer.name as "customerName", product.name as "productName" from....
Larry
On Dec 5, 2007 3:54 PM, Jeff Butler <je...@gmail.com> wrote:
> It's not magic, but you could also use columnIndex in your result map
> instead of column - then you wouldn't need to do any renaming in the SQL
> statement.
>
> Jeff Butler
Re: A couple newbie questions
Posted by Jeff Butler <je...@gmail.com>.
It's not magic, but you could also use columnIndex in your result map
instead of column - then you wouldn't need to do any renaming in the SQL
statement.
Jeff Butler
On Dec 5, 2007 4:48 PM, Brian Parkinson <pa...@avaning.com> wrote:
> Thanks Jeff:
>
>
>
> Should have been more specific (long day) – iBATIS doesn't distinguish the
> names – thought there might be some magic way to do this. No worries.
>
>
>
> Thanks for the help – answered both questions.
>
>
>
> Appreciated.
>
>
>
> parki...
>
>
>
>
> ------------------------------
>
> *From:* Jeff Butler [mailto:jeffgbutler@gmail.com]
> *Sent:* Wednesday, December 05, 2007 4:26 PM
> *To:* user-java@ibatis.apache.org
> *Subject:* Re: A couple newbie questions
>
>
>
> 1. Define "the join doesn't work". If you mean that the database chokes
> on f.bork and b.bork then you have a seriously messed up database and you
> should throw it out immediatly.
>
>
>
> If you mean that iBATIS can't distingush between the different columns,
> then that is correct behavior. You need to rename at least one of the
> columns (and the corresponding propery in your result object):
>
>
>
> select f.bork, b.bork as ANOTHER_BORK
>
> from ...
>
>
>
> 2. Batches are only for inserts, updates, deletes. They do not apply for
> selects.
>
>
>
> Jeff Butler
>
> On Dec 5, 2007 3:57 PM, Brian Parkinson <pa...@avaning.com> wrote:
>
> Hello!
>
>
>
> I'm new to iBATIS, but loving it so far – decided to go with iBATIS as
> opposed to Hibernate for simplicity, and to have arguably more control over
> the SQL. I have a couple questions (apologies if these are covered in hthe
> FAQ, but I couldn't find):
>
>
>
> 1. I have a pretty complex join, and so am using the groupBy pattern
> (whereby the ResultMap for the top level object references child resultMaps,
> with groupBy, and using LEFT OUTER joins) – basically straight from the
> docs. It looks something like:
>
>
>
> <select id="Foo.select_by_id" parameterClass="string"
> resultMap="FooResultMap">
>
> SELECT
>
> f.name, f.address, f.phone,
>
> t.colour, t.shape, t.size
>
> FROM Foo f
>
> LEFT OUTER JOIN Bar b on f.ID <http://f.id/> = b.ID <http://b.id/>
>
> WHERE f.name = #value#;
>
> </select>
>
>
>
> This is cool – my real code has many more JOINs, but you get the idea.
>
>
>
> The problem is, if both the Foo table **and** the Bar table contain a
> column with the ** same** name, then the JOIN doesn't work, even when
> distinguished as f.bork and b.bork.
>
>
>
> Is there a workaround for this?
>
>
>
> 2. Due to the problem above, I'm running a number of select queries, and
> building up the result (ie. in the example above, I'd make one query for the
> Bar object(s) and then the Foo object and call foo.setBar(bar) to build
> things up.
>
>
>
> The question – if I put this in a batch statement, can I expect a good
> performance boost?
>
>
>
> Any help or tips on how best to structure this stuff is appreciated – I"m
> comfortable with Java, but not much of a SQL expert (at all).
>
>
>
> Any help is greatly appreciated.
>
>
>
> Cheers,
>
>
>
> parki...
>
>
>
> ps. iBATIS is great so far. :)
>
>
>
>
>
RE: A couple newbie questions
Posted by Brian Parkinson <pa...@avaning.com>.
Thanks Jeff:
Should have been more specific (long day) - iBATIS doesn't distinguish
the names - thought there might be some magic way to do this. No
worries.
Thanks for the help - answered both questions.
Appreciated.
parki...
________________________________
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Wednesday, December 05, 2007 4:26 PM
To: user-java@ibatis.apache.org
Subject: Re: A couple newbie questions
1. Define "the join doesn't work". If you mean that the database chokes
on f.bork and b.bork then you have a seriously messed up database and
you should throw it out immediatly.
If you mean that iBATIS can't distingush between the different columns,
then that is correct behavior. You need to rename at least one of the
columns (and the corresponding propery in your result object):
select f.bork, b.bork as ANOTHER_BORK
from ...
2. Batches are only for inserts, updates, deletes. They do not apply
for selects.
Jeff Butler
On Dec 5, 2007 3:57 PM, Brian Parkinson <pa...@avaning.com> wrote:
Hello!
I'm new to iBATIS, but loving it so far - decided to go with iBATIS as
opposed to Hibernate for simplicity, and to have arguably more control
over the SQL. I have a couple questions (apologies if these are covered
in hthe FAQ, but I couldn't find):
1. I have a pretty complex join, and so am using the groupBy pattern
(whereby the ResultMap for the top level object references child
resultMaps, with groupBy, and using LEFT OUTER joins) - basically
straight from the docs. It looks something like:
<select id="Foo.select_by_id" parameterClass="string"
resultMap="FooResultMap">
SELECT
f.name <http://f.name/> , f.address, f.phone,
t.colour, t.shape, t.size
FROM Foo f
LEFT OUTER JOIN Bar b on f.ID <http://f.id/> = b.ID <http://b.id/>
WHERE f.name <http://f.name/> = #value#;
</select>
This is cool - my real code has many more JOINs, but you get the idea.
The problem is, if both the Foo table *and* the Bar table contain a
column with the * same* name, then the JOIN doesn't work, even when
distinguished as f.bork and b.bork.
Is there a workaround for this?
2. Due to the problem above, I'm running a number of select queries, and
building up the result (ie. in the example above, I'd make one query for
the Bar object(s) and then the Foo object and call foo.setBar(bar) to
build things up.
The question - if I put this in a batch statement, can I expect a good
performance boost?
Any help or tips on how best to structure this stuff is appreciated -
I"m comfortable with Java, but not much of a SQL expert (at all).
Any help is greatly appreciated.
Cheers,
parki...
ps. iBATIS is great so far. :)
Re: A couple newbie questions
Posted by Jeff Butler <je...@gmail.com>.
1. Define "the join doesn't work". If you mean that the database chokes on
f.bork and b.bork then you have a seriously messed up database and you
should throw it out immediatly.
If you mean that iBATIS can't distingush between the different columns, then
that is correct behavior. You need to rename at least one of the columns
(and the corresponding propery in your result object):
select f.bork, b.bork as ANOTHER_BORK
from ...
2. Batches are only for inserts, updates, deletes. They do not apply for
selects.
Jeff Butler
On Dec 5, 2007 3:57 PM, Brian Parkinson <pa...@avaning.com> wrote:
> Hello!
>
>
>
> I'm new to iBATIS, but loving it so far – decided to go with iBATIS as
> opposed to Hibernate for simplicity, and to have arguably more control over
> the SQL. I have a couple questions (apologies if these are covered in hthe
> FAQ, but I couldn't find):
>
>
>
> 1. I have a pretty complex join, and so am using the groupBy pattern
> (whereby the ResultMap for the top level object references child resultMaps,
> with groupBy, and using LEFT OUTER joins) – basically straight from the
> docs. It looks something like:
>
>
>
> <select id="Foo.select_by_id" parameterClass="string"
> resultMap="FooResultMap">
>
> SELECT
>
> f.name, f.address, f.phone,
>
> t.colour, t.shape, t.size
>
> FROM Foo f
>
> LEFT OUTER JOIN Bar b on f.ID <http://f.id/> = b.ID <http://b.id/>
>
> WHERE f.name = #value#;
>
> </select>
>
>
>
> This is cool – my real code has many more JOINs, but you get the idea.
>
>
>
> The problem is, if both the Foo table **and** the Bar table contain a
> column with the **same** name, then the JOIN doesn't work, even when
> distinguished as f.bork and b.bork.
>
>
>
> Is there a workaround for this?
>
>
>
> 2. Due to the problem above, I'm running a number of select queries, and
> building up the result (ie. in the example above, I'd make one query for the
> Bar object(s) and then the Foo object and call foo.setBar(bar) to build
> things up.
>
>
>
> The question – if I put this in a batch statement, can I expect a good
> performance boost?
>
>
>
> Any help or tips on how best to structure this stuff is appreciated – I"m
> comfortable with Java, but not much of a SQL expert (at all).
>
>
>
> Any help is greatly appreciated.
>
>
>
> Cheers,
>
>
>
> parki...
>
>
>
> ps. iBATIS is great so far. :)
>
>
>