<..._getusercountbyroleid为根据角色id查询该角色下是否有用户信息。返回该角色id下的">
devbox
小小林熬夜学编程
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

mybatis动态SQL对数据库执行增删改查学习笔记_getusercountbyroleid为根据角色id查询该角色下是否有用户信息。返回该角色id下的

作者：小小林熬夜学编程 | 2024-05-14 17:02:58

赞

踩

getusercountbyroleid为根据角色id查询该角色下是否有用户信息。返回该角色id下的

jar包以及目录结构

在这里插入图片描述
SQL映射配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--接口所在的包名-->
<mapper namespace="cn.com.yan.mapper.UserMapper">
    <!--修改信息-->
    <!--<update id="modify" parameterType="User">
        update smbms_user set username=#{username},address=#{address} where id=#{id}
    </update>-->
    <!--使用if+set改造更新操作-->
    <!--<update id="modify" parameterType="User">
        update smbms_user
        <set>
            <if test="username!=null">username=#{username},</if>
            <if test="address!=null">address=#{address}</if>
        </set>
         where id=#{id}
    </update>-->
    <!--使用if+trim替代if+set更新用户-->
    <update id="modify" parameterType="User">
        update smbms_user
        <trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
            <if test="username!=null">username=#{username},</if>
            <if test="address!=null">address=#{address}</if>
        </trim>
    </update>
    <!--多条件查询用户表中的信息-->
    <!--<select id="getuserlist" resultType="User">
        select u.id,u.username,u.userpassword,u.phone,u.address from smbms_user u,smbms_role r
        where u.userrole=r.id
        <if test="username!=null and username!=''">
             and username like concat('%',#{username},'%')
        </if>
        <if test="userrole!=null and userrole!=0">
            and userrole=#{userrole}
        </if>
    </select>-->
    <!--错误的例子-->
    <!--<select id="getuserlist" resultType="User">
        select * from smbms_user u where
        <if test="username!=null and username!=''">
           and username like concat('%',#{username},'%')
        </if>
        <if test="userrole!=null and userrole!=0">
            and userrole=#{userrole}
        </if>
    </select>-->
    <!--第一种更正以上错误的方法 -->
    <!--<select id="getuserlist" resultType="User">
        select * from smbms_user u
        <where>
            <if test="username!=null and username!=''">
                and username like concat('%',#{username},'%')
            </if>
            <if test="userrole!=null and userrole!=0">
                and userrole=#{userrole}
            </if>
        </where>
    </select>-->
    <!--第二种更正以上错误的方法-->
    <select id="getuserlist" resultType="User">
        select * from smbms_user u
        <trim prefix="where" prefixOverrides="and | or">
            <if test="username!=null and username!=''">
                and username like concat('%',#{username},'%')
            </if>
            <if test="userrole!=null and userrole!=0">
                and userrole=#{userrole}
            </if>
        </trim>
    </select>
    <!--根据用户角色列表。获取该角色列表下用户列表信息-foreach-array-->
    <select id="getUserByRoleId_foreach_array" resultType="User">
        select * from smbms_user where userrole in
        <foreach collection="array" item="roleIds"
            open ="(" separator="," close=")">
            #{roleIds}
        </foreach>
    </select>
    <!--根据用户角色列表。获取该角色列表下用户列表信息-foreach-list-->
    <select id="getUserByRoleId_foreach_list" resultType="User">
        select * from smbms_user where userrole in
        <foreach collection="list" item="rolelist"
                 open ="(" separator="," close=")">
            #{rolelist}
        </foreach>
    </select>
    <!--Map方式入参-->
    <select id="getUserByConditionMap_foreach_map" resultType="User">
       /* 此处的参数gender和测试方法中的Map键值一样-conditionMap.put("gender",1);*/
        select * from smbms_user where gender=#{gender} and userrole in
        <foreach collection="roleIds" item="rolemap"
                 open ="(" separator="," close=")">
            #{rolemap}
        </foreach>
    </select>
    <!--将list封装成一个Map进行入参-->
    <select id="getUser_foreach_ByMap" resultType="User">
        select * from smbms_user where userrole in
        <foreach collection="rolekey" item="rolemap"
                 open ="(" separator="," close=")">
            #{rolemap}
        </foreach>
    </select>
    <!---->
    <select id="getUserList_choose" resultType="User">
        select * from smbms_user where 1=1
            <choose>
                <when test="username!=null and username!=''">
                    and username like concat('%',#{username},'%')
                </when>
                <when test="userrole!=null and userrole!=0">
                    and userrole =#{userrole}
                </when>
                <when test="usercode!=null and usercode!=''">
                    and usercode like concat('%',#{usercode},'%')
                </when>
                <otherwise>
                   and creationDate=#{creationDate}
                </otherwise>
            </choose>
    </select>
    <!--查询用户表的记录数-->
    <select id="count" resultType="int">
        select count(1) as count from smbms_user
    </select>
    <!--分页查询-->
    <!--<resultMap id="userRoleResult" type="User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>

        <association property="role" javaType="Role">
            <id property="id" column="id"/>
            <result property="rolename" column="rolename"/>
        </association>
    </resultMap>-->
    <!--分页查询-->
    <resultMap id="roleUserResult" type="Role">
        <id property="id" column="id"/>
        <result property="rolename" column="rolename"/>
        <collection property="userList" ofType="User">
            <id property="id" column="id"/>
            <result property="username" column="username"/>
            <result property="address" column="address"/>
        </collection>
    </resultMap>
    <select id="getUserListpage1" resultMap="roleUserResult">
        select u.username,u.address,r.rolename from smbms_user u,smbms_role r where u.userrole=r.id
            <if test="username!=null and username!=''">
                and u.username like concat('%',#{username},'%')
            </if>
            <if test="userrole!=null and userrole!=0">
                and u.userrole=#{userrole}
            </if>
            order by u.creationDate desc limit #{from},#{pagesize}
    </select>
    <select id="getUserListpage2" resultMap="roleUserResult">
        select u.username,u.address,r.rolename from smbms_user u,smbms_role r where u.userrole=r.id
        <if test="username!=null and username!=''">
            and u.username like concat('%',#{username},'%')
        </if>
        <if test="userrole!=null and userrole!=0">
            and u.userrole=#{userrole}
        </if>
        order by u.creationDate desc limit #{from},#{pagesize}
    </select>
    <!--在user实体类中添加rolename属性-->
    <select id="getUserListpage3" resultType="User">
        select u.username,u.address,r.rolename as userrolename from smbms_user u,smbms_role r where u.userrole=r.id
        <if test="username!=null and username!=''">
            and u.username like concat('%',#{username},'%')
        </if>
        <if test="userrole!=null and userrole!=0">
            and u.userrole=#{userrole}
        </if>
        order by u.creationDate desc limit #{from},#{pagesize}
    </select>
   <!-- <select id="getuserlist">
        select * from sys_users
        <where>
            <if test="username!=null and username!=''">
                and user_name like CONCAT('%',#{username},'%')
            </if>
            <if test="">
            </if>
        </where>
    </select>-->
</mapper>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190

mybatis-config核心配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--实体类所在包名-->
    <settings>
        <setting name="autoMappingBehavior" value="FULL"/>
    </settings>
    <typeAliases>
      <package name="cn.com.yan.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/supermarket?characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="000000"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--映射配置文件下的路径-->
        <mapper resource="mapper/UserMapper.xml"/>
    </mappers>
</configuration>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

UserMapper接口

package cn.com.yan.mapper;

import cn.com.yan.pojo.Role;
import cn.com.yan.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface UserMapper {
    /**
     *根据用户名和角色id查询用户信息
     * @param username
     * @param userrole
     * @return
     */
    List<User> getuserlist(@Param("username")String username,@Param("userrole")Integer userrole);
    /**
     * 通过id修改指定用户信息
     * @param user
     * @return
     */
    public Integer modify(User user);
    /**
     * 数组的方式传参
     * 根据用户角色列表。获取该角色列表下用户列表信息-foreach-array
     * @param roleIds
     * @return
     */
    public List<User> getUserByRoleId_foreach_array(Integer[] roleIds);

    /**
     *list集合的方式传参
     * @param rolelist
     * @return
     */
    public List<User> getUserByRoleId_foreach_list(List<Integer> rolelist);

    /**
     * Map的方式传参
     * @param conditionMap
     * @return
     */
    public List<User> getUserByConditionMap_foreach_map(Map<String,Object> conditionMap);
    /**
     * 将list集合封装成Map方式作为参数
     * @param roleMap
     * @return
     */
    public List<User> getUser_foreach_ByMap(Map<String,Object> roleMap);
    /**
     *根据多条件查询
     * @param username
     * @param userrole
     * @param usercode
     * @param creationDate
     * @return
     */
    public List<User> getUserList_choose(@Param("username")String username,
                                         @Param("userrole")Integer userrole,
                                         @Param("usercode")String usercode,
                                         @Param("creationDate")String creationDate);
    /**
     * 查询表中数据条数
     * @return
     */
    public Integer count();
    /**
     * 分页查询
     * @param username
     * @param userrole
     * @param currentpage
     * @param pagesize
     * @return
     */
    public List<Role> getUserListpage1(@Param("username")String username, @Param("userrole")Integer userrole, @Param("from")Integer currentpage,
                                       @Param("pagesize")Integer pagesize);
    public List<Role> getUserListpage2(@Param("username")String username, @Param("userrole")Integer userrole, @Param("from")Integer currentpage,
                                       @Param("pagesize")Integer pagesize);
    public List<User> getUserListpage3(@Param("username")String username, @Param("userrole")Integer userrole, @Param("from")Integer currentpage,
                                       @Param("pagesize")Integer pagesize);
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83

User实体类

package cn.com.yan.pojo;

/**
 * @author ysf
 * @date 2019/8/30 19:53
 * @用户实体类
 */
public class User {
    private Integer id;

    private String usercode;
    //用户名称
    private String username;
    //密码
    private String userpassword;
    //性别
    private Integer gender;
    //生日
    private String birthday;
    //电话
    private String phone;
    //地址
    private String address;
    //角色id
    private Integer userrole;
    private String userrolename;
    private Integer createby;
    private String creationDate;
    private Integer modifyby;
    private String modifydate;
    private Role role;

    public Role getRole() {
        return role;
    }
    public void setRole(Role role) {
        this.role = role;
    }

    public String getUserrolename() {
        return userrolename;
    }

    public void setUserrolename(String userrolename) {
        this.userrolename = userrolename;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsercode() {
        return usercode;
    }

    public void setUsercode(String usercode) {
        this.usercode = usercode;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getUserpassword() {
        return userpassword;
    }

    public void setUserpassword(String userpassword) {
        this.userpassword = userpassword;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public String getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Integer getUserrole() {
        return userrole;
    }

    public void setUserrole(Integer userrole) {
        this.userrole = userrole;
    }

    public Integer getCreateby() {
        return createby;
    }

    public void setCreateby(Integer createby) {
        this.createby = createby;
    }

    public String getCreationDate() {
        return creationDate;
    }

    public void setCreationDate(String creationDate) {
        this.creationDate = creationDate;
    }

    public Integer getModifyby() {
        return modifyby;
    }

    public void setModifyby(Integer modifyby) {
        this.modifyby = modifyby;
    }

    public String getModifydate() {
        return modifydate;
    }

    public void setModifydate(String modifydate) {
        this.modifydate = modifydate;
    }

    public User() {
    }

    public User(Integer id, String usercode, String username, String userpassword, Integer gender, String birthday, String phone, String address, Integer userrole, Integer createby, String creationDate, Integer modifyby, String modifydate) {
        this.id = id;
        this.usercode = usercode;
        this.username = username;
        this.userpassword = userpassword;
        this.gender = gender;
        this.birthday = birthday;
        this.phone = phone;
        this.address = address;
        this.userrole = userrole;
        this.createby = createby;
        this.creationDate = creationDate;
        this.modifyby = modifyby;
        this.modifydate = modifydate;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", usercode='" + usercode + '\'' +
                ", username='" + username + '\'' +
                ", userpassword='" + userpassword + '\'' +
                ", gender=" + gender +
                ", birthday='" + birthday + '\'' +
                ", phone='" + phone + '\'' +
                ", address='" + address + '\'' +
                ", userrole=" + userrole +
                ", createby=" + createby +
                ", creationDate='" + creationDate + '\'' +
                ", modifyby=" + modifyby +
                ", modifydate='" + modifydate + '\'' +
                '}';
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189

Role角色实体类

package cn.com.yan.pojo;

import java.util.List;

/**
 * @author ysf
 * @date 2019/8/30 19:53
 * @角色实体类
 */
public class Role {
    private Integer id;
    private String rolecode;
    private String rolename;
    private Integer createby;
    private String creationdate;
    private Integer modifyby;
    private String modifydate;

    List<User> userList;

    public List<User> getUserList() {
        return userList;
    }

    public void setUserList(List<User> userList) {
        this.userList = userList;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getRolecode() {
        return rolecode;
    }

    public void setRolecode(String rolecode) {
        this.rolecode = rolecode;
    }

    public String getRolename() {
        return rolename;
    }

    public void setRolename(String rolename) {
        this.rolename = rolename;
    }

    public Integer getCreateby() {
        return createby;
    }

    public void setCreateby(Integer createby) {
        this.createby = createby;
    }

    public String getCreationdate() {
        return creationdate;
    }

    public void setCreationdate(String creationdate) {
        this.creationdate = creationdate;
    }

    public Integer getModifyby() {
        return modifyby;
    }

    public void setModifyby(Integer modifyby) {
        this.modifyby = modifyby;
    }

    public String getModifydate() {
        return modifydate;
    }

    public void setModifydate(String modifydate) {
        this.modifydate = modifydate;
    }

    public Role() {
    }

    public Role(Integer id, String rolecode, String rolename, Integer createby, String creationdate, Integer modifyby, String modifydate) {
        this.id = id;
        this.rolecode = rolecode;
        this.rolename = rolename;
        this.createby = createby;
        this.creationdate = creationdate;
        this.modifyby = modifyby;
        this.modifydate = modifydate;
    }
    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", rolecode='" + rolecode + '\'' +
                ", rolename='" + rolename + '\'' +
                ", createby=" + createby +
                ", creationdate='" + creationdate + '\'' +
                ", modifyby=" + modifyby +
                ", modifydate='" + modifydate + '\'' +
                '}';
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110

工具类MybatisUtil

package cn.com.yan;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

/**
 * @author ysf
 * @date 2019/8/28 21:20
 */
public class MybatisUtil {
    private static SqlSessionFactory factory;
    static {  //在静态代码块下factory只会被创建一次
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            factory=new SqlSessionFactoryBuilder().build(is);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * 创建SqlSession对象
     * @return
     */
    public static SqlSession createSqlSession(){
        return factory.openSession(false); //true为自动提交事务
    }
    /**
     * 关闭SqlSession对象
     * @param sqlsession
     */
    public static void closeSqlSession(SqlSession sqlsession){
        if(sqlsession!=null){
            sqlsession.close();
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41

Main方法

package cn.com.yan;

import cn.com.yan.mapper.UserMapper;
import cn.com.yan.pojo.Role;
import cn.com.yan.pojo.User;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

public class Main {
    private static final Logger LOGGER = Logger.getLogger(String.valueOf(Main.class));

    public static void main(String[] args) {
        // write your code here
    }

    /**
     * 根据用户名和角色Id查询用户的信息
     */
    @Test
    public void testgetuserlist() {
        SqlSession sqlsession = null;
        List<User> list = new ArrayList<User>();
        try {
            sqlsession = MybatisUtil.createSqlSession();
            User user = new User();
            /*user.setUsername("小");
            user.setUserrole(2);*/
            String username = "明";
            Integer roleId = 0;
            list = sqlsession.getMapper(UserMapper.class).getuserlist(username, roleId);
            for (User users : list) {
                System.out.println(users.getUsername() + "\t" + users.getAddress());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MybatisUtil.closeSqlSession(sqlsession);
        }
    }

    /**
     *根据用户id修改用户信息
     */
    @Test
    public void testmodify() {
        SqlSession sqlsession = null;
        int count = 0;
        try {
            sqlsession = MybatisUtil.createSqlSession();
            User user = new User();
            user.setId(3);
            user.setUsername("小明");
            user.setAddress("上海浦东区");
            count = sqlsession.getMapper(UserMapper.class).modify(user);
            //提交事务
            sqlsession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlsession.rollback();
            count = 0;
        } finally {
            MybatisUtil.closeSqlSession(sqlsession);
        }
    }
   /**
     * 数组的方式传参
     * 根据用户角色列表，获取该角色列表下用户列表信息-foreach-array
     */  
    @Test
    public void getUserByRoleId_foreach_array() {
        SqlSession sqlsession = null;
        List<User> list = new ArrayList<User>();
        Integer roleids[] = {2, 3};
        try {
            sqlsession=MybatisUtil.createSqlSession();
            list=sqlsession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(roleids);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MybatisUtil.closeSqlSession(sqlsession);
        }
        System.out.println("姓名\t用户角色Id\t用户地址");
        for (User user:list){
            System.out.println("  "+user.getUsername() + "\t" +user.getUserrole()+"\t\t\t"+ user.getAddress());
        }
    }
    /**
     * list集合的方式传参
     * 根据用户角色列表查询用户信息
     */
    @Test
    public void getUserByRoleId_foreach_list() {
        SqlSession sqlsession = null;
        List<User> list = new ArrayList<User>();
        List<Integer> rolelist =new ArrayList<Integer>();
        rolelist.add(2);
        rolelist.add(3);
        try {
            sqlsession=MybatisUtil.createSqlSession();
            list=sqlsession.getMapper(UserMapper.class).getUserByRoleId_foreach_list(rolelist);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MybatisUtil.closeSqlSession(sqlsession);
        }
        System.out.println("姓名\t用户角色Id\t用户地址");
        for (User user:list){
            System.out.println("  "+user.getUsername() + "\t" +user.getUserrole()+"\t\t\t"+ user.getAddress());
        }
    }
    /**
     * Map的方式传参
     * 查询符合条件的用户信息
     */
    @Test
    public void getUserByConditionMap_foreach_map() {
        SqlSession sqlsession = null;
        List<User> list = new ArrayList<User>();
        Map<String,Object> conditionMap=new HashMap<String,Object>();
        List<Integer> rolelist =new ArrayList<Integer>();
        rolelist.add(2);
        rolelist.add(3);
        conditionMap.put("gender",1);
        conditionMap.put("roleIds",rolelist);
        try {
            sqlsession=MybatisUtil.createSqlSession();
            list=sqlsession.getMapper(UserMapper.class).getUserByConditionMap_foreach_map(conditionMap);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MybatisUtil.closeSqlSession(sqlsession);
        }
        System.out.println("姓名\t用户角色Id\t用户地址\t性别");
        for (User user:list){
            System.out.println("  "+user.getUsername() + "\t" +user.getUserrole()+"\t\t\t"+ user.getAddress()+"\t"+user.getGender());
        }
    }
    /**
     *将list集合封装成Map方式作为参数，查询用户信息
     */
    @Test
    public void getUser_foreach_ByMap() {
        SqlSession sqlsession = null;
        List<User> list = new ArrayList<User>();
        Map<String,Object> roleMap=new HashMap<String,Object>();
        List<Integer> rolelist =new ArrayList<Integer>();
        rolelist.add(2);
        rolelist.add(3);
        roleMap.put("rolekey",rolelist);
        try {
            sqlsession=MybatisUtil.createSqlSession();
            list=sqlsession.getMapper(UserMapper.class).getUser_foreach_ByMap(roleMap);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MybatisUtil.closeSqlSession(sqlsession);
        }
        System.out.println("姓名\t用户角色Id\t用户地址\t性别");
        for (User user:list){
            System.out.println("  "+user.getUsername() + "\t" +user.getUserrole()+"\t\t\t"+ user.getAddress()+"\t"+user.getGender());
        }
    }
    /**
     * 根据多条件查询
     * @throws ParseException
     */
    @Test
    public void getUserList_choose() throws ParseException {
        SqlSession sqlsession = null;
        List<User> list = new ArrayList<User>();
        String username="";
        Integer userrole=2;
        String usercode="";
        //Date creationDate= (Date) new SimpleDateFormat("YYYY-MM-dd").parse("2018-2-10");
        String creationdate="2018-2-10";
        try {
            sqlsession=MybatisUtil.createSqlSession();
            list=sqlsession.getMapper(UserMapper.class).getUserList_choose(username,userrole,usercode,creationdate);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MybatisUtil.closeSqlSession(sqlsession);
        }
        System.out.println("姓名\t用户角色Id\t用户地址\t性别");
        for (User user:list){
            System.out.println("  "+user.getUsername() + "\t" +user.getUserrole()+"\t\t\t"+ user.getAddress()+"\t"+user.getGender());
        }
    }
    /**
     * 分页查询
     * 以下共有三个list1,list2,list3分别代表不同的方法实现分页查询
     */
    @Test
    public void getUserListpage() {
        SqlSession sqlsession = null;
        List<Role> list1=new ArrayList<Role>();
        //List<Role> list2 = new ArrayList<Role>();
        //List<User> list3 = new ArrayList<User>();
        String username="";
        Integer userrole=2;
        Integer pagsize=3;
        Integer currentpageNo=0;
        try {
            sqlsession=MybatisUtil.createSqlSession();
            list1=sqlsession.getMapper(UserMapper.class).getUserListpage1(username,userrole,currentpageNo,pagsize);
            //list2=sqlsession.getMapper(UserMapper.class).getUserListpage2(username,userrole,currentpageNo,pagsize);
            //list3=sqlsession.getMapper(UserMapper.class).getUserListpage3(username,userrole,currentpageNo,pagsize);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MybatisUtil.closeSqlSession(sqlsession);
        }
        System.out.println("姓名\t用户地址\t\t角色名称");
        //方法一
        List<Role> userList1=list1;
        for(Role role:userList1){
            for(int i=0;i<role.getUserList().size();i++){
                System.out.println(role.getUserList().get(i).getUsername()+"\t"+role.getUserList().get(i).getAddress()+"\t"+role.getRolename());
            }
        }
        //方法二
        /*List<User> userList2 = list2.get(0).getUserList();
        for (User user:userList2){
            System.out.println("  "+user.getUsername() + "\t"+user.getAddress()+"\t"+list2.get(0).getRolename());
        }*/
        //方法三
        /*List<User> userList3=list3;
        for(User user:userList3){
            System.out.println("  "+user.getUsername() + "\t"+user.getAddress()+"\t"+user.getUserrolename());
        }*/
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190
  • 191
  • 192
  • 193
  • 194
  • 195
  • 196
  • 197
  • 198
  • 199
  • 200
  • 201
  • 202
  • 203
  • 204
  • 205
  • 206
  • 207
  • 208
  • 209
  • 210
  • 211
  • 212
  • 213
  • 214
  • 215
  • 216
  • 217
  • 218
  • 219
  • 220
  • 221
  • 222
  • 223
  • 224
  • 225
  • 226
  • 227
  • 228
  • 229
  • 230
  • 231
  • 232
  • 233
  • 234
  • 235
  • 236
  • 237
  • 238
  • 239
  • 240
  • 241
  • 242

数据库就不展示了，有看不懂的可以留言，当然有不对的地方欢迎指正。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/569323
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号