3.insert添加用法

2022-11-10 19:02:28

一.新增用户接口

UserMapper.java

package tk.mybatis.simple.mapper;

import org.apache.ibatis.annotations.Param;

import tk.mybatis.simple.model.SysRole;

import tk.mybatis.simple.model.SysUser;

import java.util.List;

/**

 * @author weihu

 * @date 2018/8/3/003 0:01

 */

public interface UserMapper {

    /**

     * 新增用户

     */

    int insertUser(SysUser sysUser);

}

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">

<!--sql语句映射文件-->

<!--因为已经配置了别名,所以resultType可以直接写类名,没有配置的话就要写类全名-->

<mapper namespace="tk.mybatis.simple.mapper.UserMapper">

    <!--添加用户,不需要返回值类型-->

    <insert id="insertUser" parameterType="tk.mybatis.simple.model.SysUser">

        INSERT INTO sys_user (

            id,

            user_name,

            user_password ,

            user_email,

            user_info,

            head_img,

            create_time)

        VALUES

            (#{id},#{userName},#{userPassword},#{userEmail},#{userInfo},#{headImg,jdbcType=BLOB},#{createTime,jdbcType=TIMESTAMP})

    </insert>

</mapper>

id:命名空间唯一标识符，可用来代表这条语句。

parameterType：即将传入的语句参数的完全限定类名或别名。这个属性是可选的，因为MyBatis可以推断出出入语句的具体参数，因此不建议配置该属性。

jdbcType:为了防止类型错误，对于一些特殊的数据类型，建议指定具体的jdbcType值。

UserMapperTest.java

package tk.mybatis.simple.mapper;

import org.apache.ibatis.session.SqlSession;

import org.junit.Assert;

import org.junit.Test;

import tk.mybatis.simple.model.SysRole;

import tk.mybatis.simple.model.SysUser;

import java.util.Date;

import java.util.List;

/**

 * @author weihu

 * @date 2018/8/5/005 9:34

 * @desc

 */

public class UserMapperTest extends BaseMapperTest {

    /**

     * 插入用户

     */

    @Test

    public void insertUserTest(){

        SqlSession sqlSession = getSqlSession();

        try {

            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

            //创建一个user对象

            SysUser sysUser=new SysUser();

            sysUser.setId(4L);

            sysUser.setUserName("huge");

            sysUser.setUserPassword("");

            sysUser.setUserEmail("1210740934@qq.com");

            sysUser.setUserInfo("测试用户");

            sysUser.setHeadImg(new byte[]{,,});

        /*

            插入的时间格式

            sysUser.setCreateTime("2018-08-05 17:00:58");插入这种的时间格式是不正确的

         */

            sysUser.setCreateTime(new Date());

            int result = userMapper.insertUser(sysUser);

            System.out.println("插入1条数据"+result);

            //注意一定要commit(),不提交的话数据是插入不到数据库中的
            sqlSession.commit();

        } catch (Exception e) {

            e.printStackTrace();

            //数据插入不成功进行回滚
            sqlSession.rollback();

        } finally {

            //关闭sqlSession

            sqlSession.close();

        }

    }

}

二、返回主键自增的值

UserMapper.java

package tk.mybatis.simple.mapper;

import org.apache.ibatis.annotations.Param;

import tk.mybatis.simple.model.SysRole;

import tk.mybatis.simple.model.SysUser;

import java.util.List;

/**

 * @author weihu

 * @date 2018/8/3/003 0:01

 */

public interface UserMapper {

    /**

     * 获取主键自增Id

     */

    int insertPrimaryKey(SysUser sysUser);

}

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">

<!--sql语句映射文件-->

<!--因为已经配置了别名,所以resultType可以直接写类名,没有配置的话就要写类全名-->

<mapper namespace="tk.mybatis.simple.mapper.UserMapper">

    <insert id="insertPrimaryKey" parameterType="tk.mybatis.simple.model.SysUser" useGeneratedKeys="true" keyProperty="id">

        INSERT INTO sys_user (

            user_name,

            user_password ,

            user_email,

            user_info,

            head_img,

            create_time)

        VALUES

            (#{userName},#{userPassword},#{userEmail},#{userInfo},#{headImg,jdbcType=BLOB},#{createTime,jdbcType=TIMESTAMP})

    </insert>

</mapper>

UserMapperTest.java

package tk.mybatis.simple.mapper;

import org.apache.ibatis.session.SqlSession;

import org.junit.Assert;

import org.junit.Test;

import tk.mybatis.simple.model.SysRole;

import tk.mybatis.simple.model.SysUser;

import java.util.Date;

import java.util.List;

/**

 * @author weihu

 * @date 2018/8/5/005 9:34

 * @desc

 */

public class UserMapperTest extends BaseMapperTest {

    @Test

    public void insertGetPrimayKey(){

        SqlSession sqlSession = getSqlSession();

        try {

            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

            //创建一个user对象

            SysUser sysUser=new SysUser();

            sysUser.setUserName("huge2");

            sysUser.setUserPassword("");

            sysUser.setUserEmail("1210740934@qq.com");

            sysUser.setUserInfo("测试用户");

            sysUser.setHeadImg(new byte[]{,,});

            int result=userMapper.insertPrimaryKey(sysUser);

            sqlSession.commit();
　　　　　　　//自动获取id

         System.out.println(sysUser.getId());

        } catch (Exception e) {

            //数据插入不成功进行回滚

            sqlSession.rollback();

            e.printStackTrace();

        } finally {

            //关闭sqlSession

            sqlSession.close();

        }

    }

}

三、使用selectKey返回主键的值

UserMapper.java

package tk.mybatis.simple.mapper;

import org.apache.ibatis.annotations.Param;

import tk.mybatis.simple.model.SysRole;

import tk.mybatis.simple.model.SysUser;

import java.util.List;

/**

 * @author weihu

 * @date 2018/8/3/003 0:01

 */

public interface UserMapper {

    /**

     * 获取主键，主键自增和非自增主键都可以获取

     */

    int insertAnyPrimaryKey(SysUser sysUser);

}

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">

<!--sql语句映射文件-->

<!--因为已经配置了别名,所以resultType可以直接写类名,没有配置的话就要写类全名-->

<mapper namespace="tk.mybatis.simple.mapper.UserMapper">

    <insert id="insertAnyPrimaryKey" parameterType="tk.mybatis.simple.model.SysUser">

        INSERT INTO sys_user (

            user_name,

            user_password ,

            user_email,

            user_info,

            head_img,

            create_time)

        VALUES

            (#{userName},#{userPassword},#{userEmail},#{userInfo},#{headImg,jdbcType=BLOB},#{createTime,jdbcType=TIMESTAMP})
<selectKey keyColumn="id" resultType="long" keyProperty="id" order="AFTER">

            SELECT LAST_INSERT_ID()

        </selectKey>

    </insert>

</mapper>

order属性设置和使用的数据库有关,msyql用AFTER，Oracle用BEFORE

SELECT LAST_INSERT_ID()用于获取数据库中最后插入的数据的ID值。

UserMapperTest.java

package tk.mybatis.simple.mapper;

import org.apache.ibatis.session.SqlSession;

import org.junit.Assert;

import org.junit.Test;

import tk.mybatis.simple.model.SysRole;

import tk.mybatis.simple.model.SysUser;

import java.util.Date;

import java.util.List;

/**

 * @author weihu

 * @date 2018/8/5/005 9:34

 * @desc

 */

public class UserMapperTest extends BaseMapperTest {

    @Test

    public void insertAnyPrimaryKey(){

        SqlSession sqlSession = getSqlSession();

        try {

            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

            //创建一个user对象

            SysUser sysUser=new SysUser();

            sysUser.setUserName("huge2");

            sysUser.setUserPassword("");

            sysUser.setUserEmail("1210740934@qq.com");

            sysUser.setUserInfo("测试用户");

            sysUser.setHeadImg(new byte[]{,,});

            int result=userMapper.insertAnyPrimaryKey(sysUser);
 sqlSession.commit();

          System.out.println(sysUser.getId());

        } catch (Exception e) {

            //数据插入不成功进行回滚

            sqlSession.rollback();

            e.printStackTrace();

        } finally {

            //关闭sqlSession

            sqlSession.close();

        }

    }

}

码农公寓

相关文章