一个用户表,有id,name,groupid等
一个用户组表 有id,name,
用户表和用户组表是多对一的关系
sqlxml配置如下:
<sqlMap namespace="Users"> <typeAlias alias="Users" type="com.ch.entity.user.Users" /> <typeAlias alias="Group" type="com.ch.entity.user.Group" /> <resultMap class="Users" id="resultUsers"> <result column="USER_ID" property="id" /> <result column="LOGIN_NAME" property="loginName" /> <result column="SHOW_NAME" property="showName" /> <result column="PASS_WORD" property="password" /> <result column="CREATE_TIME" property="createTime" /> <result column="GROUP_ID" property="groupId" /> <result column="ADMIN_FLG" property="adminFlag" /> <result column="b.ID" property="group.id" /> <result column="b.GROUP_NAME" property="group.groupName" /> <result column="b.HIDE_FLG" property="group.hideFlag" /> </resultMap> <select id="selectUserByIdAndPass" parameterClass="Users" resultMap="resultUsers"> select a.USER_ID ,a.LOGIN_NAME ,a.PASS_WORD ,a.SHOW_NAME ,a.PASS_WORD ,a.CREATE_TIME ,a.GROUP_ID ,a.ADMIN_FLG ,b.ID ,b.GROUP_NAME from table_user as a ,table_group as b where LOGIN_NAME = #loginName# and PASS_WORD = #password# and b.ID = a.GROUP_ID </select> <select id="selectAllUser" parameterClass="Users" resultMap="resultUsers"> SELECT a.USER_ID ,a.LOGIN_NAME ,a.PASS_WORD ,a.SHOW_NAME ,a.PASS_WORD ,a.CREATE_TIME ,a.GROUP_ID ,a.ADMIN_FLG ,b.ID ,b.GROUP_NAME ,b.HIDE_FLG FROM table_user as a ,table_group as b WHERE b.ID = a.GROUP_ID ORDER BY a.USER_ID DESC </select> <select id="selectUserByGroupId" parameterClass="int" resultMap="resultUsers"> SELECT a.USER_ID, a.LOGIN_NAME, a.SHOW_NAME, a.PASS_WORD, a.CREATE_TIME, a.GROUP_ID, a.ADMIN_FLG, b.ID, b.GROUP_NAME, b.DISPLAY_ORDER, b.HIDE_FLG FROM table_user AS a LEFT JOIN table_group AS b ON b.ID = a.GROUP_ID WHERE a.GROUP_ID = #groupId# ORDER BY USER_ID DESC </select>
需要说明的是:sql语句中查询哪些字段。在<resultMap class="Users" id="resultUsers">中就要配置哪些字段,并对应多的一端的属性