Spring Boot 2 + Spring Data JDBC (Jdbc Template) Insert Query Example | java Inspires

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:

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:
















Post a Comment

Previous Post Next Post