mybatis-learn
public interface UserMapper { public List<User> getUserListByNullParam(); public List<User> getUesrListByUserName(String userName); public List<User> getUserList(User user); public List<User> getUserListByMap(Map<String,String> userMap); public List<User> getUserList(); public int add(User user); public int modify(User user); } <?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.kgc.dao.UserMapper"> <select id="count" resultType="int"> select count(1) from smbms_user </select> <select id="userlist" resultType="User">-- 返回的是user类型 select * from smbms_user </select> <select id="getUserListByNullParam" resultType="User"> select * from smbms_user </select> <!-- 耿局用户名来查询列表，模糊查询 parameterType 参数类型 resultType返回类型--> <select id="getUesrListByUserName" parameterType="String" resultType="User"> select * from smbms_user where userName like concat('%',#{userName},'%'); </select> <!-- 查询用户列表--> <!-- <select id="getUserList" parameterType="User" resultType="User">--> <!-- select * from smbms_user where userName like concat('%',#{userName},'%') and userRole = #{userRole};--> <!-- </select>--> <!-- Map查询--> <select id="getUserListByMap" parameterType="Map" resultType="User"> select * from smbms_user where userName like concat('%',#{userName},'%') and userRole = #{userRole}; </select> <!-- 多表查询低级--> <!-- <select id="getUserList" parameterType="User" resultType="User">--> <!-- select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where userName like concat('%',#{userName},'%') and userRole = #{userRole}--> <!-- and u.userRole = r.id;--> <!-- </select>--> <!-- 多表查询Map--> <resultMap id="userList" type="User"> <result column="id" property="id"/> <result column="userCode" property="userCode"/> <result column="userName" property="userName"/> <result column="phone" property="phone"/> <result column="userRole" property="userRole"/> <result column="roleName" property="userRoleName"/> <!-- column字段名指向 property 属性名 手工映射--> </resultMap> <select id="getUserList" parameterType="User" resultMap="userList"> select u.*,r.roleName from smbms_user u,smbms_role r where userName like concat('%',#{userName},'%') and userRole = #{userRole} and u.userRole = r.id; </select> <!-- 插入insert--> <insert id="add" parameterType="com.kgc.pojo.User"> insert into smbms_user(userCode,userName,userPassword,gender,birthday,phone,address,userRole,createdBy,creationDate) values(#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},#{address},#{userRole},#{createdBy},#{creationDate}) </insert> <!-- 修改--> <update id="modify" parameterType="com.kgc.pojo.User"> update smbms_user set userCode=#{userCode},userPassword=#{userPassword} where id=#{id} </update> </mapper>
//Serializable 序列化 网络传输 public class User implements Serializable { private Integer id; //id private String userCode; //用户编码 private String userName; //用户名称 private String userPassword; //用户密码 private Integer gender; //性别 private Date birthday; //出生日期 private String phone; //电话 private String address; //地址 private Integer userRole; //用户角色 private Integer createdBy; //创建者 private Date creationDate; //创建时间 private Integer modifyBy; //更新者 private Date modifyDate; //更新时间 private String userRoleName; //用户角色名称 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 Date getBirthday() { return birthday; } public void setBirthday(Date 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 getCreatedBy() { return createdBy; } public void setCreatedBy(Integer createdBy) { this.createdBy = createdBy; } public Date getCreationDate() { return creationDate; } public void setCreationDate(Date creationDate) { this.creationDate = creationDate; } public Integer getModifyBy() { return modifyBy; } public void setModifyBy(Integer modifyBy) { this.modifyBy = modifyBy; } public Date getModifyDate() { return modifyDate; } public void setModifyDate(Date modifyDate) { 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 + ", createdBy=" + createdBy + ", creationDate=" + creationDate + ", modifyBy=" + modifyBy + ", modifyDate=" + modifyDate + ", userRoleName='" + userRoleName + '\'' + '}'; } }
public class MybatisUtils { private static SqlSessionFactory sqlSessionFactory; static { String path = "mybatis-config.xml"; try { InputStream is = Resources.getResourceAsStream(path); sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); } catch (IOException e) { e.printStackTrace(); } } public static SqlSession getSqlSession(){ //事务 // 默认true 关闭 自动提交 //false 开启 return sqlSessionFactory.openSession(); } public static void closeSqlSession(SqlSession sqlSession){ if(null != sqlSession) sqlSession.close(); } }
public class TestUserMapper { @Test public void test01(){ //1.记载核心配置文件 String path = "mybatis-config.xml"; InputStream is = null; try { is = Resources.getResourceAsStream(path); //2.mybatis的核心类 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); SqlSession sqlSession = sqlSessionFactory.openSession(); int result = sqlSession.selectOne("com.kgc.dao.UserMapper.count"); System.out.println(result); } catch (IOException e) { e.printStackTrace(); } } @Test //使用工具类 public void test02(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); List<User> userList = sqlSession.selectList("com.kgc.dao.UserMapper.userlist"); for (User user : userList) { System.out.println(user); } } @Test //接口方式(以后全部用接口方式) public void test03(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); List<User> userList = sqlSession.getMapper(UserMapper.class).getUserListByNullParam(); for (User user : userList) { System.out.println(user); } } @Test public void test04(){ String userName = "赵"; SqlSession sqlSession = MybatisUtils.getSqlSession(); List<User> userList = sqlSession.getMapper(UserMapper.class).getUesrListByUserName(userName); for (User user : userList) { System.out.println(user); } } @Test //多条件查询 public void test05(){ User user = new User(); user.setUserName("赵"); user.setUserRole(2); SqlSession sqlSession = MybatisUtils.getSqlSession(); List<User> userList = sqlSession.getMapper(UserMapper.class).getUserList(user); for (User user1 : userList) { System.out.println(user1.getUserName()); } } @Test //Map查询 public void test06(){ List<User> userList = new ArrayList<>(); Map<String,String> userMap = new HashMap<>(); SqlSession sqlSession = MybatisUtils.getSqlSession(); userMap.put("userName","赵"); userMap.put("userRole","2"); List<User> userListByMap = sqlSession.getMapper(UserMapper.class).getUserListByMap(userMap); for (User user : userListByMap) { System.out.println(user.getUserName()); } } @Test public void test07(){ User user = new User(); user.setUserName("赵"); user.setUserRole(2); SqlSession sqlSession = MybatisUtils.getSqlSession(); List<User> userList = sqlSession.getMapper(UserMapper.class).getUserList(user); for (User user1 : userList) { System.out.println(user1.getUserName()+"*******"+user1.getUserRoleName()+ "*******"+user1.getAddress()); } } @Test public void testAdd(){ int count = 0; SqlSession sqlSession = null; User user = null; try{ sqlSession = MybatisUtils.getSqlSession(); user = new User(); user.setUserCode("test001"); user.setUserName("测试001"); user.setAddress("测试地址"); user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("2019-10-21")); user.setGender(1); user.setUserPassword("123456"); user.setUserRole(2); user.setCreatedBy(1); user.setCreationDate(new Date()); count = sqlSession.getMapper(UserMapper.class).add(user); //int i = 2/0; sqlSession.commit(); }catch (Exception e){ e.printStackTrace(); sqlSession.rollback(); count = 0; }finally { MybatisUtils.closeSqlSession(sqlSession); } System.out.println(count); } @Test public void testModify(){ int count = 0; SqlSession sqlSession = null; User user = null; try{ sqlSession = MybatisUtils.getSqlSession(); user = new User(); user.setId(16); user.setUserPassword("654321"); user.setUserCode("009"); count = sqlSession.getMapper(UserMapper.class).modify(user); sqlSession.commit(); }catch (Exception e){ //sqlSession.rollback(); e.printStackTrace(); }finally { MybatisUtils.closeSqlSession(sqlSession); } System.out.println(count); } }
<?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> <!--加载数据库配置信息--> <properties resource="database.properties"> <!-- <property name="driver" value="com.mysql.cj.jdbc.Driver"></property>--> <!-- <property name="url" value="jdbc:mysql://localhost:3306/smbms?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL"></property>--> <!-- <property name="username" value="root"></property>--> <!-- <property name="password" value=""></property>--> </properties> <!--设置日记行为--> <settings> <setting name="logImpl" value="LOG4J"></setting> <!-- 延迟加载--> <setting name="lazyLoadingEnabled" value="false"></setting> <!-- 禁止自动匹配--> <setting name="autoMappingBehavior" value="NONE"></setting> </settings> <!--設置別名--> <typeAliases> <!--不推荐使用，当我们有很多的实体类的时候，这里配置的也很冗余 <typeAlias type="cn.kgc.pojo.User"></typeAlias>--> <package name="com.kgc.pojo"></package> </typeAliases> <!--配置运行环境--> <!-- dev开发环境--> <environments default="dev"> <environment id="dev"> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> <!-- 测试环境--> <environment id="test"> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> <!-- 生产环境--> <environment id="pro"> <transactionManager type=""></transactionManager> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/kgc/dao/UserMapper.xml"></mapper> </mappers> </configuration>
