mybatis 增删改(1)

 

1.pom

    <!--我用的是：2.1.4.RELEASE-->       

 <!--jsonObject-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.28</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.22</version>
        </dependency>

        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

2.application.properties

但Mybatis映射文件中的sql语句默认是不支持以" ; " 结尾的，也就是不支持多条sql语句的执行。所以需要在连接mysql的url上加 &allowMultiQueries=true 这个才可以执行。

#mysql连接参数
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc\:mysql\://localhost\:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8&&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=xxxxxxxxxx
 
#Mapper.xml所在的位置
mybatis.mapper-locations=classpath:mapper/*.xml

3.实体类

@Setter
@Getter
@ToString
public class Student {
    private int id;
    private String name;
    private String age;

}

4.接口

    //数据库自增获取主键的值(参数传入:可以是map或者是json,)
    int insertStudent(Student student);
   
    //删除
    int deleteStudentByID(int id);

    //修改(传入JSONObject,Student(如果属性和sql不配报错),Map)都可以
    int updateStudentByID(JSONObject jsonObject);

     //批量增加
    int insertBatchStudent(List<Student> students);

    //批量删除
    int deleteBatchStudent(List<Integer> students);

    //批量更新
    int updateBatchStudent(List<Student> students);

5.sql

  <!--
        mybatis支持自增主键，利用staement.getGenreatedKeys();
        useGeneratedKeys="true" 使用自增主键,获取主键的值值策略
        keyProperty：指定对应的主键属性，也就是mybatis获取到主键以后，将这个值封装给javaBean(实体类)的哪个属性
    -->
    <insert id="insertStudent" parameterType="mq.demo.entity.Student" useGeneratedKeys="true" keyProperty="id">
        insert into student values (#{id},#{name},#{age})
    </insert>

        <update id="updateStudentByID" parameterType="com.alibaba.fastjson.JSONObject">
        update  student
        <set>
            <if test="name != null">
                name = #{name,jdbcType=VARCHAR},
            </if>
            <if test="age != null">
                age = #{age,jdbcType=VARCHAR},
            </if>
        </set>
      where id=#{id}
    </update>

    <delete id="deleteStudentByID">
        delete from student where id=#{id}
    </delete>

        <!--
      select:标签名字:  CRUD的时候对应的名字
      id:这个语句的唯一标识;   【必须和接口的方法名字一样】;
      parameterType:传入参数类型
      resultType:返回值类型
      #{id}:参数的接收

      collection=""  遍历集合
      index=""  遍历时候的索引
      item=""   每此遍历得到的对象
      open=""   以什么开始
      close=""  以什么结束
      separator="" 以什么作为分割符
    -->
     <insert id="insertBatchStudent" parameterType="list">
        insert into student(name,age) values
          <foreach collection="list" item="stu" separator=",">
              (#{stu.name},#{stu.age})
          </foreach>
    </insert>


    <delete id="deleteBatchStudent">
        delete from student where id in
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>


         <update id="updateBatchStudent" parameterType="java.util.List">
        <foreach collection="list" item="str" index="index" open="" close="" separator=";">
            update student
            <set>
                name = #{str.name,jdbcType=VARCHAR},
                age = #{str.age,jdbcType=VARCHAR}
            </set>
            where id =#{str.id}
        </foreach>
    </update>