Re: n+1 is triggering multiple queries, 3?
Thanks Clinton, I will look into this myself when I have more time. I wish there were a MySql Profiler or something so that I could see what queries are actually being executed. Maybe I will step into your code if I get a chance. KrisClinton Begin [EMAIL PROTECTED] wrote: Kris,This is the right list ([EMAIL PROTECTED]).Sorry nobody responded. I had a quick look when you originally sent it, and I couldn't see how it was possible that the groupBy or a nested resultMap could cause additional queries...Perhaps someone else has thoughts? If not, you might have to write an isolated unit test and submit it through JIRA.Cheers,Clinton On 7/9/05, Kris Rasmussen [EMAIL PROTECTED] wrote: I have an n+1 mapping. When I look at mysql health in the administrator it apears that 3 queries are being executed instead of one. The first query gets executed when I retrieve the intial object via queryForObject. The next two queries are being executed when I attempt to access the List which stores the related objects from the main object. I am very curious what is going on since I don't know how Ibatis could even know what query to execute to get the items later? The following maps are used to retrive a single instance of classB with all of its classA children stored in one of its properties which is of course a list. resultMap id="getClassAResult" class="classA" ... some basic properties /resultMap resultMap id="getClassBResult" class="classB" result property="primKey1" column="classB.primKey1"/ result property="primKey2" column="classB.primKey2"/ ... some more properties /resultMap resultMap id="getClassBWithClassAsResult" class="classB" extends="getClassBResult" groupBy="primKey1,primKey2" result property="classAs" resultMap="namespace.getClassAResult"/ /resultMap and my select statement... select id="getClassBWithClassAs" parameterClass="keyClass" resultMap="getClassBWithClassAsResult" SELECT classB.*,classA.* FROM classB ,classA WHERE classB.primKey1=#primKey1# AND classB.primKey2=#primKey2# AND gallery_image.SiteId=#siteId# AND gallery_image.BlockId=#blockId# GROUP BY classB.primKey1,classB.primKey2,classA.primKey3 /select __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com Start your day with Yahoo! - make it your home page
Re: [OT]SQL question
Hello this is what i tried select * from test1 where substring(type1,1,1) '0' and substring(type1,2,1) '0' i think it works --- Larry Meadors [EMAIL PROTECTED] wrote: Brute force? select * from test1 where type1 not like '%1%' and type1 not like '%2%' and type1 not like '%3%' and type1 not like '%4%' and type1 not like '%5%' and type1 not like '%6%' and type1 not like '%7%' and type1 not like '%8%' and type1 not like '%9%' and type1 not like '%0%' Crude, but effective... Larry On 7/13/05, Ashish Kulkarni [EMAIL PROTECTED] wrote: Hello I have a table called test1 in DB2 database, in this table there is column name type1 which is 2 char in lenght, I want to write a SQL statement where i want to select all the records where type1 does not have any numeric data for example if following is data in type1 column AB CA AA 12 23 A3 then this sql statment must select only AB, CA and AA and ignore 12,23,A3 because there is a numeric data in any field Ashish __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com A$HI$H Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
Re: [jira] Commented: (IBATIS-170) i am not sure it is a bug!
PLEASE! PLEASE! PLEASE!
If you are not 99.999% sure it is a bug, do not put it in JIRA.
Larry
On 7/14/05, leo zhang (JIRA) ibatis-dev@incubator.apache.org wrote:
[
http://issues.apache.org/jira/browse/IBATIS-170?page=comments#action_12315824
]
leo zhang commented on IBATIS-170:
--
sorry, the sql statement is this:
insert into test (col) values (to_date('2005-07-14 16:06:48','-mm-dd
hh:mi:ss'));
i am not sure it is a bug!
--
Key: IBATIS-170
URL: http://issues.apache.org/jira/browse/IBATIS-170
Project: iBatis for Java
Type: Bug
Components: SQL Maps
Versions: 2.1.0
Environment: windows xp, professional oracle9i, eclipse
Reporter: leo zhang
i use oralce9i database.
the table names test;
CREATE TABLE TEST
(
col DATE
);
sqlmap like this
insert id = product.testinsert parameterClass=java.lang.String
insert into test (col) values (to_date('$timestamp$','-mm-dd
hh:mi:ss'));
/insert
java code like this:
..
long nCurrentTime = System.currentTimeMillis();
Timestamp ts = new Timestamp(nCurrentTime);
broker.insert(product.testinsert,ts.toLocaleString());
..
the ibatis error is :
- @@{conn-10} Connection$$
- @@{pstm-11} PreparedStatement: insert into test (col) values
(to_date('2005-07-14 16:06:48.921','-mm-dd hh:mi:ss')); $$
- @@{pstm-11} Parameters: []$$
- @@{pstm-11} Types: []$$
- [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] 2005.07.14 at
16:06:[EMAIL PROTECTED]
--- The error occurred in com/ctbr/config/ibatis/sqlmap/Product.xml.
--- The error occurred while applying a parameter map.
--- Check the product.testinsert-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: ORA-00911: invalid characters.
]
but i use toad, the follow statement can execute successful.
insert into test (col) values (to_date('2005-07-14
16:06:48.921','-mm-dd hh:mi:ss'));
i am mad! please help me!
leo
[EMAIL PROTECTED]
[EMAIL PROTECTED]
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (IBATIS-170) i am not sure it is a bug!
I think, it is not a bug :)
It doesn't work for me.
But this does
insert into testddo (col) values (to_date('2005-07-14 16:06:48','-mm-dd
hh24:mi:ss'));
hours: 16 should be declared as HH24 not hh
Anyway:)
Darek Dober
- Original Message -
From: Larry Meadors [EMAIL PROTECTED]
To: dev@ibatis.apache.org; user-java@ibatis.apache.org;
user-cs@ibatis.apache.org
Sent: Thursday, July 14, 2005 1:51 PM
Subject: Re: [jira] Commented: (IBATIS-170) i am not sure it is a bug!
PLEASE! PLEASE! PLEASE!
If you are not 99.999% sure it is a bug, do not put it in JIRA.
Larry
On 7/14/05, leo zhang (JIRA) ibatis-dev@incubator.apache.org wrote:
[
http://issues.apache.org/jira/browse/IBATIS-170?page=comments#action_12315824 ]
leo zhang commented on IBATIS-170:
--
sorry, the sql statement is this:
insert into test (col) values (to_date('2005-07-14 16:06:48','-mm-dd
hh:mi:ss'));
i am not sure it is a bug!
--
Key: IBATIS-170
URL: http://issues.apache.org/jira/browse/IBATIS-170
Project: iBatis for Java
Type: Bug
Components: SQL Maps
Versions: 2.1.0
Environment: windows xp, professional oracle9i, eclipse
Reporter: leo zhang
i use oralce9i database.
the table names test;
CREATE TABLE TEST
(
col DATE
);
sqlmap like this
insert id = product.testinsert parameterClass=java.lang.String
insert into test (col) values (to_date('$timestamp$','-mm-dd
hh:mi:ss'));
/insert
java code like this:
..
long nCurrentTime = System.currentTimeMillis();
Timestamp ts = new Timestamp(nCurrentTime);
broker.insert(product.testinsert,ts.toLocaleString());
..
the ibatis error is :
- @@{conn-10} Connection$$
- @@{pstm-11} PreparedStatement: insert into test (col) values
(to_date('2005-07-14 16:06:48.921','-mm-dd hh:mi:ss')); $$
- @@{pstm-11} Parameters: []$$
- @@{pstm-11} Types: []$$
- [EMAIL PROTECTED] [EMAIL PROTECTED]
[EMAIL PROTECTED] 2005.07.14 at
16:06:[EMAIL PROTECTED]
--- The error occurred in com/ctbr/config/ibatis/sqlmap/Product.xml.
--- The error occurred while applying a parameter map.
--- Check the product.testinsert-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: ORA-00911: invalid characters.
]
but i use toad, the follow statement can execute successful.
insert into test (col) values (to_date('2005-07-14
16:06:48.921','-mm-dd hh:mi:ss'));
i am mad! please help me!
leo
[EMAIL PROTECTED]
[EMAIL PROTECTED]
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: n+1 is triggering multiple queries, 3?
p6spy is also a great sql logging tool. On 7/14/05, Ron Grabowski [EMAIL PROTECTED] wrote: The Java version of iBATIS has support for logging all sql sent to the database. Here's some sample output: http://issues.apache.org/jira/browse/IBATISNET-35 --- Kris Rasmussen [EMAIL PROTECTED] wrote: Thanks Clinton, I will look into this myself when I have more time. I wish there were a MySql Profiler or something so that I could see what queries are actually being executed. Maybe I will step into your code if I get a chance. Kris Clinton Begin [EMAIL PROTECTED] wrote: Kris, This is the right list ([EMAIL PROTECTED]). Sorry nobody responded. I had a quick look when you originally sent it, and I couldn't see how it was possible that the groupBy or a nested resultMap could cause additional queries... Perhaps someone else has thoughts? If not, you might have to write an isolated unit test and submit it through JIRA. Cheers, Clinton On 7/9/05, Kris Rasmussen [EMAIL PROTECTED] wrote:I have an n+1 mapping. When I look at mysql health in the administrator it apears that 3 queries are being executed instead of one. The first query gets executed when I retrieve the intial object via queryForObject. The next two queries are being executed when I attempt to access the List which stores the related objects from the main object. I am very curious what is going on since I don't know how Ibatis could even know what query to execute to get the items later? The following maps are used to retrive a single instance of classB with all of its classA children stored in one of its properties which is of course a list. resultMap id=getClassAResult class=classA ... some basic properties /resultMap resultMap id=getClassBResult class=classB result property=primKey1 column=classB.primKey1/ result property=primKey2 column=classB.primKey2/ ... some more properties /resultMap resultMap id=getClassBWithClassAsResult class=classB extends=getClassBResult groupBy=primKey1,primKey2 result property=classAs resultMap=namespace.getClassAResult/ /resultMap and my select statement... select id=getClassBWithClassAs parameterClass=keyClass resultMap=getClassBWithClassAsResult SELECT classB.*,classA.* FROM classB ,classA WHERE classB.primKey1=#primKey1# AND classB.primKey2=#primKey2# AND gallery_image.SiteId=#siteId# AND gallery_image.BlockId=#blockId# GROUP BY classB.primKey1,classB.primKey2,classA.primKey3 /select __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - Start your day with Yahoo! - make it your home page
Using WHERE IN along with AND
Title: Using WHERE IN along with AND I have the following select and it throws a UncategorizedSQL exception select id=listDetailsForOrderIds parameterClass=java.util.List resultMap=list-rundetail-result SELECT R.ORD_ID AS ORD_ID, PART_ID, CLNT_ID_N, PLAN_N, PROD_ID_C, OFFR_C, PRTY_N, EMAIL_ADDR_X FROM TABLE1 R, TABLE2 O dynamic prepend=WHERE iterate open= R.ORD_ID IN ( close=) conjunction=, #orderIdList[]# /iterate isNotEmpty prepend=AND property=orderIdList R.ORD_ID = O.ORD_ID /isNotEmpty /dynamic /select It works correctly without the isNotEmpty tag. But as soon as I add the isNotEmpty construct it throws a Uncategorized SQL Exception. Can we not pass a java.util.List property type to isNotEmpty tag? orderIdList is a java.util.List
