# gs-relational-data-access **Repository Path**: yezuoyi/gs-relational-data-access ## Basic Information - **Project Name**: gs-relational-data-access - **Description**: No description available - **Primary Language**: Unknown - **License**: Apache-2.0 - **Default Branch**: main - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2024-06-05 - **Last Updated**: 2024-06-05 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README :toc: :icons: font :source-highlighter: prettify :project_id: gs-relational-data-access This guide walks you through the process of accessing relational data with Spring. == What You Will Build You will build an application that uses Spring's `JdbcTemplate` to access data stored in a relational database. == What You Need :java_version: 17 include::https://raw.githubusercontent.com/spring-guides/getting-started-macros/main/prereq_editor_jdk_buildtools.adoc[] include::https://raw.githubusercontent.com/spring-guides/getting-started-macros/main/how_to_complete_this_guide.adoc[] [[scratch]] == Starting with Spring Initializr You can use this https://start.spring.io/#!type=maven-project&language=java&packaging=jar&jvmVersion=11&groupId=com.example&artifactId=relational-data-access&name=relational-data-access&description=Demo%20project%20for%20Spring%20Boot&packageName=com.example.relational-data-access&dependencies=jdbc,h2[pre-initialized project] and click Generate to download a ZIP file. This project is configured to fit the examples in this tutorial. To manually initialize the project: . Navigate to https://start.spring.io. This service pulls in all the dependencies you need for an application and does most of the setup for you. . Choose either Gradle or Maven and the language you want to use. This guide assumes that you chose Java. . Click *Dependencies* and select *JDBC API* and *H2 Database*. . Click *Generate*. . Download the resulting ZIP file, which is an archive of a web application that is configured with your choices. NOTE: If your IDE has the Spring Initializr integration, you can complete this process from your IDE. NOTE: You can also fork the project from Github and open it in your IDE or other editor. [[initial]] == Create a `Customer` Object The simple data access logic you will work with manages the first and last names of customers. To represent this data at the application level, create a `Customer` class, as the following listing (from `src/main/java/com/example/relationaldataaccess/Customer.java`) shows: ==== [source,java,tabsize=2] ---- include::complete/src/main/java/com/example/relationaldataaccess/Customer.java[] ---- ==== == Store and Retrieve Data Spring provides a template class called `JdbcTemplate` that makes it easy to work with SQL relational databases and JDBC. Most JDBC code is mired in resource acquisition, connection management, exception handling, and general error checking that is wholly unrelated to what the code is meant to achieve. The `JdbcTemplate` takes care of all of that for you. All you have to do is focus on the task at hand. The following listing (from `src/main/java/com/example/relationaldataaccess/RelationalDataAccessApplication.java`) shows a class that can store and retrieve data over JDBC: ==== [source,java,tabsize=2] ---- include::complete/src/main/java/com/example/relationaldataaccess/RelationalDataAccessApplication.java[] ---- ==== `@SpringBootApplication` is a convenience annotation that adds all of the following: - `@Configuration`: Tags the class as a source of bean definitions for the application context. - `@EnableAutoConfiguration`: Tells Spring Boot to start adding beans, based on classpath settings, other beans, and various property settings. - `@ComponentScan`: Tells Spring to look for other components, configurations, and services in the `com.example.relationaldataaccess` package. In this case, there are none. The `main()` method uses Spring Boot's `SpringApplication.run()` method to launch an application. Spring Boot supports H2 (an in-memory relational database engine) and automatically creates a connection. Because we use `spring-jdbc`, Spring Boot automatically creates a `JdbcTemplate`. The `@Autowired JdbcTemplate` field automatically loads it and makes it available. This `Application` class implements Spring Boot's `CommandLineRunner`, which means it will execute the `run()` method after the application context is loaded. First, install some DDL by using the `execute` method of `JdbcTemplate`. Second, take a list of strings and, by using Java 8 streams, split them into firstname/lastname pairs in a Java array. Then install some records in your newly created table by using the `batchUpdate` method of `JdbcTemplate`. The first argument to the method call is the query string. The last argument (the array of `Object` instances) holds the variables to be substituted into the query where the `?` characters are. TIP: For single insert statements, the `insert` method of `JdbcTemplate` is good. However, for multiple inserts, it is better to use `batchUpdate`. IMPORTANT: Use `?` for arguments to avoid https://en.wikipedia.org/wiki/SQL_injection[SQL injection attacks] by instructing JDBC to bind variables. Finally, use the `query` method to search your table for records that match the criteria. You again use the `?` arguments to create parameters for the query, passing in the actual values when you make the call. The last argument is a Java 8 lambda that is used to convert each result row into a new `Customer` object. NOTE: Java 8 lambdas map nicely onto single method interfaces, such as Spring's `RowMapper`. If you use Java 7 or earlier, you can plug in an anonymous interface implementation and have the method body be the same as the lambda expression's body. include::https://raw.githubusercontent.com/spring-guides/getting-started-macros/main/build_an_executable_jar_mainhead.adoc[] include::https://raw.githubusercontent.com/spring-guides/getting-started-macros/main/build_an_executable_jar_with_both.adoc[] You should see the following output: ==== [source,bash] ---- 2019-09-26 13:46:58.561 INFO 47569 --- [ main] c.e.r.RelationalDataAccessApplication : Creating tables 2019-09-26 13:46:58.564 INFO 47569 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2019-09-26 13:46:58.708 INFO 47569 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. 2019-09-26 13:46:58.809 INFO 47569 --- [ main] c.e.r.RelationalDataAccessApplication : Inserting customer record for John Woo 2019-09-26 13:46:58.810 INFO 47569 --- [ main] c.e.r.RelationalDataAccessApplication : Inserting customer record for Jeff Dean 2019-09-26 13:46:58.810 INFO 47569 --- [ main] c.e.r.RelationalDataAccessApplication : Inserting customer record for Josh Bloch 2019-09-26 13:46:58.810 INFO 47569 --- [ main] c.e.r.RelationalDataAccessApplication : Inserting customer record for Josh Long 2019-09-26 13:46:58.825 INFO 47569 --- [ main] c.e.r.RelationalDataAccessApplication : Querying for customer records where first_name = 'Josh': 2019-09-26 13:46:58.835 INFO 47569 --- [ main] c.e.r.RelationalDataAccessApplication : Customer[id=3, firstName='Josh', lastName='Bloch'] 2019-09-26 13:46:58.835 INFO 47569 --- [ main] c.e.r.RelationalDataAccessApplication : Customer[id=4, firstName='Josh', lastName='Long'] ---- ==== == Summary Congratulations! You have just used Spring to develop a simple JDBC client. NOTE: Spring Boot has many features for configuring and customizing the connection pool -- for instance, to connect to an external database instead of an in-memory one. See the http://docs.spring.io/spring-boot/docs/2.2.1.RELEASE/reference/htmlsingle/#boot-features-configure-datasource[Reference Guide] for more detail. == See Also The following guides may also be helpful: * https://spring.io/guides/gs/accessing-data-jpa/[Accessing Data with JPA] * https://spring.io/guides/gs/accessing-data-mongodb/[Accessing Data with MongoDB] * https://spring.io/guides/gs/accessing-data-gemfire/[Accessing Data with GemFire] * https://spring.io/guides/gs/accessing-data-neo4j/[Accessing Data with Neo4j] * https://spring.io/guides/gs/accessing-data-mysql/[Accessing data with MySQL] * https://spring.io/guides/gs/spring-boot/[Building an Application with Spring Boot] include::https://raw.githubusercontent.com/spring-guides/getting-started-macros/main/footer.adoc[]