Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 queryByJoin 异常
发布于 1832天前 作者 Hamming 1504 次浏览 复制 上一个帖子 下一个帖子
标签: 无

user 关联member 之前fetchLinks 查询没有问题 现在改成queryByJoin异常了 

@Table("sys_user")
public class User extends BaseModel implements Serializable {
    private static final long serialVersionUID = 1L;

    @Name
    @Column
    @Comment("ID")
    @ColDefine(type = ColType.VARCHAR, width = 32)
    @Prev(els = {@EL("uuid()")})
    private String id;

    /**
     * 部门ID
     */
    @Column("dept_id")
    @Comment("部门ID")
    private String deptId;

    /**
     * 部门父ID
     */
    private String parentId;

    /**
     * 登录名称
     */
    @Column("login_name")
    @Comment("登录名称")
    private String loginName;

    /**
     * 用户名称
     */
    @Column("user_name")
    @Comment("用户名称")
    private String userName;

    /**
     * 密码
     */
    @Column("password")
    @Comment("密码")
    private String password;

    /**
     * 盐加密
     */
    @Column("salt")
    @Comment("盐加密")
    private String salt;

    /**
     * 用户邮箱
     */
    @Column("email")
    @Comment("用户邮箱")
    private String email;

    /**
     * 手机号码
     */
    @Column("phonenumber")
    @Comment("手机号码")
    private String phonenumber;

    /**
     * 用户性别
     */
    @Column
    @Comment("用户性别")
    private String gender;

    /**
     * 用户头像
     */
    @Column
    @Comment("用户头像")
    private String avatar;

    @One(field = "avatar")
    private Image image;

    /**
     * 帐号状态（0正常 1停用）
     */
    @Column
    @Comment("帐号状态（0正常 1停用） ")
    private boolean status;

    /**
     * 删除标志（0代表存在 1代表删除）
     */
    @Column("del_flag")
    @Comment("删除标记")
    @ColDefine(type = ColType.BOOLEAN)
    private boolean delFlag;

    /**
     * 最后登陆IP
     */
    @Column("login_ip")
    @Comment("最后登陆IP")
    private String loginIp;

    /**
     * 最后登陆时间
     */
    @Column("login_date")
    @Comment("最后登陆时间")
    private Date loginDate;

    /**
     * 部门对象
     */
    @One(field = "deptId")
    private Dept dept;

    /**
     * 角色集合
     */
    @ManyMany(from = "user_id", relation = "sys_user_role", to = "role_id")
    private List<Role> roles;

    /**
     * 角色组
     */
    private String roleIds;

    /**
     * 岗位组
     */
    private String postIds;

    /**
     * 创建者
     */
    @Column("create_by")
    @Comment("创建者 ")
    @Prev(els = @EL("$me.uid()"))
    private String createBy;

    /**
     * 创建时间
     */
    @Column("create_time")
    @Comment("创建时间 ")
    @Prev(els = {@EL("$me.now()")})
    private Date createTime;

    /**
     * 更新者
     */
    @Column("update_by")
    @Comment("更新者 ")
    @Prev(els = @EL("$me.uid()"))
    private String updateBy;

    /**
     * 更新时间
     */
    @Column("update_time")
    @Comment("更新时间 ")
    @Prev(els = {@EL("$me.now()")})
    private Date updateTime;

    /** 关联的会员Id */
    @Column("member_id")
    @Comment("关联的会员Id")
    private String memberId;

    /** 关联会员的角色 */
    @Column("member_role")
    @Comment("关联会员的角色")
    private String memberRole;

    @One(field = "memberId")
    private Member member;
}

异常信息如下

[DEBUG] 11:05:27.113 org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:103) - SQLException
java.sql.SQLSyntaxErrorException: 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 'member ON sys_user.member_id = member.id  ORDER BY create_time DESC   LIMIT 0, 1' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1218)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2883)
	at com.alibaba.druid.filter.FilterAdapter.statement_executeQuery(FilterAdapter.java:2514)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_executeQuery(FilterEventAdapter.java:302)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2880)
	at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.executeQuery(StatementProxyImpl.java:221)
	at com.alibaba.druid.pool.DruidPooledStatement.executeQuery(DruidPooledStatement.java:297)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:250)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:59)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:65)
	at org.nutz.dao.impl.interceptor.DaoLogInterceptor.filter(DaoLogInterceptor.java:22)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:63)
	at org.nutz.dao.DaoInterceptorChain.invoke(DaoInterceptorChain.java:138)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:163)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithoutTransaction(NutDaoRunner.java:130)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:97)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:86)
	at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:242)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:254)
	at org.nutz.dao.impl.NutDao.queryByJoin(NutDao.java:1284)
	at org.nutz.dao.impl.NutDao.queryByJoin(NutDao.java:1274)
	at io.nutz.nutzsite.common.base.Service.tableList(Service.java:343)
	at io.nutz.nutzsite.module.sys.controllers.UserController.list(UserController.java:94)
	at io.nutz.nutzsite.module.sys.controllers.UserController$FM$list$408746d29ae439cb459b03dfdeac110b.invoke(UserController.java)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:31)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.plugins.validation.ValidationProcessor.process(ValidationProcessor.java:56)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:38)
	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 io.nutz.nutzsite.common.mvc.processor.XssSqlFilterProcessor.process(XssSqlFilterProcessor.java:80)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.integration.shiro.NutShiroProcessor.process(NutShiroProcessor.java:139)
	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 io.nutz.nutzsite.common.mvc.processor.GlobalsSettingProcessor.process(GlobalsSettingProcessor.java:49)
	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.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:202)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
	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.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
	at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
	at org.nutz.mvc.WhaleFilter.doFilter(WhaleFilter.java:171)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1596)
	at org.eclipse.jetty.websocket.server.WebSocketUpgradeFilter.doFilter(WebSocketUpgradeFilter.java:226)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:545)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:536)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1592)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1296)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1562)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1211)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.Server.handle(Server.java:500)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:386)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:562)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:378)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
	at java.lang.Thread.run(Thread.java:748)
[WARN ] 11:05:27.114 io.nutz.nutzsite.common.mvc.processor.ErrorProcessor.process(ErrorProcessor.java:41) - Error@/sys/user/list :
org.nutz.dao.DaoException: !Nutz SQL Error: 'SELECT sys_user.id as id,sys_user.dept_id as dept_id,sys_user.login_name as login_name,sys_user.user_name as user_name,sys_user.password as password,sys_user.salt as salt,sys_user.email as email,sys_user.phonenumber as phonenumber,sys_user.gender as gender,sys_user.avatar as avatar,sys_user.status as status,sys_user.del_flag as del_flag,sys_user.login_ip as login_ip,sys_user.login_date as login_date,sys_user.create_by as create_by,sys_user.create_time as create_time,sys_user.update_by as update_by,sys_user.update_time as update_time,sys_user.member_id as member_id,sys_user.member_role as member_role , dept.id as dept_z_id,dept.parent_id as dept_z_parent_id,dept.ancestors as dept_z_ancestors,dept.dept_name as dept_z_dept_name,dept.order_num as dept_z_order_num,dept.leader as dept_z_leader,dept.phone as dept_z_phone,dept.email as dept_z_email,dept.status as dept_z_status,dept.del_flag as dept_z_del_flag,dept.create_by as dept_z_create_by,dept.create_time as dept_z_create_time,dept.update_by as dept_z_update_by,dept.update_time as dept_z_update_time , member.id as member_z_id,member.login_name as member_z_login_name,member.user_name as member_z_user_name,member.gender as member_z_gender,member.email as member_z_email,member.password as member_z_password,member.salt as member_z_salt,member.user_image as member_z_user_image,member.signature as member_z_signature,member.status as member_z_status,member.device as member_z_device,member.platform as member_z_platform,member.openid as member_z_openid,member.remark as member_z_remark,member.del_flag as member_z_del_flag,member.identity_card_front as member_z_identity_card_front,member.identity_card_reverse as member_z_identity_card_reverse,member.audit_status as member_z_audit_status,member.leader_id as member_z_leader_id,member.employee_card_url as member_z_employee_card_url,member.agent_status as member_z_agent_status,member.sys_user_id as member_z_sys_user_id,member.member_role as member_z_member_role,member.real_name as member_z_real_name,member.id_card_no as member_z_id_card_no,member.service_fees_id as member_z_service_fees_id,member.parent_id as member_z_parent_id,member.ancestors as member_z_ancestors,member.agent_type as member_z_agent_type,member.corporate_account_url as member_z_corporate_account_url,member.corporate_id_card_front_url as member_z_corporate_id_card_front_url,member.business_license_url as member_z_business_license_url,member.corporate_id_card_back_url as member_z_corporate_id_card_back_url,member.openid_agent as member_z_openid_agent,member.openid_tenants as member_z_openid_tenants,member.openid_landlord as member_z_openid_landlord,member.create_by as member_z_create_by,member.create_time as member_z_create_time,member.update_by as member_z_update_by,member.update_time as member_z_update_time FROM sys_user LEFT JOIN sys_dept as dept ON sys_user.dept_id = dept.id LEFT JOIN app_member as member ON sys_user.member_id = member.id  ORDER BY create_time DESC   LIMIT 0, 10 '
PreparedStatement: 
'SELECT sys_user.id as id,sys_user.dept_id as dept_id,sys_user.login_name as login_name,sys_user.user_name as user_name,sys_user.password as password,sys_user.salt as salt,sys_user.email as email,sys_user.phonenumber as phonenumber,sys_user.gender as gender,sys_user.avatar as avatar,sys_user.status as status,sys_user.del_flag as del_flag,sys_user.login_ip as login_ip,sys_user.login_date as login_date,sys_user.create_by as create_by,sys_user.create_time as create_time,sys_user.update_by as update_by,sys_user.update_time as update_time,sys_user.member_id as member_id,sys_user.member_role as member_role , dept.id as dept_z_id,dept.parent_id as dept_z_parent_id,dept.ancestors as dept_z_ancestors,dept.dept_name as dept_z_dept_name,dept.order_num as dept_z_order_num,dept.leader as dept_z_leader,dept.phone as dept_z_phone,dept.email as dept_z_email,dept.status as dept_z_status,dept.del_flag as dept_z_del_flag,dept.create_by as dept_z_create_by,dept.create_time as dept_z_create_time,dept.update_by as dept_z_update_by,dept.update_time as dept_z_update_time , member.id as member_z_id,member.login_name as member_z_login_name,member.user_name as member_z_user_name,member.gender as member_z_gender,member.email as member_z_email,member.password as member_z_password,member.salt as member_z_salt,member.user_image as member_z_user_image,member.signature as member_z_signature,member.status as member_z_status,member.device as member_z_device,member.platform as member_z_platform,member.openid as member_z_openid,member.remark as member_z_remark,member.del_flag as member_z_del_flag,member.identity_card_front as member_z_identity_card_front,member.identity_card_reverse as member_z_identity_card_reverse,member.audit_status as member_z_audit_status,member.leader_id as member_z_leader_id,member.employee_card_url as member_z_employee_card_url,member.agent_status as member_z_agent_status,member.sys_user_id as member_z_sys_user_id,member.member_role as member_z_member_role,member.real_name as member_z_real_name,member.id_card_no as member_z_id_card_no,member.service_fees_id as member_z_service_fees_id,member.parent_id as member_z_parent_id,member.ancestors as member_z_ancestors,member.agent_type as member_z_agent_type,member.corporate_account_url as member_z_corporate_account_url,member.corporate_id_card_front_url as member_z_corporate_id_card_front_url,member.business_license_url as member_z_business_license_url,member.corporate_id_card_back_url as member_z_corporate_id_card_back_url,member.openid_agent as member_z_openid_agent,member.openid_tenants as member_z_openid_tenants,member.openid_landlord as member_z_openid_landlord,member.create_by as member_z_create_by,member.create_time as member_z_create_time,member.update_by as member_z_update_by,member.update_time as member_z_update_time FROM sys_user LEFT JOIN sys_dept as dept ON sys_user.dept_id = dept.id LEFT JOIN app_member as member ON sys_user.member_id = member.id  ORDER BY create_time DESC   LIMIT 0, 10 '
CaseMessage=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 'member ON sys_user.member_id = member.id  ORDER BY create_time DESC   LIMIT 0, 1' at line 1
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:110)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:65)
	at org.nutz.dao.impl.interceptor.DaoLogInterceptor.filter(DaoLogInterceptor.java:22)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:63)
	at org.nutz.dao.DaoInterceptorChain.invoke(DaoInterceptorChain.java:138)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:163)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithoutTransaction(NutDaoRunner.java:130)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:97)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:86)
	at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:242)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:254)
	at org.nutz.dao.impl.NutDao.queryByJoin(NutDao.java:1284)
	at org.nutz.dao.impl.NutDao.queryByJoin(NutDao.java:1274)
	at io.nutz.nutzsite.common.base.Service.tableList(Service.java:343)
	at io.nutz.nutzsite.module.sys.controllers.UserController.list(UserController.java:94)
	at io.nutz.nutzsite.module.sys.controllers.UserController$FM$list$408746d29ae439cb459b03dfdeac110b.invoke(UserController.java)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:31)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.plugins.validation.ValidationProcessor.process(ValidationProcessor.java:56)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:38)
	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 io.nutz.nutzsite.common.mvc.processor.XssSqlFilterProcessor.process(XssSqlFilterProcessor.java:80)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.integration.shiro.NutShiroProcessor.process(NutShiroProcessor.java:139)
	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 io.nutz.nutzsite.common.mvc.processor.GlobalsSettingProcessor.process(GlobalsSettingProcessor.java:49)
	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.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:202)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
	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.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
	at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
	at org.nutz.mvc.WhaleFilter.doFilter(WhaleFilter.java:171)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1596)
	at org.eclipse.jetty.websocket.server.WebSocketUpgradeFilter.doFilter(WebSocketUpgradeFilter.java:226)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:545)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:536)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1592)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1296)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1562)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1211)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.Server.handle(Server.java:500)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:386)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:562)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:378)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: 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 'member ON sys_user.member_id = member.id  ORDER BY create_time DESC   LIMIT 0, 1' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1218)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2883)
	at com.alibaba.druid.filter.FilterAdapter.statement_executeQuery(FilterAdapter.java:2514)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_executeQuery(FilterEventAdapter.java:302)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeQuery(FilterChainImpl.java:2880)
	at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.executeQuery(StatementProxyImpl.java:221)
	at com.alibaba.druid.pool.DruidPooledStatement.executeQuery(DruidPooledStatement.java:297)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:250)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:59)
	... 89 more

这个 算不算是BUG 求解答

17 回复

controller方法


	/**
	 * 查询用户列表
	 */
	@At
	@Ok("json")
	public Object list(@Param("pageNum")int pageNum,
					   @Param("pageSize")int pageSize,
					   @Param("deptId") String deptId,
					   @Param("loginName") String loginName,
					   @Param("phonenumber") String phonenumber,
					   @Param("beginTime") Date beginTime,
					   @Param("endTime") Date endTime,
					   @Param("orderByColumn") String orderByColumn,
					   @Param("isAsc") String isAsc,
					   HttpServletRequest req) {
		Cnd cnd = Cnd.NEW();
		if (!Strings.isBlank(loginName)){
			cnd.and("user_name", "like", "%" + loginName +"%");
		}
		if (!Strings.isBlank(phonenumber)){
			cnd.and("phonenumber", "=",  phonenumber);
		}
		if(Lang.isNotEmpty(beginTime)){
			cnd.and("create_time",">=", beginTime);
		}
		if(Lang.isNotEmpty(endTime)){
			cnd.and("create_time","<=", endTime);
		}
		if (!Strings.isBlank(deptId)){
			cnd.where().andInBySql("dept_id","SELECT id FROM sys_dept  WHERE FIND_IN_SET ('%s',ancestors)", deptId)
					.or("dept_id","=", deptId);
		}

		return userService.tableList(pageNum,pageSize,cnd,orderByColumn,isAsc,"dept|member");
	}


    /**
     * 分页查询数据封装 查询关联数据
     *
     * @param pageNumber
     * @param pageSize
     * @param cnd
     * @param linkname
     * @return
     */
    public TableDataInfo tableList(int pageNumber, int pageSize, Cnd cnd, String orderByColumn, String isAsc, String linkname) {
        Pager pager = this.dao().createPager(pageNumber, pageSize);
        if (Strings.isNotBlank(orderByColumn) && Strings.isNotBlank(isAsc)) {
            MappingField field = dao().getEntity(this.getEntityClass()).getField(orderByColumn);
            if (Lang.isNotEmpty(field)) {
                cnd.orderBy(field.getColumnName(), isAsc);
            }
        }
        if (Strings.isBlank(linkname)) {
            List<T> list = this.dao().query(this.getEntityClass(), cnd, pager);
            return new TableDataInfo(list, this.dao().count(this.getEntityClass(), cnd));
        } else {
            List<T> list = this.dao().queryByJoin(this.getEntityClass(), linkname, cnd, pager);
            return new TableDataInfo(list, this.dao().count(this.getEntityClass(), cnd));
        }
    }

order by . create_time 没有看到表名。

sql整理如下 IDEA 执行 没问题 

SELECT sys_user.id                        as id,
       sys_user.dept_id                   as dept_id,
       sys_user.login_name                as login_name,
       sys_user.user_name                 as user_name,
       sys_user.password                  as password,
       sys_user.salt                      as salt,
       sys_user.email                     as email,
       sys_user.phonenumber               as phonenumber,
       sys_user.gender                    as gender,
       sys_user.avatar                    as avatar,
       sys_user.status                    as status,
       sys_user.del_flag                  as del_flag,
       sys_user.login_ip                  as login_ip,
       sys_user.login_date                as login_date,
       sys_user.create_by                 as create_by,
       sys_user.create_time               as create_time,
       sys_user.update_by                 as update_by,
       sys_user.update_time               as update_time,
       sys_user.member_id                 as member_id,
       sys_user.member_role               as member_role,
       dept.id                            as dept_z_id,
       dept.parent_id                     as dept_z_parent_id,
       dept.ancestors                     as dept_z_ancestors,
       dept.dept_name                     as dept_z_dept_name,
       dept.order_num                     as dept_z_order_num,
       dept.leader                        as dept_z_leader,
       dept.phone                         as dept_z_phone,
       dept.email                         as dept_z_email,
       dept.status                        as dept_z_status,
       dept.del_flag                      as dept_z_del_flag,
       dept.create_by                     as dept_z_create_by,
       dept.create_time                   as dept_z_create_time,
       dept.update_by                     as dept_z_update_by,
       dept.update_time                   as dept_z_update_time,
       member.id                          as member_z_id,
       member.login_name                  as member_z_login_name,
       member.user_name                   as member_z_user_name,
       member.gender                      as member_z_gender,
       member.email                       as member_z_email,
       member.password                    as member_z_password,
       member.salt                        as member_z_salt,
       member.user_image                  as member_z_user_image,
       member.signature                   as member_z_signature,
       member.status                      as member_z_status,
       member.device                      as member_z_device,
       member.platform                    as member_z_platform,
       member.openid                      as member_z_openid,
       member.remark                      as member_z_remark,
       member.del_flag                    as member_z_del_flag,
       member.identity_card_front         as member_z_identity_card_front,
       member.identity_card_reverse       as member_z_identity_card_reverse,
       member.audit_status                as member_z_audit_status,
       member.leader_id                   as member_z_leader_id,
       member.employee_card_url           as member_z_employee_card_url,
       member.agent_status                as member_z_agent_status,
       member.sys_user_id                 as member_z_sys_user_id,
       member.member_role                 as member_z_member_role,
       member.real_name                   as member_z_real_name,
       member.id_card_no                  as member_z_id_card_no,
       member.service_fees_id             as member_z_service_fees_id,
       member.parent_id                   as member_z_parent_id,
       member.ancestors                   as member_z_ancestors,
       member.agent_type                  as member_z_agent_type,
       member.corporate_account_url       as member_z_corporate_account_url,
       member.corporate_id_card_front_url as member_z_corporate_id_card_front_url,
       member.business_license_url        as member_z_business_license_url,
       member.corporate_id_card_back_url  as member_z_corporate_id_card_back_url,
       member.openid_agent                as member_z_openid_agent,
       member.openid_tenants              as member_z_openid_tenants,
       member.openid_landlord             as member_z_openid_landlord,
       member.create_by                   as member_z_create_by,
       member.create_time                 as member_z_create_time,
       member.update_by                   as member_z_update_by,
       member.update_time                 as member_z_update_time
FROM sys_user
         LEFT JOIN sys_dept as dept ON sys_user.dept_id = dept.id
         LEFT JOIN app_member as member ON sys_user.member_id = member.id
ORDER BY create_time DESC
LIMIT 0, 10;

ORDER BY sys_user.create_time DESC 调整 同样 报错
IDEA 执行 SQL 没问题 .......
Navicat 执行 异常

1064 - 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 'member ON sys_user.member_id = member.id
ORDER BY sys_user.create_time DESC
LIMI' at line 77, Time: 0.072000s

member是关键字吧？

好吧 idea 怎么做的能正常执行的

不知道呢... 把member改一下试试?

感谢 兽总 耐心回复 已经改用 fetchLinks 查询关联

LEFT JOIN app_member as member 取别名 能否再加下划线 字母 就能解决关键字问题
另外 where条件 建议加上
WHERE 主表.agency_id = '49c59ddc4090491c88b7d37b2eacdc6c'
AND 主表.status = 'SUCCESSFUL_SIGNING'

看了一下 源码 实在是看不懂
能否 解决问题 同时 顺便写个注释

package org.nutz.dao.impl.sql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.nutz.dao.DaoException;
import org.nutz.dao.FieldMatcher;
import org.nutz.dao.entity.Entity;
import org.nutz.dao.entity.LinkField;
import org.nutz.dao.entity.LinkVisitor;
import org.nutz.dao.entity.MappingField;
import org.nutz.dao.impl.sql.pojo.NoParamsPItem;
import org.nutz.dao.jdbc.JdbcExpert;
import org.nutz.dao.sql.Pojo;
import org.nutz.dao.sql.PojoCallback;
import org.nutz.dao.sql.PojoMaker;
import org.nutz.dao.sql.SqlType;
import org.nutz.dao.util.Pojos;
import org.nutz.lang.ContinueLoop;
import org.nutz.lang.Each;
import org.nutz.lang.ExitLoop;
import org.nutz.lang.Lang;
import org.nutz.lang.LoopException;

public class NutPojoMaker implements PojoMaker {
    @Override
    public Pojo makeQueryByJoin(final Entity<?> en, String regex) {
        final Pojo pojo = Pojos.pojo(expert, en, SqlType.SELECT);
        pojo.setEntity(en);
        pojo.append(new QueryJoinFeilds(en, true, en.getTableName()));
        en.visitOne(null, regex, new LinkVisitor() {
            public void visit(Object obj, LinkField lnk) {
                pojo.append(Pojos.Items.wrap(","));
                pojo.append(new QueryJoinFeilds(lnk.getLinkedEntity(), false, lnk.getName()));
            }
        });
        pojo.append(Pojos.Items.wrap("FROM"));
        pojo.append(Pojos.Items.entityViewName());
        en.visitOne(null, regex, new LinkVisitor() {
            public void visit(Object obj, LinkField lnk) {
                Entity<?> lnkEntity = lnk.getLinkedEntity();
                String LJ = String.format("LEFT JOIN %s as %s ON %s.%s = %s.%s",
                                          lnkEntity.getTableName(),
                                          lnk.getName(),
                                          en.getTableName(),
                                          lnk.getHostField().getColumnNameInSql(),
                                          lnk.getName(),
                                          lnk.getLinkedField().getColumnNameInSql());
                pojo.append(Pojos.Items.wrap(LJ));
            }
        });
        return pojo;
    }
}

我觉得还是关键字的事

的确 是关键字 但是
我原本是 LEFT JOIN app_member as member
能不能 as 成 member_n

想了想, 原本都是考虑"字段名"是否为关键字, 没有对 "表名" 进行判断...

提交了一个修改 https://gitee.com/nutz/nutz/commit/511a10d1ab2725cf2942640bce411ee4328ff76a

还是不行 SQL如下

SELECT app_hous_info.id                     as id,
       app_hous_info.name                   as name,
       app_hous_info.unit_area              as unit_area,
       app_hous_info.total_area             as total_area,
       app_hous_info.community              as community,
       app_hous_info.citycode               as citycode,
       app_hous_info.adcode                 as adcode,
       app_hous_info.longitude              as longitude,
       app_hous_info.latitude               as latitude,
       app_hous_info.address                as address,
       app_hous_info.source                 as source,
       app_hous_info.rental                 as rental,
       app_hous_info.tenant_rent            as tenant_rent,
       app_hous_info.landlord_rent          as landlord_rent,
       app_hous_info.building_head          as building_head,
       app_hous_info.area                   as area,
       app_hous_info.status                 as status,
       app_hous_info.house_status           as house_status,
       app_hous_info.check_in_date          as check_in_date,
       app_hous_info.house_type             as house_type,
       app_hous_info.certificate_house_url  as certificate_house_url,
       app_hous_info.lock_id                as lock_id,
       app_hous_info.lock_install_status    as lock_install_status,
       app_hous_info.landlord_id            as landlord_id,
       app_hous_info.agent_id               as agent_id,
       app_hous_info.formid                 as formid,
       app_hous_info.expiration_time        as expiration_time,
       app_hous_info.back_rent_time         as back_rent_time,
       app_hous_info.property_manage_fee    as property_manage_fee,
       app_hous_info.rent_type              as rent_type,
       app_hous_info.room_number            as room_number,
       app_hous_info.create_by              as create_by,
       app_hous_info.update_by              as update_by,
       app_hous_info.landlord_id_card_front as landlord_id_card_front,
       app_hous_info.landlord_id_card_back  as landlord_id_card_back,
       app_hous_info.top                    as top,
       app_hous_info.special_offers         as special_offers,
       app_hous_info.popularize             as popularize,
       app_hous_info.tag                    as tag,
       app_hous_info.create_time            as create_time,
       app_hous_info.update_time            as update_time,
       landlord.id                          as landlord_z_id,
       landlord.login_name                  as landlord_z_login_name,
       landlord.user_name                   as landlord_z_user_name,
       landlord.gender                      as landlord_z_gender,
       landlord.email                       as landlord_z_email,
       landlord.password                    as landlord_z_password,
       landlord.salt                        as landlord_z_salt,
       landlord.user_image                  as landlord_z_user_image,
       landlord.signature                   as landlord_z_signature,
       landlord.status                      as landlord_z_status,
       landlord.device                      as landlord_z_device,
       landlord.platform                    as landlord_z_platform,
       landlord.openid                      as landlord_z_openid,
       landlord.remark                      as landlord_z_remark,
       landlord.del_flag                    as landlord_z_del_flag,
       landlord.identity_card_front         as landlord_z_identity_card_front,
       landlord.identity_card_reverse       as landlord_z_identity_card_reverse,
       landlord.audit_status                as landlord_z_audit_status,
       landlord.leader_id                   as landlord_z_leader_id,
       landlord.employee_card_url           as landlord_z_employee_card_url,
       landlord.agent_status                as landlord_z_agent_status,
       landlord.sys_user_id                 as landlord_z_sys_user_id,
       landlord.member_role                 as landlord_z_member_role,
       landlord.real_name                   as landlord_z_real_name,
       landlord.id_card_no                  as landlord_z_id_card_no,
       landlord.service_fees_id             as landlord_z_service_fees_id,
       landlord.parent_id                   as landlord_z_parent_id,
       landlord.ancestors                   as landlord_z_ancestors,
       landlord.agent_type                  as landlord_z_agent_type,
       landlord.corporate_account_url       as landlord_z_corporate_account_url,
       landlord.corporate_id_card_front_url as landlord_z_corporate_id_card_front_url,
       landlord.business_license_url        as landlord_z_business_license_url,
       landlord.corporate_id_card_back_url  as landlord_z_corporate_id_card_back_url,
       landlord.openid_agent                as landlord_z_openid_agent,
       landlord.openid_tenants              as landlord_z_openid_tenants,
       landlord.openid_landlord             as landlord_z_openid_landlord,
       landlord.create_by                   as landlord_z_create_by,
       landlord.create_time                 as landlord_z_create_time,
       landlord.update_by                   as landlord_z_update_by,
       landlord.update_time                 as landlord_z_update_time,
       agent.id                             as agent_z_id,
       agent.login_name                     as agent_z_login_name,
       agent.user_name                      as agent_z_user_name,
       agent.gender                         as agent_z_gender,
       agent.email                          as agent_z_email,
       agent.password                       as agent_z_password,
       agent.salt                           as agent_z_salt,
       agent.user_image                     as agent_z_user_image,
       agent.signature                      as agent_z_signature,
       agent.status                         as agent_z_status,
       agent.device                         as agent_z_device,
       agent.platform                       as agent_z_platform,
       agent.openid                         as agent_z_openid,
       agent.remark                         as agent_z_remark,
       agent.del_flag                       as agent_z_del_flag,
       agent.identity_card_front            as agent_z_identity_card_front,
       agent.identity_card_reverse          as agent_z_identity_card_reverse,
       agent.audit_status                   as agent_z_audit_status,
       agent.leader_id                      as agent_z_leader_id,
       agent.employee_card_url              as agent_z_employee_card_url,
       agent.agent_status                   as agent_z_agent_status,
       agent.sys_user_id                    as agent_z_sys_user_id,
       agent.member_role                    as agent_z_member_role,
       agent.real_name                      as agent_z_real_name,
       agent.id_card_no                     as agent_z_id_card_no,
       agent.service_fees_id                as agent_z_service_fees_id,
       agent.parent_id                      as agent_z_parent_id,
       agent.ancestors                      as agent_z_ancestors,
       agent.agent_type                     as agent_z_agent_type,
       agent.corporate_account_url          as agent_z_corporate_account_url,
       agent.corporate_id_card_front_url    as agent_z_corporate_id_card_front_url,
       agent.business_license_url           as agent_z_business_license_url,
       agent.corporate_id_card_back_url     as agent_z_corporate_id_card_back_url,
       agent.openid_agent                   as agent_z_openid_agent,
       agent.openid_tenants                 as agent_z_openid_tenants,
       agent.openid_landlord                as agent_z_openid_landlord,
       agent.create_by                      as agent_z_create_by,
       agent.create_time                    as agent_z_create_time,
       agent.update_by                      as agent_z_update_by,
       agent.update_time                    as agent_z_update_time
FROM app_hous_info
         LEFT JOIN app_member as null
ON app_hous_info.landlord_id = null.id LEFT JOIN app_member as null ON app_hous_info.agent_id = null.id
ORDER BY create_time DESC
LIMIT 0, 10;

咦，跟之前的不是同一个吧？

啊，写错了

已提交

添加回复
请先登陆
回到顶部