NutzCN Logo
问答 拼接sql时,如果参数是Email如何拼接?
发布于 128天前 作者 qq_d6d46f6d 109 次浏览 复制 上一个帖子 下一个帖子
标签:

比如条件中有like 'xxx@gmail.com'
直接拼接执行的时候,@gmail会变成null
使用 "like @email"拼接成一个sql字符串,然后create出来,
再使用sql.params().set("email", email);
执行前看起来sql是正确的,执行的时候,@gmail也会变成null
拼接使用@@email
set("@email",email)无法替换
所以不知道怎么拼才对。
求教教我

6 回复

因为之前拼接有like 带%的时候也会报错,所以请问like 这种改怎么拼

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);

这一大堆我是没法看的, 我只能告诉你正确的写法是怎样的

例如这种

sb.append(" and m_company.company_name_cn like '%" + companyName + "%'");

//应该写成

sb.append(" and m_company.company_name_cn like @companyName ");

sql.set("companyName", "%" + companyName  "%");
添加回复
请先登陆
回到顶部