C : create增
R : retrieve检索
U : update改
D : delete删
1. insert增加操作:
- JDBC中的占位符是? , 而在mybatis中的占位符是#{}
1.1 使用map集合传参:
- java程序中使用Map可以给SQL语句中的占位符传值!
- #{这里面写什么?} , 大括号中写map集合的key.
- 如果大括号中的key不存在, 那么获取到的就是null
- map的key建议写的见名知意, 建议写成表的字段名.
// CarMapper.xml <insert id="insertCar"> // #{k1} -> 底层会调用map.get("k1") insert into car(id,car_num,brand,guide-price,produce_time,car-type) values (null, #{car_num}, #{brand}, #{guide_price}, #{produce_time}, #{car_type}) </insert> // @test public class main(String[] args){ SqlSession sqlSession = SqlSessionUtil.openSession(); Map<String,Object> map = new HashMap<>(); map.put("car_num",".."); map.put("brand",".."); map.put("guide_price",".."); map.put("produce_time",".."); map.put("car_type",".."); // insert方法的参数: // 第一个参数: sqlId, 从CarMapper.xml中复制过来 // 第二个参数: 封装数据的对象 int count = sqlSession.insert("insertCar",map); sqlsession.commit(); sqlsession.close(); }
1.2 使用POJO类传参:
- Mapper.xml中的#{}, 大括号里面写上pojo类的getXyz()方法的xyz
- mybatis在底层给#{}传值的时候, 需要先获取值. 通过反射机制调用getXxxx()方法获取到值
- getUserName() ---> #{userName}
// CarMapper.xml <insert id="insertCar"> insert into car (id,car_num,brand,guide_price,produce_time,car_type) values (null, #{carNum}, #{brand}, #{guidePrice}, #{produceTime}, #{carType}) </insert> /** * 封装汽车相关信息的 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; } // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); // 封装数据 Car car = new Car(null,"666","大奔",67.6,"2023-1-1","燃油"); // 执行sql int count = sqlSession.insert("insertCar",car); System.out.println(count); sqlSession.commit(); sqlSession.close(); }
2. delete删除操作:
// CarMapper.xml <delete id="deleteById"> <!-- 因为只有一个占位符, 所以 {里面写什么都行, 但是不能不写} --> delete from car where id=#{id} </delete> // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); int count = sqlSession.delete("deleteById",67); System.out.println(count); sqlSession.commit(); sqlSession.close(); }
3. update修改操作：
// CarMapper.xml <update id="updateById"> update car set car_num=#{carNum}, brand=#{brand}, guide_price=#{guidePrice}, produce_time=#{produceTime}, car_type=#{carType} where id=#{id} </update> // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); Car car = new Car(6L,"999","宝马",66,"2021-1-1","新能源"); int count = sqlSession.update("updateById",car); System.out.println(count); sqlSession.commit(); sqlSession.close(); }
4. select查询:
4.1 查询一个数据:
- 列名要和pojo类中的属性名要对的上, 否则查出来为null
- mybatis执行了select之后, 一定会返回一个结果集对象 ResultSet
- 接下来就是mybatis应该从ResultSet中取出数据, 封装java对象
// CarMapper.xml // 必须要指定返回结果的类型resultType <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> // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); // mybatis执行了select之后, 一定会返回一个结果集对象 ResultSet // 接下来就是mybatis应该从ResultSet中取出数据, 封装java对象 Object car = sqlSession.selectOne("selectById",12); System.out.println(car); sqlSession.close(); }
4.2 查询所有数据:
// CarMapper.xml // resultType还是指定要封装的结果集的类型, 不是指定List类型, 是指定List集合中元素的类型. <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> // @test public static void main(String[] args) { SqlSession sqlSession = SqlSessionUtil.openSession(); List<Object> cars = sqlSession.selectList("selectAll"); cars.forEach(car -> System.out.println(car)); sqlSession.close(); }
5. 关于Mapper文件的namespace:
- 在sql mapper.xml 文件中有一个namespace, 这个属性是用来指定命名空间的, 用来防止id重复.
- 实际上, mybatis中的sqlid的完整写法是: namespace.id
// CarMapper.xml <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybati s.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="..."> <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> </mapper>
