使用mybatis完成CRUD
C create
R retrieve查(检索)
U Update改
D Delete删
insert
<!--insert语句,id是这条SQL语句的唯一标识-->
<insert id="insertCar">
insert into t_car(id,car_num,brand,guide_price,produce_time,car_type)
values(null ,'1003','丰田霸道',30,'2000-10-11','燃油车')
</insert>
存在问题:值写死到了配置文件中。应该是前端的form表单提交过来数据,然后将值传给sql语句。
修改过后:
<insert id="insertCar">
insert into t_car(id,car_num,brand,guide_price,produce_time,car_type)
values(null ,#{carNum},#{brand},#{guidePrice},#{produceTime},#{carType})
</insert>
使用Map集合传参
测试程序
@Test
public void testInsertCar(){
SqlSession sqlSession= SqlSessionUtil.openSession();
//这个对象我们先使用Map集合进行数据的封装。
Map<String,Object>map=new HashMap<>();
map.put("carNum","1111");
map.put("brand","比亚迪汗");
map.put("guidePrice",10);
map.put("produceTime","2020-11-11");
map.put("carType","电车");
//执行sql语句
//insert方法的参数:
//第一个参数:sqlId,从CarMapper.xml文件中复制
//第二个参数:封装数据的对象
int count=sqlSession.insert("insertCar",map);
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
使用POJO传参
Car.java
package mybatis.pojos;
/**
* 封装汽车相关信息的pojo类,普通的java类
*/
public class Car {
//数据库表当中的字段应该和pojo类的属性一一对应
//建议使用包装类,这样可以防止Null的问题
private Long id;
private String carNum;
private String brand;
private Double guidePrice;
private String produceTime;
private String carType;
@Override
public String toString() {
return "Car{" +
"id=" + id +
", carNum='" + carNum + '\'' +
", brand='" + brand + '\'' +
", guidePrice=" + guidePrice +
", produceTime='" + produceTime + '\'' +
", carType='" + carType + '\'' +
'}';
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
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;
}
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 Car() {
}
}
CarMapper.xml
<insert id="insertCar">
insert into t_car(id,car_num,brand,guide_price,produce_time,car_type)
-- #{}内写POJO类的属性名
values(null ,#{carNum},#{brand},#{guidePrice},#{produceTime},#{carType})
</insert>
</mapper>
测试程序
@Test
public void testInsertCarByPOJO(){
SqlSession sqlSession= SqlSessionUtil.openSession();
//封装数据
Car car=new Car(null,"3333","比亚泰迪",30.0,"2020-11-20","新能源");
//执行sql
int count=sqlSession.insert("insertCar",car);
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
delete
需求:根据ID删除数据
将id=6的数据删除
Update修改
根据Id修改某条记录
select 查询(查一个—根据主键查询)
<select id="selectById" resultType="mybatis.pojos.Car">
select * from t_car where id=#{id}
</select>
Object car=sqlSession.selectOne("selectById",1);
注意:resultType属性用来告诉mybatis查询结果集封装成什么类型的java对象
resultType通常写全限定类名(后期在mybatis-config.xml配置一下,就不用写全限定类名了)
正确写法:
<select id="selectById" resultType="mybatis.pojos.Car">
<!--select * from t_car where id=#{id}-->
select
id,car_num as carNum,
brand,
guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
from
t_car
where
id=#{id}
</select>
select(查所有)
<select id="selectAll" resultType="mybatis.pojos.Car">
select
id,car_num as carNum,brand,guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
from
t_car
</select>
@Test
public void testSelectAll(){
SqlSession sqlSession=SqlSessionUtil.openSession();
//执行SQL语句
List<Object> cars = sqlSession.selectList("selectAll");
//遍历
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}
注意:resultType还是指定要封装的结果集的类型,不是指定List类型,是指定List集合中元素的类型。
namespace
在sql mapper.xml文件中有一个namespace ,这个属性是用来指定命名空间的,用来防止id重复。
在xml文件中:
<mapper namespace="asd">
<select id="selectAll" resultType="mybatis.pojos.Car">
select
id,car_num as carNum,brand,guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
from
t_car
</select>
</mapper>
在java程序中的写法:
List<Object> cars = sqlSession.selectList("asd.selectAll");
完整程序
(jdbc.properties先不管它,是下一章的内容了)
Car
package mybatis.pojos;
/**
* 封装汽车相关信息的pojo类,普通的java类
*/
public class Car {
//数据库表当中的字段应该和pojo类的属性一一对应
//建议使用包装类,这样可以防止Null的问题
private Long id;
private String carNum;
private String brand;
private Double guidePrice;
private String produceTime;
private String carType;
@Override
public String toString() {
return "Car{" +
"id=" + id +
", carNum='" + carNum + '\'' +
", brand='" + brand + '\'' +
", guidePrice=" + guidePrice +
", produceTime='" + produceTime + '\'' +
", carType='" + carType + '\'' +
'}';
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
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;
}
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 Car() {
}
}
SqlSessionUtil
package mybatis.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
/**
* mybatis工具类
*/
public class SqlSessionUtil {
private static SqlSessionFactory sqlSessionFactory;
//工具类的构造方法是私有化的
//工具类中所有方法都是静态的,直接采用类名即可调用,不需要new对象
//为了防止new对象,构造方法私有化
private SqlSessionUtil(){}
//类加载时执行
//SqlSessionUtil工具类在进行第一次加载的时候,解析mybatis-config.xml文件,创建SqlSessionFactory对象
static{
try {
sqlSessionFactory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取会话对象
* @return 会话对象
*/
public static SqlSession openSession(){
return sqlSessionFactory.openSession();
}
}
carMapper.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="org.mybatis.example.BlogMapper">
<select id="selectAll" resultType="mybatis.pojos.Car">
select
id,car_num as carNum,brand,guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
from
t_car
</select>
<select id="selectById" resultType="mybatis.pojos.Car">
<!--select * from t_car where id=#{id}-->
select
id,car_num as carNum,brand,guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
from
t_car
where
id=#{id}
</select>
<update id="updateById">
update t_car set
car_num=#{carNum},
brand=#{brand},
guide_price=#{guidePrice},
produce_time=#{produceTime},
car_type=#{carType}
where
id=#{id}
</update>
<delete id="deleteById">
delete from t_car where id=#{Id}
</delete>
<!--insert语句,id是这条SQL语句的唯一标识-->
<insert id="insertCar">
insert into t_car(id,car_num,brand,guide_price,produce_time,car_type)
-- #{}内些POJO类的属性名
values(null ,#{carNum},#{brand},#{guidePrice},#{produceTime},#{carType})
</insert>
</mapper>
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">
<configuration>
<!--开启mybatis对标准日志的实现-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/powernode"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 执行xxxMapper.xml文件的路径-->
<mapper resource="carMapper.xml"/>
</mappers>
</configuration>
UserMapper.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="asd">
<select id="selectAll" resultType="mybatis.pojos.Car">
select
id,car_num as carNum,brand,guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
from
t_car
</select>
</mapper>
CarMapperTest
package mybatis.test;
import mybatis.pojos.Car;
import mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class CarMapperTest {
@Test
public void testNamespace(){
SqlSession sqlSession=SqlSessionUtil.openSession();
// List<Object> cars = sqlSession.selectList("selectAll");
//正确完整的写法:namespace+id
List<Object> cars = sqlSession.selectList("asd.selectAll");
cars.forEach(car-> System.out.println(car));
sqlSession.close();
}
@Test
public void testSelectAll(){
SqlSession sqlSession=SqlSessionUtil.openSession();
//执行SQL语句
List<Object> cars = sqlSession.selectList("selectAll");
//遍历
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}
@Test
public void testSelectById(){
SqlSession sqlSession=SqlSessionUtil.openSession();
//执行SQL查询,根据id查询,返回结果一定是一条。
//mybatis底层执行了select语句之后,一定会返回一个结果集对象:ResultSet
//JDBC中叫做ResultSet,接下来就是mybatis应该从ResultSet中取出数据,封装java对象。
Object car= sqlSession.selectOne("selectById", 1);
System.out.println(car);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testUpdateById(){
SqlSession sqlSession=SqlSessionUtil.openSession();
//准备数据
Car car=new Car(10L,"9999","凯美瑞",30.3,"1999-11-10","燃油车");
//执行SQL语句
int count=sqlSession.update("updateById",car);
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testDeleteById(){
SqlSession sqlSession=SqlSessionUtil.openSession();
//执行SQL语句
int count=sqlSession.delete("deleteById",6);
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testInsertCarByPOJO(){
SqlSession sqlSession= SqlSessionUtil.openSession();
//封装数据
Car car=new Car(null,"3333","比亚泰迪",30.0,"2020-11-20","新能源");
//执行sql
int count=sqlSession.insert("insertCar",car);
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
@Test
public void testInsertCar(){
SqlSession sqlSession= SqlSessionUtil.openSession();
//这个对象我们先使用Map集合进行数据的封装。
Map<String,Object>map=new HashMap<>();
map.put("k1","1111");
map.put("k2","比亚迪汗");
map.put("k3",10);
map.put("k4","2020-11-11");
map.put("k5","电车");
//执行sql语句
//insert方法的参数:
//第一个参数:sqlId,从CarMapper.xml文件中复制
//第二个参数:封装数据的对象
int count=sqlSession.insert("insertCar",map);
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
}