MyBatis And Query Problem??
2,602 views
Skip to first unread message
chameleon
Dec 16, 2011, 9:09:56 AM12/16/11
to mybatis-user
Hello, I am just newbie, I try to create DAO it working well before I
create one method in dao.. this is this method mapping:
<select id="getDosenByNameAndPassword" resultMap="results"
useCache="true">
SELECT *FROM dosen d
WHERE username = #(user_name) AND password = #(passwd)
</select>
and the resultmap is like this:
<resultMap id="results" type="Dosen">
<result property="id" column="id_dosen" />
<result property="nip" column="nip"/>
<result property="nama" column="nama_dosen"/>
<result property="user_name" column="username"/>
<result property="passwd" column="password"/>
</resultMap>
whats wrong in my query? it generate error..This is the error:
SEVERE: >>org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'AND
password = #(password)' at line 3
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'AND
password = #(password)' at line 3
; bad SQL grammar []; nested exception is
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'AND
password = #(password)' at line 3
[SQL: 1064, 42000]
>>com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND
>> password = #(password)' at line 3
>> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
>> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
>> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
>> at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
>> at com.mysql.jdbc.Util.getInstance(Util.java:386)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
>> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
>> at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
>> at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1362)
>> at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
>> at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:597)
>> at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:45)
>> at $Proxy27.execute(Unknown Source)
>> at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:39)
>> at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55)
>> at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41)
>> at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:238)
>> at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:112)
>> at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:60)
>> at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:78)
>> at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:72)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:597)
>> at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)
>> at $Proxy7.selectList(Unknown Source)
>> at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:189)
>> at org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:100)
>> at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:70)
>> at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
>> at $Proxy20.getDosenByNameAndPassword(Unknown Source)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:597)
>> at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
>> at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
>> at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
>> at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
>> at $Proxy21.getDosenByNameAndPassword(Unknown Source)
>> at com.os.sipm.model.dosen.DosenService.getDosenByNameAndPassword(DosenService.java:63)
>> at com.os.sipm.model.dosen.DosenService$$FastClassByCGLIB$$2256aa64.invoke(<generated>)
>> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
>> at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
>> at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
>> at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
>> at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
>> at com.os.sipm.model.dosen.DosenService$$EnhancerByCGLIB$$cf139f8c.getDosenByNameAndPassword(<generated>)
>> at com.os.sipm.controller.general.LoginController.onClick$btnLogin(LoginController.java:46)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:597)
>> at org.zkoss.zk.ui.event.GenericEventListener.onEvent(GenericEventListener.java:87)
>> at org.zkoss.zk.ui.impl.EventProcessor.process0(EventProcessor.java:192)
>> at org.zkoss.zk.ui.impl.EventProcessor.process(EventProcessor.java:138)
>> at org.zkoss.zk.ui.impl.EventProcessingThreadImpl.process0(EventProcessingThreadImpl.java:517)
>> at org.zkoss.zk.ui.impl.EventProcessingThreadImpl.sendEvent(EventProcessingThreadImpl.java:121)
>> at org.zkoss.zk.ui.event.Events.sendEvent(Events.java:319)
>> at org.zkoss.zk.ui.event.Events.sendEvent(Events.java:329)
>> at org.zkoss.zk.ui.AbstractComponent$ForwardListener.onEvent(AbstractComponent.java:3048)
>> at org.zkoss.zk.ui.impl.EventProcessor.process0(EventProcessor.java:192)
>> at org.zkoss.zk.ui.impl.EventProcessor.process(EventProcessor.java:138)
>> at org.zkoss.zk.ui.impl.EventProcessingThreadImpl.process0(EventProcessingThreadImpl.java:517)
>> at org.zkoss.zk.ui.impl.EventProcessingThreadImpl.run(EventProcessingThreadImpl.java:444)
create one method in dao.. this is this method mapping:
<select id="getDosenByNameAndPassword" resultMap="results"
useCache="true">
SELECT *FROM dosen d
WHERE username = #(user_name) AND password = #(passwd)
</select>
and the resultmap is like this:
<resultMap id="results" type="Dosen">
<result property="id" column="id_dosen" />
<result property="nip" column="nip"/>
<result property="nama" column="nama_dosen"/>
<result property="user_name" column="username"/>
<result property="passwd" column="password"/>
</resultMap>
whats wrong in my query? it generate error..This is the error:
SEVERE: >>org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'AND
password = #(password)' at line 3
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'AND
password = #(password)' at line 3
; bad SQL grammar []; nested exception is
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'AND
password = #(password)' at line 3
[SQL: 1064, 42000]
>>com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND
>> password = #(password)' at line 3
>> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
>> at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
>> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
>> at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
>> at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
>> at com.mysql.jdbc.Util.getInstance(Util.java:386)
>> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
>> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
>> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
>> at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
>> at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1362)
>> at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
>> at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:597)
>> at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:45)
>> at $Proxy27.execute(Unknown Source)
>> at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:39)
>> at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55)
>> at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41)
>> at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:238)
>> at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:112)
>> at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:60)
>> at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:78)
>> at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:72)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:597)
>> at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)
>> at $Proxy7.selectList(Unknown Source)
>> at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:189)
>> at org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:100)
>> at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:70)
>> at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
>> at $Proxy20.getDosenByNameAndPassword(Unknown Source)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:597)
>> at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
>> at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
>> at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
>> at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
>> at $Proxy21.getDosenByNameAndPassword(Unknown Source)
>> at com.os.sipm.model.dosen.DosenService.getDosenByNameAndPassword(DosenService.java:63)
>> at com.os.sipm.model.dosen.DosenService$$FastClassByCGLIB$$2256aa64.invoke(<generated>)
>> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
>> at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
>> at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
>> at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
>> at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
>> at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
>> at com.os.sipm.model.dosen.DosenService$$EnhancerByCGLIB$$cf139f8c.getDosenByNameAndPassword(<generated>)
>> at com.os.sipm.controller.general.LoginController.onClick$btnLogin(LoginController.java:46)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:597)
>> at org.zkoss.zk.ui.event.GenericEventListener.onEvent(GenericEventListener.java:87)
>> at org.zkoss.zk.ui.impl.EventProcessor.process0(EventProcessor.java:192)
>> at org.zkoss.zk.ui.impl.EventProcessor.process(EventProcessor.java:138)
>> at org.zkoss.zk.ui.impl.EventProcessingThreadImpl.process0(EventProcessingThreadImpl.java:517)
>> at org.zkoss.zk.ui.impl.EventProcessingThreadImpl.sendEvent(EventProcessingThreadImpl.java:121)
>> at org.zkoss.zk.ui.event.Events.sendEvent(Events.java:319)
>> at org.zkoss.zk.ui.event.Events.sendEvent(Events.java:329)
>> at org.zkoss.zk.ui.AbstractComponent$ForwardListener.onEvent(AbstractComponent.java:3048)
>> at org.zkoss.zk.ui.impl.EventProcessor.process0(EventProcessor.java:192)
>> at org.zkoss.zk.ui.impl.EventProcessor.process(EventProcessor.java:138)
>> at org.zkoss.zk.ui.impl.EventProcessingThreadImpl.process0(EventProcessingThreadImpl.java:517)
>> at org.zkoss.zk.ui.impl.EventProcessingThreadImpl.run(EventProcessingThreadImpl.java:444)
Help me please,
And where can I get advanced query mybatis tutorial?
Larry Meadors
Dec 16, 2011, 9:38:20 AM12/16/11
to mybati...@googlegroups.com
It's #{}, not #()
Larry
chameleon
Dec 16, 2011, 10:21:27 AM12/16/11
to mybatis-user
On Dec 16, 9:38 pm, Larry Meadors <larry.mead...@gmail.com> wrote:
> It's #{}, not #()
>
> Larry
>
Wow, thanks to larry..
Where can I get some great tutorial? I read Ibatis in action, but some
xml format different with my project. I am confused.
and then, must I create the xml mapper manually? is there tools to
create xml mapper automatically?
c c
Dec 16, 2011, 7:42:59 PM12/16/11
to mybati...@googlegroups.com
just read the mybatis user guid first carefully
and then do the project
do not hurry
one by one! thks
and then do the project
do not hurry
one by one! thks
2011/12/16 chameleon <chameleo...@gmail.com>
chameleon
Dec 18, 2011, 2:25:17 AM12/18/11
to mybatis-user
Ok, thats sound good.
No Code generator, and no more book to get resources except the formal
documentation in the web.
So, I must be patient and learn continously.
Thank you for help.
:)
No Code generator, and no more book to get resources except the formal
documentation in the web.
So, I must be patient and learn continously.
Thank you for help.
:)
Simone Tripodi
Dec 18, 2011, 4:56:14 AM12/18/11
to mybati...@googlegroups.com
Hi Chamelon,
did you already check the MyBatis Generator in the wiki page[1]?
HTH,
Simo
did you already check the MyBatis Generator in the wiki page[1]?
HTH,
Simo
[1] http://code.google.com/p/mybatis/wiki/Generator
http://people.apache.org/~simonetripodi/
http://simonetripodi.livejournal.com/
http://twitter.com/simonetripodi
http://www.99soft.org/
Ryuken
Dec 18, 2011, 7:22:35 AM12/18/11
to mybatis-user
Yes, of course I've read it.. But the documentation is not user
friendly, and there are too few resources and articles about this
plugin..
friendly, and there are too few resources and articles about this
plugin..
Simone Tripodi
Dec 19, 2011, 7:55:43 AM12/19/11
to mybati...@googlegroups.com
Please checkout the html documentation[1] it is really detailed, I'll
publish it as soon as I have the time to migrate it in the new format.
please let us know,
-Simo
publish it as soon as I have the time to migrate it in the new format.
please let us know,
-Simo
[1] https://mybatis.googlecode.com/svn/sub-projects/generator/trunk/core/mybatis-generator-core/doc/html
http://people.apache.org/~simonetripodi/
http://simonetripodi.livejournal.com/
http://twitter.com/simonetripodi
http://www.99soft.org/
Reply all
Reply to author
Forward
0 new messages