- 导入jar包
- 编写datasource.properties连接数据库的配置文件
- 编写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"> <!-- mybatis核心配置文件 --> <configuration> <properties resource="database.properties"></properties> <settings> <!-- 设置全自动匹配 --> <setting name="autoMappingBehavior" value="FULL" /> <!-- 启用缓存 --> <setting name="cacheEnabled" value="true" /> </settings> <typeAliases> <package name="com.pojo" /> </typeAliases> <!-- 配置环境 --> <environments default="a"> <environment id="a">' <!-- 事物管理器 (type="JDBC"写死)--> <transactionManager type="JDBC"></transactionManager> <!-- 将配置文件的数据读取 (type="POOLED"写死)value值必须database.properties的name名相同--> <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/dao/UserMapper.xml"/> </mappers> </configuration>
-
- 编写查询的接口
-
package com.dao; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Param; import com.pojo.User; public interface UserMapper { // 根据用户名进行模糊查询(单个参数入参) List<User> findUserByName(String uname); // 根据用户信息查询(实体类入参) List<User> findUserByUser(User user); // (以map集合的方式入参) List<User> findUserByMap(Map<String, String> map); // (传入两个参数查询) List<User> findUserByNameAndGender(String uname, int gender); // 以配置方法传两个参数 List<User> findUserByNameAndGender1(@Param("uname") String name, @Param("gender") int gender); // 根据员工的姓名模糊查询出员工的信息,(包含员工的角色名称) List<User> findUserAndRoleNameByName(String uname); // 根据员工的姓名模糊查询出员工的信息,(包含员工的角色名称) List<User> findUserAndRoleNameByName1(String uname); // 根据员工姓名模糊查询,查询所员工的(包含所有信息和角色信息) List<User> findUserAndRoleByName(String uname); // 根据员工姓名模糊查询,显示所有住址 List<User> findUserAndAddressByName(String name); //添加user表中的数据 int addUser(User user); //修改user表中的数据 int updateUser(User user); //删除user表中的数据 int deleteUser(int id); }
-
- 编写接口同名的Mapper.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.dao.UserMapper"> <!-- 开启二级缓存 --> <cache></cache> <!-- 单参数入参,(以接口的参数名作为参数) --> <select id="findUserByName" resultType="User"> SELECT * FROM smbms_user WHERE userName LIKE CONCAT('%',#{uname},'%') </select> <!-- 以对象入参,(传入的值必须是实体类的属性值) --> <select id="findUserByUser" resultType="User"> SELECT * FROM smbms_user WHERE userName LIKE CONCAT('%',#{userName},'%') AND phone=#{phone} </select> <!-- 以map集合入属性入参(以key值作为参数) --> <select id="findUserByMap" resultType="User"> SELECT * FROM smbms_user WHERE id=#{id} AND gender=#{sex} </select> <!-- 多参数入参写法1 --> <select id="findUserByNameAndGender" resultType="User"> SELECT * FROM smbms_user WHERE userName LIKE CONCAT('%',#{param1},'%') AND gender=#{param2} </select> <!-- 多参数入参写法2 --> <select id="findUserByNameAndGender1" resultType="User"> SELECT * FROM smbms_user WHERE userName LIKE CONCAT('%',#{uname},'%') AND gender=#{gender} </select> <!-- 返回多张表的数据时,使用字段别名进行另一个表的数据绑定 --> <select id="findUserAndRoleNameByName" resultType="User"> SELECT u.*,r.roleName as userRoleName FROM `smbms_user` u,`smbms_role` r WHERE u.userRole=r.id and userName LIKE CONCAT('%',#{uname},'%') </select> <!-- 返回多张表的数据是,方法二:使用resultMap进行另一个表的数据绑定,是一个自定义的结果集 --> <select id="findUserAndRoleNameByName1" resultMap="userList"> SELECT u.*,r.roleName FROM `smbms_user` u,`smbms_role` r WHERE u.userRole=r.id and userName LIKE CONCAT('%',#{uname},'%') </select> <resultMap type="User" id="userList"> <!-- 对查询结果进行手动匹配 column是数据库的字段名,property是实体类的字段名 --> <id column="id" property="id"/> <result column="roleName" property="userRoleName"/> <result column="userName" property="userName"/> </resultMap> <!-- 查询返回一个实体类中的的对象属性时 --> <select id="findUserAndRoleByName" resultMap="userRoleList" > SELECT * FROM `smbms_user` u,`smbms_role` r WHERE u.userRole=r.id and userName LIKE CONCAT('%',#{uname},'%') </select> <resultMap type="User" id="userRoleList"> <id column="id" property="id"/> <!-- 匹配对象属性 --> <association property="role" javaType="Role"> <id column="id" property="id"/> </association> </resultMap> <!-- 查询返回一个实体类中的的对象集合属性时 --> <select id="findUserAndAddressByName" resultMap="userAddressList"> SELECT u.*,a.id as a_id,a.addressDesc,a.contact,a.tel FROM `smbms_user` u,`smbms_address` a WHERE u.id=a.userId and userName LIKE CONCAT('%',#{uname},'%') </select> <resultMap type="User" id="userAddressList"> <id column="id" property="id"/> <result column="userName" property="userName"/> <!-- 匹配对象集合的属性 --> <collection property="addressList" ofType="Address"> <id column="a_id" property="id"/> <result column="contact" property="contact"/> <result column="addressDesc" property="addressDesc"/> </collection> </resultMap> <!-- 添加数据操作 --> <insert id="addUser" > INSERT INTO `smbms_user` VALUES(0,'zhansan',#{userName},'123',1,'1999-9-9','13131313131','湖北',3,1,'2019-1-9',NULL,NULL) </insert> <!-- 修改数据操作 --> <update id="updateUser"> UPDATE `smbms_user` SET userName=#{userName} WHERE id=#{id} </update> <!-- 删除数据操作 --> <delete id="deleteUser"> DELETE FROM `smbms_user` WHERE id=#{id} </delete> </mapper>
-
- 编写测试类
-
package com.test; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.session.SqlSession; import org.apache.log4j.Logger; import org.junit.Test; import com.dao.UserMapper; import com.pojo.Address; import com.pojo.User; import com.util.MyBatisUtil; public class MyBatisTest { Logger logger=Logger.getLogger(MyBatisTest.class); @Test // 根据用户名进行模糊查询 public void selectByName(){ SqlSession sqlSession = MyBatisUtil.openSession(); List<User> users = sqlSession.getMapper(UserMapper.class).findUserByName("张"); for (User u : users) { logger.debug(u); } } @Test // 根据用户信息查询(实体类入参) public void selectByUser(){ SqlSession sqlSession = MyBatisUtil.openSession(); User user=new User(); user.setUserName("张"); user.setPhone("13544561111"); List<User> users = sqlSession.getMapper(UserMapper.class).findUserByUser(user); for (User u : users) { logger.debug(u); } } @Test // (以map集合的方式入参) public void selectByMap(){ SqlSession sqlSession = MyBatisUtil.openSession(); Map<String, String> map=new HashMap<String, String>(); map.put("id", "12"); map.put("sex", "1"); List<User> users = sqlSession.getMapper(UserMapper.class).findUserByMap(map); for (User u : users) { logger.debug(u); } } @Test // (传入两个参数查询) public void selectByNameAndGender(){ SqlSession sqlSession = MyBatisUtil.openSession(); List<User> users = sqlSession.getMapper(UserMapper.class).findUserByNameAndGender("张", 1); for (User u : users) { logger.debug(u); } } @Test // 以配置方法传两个参数 public void selectByParam(){ SqlSession sqlSession = MyBatisUtil.openSession(); List<User> users = sqlSession.getMapper(UserMapper.class).findUserByNameAndGender1("张", 1); for (User u : users) { logger.debug(u); } } @Test // 根据员工的姓名模糊查询出员工的信息,(包含员工的角色名称) public void selectUserAndRoleNameByName(){ SqlSession sqlSession = MyBatisUtil.openSession(); List<User> users = sqlSession.getMapper(UserMapper.class).findUserAndRoleNameByName("张"); for (User u : users) { logger.debug(u); } } @Test // 根据员工的姓名模糊查询出员工的信息,(包含员工的角色名称) public void selectUserAndRoleNameByName1(){ SqlSession sqlSession = MyBatisUtil.openSession(); List<User> users = sqlSession.getMapper(UserMapper.class).findUserAndRoleNameByName1("张"); for (User u : users) { logger.debug(u); } } @Test // 根据员工姓名模糊查询,查询所员工的(包含所有信息和角色信息) public void selectUserAndRoleByName(){ SqlSession sqlSession = MyBatisUtil.openSession(); List<User> users = sqlSession.getMapper(UserMapper.class).findUserAndRoleByName("张"); for (User u : users) { logger.debug(u.getPhone()+u.getUserName()+","+u.getRole().getRoleName()); } } @Test // 根据员工姓名模糊查询,显示所有住址 public void selectUserAndAddressListByName(){ SqlSession sqlSession = MyBatisUtil.openSession(); List<User> users = sqlSession.getMapper(UserMapper.class).findUserAndAddressByName("管"); for (User u : users) { logger.debug(u.getId()+":"+u.getUserName()+":"); List<Address> addressList = u.getAddressList(); for (Address add : addressList) { logger.debug(add.getAddressDesc()+":"+add.getContact()+":"+add.getTel()); } } } @Test //添加 public void insert(){ SqlSession sqlSession = MyBatisUtil.openSession(); User user=new User(); user.setUserName("张三"); int count = sqlSession.getMapper(UserMapper.class).addUser(user); //事物提交,(对表数据进行更新)对表的数据进添删改 sqlSession.commit(); logger.debug(count); } @Test //修改 public void update(){ SqlSession sqlSession = MyBatisUtil.openSession(); User user=new User(); user.setId(21); user.setUserName("李四"); int count = sqlSession.getMapper(UserMapper.class).updateUser(user); //事物提交,(对表数据进行更新)对表的数据进添删改 sqlSession.commit(); logger.debug(count); } @Test //删除 public void delete(){ SqlSession sqlSession = MyBatisUtil.openSession(); int count = sqlSession.getMapper(UserMapper.class).deleteUser(18); //事物提交,(对表数据进行更新)对表的数据进添删改 sqlSession.commit(); logger.debug(count); } @Test //二级缓存测试 public void huancun(){ SqlSession sqlSession = MyBatisUtil.openSession(); List<User> users = sqlSession.getMapper(UserMapper.class).findUserAndAddressByName("管"); for (User u : users) { logger.debug(u.getId()+":"+u.getUserName()+":"); List<Address> addressList = u.getAddressList(); for (Address add : addressList) { logger.debug(add.getAddressDesc()+":"+add.getContact()+":"+add.getTel()); } } sqlSession.close();//关闭sqlSession(一级缓存就没了) sqlSession = MyBatisUtil.openSession(); users = sqlSession.getMapper(UserMapper.class).findUserAndAddressByName("管"); for (User u : users) { logger.debug(u.getId()+":"+u.getUserName()+":"); List<Address> addressList = u.getAddressList(); for (Address add : addressList) { logger.debug(add.getAddressDesc()+":"+add.getContact()+":"+add.getTel()); } } sqlSession.close();//关闭sqlSession(一级缓存就没了) } }
-
MyBatis框架的简单搭建(多种入参方式查询和结果的映射)
最新推荐文章于 2021-01-26 21:48:53 发布