Mybatis关于mysql返回pkid学习
1.概述
对mybatis常用的操作命令做一下总结,数据库基于mysql。后面章节会涉及数据的批量插入、批量更新和更新后返回主键ID等内容。
1.数据库信息
1.1.表结构定义
CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) DEFAULT NULL,
age int(11) DEFAULT NULL,
remark varchar(200) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=682 DEFAULT CHARSET=utf8;
1.2.数据库配置db.properties
mysql.driver=com.mysql.jdbc.Driver
需要开启多行执行功能
jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false"
jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true"
mysql.url=jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
mysql.username=root
mysql.password=123456
1.3.实体类
package com.tech.ability.mybatis.Entity;
/**
-
Created by kikop on 2019/1/17.
*/
public class MyUser {//实体类的属性和表的字段名称一一对应
private int id;
private String name;
private int age;public String getRemark() {
return remark;
}public void setRemark(String remark) {
this.remark = remark;
}private String remark;
public int getId() {
return id;
}public void setId(int id) {
this.id = id;
}public String getName() {
return name;
}public void setName(String name) {
this.name = name;
}public int getAge() {
return age;
}public void setAge(int age) {
this.age = age;
}@Override
public String toString() {
return "MyUser{" +
"age=" + age +
", id=" + id +
", name='" + name + ''' +
", remark='" + remark + ''' +
'}';
}
}
2.mybatis mapper接口文件
package com.tech.ability.mybatis.Mapper;
import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.SelectKey;
import org.springframework.data.repository.query.Param;
/**
-
Created by kikop on 2019/5/19.
*/
public interface MyUserMapper {//借助SelectKey注解:
//1.keyProperty="respid" 表示将自增长后的Id赋值给实体类中的 respid 字段。
//2.如果设置为 AFTER,那么先执行插入语句,然后是 selectKey 元素-这和如 Oracle 数据库相似,可以在插入语句中嵌入序列调用
//3.resultType = Integer.class
//4.statement = "select @@identity as respid"
//@SelectKey(keyProperty = "respid",statement = "select LAST_INSERT_ID() as respid",resultType = Integer.class,before = false)
@Insert("insert into users(name,age,remark) values('{remark}')")
@SelectKey(keyProperty = "respID", statement = "select @@identity as respID", resultType = Integer.class, before = false)
public int insertUserBackground(@Param("reqUsers") JSONObject reqUsers);
}
3.mybatis sqlmap映射文件
<?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.tech.ability.mybatis.Mapper.MyUserMapper">
<insert id="insertUserXml" parameterType="com.alibaba.fastjson.JSONObject">
<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="respID">
SELECT LAST_INSERT_ID()
</selectKey>
insert into users(name,age,remark) values('{remark}');
</insert>
</mapper>
4.mybatis工具类
package com.tech.ability.mybatis;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
/**
- Created by kikop on 2019/5/19.
*/
public class mybatisUtils {
//mybatis核心配置文件
private static final String mybatis_configPath = "mybatisconfig.xml";
/**
* 读取SqlSession
*
* @return
*/
public static SqlSession getSqlSession(ExecutorType execType, boolean isAutoCommit) {
SqlSession sqlSession = null;
try {
//1.读取mybatis的配置文件
//String resource = "mybatisconfig.xml";
//2.构建sqlSession的工厂
//使用类加载器加载 mybatis 的 classspath中配置文件
InputStream inputStream = null;
// InputStream inputStream = mybatisTest.class.getClassLoader().getResourceAsStream(resource);
inputStream = Resources.getResourceAsStream(mybatis_configPath);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.构建sqlSession的工厂
//使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）
//Reader reader = Resources.getResourceAsReader(resource);
//SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
//3.创建能执行映射文件中sql的sqlSession
// sqlSession = sessionFactory.openSession(isAutoCommit);
sqlSession = sessionFactory.openSession(execType, isAutoCommit);
} catch (IOException e) {
e.printStackTrace();
}
return sqlSession;
}
/**
* 关闭session(回收连接池必须)
*
* @param session
*/
public static void closeSqlSession(SqlSession session) {
session.close();
}
/**
* 关闭inputStream
*
* @param inputStream
*/
public static void closeInputSream(InputStream inputStream) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
5.测试代码
5.1.测试1
package com.tech.ability.mybatis;
import com.alibaba.fastjson.JSONObject;
import com.tech.ability.mybatis.Entity.MyUser;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import java.util.ArrayList;
import java.util.List;
/**
-
Created by kikop on 2019/1/17.
*/
public class mybatisTest {private static final Logger logger = LogManager.getLogger(mybatisTest.class);
public static void insertUserBackgroundTest() {
//1.构建sqlSession
SqlSession session = mybatisUtils.getSqlSession(ExecutorType.SIMPLE, false);
//2.映射sql的标识字符串
StringBuffer statement = new StringBuffer();
statement.append("com.tech.ability.mybatis.Mapper.MyUserMapper"); //mapper标签的namespace属性的值
statement.append(".insertUserBackground"); //select标签的id属性值
try {
//3.定义查询条件
JSONObject jsonObject = new JSONObject();
jsonObject.put("name", String.format("name_%d", 100));
jsonObject.put("age", 100);
jsonObject.put("remark", String.format("remark_%d", 100));
int updateResult = session.insert(statement.toString(), jsonObject);
System.out.println("[insertUserXml]影响的行数updateResult:" + updateResult + "返回值respID:" + jsonObject.get("respID"));
session.commit();
} catch (Exception ex) {
ex.printStackTrace();
session.rollback();
} finally {
//5.资源释放(连接池回收)
mybatisUtils.closeSqlSession(session);
}
}
5.2.测试2
public static void insertUserXmlTest() {
//1.构建sqlSession
SqlSession session = mybatisUtils.getSqlSession(ExecutorType.SIMPLE, false);
//2.映射sql的标识字符串
StringBuffer statement = new StringBuffer();
statement.append("com.tech.ability.mybatis.Mapper.MyUserMapper"); //mapper标签的namespace属性的值
statement.append(".insertUserXml"); //select标签的id属性值
try {
//List<JSONObject> reqList=new ArrayList<JSONObject>();
for(int i=0;i<10;i++){
//3.定义查询条件
JSONObject jsonObject = new JSONObject();
jsonObject.put("name", String.format("name_%d", i));
jsonObject.put("age", i);
jsonObject.put("remark", String.format("remark_%d", i));
//reqList.add(jsonObject);
int updateResult = session.insert(statement.toString(), jsonObject);
System.out.println("[insertUserXml]影响的行数updateResult:" + updateResult + "返回值respID:" + jsonObject.get("respID"));
}
session.commit();
} catch (Exception ex) {
ex.printStackTrace();
session.rollback();
} finally {
//5.资源释放(连接池回收)
mybatisUtils.closeSqlSession(session);
}
}
6.调用
public static void main(String[] args) throws Exception {
//getUserInfoByIDTest();
//getUserInfoByInTest();
//getUserInfoByInnerForeachTest();
//batchUpdateUserInfoListManualTest();
//batchUpdateUserInfoListAutoTest();
//batchInsertUserInfoListByUnionAllTest();
//batchInsertUserInfoListByExecutorBachTest2();
//insertUserXmlTest();
batchInsertUserInfoListByUnionAllTest();
}
}
总结
1.数据库连接时,设置运行多行查询。
2.多条语句,用“；”隔开即可。
