anybody know iBatis? help please..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

anybody know iBatis? help please..

Author

Topic

my_aro
Yak Posting Veteran

50 Posts

Posted - 2006-07-06 : 23:59:45

i have a problem with joins in iBatis, i have these tables

CREATE TABLE [dbo].[Items] (
[itemid] bigint IDENTITY(1, 1) NOT NULL,
[categoryname] bigint NULL,
[itemname] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[itemimage] varchar(300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED ([itemid])
)
ON [PRIMARY]

CREATE TABLE [dbo].[Category] (
[categoryid] bigint IDENTITY(1, 1) NOT NULL,
[categoryname] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED ([categoryid])
)
ON [PRIMARY]

on my xml file i have these

<select id="searchItem" resultClass="com.globecel.portal.maintenance.promo.beans.ItemBean" parameterClass="searchItemBean">
SELECT category.categoryname as realcategoryname,
items.categoryname as categoryname,
items.itemid as itemid,
items.itemname as itemname,
items.itemimage as itemimage
FROM items
LEFT JOIN category
ON category.categoryid=items.categoryname
<dynamic prepend="where">
<isNotEmpty prepend="AND" property="itemname">
items.itemname LIKE #itemname#
</isNotEmpty>
</dynamic>
</select>

i can get all the datas correctly except for the realcategoryname.

can anybody help??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-07 : 19:34:16

It seems that some values for table Items in column CategoryName has no matching record for table Category in column CategoryID.

And since the database allows NULL for column CategoryName in table Items, the values present in table Items column categoryname and not in table Category column categoryid, is either NULL or any other integer of type bigint.

Run this query to look for the erraneous values.
It will show you all values in table items and column categoryname that can't be found in table category and column categoryid

select distinct CategoryName from items
where CategoryName not in (select categoryid from category)
order by categoryname

Peter Larsson
Helsingborg, Sweden

my_aro
Yak Posting Veteran

50 Posts

Posted - 2006-07-09 : 00:58:43

thanks for the help peso, but my objective is only to get the value of the categoryname in table category as pointed by a category name in the item table... actually when i run it with toad it queries the right datas... but as i run it thru queryList like java.util.List list = myDAO.queryList(searchItem) using DAO persistence, it doesnt returns the joined data..

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-10 : 21:56:14

does the object you're using capable of receiving multiple columns?
since you only require one column, try to remove the other columns from your query

I'm really not familiar with iBatis nor with Java but it's worth a try

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources