Spring Boot JDBC Access Database

Spring Boot JDBC Access Database

In this tutorial, we will see how to access a MySQL database using Spring Boot and JDBC. Here are the steps:

Step 1: Create a New Spring Boot Application

Create a new Spring Boot application if you haven't yet. You can do this using Spring Initializr (https://start.spring.io/).

Step 2: Add Necessary Dependencies

Add the Spring Boot Starter JDBC and MySQL Connector Java dependencies in your pom.xml file.

<dependencies> <!-- Other dependencies --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> </dependencies>

Step 3: Configure your Database

Add your database configuration to the application.properties file.

spring.datasource.url=jdbc:mysql://localhost:3306/mydb spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Replace localhost:3306, mydb, root, and root with your MySQL host and port, database name, username, and password respectively.

Step 4: Create a Database Table

For the purpose of this tutorial, let's assume that you have a table named users in your mydb database, and it has the columns id, name, and email.

Step 5: Create a Service Class

Create a service class where you will write your JDBC code to access the database.

import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import java.util.List; import java.util.Map; @Service public class UserService { @Autowired private JdbcTemplate jdbcTemplate; public List<Map<String, Object>> getAllUsers() { String sql = "SELECT * FROM users"; List<Map<String, Object>> users = jdbcTemplate.queryForList(sql); return users; } }

In this class, we're using JdbcTemplate to execute an SQL query that fetches all users from the users table. The queryForList method executes the query and returns the result as a list of maps, where each map represents a row with column names as keys.

Step 6: Create a Controller Class

Finally, create a controller class to expose an API endpoint that returns all users.

import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.Map; @RestController public class UserController { @Autowired private UserService userService; @GetMapping("/users") public List<Map<String, Object>> getAllUsers() { return userService.getAllUsers(); } }

In this class, we're injecting UserService and calling the getAllUsers method to fetch all users from the database. The @GetMapping("/users") annotation maps this method to the /users URL path.

Step 7: Test Your Application

Now you can run your application and visit http://localhost:8080/users to see the list of all users. You should replace localhost:8080 with your actual host and port if they are different.

Remember that in a real-world application, you should create a model class to represent a user and use RowMapper to map the result of the SQL query to a list of user objects. Also, you should handle SQL exceptions and probably use NamedParameterJdbcTemplate for queries with parameters.

More Tags

erb code-documentation groovy stringbuilder android-hardware lazy-loading depth-first-search beagleboneblack git-clone cross-entropy

More Programming Guides

Other Guides

More Programming Examples