{"id":4098,"date":"2024-03-13T07:56:18","date_gmt":"2024-03-13T07:56:18","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/"},"modified":"2025-07-31T01:34:46","modified_gmt":"2025-07-31T01:34:46","slug":"how-to-use-dynamic-sql-in-mybatis","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/","title":{"rendered":"MyBatis Dynamic SQL Guide"},"content":{"rendered":"

\nDynamic SQL in MyBatis is a way to dynamically generate SQL statements based on conditions, allowing for the creation of different SQL statements depending on the conditions, enabling dynamic queries. Here are some common methods for using dynamic SQL in MyBatis:<\/p>\n

    \n
  1. Conditions are added to SQL statements using tags, so that the corresponding SQL statement is only executed when the condition is met. Here is an example code:<\/li>\n<\/ol>\n
    <select<\/span> id<\/span>=\"getUserList\"<\/span> parameterType<\/span>=\"map\"<\/span> resultType<\/span>=\"User\"<\/span>><\/span>\r\n    SELECT * FROM user\r\n    <where<\/span>><\/span>\r\n        <if<\/span> test<\/span>=\"name != null\"<\/span>><\/span>\r\n            AND name = #{name}\r\n        <\/if<\/span>><\/span>\r\n        <if<\/span> test<\/span>=\"age != null\"<\/span>><\/span>\r\n            AND age = #{age}\r\n        <\/if<\/span>><\/span>\r\n    <\/where<\/span>><\/span>\r\n<\/select<\/span>><\/span>\r\n<\/code><\/pre>\n
      \n
    1. We can use if-else statements and labels to achieve similar functionality to a switch-case statement. Below is an example code:<\/li>\n<\/ol>\n
      <select<\/span> id<\/span>=\"getUserList\"<\/span> parameterType<\/span>=\"map\"<\/span> resultType<\/span>=\"User\"<\/span>><\/span>\r\n    SELECT * FROM user\r\n    <where<\/span>><\/span>\r\n        <choose<\/span>><\/span>\r\n            <when<\/span> test<\/span>=\"name != null\"<\/span>><\/span>\r\n                AND name = #{name}\r\n            <\/when<\/span>><\/span>\r\n            <when<\/span> test<\/span>=\"age != null\"<\/span>><\/span>\r\n                AND age = #{age}\r\n            <\/when<\/span>><\/span>\r\n            <otherwise<\/span>><\/span>\r\n                AND gender = #{gender}\r\n            <\/otherwise<\/span>><\/span>\r\n        <\/choose<\/span>><\/span>\r\n    <\/where<\/span>><\/span>\r\n<\/select<\/span>><\/span>\r\n<\/code><\/pre>\n
        \n
      1. Use tags to remove unnecessary parts of the SQL statements. Example code is shown below:<\/li>\n<\/ol>\n
        <select<\/span> id<\/span>=\"getUserList\"<\/span> parameterType<\/span>=\"map\"<\/span> resultType<\/span>=\"User\"<\/span>><\/span>\r\n    SELECT * FROM user\r\n    <where<\/span>><\/span>\r\n        <trim<\/span> prefix<\/span>=\"AND\"<\/span> prefixOverrides<\/span>=\"AND\"<\/span>><\/span>\r\n            <if<\/span> test<\/span>=\"name != null\"<\/span>><\/span>\r\n                AND name = #{name}\r\n            <\/if<\/span>><\/span>\r\n            <if<\/span> test<\/span>=\"age != null\"<\/span>><\/span>\r\n                AND age = #{age}\r\n            <\/if<\/span>><\/span>\r\n        <\/trim<\/span>><\/span>\r\n    <\/where<\/span>><\/span>\r\n<\/select<\/span>><\/span>\r\n<\/code><\/pre>\n
          \n
        1. Tags are used to perform looping operations, typically for constructing conditions for IN statements. Example code is as follows:<\/li>\n<\/ol>\n
          <select<\/span> id<\/span>=\"getUserList\"<\/span> parameterType<\/span>=\"map\"<\/span> resultType<\/span>=\"User\"<\/span>><\/span>\r\n    SELECT * FROM user\r\n    WHERE id IN\r\n    <foreach<\/span> collection<\/span>=\"ids\"<\/span> item<\/span>=\"id\"<\/span> open<\/span>=\"(\"<\/span> close<\/span>=\")\"<\/span> separator<\/span>=\",\"<\/span>><\/span>\r\n        #{id}\r\n    <\/foreach<\/span>><\/span>\r\n<\/select<\/span>><\/span>\r\n<\/code><\/pre>\n
          By using the above methods, you can dynamically generate different SQL statements based on conditions to achieve dynamic query functionality. For more information and examples of how to use dynamic SQL in MyBatis, refer to the official MyBatis documentation.<\/p>\n","protected":false},"excerpt":{"rendered":"
          Dynamic SQL in MyBatis is a way to dynamically generate SQL statements based on conditions, allowing for the creation of different SQL statements depending on the conditions, enabling dynamic queries. Here are some common methods for using dynamic SQL in MyBatis: Conditions are added to SQL statements using tags, so that the corresponding SQL statement […]<\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[494,1467,2688,1459,1845],"class_list":["post-4098","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-database-optimization","tag-dynamic-sql","tag-java-persistence","tag-mybatis","tag-sql-queries"],"yoast_head":"\nMyBatis Dynamic SQL Guide - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Learn to use dynamic SQL in MyBatis for conditional queries. See practical examples with tags for flexible SQL generation.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MyBatis Dynamic SQL Guide\" \/>\n<meta property=\"og:description\" content=\"Learn to use dynamic SQL in MyBatis for conditional queries. See practical examples with tags for flexible SQL generation.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-13T07:56:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-07-31T01:34:46+00:00\" \/>\n<meta name=\"author\" content=\"Liam\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Liam\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/\"},\"author\":{\"name\":\"Liam\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/23786905eb7b377f45ddb01c17da7671\"},\"headline\":\"MyBatis Dynamic SQL Guide\",\"datePublished\":\"2024-03-13T07:56:18+00:00\",\"dateModified\":\"2025-07-31T01:34:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/\"},\"wordCount\":165,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"keywords\":[\"database optimization\",\"dynamic SQL\",\"Java persistence\",\"MyBatis\",\"SQL Queries\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/\",\"name\":\"MyBatis Dynamic SQL Guide - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-13T07:56:18+00:00\",\"dateModified\":\"2025-07-31T01:34:46+00:00\",\"description\":\"Learn to use dynamic SQL in MyBatis for conditional queries. See practical examples with tags for flexible SQL generation.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MyBatis Dynamic SQL Guide\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/23786905eb7b377f45ddb01c17da7671\",\"name\":\"Liam\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g\",\"caption\":\"Liam\"},\"sameAs\":[\"http:\/\/Wilson\"],\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/liamwilson\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"MyBatis Dynamic SQL Guide - Blog - Silicon Cloud","description":"Learn to use dynamic SQL in MyBatis for conditional queries. See practical examples with tags for flexible SQL generation.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/","og_locale":"en_US","og_type":"article","og_title":"MyBatis Dynamic SQL Guide","og_description":"Learn to use dynamic SQL in MyBatis for conditional queries. See practical examples with tags for flexible SQL generation.","og_url":"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-13T07:56:18+00:00","article_modified_time":"2025-07-31T01:34:46+00:00","author":"Liam","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Liam","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/"},"author":{"name":"Liam","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/23786905eb7b377f45ddb01c17da7671"},"headline":"MyBatis Dynamic SQL Guide","datePublished":"2024-03-13T07:56:18+00:00","dateModified":"2025-07-31T01:34:46+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/"},"wordCount":165,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"keywords":["database optimization","dynamic SQL","Java persistence","MyBatis","SQL Queries"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/","url":"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/","name":"MyBatis Dynamic SQL Guide - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-13T07:56:18+00:00","dateModified":"2025-07-31T01:34:46+00:00","description":"Learn to use dynamic SQL in MyBatis for conditional queries. See practical examples with tags for flexible SQL generation.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-dynamic-sql-in-mybatis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MyBatis Dynamic SQL Guide"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/23786905eb7b377f45ddb01c17da7671","name":"Liam","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/8d37ed3e7f770dde8bf069ba0b4298688028c3abaacf1131742fc1352d174ebd?s=96&d=mm&r=g","caption":"Liam"},"sameAs":["http:\/\/Wilson"],"url":"https:\/\/www.silicloud.com\/blog\/author\/liamwilson\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/4098","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=4098"}],"version-history":[{"count":2,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/4098\/revisions"}],"predecessor-version":[{"id":148759,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/4098\/revisions\/148759"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=4098"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=4098"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=4098"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}