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 Mark Volkmann <ma...@ociweb.com> on 2007/01/31 04:13:28 UTC
inserting a record with a foreign key
Here's my situation. I have an artists table and a recordings table
in a MySQL database.
The artists table has the columns id and name.
The recordings table has the columns id, name, year and artist_id (a
foreign key to the artists table).
I have Artist and Recording POJO classes.
The Recording class has an Artist field to hold a reference to the
Artist object to which the Recording object belongs.
So now I want to insert a row in the recordings table for a Recording
object.
I do this in my Java code.
sqlMap.insert("insertRecording", recording);
The recording variable holds the Recording object which already has
its Artist field set.
Here's the insert element found in Recording.xml.
<insert id="insertRecording"
parameterClass="com.ociweb.music.Recording">
insert into recordings (name, year, artist_id)
values (#name#, #year#, #artist#)
<selectKey resultClass="int" keyProperty="id">
select last_insert_id() as id
</selectKey>
</insert>
I get this error.
[java] --- The error occurred in Recording.xml.
[java] --- The error occurred while applying a parameter map.
[java] --- Check the insertRecording-InlineParameterMap.
[java] --- Check the parameter mapping for the 'artist' property.
[java] --- Cause: java.lang.NullPointerException
Is iBATIS supposed to be able to get the key value from the Artist
object automatically?
I'm guessing the problem is in the last entry in "values( ... )", but
I don't know what to do differently.
I already verified that the Artist field in the recording isn't null
and the Artist object it points to does have an id attribute value.
Re: inserting a record with a foreign key
Posted by Mark Volkmann <ma...@ociweb.com>.
On Jan 30, 2007, at 10:33 PM, Larry Meadors wrote:
> Hey Mark,
>
> Assuming that you have something like this:
>
> Artist bean properties:
> - id
> - name
> - whatever else...
>
> ...and your Recording bean properties:
> - id
> - name
> - year
> - artist (bean)
>
> ...you should be able to use dot notation to refer to the artist id
> like this:
>
> <insert id="insertRecording"
> parameterClass="com.ociweb.music.Recording">
> insert into recordings (name, year, artist_id)
> values (#name#, #year#, #artist.id#)
> <selectKey resultClass="int" keyProperty="id">
> select last_insert_id() as id
> </selectKey>
> </insert>
That did it! Using "artist.id" in the values list was the key.
> Now for an [OT] comment: Looking at this, you may be making the same
> mistake I did when I started using iBATIS, in that you are naming
> things like "insertRecording".
>
> It'll work, but if you do it a little differently, it's easier to
> manage.
>
> In your sqlmapconfig.xml, if you enable name spaces,
I just learned how to do that. For others that might be interested,
you add
<settings useStatementNamespaces="true"/>
near the top of your SqlMapConfig.xml file.
> you can give each
> sqlmap file a namespace, like "Recording", you can name the mapped
> statements with simpler names like just "insert". Then, when you call
> the mapped statement, you call "Recording.insert" instead. The trick
> here is to keep the naming consistent - Recording.xml has a
> "Recording" namespace, etc...
>
> Now, as your system gets bigger, if you are looking for a mapped
> statement, the name tells you exactly which file it is in.
Wonderful! This is MUCH better than what I was doing.
Thank you very much for all your help!
> Larry
>
>
> On 1/30/07, Mark Volkmann <ma...@ociweb.com> wrote:
>> Here's my situation. I have an artists table and a recordings table
>> in a MySQL database.
>> The artists table has the columns id and name.
>> The recordings table has the columns id, name, year and artist_id (a
>> foreign key to the artists table).
>> I have Artist and Recording POJO classes.
>> The Recording class has an Artist field to hold a reference to the
>> Artist object to which the Recording object belongs.
>>
>> So now I want to insert a row in the recordings table for a Recording
>> object.
>> I do this in my Java code.
>>
>> sqlMap.insert("insertRecording", recording);
>>
>> The recording variable holds the Recording object which already has
>> its Artist field set.
>>
>> Here's the insert element found in Recording.xml.
>>
>> <insert id="insertRecording"
>> parameterClass="com.ociweb.music.Recording">
>> insert into recordings (name, year, artist_id)
>> values (#name#, #year#, #artist#)
>> <selectKey resultClass="int" keyProperty="id">
>> select last_insert_id() as id
>> </selectKey>
>> </insert>
>>
>> I get this error.
>>
>> [java] --- The error occurred in Recording.xml.
>> [java] --- The error occurred while applying a parameter map.
>> [java] --- Check the insertRecording-InlineParameterMap.
>> [java] --- Check the parameter mapping for the 'artist'
>> property.
>> [java] --- Cause: java.lang.NullPointerException
>>
>> Is iBATIS supposed to be able to get the key value from the Artist
>> object automatically?
>> I'm guessing the problem is in the last entry in "values( ... )", but
>> I don't know what to do differently.
>> I already verified that the Artist field in the recording isn't null
>> and the Artist object it points to does have an id attribute value.
>>
Re: inserting a record with a foreign key
Posted by Larry Meadors <lm...@apache.org>.
Hey Mark,
Assuming that you have something like this:
Artist bean properties:
- id
- name
- whatever else...
...and your Recording bean properties:
- id
- name
- year
- artist (bean)
...you should be able to use dot notation to refer to the artist id like this:
<insert id="insertRecording"
parameterClass="com.ociweb.music.Recording">
insert into recordings (name, year, artist_id)
values (#name#, #year#, #artist.id#)
<selectKey resultClass="int" keyProperty="id">
select last_insert_id() as id
</selectKey>
</insert>
Now for an [OT] comment: Looking at this, you may be making the same
mistake I did when I started using iBATIS, in that you are naming
things like "insertRecording".
It'll work, but if you do it a little differently, it's easier to manage.
In your sqlmapconfig.xml, if you enable name spaces, you can give each
sqlmap file a namespace, like "Recording", you can name the mapped
statements with simpler names like just "insert". Then, when you call
the mapped statement, you call "Recording.insert" instead. The trick
here is to keep the naming consistent - Recording.xml has a
"Recording" namespace, etc...
Now, as your system gets bigger, if you are looking for a mapped
statement, the name tells you exactly which file it is in.
Larry
On 1/30/07, Mark Volkmann <ma...@ociweb.com> wrote:
> Here's my situation. I have an artists table and a recordings table
> in a MySQL database.
> The artists table has the columns id and name.
> The recordings table has the columns id, name, year and artist_id (a
> foreign key to the artists table).
> I have Artist and Recording POJO classes.
> The Recording class has an Artist field to hold a reference to the
> Artist object to which the Recording object belongs.
>
> So now I want to insert a row in the recordings table for a Recording
> object.
> I do this in my Java code.
>
> sqlMap.insert("insertRecording", recording);
>
> The recording variable holds the Recording object which already has
> its Artist field set.
>
> Here's the insert element found in Recording.xml.
>
> <insert id="insertRecording"
> parameterClass="com.ociweb.music.Recording">
> insert into recordings (name, year, artist_id)
> values (#name#, #year#, #artist#)
> <selectKey resultClass="int" keyProperty="id">
> select last_insert_id() as id
> </selectKey>
> </insert>
>
> I get this error.
>
> [java] --- The error occurred in Recording.xml.
> [java] --- The error occurred while applying a parameter map.
> [java] --- Check the insertRecording-InlineParameterMap.
> [java] --- Check the parameter mapping for the 'artist' property.
> [java] --- Cause: java.lang.NullPointerException
>
> Is iBATIS supposed to be able to get the key value from the Artist
> object automatically?
> I'm guessing the problem is in the last entry in "values( ... )", but
> I don't know what to do differently.
> I already verified that the Artist field in the recording isn't null
> and the Artist object it points to does have an id attribute value.
>