mybatis练习

mybatis练习

一.环境准备

1.数据库实始化

-- 创建tb_brand表 create table tb_brand ( /*id 主键*/id int primary key auto_increment,/* 品牌名称*/brand_name varchar(20), /* 企业名称 */company_name varchar(20), /* 排序字段*/ ordered int,/* 描述信息*/ description varchar(100), /* 状态:0:禁用 1:启用*/ status int );-- 添加数据 insert into tb_brand (brand_name, company_name, ordered, description, status) values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0), ('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织,构建万物互联的智能世界', 1), ('小米', '小米科技有限公司', 50, 'are you ok', 1);

2.mybatis.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--根元素-->
<configuration><!--加载外部配置文件--><properties resource="jdbc.properties"></properties><!--开启驼峰映射:把数据表中的带有下划线的字段,变为java的驼峰命名方式。user_name->username userName--><settings><setting name="mapUnderscoreToCamelCase" value="true"/></settings><!--设置别名--><typeAliases><!--扫描指定的名,所包下的所有的类,都起了别名,别名格式:全部小写或驼峰命名--><package name="com.itheima.pojo"/><!--        <typeAlias type="com.itheima.pojo.User" alias="User"></typeAlias>--></typeAliases><!--配置数据源(数据库连接)--><environments default="dev"><!--开发环境--><environment id="dev"><!--事务管理器:按照jdbc默信的事务--><transactionManager type="JDBC"/><!--数据源(数据库连接池) mybatis有自带数据库连接池--><dataSource type="POOLED"><!--连接数据库需要的4个参数:驱动、URL、登录名、密码--><property name="driver" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></dataSource></environment><!--测试环境--><environment id="test"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="com.mysql.jdbc.Driver"/><property name="url" value="jdbc:mysql://localhost:3306/hsp_db02?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false"/><property name="username" value="root"/><property name="password" value="123456"/></dataSource></environment></environments><!--映射映射相关文件--><mappers><!--方案1:指定关联的映射文件,通过映射文件在关联mapper接口--><!--        <mapper resource="com/itheima/dao/UserMapper.xml"/>--><!--方案1:指定关联的mapper接口(通过mapper接口关联映射文件)要求:1.映射文件名和mapper接口必须相同2.映射文件和mapper接口存储在相同的路径下--><!--指定要扫描的所有mapper接口的包路径--><package name="com.itheima.dao"/></mappers>
</configuration>

3.pojo类

public class Brand {private Integer id;private String brandName;private String companyName;private Integer ordered;private String description;private Integer status;public Brand() {}public Brand(Integer id, String brandName, String companyName, Integer ordered, String description, Integer status) {this.id = id;this.brandName = brandName;this.companyName = companyName;this.ordered = ordered;this.description = description;this.status = status;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getBrandName() {return brandName;}public void setBrandName(String brandName) {this.brandName = brandName;}public String getCompanyName() {return companyName;}public void setCompanyName(String companyName) {this.companyName = companyName;}public Integer getOrdered() {return ordered;}public void setOrdered(Integer ordered) {this.ordered = ordered;}public String getDescription() {return description;}public void setDescription(String description) {this.description = description;}public Integer getStatus() {return status;}public void setStatus(Integer status) {this.status = status;}@Overridepublic String toString() {return "BrandDao{" +"id=" + id +", brandName='" + brandName + '\'' +", companyName='" + companyName + '\'' +", ordered=" + ordered +", description='" + description + '\'' +", status=" + status +'}';}
}

4.BrandMapper接口

package com.itheima.dao;import com.itheima.pojo.Brand;import java.util.List;public interface BrandMapper {/*** 查询所有品牌* @return*/public List<Brand> findAllBrand();
}

5.BrandMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--利用namespace属性,绑定当前映射文件和mapper接口映射关联-->
<mapper namespace="com.itheima.dao.BrandMapper"><!--使用resultMap解决查询结果中的字段名和java实体类中不一致问题--><resultMap id="brandMap" type="com.itheima.pojo.Brand" autoMapping="true"><!--id标签:配置数据表的主键和实体类中的属性映射--><id column="id" property="id"></id><!--result标签:配置数据表中非主键字段和实体类的属性映射--><result column="brand_name" property="brandName"></result><result column="company_name" property="companyName"></result></resultMap><sql id="brandFields">id,brand_name,company_name,ordered,description,status</sql><select id="findAllBrand" resultMap="brandMap">select <include refid="brandFields"/> from tb_brand</select><!--根据id查询一行数据--><select id="findBrandById" resultType="com.itheima.pojo.Brand" parameterType="int">SELECTid,brand_name as brandName,company_name as companyName,ordered,description,statusFROM tb_brandwhere id=#{id}</select></mapper>

6.测试代码

public class MybatisTest {@Testpublic void testFindAllBrand(){//使用SqlSessionFactory工厂类,创建SqlSessionSqlSession sqlSession = MybatisUtil.openSession();//利用sqlSession,创建一个代理对象BrandMapper sessionMapper = sqlSession.getMapper(BrandMapper.class);//使用代理对象和方法查询用户数据List<Brand> brandList = sessionMapper.findAllBrand();//释放资源MybatisUtil.closeSqlSession(sqlSession);//测试用户数据for (Brand brand:brandList){System.out.println(brand);}}
}

二.mybatis占位符

参数占位符详解:

mybatis提供了两种参数占位符:

${key}:在核心配置文件中获取外部配置文件中的数据;在映射文件中做为sql的占位符参数使用(注意细节:需要使用@Param(“参数”))

#{key}:在映射文件中做为sql的占位符参数使用

#{key}和${key}的区别

#{}:底层使用的是 PreparedStatement 对sql进行预编译,使用占位符。好处:防止SQL注入

2024-01-15 17:57:11.300 [DEBUG]  com.itheima.dao.BrandMapper.findBrandById [main] : ==>  Preparing: SELECT id, brand_name as brandName, company_name as companyName, ordered, description, status FROM tb_brand where id=? 
2024-01-15 17:57:11.398 [DEBUG]  com.itheima.dao.BrandMapper.findBrandById [main] : ==> Parameters: 3(Integer)
2024-01-15 17:57:11.437 [TRACE]  com.itheima.dao.BrandMapper.findBrandById [main] : <==    Columns: id, brandName, companyName, ordered, description, status
2024-01-15 17:57:11.437 [TRACE]  com.itheima.dao.BrandMapper.findBrandById [main] : <==        Row: 3, 小米, 小米科技有限公司, 50, are you ok, 1
2024-01-15 17:57:11.441 [DEBUG]  com.itheima.dao.BrandMapper.findBrandById [main] : <==      Total: 1

${} :底层使用的是 Statement,拼接SQL,会存在SQL注入问题

2024-01-15 17:58:37.816 [DEBUG]  com.itheima.dao.BrandMapper.findBrandById [main] : ==>  Preparing: SELECT id, brand_name as brandName, company_name as companyName, ordered, description, status FROM tb_brand where id=3 
2024-01-15 17:58:37.922 [DEBUG]  com.itheima.dao.BrandMapper.findBrandById [main] : ==> Parameters: 
2024-01-15 17:58:37.966 [TRACE]  com.itheima.dao.BrandMapper.findBrandById [main] : <==    Columns: id, brandName, companyName, ordered, description, status
2024-01-15 17:58:37.968 [TRACE]  com.itheima.dao.BrandMapper.findBrandById [main] : <==        Row: 3, 小米, 小米科技有限公司, 50, are you ok, 1
2024-01-15 17:58:37.976 [DEBUG]  com.itheima.dao.BrandMapper.findBrandById [main] : <==      Total: 1

三.条件查询

1.-Map集合作为SQL参数

brandMapper.xml

<!--根据条件查询品牌数据-->
<select id="findBrandByCondition" resultMap="brandMap">SELECTid,brand_name,company_name,ordered,description,statusFROM tb_brandWHERE brand_name=#{brandName}ANDcompany_name=#{companyName}ANDstatus=#{status}
</select>
<select id="findBrandByCondition2" resultMap="brandMap">SELECTid,brand_name,company_name,ordered,description,statusFROM tb_brandWHERE brand_name=#{brandName}ANDcompany_name=#{companyName}ANDstatus=#{status}
</select>
<select id="findBrandByCondition3" resultMap="brandMap">SELECTid,brand_name,company_name,ordered,description,statusFROM tb_brandWHERE brand_name=#{brandname}ANDcompany_name=#{companyname}ANDstatus=#{status}
</select>

brandMapper接口

/*** 根据条件,查询品牌数据* @param brandName  品牌名称* @param companyName  公司名称* @param status    状态* @return 返回的数据*/
//当方法中传递的参数有2个或以上时,需要使用@Param注解声明占位符参数
public List<Brand> findBrandByCondition(@Param("brandName") String brandName,@Param("companyName") String companyName,@Param("status") int status);public List<Brand> findBrandByCondition2(Brand brand);
public List<Brand> findBrandByCondition3(Map paramMap);//mpa[key=value]

测试代码

@Test
public void testFindBrandByCondition3(){//使用SqlSessionFactory工厂类,创建SqlSessionSqlSession sqlSession = MybatisUtil.openSession();//利用sqlSession,创建一个代理对象BrandMapper sessionMapper = sqlSession.getMapper(BrandMapper.class);//使用代理对象和方法查询用户数据Map map=new HashMap();map.put("brandname","华为");map.put("companyname","华为技术有限公司");map.put("status",1);List<Brand> brandList = sessionMapper.findBrandByCondition3(map);//释放资源MybatisUtil.closeSqlSession(sqlSession);for (Brand brand2:brandList){System.out.println(brand2);}
}
@Test
public void testFindBrandByCondition2(){//使用SqlSessionFactory工厂类,创建SqlSessionSqlSession sqlSession = MybatisUtil.openSession();//利用sqlSession,创建一个代理对象BrandMapper sessionMapper = sqlSession.getMapper(BrandMapper.class);//使用代理对象和方法查询用户数据Brand brand=new Brand();brand.setBrandName("华为");brand.setCompanyName("华为技术有限公司");brand.setStatus(1);List<Brand> brandList = sessionMapper.findBrandByCondition2(brand);//释放资源MybatisUtil.closeSqlSession(sqlSession);for (Brand brand2:brandList){System.out.println(brand2);}
}
@Test
public void testFindBrandByCondition(){//使用SqlSessionFactory工厂类,创建SqlSessionSqlSession sqlSession = MybatisUtil.openSession();//利用sqlSession,创建一个代理对象BrandMapper sessionMapper = sqlSession.getMapper(BrandMapper.class);//使用代理对象和方法查询用户数据List<Brand> brandList = sessionMapper.findBrandByCondition("华为","华为技术有限公司",1);//释放资源MybatisUtil.closeSqlSession(sqlSession);for (Brand brand:brandList){System.out.println(brand);}
}

2.动态SQL

2.1 if
<!--根据条件查询品牌数据-->
<select id="findBrandByCondition" resultMap="brandMap">SELECTid,brand_name,company_name,ordered,description,statusFROM tb_brandWHERE 1=1<if test="brandName !=null ">AND brand_name=#{brandName}</if><if test="companyName !=null ">AND company_name=#{companyName}</if><if test="status !=null ">AND status=#{status}</if>
</select>
2.2 where

where标签:代替sql语句中的where关键字可以自动去除sql语句中的where 条件里面多余的or或and

<select id="findBrandByCondition2" resultMap="brandMap">SELECTid,brand_name,company_name,ordered,description,statusFROM tb_brand<where><if test="brandName !=null ">AND brand_name=#{brandName}</if><if test="companyName !=null ">AND company_name=#{companyName}</if><if test="status !=null ">AND status=#{status}</if></where>
</select>
2.3 choose
<select id="findBrandByCondition3" resultMap="brandMap">SELECTid,brand_name,company_name,ordered,description,statusFROM tb_brand<where><choose><when test="brandname!=null">brand_name=#{brandname}</when><when test="companyname!=null">company_name=#{companyname}</when><when test="status!=null">status=#{status}</when></choose></where>
</select>
2.4添加

xml:

<insert id="addBrand" parameterType="com.itheima.pojo.Brand">insert into tb_brand (brand_name, company_name, ordered, description, status)values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status})
</insert>

接口:

/*** 添加品牌* @param brand 品牌对象* @return 受影响的行数*/
public  int addBrand(Brand brand);

测试代码:

@Test
public void testAddBrand(){//使用SqlSessionFactory工厂类,创建SqlSessionSqlSession sqlSession = MybatisUtil.openSession();//利用sqlSession,创建一个代理对象BrandMapper sessionMapper = sqlSession.getMapper(BrandMapper.class);Brand brand=new Brand();brand.setStatus(1);brand.setBrandName("测试1");brand.setCompanyName("测试1");brand.setDescription("测试1");brand.setOrdered(5);//插入数据int rowCount=sessionMapper.addBrand(brand);if(rowCount>0){System.out.println("插入数据成功");}//释放资源MybatisUtil.closeSqlSession(sqlSession);
}
2.5添加-主键返回

在mybatis的insert操作过程中,存在一各路扶桑 术:主键回填,在向表中插入 一行记录后,会自动把当前行的主键列下的数据值获取

2.5.1SQL
CREATE TABLE tb_order(id int primary key auto_increment,payment double,payment_type int,status int);create table tb_order_item
(id int primary key auto_increment,goods_name varchar(20),goods_price double,count varchar(32),order_id int
);insert into tb_order(payment,payment_type,status)values(4887,1,2),(1975,1,1);insert into tb_order_item(goods_name,goods_price,count,order_id)values('华为手机',3888,1,1),('华为Pad',999,1,1),('小米手机',888,2,2),('小米手环',199,1,2);
2.5.2pojo
package com.itheima.pojo;public class Order {private Integer id;private Double payment;private Integer paymentType;private Integer status;public Order() {}public Order(Integer id, Double payment, Integer paymentType, Integer status) {this.id = id;this.payment = payment;this.paymentType = paymentType;this.status = status;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public Double getPayment() {return payment;}public void setPayment(Double payment) {this.payment = payment;}public Integer getPaymentType() {return paymentType;}public void setPaymentType(Integer paymentType) {this.paymentType = paymentType;}public Integer getStatus() {return status;}public void setStatus(Integer status) {this.status = status;}@Overridepublic String toString() {return "Order{" +"id=" + id +", payment=" + payment +", paymentType=" + paymentType +", status=" + status +'}';}
}
package com.itheima.pojo;public class OrderItem {private Integer id;private String goodsName;private double goodsPrice;private String count;private Integer orderId;public OrderItem() {}public OrderItem(Integer id, String goodsName, double goodsPrice, String count, Integer orderId) {this.id = id;this.goodsName = goodsName;this.goodsPrice = goodsPrice;this.count = count;this.orderId = orderId;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getGoodsName() {return goodsName;}public void setGoodsName(String goodsName) {this.goodsName = goodsName;}public double getGoodsPrice() {return goodsPrice;}public void setGoodsPrice(double goodsPrice) {this.goodsPrice = goodsPrice;}public String getCount() {return count;}public void setCount(String count) {this.count = count;}public Integer getOrderId() {return orderId;}public void setOrderId(Integer orderId) {this.orderId = orderId;}@Overridepublic String toString() {return "OrderItem{" +"id=" + id +", goodsName='" + goodsName + '\'' +", goodsPrice=" + goodsPrice +", count='" + count + '\'' +", orderId=" + orderId +'}';}
}
2.5.3orderMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--利用namespace属性,绑定当前映射文件和mapper接口映射关联-->
<mapper namespace="com.itheima.dao.OrderMapper"><!--添加订单--><insert id="addOrder" parameterType="com.itheima.pojo.Order" useGeneratedKeys="true" keyProperty="id">insert into tb_order(payment,payment_type,status)values(#{payment},#{paymentType},#{status})</insert><!--添加订单明细--><insert id="addOrderItem" parameterType="com.itheima.pojo.OrderItem">insert into tb_order_item(goods_name,goods_price,count,order_id)values(#{goodsName},#{goodsPrice},#{count},#{orderId})</insert>
</mapper>

2.5.4orderMapper接口

package com.itheima.dao;import com.itheima.pojo.Order;
import com.itheima.pojo.OrderItem;public interface OrderMapper {/*** 添加订单* @param order 订单对象* @return 返行行数*/public int addOrder(Order order);/*** 添加订单明细* @param orderItem 订单明细对象* @return 返回行数*/public int addOrderItem(OrderItem orderItem);
}

测试代码:

package com.itheima.test;import com.itheima.dao.BrandMapper;
import com.itheima.dao.OrderMapper;
import com.itheima.pojo.Order;
import com.itheima.pojo.OrderItem;
import com.itheima.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;public class OrderMapperTest {@Testpublic void TestAddOrder(){//使用SqlSessionFactory工厂类,创建SqlSessionSqlSession sqlSession = MybatisUtil.openSession();//利用sqlSession,创建一个代理对象OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);Order order=new Order();order.setPayment(10000.0);order.setPaymentType(2); //2表示支付宝order.setStatus(1);int rowCount = orderMapper.addOrder(order);//订单对象OrderItem orderItem=new OrderItem();orderItem.setOrderId(order.getId());orderItem.setGoodsName("小米");orderItem.setGoodsPrice(1000);orderItem.setCount("10");int count2=orderMapper.addOrderItem(orderItem);if(rowCount>0 && count2>0){sqlSession.commit();System.out.println("插入数据成功");}//释放资源MybatisUtil.closeSqlSession(sqlSession);}
}
2.6修改所有字段

brandMapper.xml

    <update id="updateBrand">update tb_brandset brand_name=#{brandName},company_name=#{companyName},ordered=#{ordered},description=#{description},status=#{status}where id=#{id}</update>
2.7修改部份数据

brandMapper.xml

<!--修改数据-->
<update id="updateBrand">update tb_brand<set><if test="brandName!=null">brand_name=#{brandName}</if><if test="companyName!=null">company_name=#{companyName}</if><if test="ordered!=null">ordered=#{ordered},</if><if test="description!=null">description=#{description}</if><if test="status!=null">status=#{status}</if></set>where id=#{id}
</update>

brandMapper接口

/*** 修改品牌* @param brand 品牌对象* @return  受影响的行数*/
public int updateBrand(Brand brand);

测试代码

@Test
public void testUpdateBrand(){//使用SqlSessionFactory工厂类,创建SqlSessionSqlSession sqlSession = MybatisUtil.openSession();//利用sqlSession,创建一个代理对象BrandMapper sessionMapper = sqlSession.getMapper(BrandMapper.class);Brand brand=new Brand();brand.setStatus(1);brand.setBrandName("苹果");brand.setCompanyName("苹果股份");brand.setDescription("好用");brand.setOrdered(9);brand.setId(3);//插入数据int rowCount=sessionMapper.updateBrand(brand);if(rowCount>0){sqlSession.commit();System.out.println("修改数据成功");}//释放资源MybatisUtil.closeSqlSession(sqlSession);
}
2.8批量删除-动态SQL

brandMapper.xml

<delete id="deleteByIds">delete from tb_brand where idin(<foreach collection="ids" item="id" separator=",">#{id}</foreach>);
</delete>

brandMapper接口

/*** 批量删除品牌* @param ids* @return*/
public int deleteByIds(@Param("ids") List<Integer>ids);

测试代码

@Test
public void testDeleteBrandByIds(){//使用SqlSessionFactory工厂类,创建SqlSessionSqlSession sqlSession = MybatisUtil.openSession();//利用sqlSession,创建一个代理对象BrandMapper sessionMapper = sqlSession.getMapper(BrandMapper.class);List<Integer> ids=new ArrayList<>();Collections.addAll(ids,1,2,3);int count=sessionMapper.deleteByIds(ids);if(count>0){sqlSession.commit();System.out.println("删除成功");}//释放资源MybatisUtil.closeSqlSession(sqlSession);
}