Mybatis总结

Mybatis总结1.mybatis的简单概述:1.1mybatis简单概述:MyBatis 是一款优秀的持久层框架，它支持定制化SQL、存储过程以及高级映射。

MyBatis 避免了几乎所有的JDBC 代码和手动设置参数以及获取结果集。

MyBatis 可以使用简单的XML 或注解来配置和映射原生信息，将接口和Java 的POJOs(Plain Old Java Objects,普通的Java对象)映射成数据库中的记录。

1.2 功能架构:分为三层：（1）.API接口层:定义api接口,外部人员通过这个接口来调用方法从而操纵数据库。

（2）.数据处理层:负责具体的SQL查找、SQL解析、SQL执行和执行结果映射处理等。

它主要的目的是根据调用的请求完成一次数据库操作。

（3）.基础支撑层：负责最基础的功能支撑，包括连接管理、事务管理、配置加载和缓存处理，这些都是共用的东西，将他们抽取出来作为最基础的组件。

为上层的数据处理层提供最基础的支撑。

2.mybatis快速入门:a. 添加jar包:mybatisasm-3.3.1.jarcglib-2.2.2.jarcommons-logging-1.1.1.jarlog4j-1.2.16.jarmybatis-3.1.1.jarslf4j-api-1.6.2.jarslf4j-log4j12-1.6.2.jarmysql驱动mysql-connector-java-5.1.7-bin.jarb. 建库+表：create database mybatis;use mybatis;CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT, NAMEVARCHAR(20), age INT);INSERT INTO users(NAME, age) VALUES('Tom', 12);INSERT INTO users(NAME, age) VALUES('Jack', 11);c. 在应用的src下添加Mybatis的配置文件conf.xml:<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE configuration PUBLIC "-////DTD Config 3.0//EN /dtd/mybatis-3-config.dtd"><configuration><environments default="development"><environment id="development"><transactionManager type="JDBC" /><dataSource type="POOLED"><property name="driver" value="com.mysql.jdbc.Driver" /><propert name="url value="jdbc:mysql://localhost:3306/mybatis" /><property name="username" value="root" /><property name="password" value="root" /></dataSource></environment></environments></configuration>d. 定义表所对应的实体类:public class User {private int id;private String name;private int age;}e. 定义操作users表的sql映射文件userMapper.xml:<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="org.mybatis.example.BlogMapper"><select id="selectBlog" parameterType="int" resultType="Blog">select * from Blog where id = #{id}</select></mapper>f. 在conf.xml文件中注册userMapper.xml文件:<mappers><mapper resource="com/atguigu/day03_mybatis/test1/userMapper.xml"/> </mappers>g. 编写测试代码：执行定义的select语句:public class Test {public static void main(String[] args) throws IOException {String resource = "conf.xml";//加载mybatis的配置文件（它也加载关联的映射文件）Reader reader = Resources.getResourceAsReader(resource);//构建sqlSession的工厂SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);//创建能执行映射文件中sql的sqlSessionSqlSession session = sessionFactory.openSession();//映射sql的标识字符串String statement = "erMapper"+".selectUser";//执行查询返回一个唯一user对象的sqlUser user = session.selectOne(statement, 1);System.out.println(user);}}3. 编写基于mybatis的操作users表的CRUD操作的dao类:a. XML的实现：1. 定义sql映射xml文件：<insert id="insertUser" parameterType="er"> insert into users(name, age) values(#{name}, #{age});</insert><delete id="deleteUser" parameterType="int">delete from users where id=#{id}</delete><update id="updateUser" parameterType="er"> update users set name=#{name},age=#{age} where id=#{id}</update><select id="selectUser" parameterType="int" resultType="er">select * from users where id=#{id}</select><select id="selectAllUsers" resultType="er"> select * from users</select>2. 在config.xml中注册这个映射文件<mapper resource="net/lamp/java/ibatis/bean/userMapper.xml"/>3. 在dao中调用：public User getUserById(int id) {SqlSession session = sessionFactory.openSession();User user = session.selectOne(URI+".selectUser", id);return user;}b. 注解的实现：1. 定义sql映射的接口public interface UserMapper {@Insert("insert into users(name, age) values(#{name}, #{age})")public int insertUser(User user);@Delete("delete from users where id=#{id}")public int deleteUserById(int id);@Update("update users set name=#{name},age=#{age} where id=#{id}")public int updateUser(User user);@Select("select * from users where id=#{id}")public User getUserById(int id);@Select("select * from users")public List<User> getAllUser();}2. 在config中注册这个映射接口<mapper class="erMapper"/>3. 在dao类中调用public User getUserById(int id) {SqlSession session = sessionFactory.openSession();UserMapper mapper = session.getMapper(UserMapper.class);User user = mapper.getUserById(id);return user;}4. 几个可以优化的地方:a. 连接数据库的几个配置可以单独放在一个properties文件中db.properties:<properties resource="db.properties"/><property name="driver" value="${driver}" /><property name="url" value="${url}" /><property name="username" value="${username}" /><property name="password" value="${password}" />b. 为实体类定义别名,简化sql映射xml文件中的引用<typeAliases><typeAlias type="er" alias="_User"/> </typeAliases>c. 可以在src下加入log4j的配置文件,打印日志信息(主要是sql语句)1. log4j.propertieslog4j.properties，log4j.rootLogger=DEBUG, Console#Consolelog4j.appender.Console=org.apache.log4j.ConsoleAppenderyout=org.apache.log4j.PatternLayoutyout.ConversionPattern=%d [%t] %-5p [%c] - %m%nlog4j.logger.java.sql.ResultSet=INFO.apache=INFOlog4j.logger.java.sql.Connection=DEBUGlog4j.logger.java.sql.Statement=DEBUGlog4j.logger.java.sql.PreparedStatement=DEBUG5. 解决表的字段名与实体类的属性名不相同的冲突:a. 准备表和数据：CREATE TABLE orders(order_id INT PRIMARY KEY AUTO_INCREMENT,order_no VARCHAR(20),order_price FLOAT);INSERT INTO orders(order_no, order_price) VALUES('aaaa', 23);INSERT INTO orders(order_no, order_price) VALUES('bbbb', 33);INSERT INTO orders(order_no, order_price) VALUES('cccc', 22);b. 定义实体类：public class Order {private int id;private String orderNo;private float price;}c. 实现getOrderById(id)的查询定义<resultMap>：<select id="selectOrderResultMap" parameterType="int" resultMap="orderResultMap"> select * from orders where order_id=#{id}</select><resultMap type="_Order" id="orderResultMap"><id property="id" column="order_id"/><result property="orderNo" column="order_no"/><result property="price" column="order_price"/></resultMap>6. 实现联表查询:1). 一对一：a. 创建表和数据：CREATE TABLE teacher(t_id INT PRIMARY KEY AUTO_INCREMENT,t_name VARCHAR(20));CREATE TABLE class(c_id INT PRIMARY KEY AUTO_INCREMENT,c_name VARCHAR(20),teacher_id INT);ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);INSERT INTO teacher(t_name) VALUES('LS1');INSERT INTO teacher(t_name) VALUES('LS2');INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);b. 定义实体类：public class Teacher {private int id;private String name;}public class Classes {private int id;private String name;private Teacher teacher;}c. 定义sql映射文件ClassMapper.xml：<!-- 方式一：嵌套查询方式, 通过执行另外一个SQL映射语句来返回预期的复杂类型--><select id="getClasses" parameterType="int"resultMap="ClassesResultMap2">select * from class where c_id=#{id}</select><resultMap type="CLasses" id="ClassesResultMap2"><id column="c_id" property="id"/><result column="c_name" property="name"/><association property="teacher" javaType="Teacher" column="teacher_id" select="getTeacher"></association></resultMap><select id="getT eacher" parameterType="int" resultType="Teacher">select t_id id, t_name name from teacher where t_id=#{id}</select><!--方式二：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集--><select id="getClasses2" parameterType="int" resultMap="ClassesResultMap"> select * from class c,teacher t where c.c_id=#{id} and c.teacher_id=t.t_id;</select><resultMap type="Classes" id="ClassesResultMap"><id column="c_id" property="id"/><result column="c_name" property="name"/><association column="teacher_id" property="teacher" javaType="Teacher"><id column="t_id" property="id"/><result column="t_name" property="name"/></association></resultMap>2) 一对多a. 创建表和数据：CREATE TABLE student(s_id INT PRIMARY KEY AUTO_INCREMENT,s_name VARCHAR(20),class_id INT);INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);INSERT INTO student(s_name, class_id) VALUES('xs_D', 1);INSERT INTO student(s_name, class_id) VALUES('xs_E', 1);INSERT INTO student(s_name, class_id) VALUES('xs_A', 2);INSERT INTO student(s_name, class_id) VALUES('xs_H', 2);INSERT INTO student(s_name, class_id) VALUES('xs_J', 2);b. 定义实体类：public class Student {private int id;private String name;}public class Classes {private int id;private String name;private Teacher teacher;private List<Student> students;}c. 定义sql映射文件ClassMapper.xml：(根据classId查询对应的班级信息,包括学生)<!-- 集合的嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集--><select id="getClasses3" parameterType="int" resultMap="ClassesResultMap3"> select * from class c,teacher t, student s where c.c_id=#{id} and c.teacher_id=t.t_id and s.class_id=c.c_id</select><resultMap type="Classes" id="ClassesResultMap3"><id column="c_id" property="id"/><result column="c_name" property="name"/><association column="teacher_id" property="teacher" javaType="Teacher"><id column="t_id" property="id"/><result column="t_name" property="name"/></association><collection property="students" ofType="Student" javaType="ArrayList"><id property="id" column="s_id" /><result property="name" column="s_name"/></collection></resultMap><!-- 集合的嵌套查询方式, 通过执行另外一个SQL映射语句来返回预期的复杂类型--><select id="getClasses4" parameterType="int" resultMap="ClassesResultMap4">select * from class c where c.c_id=#{id}</select><resultMap type="CLasses" id="ClassesResultMap4"><id column="c_id" property="id"/><result column="c_name" property="name"/><association property="teacher" javaType="Teacher" column="teacher_id" select="getTeacher"></association><collection property="students" ofType="T eacher" column="c_id" select="getStudentsSelect" ></collection></resultMap><select id="getT eacher" parameterType="int" resultType="Teacher">select t_id id, t_name name from teacher where t_id=#{id}</select><select id="getStudentsSelect" parameterType="int" resultType="Student" > select s_id id, s_name name from student where class_id=#{id} </select>。