RE: lists as parameterClass

[Morone, Bill]

Brandon,
 
Thanks 
for the quick reply. It works like a charm. I had one lexical error (oh, to be 
human) and a few bogus column names.
 
I 
salute all the fine developers of iBatis SQLMaps. I am one of the select few 
"straight to xml" guys (just a select statement) -- I rigged up a nice little 
method that translates the result of  individual xml docs (each with 
an xml prolog) into one big doc. I later translate that. A very lightweight 
SOA.
 
Thanks,
Bill 
Morone
 
 
 
 
-Original Message-From: Brandon Goodin 
[mailto:[EMAIL PROTECTED]Sent: Friday, July 21, 2006 12:40 
AMTo: user-java@ibatis.apache.orgSubject: Re: lists as 
parameterClassI'm assuming the list is in your map 
parameter class.

 
select acct.acct_id as policyid 
from acct, client_acct
where 
client_acct.client_id = #clientId#
and acct.acct_id = client_acct.acct_id
and client_acct.client_acct_role_cd in

#clientRoles[]#
 
 Brandon
On 7/20/06, Morone, 
Bill <[EMAIL PROTECTED] > 
wrote:

  
  
  How about this one? Please 
  forgiv the "straight-to-xml"
   
  
   
  select acct.acct_id as policyid 
  from acct, client_acct
  where 
  client_acct.client_id = #clientId#
  and acct.acct_id = client_acct.acct_id
  and client_acct.client_acct_role_cd in
  
  #clientRoles[]#
   
   
  
  -Original 
  Message-From: Brandon Goodin [mailto:[EMAIL PROTECTED]]Sent: Thursday, July 20, 
  2006 8:35 AMTo: user-java@ibatis.apache.orgSubject: Re: lists as 
  parameterClassYes, this should work.
  select * from odm_product where id IN
          
    #[]#    iterate> Brandon 
  Goodin
  
  On 7/20/06, Thomas Karl 
  Schwaerzler < 
  [EMAIL PROTECTED] > wrote: 
  hello,i'd 
like to do a query that looks like:select * from odm_product where 
id IN (1,2); where i'd like to pass over a list of long 
valuesas parameter ( java.lang.Long[] )is there something like 
this posssible with 
  ibatis?greetst.
  This message, including any attachments, is intended only for the recipient(s) named above. It may contain confidential and privileged information. If you have received this communication in error, please notify the sender immediately and 
destroy or delete the original message. Also, please be aware that if you are not the intended recipient, any review, disclosure, copying, distribution or any action or reliance based on this message is prohibited by law.  


This message, including any attachments, is intended only for the recipient(s) 
named above. It may contain confidential and privileged information. If you have 
received this communication in error, please notify the sender immediately and 
destroy or delete the original message. Also, please be aware that if you are not 
the intended recipient, any review, disclosure, copying, distribution or any 
action or reliance based on this message is prohibited by law.  

RE: lists as parameterClass

[Morone, Bill]

Actually, one key part of a lightweight SOA - 2T + 1 I call it. But that 
is blog material.
-Original Message-From: Morone, Bill 
[mailto:[EMAIL PROTECTED]Sent: Friday, July 21, 2006 8:19 
AMTo: user-java@ibatis.apache.orgSubject: RE: lists as 
parameterClass
Brandon,
 
Thanks 
for the quick reply. It works like a charm. I had one lexical error (oh, to be 
human) and a few bogus column names.
 
I 
salute all the fine developers of iBatis SQLMaps. I am one of the select few 
"straight to xml" guys (just a select statement) -- I rigged up a nice little 
method that translates the result of  individual xml docs (each with 
an xml prolog) into one big doc. I later translate that. A very lightweight 
SOA.
 
Thanks,
Bill 
Morone
 
 
 
 
-Original Message-From: Brandon Goodin 
[mailto:[EMAIL PROTECTED]Sent: Friday, July 21, 2006 12:40 
AMTo: user-java@ibatis.apache.orgSubject: Re: lists as 
parameterClassI'm assuming the list is in your map 
parameter class.

 
select acct.acct_id as policyid 
from acct, client_acct
where 
client_acct.client_id = #clientId#
and acct.acct_id = client_acct.acct_id
and client_acct.client_acct_role_cd in

#clientRoles[]#
 
 Brandon
On 7/20/06, Morone, 
Bill <[EMAIL PROTECTED] > 
wrote: 

  
  
  How about this one? Please 
  forgiv the "straight-to-xml"
   
  
   
  select acct.acct_id as policyid 
  from acct, client_acct
  where 
  client_acct.client_id = #clientId#
  and acct.acct_id = client_acct.acct_id
  and client_acct.client_acct_role_cd in
  
  #clientRoles[]#
   
   
  
  -Original 
  Message-From: Brandon Goodin [mailto:[EMAIL PROTECTED]]Sent: Thursday, July 20, 
  2006 8:35 AMTo: user-java@ibatis.apache.orgSubject: Re: lists as 
  parameterClassYes, this should work.
  select * from odm_product where id IN
          
    #[]#    iterate> Brandon 
  Goodin
  
  On 7/20/06, Thomas Karl 
  Schwaerzler < 
  [EMAIL PROTECTED] > wrote: 
  hello,i'd 
like to do a query that looks like:select * from odm_product where 
id IN (1,2); where i'd like to pass over a list of long 
valuesas parameter ( java.lang.Long[] )is there something like 
this posssible with 
  ibatis?greetst.
  This message, including any attachments, is intended only for the recipient(s) named above. It may contain confidential and privileged information. If you have received this communication in error, please notify the sender immediately and 
destroy or delete the original message. Also, please be aware that if you are not the intended recipient, any review, disclosure, copying, distribution or any action or reliance based on this message is prohibited by law.  
This message, including any attachments, is intended only for the recipient(s) 
named above. It may contain confidential and privileged information. If you have 
received this communication in error, please notify the sender immediately and 
destroy or delete the original message. Also, please be aware that if you are not 
the intended recipient, any review, disclosure, copying, distribution or any 
action or reliance based on this message is prohibited by law.  



This message, including any attachments, is intended only for the recipient(s) 
named above. It may contain confidential and privileged information. If you have 
received this communication in error, please notify the sender immediately and 
destroy or delete the original message. Also, please be aware that if you are not 
the intended recipient, any review, disclosure, copying, distribution or any 
action or reliance based on this message is prohibited by law.  

Mapped statement with chained queries ?

[Fred Janon]

Hi,

I have 3 tables:

Tasks:
taskid int PK
jobid int FK
description String

Jobs:
jobid PK
clientid int FK

Clients:
clientid PK
clientname String.

How can I get the Clients:clientname via the Jobs table starting from a 
taskid? I know how to do it with 1 level on "indirection" but I don't 
know how to chain 2 queries...

taskid -> jobid -> clientid -> clientname.

I think it should be something like that:

 
   
   
   
   mapped-statement="getClientNameByJobId"/>

 

 
   select taskid,notes,assignedid,deptid,completion,duedate,
 donedate,tasktypeid,startdate,jobid
   from tasks order by duedate
 

 
   
   mapped-statement="getClientNameById"/>

 

 result-map="clientNameResult">

   select clientid from jobs where jobid=#value#
 

 result-class="java.lang.String">

   select name from clients where clientid = #value#
 

Thanks

Fred

begin:vcard
fn:fjanon
n:Janon;Fred
email;internet:[EMAIL PROTECTED]
title:Sr S/W Engineer & Architect
x-mozilla-html:TRUE
url:http://www.geocities.com/fjanon
version:2.1
end:vcard

Getting NullPointer exception in SqlMapDaoTemplate

[leonard . reinstein]

I am getting a NullPointer Exception when trying to update in Oracle. It
only happens sporadically. I.e., it may work several times in a row, then
it would fail once and then it would work again. We looked at the code and
cannot understand why this is happening. And the line in the Ibatis source
code where the exception occurs does not explain anything. Any ideas?

7/21/06 9:20:32:328 EDT] 7e00de6b SystemErr R at
com.ibatis.dao.client.template.SqlMapDaoTemplate.update(SqlMapDaoTemplate.java:124)
Caused by:
[7/21/06 9:20:32:359 EDT] 7e00de6b SystemErr R
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/tool/trans/proj/mytool/persistence/sqlmapdao/sql/Account.xml.
--- The error occurred while executing mapped statement.
--- Check the updateUserSubmit.
--- Check the statement or the result map.
--- Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
[7/21/06 9:20:32:359 EDT] 7e00de6b SystemErr Rat
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:94)
[7/21/06 9:20:32:359 EDT] 7e00de6b SystemErr Rat
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:500)
[7/21/06 9:20:32:359 EDT] 7e00de6b SystemErr Rat
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:85)
[7/21/06 9:20:32:359 EDT] 7e00de6b SystemErr Rat
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:62)

The mapping is
  








  

  
UPDATE  MY_TABLE SET SUBMITTED=?, MODIFIED_FLAG=?,
SUBMIT_ON=SYSDATE WHERE ACCT_ID = ? AND CUSIP=? AND CODE=? AND SHARES=? AND
DATE = ?
AND  SUBMIT_ON=TO_DATE(?,'-MM-DD HH24:MI:S')




This communication is for informational purposes only. It is not intended as an 
offer or solicitation for the purchase or sale of any financial instrument or 
as an official confirmation of any transaction. All market prices, data and 
other information are not warranted as to completeness or accuracy and are 
subject to change without notice. Any comments or statements made herein do not 
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries and 
affiliates.

This transmission may contain information that is privileged, confidential, 
legally privileged, and/or exempt from disclosure under applicable law. If you 
are not the intended recipient, you are hereby notified that any disclosure, 
copying, distribution, or use of the information contained herein (including 
any reliance thereon) is STRICTLY PROHIBITED. Although this transmission and 
any attachments are believed to be free of any virus or other defect that might 
affect any computer system into which it is received and opened, it is the 
responsibility of the recipient to ensure that it is virus free and no 
responsibility is accepted by JPMorgan Chase & Co., its subsidiaries and 
affiliates, as applicable, for any loss or damage arising in any way from its 
use. If you received this transmission in error, please immediately contact the 
sender and destroy the material in its entirety, whether in electronic or hard 
copy format. Thank you.
 

Re: Mapped statement with chained queries ?

[Debasish Dutta Roy]

You can do this in a simple manner. In the resultmap you need to provide the values properly. Here is an example.Here is the resultmap for Task     other properties ---
   --- Your select for task ---  
 --- other properties ---   -- select statement for getting job info--
  -- a normal ma with client bean properties mapped against columns --


   -- select statement for getting client info--

This way you will only call getTask from your client and iBATIS will do the rest. You will save on database trips.Whatever you need to specify in #value# of 2nd and 3rd statement is taken from the "column" attribute. In this case jobId for 2nd select and "clientId" for the 3rd select.
On 7/21/06, Fred Janon <[EMAIL PROTECTED]> wrote:
Hi,I have 3 tables:Tasks:taskid int PKjobid int FKdescription StringJobs:jobid PKclientid int FKClients:clientid PKclientname String.How can I get the Clients:clientname via the Jobs table starting from a
taskid? I know how to do it with 1 level on "indirection" but I don'tknow how to chain 2 queries...taskid -> jobid -> clientid -> clientname.I think it should be something like that:
  
mapped-statement="getClientNameByJobId"/>  
  select taskid,notes,assignedid,deptid,completion,duedate,  donedate,tasktypeid,startdate,jobidfrom tasks order by duedate
mapped-statement="getClientNameById"/>result-map="clientNameResult">select clientid from jobs where jobid=#value#
result-class="java.lang.String">select name from clients where clientid = #value#  
ThanksFred

Urgent help needed -- queryForList() problem

[rambabu.piridi]

 

 

Hi,

 
   In my
application I am using Ibatis as datamapper and firebird as the
backend.
 
In my database, I
have 10 records and I want to get first 5 of them.
 
Here I am using


sqlMap.queryForList("getUsersList", null, 0, 5) to retrive the
first 5 records from the database. 
 
 But the query
is fetching only 3 records.
 
When I give the
boundaries as (0,7) - it is fetching 4 records only.
 
but my requirenment
is to get the records in between the specified boundaries. i.e, if boundary is
specified as (0,7) it should retrieve 7 records, starting from
0.
 
I am unable to find
the problem? Can any one help me in this regard?
 
 
Thanks n
Regards,
==
Rambabu
Piridi.
 


The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. 

WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
 
www.wipro.com

Re: Mapped statement with chained queries ?

[Nathan Maves]

Although your solution will work, I would suggest another.  With your way you run into the classic n+1 problem.  One query to get the tasks, then n number of queries to get the jobs for those tasks.  You get the point.  That is why this is not the preferred way.Try to use the build in group by functionality in iBatis.  This way you only have to write one query that uses standard joins.  In your case you might have to use some outer joins to ensure that you get all tasks without jobs and all jobs without clients.  Then use the group by attribute in you result map.  Look to page 36 of the developers guild for an example of this functionality.Cheers,NathanOn Jul 21, 2006, at 8:54 AM, Debasish Dutta Roy wrote:You can do this in a simple manner. In the resultmap you need to provide the values properly. Here is an example.Here is the resultmap for Task     other properties ---    --- Your select for task ---    --- other properties ---   -- select statement for getting job info--   -- a normal ma with client bean properties mapped against columns --      -- select statement for getting client info--  This way you will only call getTask from your client and iBATIS will do the rest. You will save on database trips.Whatever you need to specify in #value# of 2nd and 3rd statement is taken from the "column" attribute. In this case jobId for 2nd select and "clientId" for the 3rd select. On 7/21/06, Fred Janon <[EMAIL PROTECTED]> wrote: Hi,I have 3 tables:Tasks:taskid int PKjobid int FKdescription StringJobs:jobid PKclientid int FKClients:clientid PKclientname String.How can I get the Clients:clientname via the Jobs table starting from a taskid? I know how to do it with 1 level on "indirection" but I don'tknow how to chain 2 queries...taskid -> jobid -> clientid -> clientname.I think it should be something like that:    mapped-statement="getClientNameByJobId"/>     select taskid,notes,assignedid,deptid,completion,duedate,  donedate,tasktypeid,startdate,jobidfrom tasks order by duedate mapped-statement="getClientNameById"/>result-map="clientNameResult">select clientid from jobs where jobid=#value# result-class="java.lang.String">select name from clients where clientid = #value#   ThanksFred

Re: Urgent help needed -- queryForList() problem

[Nathan Maves]

Could use please post your sqlMap query and result map. On Jul 21, 2006, at 9:57 AM, [EMAIL PROTECTED] wrote:        Hi,       In my application I am using Ibatis as datamapper and firebird as the backend.   In my database, I have 10 records and I want to get first 5 of them.   Here I am using   sqlMap.queryForList("getUsersList", null, 0, 5) to retrive the first 5 records from the database.     But the query is fetching only 3 records.   When I give the boundaries as (0,7) - it is fetching 4 records only.   but my requirenment is to get the records in between the specified boundaries. i.e, if boundary is specified as (0,7) it should retrieve 7 records, starting from 0.   I am unable to find the problem? Can any one help me in this regard?     Thanks n Regards, == Rambabu Piridi.    The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.   WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.  www.wipro.com 

Re: Mapped statement with chained queries ?

[Fred Janon]

Thanks Nathan, but I should have said that actually that's the other way 
around: a client has many jobs that has many tasks. So getting the 
client name for a task is a 1 to 1 mapping. I would not mind solving the 
problem with an outer join or a subquery but so far I haven't been able 
to write a suitable query and it is quite important for me since I don't 
duplicate any field in my schema but use foreign keys everywhere.


My goal is to list the task with the client name via the job, not much 
more than that: 2 levels on indirection.


Could you give me an example for solving the problem in one SQL query 
using outer join or anything else?


Thanks,

Fred

Nathan Maves wrote:
Although your solution will work, I would suggest another.  With your 
way you run into the classic n+1 problem.  One query to get the tasks, 
then n number of queries to get the jobs for those tasks.  You get the 
point.  That is why this is not the preferred way.


Try to use the build in group by functionality in iBatis.  This way 
you only have to write one query that uses standard joins.  In your 
case you might have to use some outer joins to ensure that you get all 
tasks without jobs and all jobs without clients.  Then use the group 
by attribute in you result map.  Look to page 36 of the developers 
guild for an example of this functionality.


Cheers,
Nathan


On Jul 21, 2006, at 8:54 AM, Debasish Dutta Roy wrote:

You can do this in a simple manner. In the resultmap you need to 
provide the values properly. Here is an example.


Here is the resultmap for Task


  
   other properties ---


   --- Your select for task ---



  
 --- other properties ---


   -- select statement for getting job info--



  -- a normal ma with client bean properties mapped against 
columns --



   -- select statement for getting client info--


This way you will only call getTask from your client and iBATIS will 
do the rest. You will save on database trips.
Whatever you need to specify in #value# of 2nd and 3rd statement is 
taken from the "column" attribute. In this case jobId for 2nd select 
and "clientId" for the 3rd select.


On 7/21/06, *Fred Janon* <[EMAIL PROTECTED] > 
wrote:


Hi,

I have 3 tables:

Tasks:
taskid int PK
jobid int FK
description String

Jobs:
jobid PK
clientid int FK

Clients:
clientid PK
clientname String.

How can I get the Clients:clientname via the Jobs table starting
from a
taskid? I know how to do it with 1 level on "indirection" but I don't
know how to chain 2 queries...
taskid -> jobid -> clientid -> clientname.

I think it should be something like that:

  




  

  
select taskid,notes,assignedid,deptid,completion,duedate,
  donedate,tasktypeid,startdate,jobid
from tasks order by duedate
  

  


  

  
select clientid from jobs where jobid=#value#
  

  
select name from clients where clientid = #value#
  

Thanks

Fred






begin:vcard
fn:fjanon
n:Janon;Fred
email;internet:[EMAIL PROTECTED]
title:Sr S/W Engineer & Architect
x-mozilla-html:TRUE
url:http://www.geocities.com/fjanon
version:2.1
end:vcard

Re: Mapped statement with chained queries ?

[Fred Janon]

Hi Nathan,

Which developer guide are you referring to? Version 1 or 2? Data Mapper? 
I couldn't find anything on pages 36 in any of the guides I have, v1 or 2.


Thanks

Fred

Nathan Maves wrote:
Although your solution will work, I would suggest another.  With your 
way you run into the classic n+1 problem.  One query to get the tasks, 
then n number of queries to get the jobs for those tasks.  You get the 
point.  That is why this is not the preferred way.


Try to use the build in group by functionality in iBatis.  This way 
you only have to write one query that uses standard joins.  In your 
case you might have to use some outer joins to ensure that you get all 
tasks without jobs and all jobs without clients.  Then use the group 
by attribute in you result map.  Look to page 36 of the developers 
guild for an example of this functionality.


Cheers,
Nathan


On Jul 21, 2006, at 8:54 AM, Debasish Dutta Roy wrote:

You can do this in a simple manner. In the resultmap you need to 
provide the values properly. Here is an example.


Here is the resultmap for Task


  
   other properties ---


   --- Your select for task ---



  
 --- other properties ---


   -- select statement for getting job info--



  -- a normal ma with client bean properties mapped against 
columns --



   -- select statement for getting client info--


This way you will only call getTask from your client and iBATIS will 
do the rest. You will save on database trips.
Whatever you need to specify in #value# of 2nd and 3rd statement is 
taken from the "column" attribute. In this case jobId for 2nd select 
and "clientId" for the 3rd select.


On 7/21/06, *Fred Janon* <[EMAIL PROTECTED] > 
wrote:


Hi,

I have 3 tables:

Tasks:
taskid int PK
jobid int FK
description String

Jobs:
jobid PK
clientid int FK

Clients:
clientid PK
clientname String.

How can I get the Clients:clientname via the Jobs table starting
from a
taskid? I know how to do it with 1 level on "indirection" but I don't
know how to chain 2 queries...
taskid -> jobid -> clientid -> clientname.

I think it should be something like that:

  




  

  
select taskid,notes,assignedid,deptid,completion,duedate,
  donedate,tasktypeid,startdate,jobid
from tasks order by duedate
  

  


  

  
select clientid from jobs where jobid=#value#
  

  
select name from clients where clientid = #value#
  

Thanks

Fred






begin:vcard
fn:fjanon
n:Janon;Fred
email;internet:[EMAIL PROTECTED]
title:Sr S/W Engineer & Architect
x-mozilla-html:TRUE
url:http://www.geocities.com/fjanon
version:2.1
end:vcard

ibatis, eclipse, and tomcat

[Aaron Luke]

Hi-These are 2 newbie questions...  I searched everywhere for the answer... if i do get the answer (if it didn't exist elsewhere), I promise to put it up in a tutorial for people looking to do the same...All I'm trying to do is make my way through the iBATIS SQLMaps tutorial (
http://cvs.apache.org/dist/ibatis/ibatis.java/docs/iBATIS-SqlMaps-2-Tutorial.pdf), but I'm trying to do this on Tomcat using an Eclipse environment.
The first problem is that I'm wondering where the best place to put the MyAppSqlConfig instantiation if you are using just JSP... Do I just instantiate a sqlMap at the top of each JSP page?  Or is there a better way to do this?
The second problem is that I get an error from Eclipse in the IDE when I enter the statement "import com.ibatis.sqlmap.client.*;" at the top of file for a java class that is used by our jsp test page.  The error says that the import cannot be resolved.  I have put both of the jar files (
ibatis-sqlmap-2.jar and ibatis-common-2.jar) in the WEB-INF/lib directory by drag-n-drop into the IDE.  That didn't work so I also put both of the jar files in the tomcat/shared/lib directory as well... that didn't work either... what's wrong?  sorry i know this is more of an eclipse/tomcat question... 
Thank you for any help in advance...  we are evaluating the ability of Java to do quick-and-dirty prototyping and iBATIS/Eclipse/Tomcat look like they have a lot of potential compared to what we usually use (Perl, Python, etc), in additional to being able to grow into a large-scale system...  It's just the learning curve...
Thanks-Aaron