MyBatis入门

目录

1.查询表

1.1在pom导入坐标

1.2 mybatis-config.xml

1.3userMpper.xml

1.4main方法

2.Mapper动态代理

2.1创建Mapper接口，以及UserMapper.xml

2.2 在Mapper.xml里写

2.3 在UserMappper接口里写

2.4编码

2.5包代理，在mybatis-config.xml里 

3.解决和数据库的表的字段名字冲突

3.1resuletMap

3.2SQL片段

4.单条件详细查询

4.1main方法

 4.2Mapper接口

4.3Mapper.xml

4.4细节

4.4.1#{}和￥{}的区别

4.4.2parameter属性

4.4.3 转义字符

5.多条件查询

6.动态查询

6.1 if和where

6.2 choose

7.添加

8.修改

---

1.查询表

1.1在pom导入坐标

 <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.11</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.17</version>
        </dependency>

1.2 mybatis-config.xml

改：jdbc的连接路径，以及<mapper>标签的resource属性

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--    配置数据库连接信息，可以配置多个。通过default切换-->
    <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://127.0.0.1:3306/mybatis?serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--        sql映射-->
<!--        <mapper resource="com/Mapper/UserMapper.xml"/>-->
        <package name="com.Mapper"/>
    </mappers>
</configuration>

1.3userMpper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace:映射的名称-->
<mapper namespace="com.Mapper.UserMapper">
    <select id="selectUser" resultType="com.i.User">
        select * from tb_user;
    </select>
</mapper>

1.4main方法

package com;

import com.Mapper.UserMapper;
import com.i.User;
import org.apache.ibatis.annotations.Mapper;
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;

public class Select {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();
//        List<User> list = sqlSession.selectList("tes.selectUser");
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = userMapper.selectUser();
        System.out.println(users);

        sqlSession.close();

    }
}

2.Mapper动态代理

2.1创建Mapper接口，以及UserMapper.xml

注意：resources创建包用com/Mapper

2.2 在Mapper.xml里写

2.3 在UserMappper接口里写

2.4编码

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = userMapper.selectUser();

2.5包代理，在mybatis-config.xml里 

3.解决和数据库的表的字段名字冲突

3.1resuletMap

添加

<!--    resuletMap:解决和数据库的表的字段名字冲突-->
    <resultMap id="userResultMap" type="com.i.User">
<!--        id用于主键，result用于普通字段-->
        <id column="uid" property="id"></id>
<!--    <result column="name" property="name"></result>-->
    </resultMap>

 注意：同时resultType改成resultMap，并且key等于上面resultMap标签的id值

<select id="selectUser" resultMap="userResultMap" >
        select * from tb_user;
    </select>

成品：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace:映射的名称-->
<mapper namespace="com.Mapper.UserMapper">
<!--    resuletMap:解决和数据库的表的字段名字冲突-->
    <resultMap id="userResultMap" type="com.i.User">
<!--        id用于主键，result用于普通字段-->
        <id column="uid" property="id"></id>
<!--    <result column="name" property="name"></result>-->
    </resultMap>
    <select id="selectUser" resultMap="userResultMap" >
        select * from tb_user;
    </select>
</mapper>

3.2SQL片段

4.单条件详细查询

4.1main方法

public class Select {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new 
        SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sqlSessionFactory.openSession();
//      List<User> list = sqlSession.selectList("tes.selectUser");
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = userMapper.selectUserId(1001);
        System.out.println(user);

        sqlSession.close();

    }
}

 4.2Mapper接口

public interface UserMapper {
    List<User> selectUser();
    User selectUserId(int id);

}

4.3Mapper.xml

<select id="selectUserId" resultMap="userResultMap">
        select * from tb_user where uid=#{id};
    </select>

 注意：这里用的resultMap，如果bean类的参数名和数据库一致，用resultType就行，它的key等于bean类的全限定名即java包下的路径

4.4细节

4.4.1#{}和￥{}的区别

4.4.2parameter属性

4.4.3 转义字符

用xml的或者CD区

5.多条件查询

5.1接口和xml和main方法需要修改的

 

 5.2注意条件

6.动态查询

6.1 if和where

单用if

   <select id="selectByUser" resultMap="userResultMap">
<!--   if标签实现动态添加，数字只需要！=null判断，字符串还需要！=''，另外if标签里的属性名应该与java里一致而非SQL-->
        select * from tb_user
<!--        where标签可以解决and连接问题，可以使我们不用考虑是否要有and-->
        where 1=1
        <if test="id!=null and id!=''">and uid=#{id}</if>
        <if test="pwd!=null and pwd!=''"> and pwd=#{pwd}</if>

    </select>

 加上where

<select id="selectByUser" resultMap="userResultMap">
<!--   if标签实现动态添加，数字只需要！=null判断，字符串还需要！=''，另外if标签里的属性名应该与java里一致而非SQL-->
        select * from tb_user
<!--        where标签可以解决and连接问题，可以使我们不用考虑是否要有and-->
        <where>
        <if test="id!=null and id!=''">uid=#{id}</if>
        <if test="pwd!=null and pwd!=''">pwd=#{pwd}</if>
        </where>
    </select>

6.2 choose

单用choose

    <select id="selectByUser" resultMap="userResultMap">
        select * from tb_user where
            <choose><!--   switch         -->
                <when test="id!=null and id!=''">uid=#{id}</when><!--   case         -->
                <when test="pwd!=null and pwd!=''">pwd=#{pwd}</when>
                <otherwise>1=1</otherwise>
            </choose>
    </select>

加上where

 <select id="selectByUser" resultMap="userResultMap">
        select * from tb_user
        <where>
            <choose><!--   switch         -->
                <when test="id!=null and id!=''">uid=#{id}</when><!--   case         -->
                <when test="pwd!=null and pwd!=''">pwd=#{pwd}</when>
            </choose>
        </where>
    </select>

7.添加

mapper接口

void addAll(User user);

 

 Mapper.xml

<!--   useGeneratedKeys设置是否可以获取主键，keyProperty主键赋值给某个属性值 -->
    <insert id="addAll" useGeneratedKeys="true" keyProperty="id">
        insert into tb_user(name,pwd) value(#{name},#{pwd})
    </insert>

 main

        userMapper.addAll(user);
        sqlSession.commit();//提交事务
        System.out.println(user.getId());//主键回填

 注：也可以不写commit，在sqlsession创建时加入true参数

 SqlSession sqlSession = sqlSessionFactory.openSession(true);

8.修改

Mapper接口

int updateUser(User user);

注：返回值也可以写void，写int返回的是影响的行数

mapper.xml

<update id="updateUser">
        update tb_user
<!--        set标签可以解决,的问题 所以,最好都加上  -->
        <set>
            <if test="name!=null and name!=''">name=#{name},</if>
            <if test="pwd!=null and pwd!=''">pwd=#{pwd},</if>
        </set>
        where uid=#{id}
    </update>

main

 int i = userMapper.updateUser(user);