MyBatis provides a variety of annotation mappings such as SELECT, UPDATE, INSERT, and DELETE. Let me take a closer look at the application of these mappings.
1.@Insert
We can use the @Insert annotation to declare an INSERT mapping.
Package com.owen.mybatis.mappers;
Public interface StudentMapper
{
@Insert(“INSERT INTO STUDENTS(STUD_ID, NAME, EMAIL, ADDR_ID, PHONE)
VALUES(#{studId},#{name},#{email},#{address.addrId},#{phone})”)
Int insertStudent(Student student);
}
In the insertStudent() method, we annotated @Insert, which will return the number of affected rows through the declaration of the insert.
When we used the xml file earlier, we declared the statement that automatically generated the primary key. In this annotation method, we can also use the @Options method to annotate the declaration to generate the primary key. This method contains the parameters of useGeneratedKeys and keyProperty. These two parameters are for the data to form the column value of auto_increment, the value of which is the column of an object in the existing column as the value.
@Insert(“INSERT INTO STUDENTS(NAME,EMAIL,ADDR_ID, PHONE)
VALUES(#{name},#{email},#{address.addrId},#{phone})”)
@Options(useGeneratedKeys=true, keyProperty=”studId”)
Int insertStudent(Student student);
The column of STUD_ID here will be automatically generated by the MYSQL database, and its value will be the same as the value of studId.
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
mapper.insertStudent(student);
Int studentId = student.getStudId();
Some databases, such as Oracle can not provide the AUTO_INCREMENT column and we need to use SEQUENCE to form the primary key. We can use the @Selectkey annotation to specify any SQL declaration, and it can be used as the value of the primary key.
@Insert(“INSERT INTO STUDENTS(STUD_ID, NAME, EMAIL, ADDR_ID, PHONE)
VALUES(#{studId},#{name},#{email},#{address.addrId},#{phone})”)
@SelectKey(statement=”SELECT STUD_ID_SEQ.NEXTVAL FROM DUAL”,
keyProperty=”studId”, resultType=int.class, before=true)
Int insertStudent(Student student);
Here we apply @SelectKey to generate the value of the primary key and save it in the attribute of the student’s studId. Because we defined before=true, the primary key was already generated when it was inserted.
If you use the trigger of SEQUENCE to generate the primary key, we can get the primary key from sequence_name.currval and then execute the insert statement.
@Insert(“INSERT INTO STUDENTS(NAME,EMAIL,ADDR_ID, PHONE)
VALUES(#{name},#{email},#{address.addrId},#{phone})”)
@SelectKey(statement=”SELECT STUD_ID_SEQ.CURRVAL FROM DUAL”,
keyProperty=”studId”, resultType=int.class, before=false)
Int insertStudent(Student student);
2.@Update
We can use the @Update annotation to declare an UPDATE.
@Update(“UPDATE STUDENTS SET NAME=#{name}, EMAIL=#{email},
PHONE=#{phone} WHERE STUD_ID=#{studId}”)
Int updateStudent(Student student);
In the updateStudent() method, we used the @Update annotation and will return the affected rows.
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Int noOfRowsUpdated = mapper.updateStudent(student);
3.@Delete
We can use @Delete to implement the declaration of DELETE.
@Delete(“DELETE FROM STUDENTS WHERE STUD_ID=#{studId}”)
Int deleteStudent(int studId);
The method of deleteStudent() will return the affected row.
4.@Select
We can use the @Select annotation to implement the SELECT mapping.
Package com.owen.mybatis.mappers;
Public interface StudentMapper
{
@Select(“SELECT STUD_ID AS STUDID, NAME, EMAIL, PHONE FROM
STUDENTS WHERE STUD_ID=#{studId}”)
Student findStudentById(Integer studId);
}
To match the instance of the Student object, we use studId as an alias for stud_id. How to return a value with multiple rows will report a TooManyResultException error.