MyBatis——基本增删改查操作

MyBatis配置增删改查语句

实体类的映射文件.xml
User实体类的映射文件—>UserMapper.xml

<?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="userns">

    <!-- resultType属性指定结果的类型 -->
	<select id="findUserList" resultType="user">
        select * from users
    </select>
    <!-- ParameType属性指定输入参数的类型
     如果参数类型是简单类型（基本 + String + date）#{xxx}里面的内容任意-->
    <select id="findUserById" resultType="user" parameterType="int">
        select * from users where userid = #{userid}
    </select>

    <!-- 用${value}作为 里面的内容必须是value 利用的是String的存储方式（value[]数组）-->
    <select id="findUserByName" resultType="user" parameterType="string">
        select * from users where username like '%${value}%'
    </select>

    <!-- 多条件查询利用map类型
        默认的参数类型是map-->
    <select id="findUserByScore" resultType="user">
        select * from users where score between #{minScore} and #{maxScore}
    </select>

    <!-- pageSize 分页大小 currentPage当前页码 -->
    <select id="findUserByPage" resultType="user">
        select * from users limit #{currentPage}, #{pageSize}
    </select>

    <!-- insert 插入数据 只有parameterType输入参数 -->
    <insert id="insertUser" parameterType="user">
        insert into users values(null, #{username}, #{password}, #{score})
    </insert>

    <!-- update 同样只有输入参数parameterType-->
    <update id="updateUser" parameterType="user">
        update users set username = #{username}, password = #{password}, score = #{score}
        where userid = #{userid}
    </update>

    <!-- delete 也是输入参数 -->
    <delete id="deleteUser" parameterType="int">
        delete from users where userid = #{userid}
    </delete>
</mapper>

MyBatis实现查询

package com.tjise.test;

import com.tjise.entity.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 org.junit.After;
import org.junit.Before;
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 CURDTest {

    private SqlSession session;
    @Before
    public void init() throws IOException {
        //通过Resources类加载和读取配置文件
        InputStream is = Resources.getResourceAsStream("mybatis.xml");

        //根据配置文件的信息创建SqlSessionFactory对象
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);

        //通过SqlSessionFactory获取SqlSession对象
        session = sessionFactory.openSession();
    }

    @Test
    public void sessionTest(){
        System.out.println(session);
        System.out.println(session.getConnection());
    }

    //不带参数的查询
    @Test
    public void findUserListTest(){
        List<User> userList = session.selectList("userns.findUserList");
        System.out.println(userList);
        try {
            User user = (User) Class.forName("com.tjise.entity.User").newInstance();

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //带一个参数
    @Test
    public void findUserByNameTest(){
        List<User> userList = session.selectList("userns.findUserByName", "user");
        System.out.println(userList);
    }

    //带多个参数
    @Test
    public void findUserByScoreTest(){
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("minScore", 60);
        map.put("maxScore", 100);
        List<User> userList = session.selectList("userns.findUserByScore", map);
        System.out.println(userList);
    }

    //实现分页效果
    @Test
    public void findUserByPage(){
        int pageSize = 2;
        int currentPage = 1;
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("pageSize", pageSize);
        map.put("currentPage", (currentPage - 1) * pageSize);
        List<User> userList = session.selectList("userns.findUserByPage", map);
        System.out.println(userList);
    }

    //插入数据
    @Test
    public void testInsertUser(){
        try {
            User user = new User("user7", "123456", 10);
            int rows = session.insert("userns.insertUser", user);
            System.out.println(rows);
            session.commit();
        } catch (Exception e) {
            session.rollback();
        }

    }

    //修改数据
    @Test
    public void testUpdateUser(){
        try {
            User user = session.selectOne("userns.findUserById", 8);
            user.setPassword("789");
            int rows = session.update("userns.updateUser", user);
            System.out.println(rows);
            session.commit();
        } catch (Exception e) {
            session.rollback();
        }
    }

    //修改数据
    @Test
    public void testDeleteUser(){
        try {
            int rows = session.delete("userns.deleteUser", 8);
            System.out.println(rows);
            session.commit();
        } catch (Exception e) {
            session.rollback();
            e.printStackTrace();
        }
    }

    @After
    public void destory(){
        session.close();
    }

}

遇到的问题

1、中文查询不出来
解决方案：在MyBatis配置文件中的数据库url后加入
&useUnicode=true&characterEncoding=UTF-8
具体如下：
&对于xml中不能显示，所以要用转义字符进行转移
&的转义字符：&

<property name="url" value="jdbc:mysql://localhost:3306/ssm?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>