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、数据库传值#{} 和’ ′ p a r a m e t e r T y p e 解 决 方 法 : s e l e c t i d , n a m e , a g e f r o m s t u d e n t w h e r e n a m e = ′ {}'parameterType 解决方法:select id,name, age from student where name = ' ′parameterType解决方法:selectid,name,agefromstudentwherename=′{value}’
9、数据库传值#{} 和’ ′ 1 、 {}' 1、 ′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缓存