mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--外部配置文件-->
<properties resource="db.properties"></properties>
<settings> <!--日志实现LogImpl STDOUT_LOGGING:控制打印输出-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--给实体类Student起别名,别名简称为stu-->
<!--也可以给实体类的上级实体包起别名,则默认为小写字母开头的实体类名,只有给包起别名是,实体类才可以使用别名注解自定义别名-->
<typeAliases>
<typeAlias type="com.etoak.student.pojo.Student" alias="stu"></typeAlias>
</typeAliases>
<!--使用第三方组件实现真分页,StudentMapper不用传参数-->
<plugins>
<!-- com.github.pagehelper为PageHelper类所在包名 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 使用下面的方式配置参数，后面会有所有的参数介绍 limit rownum-->
<property name="helperDialect" value="mysql"/>
</plugin>
</plugins>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${m.driver}"/>
<property name="url" value="${m.url}"/>
<property name="username" value="${m.user}"/>
<property name="password" value="${m.pwd}"/>
</dataSource>
</environment>
</environments>
<!--执行Sql语句的XxxMapper.xml文件所在位置-->
<mappers>
<mapper resource="mapper/StudentMapper.xml"></mapper>
</mappers>
</configurat添加学生返回主键两种方式(开关和selectKey标签)
StudentMapper
public int addStudent(Student stu);StudentMapper.xml
<!--用于绑定Dao接口,即面向接口编程,不用在书写接口实现类,mybatis通过帮过的Dao层接口自动找到其对应Sql语句-->
<mapper namespace="com.etoak.emp.mapper.StudentMapper"><!--
1.采用开关的方式，获取主键
useGeneratedKeys:是否返回主键 默认 否
keyProperty:类中对应主键的变量
keyColumn:表中对应主键的字段
-->
<insert id="addStudent1" parameterType="stu" useGeneratedKeys="true" keyProperty="id">
insert into tb_stu(s_name,s_age,s_birth,s_email,s_schid)
values
(#{name},#{age},#{birth},#{email},#{schid})
</insert>
<!--
selectKey:用来查询主键的
resultType:主键的返回类型
order:
AFTER:默认 表示先添加 后 查询
BEFORE: 先查询 后添加
-->
<insert id="addStudent" parameterType="stu">
<selectKey keyProperty="id" keyColumn="s_id" resultType="int" order="AFTER">
select last_insert_id()
</selectKey>
insert into tb_stu(s_name,s_age,s_birth,s_email,s_schid)
values
(#{name},#{age},#{birth},#{email},#{schid})
</insert>通过学生id查学生的两种方式(类和表不一致时起别名和resultMap标签)
StudentMapper
public Student queryStuById(int id);StudentMapper.xml
<!--类和表不一致时：起别名-->
<select id="queryStuById1" resultType="stu">
select
s_id id,
s_name name,
s_age age,
s_birth birth,
s_email email,
s_schid schid
from tb_stu where s_id=#{id}
</select>
<!--
resultMap:返回值是个Map结果集
property:类中的属性
column:表中的字段
-->
<resultMap id="rMap_stu" type="stu">
<id property="id" column="s_id"></id>
<result property="name" column="s_name"></result>
<result property="age" column="s_age"></result>
<result property="birth" column="s_birth"></result>
<result property="email" column="s_email"></result>
<result property="schid" column="s_schid"></result>
</resultMap>
<select id="queryStuById" resultMap="rMap_stu">
select * from tb_stu where s_id=#{id}
</select>查询全部学生
StudentMapper
public List<Student> queryAll();StudentMapper.xml
<select id="queryAll" resultMap="rMap_stu">
select * from tb_stu
</select>模糊查询(#改为%)
StudentMapper
public List<Student> queryByLike(String name);StudentMapper.xml
<select id="queryByLike" resultMap="rMap_stu">
select * from tb_stu where s_name like '%${name}%'
</select>顺序查询(条件得是表的字段)
StudentMapper
public List<Student> queryByOrder(String orderByColumn);StudentMapper.xml
<select id="queryByOrder" resultMap="rMap_stu">
select * from tb_stu order by ${orderbycolumn} desc
</select>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~动态标签~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
根据学生名字或邮箱查询(where,trim,choose(when,othewise))
StudentMapper
public List<Student> queryByNameAndEmail(Map<String,Object> tiaojian);StudentMapper.xml
<resultMap id="rMap_stu" type="stu">
<id property="id" column="s_id"></id>
<result property="name" column="s_name"></result>
<result property="age" column="s_age"></result>
<result property="birth" column="s_birth"></result>
<result property="email" column="s_email"></result>
<result property="schid" column="s_schid"></result>
</resultMap>
<select id="queryByNameAndEmail1" resultMap="rMap_stu">
select * from tb_stu
<where>
<if test="name!=null and name!='' ">
and s_name=#{name}
</if>
<if test="email!=null and email!='' ">
and s_email=#{email}
</if>
</where>
</select>
<select id="queryByNameAndEmail2" resultMap="rMap_stu">
select * from tb_stu
<where>
<choose>
<when test="name!=null and name!='' ">
and s_name=#{name}
</when>
<otherwise>
and s_email=#{email}
</otherwise>
</choose>
</where>
</select>
<select id="queryByNameAndEmail" resultMap="rMap_stu">
select * from tb_stu
<!--
干了两件事
1:prefix:参数开始之前添加where
prefixOverrides: 参数开始的前边遇到and删去
-->
<trim prefix="where" prefixOverrides="and ">
<if test="name!=null and name!='' ">
and s_name=#{name}
</if>
<if test="email!=null and email!='' ">
and s_email=#{email}
</if>
</trim>
</select>
修改学生信息(set全自动,手动加手动删)
StudentMapper
public void updateStudent(Student stu);StudentMapper.xml
<!--set元素会动态的在首行插入set关键字,并会删掉语句后边的逗号-->
<update id="updateStudent1" >
update tb_stu
<set>
<if test="name!=null and name!='' ">
s_name=#{name},
</if>
<if test="age!=0">
s_age=#{age},
</if>
<if test="birth!=null">
s_birth=#{birth},
</if>
<if test="email!=null and email!='' ">
s_email=#{email},
</if>
<if test="schid!=0">
s_schid=#{schid}
</if>
</set>
where s_id=#{id}
</update>
<update id="updateStudent" >
update tb_stu
<!--在语句前加set,去掉句子后边的逗号-->
<trim prefix=" set " suffixOverrides=",">
<if test="name!=null and name!='' ">
s_name=#{name},
</if>
<if test="age!=0">
s_age=#{age},
</if>
<if test="birth!=null">
s_birth=#{birth},
</if>
<if test="email!=null and email!='' ">
s_email=#{email},
</if>
<if test="schid!=0">
s_schid=#{schid}
</if>
</trim>
where s_id=#{id}
</update>
批量添加(foreach)的两种方式:子查询和联合查询
StudentMapper
public void addStus(List<Student> stus);StudentMapper.xml
<!--insert into xx (f1,f2...fn) values(),(),()
insert into xx(f1,f2..fn) (select..) union all (select )-->
<insert id="addStus1">
insert into tb_stu(s_name,s_age,s_birth,s_email,s_schid)
values
<foreach collection="list" item="s" separator=",">
(#{s.name},#{s.age},#{s.birth},#{s.email},#{s.schid})
</foreach>
</insert>
<!-- insert into xx(f1,f2..fn) (select..) union all (select )-->
<insert id="addStus">
insert into tb_stu(s_name,s_age,s_birth,s_email,s_schid)
<!--foreach:循环
collection:遍历集合 ***要能遍历
item:循环变量
separator: 每一次循环之间的分隔符
-->
<foreach collection="list" item="s" separator=" union all ">
(select #{s.name},#{s.age},#{s.birth},#{s.email},#{s.schid})
</foreach>
</insert>批量条件查询
StudentMapper
public List<Student> queryByIds(List<Integer> ids);StudentMapper.xml
<select id="queryByIds" resultMap="rMap_stu">
<include refid="sql1"></include>
<where>
<!--open:开始遍历之间添加的内容
close:结束遍历之后添加的内容-->
<foreach collection="list" item="id" separator="," open="id in (" close=")">
#{id}
</foreach>
</where>
</select>查询学生少带出学生的学校信息
StudentMapper
public Student queryStuByIdWithSch(int id);实体类
public class Student {
private int id;
private String name;
private int age;
private Date birth;
private String email;
private int schid;
//实体类 扔个学校对象
private School sch;
}
StudentMapper.xml
<resultMap id="rMap_stu1" type="stu">
<id property="id" column="s_id"></id>
<result property="name" column="s_name"></result>
<result property="age" column="s_age"></result>
<result property="birth" column="s_birth"></result>
<result property="email" column="s_email"></result>
<result property="schid" column="s_schid"></result>
<!--
property***在学生实体类引入的School实体类的对象属性
javaType属性的类型 ***给包起了别名,可以使用实体类小写,但推荐重头到尾写
-->
<!--
association一对一
-->
<association property="school" javaType="com.etoak.student.pojo.School">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="phone" column="phone"></result>
<result property="info" column="info"></result>
</association>
</resultMap>
<select id="queryStuByIdWithSch" resultMap="rMap_stu1">
select
s.s_id,s.s_name,s_age,s_birth,s_email,s_schid,
sch.id,sch.name,sch.phone,sch.info
from tb_stu s left join school sch on s.s_schid = sch.id
where s.s_id=#{id}
</select>查询学校捎带查出该学校学生的信息
StudentMapper
public School querySchByIdWithStu(int schid);实体类
public class School {
private int id;
private String name;
private String phone;
private int proid;
private int cityid;
private int areaid;
private String info;
//学校里边加多个学生
//List 、Set Collection
private Set<Student> stus;
}
StudentMapper.xml
<resultMap id="rMap_sch" type="com.etoak.student.pojo.School">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="phone" column="phone"></result>
<result property="info" column="info"></result>
<!--collection:代表集合 property:属性
ofType: 集合中的每一个元素的类型 ***可用别名-->
<collection property="stus" ofType="com.etoak.student.pojo.Student" javaType="java.util.Set">
<id property="id" column="s_id"></id>
<result property="name" column="s_name"></result>
<result property="age" column="s_age"></result>
<result property="birth" column="s_birth"></result>
<result property="email" column="s_email"></result>
<result property="schid" column="s_schid"></result>
</collection>
</resultMap>
<select id="querySchByIdWithStu1" resultMap="rMap_sch">
select
sch.id,sch.name,sch.phone,sch.info,
s.s_id,s.s_name,s_age,s_birth ,s_email,s_schid
from school sch left join tb_stu s on s.s_schid = sch.id
where sch.id=#{id}
</select>使用拦截器的方法实现真分页
导入依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.3.2</version>
</dependency>mybatis-config.xml设置拦截器
*********************************************
StudentMapper
public List<Student> queryAll();StudentMapper.xml
<select id="queryAll" resultMap="rMap_stu">
select * from tb_stu
</select>在service中使用在(service查询方法上面添加PageHelper.startPage(pageNumber,pageSize))
public List<Student> querySome(int pageNumber,int pageSize){
SqlSession session = null;
try{
session = SF.getSession();
StudentMapper dao = session.getMapper(StudentMapper.class);
//int start = (pageNumber-1)*pageSize;
//List<Student> stus = dao.querySome(start,pageSize);
//使用分页插件完成分页
PageHelper.startPage(pageNumber,pageSize);
List<Student> students = dao.queryAll();
session.commit();
return students;
}catch(Exception e){
e.printStackTrace();
if(session!=null)session.rollback();
return null;
}finally {
if(session!=null)session.close();
}
}
扫一扫
823
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
