mybatis mysql autoreconnect=true_2019-01-22_Mybatis关于mysql简单操作学习

Mybatis关于mysql简单操作学习

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

需要开启多行执行功能

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 {

}

3.mybatis sqlmap映射文件

select * from users where id=#{intid}

select * from users where id in (#{strQueryIds})

resultType="com.alibaba.fastjson.JSONObject">

select * from users where id in

#{id}

resultType="com.alibaba.fastjson.JSONObject">

select * from users where id in

${id}

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);

/**

getUserInfoByIDTest

*/

public static void getUserInfoByIDTest() {

//1.构建sqlSession

SqlSession session = mybatisUtils.getSqlSession(ExecutorType.SIMPLE, false);

//2.映射sql的标识字符串

//String statement = "com.skill.tech.mybatis.Mapper.MyUserMapper.getUserInfoByID";//映射sql的标识字符串

StringBuffer statement = new StringBuffer();

statement.append("com.tech.ability.mybatis.Mapper.MyUserMapper"); //mapper标签的namespace属性的值

statement.append(".getUserInfoByID"); //select标签的id属性值

//3.定义查询条件

int intid = 2;

//4.执行查询返回一个唯一user对象的sql

MyUser myUser = session.selectOne(statement.toString(), intid);

mybatisUtils.closeSqlSession(session);

//5.输出结果

System.out.println(myUser);

}

5.2.测试2

/**

getUserInfoByInTest

*/

private static void getUserInfoByInTest() {

//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(".getUserInfoByIn"); //select标签的id属性值

//3.定义查询条件

String strQueryIds = "1,2";

//4.执行查询返回一个唯一user对象的sql

List myUser = session.selectList(statement.toString(), strQueryIds);

mybatisUtils.closeSqlSession(session);

//5.输出结果

System.out.println(myUser);

}

5.3.测试3

/**

getUserInfoByInnerForeachTest

*/

private static void getUserInfoByInnerForeachTest() {

//1.构建sqlSession

SqlSession session = mybatisUtils.getSqlSession(ExecutorType.SIMPLE, false);

//3.映射sql的标识字符串

StringBuffer statement = new StringBuffer();

statement.append("com.tech.ability.mybatis.Mapper.MyUserMapper"); //mapper标签的namespace属性的值

statement.append(".getUserInfoByInnerForeach"); //select标签的id属性值

//3.定义查询条件

JSONObject jsonObject = new JSONObject();

List idList = new ArrayList<>();

idList.add(1);

idList.add(11);

jsonObject.put("idList", idList);

//4.执行查询返回一个唯一user对象的sql

List myUser = session.selectList(statement.toString(), jsonObject);

mybatisUtils.closeSqlSession(session);

//5.输出结果

System.out.println(myUser);

}

5.1.测试4

/**

getUserInfoByInnerForeachRetJsonTest

*/

private static void getUserInfoByInnerForeachRetJsonTest() {

//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(".getUserInfoByInnerForeachRetJson"); //select标签的id属性值

//3.定义查询条件

JSONObject jsonObject = new JSONObject();

List idList = new ArrayList<>();

idList.add(1);

idList.add(92);

jsonObject.put("idList", idList);

//4.执行查询返回一个唯一user对象的sql

List myUser = session.selectList(statement.toString(), jsonObject);

mybatisUtils.closeSqlSession(session);

//5.输出结果

System.out.println(myUser);

}

6.调用

public static void main(String[] args) throws Exception {

//getUserInfoByIDTest();

//getUserInfoByInTest();

//getUserInfoByInnerForeachTest();

//batchUpdateUserInfoListManualTest();

//batchUpdateUserInfoListAutoTest();

//batchInsertUserInfoListByUnionAllTest();

//batchInsertUserInfoListByExecutorBachTest2();

//insertUserXmlTest();

batchInsertUserInfoListByUnionAllTest();

}

}

总结

1.数据库连接时,设置运行多行查询。

2.多条语句,用“；”隔开即可。