比如条件中有like 'xxx@gmail.com'
直接拼接执行的时候，@gmail会变成null
使用 "like @email"拼接成一个sql字符串，然后create出来，
再使用sql.params().set("email", email);
执行前看起来sql是正确的，执行的时候，@gmail也会变成null
拼接使用@@email
set("@email",email)无法替换
所以不知道怎么拼才对。
求教教我
6 回复
Sql sql = Sqls.create("select * from t_user where email=@email");
sql.params.set("email", "abc@gmail.com");
set的key怎么会加@呢?
log如下
DEBUG - select count(1) from (SELECT
m_account.id,
m_account.account_id,
m_account.account_name,
m_account.`password`,
m_account.id_type,
m_account.id_no,
m_account.mobile,
m_account.email,
m_account.deleted,
m_account.remark,
m_account.last_login_dt,
m_account.register_id,
m_account.last_login_ip,
m_user_company.company_id,
m_user_company.onduty,
m_company.company_name_cn
FROM
m_account
LEFT JOIN m_user_company
ON m_user_company.account_id = m_account.id
LEFT JOIN m_company
ON m_company.id = m_user_company.company_id where m_account.deleted = 0 and m_account.email='morningchin?.es')as _nutz_tmp_02alidkgesiriqkj4bt56ie760
| 1 |
|------|
| NULL |
For example:> "select count(1) from (SELECT
m_account.id,
m_account.account_id,
m_account.account_name,
m_account.`password`,
m_account.id_type,
m_account.id_no,
m_account.mobile,
m_account.email,
m_account.deleted,
m_account.remark,
m_account.last_login_dt,
m_account.register_id,
m_account.last_login_ip,
m_user_company.company_id,
m_user_company.onduty,
m_company.company_name_cn
FROM
m_account
LEFT JOIN m_user_company
ON m_user_company.account_id = m_account.id
LEFT JOIN m_company
ON m_company.id = m_user_company.company_id where m_account.deleted = 0 and m_account.email='morningchin'NULL'.es')as _nutz_tmp_02alidkgesiriqkj4bt56ie760"
2017-03-16 15:44:12,165 org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:97) DEBUG - SQLException
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3321)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3306)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3345)
at com.mysql.jdbc.PreparedStatement.setNull(PreparedStatement.java:3384)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.setNull(DruidPooledPreparedStatement.java:266)
at org.nutz.dao.jdbc.Jdbcs$Adaptor$1.set(Jdbcs.java:270)
at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:269)
at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:53)
at org.nutz.plugins.cache.dao.CachedNutDaoExecutor._exec(CachedNutDaoExecutor.java:317)
at org.nutz.plugins.cache.dao.CachedNutDaoExecutor.exec(CachedNutDaoExecutor.java:187)
at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:66)
at org.nutz.dao.impl.interceptor.DaoLogInterceptor.filter(DaoLogInterceptor.java:22)
at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:64)
at org.nutz.dao.DaoInterceptorChain.invoke(DaoInterceptorChain.java:139)
at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:147)
at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithoutTransaction(NutDaoRunner.java:114)
at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:81)
at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:70)
at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:239)
at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:247)
at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:235)
at org.nutz.dao.impl.NutDao.execute(NutDao.java:977)
at org.nutz.dao.util.Daos.queryCount(Daos.java:362)
at com.timemans.common.base.Service.data(Service.java:394)
at com.timemans.business.controllers.user.MAccountController.data(MAccountController.java:201)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:25)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:58)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at com.timemans.frame.common.processor.XssSqlFilterProcessor.process(XssSqlFilterProcessor.java:35)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at com.timemans.frame.common.processor.NutShiroProcessor.process(NutShiroProcessor.java:52)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:123)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at com.timemans.frame.common.processor.GlobalsSettingProcessor.process(GlobalsSettingProcessor.java:36)
at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
at com.timemans.frame.common.processor.LogTimeProcessor.process(LogTimeProcessor.java:21)
at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:44)
at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:67)
at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
at org.nutz.mvc.NutFilter.doFilter(NutFilter.java:198)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)
at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:509)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1104)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
2017-03-16 15:44:12,167 com.timemans.frame.common.processor.LogTimeProcessor.process(LogTimeProcessor.java:26) DEBUG - [POST]URI=/private/manager/account/data 16883ms
2017-03-16 15:44:12,167 org.nutz.mvc.impl.processor.FailProcessor.process(FailProcessor.java:28) WARN - Error@/private/manager/account/data :
org.nutz.dao.DaoException: !Nutz SQL Error: 'select count(1) from (SELECT
m_account.id,
m_account.account_id,
m_account.account_name,
m_account.`password`,
m_account.id_type,
m_account.id_no,
m_account.mobile,
m_account.email,
m_account.deleted,
m_account.remark,
m_account.last_login_dt,
m_account.register_id,
m_account.last_login_ip,
m_user_company.company_id,
m_user_company.onduty,
m_company.company_name_cn
FROM
m_account
LEFT JOIN m_user_company
ON m_user_company.account_id = m_account.id
LEFT JOIN m_company
ON m_company.id = m_user_company.company_id where m_account.deleted = 0 and m_account.email='morningchin'NULL'.es')as _nutz_tmp_02alidkgesiriqkj4bt56ie760'
PreparedStatement:
'select count(1) from (SELECT
m_account.id,
m_account.account_id,
m_account.account_name,
m_account.`password`,
m_account.id_type,
m_account.id_no,
m_account.mobile,
m_account.email,
m_account.deleted,
m_account.remark,
m_account.last_login_dt,
m_account.register_id,
m_account.last_login_ip,
m_user_company.company_id,
m_user_company.onduty,
m_company.company_name_cn
FROM
m_account
LEFT JOIN m_user_company
ON m_user_company.account_id = m_account.id
LEFT JOIN m_company
ON m_company.id = m_user_company.company_id where m_account.deleted = 0 and m_account.email='morningchin?.es'
代码如下
sb.append(" where m_account.deleted = 0 ") ;//只从上架商品挑选
if(!StringUtils.isEmpty(accountId))
{
sb.append(" and m_account.account_id like '%" + accountId + "%'");
}
//身份证
if(!StringUtils.isEmpty(idNos))
{
sb.append(" and m_account.id_no like '%" + idNos + "%'");
}
//用户名
if(!StringUtils.isEmpty(user_name))
{
sb.append(" and m_account.account_name like '%" + user_name + "%'");
}
//电话
if(!StringUtils.isEmpty(phoneNum))
{
sb.append(" and m_account.mobile like '%" + phoneNum + "%'");
}
//email
if(!StringUtils.isEmpty(email))
{
sb.append(" and m_account.email=@email");
}
//公司名称
if(!StringUtils.isEmpty(companyName))
{
sb.append(" and m_company.company_name_cn like '%" + companyName + "%'");
}
//是否在职
if(!StringUtils.isEmpty(duty))
{
sb.append(" and onduty ='" + duty + "'");
}
String sqlStr = "SELECT\n" +
"m_account.id,\n" +
"m_account.account_id,\n" +
"m_account.account_name,\n" +
"m_account.`password`,\n" +
"m_account.id_type,\n" +
"m_account.id_no,\n" +
"m_account.mobile,\n" +
"m_account.email,\n" +
"m_account.deleted,\n" +
"m_account.remark,\n" +
"m_account.last_login_dt,\n" +
"m_account.register_id,\n" +
"m_account.last_login_ip,\n" +
"m_user_company.company_id,\n" +
"m_user_company.onduty,\n" +
"m_company.company_name_cn\n" +
"FROM\n" +
"m_account\n" +
"LEFT JOIN m_user_company\n" +
"ON m_user_company.account_id = m_account.id \n" +
"LEFT JOIN m_company\n" +
"ON m_company.id = m_user_company.company_id";
if(!StringUtils.isEmpty(sb.toString()))
{
sqlStr += sb.toString();
}
Sql sql = Sqls.create(sqlStr);
sql.params().set("email", email);
添加回复
请先登陆