配置MyBatis 的日志打印
在mybatis-config.xml文件中增加如下配置
<settings>
<setting name="cacheEnabled" value="true" />
<setting name="useGeneratedKeys" value="true" />
<setting name="defaultExecutorType" value="REUSE" />
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings>
if
/**
* 根据用户名和用户角色进行动态查询
* @param userName
* @param userRole
* @return
*/
List<User> findByUserNameAndRole(@Param("userName") String userName,@Param("userRole") Integer userRole);
<select id="findByUserNameAndRole" resultType="User">
select * from smbms_user where
<if test="userName != null and userName != ''">
userName like concat("%",#{userName},"%")
</if>
<if test="userRole != null">
and userRole=#{userRole}
</if>
</select>
where
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
/**
* 根据用户名和用户角色进行动态查询
* @param userName
* @param userRole
* @return
*/
List<User> findByUserNameAndRoleUserWhere(@Param("userName") String userName,@Param("userRole") Integer userRole);
<select id="findByUserNameAndRoleUserWhere" resultType="User">
select * from smbms_user
<where>
<if test="userName != null and userName != ''">
and userName like concat("%",#{userName},"%")
</if>
<if test="userRole != null">
and userRole=#{userRole}
</if>
</where>
</select>
update
update的时候,如果我们传入了用户的信息,那么该信息就应该被修改,但是假设userCode没有传入数据,应该要对其进行保留原始的数据
/**
* 更新用户
* @param user
* @return
*/
int updateUser(User user);
<update id="updateUser">
UPDATE `smbms`.`smbms_user` SET `userCode` = #{usercode}, `userName` = '含大牛1', `userPassword` = #{userpassword}, `gender` = #{gender}, `birthday` = #{birthday}, `phone` = #{phone}, `address` = #{address}, `userRole` = #{userrole}, `createdBy` = 2, `creationDate` = '2020-01-01 00:00:00', `modifyBy` = 2, `modifyDate` = '2020-01-01 00:00:00', `token` = NULL WHERE `id` = #{id};
</update>
set
能自动拼接set,并且能自动的识别需不需要添加,
/**
* 更新用户
* @param user
* @return
*/
int updateUserUserSet(User user);
<update id="updateUserUserSet">
UPDATE `smbms`.`smbms_user`
<set>
<if test="usercode != null and usercode != ''">
userCode = #{usercode},
</if>
<if test="username != null and username != ''">
username = #{username},
</if>
<if test="userpassword != null and userpassword != ''">
userpassword = #{userpassword},
</if>
<if test="gender != null">
gender = #{gender},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
</set>
where id = #{id}
</update>
trim
代替where+if
/**
* 根据用户名和用户角色进行动态查询
* @param userName
* @param userRole
* @return
*/
List<User> findByUserNameAndRoleUseTrim(@Param("userName") String userName,@Param("userRole") Integer userRole);
<select id="findByUserNameAndRoleUseTrim" resultType="User">
select * from smbms_user
<trim prefix="where" prefixOverrides="and | or">
<if test="userName != null and userName != ''">
and userName like concat("%",#{userName},"%")
</if>
<if test="userRole != null">
and userRole=#{userRole}
</if>
</trim>
</select>
代替set+if
/**
* 更新用户
* @param user
* @return
*/
int updateUserUseTrim(User user);
<update id="updateUserUseTrim">
UPDATE `smbms`.`smbms_user`
<trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
<if test="usercode != null and usercode != ''">
userCode = #{usercode},
</if>
<if test="username != null and username != ''">
username = #{username},
</if>
<if test="userpassword != null and userpassword != ''">
userpassword = #{userpassword},
</if>
<if test="gender != null">
gender = #{gender},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
</trim>
</update>
foreach
一般是用来实现像批量删除和新增等操作的
(注:参数可为数组类型、List类型、Map类型,当为数组时,collection属性应为array,为List时,值为list,为Map时,值为Key值)
/**
* 批量删除
* @param ids ID的数组
* @return
*/
int deleteBatch(Integer[] ids);
<delete id="deleteBatch" parameterType="list">
delete from smbms_user where id in
<!--
open是循环开始的时候以 ( 开头
separator是循环中间的时候以 , 开头
close是循环中间的时候以 ) 开头
item是循环出来的变量
index是循环的下标
-->
<foreach open="(" separator="," close=")" collection="array" item="id" index="index" >
#{id}
</foreach>
</delete>
choose
满足其一条件(when)就退出choose,都不满足则进入otherwise,相当于java中的多重if
/**
* 使用choose进行条件查询
* @param userCode
* @param userName
* @param userRole
* @return
*/
List<User> findByChoose(
@Param("userCode") String userCode,
@Param("userName") String userName,
@Param("userRole") Integer userRole
);
<select id="findByChoose" resultType="User">
select * from smbms_user
<trim prefix="where" prefixOverrides="and | or">
<choose>
<when test="userCode != null and userCode != ''">
and userCode = #{userCode}
</when>
<when test="userName != null and userName != ''">
and userName = #{userName}
</when>
<otherwise>
and userRole = #{userRole}
</otherwise>
</choose>
</trim>
</select>
分页查询
在pom.xml中添加坐标
<!--pageHelper分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.4</version>
</dependency>
在mybatis-config.xml中添加配置
<!-- 配置分页插件 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库-->
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
使用
@Test
public void findByPage(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//开始分页
PageHelper.startPage(3,4);
//查询所有的数据
List<User> users = userMapper.findUsers();
//封装pageInfo对象
PageInfo<User> pageInfo = new PageInfo<>(users);
//ctrl+D
System.out.println("总条数:"+pageInfo.getTotal());
System.out.println("总页数:"+pageInfo.getPages());
List<User> u = pageInfo.getList();
u.forEach(user -> {
System.out.println(user.toString());
});
System.out.println("当前页码:"+pageInfo.getPageNum());
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
整体代码
User
package com.changan.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
import java.util.List;
@Data
//有参构造方法
@AllArgsConstructor
//无参构造方法
@NoArgsConstructor
public class User {
/**
* 主键ID
*/
private Long id;
/**
* 用户编码
*/
private String usercode;
/**
* 用户名称
*/
private String username;
/**
* 用户密码
*/
private String userpassword;
/**
* 性别(1:女、 2:男)
*/
private Integer gender;
/**
* 出生日期
*/
private Date birthday;
/**
* 手机
*/
private String phone;
/**
* 地址
*/
private String address;
/**
* 用户角色(取自角色表-角色id)
*/
private Long userrole;
/**
* 创建者(userId)
*/
private Long createdby;
/**
* 创建时间
*/
private Date creationdate;
/**
* 更新者(userId)
*/
private Long modifyby;
/**
* 更新时间
*/
private Date modifydate;
/**
* 令牌
*/
private String token;
/**
* 用户角色名称
*/
//private String userRoleName;
//如果我想展示的是角色表中全部信息?
//一对一
private Role role;
//一对多
private List<Address> addressList;
}
Role
package com.changan.entity;
import lombok.Data;
import java.util.Date;
@Data
public class Role {
private Integer id; //id
private String roleCode; //角色编码
private String roleName; //角色名称
private Integer createdBy; //创建者
private Date creationDate; //创建时间
private Integer modifyBy; //更新者
private Date modifyDate;//更新时间
}
Address
package com.changan.entity;
import lombok.Data;
import java.util.Date;
@Data
public class Address {
//主键ID
private Integer id;
//邮编
private String postCode;
//联系人
private String contact;
//地址
private String addressDesc;
//联系电话
private String tel;
//创建者
private Integer createdBy;
//创建时间
private Date creationDate;
//更新者
private Integer modifyBy;
//更新时间
private Date modifyDate;
//用户ID
private Integer userId;
}
mybatis-config.xml
<?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"/>
<!--mybatis打印日志-->
<setting name="cacheEnabled" value="true" />
<setting name="useGeneratedKeys" value="true" />
<setting name="defaultExecutorType" value="REUSE" />
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings>
<!--别名配置-->
<typeAliases>
<package name="com.changan.entity"></package>
</typeAliases>
<!-- 配置分页插件 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库-->
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
<!--多套开发环境-->
<environments default="development">
<!--开发环境-->
<environment id="development">
<!--事务管理器 默认使用JDBC进行事务管理-->
<transactionManager type="JDBC"/>
<!--数据源配置-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/smbms?characterEncoding=UTF-8&useUnicode=true&serverTimezone=GMT%2B8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--对应的sql映射文件-->
<mappers>
<!--对应的mybatis的xml文件-->
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
UserMapper
package com.changan.mapper;
import com.changan.entity.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface UserMapper {
/**
* 查询所有用户
*/
List<User> findUsers();
/**
* 根据用户名称和用户角色进行查询
* @return
*/
List<User> findByUserNameAndUserRole(@Param("userName1") String userName,@Param("userRole1") Integer userRole);
/**
* 根据用户名称和用户角色进行查询
* @return
*/
List<User> findByUserNameAndUserRole1(User user);
/**
* 根据用户名称和用户角色进行查询
* @return
*/
List<User> findByUserNameAndUserRole2(Map<String,Object> map);
/**
* 根据用户表和角色表进行联表查询
* @return
*/
List<User> findByUserAndRole();
/**
* 根据用户表和角色表进行联表查询
* @return
*/
List<User> findByUserAndRole2();
/**
* 根据ID修改密码
* @param pwd 新密码
* @param id 用户ID
* @return
*/
int updatePwdById(@Param("userpassword") String pwd,@Param("id") Integer id);
/**
* 对用户和角色进行联表查询
* @return
*/
List<User> findAllUserAndRole();
/**
* 根据用户ID查询所有的地址信息
* @return
*/
List<User> findAddressById(Long id);
/**
* 根据用户名和用户角色进行动态查询
* @param userName
* @param userRole
* @return
*/
List<User> findByUserNameAndRole(@Param("userName") String userName,@Param("userRole") Integer userRole);
/**
* 根据用户名和用户角色进行动态查询
* @param userName
* @param userRole
* @return
*/
List<User> findByUserNameAndRoleUserWhere(@Param("userName") String userName,@Param("userRole") Integer userRole);
/**
* 更新用户
* @param user
* @return
*/
int updateUser(User user);
/**
* 更新用户
* @param user
* @return
*/
int updateUserUserSet(User user);
/**
* 根据用户名和用户角色进行动态查询
* @param userName
* @param userRole
* @return
*/
List<User> findByUserNameAndRoleUseTrim(@Param("userName") String userName,@Param("userRole") Integer userRole);
/**
* 更新用户
* @param user
* @return
*/
int updateUserUseTrim(User user);
/**
* 批量删除
* @param ids ID的数组
* @return
*/
int deleteBatch(Integer[] ids);
/**
* 使用choose进行条件查询
* @param userCode
* @param userName
* @param userRole
* @return
*/
List<User> findByChoose(
@Param("userCode") String userCode,
@Param("userName") String userName,
@Param("userRole") Integer userRole
);
}
UserMapper.xml
<?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="com.changan.mapper.UserMapper">
<!--id是唯一的,对应resultMap的唯一标识名称 type是里面返回的类型-->
<resultMap id="userMap" type="User">
<!--id标签表示表的主键-->
<!--column表示数据库的列 property表示实体类的属性-->
<!--使用id可以提高性能-->
<id column="id" property="id"></id>
<!--如果不是主键列,就用result表示-->
<result column="roleName" property="userRoleName"></result>
</resultMap>
<select id="findUsers" resultType="User">
select * from smbms_user
</select>
<select id="findByUserNameAndUserRole" resultType="User">
select * from smbms_user where userName like concat("%",#{userName1},"%")
and userRole=#{userRole1}
</select>
<select id="findByUserNameAndUserRole1" resultType="User" parameterType="User">
select * from smbms_user where userName like concat("%",#{username},"%")
and userRole=#{userrole}
</select>
<select id="findByUserNameAndUserRole2" resultType="User">
select * from smbms_user where userName like concat("%",#{username},"%")
and userRole=#{userrole}
</select>
<select id="findByUserAndRole" resultType="User">
select user.*,role.roleName as userRoleName from smbms_user user inner join smbms_role role
on user.userRole = role.id
</select>
<select id="findByUserAndRole2" resultMap="userMap">
select user.*,role.roleName from smbms_user user inner join smbms_role role
on user.userRole = role.id
</select>
<update id="updatePwdById">
update smbms_user set userPassword=#{userpassword} where id = #{id}
</update>
<resultMap id="UserAndRole" type="User">
<id property="id" column="id"></id>
<result property="username" column="userName"></result>
<!--association表示一个实体类-->
<!--association对应User类的属性值,javaType表示属性值的类型-->
<association property="role" javaType="Role">
<id property="id" column="roleId"></id>
<result property="roleName" column="roleName"></result>
</association>
</resultMap>
<select id="findAllUserAndRole" resultMap="UserAndRole">
select user.*,role.id as roleId,role.roleName from smbms_user user inner join smbms_role role
on user.userRole = role.id
</select>
<resultMap id="UserAndAddressList" type="User">
<id property="id" column="id"></id>
<result property="username" column="userName"></result>
<!--association表示一个实体类-->
<!--association对应User类的属性值,javaType表示属性值的类型-->
<collection property="addressList" ofType="com.changan.entity.Address">
<id property="id" column="addressId"></id>
<result property="addressDesc" column="addressDesc"></result>
</collection>
</resultMap>
<select id="findAddressById" resultMap="UserAndAddressList">
select user.*,address.id as addressId,address.addressDesc from smbms_user user inner join smbms_address address on
user.id = address.userId where user.id = #{id}
</select>
<select id="findByUserNameAndRole" resultType="User">
select * from smbms_user where
<if test="userName != null and userName != ''">
userName like concat("%",#{userName},"%")
</if>
<if test="userRole != null">
and userRole=#{userRole}
</if>
</select>
<select id="findByUserNameAndRoleUserWhere" resultType="User">
select * from smbms_user
<where>
<if test="userName != null and userName != ''">
and userName like concat("%",#{userName},"%")
</if>
<if test="userRole != null">
and userRole=#{userRole}
</if>
</where>
</select>
<update id="updateUser">
UPDATE `smbms`.`smbms_user` SET `userCode` = #{usercode}, `userName` = '含大牛1', `userPassword` = #{userpassword}, `gender` = #{gender}, `birthday` = #{birthday}, `phone` = #{phone}, `address` = #{address}, `userRole` = #{userrole}, `createdBy` = 2, `creationDate` = '2020-01-01 00:00:00', `modifyBy` = 2, `modifyDate` = '2020-01-01 00:00:00', `token` = NULL WHERE `id` = #{id};
</update>
<update id="updateUserUserSet">
UPDATE `smbms`.`smbms_user`
<set>
<if test="usercode != null and usercode != ''">
userCode = #{usercode},
</if>
<if test="username != null and username != ''">
username = #{username},
</if>
<if test="userpassword != null and userpassword != ''">
userpassword = #{userpassword},
</if>
<if test="gender != null">
gender = #{gender},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
</set>
where id = #{id}
</update>
<select id="findByUserNameAndRoleUseTrim" resultType="User">
select * from smbms_user
<trim prefix="where" prefixOverrides="and | or">
<if test="userName != null and userName != ''">
and userName like concat("%",#{userName},"%")
</if>
<if test="userRole != null">
and userRole=#{userRole}
</if>
</trim>
</select>
<update id="updateUserUseTrim">
UPDATE `smbms`.`smbms_user`
<trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
<if test="usercode != null and usercode != ''">
userCode = #{usercode},
</if>
<if test="username != null and username != ''">
username = #{username},
</if>
<if test="userpassword != null and userpassword != ''">
userpassword = #{userpassword},
</if>
<if test="gender != null">
gender = #{gender},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
</trim>
</update>
<delete id="deleteBatch" parameterType="list">
delete from smbms_user where id in
<!--
open是循环开始的时候以 ( 开头
separator是循环中间的时候以 , 开头
close是循环中间的时候以 ) 开头
item是循环出来的变量
index是循环的下标
-->
<foreach open="(" separator="," close=")" collection="array" item="id" index="index" >
#{id}
</foreach>
</delete>
<select id="findByChoose" resultType="User">
select * from smbms_user
<trim prefix="where" prefixOverrides="and | or">
<choose>
<when test="userCode != null and userCode != ''">
and userCode = #{userCode}
</when>
<when test="userName != null and userName != ''">
and userName = #{userName}
</when>
<otherwise>
and userRole = #{userRole}
</otherwise>
</choose>
</trim>
</select>
</mapper>
MyBaitsUtil
package com.changan.utils;
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.IOException;
import java.io.InputStream;
public class MyBaitsUtil {
static SqlSession sqlSession = null;
/**
* 获取SqlSession
* @return SqlSession对象
*/
public static SqlSession getSqlSession(){
String resource = "mybatis-config.xml";
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(resource);
} catch (IOException e) {
e.printStackTrace();
}
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sqlSessionFactory.openSession();
return sqlSession;
}
/**
* 关闭SqlSession
*/
public static void closeSqlSession(SqlSession sqlSession){
if(sqlSession!=null){
sqlSession.close();
}
}
}
MyBatisTest
package com.changan.test;
import com.changan.entity.User;
import com.changan.mapper.UserMapper;
import com.changan.utils.MyBaitsUtil;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyBatisTest {
@Test
public void select(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findUsers();
users.forEach(user -> {
System.out.println(user.toString());
});
}
@Test
public void findByUserNameAndUserRole(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findByUserNameAndUserRole("孙", 3);
users.forEach(user -> {
System.out.println(user.toString());
});
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void findByUserNameAndUserRole1(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsername("孙");
user.setUserrole(3L);
List<User> users = userMapper.findByUserNameAndUserRole1(user);
users.forEach(u -> {
System.out.println(u.toString());
});
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void findByUserNameAndUserRole2(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String,Object> map = new HashMap<>();
map.put("username","孙");
map.put("userrole","3");
List<User> users = userMapper.findByUserNameAndUserRole2(map);
users.forEach(u -> {
System.out.println(u.toString());
});
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void findByUserAndRole(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findByUserAndRole();
users.forEach(u -> {
System.out.println(u.toString());
});
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void findByUserAndRole2(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findByUserAndRole2();
users.forEach(u -> {
System.out.println(u.toString());
});
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void updatePwdById(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int i = userMapper.updatePwdById("123456", 25);
System.out.println(i);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void findAllUserAndRole(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findAllUserAndRole();
users.forEach(u -> {
System.out.println(u.toString());
});
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void findAddressById(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findAddressById(1L);
users.forEach(u -> {
System.out.println(u.toString());
});
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void findByUserNameAndRole(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findByUserNameAndRole("孙",3);
users.forEach(u -> {
System.out.println(u.toString());
});
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void findByUserNameAndRoleUserWhere(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findByUserNameAndRoleUserWhere(null,null);
users.forEach(u -> {
System.out.println(u.toString());
});
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void updateUser(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(25L);
user.setUsername("含宝宝");
user.setUserpassword("000000");
user.setAddress("长沙科泰");
userMapper.updateUser(user);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void updateUserUserSet(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(25L);
user.setUsercode("hanbaobao");
user.setUsername("含宝宝");
user.setUserpassword("000000");
user.setGender(1);
user.setBirthday(new Date());
userMapper.updateUserUserSet(user);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void findByUserNameAndRoleUseTrim(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findByUserNameAndRoleUseTrim(null,3);
users.forEach(u -> {
System.out.println(u.toString());
});
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void updateUserUseTrim(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(25L);
user.setUsercode("hanbaobao001");
user.setUsername("含宝宝001");
user.setUserpassword("123456");
user.setGender(2);
user.setBirthday(new Date());
userMapper.updateUserUseTrim(user);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void deleteBatch(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
userMapper.deleteBatch(new Integer[]{25,26});
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void findByChoose(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findByChoose(null,"李明",3);
users.forEach(u -> {
System.out.println(u.toString());
});
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
@Test
public void findByPage(){
SqlSession sqlSession = MyBaitsUtil.getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//开始分页
PageHelper.startPage(3,4);
//查询所有的数据
List<User> users = userMapper.findUsers();
//封装pageInfo对象
PageInfo<User> pageInfo = new PageInfo<>(users);
//ctrl+D
System.out.println("总条数:"+pageInfo.getTotal());
System.out.println("总页数:"+pageInfo.getPages());
List<User> u = pageInfo.getList();
u.forEach(user -> {
System.out.println(user.toString());
});
System.out.println("当前页码:"+pageInfo.getPageNum());
} catch (Exception e) {
e.printStackTrace();
} finally {
MyBaitsUtil.closeSqlSession(sqlSession);
}
}
}