fzhiy

Running all the time.

MyBatis——杂项、使用注解配置SQL映射器

点击量:11

MyBatis杂项

第一节 处理CLOB、BLOB类型数据

CLOB: 数据库表使用longtext,java中使用String;

BLOB:数据库表使用longblob,java中使用byte[];

Student.java

package com.fzhiy.entity;
​
public class Student {
    
    private Integer id;//自动生成
    private String name;
    private Integer age;
    private byte[] pic;
    private String remark;
    
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public Student() {
        super();
    }
    public Student(Integer id, String name, Integer age) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
    }
    public byte[] getPic() {
        return pic;
    }
    public void setPic(byte[] pic) {
        this.pic = pic;
    }
    public String getRemark() {
        return remark;
    }
    public void setRemark(String remark) {
        this.remark = remark;
    }
    public Student(String name, Integer age) {
        super();
        this.name = name;
        this.age = age;
    }
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", age=" + age + ", remark=" + remark + "]";
    }
}
​

StudentTest2.java

package com.fzhiy.test;
​
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
​
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
​
import com.fzhiy.dao.StudentDao;
import com.fzhiy.entity.Student;
import com.fzhiy.util.SqlSessionFactoryUtil;
​
public class StudentTest2 {
​
    private static Logger logger=Logger.getLogger(StudentTest2.class);
    private SqlSession sqlSession=null;
    private StudentDao studentDao=null;
    
    /**
     * 测试方法前调用
     * @throws Exception
     */
    @Before
    public void setUp() throws Exception {
        sqlSession=SqlSessionFactoryUtil.openSession();
        studentDao=sqlSession.getMapper(StudentDao.class);
    }
​
    /**
     * 测试方法后调用
     * @throws Exception
     */
    @After
    public void tearDown() throws Exception {
        sqlSession.close();
    }
​
    @Test
    public void testInsertStudent() {
        logger.info("添加学生");
        Student student=new Student();
        student.setName("张三4");
        student.setAge(14);
        student.setRemark("很长的文本");
        byte []pic = null;
        try {
            File file=new File("F://workSpaces//SoftEngine//Java EE//images//timg.jpg");
            InputStream inputStream=new FileInputStream(file);
            pic=new byte[inputStream.available()];
            inputStream.read(pic);
            inputStream.close();
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        student.setPic(pic);
        studentDao.insertStudent(student);
        sqlSession.commit();
    }
    
    @Test
    public void testGetStudnetById() {
        logger.info("通过ID查找学生");
        Student student=studentDao.getStudnetById(9);
        System.out.println(student);
        byte []pic=student.getPic();
        try {
            File file=new File("F://workSpaces//SoftEngine//Java EE//images//tmp2.jpg");
            OutputStream outputStream=new FileOutputStream(file);
            outputStream.write(pic);
            outputStream.close();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }
    
    @Test
    public void testSearchStudents6() {
        logger.info("查找学生(带条件)");
        List<Student> studentList=studentDao.searchStudents6("%张%", 12);
        for(Student student: studentList) {
            System.out.println(student);
        }
    }
}

 

第二节 传入多个输入参数

StudentDao.java

public List<Student> searchStudents6(String name, Integer age);

测试代码

    @Test
    public void testSearchStudents6() {
        logger.info("查找学生(带条件)");
        List<Student> studentList=studentDao.searchStudents6("%张%", 12);
        for(Student student: studentList) {
            System.out.println(student);
        }
    }

第三节 MyBatis分页

  1. 逻辑分页(一次查询然后利用offset和limit设置一页的个数)

  2. 物理分页(利用start和limit来实现分页)

StudentDao.java

public List<Student> findStudents(RowBounds rowBounds);
​
public List<Student> findStudents2(Map<String,Object>map);

StudentDao.xml

<resultMap type="Student" id="StudentResult">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
    </resultMap>
    
    <select id="findStudents" resultMap="StudentResult" flushCache="true" useCache="true">
        select * from t_student
    </select>
    
    <select id="findStudents2" parameterType="map" resultMap="StudentResult">
        select * from t_student
        <if test="start!=null and size!=null">
            limit #{start},#{size}
        </if>
    </select>

测试代码

    @Test
    public void testFindStudent() {
        logger.info("查询学生");
        int offset=0,limit=3;
        RowBounds rowBounds=new RowBounds(offset, limit);
        List<Student>studentList=studentDao.findStudents(rowBounds);
        for(Student student: studentList) {
            System.out.println(student);
        }
    }
    
    @Test
    public void testFindStudent2() {
        logger.info("查询学生");
        Map<String, Object>map=new HashMap<String, Object>();
        map.put("start", 3);
        map.put("size", 3);
        List<Student>studentList=studentDao.findStudents2(map);
        for(Student student: studentList) {
            System.out.println(student);
        }
    }

第四节 MyBatis缓存

mybatis默认情况下,启用一级缓存,即同一个SqlSession接口对象调用了相同的select语句,则直接会从缓存中返回结果,而不是再查询一次数据库;

开发者可以自己配置二级缓存,二级缓存是全局的;

默认情况下,select使用缓存的,insert update,delete是不适用缓存的。

<!-- 
        1.size: 表示缓存cache中能容纳的最大元素数,默认是1024
        2.flushInterval:定义缓存刷新周期,以毫秒计
        3.eviction:定义缓存的移除机制;默认是LRU,还有FIFO
        4。readOnly:默认是false,假如是true,缓存只能读
     -->
     <cache size="1024" flushInterval="60000" eviction="LRU" readOnly="false"/>

使用注解配置SQL映射器

第一节 基本映射语句

  1. @Insert

  2. @Update

  3. @Delete

  4. @Select

第二节 结果集映射语句

第三节 关系映射

  1. 一对一映射

  2. 一对多映射

AddressDao.java

package com.fzhiy.dao;
​
import org.apache.ibatis.annotations.Select;
​
import com.fzhiy.entity.Address;
​
public interface AddressDao {
    
    @Select("select * from t_address where id=#{id}")
    public Address findById(Integer id);
}
​

GradeDao.java

package com.fzhiy.dao;
​
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
​
import com.fzhiy.entity.Grade;
​
public interface GradeDao {
    
    @Select("select * from t_grade where id=#{id}")
    @Results({
        @Result(id=true, column="id", property="id"),
        @Result(column="gradeName",property="gradeName"),
        @Result(column="id",property="students", many=@Many(select="com.fzhiy.dao.StudentDao.selectStudentByGradeId"))
    }
        )
    public Grade findById(Integer id);
}
​

StudentDao.java

package com.fzhiy.dao;
​
import java.util.List;
​
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
​
import com.fzhiy.entity.Student;
​
public interface StudentDao {
​
    @Insert("insert into t_student values(null,#{name},#{age})")
    public int insertStudent(Student student);
    
    @Update("update t_student set name=#{name},age=#{age} where id=#{id}")
    public int updateStudent(Student student);
    
    @Delete("delete from t_student where id=#{id}")
    public int deleteStudent(Integer id);
    
    @Select("select * from t_student where id=#{id}")
    public Student getStudnetById(Integer id);
    
    @Select("select * from t_student")
    @Results({
                @Result(id=true, column="id", property="id"),
                @Result(column="name",property="name"),
                @Result(column="age", property="age")}
    )
    public List<Student> findStudents();
    
    @Select("select * from t_student where id=#{id}")
    @Results({
        @Result(id=true, column="id", property="id"),
        @Result(column="name",property="name"),
        @Result(column="age", property="age"),
        @Result(column="addressId",property="address",one=@One(select="com.fzhiy.dao.AddressDao.findById"))
    })
    public Student selectStudentWithAddress(Integer id);
    
    @Select("select * from t_student where id=#{id}")
    @Results({
        @Result(id=true, column="id", property="id"),
        @Result(column="name",property="name"),
        @Result(column="age", property="age"),
        @Result(column="addressId",property="address",one=@One(select="com.fzhiy.dao.AddressDao.findById"))
    })
    public Student selectStudentByGradeId(Integer gradeId);
    
    @Select("select * from t_student where id=#{id}")
    @Results({
        @Result(id=true, column="id", property="id"),
        @Result(column="name",property="name"),
        @Result(column="age", property="age"),
        @Result(column="addressId",property="address",one=@One(select="com.fzhiy.dao.AddressDao.findById")),
        @Result(column="gradeId",property="grade",one=@One(select="com.fzhiy.dao.GradeDao.findById"))
    })
    public Student selectStudentByAddressAndGrade(Integer gradeId);
}

第四节 动态SQL

  1. @InsertProvider

  2. @UpdateProvider

  3. @DeleteProvider

  4. @SelectProvider

StudentDao.java

package com.fzhiy.dao;
​
import java.util.List;
import java.util.Map;
​
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
​
import com.fzhiy.dao.impl.StudentDaoImpl;
import com.fzhiy.entity.Student;
​
public interface StudentDao {
​
    @InsertProvider(type=StudentDaoImpl.class,method="insertStudent")
    public int insertStudent(Student student);
    
    @UpdateProvider(type=StudentDaoImpl.class,method="updateStudent")
    public int updateStudent(Student student);
    
    @DeleteProvider(type=StudentDaoImpl.class,method="deleteStudent")
    public int deleteStudent(Integer id);
    
    @SelectProvider(type=StudentDaoImpl.class,method="getStudentById")
    public Student getStudnetById(Integer id);
    
    @SelectProvider(type=StudentDaoImpl.class,method="findStudents")
    public List<Student> findStudents(Map<String, Object>map);
    
}
​

StudentDaoImpl.java(使用Provider拼接SQL)

package com.fzhiy.dao.impl;
​
import java.util.Map;
​
import org.apache.ibatis.jdbc.SQL;
​
import com.fzhiy.entity.Student;
​
public class StudentDaoImpl {
    //动态拼接
    public String insertStudent(final Student student) {
        return new SQL() {
            {
                INSERT_INTO("t_student");
                if(student.getName()!=null) {
                    VALUES("name", "#{name}");
                }
                if(student.getAge()!=null) {
                    VALUES("age", "#{age}");
                }
            }
        }.toString();
    }
    
    public String updateStudent(final Student student) {
        return new SQL() {
            {
                UPDATE("t_student");
                if(student.getName()!=null) {
                    SET("name=#{name}");
                }
                if(student.getAge()!=null) {
                    SET("age=#{age}");
                }
                WHERE("id=#{id}");
            }
        }.toString();
    }
    
    public String deleteStudent() {
        return new SQL() {
            {
                DELETE_FROM("t_student");
                WHERE("id=#{id}");
            }
        }.toString();
    }
    
    public String getStudentById() {
        return new SQL() {
            {
                SELECT("*");
                FROM("t_student");
                WHERE("id=#{id}");
            }
        }.toString();
    }
    
    public String findStudents(final Map<String, Object>map) {
        return new SQL() {
            {
                SELECT("*");
                FROM("t_student");
                StringBuffer sBuffer=new StringBuffer();
                if(map.get("name")!=null) {
                    sBuffer.append(" and name like '" + map.get("name") + "'" );
                }
                if(map.get("age")!=null) {
                    sBuffer.append(" and age="+map.get("age"));
                }
                if (!sBuffer.toString().equals("")) {
                    WHERE(sBuffer.toString().replaceFirst("and", ""));
                }
            }
        }.toString();
    }
}
​

 

点赞

发表评论

电子邮件地址不会被公开。