Java后端05（初识MyBatis） - ToB企服应用市场:ToB评测及商务社交产业平台

<?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="com.iweb.entity"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="url" value="jdbc:mysql://localhost:3306/iweb?characterEncoding=utf-8"/>
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/user.xml"/>
</mappers>
</configuration>

复制代码

<?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="com.iweb.entity">
<select id="listUser" resultType="User">select * from user</select>
<insert id="addUser" parameterType="User">insert into user values (#{userId},#{username},#{password})</insert>
<delete id="deleteUser" parameterType="String">delete from user where userId = #{userId}</delete>
<update id="updateUser" parameterType="User">update user set username = #{username},password = #{password} where userId = #{userId}</update>
<select id="getUser" parameterType="String" resultType="User">select * from user where userId = #{userId}</select>
<select id="listUserByNameLike" parameterType="String" resultType="User">select * from user where username like concat('%',#{0},'%')</select>
<select id="listUserByIdAndNameLike" parameterType="map" resultType="User">select * from user where userId > #{userId} and username like concat('%',#{username},'%')</select>
</mapper>

复制代码

public void test1() throws IOException {
private SqlSession sqlSession;
@Before
public void init() throws IOException {
// 输入流读取配置文件信息
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 基于配置文件获取 mybatis 的一级缓存对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 基于这个一级缓存，创建一个二级缓存
sqlSession = sqlSessionFactory.openSession();
}
@Test
public void test01(){
// 使用二级缓存实现sql语句调用
List<User> userList = sqlSession.selectList("listUser");
// 遍历集合
for (User user : userList) {
System.out.println(user);
}
}
@Test
public void test02(){
User user = new User("3","robot01","123456");
sqlSession.insert("addUser",user);
// mybatis 需要手动提交缓存
sqlSession.commit();
test01();
}
@Test
public void test03(){
User user = new User();
user.setUserId("3");
sqlSession.delete("deleteUser",user);
sqlSession.commit();
test01();
}
@Test
public void test04(){
User user = sqlSession.selectOne("getUser","2");
System.out.println(user);
}
@Test
public void test05(){
User user = new User("2","HSS","123456");
sqlSession.update("updateUser",user);
sqlSession.commit();
test01();
}
@Test
public void test06(){
List<User> users = sqlSession.selectList("listUserByNameLike","ss");
System.out.println(users);
}
@Test
public void test07(){
Map<String,Object> params = new HashMap<>();
params.put("userId",2);
params.put("username","ss");
List<User> users = sqlSession.selectList("listUserByIdAndNameLike",params);
System.out.println(users);
}
}

复制代码

<resultMap id="productBean" type="Product">
<id column="productId" property="productId"/>
<result column="productName" property="productName"/>
<result column="price" property="price"/>
<result column="stock" property="stock"/>
<association property="user" javaType="User">
<id column="uId" property="userId"/>
<result column="username" property="username"/>
</association>
</resultMap>
<select id="listProduct" resultMap="productBean">
select productName,productId,price,stock,u.userId 'uId',username from product left join user u on product.userId = u.userId
</select>

复制代码

public class TestMybatis {
private SqlSession sqlSession;
@Before
public void init() throws IOException {
// 输入流读取配置文件信息
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 基于配置文件获取 mybatis 的一级缓存对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 基于这个一级缓存，创建一个二级缓存
sqlSession = sqlSessionFactory.openSession();
}
@Test
public void test01(){
List<Product> productList = sqlSession.selectList("listProduct");
for (Product product : productList) {
System.out.println(product);
}
}
}

复制代码

<select id="listProduct" resultType="product">
select * from product
<if test="productName != null">
where productName like concat('%',#{productName},'%')
</if>
</select>

复制代码

@Test
public void test01(){
List<Product> productList = sqlSession.selectList("listProduct");
for (Product product : productList) {
System.out.println(product);
}
}
@Test
public void test02(){
Map<String,String> params = new HashMap<>();
params.put("productName","1");
List<Product> productList = sqlSession.selectList("listProduct",params);
for (Product product : productList) {
System.out.println(product);
}
}

复制代码

<select id="listProduct" resultType="Product">
select * from product
<where>
<if test="productName != null">
and productName like concat('%',#{productName},'%')
</if>
<if test="price != 0">
and price > #{price}
</if>
</where>
</select>

复制代码

@Test
public void test01(){
Map<String,Object> params = new HashMap<>();
params.put("productName","1");
params.put("price",0);
List<Product> productList = sqlSession.selectList("listProduct",params);
for (Product product : productList) {
System.out.println(product);
}
}

复制代码

<update id="updateProduct">
update product
<set>
<if test="productName != null">
productName = #{productName},
</if>
<if test="price != null">
price = #{price},
</if>
<if test="stock != null">
stock = #{stock},
</if>
</set>
where productId = #{productId}
</update>

复制代码

@Test
public void test01(){
Map<String,Object> params = new HashMap<>();
params.put("productId","1");
params.put("productName","product");
params.put("price","10086");
params.put("stock","10000");
sqlSession.update("updateProduct",params);
sqlSession.commit();
}

复制代码

<mapper namespace="com.iweb.entity">
<select id="listProduct" resultType="Product">
select * from product
<where>
<choose>
<when test="productName != null">
and name like concat('%',#{productName},'%')
</when>
<when test="price != null">
and price > #{price}
</when>
<otherwise>
and id > 5
</otherwise>
</choose>
</where>
</select>
</mapper>

复制代码

/**
* 通过接口（底层是java的JDK动态代理）实现 mybatis 调用
* 开发人员只需要关心接口，实现类由 mybatis 动态生成
* 1. 注解开发方式：适用于简单场景（注解场景下编写一对多多对一或者是动态sql非常麻烦）
* 2. xml配置文件开发方式：适用于所有场景（推荐）
* @author te9uila
* @since 2023/8/5
*/
public interface ProductMapper {
@Insert("insert into product values (#{productId},#{productName},#{price},#{stock},#{userId})")
void add(Product product);
@Delete("delete from product where productId = #{productId}")
void delete(String id);
@Select("select * from product where productId = #{productId}")
Product get(String id);
@Update("update product set productName = #{productName},price = #{price},stock = #{stock} where productId = #{productId}")
int update(Product product);
@Select("select * from product")
List<Product> list();
}

复制代码

@Test
public void test01(){
ProductMapper productMapper = sqlSession.getMapper(ProductMapper.class);
List<Product> productList = productMapper.list();
System.out.println(productList);
}

复制代码