MyBatis Integration
Sometimes you need full control over your SQL queries. Maybe you have complex queries, legacy database, or need specific optimizations. MyBatis is a persistence framework that gives you that control while still simplifying database access.
Unlike JPA which hides SQL, MyBatis lets you write your own SQL and maps results to Java objects. It's like having a manual transmission car – more work, but more control.
MyBatis Components:
- Mapper Interface – Java interface defining database operations.
- XML Mapper or Annotations – Where you write your SQL queries.
- SqlSessionFactory – Creates database sessions.
Here is how to set up MyBatis with Spring:
@Configuration
public class MyBatisConfig {
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setTypeAliasesPackage("com.example.entity");
return sessionFactory.getObject();
}
@Bean
public MapperScannerConfigurer mapperScannerConfigurer() {
MapperScannerConfigurer scanner = new MapperScannerConfigurer();
scanner.setBasePackage("com.example.mapper");
return scanner;
}
}
Method 1: Using Annotations
public interface StudentMapper {
@Select("SELECT * FROM students WHERE id = #{id}")
@Results({
@Result(property = "id", column = "id"),
@Result(property = "studentName", column = "name"),
@Result(property = "emailAddress", column = "email")
})
Student findById(int id);
@Select("SELECT * FROM students")
List findAll();
@Insert("INSERT INTO students(name, email, age) VALUES(#{name}, #{email}, #{age})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert(Student student);
@Update("UPDATE students SET name = #{name}, email = #{email}, age = #{age} WHERE id = #{id}")
int update(Student student);
@Delete("DELETE FROM students WHERE id = #{id}")
int deleteById(int id);
// Dynamic SQL with script
@Select("")
List search(@Param("name") String name, @Param("minAge") Integer minAge);
}
Method 2: Using XML Mapper (for complex queries)
Create StudentMapper.xml:
<?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.example.mapper.StudentMapper">
<resultMap id="studentResultMap" type="Student">
<id property="id" column="id"/>
<result property="studentName" column="name"/>
<result property="emailAddress" column="email"/>
<result property="studentAge" column="age"/>
</resultMap>
<select id="findById" resultMap="studentResultMap">
SELECT * FROM students WHERE id = #{id}
</select>
<select id="findAll" resultMap="studentResultMap">
SELECT * FROM students
</select>
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
INSERT INTO students(name, email, age) VALUES(#{name}, #{email}, #{age})
</insert>
<update id="update">
UPDATE students
SET name = #{name}, email = #{email}, age = #{age}
WHERE id = #{id}
</update>
<delete id="deleteById">
DELETE FROM students WHERE id = #{id}
</delete>
<select id="search" resultMap="studentResultMap">
SELECT * FROM students
<where>
<if test="name != null and name != ''">
AND name LIKE CONCAT('%', #{name}, '%')
</if>
<if test="minAge != null">
AND age >= #{minAge}
</if>
</where>
</select>
</mapper>
Using the mapper in service:
@Service
public class StudentService {
@Autowired
private StudentMapper studentMapper;
public Student getStudent(int id) {
return studentMapper.findById(id);
}
public void addStudent(Student student) {
studentMapper.insert(student);
}
public List searchStudents(String name, Integer minAge) {
return studentMapper.search(name, minAge);
}
}
Two Minute Drill
- MyBatis gives you full control over SQL while handling mapping.
- Use annotations for simple queries, XML for complex/dynamic SQL.
- @Select, @Insert, @Update, @Delete for CRUD operations.
- @Results maps database columns to Java fields.
- Dynamic SQL with
tags in XML or
Need more clarification?
Drop us an email at career@quipoinfotech.com