Mybatis - CURD（01）

返回值为集合类型

<!-- 占位符
    #{}  类似于JBDC的占位符，比如上面的sql语句，传入Integer10时，它就是把Integer10填在占位符的位置
          如果是String10，就是把String10填在里面。里面的参数可以随便填，但不能为空。

    ${}     字符串拼接，如下面的sql语句，传入"五"，最终注入数据库的是"%五%"。里面的参数只能写value

    #{}  占位符实现字符串拼接，"%${value}%" <==> "%"#{v}"%"
 -->
<select id="findUsersByName" parameterType="String" resultType="com.pojo.User">
    select * from user where username like "%${value}%"
</select>

//1、加载核心配置文件
String coreXml = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(coreXml);
//2、创建工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//3、获得sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//4、执行sql语句
List<User> users = sqlSession.selectList("User.findUsersByName", "五");
//5、处理结果
System.out.println(users);

插入

<!-- 这里的 #{} 参数不能再随便写了，需要取出来User里的属性-->
<insert id="insert" parameterType="com.pojo.User">
    insert into user(username, sex, birthday, address) 
        values(#{username}, #{sex}, #{birthday}, #{address});
</insert>

public void insert() throws Exception {
    //1、加载核心配置文件
    String coreXml = "sqlMapConfig.xml";
    InputStream in = Resources.getResourceAsStream(coreXml);
    //2、创建工厂
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    //3、获得sqlSession
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //4、执行sql语句
    User user = new User("helloworld", "2", new Date(), "aaaaaa");
    int i = sqlSession.insert("User.insert", user);
    //默认是不自动提交事务
    sqlSession.commit();
    //5、处理结果
    System.out.println(i);
}

插入数据时返回主键

<insert id="insert" parameterType="com.pojo.User">
    <!-- 返回最新插入的id，底层是mysql提供 -->
    <!-- Integer类型，自增长是先保存数据再生成ID，比如uuid，会先生成ID，再保存数据 -->
    <selectKey keyProperty="id" resultType="Integer" order="AFTER">
        select LAST_INSERT_ID()
    </selectKey>
    insert into user(username, sex, birthday, address) 
        values(#{username}, #{sex}, #{birthday}, #{address});
</insert>

public void insert() throws Exception {
    //1、加载核心配置文件
    String coreXml = "sqlMapConfig.xml";
    InputStream in = Resources.getResourceAsStream(coreXml);
    //2、创建工厂
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    //3、获得sqlSession
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //4、执行sql语句
    User user = new User("helloworld", "2", new Date(), "aaaaaa");
    int i = sqlSession.insert("User.insert", user);
    //默认是不自动提交事务
    sqlSession.commit();
    //5、处理结果
    System.out.println(user.getId());
}

更新用户

<update id="updateUser" parameterType="com.pojo.User">
    update user 
    set username = #{username}, sex = #{sex}, birthday = #{birthday}, address = #{address}
    where id = #{id}
</update>

@org.junit.Test
public void update() throws Exception {
    //1、加载核心配置文件
    String coreXml = "sqlMapConfig.xml";
    InputStream in = Resources.getResourceAsStream(coreXml);
    //2、创建工厂
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    //3、获得sqlSession
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //4、执行sql语句
    User user = new User("helloworld", "1", new Date(), "aaaaaa");
    user.setId(1);
    int i = sqlSession.update("User.updateUser", user);
    //默认是不自动提交事务
    sqlSession.commit();
    //5、处理结果
}

删除

<update id="delete" parameterType="Integer">
    delete from user where id = #{aaa}
</update>

@org.junit.Test
public void delete() throws Exception {
    //1、加载核心配置文件
    String coreXml = "sqlMapConfig.xml";
    InputStream in = Resources.getResourceAsStream(coreXml);
    //2、创建工厂
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    //3、获得sqlSession
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //4、执行sql语句
    User user = new User("helloworld", "1", new Date(), "aaaaaa");
    user.setId(1);
    int i = sqlSession.delete("User.delete", 28);
    //默认是不自动提交事务
    sqlSession.commit();
    //5、处理结果
}