用mybatis进行增删改查

1.查询

 

（1）查询所有数据

 UserMapper.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="org.example.UserMapper">
    <!-- id：每一个sql语句的唯一标识，即外部通过id来调用语句   -->
    <!-- resultType：返回的结果类型，即定义的POJO类-->
    <select id="selectAll" resultType="org.example.pojo.user">
        select * from user
    </select>
</mapper>

 usermapper类

package org.example.mapper;
import org.example.pojo.user;
import java.util.List;

public interface UserMapper {
    List<user> selectAll();
}

tset测试类 

package org.example.test;

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.example.pojo.user;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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

//            2.获取SqlSession对象，执行sql语句
            SqlSession sqlSession = sqlSessionFactory.openSession();

//            3.执行sql语句
            List<user> users = sqlSession.selectList("org.example.UserMapper.selectAll");
            System.out.println(users);

//            4.释放资源
            sqlSession.close();

 结果：

原表：

2）条件查询（查询uname中含“A”且gender为“NAN"的数据）

多条件查询：由于UserMapper中查询方法接收参数的方式不同，因此条件查询的方式有三种

①散装参数

<?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="org.example.mapper.UserMapper">
    <!-- id：每一个sql语句的唯一标识，即外部通过id来调用语句   -->
    <!-- resultType：返回的结果类型，即定义的POJO类-->
    <select id="selectByCondition" resultType="org.example.pojo.user">
        select * from user where uname like #{uname} and gender like #{gender}
    </select>
</mapper>

package org.example.mapper;

import org.apache.ibatis.annotations.Param;
import org.example.pojo.user;
import java.util.List;

public interface UserMapper {
       List<user> selectByCondition(@Param("uname") String uname, @Param("gender") String gender);

}

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

//            2.获取SqlSession对象，执行sql语句
            SqlSession sqlSession = sqlSessionFactory.openSession();

//            3.执行sql语句
            String uname ="A";
            uname="%" + uname + "%";
            String gender ="NAN";
            gender="%" + gender + "%";
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<user> users = userMapper.selectByCondition(uname,gender);
            System.out.println(users);

//            4.释放资源
            sqlSession.close();

        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

②对象参数

mapper接口

public interface UserMapper {
       List<user> selectByCondition(user user1);
}

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

//            2.获取SqlSession对象，执行sql语句
            SqlSession sqlSession = sqlSessionFactory.openSession();

//            3.执行sql语句
            String uname ="A";
            uname="%" + uname + "%";
            String gender ="NAN";
            gender="%" + gender + "%";
            user user1=new user();
            user1.setGender(gender);
            user1.setUname(uname);
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<user> users = userMapper.selectByCondition(user1);
            System.out.println(users);

//            4.释放资源
            sqlSession.close();

③map集合参数

mapper接口

public interface UserMapper {
       List<user> selectByCondition(Map map);
}

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

//            2.获取SqlSession对象，执行sql语句
            SqlSession sqlSession = sqlSessionFactory.openSession();

//            3.执行sql语句
            String uname ="A";
            uname="%" + uname + "%";
            String gender ="NAN";
            gender="%" + gender + "%";
            Map map =new HashMap();
            map.put("uname",uname);
            map.put("gender",gender);
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<user> users = userMapper.selectByCondition(map);
            System.out.println(users);

//            4.释放资源
            sqlSession.close();

        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}

 结果：

 

3）动态条件查询（先判断条件后查询）

①if

<mapper namespace="org.example.mapper.UserMapper">
    <!-- id：每一个sql语句的唯一标识，即外部通过id来调用语句   -->
    <!-- resultType：返回的结果类型，即定义的POJO类-->
    <select id="selectByCondition" resultType="org.example.pojo.user">
        select * from user where
        <if test="uname!=null">
            uname like #{uname}
        </if>
        <if test="gender!=null">
            and gender like #{gender}
        </if>
    </select>
</mapper>

 ②where

<mapper namespace="org.example.mapper.UserMapper">
    <!-- id：每一个sql语句的唯一标识，即外部通过id来调用语句   -->
    <!-- resultType：返回的结果类型，即定义的POJO类-->
    <select id="selectByCondition" resultType="org.example.pojo.user">
        select * from user
        <where>
        <if test="uname!=null">
            uname like #{uname}
        </if>
        <if test="gender!=null">
            and gender like #{gender}
        </if>
        </where>
    </select>
</mapper>

 ③choose(相当于Switch语句）

<mapper namespace="org.example.mapper.UserMapper">
    <!-- id：每一个sql语句的唯一标识，即外部通过id来调用语句   -->
    <!-- resultType：返回的结果类型，即定义的POJO类-->
    <select id="selectByCondition" resultType="org.example.pojo.user">
        select * from user
        where
        <choose>
            <when test="uname!=null">
                uname like #{uname}
            </when>
            <when test="gender!=null">
                gender like #{gender}
            </when>
            <otherwise>
                1=1
            </otherwise>
        </choose>
    </select>
</mapper>

 2.添加数据

mapper接口

public interface UserMapper {
       void add(user user1);
}

usermapper.xml  

public interface UserMapper {
       void add(user user1);
}

 

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

//            2.获取SqlSession对象，执行sql语句
            SqlSession sqlSession = sqlSessionFactory.openSession();

//            3.执行sql语句
            int id=3;
            String uname="LISI";
            String gender="NAN";
            user user1=new user();
            user1.setId(id);
            user1.setUname(uname);
            user1.setGender(gender);
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            userMapper.add(user1);
            sqlSession.commit();   //提交

//            4.释放资源
            sqlSession.close();

        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}

 

3.修改数据

1）修改全部字段

<update id="update">
        update user
        set uname = #{uname},gender = #{gender} where number = #{number}
    </update>     //xml文件
 

void update(user user1);   //mapper接口
  


int number=2;
String uname="LISI111";
String gender="NAN111";
user user1=new user();
user1.setUname(uname);
user1.setGender(gender);
user1.setNumber(number);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
userMapper.update(user1);
sqlSession.commit();      //test
            

结果：

 

2）修改动态字段

<update id="update">
   update user
    <set>
      <if test="uname!=null and uname!=''">
       name=#{uname},
      </if>
      <if test="pwd!=null and pwd!=''">
       pwd=#{pwd},
      </if>
      <if test="gender!=null and gender!=''">
       gender=#{gender},
      </if>
    </set>
   where id = #{id};
  </update>

 

4.删除数据（在此操作中之前把表中的ID字段改为了number（懒得改回来了......））

1）删除一个

void deleteByNumber(user user1);     //mapper接口




<delete id="deleteByNumber">
        delete from user where number = #{number};
</delete>                 //xml文件







int number=2;
user user1=new user();
user1.setNumber(number);
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
userMapper.deleteByNumber(user1);
sqlSession.commit();   //提交               //test

结果：

 

2）批量删除

void deleteByNumbers(@Param("numbers") int[] numbers);     //mapper接口





<delete id="deleteByNumbers">
        delete from user where number in (
        <foreach collection="numbers" item="number" separator=",">
           #{number}
        </foreach>
        )
</delete>                     //xml文件





int[] numbers={1,3};
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
userMapper.deleteByNumbers(numbers);
sqlSession.commit();              //test

结果：