1, Configure the environment variable moven
2, MyBatis
Getting started steps
1. Introduce dependency
2. Write entity user Dao
User write field get set toString method
Methods defined in UserDao
3. Write UserMapper in restore
<mapper namespace="com.qcby.dao.UserDao">
<select id="findAll" resultType="com.qcby.entity.User">
select * from user
</select>
</mapper>
The mapper tag is written into the interface, and the select tag finds the id method name and resultType output parameters, in which the query statement is written
4. Create test class
public class UserTest {
private InputStream in = null;
private SqlSession session = null;
private UserDao mapper = null;
@Before //Pre notification, executed before method execution
public void init() throws IOException {
//The main configuration file is loaded to build the SqlSessionFactory object
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//Create SqlSessionFactory object
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//Create a sqlsession object from the SqlSessionFactory factory object
session = factory.openSession();
//Create UserDao interface proxy object through Session
mapper = session.getMapper(UserDao.class);
}
@After //@After: Post notification, which is executed after the method is executed.
public void destory() throws IOException {
//Release resources
session.close();
in.close();
}
}
@Test
public void findAll() throws IOException {
List<User> users = mapper.findAll();
for (User user:users) {
System.out.println(user.toString());
}
}
5. What's better about mybatis than jdbc
1. Compared with JBDC, it eliminates a large number of redundant codes in JDBC and does not require manual switch connection
2. The sql statement of mybatis is written in the xml file. Changing the sql statement does not need to be recompiled
3, MyBatis implements addition, deletion, modification and query
Write to Mapper label
There are four Tags: select insert delete update. The id of each tag corresponds to the method name. resultType is the input parameter, parameterType is the output parameter, and resultMap
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qcby.Dao.UserDao">
<select id="findAll" resultType="com.qcby.entity.User">
select * from user
</select>
<select id="findById" resultType="com.qcby.entity.User" parameterType="java.lang.Integer">
select * from user where id = #{id}
</select>
<insert id="insert" parameterType="com.qcby.entity.User">
insert into user(username,birthday,sex,address)
values(#{username},#{birthday},#{sex},#{address})
</insert>
<delete id="delete" parameterType="java.lang.Integer">
delete from user where id = #{id}
</delete>
<update id="update" parameterType="com.qcby.entity.User">
update user set username = #{username},birthday = #{birthday},
sex = #{sex},address = #{address} where id = #{id}
</update>
<!--Return primary key: our primary key needs to be automatically incremented -->
<insert id="insertGetId" parameterType="com.qcby.entity.User">
<selectKey keyProperty="id" resultType="int" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
insert into user(username,birthday,sex,address)
values(#{username},#{birthday},#{sex},#{address})
</insert>
<!--${}:Splicing , #{} precompiled -- >
<select id="likeByName" resultType="com.qcby.entity.User" parameterType="java.lang.String">
select * from user where username like '%${value}%';
</select>
</mapper>
Test get javabean, mapper Method output, which is basically consistent with the above test
set up mapping Layer mapping
<resultMap id="ResultMap" type="com.qcby.entity.User">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="username" property="username" jdbcType="VARCHAR"/>
<result column="birthday" property="birthday" jdbcType="VARCHAR"/>
<result column="sex" property="sex" jdbcType="VARCHAR"/>
<result column="address" property="address" jdbcType="VARCHAR"/>
</resultMap>
<select id="findAlResultMap" resultMap="ResultMap">
select * from user
</select>
Look at the output of #{} and ${}
#The {} statement printed on the console is?, Assign later
${} print the value directly on the console, and directly splice sql statements, which may cause sql injection problems
4, mybatis dynamic SQL
It mainly refers to the sql statements configured in mapper. Different sql statements are used in different situations
1,if
<select id="selectUserByUsernameAndSex" parameterType="com.qcby.entity.User"
resultType="com.qcby.entity.User">
select * from user
<where>
<if test="username != null">
username=#{username}
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</where>
</select>
Use the where tag and if tag to add and from the second if tag, because compilation will automatically eliminate and
2,set if
<update id="update" parameterType="com.qcby.entity.User">
update user
<set>
<if test="username !=null and username!=''">
username = #{username} ,
</if>
<if test="address != null and address != ''">
address = #{address}
</if>
</set>
where id = #{id}
</update>
For update splicing
3,choose when otherwish
<select id="selectUserByChoose" resultType="com.qcby.entity.User"
parameterType="com.qcby.entity.User">
select * from user
<where>
<choose>
<when test="id !='' and id != null">
id=#{id}
</when>
<when test="username !='' and username != null">
and username=#{username}
</when>
<otherwise>
and sex=#{sex}
</otherwise>
</choose>
</where>
</select>
Execute a when in the choose first. If one is true, do not judge the later. If none of the when is true, execute otherwise
4,trim
<trim prefix="where" prefixOverrides="and | or">
<if test="username != null">
and username=#{username}
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</trim>
The write type in prefix can be changed into any label
5, mybatis association mapping
Suppose that the tables of students and teachers are many to one, that is, one teacher teaches multiple students, and each student has only one teacher
1. The first form: nested processing according to query
Query the teacher corresponding to each Student, Student's table and teacher where t_ Table with id = #{t#u ID}
public class Student {
private Integer id;
private String Sname;
private String sex;
private Integer age;
private Integer t_id;
//This is the point
private Teacher teacher;
}
public class Teacher {
private Integer id;
private String Tname;
}
Create two classes and write fields, and write set, get and toString methods for each field, where student t_id corresponds to the id of the teacher
<!-- Many to one query: query the teacher corresponding to each student-->
<!--The first form: nested processing according to query-->
<!--
1.Query all student information
2.According to the query t_id,Find the corresponding teacher
-->
<!-- Note that the student entity class must have the object of the teacher class to realize the association query-->
<!-- resultMap:Returns the associated result map-->
<select id = "getStudent" resultMap="StudentTeacher">
select * from student;
</select>
<!--Result mapping set-->
<resultMap id="StudentTeacher" type="com.qcby.entity.Student">
<result property="id" column="id"/>
<result property="Sname" column="Sname"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<result property="t_id" column="t_id"/>
<!-- For complex properties, we need to deal with objects separately:association Set: collection -->
<!-- property="teacher" student Associated fields in class -->
<!-- column="t_id" Associated fields of two tables-->
<!-- javaType="com.javen.model.Teacher" Set class type for complex properties-->
<!-- select="getTeacher" :Call the next query statement -->
<association property="teacher" column="t_id" javaType="com.qcby.entity.Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="com.qcby.entity.Teacher">
select * from teacher where id = #{t_id}; <!-- #{id}; You can write anything because it will automatically match t_ id -->
</select>
The map is used for association. The resultMap contains the result field. The general field is written with the result tag property colum n, and the complex object uses the association tag. The complex property attribute is the field in Student, column is the field associated with the two tables, javaType is the full class name, and select is the following select attribute called
2. The second form: nested query according to results (more commonly used)
sql statement to directly investigate and deal with the required tables
SELECT student.id,student.name,teacher.name FROM student
LEFT JOIN teacher on student.t_id = teacher.id
Writing in mapper
<!-- Nested processing according to results-->
<select id="getStudent1" resultMap="StudentTeacher1">
SELECT student.id,student.Sname,teacher.Tname FROM student LEFT JOIN teacher on student.t_id = teacher.id
</select>
<resultMap id="StudentTeacher1" type="com.qcby.entity.Student">
<result property="id" column="id"/>
<result property="Sname" column="Sname"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<result property="t_id" column="t_id"/>
<association property="teacher" javaType="com.qcby.entity.Teacher">
<result property="id" column="id"/>
<result property="Tname" column="Tname"/>
</association>
</resultMap>
Using map mapping, write ordinary fields and complex fields Association in resultMap, write fields in Teacher class in association, and javaType is the full class name