DOCSEARCHbyAlgolia
Submit Docs

MyBatis SQL Mapper Framework

repository·master·Indexed 12 days ago

https://github.com/mybatis/mybatis-3

A lightweight SQL mapper framework for Java that simplifies interaction between object-oriented applications and relational databases by mapping Java objects to SQL statements or stored procedures using XML descriptors or Java annotations.

Tokens
41.9K
Snippets
112
Records
156
Agent score
96%

What's inside MyBatis

  1. What is MyBatis?

    master

    MyBatis is a first-class persistence framework for Java that provides support for custom SQL, stored procedures, and advanced mappings. It is designed to eliminate the boilerplate associated with JDBC, such as manual parameter setting and result retrieval.

    Key features include:

    • Configuration Options: Use either simple XML or Java Annotations.
    • Mapping Capabilities: Map database records to primitives, Map interfaces, and Java POJOs (Plain Old Java Objects).
    • SQL Control: Full support for custom SQL and stored procedures.

  2. Overview of MyBatis SQL Mapper Framework

    master
    MyBatis is a SQL mapper framework for Java designed to simplify the interaction between object-oriented applications and relational databases. Unlike heavy Object-Relational Mapping (ORM) tools, MyBatis focuses on simplicity by coupling Java objects directly with stored procedures or SQL statements. This mapping is achieved using either XML descriptors or Java annotations.

  3. Nested Results for Collections (Join approach)

    master

    You can map collections using a single SQL query with a LEFT OUTER JOIN. This is often more efficient than nested selects. Ensure you use unique column aliases in your SQL and map them using the column attribute in the <collection> element. The ofType attribute is mandatory to define the type of the collection elements.

    <select id="selectBlog" resultMap="blogResult">
  select
  B.id as blog_id,
  B.title as blog_title,
  B.author_id as blog_author_id,
  P.id as post_id,
  P.subject as post_subject,
  P.body as post_body
  from Blog B
  left outer join Post P on B.id = P.blog_id
  where B.id = #{id}
</select>

<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <collection property="posts" ofType="Post">
    <id property="id" column="post_id"/>
    <result property="subject" column="post_subject"/>
    <result property="body" column="post_body"/>
  </collection>
</resultMap>

  4. Handle complex relationships with Advanced Result Maps

    master

    MyBatis ResultMaps allow you to map complex, nested object models (joins) that standard JDBC cannot easily handle. Key components for advanced mapping include:

    • <constructor>: Used to map columns to constructor arguments.
    • <association>: Maps a single object relationship (e.g., a Blog has one Author).
    • <collection>: Maps a one-to-many relationship (e.g., a Blog has many Posts).
    • <discriminator>: Allows you to choose different mapping logic based on a specific column value (e.g., different types of Posts).
    <resultMap id="detailedBlogResultMap" type="Blog">
  <constructor>
    <idArg column="blog_id" javaType="int"/>
  </constructor>
  <result property="title" column="blog_title"/>
  
  <!-- One-to-One relationship -->
  <association property="author" javaType="Author">
    <id property="id" column="author_id"/>
    <result property="username" column="author_username"/>
  </association>
  
  <!-- One-to-Many relationship -->
  <collection property="posts" ofType="Post">
    <id property="id" column="post_id"/>
    <result property="subject" column="post_subject"/>
    
    <!-- Nested collection -->
    <collection property="comments" ofType="Comment">
      <id property="id" column="comment_id"/>
    </collection>
    
    <!-- Discriminator for different subtypes -->
    <discriminator javaType="int" column="draft">
      <case value="1" resultType="DraftPost"/>
    </discriminator>
  </collection>
</resultMap>

  5. Configure Multiple Environments

    master

    MyBatis supports multiple environments (e.g., Development, Test, Production) within a single configuration. This allows you to apply the same SQL Maps to different databases.

    Critical Constraint: You can only choose ONE environment per SqlSessionFactory instance. If you need to connect to multiple databases simultaneously, you must create a separate SqlSessionFactory for each database.

    // To build a factory with a specific environment:
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, environment);

// Or with environment and properties:
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader, environment, properties);

  6. Define Mapped SQL Statements via XML or Annotations

    master

    MyBatis supports two ways to define SQL mappings:

    XML Mapping

    Provides the full feature set of MyBatis, including complex mappings like Nested Join Mapping. Statements are identified by a namespace and an id.

    <mapper namespace="org.mybatis.example.BlogMapper">
  <select id="selectBlog" resultType="Blog">
    select * from Blog where id = #{id}
  </select>
</mapper>

    Annotation Mapping

    Ideal for simple SQL statements. You can use annotations like @Select directly on your Mapper interface methods.

    public interface BlogMapper {
  @Select("SELECT * FROM blog WHERE id = #{id}")
  Blog selectBlog(int id);
}

  7. Handle multi-db vendor support with _databaseId

    master

    If a databaseIdProvider is configured, you can use the built-in _databaseId variable to execute vendor-specific SQL logic within your mappings.

    <insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    </if>
    <if test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1
    </if>
  </selectKey>
  insert into users values (#{id}, #{name})
</insert>

  8. Use constructor injection for immutable nested results

    master

    Since MyBatis 3.6.0, you can use <constructor> mapping to inject both association and collection types. This is ideal for creating fully immutable object graphs.

    When using constructor mapping for nested results, MyBatis waits until the row is fully 'completed' before creating the object. This ensures that collections (like List<UserRole>) are fully populated before the parent object (like User) is instantiated.

    Important Requirements:

    1. Ordering: You must use the resultOrdered="true" attribute on the <select> statement.
    2. SQL Order: Your SQL query must include an ORDER BY clause that matches the grouping logic (e.g., ordering by the parent ID) to ensure MyBatis can correctly identify when a new main row begins.
    3. Consistency: It is recommended to use immutable constructor mappings for the entire hierarchy. Mixing property mappings with nested constructor mappings has limited support and may fail.

    Example Workflow:

    • Define a ResultMap for the child object using <constructor>.
    • Define a ResultMap for the parent object using <constructor>, referencing the child's resultMap via an <arg> element.
    <resultMap id="userResultMap" type="User">
  <constructor>
    <idArg column="id" javaType="int" />
    <arg column="username" javaType="String" />
    <arg javaType="List" resultMap="userRoleResultMap" columnPrefix="role_"/>
  </constructor>
</resultMap>

<resultMap id="userRoleResultMap" type="UserRole">
  <constructor>
    <idArg column="id" javaType="int" />
    <arg column="role" javaType="String" />
  </constructor>
</resultMap>

<select id="getAllUsers" resultMap="userResultMap" resultOrdered="true">
    select
      u.id,
      u.username,
      r.id as role_id,
      r.role as role_role
    from user u
      left join user_role ur on u.id = ur.user_id
      inner join role r on r.id = ur.role_id
    order by u.id, r.id
</select>

  9. Define reusable SQL fragments with the sql element

    master

    The <sql> element allows you to define reusable fragments of SQL code. These fragments can be parameterized and included in other statements using the <include> element.

    Static Parameterization

    You can pass properties into an <include> block to customize the fragment at runtime. This is useful for handling table aliases or dynamic column prefixes.

    Dynamic refid

    You can use properties within the refid attribute of the <include> tag to decide which SQL fragment to include dynamically.

    <!-- Define a reusable fragment -->
<sql id="userColumns">
  ${alias}.id, ${alias}.username, ${alias}.password
</sql>

<!-- Include the fragment with a property -->
<select id="selectUsers" resultType="map">
  select
    <include refid="userColumns">
      <property name="alias" value="t1"/>
    </include>
  from some_table t1
</select>

  10. Implement Nested Results for Associations

    master

    Instead of executing separate select statements (which can cause N+1 performance issues), you can use a single SQL JOIN to retrieve all data at once and map it into a nested object graph using resultMap within an <association> tag.

    Key Attributes for <association>:

    • resultMap: The ID of an external resultMap used to map the nested results. This allows for reusable mappings.
    • columnPrefix: Used when joining tables to avoid column name collisions. It allows you to map aliased columns to an external resultMap by applying a prefix.
    • notNullColumn: Specifies which columns must be non-null for MyBatis to create the child object. You can provide a comma-separated list of column names.
    • autoMapping: Enables or disables automapping for this property, overriding the global autoMappingBehavior.

    Best Practice: Use <id> elements When using Nested Result mapping, always specify one or more <id> elements to uniquely identify results. Omitting them causes a severe performance penalty during the decomposition of the ResultSet into the object graph.

    <!-- Using an external resultMap for the association -->
<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <association property="author" resultMap="authorResult" />
</resultMap>

<resultMap id="authorResult" type="Author">
  <id property="id" column="author_id"/>
  <result property="username" column="author_username"/>
  <result property="password" column="author_password"/>
  <result property="email" column="author_email"/>
  <result property="bio" column="author_bio"/>
</resultMap>

<select id="selectBlog" resultMap="blogResult">
  select
    B.id            as blog_id,
    B.title         as blog_title,
    B.author_id     as blog_author_id,
    A.id            as author_id,
    A.username      as author_username,
    A.password      as author_password,
    A.email         as author_email,
    A.bio           as author_bio
  from Blog B left outer join Author A on B.author_id = A.id
  where B.id = #{id}
</select>

  11. Property Loading Priority in MyBatis

    master

    When a property is defined in multiple locations, MyBatis resolves them using the following priority (highest to lowest):

    1. Method Parameters: Properties passed directly to the build() method.
    2. Resource/URL Attributes: Properties loaded from the classpath or url attributes within a <properties> element.
    3. Properties Body: Properties defined within the body of the <properties> element in the XML configuration.

  12. Configure the association element for 'has-one' relationships

    master

    The <association> element is used to map a "has-one" relationship (e.g., a Blog has one Author).

    Configuration Attributes

    • property: The field or property name in the target Java object. Supports complex navigation using dot notation (e.g., address.street.number).
    • javaType: The fully qualified Java class name or type alias.
    • jdbcType: The JDBC Type (required for nullable columns in write operations).
    • typeHandler: An override for the default TypeHandler.

    Loading Strategies

    1. Nested Select: Executes a separate SQL statement to load the associated object. Use the select attribute to specify the statement ID.
    2. Nested Results: Uses a joined ResultSet to populate the association. Use the resultMap attribute to specify a nested ResultMap that decomposes the joined data.
    <association property="author" javaType="Author">
  <id property="id" column="author_id"/>
  <result property="username" column="author_username"/>
</association>