DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Recover Distributed Transactions in MySQL
  • Enhancing Database Efficiency With MySQL Views: A Comprehensive Guide and Examples
  • Seamless Transition: Strategies for Migrating From MySQL to SQL Server With Minimal Downtime
  • How To Convert MySQL Database to SQL Server

Trending

  • Solid Testing Strategies for Salesforce Releases
  • Subtitles: The Good, the Bad, and the Resource-Heavy
  • Streamlining Event Data in Event-Driven Ansible
  • Cookies Revisited: A Networking Solution for Third-Party Cookies
  1. DZone
  2. Data Engineering
  3. Databases
  4. IBatis (MyBatis): Working with Dynamic Queries (SQL)

IBatis (MyBatis): Working with Dynamic Queries (SQL)

By 
Loiane Groner user avatar
Loiane Groner
·
Mar. 23, 11 · Tutorial
Likes (0)
Comment
Save
Tweet
Share
71.8K Views

Join the DZone community and get the full member experience.

Join For Free

this tutorial will walk you through how to setup ibatis ( mybatis ) in a simple java project and will present how to work with dynamic queries (sql).

pre-requisites

for this tutorial i am using:

ide: eclipse (you can use your favorite one)
database: mysql
libs/jars: mybatis , mysql conector and junit (for testing)

this is how your project should look like:

sample database

please run the script into your database before getting started with the project implementation. you will find the script (with dummy data) inside the sql folder.

1 – article pojo

i represented the pojo we are going to use in this tutorial with a uml diagram, but you can download the complete source code in the end of this article.

the goal of this tutorial is to demonstrate how to retrieve the article information from database using dynamic sql to filter the data.

2 – article mapper – xml

one of the most powerful features of mybatis has always been its dynamic sql capabilities. if you have any experience with jdbc or any similar framework, you understand how painful it is to conditionally concatenate strings of sql together, making sure not to forget spaces or to omit a comma at the end of a list of columns. dynamic sql can be downright painful to deal with.

while working with dynamic sql will never be a party, mybatis certainly improves the situation with a powerful dynamic sql language that can be used within any mapped sql statement.

the dynamic sql elements should be familiar to anyone who has used jstl or any similar xml based text processors. in previous versions of mybatis, there were a lot of elements to know and understand. mybatis 3 greatly improves upon this, and now there are less than half of those elements to work with. mybatis employs powerful ognl based expressions to eliminate most of the other elements.

  • if
  • choose (when, otherwise)
  • trim (where, set)
  • foreach

let’s explain each one with examples.

1 – first scenario : we want to retrieve all the articles from database with an optional filter: title. in other words, if user specify an article title, we are going to retrieve the articles that match with the title, otherwise we are going to retrieve all the articles from database. so we are going to implement a condition (if) :

<select id="selectarticlebytitle" parametertype="com.loiane.model.article"    resulttype="article">    select id, title, author    from article    where id_status = 1    <if test="title != null">        and title like #{title}    </if></select>

2 – second scenario : now we have two optional filters: article title and author. the user can specify both, none or only one filter. so we are going to implement two conditions:

<select id="selectarticlebytitleandauthor" parametertype="com.loiane.model.article"    resulttype="article">    select id, title, author    from article    where id_status = 1    <if test="title != null">        and title like #{title}    </if>    <if test="author != null">        and author like #{author}    </if></select>

3 – third scenario : now we want to give the user only one option: the user will have to specify only one of the following filters: title, author or retrieve all the articles from ibatis category. so we are going to use a choose element:

<select id="selectarticlebytitleorauthororcategory"    parametertype="com.loiane.model.article" resulttype="article">    select id, title, author    from article    where id_status = 1    <choose>        <when test="title != null">            and title like #{title}        </when>        <when test="author != null">            and author like #{author}        </when>        <otherwise>            and id_category = 3        </otherwise>    </choose></select>

4 – fourth scenario : take a look at all three statements above. they all have a condition in common: where id_status = 1. it means we are already filtering the active articles let’s remove this condition to make it more interesting.

<select id="selectarticlebytitleandauthor" parametertype="com.loiane.model.article"    resulttype="article">    select id, title, author    from article    where    <if test="title != null">        title like #{title}    </if>    <if test="author != null">        and author like #{author}    </if></select>

what if both title and author are null? we are going to have the following statement:

select id, title, authorfrom articlewhere

and what if only the author is not null? we are going to have the fololwing statement:

select id, title, authorfrom articlewhereand author like #{author}

and both fails!

how to fix it?

5 – fifth scenario : we want to retrieve all the articles with two optional filters: title and author. to avoid the 4th scenatio, we are going to use a where element:

<select id="selectarticlebytitleandauthordynamic" parametertype="com.loiane.model.article"    resulttype="article">    select id, title, author    from article    <where>        <if test="title != null">            title like #{title}        </if>        <if test="author != null">            and author like #{author}        </if>    </where></select>

mybatis has a simple answer that will likely work in 90% of the cases. and in cases where it doesn’t, you can customize it so that it does.

the where element knows to only insert “where” if there is any content returned by the containing tags. furthermore, if that content begins with “and” or “or”, it knows to strip it off.

if the where element does not behave exactly as you like, you can customize it by defining your own trim element. for example,the trim equivalent to the where element is:

<select id="selectarticlebytitleandauthordynamic2" parametertype="com.loiane.model.article"    resulttype="article">    select id, title, author    from article    <trim prefix="where" prefixoverrides="and |or ">        <if test="title != null">            title like #{title}        </if>        <if test="author != null">            and author like #{author}        </if>    </trim></select>

the overrides attribute takes a pipe delimited list of text to override, where whitespace is relevant. the result is the removal of anything specified in the overrides attribute, and the insertion of anything in the with attribute.

you can also use the trim element with set.

6 – sixth scenario : the user will choose all the categories an article can belong to. so in this case, we have a list (a collection), and we have to interate this collection and we are going to use a foreach element:

<select id="selectarticlebytitleandauthordynamic2" parametertype="com.loiane.model.article"    resulttype="article">    select id, title, author    from article    <trim prefix="where" prefixoverrides="and |or ">        <if test="title != null">            title like #{title}        </if>        <if test="author != null">            and author like #{author}        </if>    </trim></select>

the foreach element is very powerful, and allows you to specify a collection, declare item and index variables that can be used inside the body of the element. it also allows you to specify opening and closing strings, and add a separator to place in between iterations. the element is smart in that it won’t accidentally append extra separators.

note : you can pass a list instance or an array to mybatis as a parameter object. when you do, mybatis will automatically wrap it in a map, and key it by name. list instances will be keyed to the name “list” and array instances will be keyed to the name “array”.

the complete article.xml file looks like this:

<?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="article">     <select id="selectarticlebytitle" parametertype="com.loiane.model.article"     resulttype="article">        select id, title, author        from article        where id_status = 1        <if test="title != null">            and title like #{title}        </if>    </select>     <select id="selectarticlebytitleandauthor" parametertype="com.loiane.model.article"     resulttype="article">        select id, title, author        from article        where id_status = 1        <if test="title != null">            and title like #{title}        </if>        <if test="author != null">            and author like #{author}        </if>    </select>     <select id="selectarticlebytitleorauthororcategory" parametertype="com.loiane.model.article"     resulttype="article">        select id, title, author        from article        where id_status = 1        <choose>            <when test="title != null">                and title like #{title}            </when>            <when test="author != null">                and author like #{author}            </when>            <otherwise>                and id_category = 3            </otherwise>        </choose>    </select>      <select id="selectarticlebytitleandauthordynamic" parametertype="com.loiane.model.article"     resulttype="article">        select id, title, author        from article        <where>            <if test="title != null">                title like #{title}            </if>            <if test="author != null">                and author like #{author}            </if>        </where>    </select>     <select id="selectarticlebytitleandauthordynamic2" parametertype="com.loiane.model.article"     resulttype="article">        select id, title, author        from article        <trim prefix="where" prefixoverrides="and |or ">            <if test="title != null">                title like #{title}            </if>            <if test="author != null">                and author like #{author}            </if>        </trim>    </select>     <select id="selectarticlebylistcategories" resulttype="article">        select id, title, author        from article        where id_category in        <foreach item="category" index="index" collection="list"            open="(" separator="," close=")">            #{category}        </foreach>    </select></mapper>

download

if you want to download the complete sample project, you can get it from my github account: https://github.com/loiane/ibatis-dynamic-sql

if you want to download the zip file of the project, just click on download:

there are more articles about ibatis to come. stay tooned!

happy coding! :)

from http://loianegroner.com/2011/03/ibatis-mybatis-working-with-dynamic-queries-sql/

sql Database MyBatis MySQL Element

Opinions expressed by DZone contributors are their own.

Related

  • Recover Distributed Transactions in MySQL
  • Enhancing Database Efficiency With MySQL Views: A Comprehensive Guide and Examples
  • Seamless Transition: Strategies for Migrating From MySQL to SQL Server With Minimal Downtime
  • How To Convert MySQL Database to SQL Server

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!