查询所有数据
首先在数据库里新建一个表
-- 删除tb_brand表
drop table if exists tb_brand;
-- 创建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);
IDEA添加插件mybatisx
在file setting里的plugins里搜索下载即可
然后创建对应的接口和映射文件，还有实体类brand
在test下的文件里建立对应的测试文件
注意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>
<typeAliases>
<package name="pojo"/>
</typeAliases>
<!--
environments：配置数据库连接环境信息。可以配置多个environment，通过default属性切换不同的environment
-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!--数据库连接信息-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="111111"/>
</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:///mybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="111111"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载sql映射文件
<mapper resource="mapper/UserMapper.xml"/>
<mapper resource="mapper/BrandMapper.xml"/>-->
<!--Mapper代理方式-->
<package name="mapper"/>
</mappers>
</configuration>
这种方法可以加载你该包下所有的映射文件，就不要一个个文件添加。
你的包含实体类的文件夹
brand.java
package pojo;
public class brand {
// id 主键
private Integer id;
// 品牌名称
private String brandName;
// 企业名称
private String companyName;
// 排序字段
private Integer ordered;
// 描述信息
private String description;
// 状态：0：禁用 1：启用
private Integer 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;
}
@Override
public String toString() {
return "brand{" +
"id=" + id +
", brandName='" + brandName + '\'' +
", companyName='" + companyName + '\'' +
", ordered=" + ordered +
", description='" + description + '\'' +
", status=" + status +
'}';
}
}
BrandMapper.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">
<!--
namespace:名称空间,必须是对应接口的全限定名
-->
<mapper namespace="mapper.BrandMapper">
<!--
数据库的姓名是brand_name,实体类的姓名是brandName。即数据库表的字段名称和实体类属性名称不一样，
则不能自动封装，需要手动封装。
* 起别名，但是每次查询都要起一次
* resultMap
-->
<resultMap id="brandResultMap" type="brand">
<!--
id：完成主键字段的映射
column：表的列名
property：实体类的属性名
result：完成一般字段的映射
column：表的列名
property：实体类的属性名
-->
<result column="brand_name" property="brandName"/>
<result column="company_name" property="companyName"/>
</resultMap>
<select id="selectAll" resultMap="brandResultMap">
select * from tb_brand;
</select>
<!--<select id="selectAll" resultType="pojo.brand">
select *
from tb_brand;
</select>-->
</mapper>
BrandMapper.java
```bash
package mapper;
import pojo.brand;
import java.util.List;
public interface BrandMapper {
//查询所有
List<brand> selectAll();
}
myBatisTest,java
package demoTest;
import mapper.BrandMapper;
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 org.junit.Test;
import pojo.brand;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class myBatisTest {
@Test
public void testSelectAll() throws IOException {
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
List<brand> brands = brandMapper.selectAll();
System.out.println(brands);
//5. 释放资源
sqlSession.close();
}
}
点击运行可以实现所有数据查询。
根据id进行查询
BrandMapper.java
//根据id进行查询
List<brand> selectById(int id);
BrandMapper.xml
<select id="selectById" resultMap="brandResultMap">
select * from tb_brand where id = #{id};
</select>
其中关于那个sql语句
参数占位符：
#{}
会将其替换为 ?，为了防止SQL注入，
${}
不常用，会存在参数注入问题
使用时机：
* 参数传递的时候：#{}
* 表名或者列名不固定的情况下：${} 会存在SQL注入问题
* 参数类型：parameterType：可以省略
* 特殊字符处理：
1. 转义字符：<(<)
2. CDATA区:
<select id="selectById" resultMap="brandResultMap">
select *
from tb_brand
where id
<![CDATA[
<
]]>
#{id};
</select>
myBatisTest.java
@Test
public void testSelectById() throws IOException {
int id = 1;
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
List<brand> brands = brandMapper.selectById(id);
System.out.println(brands);
//5. 释放资源
sqlSession.close();
}
条件查询
多条件满足查询
1、接口方法参数使用 @Param 方式调用的方法
BrandMapper.java
List<brand> selectByCondition(@Param("status") int status,
@Param("companyName") String companyName, @Param("brandName") String
brandName);
BrandMapper.xml
<!--条件查询-->
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
where status = #{status}
and company_name like #{companyName}
and brand_name like #{brandName}
</select>
myBatisTest.java
@Test
public void testSelectCondition() throws IOException {
//接收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
// 处理参数
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
List<brand> brands = brandMapper.selectByCondition(status,companyName,brandName);
System.out.println(brands);
//5. 释放资源
sqlSession.close();
}
运行结果
2、对象参数
List<brand> selectByCondition(brand brand);
public void testSelectCondition() throws IOException {
//接收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
// 处理参数
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
brand brand1 = new brand();
brand1.setStatus(status);
brand1.setCompanyName(companyName);
brand1.setBrandName(brandName);
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
List<brand> brands = brandMapper.selectByCondition(brand1);
System.out.println(brands);
//5. 释放资源
sqlSession.close();
}
3、使用map
List<brand> selectByCondition(Map map);
@Test
public void testSelectCondition() throws IOException {
//接收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
// 处理参数
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
Map map = new HashMap();
map.put("status" , status);
map.put("companyName", companyName);
map.put("brandName" , brandName);
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
List<brand> brands = brandMapper.selectByCondition(map);
System.out.println(brands);
//5. 释放资源
sqlSession.close();
}
多数据查询（动态）
上面得方法需要所有的数据都有值，如果有的为空是没有查询结果的。现在完成有的条件不填写时的查询。
sql会随着输入条件的变化而变化
MyBatis提供了动态sql
test 属性：逻辑表达式
where 标签
- 作用：
- 替换where关键字
- 会动态的去掉第一个条件前的 and
- 如果所有的参数没有值则不加where关键字
注意：需要给每个条件前都加上 and 关键字。
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
<where>
<if test="status != null">
and status = #{status}
</if>
<if test="companyName != null and companyName != '' ">
and company_name like #{companyName}
</if>
<if test="brandName != null and brandName != '' ">
and brand_name like #{brandName}
</if>
</where>
</select>
动态查询单个（类似下拉框选择一个查询）
- choose 相当于switch
- when 相当于case
<select id="selectByConditionSingle" resultMap="brandResultMap">
select *
from tb_brand
<where>
<choose><!--相当于switch-->
<when test="status != null"><!--相当于case-->
status = #{status}
</when>
<when test="companyName != null and companyName != '' "><!--相当于case-->
company_name like #{companyName}
</when>
<when test="brandName != null and brandName != ''"><!--相当于case-->
brand_name like #{brandName}
</when>
</choose>
</where>
</select>
添加
注意要提交事务，不然会自动回滚，数据库并不会改变
可以使用手动提交
sqlSession.commit();
或者使用自动提交
SqlSession sqlSession = sqlSessionFactory.openSession(true);
<insert id="add">
insert into tb_brand (brand_name, company_name, ordered, description, status)
values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
</insert>
/*添加数据*/
void add(brand brand);
@Test
public void testAdd() throws IOException {
//接收参数
int status = 1;
String companyName = "波导手机";
String brandName = "波导";
String description = "手机中的战斗机";
int ordered = 100;
//封装对象
brand brand1 = new brand();
brand1.setStatus(status);
brand1.setCompanyName(companyName);
brand1.setBrandName(brandName);
brand1.setDescription(description);
brand1.setOrdered(ordered);
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//SqlSession sqlSession = sqlSessionFactory.openSession(true); //设置自动提交事务，这种情况不需要手动提交事务了
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
brandMapper.add(brand1);
//提交事务
sqlSession.commit();
//5. 释放资源
sqlSession.close();
}
主键返回，获取你新增的数据的id
useGeneratedKeys=“true” keyProperty=“id”
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand (brand_name, company_name, ordered, description, status)
values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
</insert>
在最后对id进行输出
@Test
public void testAdd() throws IOException {
//接收参数
int status = 1;
String companyName = "波导手机";
String brandName = "波导";
String description = "手机中的战斗机";
int ordered = 100;
//封装对象
brand brand1 = new brand();
brand1.setStatus(status);
brand1.setCompanyName(companyName);
brand1.setBrandName(brandName);
brand1.setDescription(description);
brand1.setOrdered(ordered);
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//SqlSession sqlSession = sqlSessionFactory.openSession(true); //设置自动提交事务，这种情况不需要手动提交事务了
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
brandMapper.add(brand1);
//提交事务
sqlSession.commit();
//5. 释放资源
sqlSession.close();
System.out.println(brand1.getId());
}
修改
根据id进行对应数据的修改
set 标签可以用于动态包含需要更新的列，忽略其它不更新的列
<update id="update">
update tb_brand
<set>
<if test="brandName != null and brandName != ''">
brand_name = #{brandName},
</if>
<if test="companyName != null and companyName != ''">
company_name = #{companyName},
</if>
<if test="ordered != null">
ordered = #{ordered},
</if>
<if test="description != null and description != ''">
description = #{description},
</if>
<if test="status != null">
status = #{status}
</if>
</set>
where id = #{id};
</update>
/*修改数据*/
void update(brand brand);
@Test
public void testUpdate() throws IOException {
//接收参数
int status = 1;
String companyName = "手机12138";
String brandName = "波导12138";
String description = "手机中的战斗机";
int ordered = 100;
int id = 5;
//封装对象
brand brand1 = new brand();
brand1.setStatus(status);
brand1.setCompanyName(companyName);
brand1.setBrandName(brandName);
// brand1.setDescription(description);
// brand1.setOrdered(ordered);
brand1.setId(id);
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//SqlSession sqlSession = sqlSessionFactory.openSession(true); //设置自动提交事务，这种情况不需要手动提交事务了
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
brandMapper.update(brand1);
//提交事务
sqlSession.commit();
//5. 释放资源
sqlSession.close();
}
删除
删除一行数据
<delete id="deleteById">
delete from tb_brand where id = #{id};
</delete>
别的都是大同小异的调用方法
删除多行数据
编写SQL时需要遍历数组来拼接SQL语句。Mybatis 提供了 foreach 标签供我们使用
foreach 标签
用来迭代任何可迭代的对象（如数组，集合）。
- collection 属性：
- mybatis会将数组参数，封装为一个Map集合。
- 默认：array = 数组
- 使用@Param注解改变map集合的默认key的名称
- item 属性：本次迭代获取到的元素。
- separator 属性：集合项迭代之间的分隔符。foreach 标签不会错误地添加多余的分隔符。也就是最后一次迭代不会加分隔符。
- open 属性：该属性值是在拼接SQL语句之前拼接的语句，只会拼接一次
- close 属性：该属性值是在拼接SQL语句拼接后拼接的语句，只会拼接一次
如果这里使用array，那么
可以不使用@param
如果这里使用名称，即ids，那么要使用@param
/*根据id删除多行数据*/
void deleteByIds(int ids[]);
------------------------------------------------------
<delete id="deleteByIds">
delete from tb_brand where id in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
/*根据id删除多行数据*/
void deleteByIds(@Param("ids") int ids[]);
-------------------------------------------------------
<delete id="deleteByIds">
delete from tb_brand where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
635
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
