Mybatis操作MySQL

进行增删改查操作

只需修改接口文件、Mapper配置文件以及测试类文件

 

接口文件：

package com.hzj.dao;

import com.hzj.pojo.Users;

import java.util.List;

//编写接口
public interface UserDao {
     //查询全部用户
     List<Users> getUsersList();

     //根据ID查询用户
     Users getUserID(int id);

     //增加一个用户
     int addUser(Users user);

     //更改一个用户信息
     int updateUser(Users users);

     //删除一个用户
     int deleteUser(int id);
}

Mapper配置文件：

<?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.hzj.dao.UserDao">
    <select id="getUsersList" resultType="com.hzj.pojo.Users">
        select * from mydb.person
    </select>

    <select id="getUserID" parameterType="int" resultType="com.hzj.pojo.Users">
        select * from mydb.person where id=#{id}
    </select>

    <insert id="addUser" parameterType="com.hzj.pojo.Users">
        insert into mydb.person (id, name, id_number) VALUES (#{id},#{name},#{id_number});
    </insert>

    <update id="updateUser" parameterType="com.hzj.pojo.Users"><![CDATA[
        update mydb.person set name=#{name},id_number=#{id_number} where id=#{id}
    ]]></update>

    <delete id="deleteUser" parameterType="com.hzj.pojo.Users">
        delete from mydb.person where id=#{id}
    </delete>
</mapper>

 

测试类文件：

package com.hzj.dao;

import com.hzj.pojo.Users;
import com.hzj.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class UserDaoTest {

    @Test
    public void test(){
        //第一步，获取session对象
        SqlSession session = MybatisUtils.getSqlSession();

        //方式一：getMapper，常用
        try {
            UserDao mapper = session.getMapper(UserDao.class);
            List<Users> usersList = mapper.getUsersList();

            //方式二
            //List<Object> userList = session.selectList(s:"com.hzj.dao.User.getUsersList");

            for (Users users : usersList) {
                System.out.println(users);
            }
        }finally {
            session.close();
        }
    }

    @Test
    public void Test1(){
        SqlSession session = MybatisUtils.getSqlSession();
        UserDao mapper = session.getMapper(UserDao.class);

        Users user = mapper.getUserID(2);
        System.out.println(user);

        session.close();
    }


    @Test
    public void Test2(){
        SqlSession session = MybatisUtils.getSqlSession();
        UserDao mapper = session.getMapper(UserDao.class);

        int user = mapper.addUser(new Users(7, "吴六", "111"));

        //增删改需要提交事务
        if (user>0){
            System.out.println("插入成功");
        }
        session.commit();
        session.close();
    }

    @Test
    public void Test3(){
        SqlSession session = MybatisUtils.getSqlSession();
        UserDao mapper = session.getMapper(UserDao.class);

        int user = mapper.updateUser(new Users(2,"李四","764"));
        if (user>0){
            System.out.println("修改成功");
        }
        session.commit();
        session.close();
    }

    @Test
    public void Test4(){
        SqlSession session = MybatisUtils.getSqlSession();
        UserDao mapper = session.getMapper(UserDao.class);

        int user = mapper.deleteUser(7);
        if (user>0){
            System.out.println("删除成功");
        }
        session.commit();
        session.close();
    }
}

 

Map传递参数，直接在SQL中取出key即可 【parameterType = "map"】

对象传递参数，直接在SQL中取出对象的属性即可 【parameterType = "Object"】

当需要操作的数据过多且需要多个参数类型时，应考虑使用Map
进行模糊查询时，注意通配符%，如配置文件中写为 select * from table where column like "%"#{value}"%"，可能会发生SQL注入

正确写法为

select * from table where column like "%",#{value},"%"

List<Users> userList = mapper.getUsersLike("%张%");