mybatis 框架 的应用之二(批量添加、实现分页查询)
2017-02-14 09:36 甘雨路 阅读(1471) 评论(0) 编辑 收藏 举报lf-driver=com.mysql.jdbc.Driver lf-url=jdbc:mysql://localhost:3306/test lf-user=LF lf-password=LF
<?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.zr.mybatis.mapper.HouseMapper"> <!-- 数据库的表字段与实体类的属性之间的映射 --> <resultMap type="cn.zr.mybatis.entity.House" id="house"> <id column="addr_city" property="city"/> <result column="addr_province" property="province"/> <result column="addr_country" property="country"/> </resultMap> <!-- 查询所有数据 --> <select id="getAllHouse" resultMap="house"> SELECT addr_city,addr_province,addr_country FROM HOUSE </select> </mapper>
<?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.zr.mybatis.mapper.UserMapper"> <!-- USERTEST 表的字段 --> <sql id="usertestColumns"> NAME,AGE,ADDRESS </sql> <!-- 查询USERTEST的所有数据 --> <select id="getAllUser" resultType="cn.zr.mybatis.entity.User"> SELECT <include refid="usertestColumns"/> FROM USERTEST </select> <!-- 通过字段来插入用户 --> <insert id="addUserByWords"> INSERT INTO USERTEST(NAME,AGE,ADDRESS) VALUES(#{0},#{1},#{2}) </insert> <!-- 插入用户 --> <insert id="addUser" parameterType="cn.zr.mybatis.entity.User"> INSERT INTO USERTEST VALUES(#{name},#{age},#{address}) </insert> <!-- 批量添加用户 --> <insert id="bacthUsers" parameterType="cn.zr.mybatis.entity.User"> INSERT INTO USERTEST(<include refid="usertestColumns"/>) VALUES <foreach collection="list" item="val" separator=","> (#{val.name},#{val.age},#{val.address}) </foreach> </insert> <!-- 模糊查询,并实现分页 --> <select id="queryUserByWord" parameterType="map" resultType="cn.zr.mybatis.entity.User"> <if test="name != null"> <!-- 给name拼接% --> <bind name="pattern" value=" '%'+name+'%' "/> SELECT <include refid="usertestColumns"/> FROM USERTEST <where> NAME LIKE #{pattern} ORDER BY NAME LIMIT #{star},#{size} </where> </if> </select> <!-- 在给定的区间内查询,并实现分页 --> <select id="queryUserInScope" parameterType="map" resultType="cn.zr.mybatis.entity.User"> SELECT <include refid="usertestColumns"/> FROM USERTEST WHERE NAME IN <foreach collection="names" item="value" open="(" close=")" separator=","> #{value} </foreach> LIMIT #{stat},#{size} </select> <!-- 根据数组中的值进行查询 --> <select id="queryUserByNames" parameterType="java.lang.String" resultType="cn.zr.mybatis.entity.User"> SELECT <include refid="usertestColumns"/> FROM USERTEST <where> NAME IN <foreach collection="array" item="val" open="(" close=")" separator=","> #{val} </foreach> </where> </select> </mapper>
<?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="mybatis/config/db.properties"></properties> <!-- 配置环境 --> <environments default="development"> <environment id="development"> <!-- 配置事务管理器的类型 --> <transactionManager type="JDBC"/> <!-- 配置数据源相关的属性 --> <dataSource type="UNPOOLED"> <property name="driver" value="${lf-driver}"/> <property name="url" value="${lf-url}"/> <property name="username" value="${lf-user}"/> <property name="password" value="${lf-password}"/> </dataSource> </environment> </environments> <!--END 配置环境 --> <!-- 配置映射路径 --> <mappers> <mapper resource="mybatis/config/mapper/UserMapper.xml"/> <mapper resource="mybatis/config/mapper/HouseMapper.xml"/> </mappers> <!-- END 配置映射路径 --> </configuration>
package cn.zr.mybatis.entity; public class House { private String country;//国家 private String province;//省份 private String city;//城市 public String getCountry() { return country; } public void setCountry(String country) { this.country = country; } public String getProvince() { return province; } public void setProvince(String province) { this.province = province; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public House() { super(); } public House(String country, String province, String city) { super(); this.country = country; this.province = province; this.city = city; } @Override public String toString() { return "Address [country=" + country + ", province=" + province + ", city=" + city + "]"; } }
package cn.zr.mybatis.entity; public class User { private String name; private Integer age; private String address; public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public User() { super(); } public User(String name, Integer age, String address) { super(); this.name = name; this.age = age; this.address = address; } @Override public String toString() { return "User [name=" + name + ", age=" + age + ", address=" + address + "]"; } }
package cn.zr.mybatis.mapper; import java.util.List; import cn.zr.mybatis.entity.User; public interface HouseMapper { /** * 查询 * @return 返回相关的房子数据 */ List<User> getAllHouse(); }
package cn.zr.mybatis.mapper; import java.util.List; import java.util.Map; import cn.zr.mybatis.entity.User; public interface UserMapper { /** * 获取所有数据 */ public List<User> getAllUser(); /** * 通过字段插入插入用户 * @param name 用户名 * @param age 年龄 * @param address 地址 * @return 返回成功插入数据的数量 */ public int addUserByWords(String name, int age, String address); /** * 插入数据 * @param user 被插入的用户 * @return 返回成功插入数据的数量 */ public int addUser(User user); /** * 批量添加用户 * @param list 集合(存储User对象) * @return 返回成功插入数据的数量 */ public int bacthUsers(List<User> list); /** * 模糊查询,并实现分页 * @param map 键值对(存着起始位置,每页的数量,以及模糊查询的数量) * @return 返回相关的用户数据 */ public List<User> queryUserByWord(Map<String, Object> map); /** * 在某个区间内查询,并实现分页 * @param map键值对(存着起始位置,每页的数量,以及区间的范围) * @return 返回相关的用户数据 */ public List<User> queryUserInScope(Map<String, Object> map); /** * 通过数组中的值作为区间进行查询操作 * @param names 区间的值 * @return 返回相关的用户数据 */ public List<User> queryUserByNames(String[] names); }
package cn.zr.mybatis.service; import java.util.List; import java.util.Map; import cn.zr.mybatis.entity.User; public interface UserService { /** * 获取所有的用户信息 */ List<User> getAllUser(); /** * 插入用户 * @param name 用户名 * @param age 年龄 * @param address 地址 * @return 返回成功插入数据的数量 */ int addUserByWords(String name, int age, String address); /** * 插入数据 * @param user 被插入的用户 * @return 返回成功插入数据的数量 */ int addUser(User user); /** * 批量添加用户 * @param list 集合(存储User对象) * @return 返回成功插入数据的数量 */ int bacthUsers(List<User> list); /** * 模糊查询,并实现分页 * @param map 键值对(存着起始位置,每页的数量,以及模糊查询的数量) * @return 返回相关的用户数据 */ List<User> queryUserByWord(Map<String, Object> map); /** * 在某个区间内查询,并实现分页 * @param map键值对(存着起始位置,每页的数量,以及区间的范围) * @return 返回相关的用户数据 */ List<User> queryUserInScope(Map<String, Object> map); /** * 通过数组中的值作为区间进行查询操作 * @param names 区间的值 * @return 返回相关的用户数据 */ List<User> queryUserByNames(String[] names); /** * 查询 * @return 返回相关的房子数据 */ List<User> getAllHouse(); }
package cn.zr.mybatis.service.impl; import java.io.IOException; import java.io.Reader; import java.util.List; import java.util.Map; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSessionManager; import cn.zr.mybatis.entity.User; import cn.zr.mybatis.mapper.HouseMapper; import cn.zr.mybatis.mapper.UserMapper; import cn.zr.mybatis.service.UserService; public class UserServiceImpl implements UserService{ private static SqlSessionManager manager; private UserMapper userMapper; private HouseMapper houseMapper; { // 配置文件的路径 String resource = "mybatis/config/mybatis-config.xml"; try { // 将配置文件读取进来 Reader reader = Resources.getResourceAsReader(resource); // 根据读取进来的配置文件创建SqlSessionManager对象 manager = SqlSessionManager.newInstance(reader); //创建映射关系 userMapper = manager.getMapper(UserMapper.class); //创建映射关系 houseMapper = manager.getMapper(HouseMapper.class); } catch (IOException e) { e.printStackTrace(); } } @Override public List<User> getAllUser() { //userMapper调用接口的相关方法 return userMapper.getAllUser(); } @Override public int addUserByWords(String name, int age, String address) { return userMapper.addUserByWords(name,age,address); } @Override public int addUser(User user) { return userMapper.addUser(user); } @Override public int bacthUsers(List<User> list) { return userMapper.bacthUsers(list); } @Override public List<User> queryUserByWord(Map<String, Object> map) { return userMapper.queryUserByWord(map); } @Override public List<User> queryUserInScope(Map<String, Object> map) { return userMapper.queryUserInScope(map); } @Override public List<User> queryUserByNames(String[] names) { return userMapper.queryUserByNames(names); } @Override public List<User> getAllHouse() { return houseMapper.getAllHouse(); } }
package cn.zr.mybatis.test; import java.io.IOException; import java.io.Reader; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSessionManager; import org.junit.Before; import org.junit.Test; import cn.zr.mybatis.entity.House; import cn.zr.mybatis.entity.User; import cn.zr.mybatis.mapper.UserMapper; import cn.zr.mybatis.service.UserService; import cn.zr.mybatis.service.impl.UserServiceImpl; /** * * @author lf * 2017年2月13日 * 通过接口调用相关方法 */ public class TestMybatis { // public static void main(String[] args) { // TestMybatis testMybatis = new TestMybatis(); // testMybatis.addUserAndAddress(); // // } private SqlSessionManager manager; private UserService userService; { //创建实现类 userService = new UserServiceImpl(); } /** * 获取所有数据 */ @Test public void getAllData() { // 调用相关方法 List<User> list = userService.getAllUser();; System.out.println(list); } /** * 通过字段来添加用户 */ @Test public void addUserByWords() { int count = userService.addUserByWords("lffff",18,"beijing"); if (count > 0) { System.out.println("成功插入"+count+"条数据"); }else { System.out.println("插入操作失败"); } } /** * 添加用户 */ @Test public void addUser () { User user = new User("dew",18,"hubei"); int count = userService.addUser(user); if (count > 0) { System.out.println("成功插入"+count+"条数据"); }else { System.out.println("插入操作失败"); } } /** * 批量增加用户 */ @Test public void bacthUsers(){ // 创建list List<User> list = new ArrayList<User>(); list.add(new User("asd", 23, "China")); list.add(new User("ad", 23, "China")); list.add(new User("asddd", 23, "China")); list.add(new User("aswwd", 23, "China")); list.add(new User("asqwqd", 23, "China")); int count = userService.bacthUsers(list); if (count > 0) { System.out.println("成功插入"+count+"条数据"); }else { System.out.println("插入操作失败"); } } /** * 模糊查询,并实现分页效果 */ @Test public void queryUserByWord() { int star = 0;// 起始位置 int size = 3;// 每页的条数 Map<String, Object> map = new HashMap<String, Object>(); map.put("star", star); map.put("size", size); map.put("name", "a"); List<User> list = userService.queryUserByWord(map); if (list.size() > 0) { System.out.println(list); }else { System.out.println("操作失败"); } } /** * 区间分页查询 */ @Test public void queryUserInScope() { // 创建list List<String> list = new ArrayList<String>(); list.add("asd"); list.add("ad"); list.add("asddd"); list.add("aswwd"); list.add("asqwqd"); int stat = 1;//起始位置 int size = 3;//每页显示的数量 //创建Map Map<String, Object> map = new HashMap<String, Object>(); map.put("stat", stat); map.put("size", size); map.put("names", list); List<User> reseultList = userService.queryUserInScope(map); if (reseultList.size() > 0) { System.out.println(reseultList); }else { System.out.println("操作失败"); } } /** * 通过数组中的值作为区间取查询 */ @Test public void queryUserByNames() { String[] names = {"asddd","aswwd","asqwqd"}; List<User> list = userService.queryUserByNames(names); if (list.size() > 0) { System.out.println(list); }else { System.out.println("操作失败"); } } /** * 查询所有数据 */ @Test public void getAllHouse(){ List<User> list = userService.getAllHouse(); if (list.size() > 0) { System.out.println(list); }else { System.out.println("操作失败"); } } }