package bean;
/**
* TODO
*
* @author asus
* @version 1.0
* @date 2021/1/16 11:04
*/
public class Student {
private Integer id;
private String sname;
private String address;
private String sex;
private Integer tid;
public Student() {
}
public Student(String sname, String address, String sex, Integer tid) {
this.sname = sname;
this.address = address;
this.sex = sex;
this.tid = tid;
}
public Student(Integer id, String sname, String address, String sex, Integer tid) {
this.id = id;
this.sname = sname;
this.address = address;
this.sex = sex;
this.tid = tid;
}
...
}
package bean.params;
/**
* TODO
*
* @author asus
* @version 1.0
* @date 2021/1/16 22:06
*/
public class StudentParams {
private String sname;
private String sex;
private Integer tid_begin;
private Integer tid_end;
public StudentParams() {
}
public StudentParams(String sname, String sex, Integer tid_begin, Integer tid_end) {
this.sname = sname;
this.sex = sex;
this.tid_begin = tid_begin;
this.tid_end = tid_end;
}
...
}
<?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="bean.Student">
<!--
mybaits通过自己手动写sql语句来对数据库进行操作,select就是查询操作
同样有update,delete,insert
id:标识符,测试时调用的标识符来调用sql语句,相当于方法名
resultType:返回值类型
parameterType:传入的参数类型
如果是引用数据类型的话,需要传入完整的路径.如:java.lang.String以及对象com.oralc.pojo.Student
在下方的条件需要以#{}来占位,如果传入类型是对象型,需要与对象的属性名一致
-->
<resultMap id="student_info" type="java.util.HashMap">
<result column="code" property="key"></result>
<result column="name" property="value"></result>
</resultMap>
<select id="getStudentMap" resultType="java.util.HashMap">
select * from student a where a.id = #{id}
</select>
<select id="findStudentById" resultType="bean.Student" parameterType="int">
select * from student where id=#{id}
</select>
<select id="findAll" resultType="bean.Student">
select * from student
</select>
<select id="findWithParameters" resultType="bean.Student" parameterType="bean.params.StudentParams">
select * from student a where 1 = 1
<if test="sname != null and sname != ''">
and a.sname like concat('%', #{sname}, '%')
</if>
<if test="sex != null and sex != ''">
and a.sex = #{sex}
</if>
<if test="tid_begin != null and tid_begin != ''">
and a.tid <![CDATA[>= ]]> #{tid_begin}
</if>
<if test="tid_end != null and tid_end != ''">
and a.tid <![CDATA[<= ]]> #{tid_end}
</if>
</select>
<select id="queryList" resultType="java.util.HashMap">
select * from student limit 2
</select>
<insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="bean.Student">
insert into student
(sname, address, sex, tid)
values (#{sname}, #{address}, #{sex}, #{tid})
</insert>
<insert id="insertMore" parameterType="java.util.List" >
insert into student
(sname, address, sex, tid)
values
<foreach collection="list" item="student" separator=",">
(
#{student.sname, jdbcType=VARCHAR},
#{student.address, jdbcType=VARCHAR},
#{student.sex, jdbcType=VARCHAR},
#{student.tid, jdbcType=INTEGER}
)
</foreach>
</insert>
<update id="updateById" parameterType="bean.Student">
update student a
<set>
<if test="sname != null">a.sname=#{sname},</if>
<if test="address != null">a.address=#{address},</if>
<if test="sex != null">a.sex=#{sex},</if>
<if test="tid != null">a.tid=#{tid},</if>
</set>
where a.id = #{id}
</update>
</mapper>
package util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.Configuration;
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.Reader;
/**
* TODO
*
* @author asus
* @version 1.0
* @date 2021/1/16 15:24
*/
public class MybatisUtil {
private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
private static SqlSessionFactory sqlSessionFactory;
static {
try {
// 读取mybatis核心配置文件
Reader reader = Resources.getResourceAsReader("mappers/mysql.xml");
// 创建会话工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static SqlSession getSession() {
// 获取会话
SqlSession sqlSession = threadLocal.get();
if (sqlSession == null) {
// 从会话工厂获取session
sqlSession = sqlSessionFactory.openSession();
// 绑定会话工厂
threadLocal.set(sqlSession);
}
return sqlSession;
}
public static void close() {
SqlSession sqlSession = threadLocal.get();
if (sqlSession != null) {
sqlSession.close();
}
}
/**
* 重新加载xml
*/
public void refreshMapper() {
Configuration configuration = this.sqlSessionFactory.getConfiguration();
}
}
import bean.Student;
import bean.params.StudentParams;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import util.MybatisUtil;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* TODO
*
* @author asus
* @version 1.0
* @date 2021/1/16 15:27
*/
public class Test {
private SqlSession session = null;
@Before
public void before() {
//获取会话
session = MybatisUtil.getSession();
}
@org.junit.Test
public void insert() {
/**
* 从会话中调用映射文件中的sql语句,为了防止不同的映射文件标识符重复,通过namespace.标识符来调用
* 如果后面有参数的话,在后面添加参数
*/
Student newSudent = new Student("小明", "苏州", "男", 22);
session.insert("bean.Student.insert", newSudent);
session.commit();
System.out.println(newSudent.getId());//当前插入的ID
}
@org.junit.Test
public void insertMore() {
List<Student> studentList = new ArrayList<Student>();
studentList.add(new Student("小红", "盐城", "女", 20));
studentList.add(new Student("小蓝", "泰州", "男", 25));
studentList.add(new Student("小美", "安徽", "女", 20));
studentList.add(new Student("小黄", "北京", "男", 24));
session.insert("bean.Student.insertMore", studentList);
session.commit();
for (Student student : studentList) {
System.out.println(student.getId());//null
}
}
@org.junit.Test
public void batchInsert() {
Student student1 = new Student("小红", "盐城", "女", 20);
Student student2 = new Student("小蓝", "泰州", "男", 25);
session.insert("bean.Student.insert", student1);
session.insert("bean.Student.insert", student2);
session.commit();
System.out.println(student1.getId());
System.out.println(student2.getId());
}
@org.junit.Test
public void getStudentMap() {
Map<String, Object> map = session.selectMap("bean.Student.getStudentMap", 2, "id");
System.out.println(map);
}
@org.junit.Test
public void findStudentById() {
Student student = session.selectOne("bean.Student.findStudentById",1);
System.out.println(student);
}
@org.junit.Test
public void findAll() {
List<Student> studentList = session.selectList("bean.Student.findAll");
for (Student student2 : studentList) {
System.out.println(student2);
}
}
@org.junit.Test
public void findWithParameters() {
StudentParams params = new StudentParams("明", null, null, null);
//params = new StudentParams("", "男", null, null);
//params = new StudentParams("", null, 20, null);
params = new StudentParams("", null, null, 30);
List<Student> studentList = session.selectList("bean.Student.findWithParameters", params);
for (Student student2 : studentList) {
System.out.println(student2);
}
}
@org.junit.Test
public void queryList() {
System.out.println("begin");
try {
Thread.sleep(5000);
} catch (InterruptedException e) {
e.printStackTrace();
}
List<Map<String, Object>> studentList = session.selectList("bean.Student.queryList");
System.out.println(studentList.size());
for (Map<String, Object> studentInfo : studentList) {
System.out.println(studentInfo);
}
}
@org.junit.Test
public void updateById() {
Student student = new Student(2, "小张", "无锡", "女", 23);
int res = session.update("bean.Student.updateById", student);
System.out.println(res);
}
@After
public void after() {
session.commit();
MybatisUtil.close();
}
}