定义一个Car类:
/** * 封装汽车相关信息的 pojo类 */ public class Car { // 数据库表当中的字段应该和pojo类的属性一一对应 // 建议使用包装类, 这样可以防止null的问题 private Long id; private String carNum; private String brand; private Double guidePrice; private String produceTime; private String carType; public Car() { } public Car(Long id, String carNum, String brand, Double guidePrice, String produceTime, String carType) { this.id = id; this.carNum = carNum; this.brand = brand; this.guidePrice = guidePrice; this.produceTime = produceTime; this.carType = carType; } public Long getId() {return id;} public void setId(Long id) {this.id = id;} @Override public String toString() { return "Car{" + "id=" + id + ", carNum='" + carNum + '\'' + ", brand='" + brand + '\'' + ", guidePrice=" + guidePrice + ", produceTime='" + produceTime + '\'' + ", carType='" + carType + '\'' + '}'; } public String getCarNum() {return carNum;} public void setCarNum(String carNum) {this.carNum = carNum;} public String getBrand() {return brand;} public void setBrand(String brand) {this.brand = brand;} public Double getGuidePrice() {return guidePrice;} public void setGuidePrice(Double guidePrice) {this.guidePrice = guidePrice;} public String getProduceTime() {return produceTime;} public void setProduceTime(String produceTime) {this.produceTime = produceTime;} public String getCarType() {return carType;} public void setCarType(String carType) {this.carType = carType;} }
1. 查询之返回Car:
// CarMapper.xml // 必须要指定返回结果的类型 <select id="selectById" resultType="Car"> <!-- select * from car where id = #{id}--> // 列名要和pojo类中的属性名要对的上, 否则查出来为null select id, car_num as carNum, brand, guide_price as guidePrice, produce_time as produceTimme, car_type as catType from car where id = #{id} </select> // 接口 public interface CarMapper{ // 根据id查询Car信息 Car selectById(Long id); } // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); CarMapper mapper = sqlSession.getMapper(CarMapper.class); Car car = mapper.selectById(16L); System.out.println(car); sqlSession.close(); }
2. 查询之返回多个Car:
// CarMapper.xml // 必须要指定返回结果的类型 <select id="selectAll" resultType="Car"> <!-- select * from car where id = #{id}--> // 列名要和pojo类中的属性名要对的上, 否则查出来为null select id, car_num as carNum, brand, guide_price as guidePrice, produce_time as produceTimme, car_type as catType from car </select> // 接口 public interface CarMapper{ // 查询所有Car信息 List<Car> selectAll(); } // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); CarMapper mapper = sqlSession.getMapper(CarMapper.class); List<Car> cars = mapper.selectAll(); cars.forEach(car -> System.out.println(car)); sqlSession.close(); }
3. 查询之返回Map:
- map的key都是数据库的列名
// CarMapper.xml // resultType="java.util.Map" 有别名: "map" <select id="selectByIdRetMap" resultType="map"> // 返回的类型是个map, 不需要和Car类属性匹配了 select * from car where id = #{id} </select> // 接口 public interface CarMapper{ // 根据id获取汽车信息, 将信息放到Map集合中 Map<String, Object> selectByIdRetMap(Long id); } // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); CarMapper mapper = sqlSession.getMapper(CarMapper.class); Map<String, Object> car = mapper.selectByIdRetMap(16L); System.out.println(car); sqlSession.close(); }
4. 查询之返回多个Map:
// CarMapper.xml // resultType="map", 不能写"list" <select id="selectAllRetListMap" resultType="map"> // 返回的类型是个map, 不需要和Car类属性匹配了 select * from car </select> // 接口 public interface CarMapper{ // 查询所有的car信息, 返回一个存放Map集合的List集合 List<Map<String, Object>> selectAllRetListMap(); } // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); CarMapper mapper = sqlSession.getMapper(CarMapper.class); List<Map<String, Object>> maps = mapper.selectAllRetListMap(); maps.forEach(map -> System.out.println(map)); sqlSession.close(); }
5. 查询之返回Map:
- 拿Car的id做key, 以后取出对应的Map集合时更加方便
- 需要使用到@MapKey注解
// CarMapper.xml // resultType="map", 不能写"list" <select id="selectAllRetMap" resultType="map"> // 返回的类型是个map, 不需要和Car类属性匹配了 select * from car </select> // 接口 public interface CarMapper{ // 查询所有的Car, 返回一个Map集合 // Map集合的key是每条记录的主键值 // Map集合的value是每条记录 @MapKey("id") // 将查询结果的id值作为一个大map集合的key Map<Long,Map<String, Object>> selectAllRetMap(); } // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); CarMapper mapper = sqlSession.getMapper(CarMapper.class); Map<Long,Map<String, Object>> maps = mapper.selectAllRetMap(); System.out.println(maps); sqlSession.close(); }
6. resultMap结果映射:
查询结果的列名和Java对象的属性名对应不上怎么办?
- as给列起别名
// CarMapper.xml // 必须要指定返回结果的类型 <select id="selectAll" resultType="Car"> select id, car_num as carNum, brand, guide_price as guidePrice, produce_time as produceTimme, car_type as catType from car </select> // 接口 public interface CarMapper{ // 查询所有Car信息 List<Car> selectAll(); } // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); CarMapper mapper = sqlSession.getMapper(CarMapper.class); List<Car> cars = mapper.selectAll(); cars.forEach(car -> System.out.println(car)); sqlSession.close(); }
- 使用resultMap进行结果映射
// CarMapper.xml <resultMap id="carResultMap" type="Car"> // 如果数据库表中有主键, 一般都是有主键的, 否则不符合数据库设计第一范式 // 如果有主键, 建议这里面配置一个id标签, 这样是为了提高效率 <id property ="id" column="id"/> // property后面填写的是POJO类的属性名 // column后面填写的是数据库的字段名 <result property="carNum" column="car_num"/> // 如果属性名和字段名相同, 可以不配置 <result property="brand" column="brand"/> <result property="guidePrice" column="guide_price"/> <result property="produceTime" column="produce_time"/> <result property="carType" column="car_type"/> </resultMap> // resultMap属性用来指定使用哪个结果映射, resultMap后面的值是resultMap的id <select id="selectAllByResultMap" resultMap="carResultMap"> select * from car </select> // 接口 public interface CarMapper{ // 查询所有Car信息, 使用resultMap标签 List<Car> selectAllByResultMap(); } // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); CarMapper mapper = sqlSession.getMapper(CarMapper.class); List<Car> cars = mapper.selectAllByResultMap(); cars.forEach(car -> System.out.println(car)); sqlSession.close(); }
- 是否开启驼峰命名自动映射(配置settings)
- 使用这种方式的前提是: 属性遵循java命名规范, 数据库的列名遵循sql的命名规范
- java命名规范: 首字母小写, 后面每个单词首字母大写, 遵循驼峰命名方式
- sql命名规范: 全部小写, 单词之间采用下划线分割
如何启用该功能呢? 需要在mybatis-config.xml文件中配置 // 放在properties标签后面 <settings> <setting name="mapUnderscoreToCameCase" value="true"> </settings> // CarMapper.xml <select id="selectAllBymapUnderscoreToCameCase" resultType="Car"> select * from car </select> // 接口 public interface CarMapper{ // 查询所有Car信息, 启用驼峰命名自动映射机制 List<Car> selectAllBymapUnderscoreToCameCase(); } // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); CarMapper mapper = sqlSession.getMapper(CarMapper.class); List<Car> cars = mapper.selectAllBymapUnderscoreToCameCase(); cars.forEach(car -> System.out.println(car)); sqlSession.close(); }
7. 查询之返回总记录条数:
// CarMapper.xml // resultType后面也可以写上java.lang.Long <select id="selectTotal" resultType="long"> select count(*) from car </select> // 接口 public interface CarMapper{ // 获取Car的总记录条数 Long selectTotal(); } // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); CarMapper mapper = sqlSession.getMapper(CarMapper.class); Long nums = mapper.selectTotal(); System.out.println(nums); sqlSession.close(); }
