1、普通Sql编写
前景准备
SalaryMapper.java
package com.liu.work.Dao.User;
import com.liu.work.pojo.user.Salary;
import java.util.List;
public interface SalaryMapper {
//查询
List<Salary> getSalaryList();
//指定查询
List<Salary> getPersonMsg(String ID);
//增加
int addSalary(Salary salary);
//修改
int updateSalary(String EmployeeID);
//删除
int delSalary(String EmployeeID);
}
sql语句编写,SalaryMapper.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">
<!--这里namespace=接口的路径-->
<mapper namespace="com.liu.work.Dao.User.SalaryMapper">
<!--id=接口里的方法名,resultType=返回的类型,实体类(要写全)-->
<select id="getSalaryList" resultType="com.liu.work.pojo.user.Salary">
select *
from salary
</select>
<!--插入-->
<insert id="addSalary" parameterType="com.liu.work.pojo.user.Salary">
insert into salary(EmployeeID, InCome, OutCome)
values (#{EmployeeID}, #{InCome}, #{OutCome})
</insert>
<!--更新-->
<update id="updateSalary" parameterType="String">
update Salary
set InCome=InCome + 100
where EmployeeID = #{EmployeeID};
</update>
<!--删除-->
<delete id="delSalary" parameterType="String">
delete
from Salary
where EmployeeID = #{EmployeeID};
</delete>
</mapper>
2、注解编写Sql语句(.xml文件依然要创建)
//注解
@Select("select * from departments where DepartmentID=#{DepartmentID}")
List<Department> getID(String id);
//添加
@Insert("insert into departments(DepartmentID,DepartmentName,Note) values(#{DepartmentID},#{DepartmentName},#{Note})")
int insertMsg(Department department);
//修改
@Update("update departments set Note=#{Note} where DepartmentID=#{DepartmentID}")
int updateMeg(@Param("DepartmentID") String id,@Param("Note") String note);
// 删除
@Delete("delete from departments where DepartmentID=#{DepartmentID}")
int DeleteMsg(@Param("DepartmentID") String id);
3、字段冲突问题(resultMap属性)
字段不一致在.xml中的处理(简单)
<resultMap id="MapUser" type="com.liu.work.pojo.user.Salary">
<!--实体类与DB的不一致的才写,column:数据库中的字段;property:实体类中的字段,type:是返回的结果类型-->
<result column="EmployeeID" property="ID"/>
</resultMap>
<!--查询-->
<select id="getPersonMsg" resultMap="MapUser">
select * from salary where EmployeeID=#{ID}
</select>
字段不一致在.xml中的处理(复杂,老师和学生举例)
了解:resultMap的复杂属性
针对对象:
<association property="对于实体类的属性" column="数据库字段" javaType="指定返回的类型" select="sql语句"></association>
针对集合:
<collection property="对于实体类的属性" javaType="指定返回的类型" ofType="集合中的泛型信息" select="sql语句" column="数据库字段">
<result property="" column=""/>
</collection>
DB:
一对多(一个老师有多个学生)
实体类:
Student.java
package com.liu.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private Teacher teacher;
}
Teacher.java
package com.liu.pojo;
import lombok.Data;
@Data
public class Teacher {
private int id;
private String name;
}
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.liu.dao.StudentMapper">
<!--复杂语句查询,按结果嵌套处理-->
<select id="getStuList" resultMap="studentMap">
select s.id sid, s.name sname, t.id tid,t.name tname
from student s,
teacher t
where s.tid = t.id;
</select>
<resultMap id="studentMap" type="com.liu.pojo.Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="com.liu.pojo.Teacher">
<result property="name" column="tname"/>
<result property="id" column="tid"/>
</association>
</resultMap>
<!--复杂语句查询按查询嵌套处理-->
<!-- <select id="getStuList" resultMap="stuMap">
select *
from student;
</select>
<resultMap id="stuMap" type="com.liu.pojo.Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" column="tid" javaType="com.liu.pojo.Teacher" select="getTeacherList"/>
</resultMap>
<select id="getTeacherList" resultType="com.liu.pojo.Teacher">
select *
from teacher
where id = #{id};
</select>-->
</mapper>
测试:
@Test
public void getStuTest(){
SqlSession sqlSession = MybatisUntil.getSqlSession();
/* List<Student> stu = sqlSession.selectList("com.liu.dao.StudentMapper.getStuList");*/
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> stuList = mapper.getStuList();
for ( Student t:
stuList) {
System.out.println(t);
}
sqlSession.close();
sqlSession.close();
多对一(多个学生对应一个老师)
实体类
Student .java
package com.liu.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
}
Teacher .java
package com.liu.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
List<Student> students;
}
TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.liu.dao.TeacherMapper">
<!--多对1-->
<!--按照结果嵌套处理-->
<!-- <select id="getTeacherList" resultMap="TeacherMapper">
select t.id tid, t.name tname, s.id sid, s.name sname
from teacher t,
student s
where t.id = s.tid
and t.id = #{tid};
</select>
<resultMap id="TeacherMapper" type="com.liu.pojo.Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="com.liu.pojo.Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>-->
<!--按照查询嵌套处理-->
<select id="getTeacherList" resultMap="TeacherMapper">
select *
from teacher where id = #{tid};
</select>
<resultMap id="TeacherMapper" type="com.liu.pojo.Teacher">
<result property="id" column="id"/>
<result property="name" column="name"/>
<collection property="students" javaType="ArrayList" ofType="com.liu.pojo.Student" select="getStu" column="id"/>
</resultMap>
<select id="getStu" resultType="com.liu.pojo.Student">
select *
from student where tid =#{tid};
</select>
</mapper>
测试:
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUntil.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher=mapper.getTeacherList(1);
System.out.println(teacher);
sqlSession.close();
}
4、动态Sql(If、choose、foreach)
<?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.liu.dao.BlogMapper">
<!--sql复用-->
<!--if使用根据test属性进行判断-->
<sql id="if_title_author">
<if test="title!=null">
and title=#{title}
</if>
<if test="author!=null">
and author=#{author}
</if>
</sql>
<!--实现复用-->
<select id="queryIf" parameterType="map" resultType="com.liu.pojo.Bolg">
select *
from blog where 1=1
<include refid="if_title_author"></include>
</select>
<!--where:若它所包含的条件一个都不成立则where会自动消失;若它包含的语句成立,则自动加上where并除去多去and/or-->
<select id="queryWhere" parameterType="map" resultType="com.liu.pojo.Bolg">
select *
from blog
<where>
<include refid="if_title_author"></include>
</where>
</select>
<!--choose:只能选择成功的一个语句执行相当于switch,若其中存在otherwise则必须赋值执行-->
<select id="queryChoose" parameterType="map" resultType="com.liu.pojo.Bolg">
select *
from blog
<where>
<choose>
<when test="title!=null">
title=#{title}
</when>
<otherwise>
views=#{views}
</otherwise>
</choose>
</where>
</select>
<!--set:自动除去多去","-->
<update id="updateSet" parameterType="map">
update blog
<set>
author=#{author},views=#{views}
</set>
where id=#{id}
</update>
<!--foreach:collection:集合名;item:表示集合中的一个元素;条件拼接{open=开头;separator:分隔符;close:结尾}-->
<select id="queryFor" parameterType="map" resultType="com.liu.pojo.Bolg">
select *
from blog
<where>
<foreach collection="ids" item="id"
open="and (" close=")" separator="or"
>
id=#{id}
</foreach>
</where>
</select>
</mapper>
测试:
import com.liu.dao.BlogMapper;
import com.liu.pojo.Bolg;
import com.liu.untils.IDutils;
import com.liu.untils.MybatisUntil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.*;
public class TestSql {
@Test
public void queryList(){
SqlSession sqlSession = MybatisUntil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<Object, Object> map = new HashMap<>();
//map.put("title","Java");
map.put("create_time",new Date("Mar 26 16:55:56 CST 2022"));
List<Bolg> bolgs = mapper.queryIf(map);
for (Bolg b: bolgs) {
System.out.println(b);
}
sqlSession.close();
}
//2.where
@Test
public void whereTest(){
SqlSession sqlSession = MybatisUntil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<Object, Object> map = new HashMap<>();
map.put("title","Mybatis");
List<Bolg> bolgs = mapper.queryWhere(map);
for (Bolg b:bolgs){
System.out.println(b);
}
sqlSession.close();
}
//queryChoose
@Test
public void chooseTest(){
SqlSession sqlSession = MybatisUntil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<Object, Object> map = new HashMap<>();
map.put("views",9999);
List<Bolg> bolgs = mapper.queryChoose(map);
for (Bolg b:bolgs){
System.out.println(b);
}
sqlSession.close();
}
4.set
@Test
public void setTest(){
SqlSession sqlSession = MybatisUntil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<Object, Object> map = new HashMap<>();
map.put("id","647099367c4e4e4fae2c73e4e75b212c");
map.put("author","李四");
map.put("views",8000);
mapper.updateSet(map);
sqlSession.close();
}
//queryFor
@Test
public void forTest(){
SqlSession sqlSession = MybatisUntil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<Object, Object> map = new HashMap<>();
ArrayList<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids",ids);
List<Bolg> bolgs = mapper.queryFor(map);
for (Bolg b:
bolgs) {
System.out.println(b);
}
sqlSession.close();
}
}