{"id":50,"date":"2019-06-30T15:42:45","date_gmt":"2019-06-30T15:42:45","guid":{"rendered":"https:\/\/openapex.org\/spaces\/software\/?p=50"},"modified":"2022-10-11T12:02:43","modified_gmt":"2022-10-11T06:32:43","slug":"spring-boot-app-connecting-to-postgresql-using-jpa","status":"publish","type":"post","link":"https:\/\/mutesoft.com\/spaces\/software\/spring-boot-app-connecting-to-postgresql-using-jpa.html","title":{"rendered":"Spring Boot app connecting to PostgreSQL using JPA"},"content":{"rendered":"\n<figure class=\"wp-block-image aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1612\" height=\"774\" src=\"https:\/\/mutesoft.com\/spaces\/software\/wp-content\/uploads\/sites\/7\/2022\/01\/SpringBoot-JPA-PostgreSQL.png\" alt=\"SpringBoot app with PostgreSQL database using JPA.\" class=\"wp-image-949\" srcset=\"https:\/\/mutesoft.com\/spaces\/software\/wp-content\/uploads\/sites\/7\/2022\/01\/SpringBoot-JPA-PostgreSQL.png 1612w, https:\/\/mutesoft.com\/spaces\/software\/wp-content\/uploads\/sites\/7\/2022\/01\/SpringBoot-JPA-PostgreSQL-300x144.png 300w, https:\/\/mutesoft.com\/spaces\/software\/wp-content\/uploads\/sites\/7\/2022\/01\/SpringBoot-JPA-PostgreSQL-1024x492.png 1024w, https:\/\/mutesoft.com\/spaces\/software\/wp-content\/uploads\/sites\/7\/2022\/01\/SpringBoot-JPA-PostgreSQL-768x369.png 768w, https:\/\/mutesoft.com\/spaces\/software\/wp-content\/uploads\/sites\/7\/2022\/01\/SpringBoot-JPA-PostgreSQL-1536x738.png 1536w\" sizes=\"auto, (max-width: 1612px) 100vw, 1612px\" \/><figcaption>SpringBoot app with PostgreSQL database using JPA<\/figcaption><\/figure>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_79_2 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\"><p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<\/div><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/mutesoft.com\/spaces\/software\/spring-boot-app-connecting-to-postgresql-using-jpa.html\/#1_Context\" >1. Context<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/mutesoft.com\/spaces\/software\/spring-boot-app-connecting-to-postgresql-using-jpa.html\/#2_Maven_Dependencies\" >2. Maven Dependencies<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/mutesoft.com\/spaces\/software\/spring-boot-app-connecting-to-postgresql-using-jpa.html\/#3_Spring_Boot_Application\" >3. Spring Boot Application<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/mutesoft.com\/spaces\/software\/spring-boot-app-connecting-to-postgresql-using-jpa.html\/#4_REST_Controller_and_Service\" >4. REST Controller and Service<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/mutesoft.com\/spaces\/software\/spring-boot-app-connecting-to-postgresql-using-jpa.html\/#5_JPA_Repository_and_ORM\" >5. JPA Repository and ORM<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/mutesoft.com\/spaces\/software\/spring-boot-app-connecting-to-postgresql-using-jpa.html\/#6_Database_Configuration\" >6. Database Configuration<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/mutesoft.com\/spaces\/software\/spring-boot-app-connecting-to-postgresql-using-jpa.html\/#7_Conclusion\" >7. Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"1-context\"><span class=\"ez-toc-section\" id=\"1_Context\"><\/span><strong>1. Context<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>We are going to create a spring boot app that connects to the PostgreSQL database using JPA. Let us build an imaginary TV program guide web app that lists and allows us to search TV channels, programs, etc. There would be two kinds of users in the app. Admin users would handle the management of channels and programs. End-users would view the program guide and perform various searches and filtration on channels and programs.<\/p>\n\n\n\n<p>We are going to use: <\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The popular <a rel=\"noreferrer noopener\" aria-label=\"spring boot (opens in a new tab)\" href=\"https:\/\/spring.io\/projects\/spring-boot\" target=\"_blank\">spring boot<\/a> framework for the core application <\/li><li><a href=\"https:\/\/en.wikipedia.org\/wiki\/Java_Persistence_API\">JPA <\/a>(Java Persistence API) as the database access layer<\/li><li><a href=\"https:\/\/www.postgresql.org\/\">PostgreSQL <\/a>for data persistence<\/li><li><a href=\"https:\/\/angular.io\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Angular  (opens in a new tab)\">Angular <\/a>based UI<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"2-maven-dependencies\"><span class=\"ez-toc-section\" id=\"2_Maven_Dependencies\"><\/span><strong>2. Maven Dependencies<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Here is the pom.xml with spring-boot dependencies to <code>spring-boot-starter-web<\/code>, <code>spring-boot-starter-tomcat<\/code> and <code>spring-boot-starter-data-jpa<\/code> artifacts. Also, for PostgreSQL dependency to <code>org.postgresql:postgresql<\/code>.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"xml\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?>\n&lt;project xmlns=\"http:\/\/maven.apache.org\/POM\/4.0.0\"\n         xmlns:xsi=\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\"\n         xsi:schemaLocation=\"http:\/\/maven.apache.org\/POM\/4.0.0 http:\/\/maven.apache.org\/xsd\/maven-4.0.0.xsd\">\n    &lt;modelVersion>4.0.0&lt;\/modelVersion>\n    &lt;groupId>org.openapex.tvguide&lt;\/groupId>\n    &lt;artifactId>server&lt;\/artifactId>\n    &lt;version>1.0-SNAPSHOT&lt;\/version>\n    &lt;dependencies>\n        &lt;dependency>\n            &lt;groupId>org.springframework.boot&lt;\/groupId>\n            &lt;artifactId>spring-boot-starter-web&lt;\/artifactId>\n            &lt;version>2.1.2.RELEASE&lt;\/version>\n        &lt;\/dependency>\n        &lt;dependency>\n            &lt;groupId>org.springframework.boot&lt;\/groupId>\n            &lt;artifactId>spring-boot-starter-tomcat&lt;\/artifactId>\n            &lt;version>2.1.2.RELEASE&lt;\/version>\n        &lt;\/dependency>\n        &lt;dependency>\n            &lt;groupId>org.springframework.boot&lt;\/groupId>\n            &lt;artifactId>spring-boot-starter-data-jpa&lt;\/artifactId>\n            &lt;version>2.1.2.RELEASE&lt;\/version>\n        &lt;\/dependency>\n        &lt;dependency>\n            &lt;groupId>org.postgresql&lt;\/groupId>\n            &lt;artifactId>postgresql&lt;\/artifactId>\n            &lt;version>42.2.5&lt;\/version>\n        &lt;\/dependency>\n        &lt;dependency>\n            &lt;groupId>org.projectlombok&lt;\/groupId>\n            &lt;artifactId>lombok&lt;\/artifactId>\n            &lt;version>1.18.8&lt;\/version>\n            &lt;scope>provided&lt;\/scope>\n        &lt;\/dependency>\n    &lt;\/dependencies>\n&lt;\/project><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"3-spring-boot-application\"><span class=\"ez-toc-section\" id=\"3_Spring_Boot_Application\"><\/span><strong>3. Spring Boot Application<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The main Spring application class is the starting point of the spring boot application. Note the annotations <code>@EnableJpaRepositories<\/code> and <code>@EntityScan<\/code>, are required besides <code>@ComponentScan<\/code> to include repositories and ORMs in the spring application context.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"java\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">package org.openapex.tvguide;\n\nimport org.springframework.boot.SpringApplication;\nimport org.springframework.boot.autoconfigure.SpringBootApplication;\nimport org.springframework.boot.autoconfigure.domain.EntityScan;\nimport org.springframework.context.annotation.ComponentScan;\nimport org.springframework.data.jpa.repository.config.EnableJpaRepositories;\n\n@SpringBootApplication\n@ComponentScan(\"org.openapex.tvguide\")\n@EnableJpaRepositories(\"org.openapex.tvguide\")\n@EntityScan(\"org.openapex.tvguide\")\npublic class TvGuideApplication {\n    public static void main(String[] args) {\n        SpringApplication.run(TvGuideApplication.class, args);\n    }\n}<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"4-rest-controller-and-service\"><span class=\"ez-toc-section\" id=\"4_REST_Controller_and_Service\"><\/span><strong>4. REST Controller and Service<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>This is the controller class, the handler of the REST operations, exposing list, search and create APIs:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"java\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">package org.openapex.tvguide.controller;\n\nimport org.openapex.tvguide.model.Channel;\nimport org.openapex.tvguide.service.TvGuideService;\nimport org.springframework.beans.factory.annotation.Autowired;\nimport org.springframework.stereotype.Controller;\nimport org.springframework.web.bind.annotation.*;\n\nimport java.util.ArrayList;\nimport java.util.List;\n\n@RestController(value = \"tvGuideController\")\n@RequestMapping(path = \"\/guide\")\npublic class TvGuideController {\n    @Autowired\n    private TvGuideService tvGuideService;\n\n    @RequestMapping(method = RequestMethod.GET, path = \"channels\")\n    public List&lt;Channel> getChannels() {\n        return tvGuideService.getChannels();\n    }\n\n    @RequestMapping(method = RequestMethod.GET, path = \"search-channels\")\n    public List&lt;Channel> searchChannels(@RequestParam(name = \"channelGroup\", required = false) String group) {\n        return tvGuideService.searchChannels(group);\n    }\n    @RequestMapping(method = RequestMethod.POST, path=\"channel\")\n    public void createChannel(@RequestBody Channel channel){\n        tvGuideService.createChannel(channel);\n    }\n}<\/pre>\n\n\n\n<p>Here are the REST endpoints this app is exposing:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>GET <\/strong><code>\/guide\/channels<\/code> &#8211; lists all the channels<\/li><li><strong>GET <\/strong><code>\/guide\/search-channel?channelGroup=group<\/code> &#8211; filters channels by channel group<\/li><li><strong>POST <\/strong><code>\/guide\/channel<\/code> with Channel object in the request body &#8211; creates a channel.<\/li><\/ul>\n\n\n\n<p>The Service class that receives calls from the Controller and interacts with the repository to retrieve data from the database. This is the place where all business logic of the program guide app would be added.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"java\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">package org.openapex.tvguide.service;\n\nimport org.openapex.tvguide.dbaccess.repository.ChannelRepository;\nimport org.openapex.tvguide.model.Channel;\nimport org.springframework.beans.factory.annotation.Autowired;\nimport org.springframework.stereotype.Service;\n\nimport java.util.ArrayList;\nimport java.util.List;\n\n@Service\npublic class TvGuideService {\n    @Autowired\n    private ChannelRepository channelRepository;\n\n    public List&lt;Channel> getChannels() {\n        return channelRepository.findAll();\n    }\n\n    public List&lt;Channel> searchChannels(String group) {\n        return channelRepository.findByChannelGroup(group);\n    }\n\n    public void createChannel(Channel channel) {\n        channelRepository.save(channel);\n    }\n}<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"5-jpa-repository-and-orm\"><span class=\"ez-toc-section\" id=\"5_JPA_Repository_and_ORM\"><\/span><strong>5. JPA Repository and ORM<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The Repository interface that extends from <code>JpaRepository<\/code>. We have a method to filter channels based on the group.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"java\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">package org.openapex.tvguide.dbaccess.repository;\n\nimport org.openapex.tvguide.model.Channel;\nimport org.springframework.data.jpa.repository.JpaRepository;\nimport org.springframework.data.jpa.repository.Query;\nimport org.springframework.data.repository.query.Param;\nimport org.springframework.stereotype.Repository;\n\nimport java.util.List;\n\n@Repository\npublic interface ChannelRepository extends JpaRepository&lt;Channel, Integer>{\n    List&lt;Channel> findByChannelGroup(String group);\n}<\/pre>\n\n\n\n<p>The ORM class <code>Channel<\/code> represents the channel table in the PostgreSQL database. We are using this ORM class also as a model. Alternatively, we can define a separate model class and then we would require to add two-way conversions between ORM and model objects. It uses Lombok annotations <code>@Getter<\/code>, <code>@Setter<\/code>, <code>@AllArgsConstructor<\/code>, <code>@NoArgsConstructor<\/code> to make the model class neat. The <code>@Id<\/code> annotation denotes that this field is the primary key of the database table.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"java\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">package org.openapex.tvguide.model;\n\nimport lombok.AllArgsConstructor;\nimport lombok.Getter;\nimport lombok.NoArgsConstructor;\nimport lombok.Setter;\n\nimport javax.persistence.*;\n\n@Getter\n@Setter\n@AllArgsConstructor\n@NoArgsConstructor\n@Entity(name = \"channel\")\npublic class Channel {\n    @Id\n    @SequenceGenerator(name=\"channel_number_seq\", initialValue = 101)\n    @GeneratedValue (strategy = GenerationType.SEQUENCE, generator = \"channel_number_seq\")\n    private int channelNumber;\n    private String name;\n    private double price;\n    private String channelGroup;\n}<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"6-database-configuration\"><span class=\"ez-toc-section\" id=\"6_Database_Configuration\"><\/span><strong>6. Database Configuration<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The resource file <code>application.properties<\/code> contains database connection details such as data source URL, username, password, etc.:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"ini\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">spring.datasource.driverClassName=org.postgresql.Driver\nspring.datasource.username=tvguide\nspring.datasource.password=password\nspring.datasource.url=jdbc:postgresql:\/\/localhost:5432\/postgres<\/pre>\n\n\n\n<p>Here are the database table and the sequence creation SQL script. We need the sequence for the primary key generation. See <code>@GeneratedValue<\/code> and <code>@SequenceGenerator<\/code> in <strong>Channel <\/strong>ORM class.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">create table channel(\n  channel_number int primary key,\n  name text,\n  price double precision,\n  channel_group text\n);\n\ncreate sequence channel_number_seq cycle\n  increment 1\n  start 101\n  minvalue 1\n  maxvalue 9999999999;\n\ninsert into channel (channel_number, name, price, channel_group)\nvalues (100, 'Star Movies', 10.00, 'Star');<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"7-conclusion\"><span class=\"ez-toc-section\" id=\"7_Conclusion\"><\/span><strong>7. Conclusion<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>We can use this sample application as a template for developing any sort of 3-tier web application based on Spring Boot. We can plug in different relational databases by just changing the database configuration.<br><br>The full code of this program guide app is available at GitHub:  <a rel=\"noreferrer noopener\" aria-label=\" (opens in a new tab)\" href=\"https:\/\/github.com\/fiveobjects\/reference\/tree\/master\/java\/tvguide\/server\" target=\"_blank\">tvguide<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>We are going to create a spring boot app that connects to the PostgreSQL database using JPA.  Let us build an imaginary TV program guide web app that lists and allows us to search TV channels, programs, etc.<\/p>\n","protected":false},"author":2,"featured_media":949,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_seopress_robots_primary_cat":"none","_seopress_titles_title":"","_seopress_titles_desc":"We are going to create a spring boot app that connects to the PostgreSQL database using JPA.  Let us build an imaginary TV program guide web app that lists and allows us to search TV channels, programs, etc.","_seopress_robots_index":"","_vp_format_video_url":"","_vp_image_focal_point":[],"footnotes":""},"categories":[6],"tags":[28,16,9,29,51],"class_list":["post-50","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programming","tag-app","tag-database","tag-java","tag-spring","tag-webapp"],"_links":{"self":[{"href":"https:\/\/mutesoft.com\/spaces\/software\/wp-json\/wp\/v2\/posts\/50","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mutesoft.com\/spaces\/software\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mutesoft.com\/spaces\/software\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mutesoft.com\/spaces\/software\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/mutesoft.com\/spaces\/software\/wp-json\/wp\/v2\/comments?post=50"}],"version-history":[{"count":30,"href":"https:\/\/mutesoft.com\/spaces\/software\/wp-json\/wp\/v2\/posts\/50\/revisions"}],"predecessor-version":[{"id":1296,"href":"https:\/\/mutesoft.com\/spaces\/software\/wp-json\/wp\/v2\/posts\/50\/revisions\/1296"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mutesoft.com\/spaces\/software\/wp-json\/wp\/v2\/media\/949"}],"wp:attachment":[{"href":"https:\/\/mutesoft.com\/spaces\/software\/wp-json\/wp\/v2\/media?parent=50"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mutesoft.com\/spaces\/software\/wp-json\/wp\/v2\/categories?post=50"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mutesoft.com\/spaces\/software\/wp-json\/wp\/v2\/tags?post=50"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}