Mybatis

Mybatis搭建

1、Mybatis 安装

2、第一个mybatis搭建
1、导入jar包
Mybatis-3.46.Jar
mysql-connector
2、创建映射类Student
3、创建映射表Student
4、创建personMapper.xml
细节1导入该配置xml路径:
细节2:查询到返回类型路径，和根据什么查询的类型
5、创建conf.xml
细节1：写入数据库连接
细节2：加载personMapper.xml查询文件
6、读入Reader reader=Resources.getResourceAsReader(“conf.xml”);
7、查询SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader) ;
//session - connection

SqlSession session = sessionFactory.openSession() ;
String statement = "org.lsj.entity.personMapper.queryPersonById" ;

3、conf.xml和personMapper.xml了解
数据库信息default设置

4、Statement方式的增删改查
1、personMaper.xml配置


select * from Student where id = #{id}



insert into student(id,name,age) values(#{id},#{name},#{age}} )



delete from student where id = #{id}



update student set age=#{age} ,name=#{name} where id=#{id}



select * from student

5、mapper动态的增删改查
1、StudentMapper.java 和studentMapper.xml
public interface StudentMapper与一致5
2、

6、mapper优化
1、属性文件db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/MyBatis
username=root
password=
在con.xml加入
2、全局参数(缓存)
3、别名（conf.xml里面配置）



4、类型转换器
1、studentMapper.xml



2、实现类继承
@Override
public Boolean getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
// TODO Auto-generated method stub
int sexNum = cs.getInt(columnIndex) ;//rs.getInt(1)
return sexNum == 1?true:false ;
}
3、写增加数据库行
4、设置主键
5、测试
7、如果数据属性和eclipse属性没有相同
解决方法:

select * from student where id = #{id}

8、数据库传值#{} 和’${}^{\prime }parameterType解决方法:selectid,name,agefromstudentwherename{=}^{\prime }${value}’

9、数据库传值#{} 和’${}^{\prime }1、${} 好处降序排序

10、级联写法输入值

select id,name,age,sex from student
where homeAddress = #{address.homeAddress} or schoolAddress =’${address.schoolAddress}’

11、hashmap

select id,name,age from student
where age= #{age} or name like ‘%${name}%’

12、mybatis 存储过程
输入：1、查询个年龄的学生总数
输出:查询年纪的学生总数
create procedure pre_student( IN aage int, OUT scount INT)
begin
select count(1) into scount from student where age=aage;
end //

delimiter //
create procedure pre_name1( IN hadr varchar(10),OUT scount INT)
begin
select count(1) into scount from student where homeaddress=hadr ;
end //
解决方法:

{CALL pre_name1(#{hadr,jdbcType=VARCHAR,mode=IN},#{scount,jdbcType=INTEGER,mode=OUT}) }

13、根据Id删除学生

delimiter //
create procedure pre_deid( IN iid int)
begin
delete from student where id =iid;
end //
delimiter ;
解决方法:

	<!-- 通过存储过程实现删除 -->
<delete id="deleteStuBynoWithProcedure" statementType="CALLABLE" parameterType="HashMap">
	{
		CALL pre_deid(
			#{iid,jdbcType=INTEGER,mode=IN}
		) 
	}	
</delete>

14、输出参数resultType 和resultMap


select * from student where id = ${value}



select count(*) from student

15、动态sql

select * from student



#{id}




16、数组、集合、open: item : close :的使用

select * from student



#{student.id}

17、SQL片段





#{student.id}

<!-- 25、将多个元素值 放入对象数组中Student[] students = {student0,student1,student2}  每个studentx包含一个学号属性 -->
<select id="queryStudentsWithObjectArray"  parameterType="Object[]" resultType="student">
  	select * from student 
  	 <include refid="objectArrayStunos"></include>
</select>

18、关联查询
1、一对一 a、业务扩展类

解决方法
1、建表、建立外键
	 create table studentcard(carid int(20) primary key,cardinfo varchar(20));
 	alter tables student add carid varchar(20);
	alter table studentcard change carid cardid int(20);
	alter table studentcard modify cardid varchar(20);
	alter table student modify cardid varchar(20);
	alter table student add constraint fk_student_card foreign key(cardid) references studentcard(cardid);
	select s.*,c.* from student s join studentcard c on s.cardid =c.cardid  where s.id=#{id}
	<!-- 27、关联查询也就是多表查询 -->
	<select id="queryStudentByNoWithOO" 	parameterType="int"  	resultType="StudentBusiness" >
	select student.*,studentcard.* from student ,studentcard where student.cardid=studentcard.cardid and student.id=#{id}
	</select>	
利用resultMap
<select id="queryStudentByNoWith002" 	parameterType="int"  	resultMap="studentcardmap" >
		<!-- select * from student where id=#{id} -->
		<!-- select student.*,studentcard.* from student,studentcard where student.cardid =student.cardid and student.id=#{id} -->
		select student.*,studentcard.* from student ,studentcard where student.cardid=studentcard.cardid and student.id=#{id}
	</select>
	<resultMap type="student" id="studentcardmap">
		<!-- 学生信息 -->
		<id property="id"  column="id"  />
		<result property="name"  column="name" />
		<result property="age"  column="age" />
		<!-- 一对一映射， -->
		<association property="card" javaType="StudentCard">
			<id property="cardid"  column="cardid"  />
			<result property="cardinfo"  column="cardinfo" />
		</association>
</resultMap>
2、一对多
alter table student add constraint fk_student_class foreign key(classid) references studentclass(classid);
select student.*,studentclass.* from student ,studentclass where student.classid=studentclass.classid and studentclass.classid=1；

19、延迟加载
1、配置



select * from studentCard where cardid = #{cardId}

2、加入.conf.xml配置
<mapper resource="org/lsj/mapper/studentCardMapper.xml"/>
3、开启延迟加载
<settings>
	<!-- 开启延迟加载 -->
	<setting name="lazyLoadingEnabled" value="true"/>
	
	<!-- 关闭立即加载 -->
	<setting name="aggressiveLazyLoading" value="false"/>
</settingfs>
4、
	<!-- 30、延迟加载 -->
<select id="queryStudentWithOO2LazyLoad" 	parameterType="int"  	resultMap="student_card_lazyLoad_map" >
	<!-- 先查学生 -->
	select * from student 
</select>
<resultMap type="student" id="student_card_lazyLoad_map">
		<!-- 学生的信息 -->
		<id  property="id" column="id"/>
		<result property="name" column="name" />
		<result property="age" column="age" />
	
		<!-- 学生证  ,通过select 在需要的时候再查学生证 -->
		<association property="card" javaType="StudentCard"  select="org.lsj.mapper.StudentCardMapper.queryCardById"  column="cardid"  >
			<!-- <id property="cardId" column="cardId"/>             
				<result property="cardInfo" column="cardInfo"/>   -->	
		</association>
	</resultMap>

20、查询一级缓存、二级缓存

21、禁用缓存

22、整合Ehcache缓存