Mybatis
什么是Mybatis框架?
1 、mybatis就是一个封装来jdbc的持久层框架,它和hibernate都属于ORM框架,但是具体的说,hibernate是一个完全的orm框架,而mybatis是一个不完全的orm框架;
2 、Mybatis让程序员只关注sql本身,而不需要去关注如连接的创建、statement的创建等操作。
3、Mybatis会将输入参数、输出结果进行映射。
使用
1.下载所需的jar文件
2.编写Mybatis框架的核心配置文件
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="detabase.properties">
</properties>
<settings>
<!-- 打印sql日志到控制台上方便找错-->
<setting name="logImpl" value="STDOUT_LOGGING"></setting>
</settings>
<!--环境池 -->
<environments default="mysql">
<!--具体的环境 可以配置多个数据库 调用不同的id可以实现对数据库的随意访问-->
<environment id="mysql">
<!--事务管理器 -->
<transactionManager type="JDBC"></transactionManager>
<!--数据源-->
<dataSource type="POOLED">
<property name="driver" value="${diver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
<!-- 如果要写就要写完 不写完会报错-->
<!-- <environment id="Oracle">-->
<!-- <transactionManager type=""></transactionManager>-->
<!-- <dataSource type=""></dataSource>-->
<!-- </environment>-->
</environments>
<!--管理 xml -->
<mappers>
//调用那个xml进行增删改查就改成那个xml
<mapper resource="dao/BnanaMapper.xml"></mapper>
</mappers>
</configuration>
BnanaMapper.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">
<!-- 这个<mapper>标签就是用来映射接口 实现接口中方法的-->
<mapper namespace="dao.BananaMapper">
<!--resultType 返回值类型 id="count" 调用那个方法 -->
<select id="count" resultType="int">
select count(*)
from banana
</select>
<resultMap id="bananamap" type="entity.Banana">
<!--主键列用id标签 -->
<!-- <id property="bid" column="bid"></id>-->
<!-- <result property="bn" column="bname"></result>-->
<!-- <result property="bc" column="bcolor"></result>-->
<!-- 映射对象-->
<association property="p" javaType="entity.Place">
<id property="pid" column="pid"></id> <!-- 主键用id标签-->
<result property="pname" column="pname"></result>
</association>
<association property="b" javaType="entity.Bananatype">
<id property="tid" column="tid"></id> <!-- 主键用id标签-->
<result property="tname" column="tname"></result>
</association>
</resultMap>
<select id="getAll" resultMap="bananamap">
select *
from banana
join place join bananatype on banana.pid = place.pid and bananatype.tid=banana.tid
</select>
</mapper>
使用mybatis
package Test;
import dao.BananaMapper;
import entity.Banana;
import org.apache.ibatis.io.Resources;
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;
import java.util.HashMap;
import java.util.Map;
public class Test {
public static void main(String[] args) {
try {
//第一步:读取mybatis的核心配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//第二步:创建sqlSessionFactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(is);
//第三步:通过工厂生产一个具体的SqlSession
//JSP中的Session是一个网页端的会话 SqlSession是一个数据库会话
SqlSession session = ssf.openSession();
//第四步:调用 利用反射生成接口的对象(底层寻找到跟接口同名的xml映射文件来生成)
//反射的原理 就是当某个程序编译之后会生成一个class文件里面有改接口的详细信息 ,我们就利用反射机制取点里面的方法
//调用那个就是写了那个
BananaMapper bm = session.getMapper(BananaMapper.class);
for (Banana b:bm.getAll()
) {
System.out.println(b.getBname()+"~~"+b.getP().getPname()+"~~"+b.getB().getTname());
}
session.commit();
//第五步:关闭会话
session.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
增删改查语法
//查询
select *
from banana
where bname like CONCAT('%', #{name}, '%')
</select>
//新增
<insert id="insertAll" parameterType="entity.Work">
insert into work (wname, waddress, wage, wmoney, flag, wdate, jdate)
values (#{wname}, #{waddress}, #{wage}, #{wmoney}, #{flag}, #{wdate}, #{jdate});
</insert>
//删除
<delete id="DeleteAll" parameterType="int">
delete
from work
where wid = #{wid}
</delete>
//修改
<update id="Update" parameterType="entity.Work">
update work set wname=#{wname},waddress=#{waddress},wage=#{wage},wmoney=#{wmoney},flag=#{flag},wdate=#{wdate},jdate=#{jdate} where wid=#{wid}
</update>
BananaMapper的映射
如果进行多表查询那就不能这么写了,因为Mybatis会自动当前调用的实体类取查找对应的字段名,而难以避免字段有重复,系统无法分清那个是那个所有就需要映射指定他的对象(就是实体类,指定某个实体类去查找这样就没有重复字段根本就没有重复字段系统一下就识别到了),需要用到映射,什么是映射?
映射就是你表里面的列跟----类里面的属性按照同名映射
就跟你照镜子一样
当然也可以打破他默认的规则自己指定规则就是比如你想整容 那就是你自己的事情你整成什么样子都行是你自己的事情
映射实现
<?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>标签就是用来映射接口 实现接口中方法的-->
<mapper namespace="dao.FoodMapper">
<!--resultType 返回值类型 id="count" 调用那个方法 -->
<resultMap id="FoodMapper" type="entity.Food">
<!--主键列用id标签 -->
<id property="fid" column="fid"></id>
<result property="fname" column="fname"></result>
<result property="fprice" column="fprice"></result>
<result property="fcolor" column="fcolor"></result>
<result property="fsize" column="fsize"></result>
<result property="ftid" column="ftid"></result>
<!-- 映射对象-->
<association property="f" javaType="entity.Foodtype">
<id property="ftid" column="fid"></id> <!-- 主键用id标签-->
<result property="ftname" column="ftname"></result>
</association>
<association property="c" javaType="entity.Cook">
<id property="cid" column="cid"></id> <!-- 主键用id标签-->
<result property="cname" column="cname"></result>
<result property="ftid" column="ftid"></result>
</association>
</resultMap>
<select id="getAllPadd" resultMap="FoodMapper">
SELECT *
FROM `food`
JOIN cook
JOIN foodtype on food.ftid = cook.ftid AND foodtype.ftid = cook.ftid limit #{a}, #{b}
</select>
<select id="getCount" resultType="int">
select count(*)
from food
</select>
<select id="getAll" resultMap="FoodMapper">
SELECT *
FROM `food`
JOIN cook
JOIN foodtype on food.ftid = cook.ftid AND foodtype.ftid = cook.ftid
</select>
<select id="getConDitionAll" resultMap="FoodMapper">
SELECT *
FROM `food`
JOIN cook
JOIN foodtype on food.ftid = cook.ftid AND foodtype.ftid = cook.ftid WHERE cname=#{name} AND ftname=#{tname} AND fprice=#{price}
</select>
<select id="Sum" resultType="int">
SELECT fprice
FROM `food`
where fid=#{fid}
</select>
</mapper>
进阶
工具类
package util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class MyBatisUtil {
private static SqlSessionFactory factory;
static{
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
factory = new SqlSessionFactoryBuilder().build(is);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static SqlSession createSqlSession(){
return factory.openSession(true);//true 为自动提交事务
}
public static void closeSqlSession(SqlSession sqlSession){
if(null != sqlSession)
sqlSession.close();
}
}
自动映射
需要在config里加
<!-- 三种等级:不映射 普通映射 全部映射
普通映射就是你单表查就是普通映射
全部映射
<setting name="autoMappingBehavior" value="FULL"></setting>
-->
<setting name="autoMappingBehavior" value="FULL"></setting>
ForumMapper.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">
<!-- 这个<mapper>标签就是用来映射接口 实现接口中方法的-->
<mapper namespace="mapper.ForumMapper">
<cache></cache>
<resultMap id="map" type="entity.Posts">
<association property="u" javaType="entity.User"></association>
</resultMap>
</mapper>
自动映射调用 and 注解使用
@Select("<script>" +
"SELECT * FROM posts JOIN user on posts.uid=user.uid " + "<where> " +
" <if test=\"goodMin != null and goodMin !=-1 and goodMax != null and goodMax !=-1 \" >" +
"good \t>= #{goodMin,jdbcType=INTEGER} and good < #{goodMax,jdbcType=INTEGER} " +
" </if>" +
" <if test=\"BadMin != null and BadMin !=-1 and BadMax != null and BadMax !=-1 \" >" +
" and bad \t>= #{BadMin,jdbcType=INTEGER} and bad < #{BadMax,jdbcType=INTEGER} " +
" </if>" +
" <if test=\"goodMin != null and goodMin != -1 and goodMax==null \" >" +
"and good =#{goodMin,jdbcType=INTEGER} " +
" </if>" +
" <if test=\"goodMax != null and goodMax != -1 and goodMin == null \" >" +
" and good =#{goodMax,jdbcType=INTEGER} " +
" </if>" +
" <if test=\"BadMin != null and BadMin != -1 and BadMax==null \" >" +
"and bad =#{BadMin,jdbcType=INTEGER} " +
" </if>" +
" <if test=\"BadMax != null and BadMax != -1 and BadMin == null \" >" +
" and bad =#{BadMax,jdbcType=INTEGER} " +
" </if>" +
" <if test=\"title != null and title != ''\" >" +
" and ptitle =#{title} " +
" </if>" + " <if test=\"uid != null and uid != -1\" >" +
" and posts.uid =#{uid,jdbcType=INTEGER} " +
" </if>" +
" <if test=\"Bans != null and Bans !=-1 \" >" +
" and Bans= #{Bans,jdbcType=INTEGER}" +
" </if>" +
"</where>" +
"" +
"</script>")
@ResultMap("map")
缓存
config加
<!--开启会话缓存全局共享 ture开启 默认是flase 就是不共享-->
<setting name="cacheEnabled" value="true"/>
xml需要加
<cache></cache>
高级判断
修改set
实用于当你想修改一个或着多个值得时候使用
<update id="update" parameterType="entity.Food">
update food
<set>
<if test="fname!=null and fname!=''">
fname=#{fname}
</if>
<if test="fcolor!=null and fcolor!=''">
fcolor=#{fcolor}
</if>
</set>
</update>
foreach循环
实用于当你想使用数组或前台传过来得多个值获取数据得时候使用
<select id="getColor" resultType="entity.Food">
select * from food where fcolor
in
<foreach collection="color" item="p" open="(" separator="," close=")">
#{p}
</foreach>
</select>
where if判断
实用于你想判断多个值作为条件
<where>
<if test="caixi!=null and caixi!=''">cname = #{caixi}</if>
<if test="chushi!=null and chushi!=''">AND ftname = #{chushi}</if>
<if test="price!=null and price!=''">AND fprice = #{price}</if>
</where>