Mybatis基础---------增删查改

目录结构

增删改

1、新建工具类用来获取会话对象

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.io.Resources;

import java.io.IOException;
import java.io.InputStream;

/**
 * 静态变量：使用 static 关键字声明的变量是类级别的，所有实例共享相同的静态变量。这意味着无论创建多少个对象，它们都将共享相同的静态变量值。
 * public class MyClass {
 *     static int staticVar; // 静态变量，所有实例共享
 * }
 * 静态方法：使用 static 关键字声明的方法是类级别的，可以通过类名直接调用，无需实例化对象。这些方法通常用于执行与类本身相关的操作，而不是特定实例的操作。
 * public class MyClass {
 *     static void staticMethod() {
 *         // 静态方法
 *     }
 * }
 * 静态代码块：使用 static 关键字标识的代码块在类加载时执行，通常用于进行类级别的初始化操作。
 */
public class SqlSessionUtil {
    static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//加载输入流创建会话工厂
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public  static SqlSession openSession(){
        return sqlSessionFactory.openSession();
    }

}

2、加入junit依赖

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.2</version>
    <scope>test</scope>
</dependency>

3、通过映射传递属性

之前的sql语句全部写在了映射文件中，然而在实际应用时是通过映射传递属性的，也就是java对象对应sql语句中的占位符属性，属性名一般和java对象中的属性名相同，我们只需要用#{}作为占位符，占位符名称与java对象属性名一致即可。

如下实体类：

import java.util.Date;
public class User {
    private Integer id;
    private String username;
    private String password;
    private String salt;
    private String email;
    private int type;
    private int status;
    private String activationCode;
    private String headerUrl;
    private Date createTime;


    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getSalt() {
        return salt;
    }

    public void setSalt(String salt) {
        this.salt = salt;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public int getType() {
        return type;
    }

    public void setType(int type) {
        this.type = type;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public String getActivationCode() {
        return activationCode;
    }

    public void setActivationCode(String activationCode) {
        this.activationCode = activationCode;
    }

    public String getHeaderUrl() {
        return headerUrl;
    }

    public void setHeaderUrl(String headerUrl) {
        this.headerUrl = headerUrl;
    }

    public User(Integer id, String username, String password, String salt, String email, int type, int status, String activationCode, String headerUrl, Date createTime) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.salt = salt;
        this.email = email;
        this.type = type;
        this.status = status;
        this.activationCode = activationCode;
        this.headerUrl = headerUrl;
        this.createTime = createTime;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
}

映射文件

<insert id="insertUser">
           INSERT INTO users (user_id, username, password, salt, email, type, status, activation_Code, header_url, create_time)
VALUES (null, #{username}, #{password}, #{salt}, #{email}, #{type}, #{status}, #{activationCode}, #{headerUrl}, #{createTime});
</insert>

测试：

@Test
public  void insertTest(){
    SqlSession sqlSession = SqlSessionUtil.openSession();
    User user = new User(null, "JohnDoe", "password", "salt123", "johndoe@example.com", 1, 1, "abcxyz", "https://example.com/image.jpg", new Date());
    //这里传入user实体,mybatis会自动将user属性值填充到sql语句中的占位符
    sqlSession.insert("insertUser",user);

    sqlSession.commit();
    sqlSession.close();//关闭会话
}

测试下修改和删除

<delete id="deleteUser">
    delete  from users where user_id=#{id};
</delete>
<update id="updateUser">
    UPDATE users
    SET username = #{username},
        password = #{password},
        salt = #{salt},
        email = #{email},
        type = #{type},
        status = #{status},
        activation_Code = #{activationCode},
        header_Url = #{headerUrl},
        create_Time = #{createTime}
   WHERE user_id = #{id};
</update>

@Test//修改
public  void updateTest(){
    SqlSession sqlSession = SqlSessionUtil.openSession();
    User user = new User(2, "DDDD", "password", "salt123", "johndoe@example.com", 1, 1, "abcxyz", "https://example.com/image.jpg", new Date());

    sqlSession.insert("updateUser",user);
    sqlSession.commit();
    sqlSession.close();//关闭会话
}
@Test//删除
public  void deleteTest(){
    SqlSession sqlSession = SqlSessionUtil.openSession();
    sqlSession.insert("deleteUser",2);//当sql只有一个占位符时，传递的参数会直接赋值到该占位符中，与占位符名称无关
    sqlSession.commit();
    sqlSession.close();//关闭会话
}

查询

获取结果集，通过select标签中的resultType参数来指定查询结果封装到对应的实体类中，如果实体类中的属性与数据库表中属性不一致，可以使用as将对应数据库表中列名重命名并与实体类一致。

<select id="selectOneUser" resultType="User">
    select user_id as id, username, password, salt, email, type, status, activation_Code as activationCode, header_url as headerUrl, create_time as createTime from users where user_id=#{id};
</select>

或者采用另一种方式：通过在<resultMap> 中使用 <result> 标签来进行手动映射。

column--property==>数据库列名--实体类属性名

<resultMap id="userResultMap" type="User">
    <id property="id" column="user_id"/>
    <result property="username" column="username"/>
    <result property="password" column="password"/>
    <result property="salt" column="salt"/>
    <result property="email" column="email"/>
    <result property="type" column="type"/>
    <result property="status" column="status"/>
    <result property="activationCode" column="activation_Code"/>
    <result property="headerUrl" column="header_url"/>
    <result property="create_time" column="createTime"/>
</resultMap>
<select id="selectUser" resultMap="userResultMap">
    select  * from users;
</select>

@Test
public void selectTest(){
    SqlSession sqlSession = SqlSessionUtil.openSession();
    List<User> selectUser = sqlSession.selectList("selectUser");
    for (User user : selectUser) {
        System.out.println(user.toString());
    }
    sqlSession.commit();
    sqlSession.close();//关闭会话
}

这里说明：不管是查询单个记录还多个记录，设置返回封装映射时，resultType应该设置为实体类或者List<实体类>型中的实体类。设置手动映射resultMapper同样如此。