{"id":15495,"date":"2018-08-28T14:34:59","date_gmt":"2018-08-28T12:34:59","guid":{"rendered":"https:\/\/blog.trifork.com\/?p=15495"},"modified":"2018-08-28T14:34:59","modified_gmt":"2018-08-28T12:34:59","slug":"spring-data-native-queries-and-projections-in-kotlin","status":"publish","type":"post","link":"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/","title":{"rendered":"Spring Data Native Queries and Projections in Kotlin"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-15509\" src=\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-1024x214.png\" alt=\"Koltin, Spring Boot and JPA\" width=\"1024\" height=\"214\" srcset=\"https:\/\/trifork.nl\/blog\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-1024x214.png 1024w, https:\/\/trifork.nl\/blog\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-300x63.png 300w, https:\/\/trifork.nl\/blog\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-768x160.png 768w, https:\/\/trifork.nl\/blog\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa.png 1265w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/p>\n<p>This blog describes the solution to mapping native queries to objects. This is useful because sometimes you want to use a feature of the underlying database implementation (such as PostgreSQL) that is not part of the JPQL standard. By the end of this blog you should be able to confidently use native queries and use their outcome in a type-safe way.<\/p>\n<p>In creating great applications based on Machine Learning solutions, we often come across uses for frameworks and databases that aren&#8217;t exactly standard. We sometimes need to build functionality that is either so new or so specific that it hasn&#8217;t been adopted into JPA implementations yet.<\/p>\n<p>Working on a project with <a href=\"http:\/\/projects.spring.io\/spring-data\/\">Spring Data<\/a> is usually simple albeit somewhat opaque. Write a repository, annotate methods with @Query annotation and presto! You have mapped your database entities to Kotlin objects. Especially since Spring Framework 5 many of the interoperability issues (such as nullable values that are never null) have been alleviated.<\/p>\n<p>Confucius wrote &#8220;Real knowledge is to know the extent of one&#8217;s ignorance&#8221;. So, to gauge the extent of our ignorance, let&#8217;s have a look at what happens when we cannot use the JPA abstraction layer in full and instead need to work with native queries.<\/p>\n<h2>Setting up the entity<\/h2>\n<p>When you use non-JPA features of the underlying database store, things can become complex.<br \/>\nLet\u2019s say we have the following PostgreSQL table for storing people:<\/p>\n<pre>CREATE TABLE person (\n  id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,\n  first_name VARCHAR(20),\n  last_name VARCHAR(20)\n);<\/pre>\n<p><!--more-->Given we represent an individual person like this:<\/p>\n<pre>import javax.persistence.Entity\nimport javax.persistence.GeneratedValue\nimport javax.persistence.Id\nimport javax.persistence.Table<\/pre>\n<pre>@Entity\n@Table(name = \"person\")\nclass PersonEntity {\n  @Id\n  @GeneratedValue\n  var id: Long? = null\n  var firstName: String? = null\n  var lastName: String? = null\n}<\/pre>\n<p>We can access that using a Repository:<\/p>\n<pre>import org.springframework.data.jpa.repository.JpaRepository\nimport org.springframework.stereotype.Repository<\/pre>\n<pre>@Repository interface PersonRepo : JpaRepository&lt;PersonEntity, Long&gt;<\/pre>\n<p>We could now implement a custom query on the repository as follows:<\/p>\n<pre>@Repository interface PersonRepo : JpaRepository&lt;PersonEntity, Long&gt; {\n\n  @Query(\"FROM PersonEntity WHERE first_name = :firstName\")\n  fun findAllByFirstName(@Param(\"firstName\") firstName: String):\n    List&lt;PersonEntity&gt;\n}<\/pre>\n<p>So far so good. It uses JPQL syntax to form database-agnostic queries which is nice because we get some validation of these queries when starting the application, plus the added benefit of the syntax being database-type ignorant.<\/p>\n<h2>Adding a native query<\/h2>\n<p>Sometimes however, we want to use syntax that is specific to the database that we are using. We can do that by adding the boolean <code>nativeQuery<\/code> attribute to the <code>@Query<\/code> annotation and using Postgres\u2019 SQL instead of JPQL:<\/p>\n<pre>  @Query(\"SELECT first_name, random() AS luckyNumber FROM person\",\n    nativeQuery = true)\n  fun getPersonsLuckyNumber(): LuckyNumberProjection?<\/pre>\n<p>Obviously this example is simple for the sake of this context, more practical applications are in the area of using the extra data types that Postgres offers such as the cube data type for storing matrices.<\/p>\n<p>You may be, as I was at first, tempted to write a class for LuckyNumberProjection.<\/p>\n<pre>class LuckyNumberProjection {\n  var firstName: String? = null\n  var luckyNumber: Float? = null\n}<\/pre>\n<p>You will run cause into the following error:<\/p>\n<pre>org.springframework.core.convert.ConverterNotFoundException: No converter found\ncapable of converting from type\n[org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap]\nto type\n[com.trifork.machinelearning.PersonRepo$LuckyNumberProjection]<\/pre>\n<p>The accompanying stack trace points in the direction of converters. This then makes you need to add a converter. However that doesn\u2019t seem like it should be as hard. Good for us it turns out it isn\u2019t!<\/p>\n<p>Turns out that contrary to Entities, Projections, like Repositories, are expected to be interfaces. So let\u2019s do that instead:<\/p>\n<pre>interface LuckyNumberProjection {\n  val firstName: String?\n  val luckyNumber: Float\n}<\/pre>\n<p>This should set you straight next time you want to get custom objects mapped out of your JPA queries.<\/p>\n<p>At Trifork Amsterdam, we are currently doing multiple projects using Kotlin using frameworks such as Spring Boot, Axon Framework and Project Reactor on top of Kubernetes clusters using Helm to build small and smart microservices. More and more of those microservices contain our Machine Learning based solutions. These are in a variety of areas ranging from natural language processing (NLP) to time-series analysis and clustering data for recommender systems and predictive monitoring.<\/p>\n\n\n<figure class=\"wp-block-image\"><a href=\"https:\/\/bit.ly\/3BAo305\" target=\"_blank\" rel=\"noreferrer noopener\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"256\" src=\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png\" alt=\"\" class=\"wp-image-20303\" srcset=\"https:\/\/trifork.nl\/blog\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1024x256.png 1024w, https:\/\/trifork.nl\/blog\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-300x75.png 300w, https:\/\/trifork.nl\/blog\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-768x192.png 768w, https:\/\/trifork.nl\/blog\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1536x384.png 1536w, https:\/\/trifork.nl\/blog\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-2048x512.png 2048w, https:\/\/trifork.nl\/blog\/wp-content\/uploads\/sites\/3\/2022\/02\/Blog-Banner-1-1920x480.png 1920w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>This blog describes the solution to mapping native queries to objects. This is useful because sometimes you want to use a feature of the underlying database implementation (such as PostgreSQL) that is not part of the JPQL standard. By the end of this blog you should be able to confidently use native queries and use [&hellip;]<\/p>\n","protected":false},"author":17,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"content-type":"","footnotes":""},"categories":[113,337,465,10,94],"tags":[11,466,439,467,126,285],"class_list":["post-15495","post","type-post","status-publish","format-standard","hentry","category-axon","category-from-the-trenches","category-kotlin","category-development","category-spring","tag-java","tag-kotlin","tag-postgresql","tag-software-development","tag-spring-boot","tag-spring-data"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Spring Data Native Queries and Projections in Kotlin - Trifork Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Spring Data Native Queries and Projections in Kotlin - Trifork Blog\" \/>\n<meta property=\"og:description\" content=\"This blog describes the solution to mapping native queries to objects. This is useful because sometimes you want to use a feature of the underlying database implementation (such as PostgreSQL) that is not part of the JPQL standard. By the end of this blog you should be able to confidently use native queries and use [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/\" \/>\n<meta property=\"og:site_name\" content=\"Trifork Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-28T12:34:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-1024x214.png\" \/>\n<meta name=\"author\" content=\"Attila Houtkooper\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Attila Houtkooper\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/\",\"url\":\"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/\",\"name\":\"Spring Data Native Queries and Projections in Kotlin - Trifork Blog\",\"isPartOf\":{\"@id\":\"https:\/\/trifork.nl\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-1024x214.png\",\"datePublished\":\"2018-08-28T12:34:59+00:00\",\"author\":{\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/d5ea2b0017fe878b52c016b6567560f4\"},\"breadcrumb\":{\"@id\":\"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/#primaryimage\",\"url\":\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-1024x214.png\",\"contentUrl\":\"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-1024x214.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/trifork.nl\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Spring Data Native Queries and Projections in Kotlin\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/trifork.nl\/blog\/#website\",\"url\":\"https:\/\/trifork.nl\/blog\/\",\"name\":\"Trifork Blog\",\"description\":\"Keep updated on the technical solutions Trifork is working on!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/trifork.nl\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/d5ea2b0017fe878b52c016b6567560f4\",\"name\":\"Attila Houtkooper\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/trifork.nl\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/06b91205b3dfbb609338e6478b68e84c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/06b91205b3dfbb609338e6478b68e84c?s=96&d=mm&r=g\",\"caption\":\"Attila Houtkooper\"},\"url\":\"https:\/\/trifork.nl\/blog\/author\/attilah\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Spring Data Native Queries and Projections in Kotlin - Trifork Blog","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:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/","og_locale":"en_US","og_type":"article","og_title":"Spring Data Native Queries and Projections in Kotlin - Trifork Blog","og_description":"This blog describes the solution to mapping native queries to objects. This is useful because sometimes you want to use a feature of the underlying database implementation (such as PostgreSQL) that is not part of the JPQL standard. By the end of this blog you should be able to confidently use native queries and use [&hellip;]","og_url":"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/","og_site_name":"Trifork Blog","article_published_time":"2018-08-28T12:34:59+00:00","og_image":[{"url":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-1024x214.png","type":"","width":"","height":""}],"author":"Attila Houtkooper","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Attila Houtkooper","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/","url":"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/","name":"Spring Data Native Queries and Projections in Kotlin - Trifork Blog","isPartOf":{"@id":"https:\/\/trifork.nl\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/#primaryimage"},"image":{"@id":"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/#primaryimage"},"thumbnailUrl":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-1024x214.png","datePublished":"2018-08-28T12:34:59+00:00","author":{"@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/d5ea2b0017fe878b52c016b6567560f4"},"breadcrumb":{"@id":"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/#primaryimage","url":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-1024x214.png","contentUrl":"https:\/\/trifork.nl\/articles\/wp-content\/uploads\/sites\/3\/2018\/08\/koltin-springboot-jpa-1024x214.png"},{"@type":"BreadcrumbList","@id":"https:\/\/trifork.nl\/blog\/spring-data-native-queries-and-projections-in-kotlin\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/trifork.nl\/blog\/"},{"@type":"ListItem","position":2,"name":"Spring Data Native Queries and Projections in Kotlin"}]},{"@type":"WebSite","@id":"https:\/\/trifork.nl\/blog\/#website","url":"https:\/\/trifork.nl\/blog\/","name":"Trifork Blog","description":"Keep updated on the technical solutions Trifork is working on!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/trifork.nl\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/d5ea2b0017fe878b52c016b6567560f4","name":"Attila Houtkooper","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/trifork.nl\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/06b91205b3dfbb609338e6478b68e84c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/06b91205b3dfbb609338e6478b68e84c?s=96&d=mm&r=g","caption":"Attila Houtkooper"},"url":"https:\/\/trifork.nl\/blog\/author\/attilah\/"}]}},"_links":{"self":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts\/15495","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/users\/17"}],"replies":[{"embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/comments?post=15495"}],"version-history":[{"count":0,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/posts\/15495\/revisions"}],"wp:attachment":[{"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/media?parent=15495"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/categories?post=15495"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trifork.nl\/blog\/wp-json\/wp\/v2\/tags?post=15495"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}