package Test;
import Test.pojo.User;
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;
import java.io.InputStream;
import java.util.List;
/**
* @author 杨世博
*/
public class MybatisDemo {
public static void main(String[] args) throws IOException {
// 1、加载Mybatis的核心配置文件,获取SqlSessionFactory;
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2、获取SqlSession对象,用它来执行sql;
SqlSession sqlSession = sqlSessionFactory.openSession();
// 3、执行Sql语句
List<User> users = sqlSession.selectList("test.selectAll");
System.out.println(users);
// 4、释放资源
sqlSession.close();
}
}
package Test;
import Test.mapper.AccountMapper;
import Test.pojo.User;
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;
import java.io.InputStream;
import java.util.List;
/**
* @author 杨世博
*
* Mapper代理开发
*/
public class MybatisDemo2 {
public static void main(String[] args) throws IOException {
// 1、加载Mybatis的核心配置文件,获取SqlSessionFactory;
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2、获取SqlSession对象,用它来执行sql;
SqlSession sqlSession = sqlSessionFactory.openSession();
// 3、执行Sql语句
// List<User> users = sqlSession.selectList("test.selectAll");
// 3、1 获取 UserMapper 接口的代理对象
AccountMapper accountMapper = sqlSession.getMapper(AccountMapper.class);
List<User> users = accountMapper.selectAll();
System.out.println(users);
// 4、释放资源
sqlSession.close();
}
}
<?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="test.mapper.BrandMapper">
<!--
数据库表中的字段名称 和 实体类的属性名称 不一样,则不能自动封装数据
* 起别名:对不一样的列名起别名,让别名和实体类的属性名一样
* 缺点:每次查询都要定义一次别名
* sql片段
* 缺点:不灵活
* resultMap;
1、定义<resultMap>标签
2、在<select>标签中,使用resultMap属性替换 resultType属性
-->
<!--
id:唯一标识
type:映射类型,支持别名
-->
<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>
<!--
* 参数占位符
1、#{}:会将其替换为?,为了防止SQL注入
2、${}:拼sql。会存在sql注入问题
3、使用时机:
* 参数传递时:#{}
* 表名或者列名不固定的情况下:${}
* 参数类型:
parameterType:可以省略
* 特殊字符的处理:
1、转义字符:
2、CDATA区:
-->
<select id="selectById" resultMap="brandResultMap">
select *
from tb_brand where id = #{id};
</select>
<!-- 起别名-->
<!-- <select id="selectAll" resultType="Brand">-->
<!-- select id, brand_name as brandName, company_name as companyName, ordered, description, status-->
<!-- from tb_brand;-->
<!-- </select>-->
<!-- 普通-->
<!-- <select id="selectAll" resultType="Brand">-->
<!-- select * from tb_brand;-->
<!-- </select>-->
</mapper>
package test.mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import test.pojo.Brand;
import java.util.List;
import java.util.Map;
/**
* @author 杨世博
*/
public interface BrandMapper {
/**
* 查询Brand对象
* @return 返回查到的对象
*/
List<Brand> selectAll();
/**
* 查看详细:根据Id查询 得到Brand对象
* @param id 信息
* @return 返回查询到的对象
*/
@Select(value = "SELECT * FROM tb_brand WHERE id = #{id}")
Brand selectById(@Param("id") int id);
/**
* 条件查询
* * 参数接收
* 散装参数:如果方法中有多个参数,需要使用@Param("SQL参数占位符名称")
* @param status
* @param companyName
* @param brandName
* @return
*/
List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
/**
* 条件查询
* * 参数接收
* 对象参数:对象的属性名称要和参数占位符名称一致
* @param brand
* @return
*/
List<Brand> selectByCondition(Brand brand);
/**
* 条件查询
* * 参数接收
* map集合参数
* @param map
* @return
*/
List<Brand> selectByCondition(Map map);
/**
* 单条件动态查询Brand对象
* @param brand 传入随机参数
* @return 返回多个 Brand 对象
*/
List<Brand> selectByConditionSingle(Brand brand);
/**
* 添加 Brand 对象
* @param brand 对象的信息
*/
void add(Brand brand);
/**
* 修改 Brand对象
* @param brand
* @return
*/
int update(Brand brand);
/**
* 通过Id删除数据库中的Brand
* @param id
*/
void deleteById(Integer id);
/**
* 批量删除
* @param ids 要删除的id
*/
void deleteByIds(@Param("ids")int[] ids);
}
<?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="test.mapper.BrandMapper">
<!--
数据库表中的字段名称 和 实体类的属性名称 不一样,则不能自动封装数据
* 起别名:对不一样的列名起别名,让别名和实体类的属性名一样
* 缺点:每次查询都要定义一次别名
* sql片段
* 缺点:不灵活
* resultMap;
1、定义<resultMap>标签
2、在<select>标签中,使用resultMap属性替换 resultType属性
-->
<!--
id:唯一标识
type:映射类型,支持别名
-->
<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>
<!--
* 参数占位符
1、#{}:会将其替换为?,为了防止SQL注入
2、${}:拼sql。会存在sql注入问题
3、使用时机:
* 参数传递时:#{}
* 表名或者列名不固定的情况下:${}
* 参数类型:
parameterType:可以省略
* 特殊字符的处理:
1、转义字符:
2、CDATA区:
-->
<!-- <select id="selectById" resultMap="brandResultMap">-->
<!-- select *-->
<!-- from tb_brand where id = #{id};-->
<!-- </select>-->
<!-- 条件查询-->
<!-- <select id="selectByCondition" resultMap="brandResultMap">-->
<!-- select *-->
<!-- from tb_brand-->
<!-- where status = #{status}-->
<!-- and company_name like #{companyName}-->
<!-- and brand_name like #{brandName}-->
<!-- </select>-->
<!--
动态条件查询
*if:条件判断
*test:逻辑表达式
*问题:
* 恒等式
* <where> 替换 where 关键字
-->
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
<where>
<if test="status != null">
and status = #{status}
</if>
<if test="companyName != null and company != '' ">
and company_name like #{companyName}
</if>
<if test="brandName != null and brandName != '' ">
and brand_name like #{brandName}
</if>
</where>
</select>
<!--
单条件的动态条件查询
-->
<!-- <select id="selectByConditionSingle" resultMap="brandResultMap">-->
<!-- select *-->
<!-- from tb_brand-->
<!-- where-->
<!-- <choose><!– 相当于 switch –>-->
<!-- <when test="status != null">-->
<!-- status = #{status}-->
<!-- </when><!– 相当于 case –>-->
<!-- <when test="companyName != null and company != '' ">-->
<!-- company_name like #{companyName}-->
<!-- </when>-->
<!-- <when test="brandName != null and brandName != '' ">-->
<!-- brand_name like #{brandName}-->
<!-- </when>-->
<!-- <otherwise>-->
<!-- 1 = 1-->
<!-- </otherwise>-->
<!-- </choose>-->
<!-- </select>-->
<select id="selectByConditionSingle" resultMap="brandResultMap">
select *
from tb_brand
<where>
<choose><!-- 相当于 switch -->
<when test="status != null">
status = #{status}
</when><!-- 相当于 case -->
<when test="companyName != null and company != '' ">
company_name like #{companyName}
</when>
<when test="brandName != null and brandName != '' ">
brand_name like #{brandName}
</when>
</choose>
</where>
</select>
<!-- 起别名-->
<!-- <select id="selectAll" resultType="Brand">-->
<!-- select id, brand_name as brandName, company_name as companyName, ordered, description, status-->
<!-- from tb_brand;-->
<!-- </select>-->
<!-- 普通-->
<!-- <select id="selectAll" resultType="Brand">-->
<!-- select * from tb_brand;-->
<!-- </select>-->
<!-- 返回添加的主键-->
<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>
<!-- 修改动态字段-->
<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>
<!-- 删除一个数据-->
<delete id="deleteById">
delete from tb_brand where id = #{id}
</delete>
<!-- 动态删除多个数据-->
<!--
mybatis 会将数组参数,封装为一个Map集合。
* 默认:array = 数组
* 使用@Param注解改变map集合的默认key的名称
-->
<delete id="deleteByIds">
delete from tb_brand where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
</mapper>
package test;
import test.mapper.BrandMapper;
import test.pojo.Brand;
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 java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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 mapper = sqlSession.getMapper(BrandMapper.class);
// 4、执行方法
List<Brand> brands = mapper.selectAll();
System.out.println(brands);
// 5、释放资源
sqlSession.close();
}
@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 mapper = sqlSession.getMapper(BrandMapper.class);
// 4、执行方法
Brand brand = mapper.selectById(id);
System.out.println(brand);
// 5、释放资源
sqlSession.close();
}
@Test
public void testSelectByCondition() throws IOException {
// 就收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
// 处理参数
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
// 封装对象
// Brand brand = new Brand();
// brand.setStatus(status);
// brand.setBrandName(brandName);
// brand.setCompanyName(companyName);
Map map = new HashMap();
map.put("status",status);
map.put("brandName",brandName);
map.put("companyName",companyName);
// 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 mapper = sqlSession.getMapper(BrandMapper.class);
// 4、执行方法
// 散装参数查询
// List<Brand> brands = mapper.selectByCondition(status, companyName, brandName);
// 对象参数查询
// List<Brand> brands = mapper.selectByCondition(brand);
// map集合查询
List<Brand> brands = mapper.selectByCondition(map);
System.out.println(brands);
// 5、释放资源
sqlSession.close();
}
@Test
public void testSelectByConditionSingle() throws IOException {
// 就收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
// 处理参数
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
// 封装对象
Brand brand = new Brand();
// brand.setStatus(status);
// brand.setBrandName(brandName);
// brand.setCompanyName(companyName);
// 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 mapper = sqlSession.getMapper(BrandMapper.class);
// 4、执行方法
List<Brand> brands = mapper.selectByConditionSingle(brand);
System.out.println(brands);
// 5、释放资源
sqlSession.close();
}
@Test
public void testAdd() throws IOException {
// 就收参数
int status = 1;
String companyName = "鸭梨";
String brandName = "鸭梨牌山寨手机";
String description = "美国有苹果,中国有鸭梨";
int ordered = 2;
// 封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setDescription(description);
brand.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(true);
// 3、获取Mapper接口的代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
// 4、执行方法
mapper.add(brand);
// 提交事务
// sqlSession.commit();
List<Brand> brands = mapper.selectAll();
System.out.println(brands);
// 5、释放资源
sqlSession.close();
}
@Test
public void testAdd2() throws IOException {
// 就收参数
int status = 1;
String companyName = "鸭梨";
String brandName = "鸭梨牌山寨手机";
String description = "美国有苹果,中国有鸭梨";
int ordered = 2;
// 封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setDescription(description);
brand.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(true);
// 3、获取Mapper接口的代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
// 4、执行方法
mapper.add(brand);
Integer id = brand.getId();
System.out.println(id);
// 5、释放资源
sqlSession.close();
}
@Test
public void testUpdate() throws IOException {
// 就收参数
int status = 1;
String companyName = "菠萝";
String brandName = "菠萝牌山寨手机";
String description = "美国有苹果,中国有菠萝";
int ordered = 2;
int id = 8;
// 封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
// brand.setDescription(description);
brand.setOrdered(ordered);
brand.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(true);
// 3、获取Mapper接口的代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
// 4、执行方法
int count = mapper.update(brand);
System.out.println(count);
// 5、释放资源
sqlSession.close();
}
@Test
public void testDeleteById() throws IOException {
// 就收参数
int id = 8;
// 1、获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2、获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
// 3、获取Mapper接口的代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
// 4、执行方法
mapper.deleteById(id);
// 5、释放资源
sqlSession.close();
}
@Test
public void testDeleteByIds() throws IOException {
// 就收参数
int[] ids = {4,9,10};
// 1、获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2、获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession(true);
// 3、获取Mapper接口的代理对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
// 4、执行方法
mapper.deleteByIds(ids);
// 5、释放资源
sqlSession.close();
}
}