Spring Boot: Connect to a JDBC database
220927
Here is a quick intro about the dependencies and the settings required to connect and start using any one of the major relational databases via the JDBC driver. Example repositories are also provided.
Intro
Have you started working with Spring Boot? Do you want to offer some data via your very own API endpoints? Do you use databases? Look at here on how you can start working with any of the major databases using the respective JDBC driver from Maven repositories.
The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases—SQL databases and other data sources, such as .csv files, spreadsheets or even flat files. The JDBC API allows us to access SQL-based databases and run SQL commands or even to deal with other objects, e.g.: to call stored procedures and functions.
JDBC technology enables you to use the Java programming language to take advantage of “Write Once, Run Anywhere” capabilities for enterprise data access applications.
Actually, a JDBC driver is a collection of Java classes that implement the JDBC interfaces for a particular database. The JDBC interfaces are included with standard Java, but their implementation varies depending on the database to which you need to connect. A JDBC driver is one such implementation.
For basic SQL queries performing raw operations with SpringBoot, we can use just the JDBC starter Maven dependency:
JDBC
You can check and get latest releases from the Maven repository, below:
pom.xml settings
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jdbc --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jdbc</artifactId> </dependency>
Notes:
Τhe JDBCTemplate is also part of the JPA starter dependency. However, here we want to use just pure raw SQL queries for database manipulation, without using any JPA/ORM, so the above JDBC starter dependency is quite competent.
The raw SQL actions are actually performed by the JDBCTemplate engine included in the above JDBC starter dependency. “This is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions”
However, the JDBCTemplate allows us to go beyond simple SQL raw queries and perform more complex database operations (not possible via JPA) and even work via DDL on database objects and stored procedures and functions.
About the repositories provided
All the repositories provided are almost identical, except for the settings concerning the matching database, as well as the data initialization scripts (schema.sql and data.sql). The user and the user credentials, the connection string(s), and/or the databases used are those I have presented in other posts of mine, and you can take a look below:
Each repository presents a very elementary REST API implementation exposing just 2 GET endpoints: http://localhost/8080/api/items and http://localhost/8080/api/vendors. The 2 corresponding POJO/entity classes and the Controllers are also provided, as well as just 1 data repository that uses the JdbcTemplate. You can see the example repos structure below:
Let’s see the basic settings for each one of them.
MariaDB (also compatible with MySQL)
Get the driver or check for new versions, in Maven repository:
pom.xml settings
<!-- https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client -->
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>3.1.0</version>
</dependency>
application.properties settings example
spring.datasource.url=jdbc:mariadb://192.168.0.17:3316/store1 spring.datasource.username=user1 spring.datasource.password=upassw1
Get the example repository here.
MySQL (also compatible with MariaDB)
Check the driver in the Maven repository:
pom.xml settings
<!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.31</version>
</dependency>
application.properties settings example
spring.datasource.url=jdbc:mysql://192.168.0.17:3316/store1 spring.datasource.username=user1 spring.datasource.password=upassw1
Get the example repository here.
PostgreSQL
Check the driver in the Maven repository:
You can also check and download the driver at the official PostgreSQL site:
pom.xml settings
<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.5.1</version>
</dependency>
application.properties settings example
spring.datasource.url=jdbc:postgresql://192.168.0.17:5462/store1 spring.datasource.username=user1 spring.datasource.password=upassw1
Get the example repository here.
MS SQL Server
Check and/or get the driver at the Maven repository:
pom.xml settings
<!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>11.2.1.jre18</version>
</dependency>
application.properties settings example
#spring.datasource.url=jdbc:sqlserver://192.168.0.17:1443;databaseName=store1 spring.datasource.url=jdbc:sqlserver://192.168.0.17:1443; encrypt=true;trustServerCertificate=true;databaseName=store1 spring.datasource.username=user1 spring.datasource.password=Upassw!1
alternatively, you can also use a full connection string, like that:
spring.datasource.url=jdbc:sqlserver://192.168.0.17:1443; encrypt=true;trustServerCertificate=true;databaseName=store1 spring.datasource.username=user1 spring.datasource.password=Upassw!1
Get the example repository here.
Oracle
Check and/or get the driver at the Maven repository:
Note: You probably have noticed that different ojdbc<N> drivers can support same versions of JDKs. For instance, both of the ojdbc8 and the ojdbc11, (shipped with the latest Java version 21.7.0.0) support JDK versions JDK11, JDK12, JDK13, JDK14 and JDK15. However, you might also be aware that ojdbc8 has been initially and primarily designed for JDK/JRE 8, and the ojdbc11 for JDK/JRE 11, respectively. Oracle, also make updates with each new version of Java, e.g. Java version 19.15, 21.7, etc.
Also, it will be useful for you to visit the Oracle JDBC FAQs page, and see more about related info, such as: JDBC drivers support, JDBC-RDBMS interoperability matrix, Oracle JDBC releases Vs JDK versions, etc. So, it’s up to you to decide which is the most appropriate ojdbc version for your particular project.
Alternatively, you can grab the most up-to-date .jar versions, by visiting the Oracle official page at:
Oracle Database JDBC driver and Companion Jars Downloads
https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html https://docs.oracle.com/middleware/12211/lcm/MAVEN/maven_version.htm#MAVEN8905 https://stackoverflow.com/questions/9898499/oracle-jdbc-ojdbc6-jar-as-a-maven-dependency
pom.xml settings
<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>21.7.0.0</version>
</dependency>
application.properties settings example
spring.datasource.url=jdbc:oracle:thin:@//192.168.0.17:1621/dockor19cpdb spring.datasource.username=store1 spring.datasource.password=storePassw1
Get the example repository here.
Conclusion
That’s it for now! You can use this post as well as the provided repos as templates for further development of your REST APIs, using any of the major relational databases.
Thanks for reading and stay tuned!
About Post Author