四、MyBatis
1.需要导三个包：
数据库驱动包
mybatis-3.4.4.jar
log4j-1.2.16.jar+log4j.properties
2.创建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">
<!-- 在XML Catalog进行配置，网址映射到本地文件 -->
<configuration>
<!-- mybatis若取到空值，其类型为other，配置成NULL -->
<settings>
<setting name="jdbcTypeForNull" value="NULL" />
</settings>
<!-- 环境配置 -->
<environment default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521/lizi" />
<property name="username" value="SYSTEM" />
<property name="password" value="Xuelei123" />
</dataSource>
</environment>
</environment>
<mappers>
<mapper resource="cn/dlian/entities/EmpMapper.xml" />
</mappers>
</configuration>
3.Mappier.xml配置
作用：
连接数据库，并且设置
把所有的映射文件引用进来
采用#可以防止sql注入
<?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">
<!-- EmpMappier.xml -->
<!-- namespace作用是找到当前映射文件中的某个具体语句，其值唯一 -->
<mapper namespace="cn.dlian.entities.EmpMapper">
<!-- 查询语句 -->
<select id="getEmpById" parameterType="int" resultType="cn.dlian.entities.Emp">
select * from EMP where EMPNO = #{id}
</select>
<!--封装成一个对象，语句中写入其属性-->
<select id="getEmpsByJobAndSal" parameterType="cn.dlian.entities.Emp"
resultType="cn.dlian.entities.Emp">
select * from Emp where JOB=#{JOB} and SAL >#{SAL}
</select>
<!--将参数加入到Map中，传入Map,语句中写入key值-->
<select id="getEmpsBySalaryMinMax" parameterType="hashmap"
resultType="cn.dlian.entities.Emp">
select * from EMP where SAL between #{low} and #{high}
</select>
<select id="getEmpByMultiInfo" parameterType="cn.dlian.entities.Emp"
resultType="cn.dlian.entities.Emp">
select * from EMP
<where>
<if test="SAL != null">
SAL > #{SAL}
</if>
<if test="COMM != null">
and COMM > #{COMM}
</if>
</where>
</select>
<!-- 插入 -->
<insert id="insertEmp" parameterType="cn.dlian.entities.Emp">
inser into EMP values(#{EMPNO},#{ENAME},#{JOB},#{MGR},#{HIREDATE},#{SAL},#{COMM},#{DEPTNO})
</insert>
<!-- 删除 -->
<delete id="deleteEmpByID" parameterType="int">
delete from EMP where EMPNO = #{id}
</delete>
<!-- 修改 -->
<update id="updateEmp" parameterType="cn.dlian.entities.Emp">
update EMP set ENAME = #{ENAME},JOB=#{JOB}
</update>
</mapper>
4.MyBatisHelper
public class MyBatisHelper{
private static SqlSessionFactory sessionFactory;
static{
//此种方法加载一个文件，适用于jar包内!
InputStream is =
MyBatisHelper.class.getClassLoader().
getResourceAsStream("MyBatis-config.xml");
//通过配置文件(的输入流)，创建一个SessionFactory
sessinFactory = new SqlSessionFactoryBuilder().build(is);
}
public static SqlSession getSqlSessionFactory(){
//通过一个已经连接好数据库的工厂类，获取当前连接会话对形象(用于正删改查),用完请归还
return sessionFactory.openSession();
}
}
5.Dao层
public class EmpDaoImpl implements IEmpDao{
public Emp getEmpByID(int id){
//通过SessionFactory，获取当前session
SqlSession session = MyBatisHelper.getSqlSession();
//找到映射文件中的语句，并传递阐述，执行后获取返回结果！
String statement = "cn.dlian.entities.EmpMapper.getEmpById";
Emp emp = session.selectOne(statement,id);
session.close();//手动关闭一个session，释放资源
return emp;
}
//传递对象的属性
public List<Emp> getEmpsByJobAndSal(Emp emp){
SqlSession session = MyBatisHelper.getSqlSession();
String statement = "cn.dlian.entities.EmpMapper.getEmpsByJobAndSal";
List<Emp> emps = session.selectList(statement,emp);
session.close();
return emps;
}
//传递多个参数时，利用Map
public List<Emp> getEmpsBySalaryMinMax(float min,float max){
SqlSession session = MyBatisHelper.getSqlSession();
String statement = "cn.dlian.entities.EmpMapper.getEmpsBySalaryMinMax";
HashMap<String,Float> params = new HashMap<String,Float>();
params.put("low",min);
params.put("high",max);
List<Emp> emps = session.selectList<statement,params);
session.close();
return emps;
}
}
6.关系映射
<?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">
<!-- EmpMappier.xml -->
<!-- namespace作用是找到当前映射文件中的某个具体语句，其值唯一 -->
<mapper namespace="cn.dlian.entities.EmpMapper">
<!-- 1.多对一的映射，应当先定义出存放多对一结果的“结果类型”，MyBatis中称为“结果集“ -->
<!-- 一对一在java代码中其实就是多对一，只是外键在数据库中唯一，导致逻辑上为一对一 -->
<resultMap id="EmpWithDeptType" type="cn.dlian.entities.Emp">
<id property="EMPNO" column="EMPNO" />
<result property="ENAME" column="ENAME" />
<result property="JOB" column="JOB" />
<result property="MGR" column="MGR" />
<result property="HIREDATE" column="HIREDATE" />
<result property="SAL" column="SAL" />
<result property="COMM" column="COMM" />
<result property="DEPTNO" column="DEPTNO" />
<!-- 在一个类的内部，“绑定”另一个对象 -->
<association property="dept" column="DEPTNO" javaType="cn.dlian.entities.Dept">
<id property="DEPTNO" column="DEPTNO" />
<result property="DNAME" column="DNAME" />
<result property="LOC" column="LOC" />
</association>
</resultMap>
<select id="getEmpById" parameterType="int" resultMap="EmpWithDeptType">
select * from EMP join DEPT on EMP.DEPTNO = DEPT.DEPTNO where EMPNO = #{id}
</select>
<!-- 2.一对多映射，类似于多对一，也需要先定义一个“结果集” -->
<resultMap id="DeptWithEmpsType" type="cn.dlian.entities.Dept">
<id property="DEPTNO" column="DEPTNO" />
<result property="DNAME" column="DNAME" />
<result property="LOC" column="LOC" />
<collection property="emps" ofType="cn.dlian.entities.Emp">
<id property="EMPNO" column="EMPNO" />
<result property="ENAME" column="ENAME" />
<result property="JOB" column="JOB" />
<result property="MGR" column="MGR" />
<result property="HIREDATE" column="HIREDATE" />
<result property="SAL" column="SAL" />
<result property="COMM" column="COMM" />
<result property="DEPTNO" column="DEPTNO" />
</collection>
</resultMap>
<select id="getDeptWithEmpByID" parameterType="int" resultMap="DeptWithEmpsType">
select * from EMP join DEPT on EMP.DEPTNO = DEPT.DEPTNO where DEPT.DEPTNO = #{id}
</select>
</mapper>
<?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">
<!-- namespace作用是找到当前映射文件中的某个具体语句，其值唯一 -->
<mapper namespace="cn.dlian.entities.EmpMapper">
<!-- 多对多，可以分解成两个一对多 -->
<resultMap id="MusicWithUsersType" type="cn.dlian.entities.Music">
<id property="SONG_ID" column="SONG_ID" />
<result property="SONG_NAME" column="SONG_NAME" />
<result property="SONG_SINGER" column="SONG_SINGER" />
<result property="SONG_LENGTH" column="SONG_LENGTH" />
<collection property="users" ofType="cn.dlian.entities.UserInfo">
<id property="USER_ID" column="USER_ID" />
<result property="USER_NAME" column="USER_NAME" />
<result property="USER_PASS" column="USER_PASS" />
<result property="USER_ROLE" column="USER_ROLE" />
</collection>
</resultMap>
<select id="queryMusicWithUsersByID" parameterType="int"
resultMap="MusicWithUsersType">
select u.* , s.* from USER_INFO u
join COLLECTIONS c on u.USER_ID = c.USER_ID
join SONG_INFO s on c.SONG_ID = s.SONG_ID
where s.SONG_ID = #{id}
</select>
<resultMap id="UserWithMusicsType" type="cn.dlian.entities.UserInfo">
<id property="USER_ID" column="USER_ID" />
<result property="USER_NAME" column="USER_NAME" />
<result property="USER_PASS" column="USER_PASS" />
<result property="USER_ROLE" column="USER_ROLE" />
<collection property="musics" ofType="cn.dlian.entities.Music">
<id property="SONG_ID" column="SONG_ID" />
<result property="SONG_NAME" column="SONG_NAME" />
<result property="SONG_SINGER" column="SONG_SINGER" />
<result property="SONG_LENGTH" column="SONG_LENGTH" />
</collection>
</resultMap>
<select id="queryUserWithMusicByID" parameterType="int"
resultMap="UserWithMusicsType">
select u.* , s.* from USER_INFO u
join COLLECTIONS c on u.USER_ID = c.USER_ID
join SONG_INFO s on s.SONG_ID = s.SONG_ID
where u.USER_ID = #{id}
</select>
<!--尝试一次性完成多对多关系的映射（暂不完美）-->
<resultMap id="UserWithMusicsAndUsersType" type="cn.dlian.entities.UserInfo">
<id property="USER_ID" column="USER_ID" />
<result property="USER_NAME" column="USER_NAME" />
<result property="USER_PASS" column="USER_PASS" />
<result property="USER_ROLE" column="USER_ROLE" />
<collection property="musics" column="SONG_ID" selct="queryMusicWithUsersByID">
</collection>
</resultMap>
<select id="queryUsersWithMusicsWithUsers" resultMap="UserWithMusicsAndUsersType">
select u.* , s.* from USER_INFO u
join COLLECTIONS c on u.USER_ID = c.USER_ID
join SONG_INFO s on c.SONG_ID = s.SONG_ID
</select>
</mapper>
7.模糊查询
<?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">
<!-- EmpMappier.xml -->
<!-- namespace作用是找到当前映射文件中的某个具体语句，其值唯一 -->
<mapper namespace="cn.dlian.entities.EmpMapper">
<select id="queryEmpByName" parameterType="String"
resultType="cn.dlian.entities.Emp">
select * from EMP where ENAME like #{name}
</select>
</mapper>
public List<Emp> queryEmpByName(String name){
SqlSession session = MyBatisHelper.getSession();
String statement = "cn.dlian.entities.EmpMapper.queryEmpByName";
List<Emp> emps = session.selectList(statement,"%"+name+"%");
session.close();
return emps;
}