{"id":4049,"date":"2024-03-13T07:51:45","date_gmt":"2024-03-13T07:51:45","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/"},"modified":"2025-07-31T00:57:53","modified_gmt":"2025-07-31T00:57:53","slug":"how-to-use-the-dynamic-sql-tags-in-mybatis","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/","title":{"rendered":"MyBatis Dynamic SQL Tags: Ultimate Guide"},"content":{"rendered":"

MyBatis’s dynamic SQL tags allow us to dynamically generate different SQL fragments based on conditions in our SQL statements, enabling more flexible queries.<\/p>\n

Here are some examples of using MyBatis dynamic SQL tags:<\/p>\n

    \n
  1. if tag: generate SQL fragments based on conditions.<\/li>\n<\/ol>\n
    <select<\/span> id<\/span>=\"selectUsers\"<\/span> resultType<\/span>=\"User\"<\/span>><\/span>\r\n    SELECT id, username, email\r\n    FROM users\r\n    <where<\/span>><\/span>\r\n        <if<\/span> test<\/span>=\"username != null\"<\/span>><\/span>\r\n            AND username = #{username}\r\n        <\/if<\/span>><\/span>\r\n        <if<\/span> test<\/span>=\"email != null\"<\/span>><\/span>\r\n            AND email = #{email}\r\n        <\/if<\/span>><\/span>\r\n    <\/where<\/span>><\/span>\r\n<\/select<\/span>><\/span>\r\n<\/code><\/pre>\n
      \n
    1. select SQL snippet based on condition\u8fdb\u884c\u9009\u62e9\u4e0d\u540c\u7684SQL\u7247\u6bb5<\/li>\n<\/ol>\n
      <select<\/span> id<\/span>=\"selectUsers\"<\/span> resultType<\/span>=\"User\"<\/span>><\/span>\r\n    SELECT id, username, email\r\n    FROM users\r\n    <where<\/span>><\/span>\r\n        <choose<\/span>><\/span>\r\n            <when<\/span> test<\/span>=\"username != null\"<\/span>><\/span>\r\n                AND username = #{username}\r\n            <\/when<\/span>><\/span>\r\n            <when<\/span> test<\/span>=\"email != null\"<\/span>><\/span>\r\n                AND email = #{email}\r\n            <\/when<\/span>><\/span>\r\n            <otherwise<\/span>><\/span>\r\n                AND id > 0\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. “foreach tag: used to iterate through a collection to build SQL fragments”<\/li>\n<\/ol>\n
        <select<\/span> id<\/span>=\"selectUsersByIds\"<\/span> resultType<\/span>=\"User\"<\/span>><\/span>\r\n    SELECT id, username, email\r\n    FROM users\r\n    WHERE id IN\r\n    <foreach<\/span> collection<\/span>=\"ids\"<\/span> item<\/span>=\"id\"<\/span> index<\/span>=\"index\"<\/span> open<\/span>=\"(\"<\/span> separator<\/span>=\",\"<\/span> close<\/span>=\")\"<\/span>><\/span>\r\n        #{id}\r\n    <\/foreach<\/span>><\/span>\r\n<\/select<\/span>><\/span>\r\n<\/code><\/pre>\n
        These are the commonly used dynamic SQL tags in MyBatis, which can easily facilitate flexible SQL queries. For more information on how to use MyBatis dynamic SQL tags, please refer to the official documentation.<\/p>\n","protected":false},"excerpt":{"rendered":"
        MyBatis’s dynamic SQL tags allow us to dynamically generate different SQL fragments based on conditions in our SQL statements, enabling more flexible queries. Here are some examples of using MyBatis dynamic SQL tags: if tag: generate SQL fragments based on conditions. <select id=”selectUsers” resultType=”User”> SELECT id, username, email FROM users <where> <if test=”username != null”> […]<\/p>\n","protected":false},"author":11,"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":[766,1467,1459,2742,3191],"class_list":["post-4049","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-database-queries","tag-dynamic-sql","tag-mybatis","tag-mybatis-tutorial","tag-sql-tags"],"yoast_head":"\nMyBatis Dynamic SQL Tags: Ultimate Guide - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Master MyBatis dynamic SQL tags for flexible database queries. Learn if\/where tags usage with code examples and best practices.\" \/>\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-the-dynamic-sql-tags-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 Tags: Ultimate Guide\" \/>\n<meta property=\"og:description\" content=\"Master MyBatis dynamic SQL tags for flexible database queries. Learn if\/where tags usage with code examples and best practices.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-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:51:45+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-07-31T00:57:53+00:00\" \/>\n<meta name=\"author\" content=\"Olivia Parker\" \/>\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=\"Olivia Parker\" \/>\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-the-dynamic-sql-tags-in-mybatis\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/\"},\"author\":{\"name\":\"Olivia Parker\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9\"},\"headline\":\"MyBatis Dynamic SQL Tags: Ultimate Guide\",\"datePublished\":\"2024-03-13T07:51:45+00:00\",\"dateModified\":\"2025-07-31T00:57:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/\"},\"wordCount\":101,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"keywords\":[\"Database Queries\",\"dynamic SQL\",\"MyBatis\",\"MyBatis tutorial\",\"SQL tags\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/\",\"name\":\"MyBatis Dynamic SQL Tags: Ultimate Guide - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-13T07:51:45+00:00\",\"dateModified\":\"2025-07-31T00:57:53+00:00\",\"description\":\"Master MyBatis dynamic SQL tags for flexible database queries. Learn if\/where tags usage with code examples and best practices.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MyBatis Dynamic SQL Tags: Ultimate 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\/3ff7b3da0e45ac5dbbef2502f3cea8d9\",\"name\":\"Olivia Parker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g\",\"caption\":\"Olivia Parker\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/oliviaparker\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"MyBatis Dynamic SQL Tags: Ultimate Guide - Blog - Silicon Cloud","description":"Master MyBatis dynamic SQL tags for flexible database queries. Learn if\/where tags usage with code examples and best practices.","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-the-dynamic-sql-tags-in-mybatis\/","og_locale":"en_US","og_type":"article","og_title":"MyBatis Dynamic SQL Tags: Ultimate Guide","og_description":"Master MyBatis dynamic SQL tags for flexible database queries. Learn if\/where tags usage with code examples and best practices.","og_url":"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-13T07:51:45+00:00","article_modified_time":"2025-07-31T00:57:53+00:00","author":"Olivia Parker","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Olivia Parker","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/"},"author":{"name":"Olivia Parker","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/3ff7b3da0e45ac5dbbef2502f3cea8d9"},"headline":"MyBatis Dynamic SQL Tags: Ultimate Guide","datePublished":"2024-03-13T07:51:45+00:00","dateModified":"2025-07-31T00:57:53+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/"},"wordCount":101,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"keywords":["Database Queries","dynamic SQL","MyBatis","MyBatis tutorial","SQL tags"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/","url":"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/","name":"MyBatis Dynamic SQL Tags: Ultimate Guide - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-13T07:51:45+00:00","dateModified":"2025-07-31T00:57:53+00:00","description":"Master MyBatis dynamic SQL tags for flexible database queries. Learn if\/where tags usage with code examples and best practices.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-to-use-the-dynamic-sql-tags-in-mybatis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MyBatis Dynamic SQL Tags: Ultimate 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\/3ff7b3da0e45ac5dbbef2502f3cea8d9","name":"Olivia Parker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/56c66f189ba32a6f9eb50f31a38fe774e2a725c213d4070835ccc51b8fbbc54b?s=96&d=mm&r=g","caption":"Olivia Parker"},"url":"https:\/\/www.silicloud.com\/blog\/author\/oliviaparker\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/4049","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\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=4049"}],"version-history":[{"count":2,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/4049\/revisions"}],"predecessor-version":[{"id":148711,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/4049\/revisions\/148711"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=4049"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=4049"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=4049"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}