Spring Boot 2 + Spring Data JDBC (Jdbc Template) Insert Query Example
Hi Guys,
Welcome to Java Inspires.
In this post, we will see how to work with Spring Data JDBC using jdbc template.
Lets start...
======================================================================
User table create script:
======================================================================
User table create script:
CREATE TABLE `user` ( `username` varchar(16) NOT NULL, `email` varchar(255) DEFAULT NULL, `password` varchar(32) NOT NULL, `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.3.1.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.javainspires</groupId> <artifactId>demo</artifactId> <version>0.0.1-SNAPSHOT</version> <name>demo</name> <description>Demo project for Spring Boot + MVC + Thymeleaf + Spring Data JDBC</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
DemoApplication.java
package com.javainspires.demo; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; /** * * @author #JavaInspires * */ @SpringBootApplication public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } }
application.properties
#Thymeleaf Configuration spring.thymeleaf.prefix=classpath:/pages/ spring.thymeleaf.suffix=.html spring.thymeleaf.encoding=UTF-8 #Mysql Datasource Configuration spring.datasource.url=jdbc:mysql://localhost:3306/testdb spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver
index.html
<!DOCTYPE html> <html xmlns:th="http://www.thymeleaf.org"> <head> <meta charset="ISO-8859-1"> <title>Java Inspires</title> </head> <body> <form th:action="@{/addUser}" th:object="${user}" method="post" > <p>User Name :<input type="text" name="username" /></p> <p>Email :<input type="email" name="email" /></p> <p>Password :<input type="password" name="password" /></p> <p><input type="submit" value="Sign Up" /></p> </form> </body> </html>
success.html
<!DOCTYPE html> <html> <head> <meta charset="ISO-8859-1"> <title>Java Inspires</title> </head> <body> <h1>Success...!!!</h1> </body> </html>
error.html
<!DOCTYPE html> <html> <head> <meta charset="ISO-8859-1"> <title>Java Inspires</title> </head> <body> <h1>Error...!!!</h1> </body> </html>
User.java
package com.javainspires.demo; import java.util.Date; /** * * @author #JavaInspires * */ public class User { private String username; private String email; private String password; private Date create_time; public User() { super(); // TODO Auto-generated constructor stub } public User(String username, String email, String password, Date create_time) { super(); this.username = username; this.email = email; this.password = password; this.create_time = create_time; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public Date getCreate_time() { return create_time; } public void setCreate_time(Date create_time) { this.create_time = create_time; } }
UserController.java
package com.javainspires.demo; import java.util.Date; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.PostMapping; /** * * @author #JavaInspires * * Controller Class */ @Controller public class UserController { // Autowire jdbc template @Autowired JdbcTemplate jdbcTemplate; @PostMapping(path = "addUser") public String addUser(User user) { // set date to the user object user.setCreate_time(new Date()); // insert query String insert_query = "insert into user (username,email,password,create_time)" + " values(?,?,?,?);"; //returns no of rows inserted = 1 int rows = jdbcTemplate.update(insert_query, user.getUsername(), user.getEmail(), user.getPassword(), user.getCreate_time()); if (rows == 1) { return "success"; } else { return "error"; } } }
Screen Shots: