Java Study Point (Java iQ): Spring Boot JdbcTemplate Example

Spring Boot JdbcTemplate Example

Now we will see the complete example of Spring Boot with JdbcTemplate.


<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 http://maven.apache.org/maven-v4_0_0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <groupId>com.topjavatutorial.app</groupId>
 <artifactId>SpringJDBC</artifactId>
 <packaging>jar</packaging>
 <version>1.0-SNAPSHOT</version>
 <name>SpringJDBC</name>
 <url>http://maven.apache.org</url>

 <parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>2.1.0.BUILD-SNAPSHOT</version>
  <relativePath /> <!-- lookup parent from repository -->
 </parent>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  <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-web</artifactId>
  </dependency>

  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-devtools</artifactId>
   <scope>runtime</scope>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-test</artifactId>
   <scope>test</scope>
  </dependency>
  <dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>jstl</artifactId>
  </dependency>
  <dependency>
   <groupId>org.webjars</groupId>
   <artifactId>bootstrap</artifactId>
   <version>4.0.0</version>
  </dependency>
  <dependency>
   <groupId>org.apache.tomcat.embed</groupId>
   <artifactId>tomcat-embed-jasper</artifactId>
   <scope>provided</scope>
  </dependency>
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
  </dependency>
 </dependencies>

 <build>
  <plugins>
   <plugin>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-maven-plugin</artifactId>
   </plugin>
  </plugins>
 </build>

 <repositories>
  <repository>
   <id>spring-snapshots</id>
   <name>Spring Snapshots</name>
   <url>https://repo.spring.io/snapshot</url>
   <snapshots>
    <enabled>true</enabled>
   </snapshots>
  </repository>
  <repository>
   <id>spring-milestones</id>
   <name>Spring Milestones</name>
   <url>https://repo.spring.io/milestone</url>
   <snapshots>
    <enabled>false</enabled>
   </snapshots>
  </repository>
 </repositories>

 <pluginRepositories>
  <pluginRepository>
   <id>spring-snapshots</id>
   <name>Spring Snapshots</name>
   <url>https://repo.spring.io/snapshot</url>
   <snapshots>
    <enabled>true</enabled>
   </snapshots>
  </pluginRepository>
  <pluginRepository>
   <id>spring-milestones</id>
   <name>Spring Milestones</name>
   <url>https://repo.spring.io/milestone</url>
   <snapshots>
    <enabled>false</enabled>
   </snapshots>
  </pluginRepository>
 </pluginRepositories>
</project>

Add datasource details in application.properties file.


spring.datasource.name=jdbcTest

# Applicationn context name
server.contextPath=/springjdbc

# Here 'test' is the database name
#spring.datasource.url=jdbc:mysql://localhost:3306/mydatabase
#spring.datasource.username=root
#spring.datasource.password=root
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver


spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.jpa.hibernate.ddl-auto=none


#spring.datasource.initialize=false
#spring.datasource.url=jdbc:h2:file:~/jdbcTest;DB_CLOSE_ON_EXIT=FALSE;IFEXISTS=TRUE;DB_CLOSE_DELAY=-1;
#spring.jpa.hibernate.ddl-auto =update

Create SpringBootApp.java


package com.shubh.jdbctemplate.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootApp {
   public static void main(String[] args) {
     SpringApplication.run(SpringBootApp.class, args);
   }
}

Create an Empoloyee class.


package com.shubh.jdbctemplate.example;

public class Employee {

 private int empid;

 private String name;
 private int age;
 private String email;
 private String phone;
 private String address;

 public int getEmpId() {
  return empid;
 }

 public void setEmpId(int id) {
  this.empid = id;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

 public int getAge() {
  return age;
 }

 public void setAge(int age) {
  this.age = age;
 }

 public int getEmpid() {
  return empid;
 }

 public void setEmpid(int empid) {
  this.empid = empid;
 }

 public String getEmail() {
  return email;
 }

 public void setEmail(String email) {
  this.email = email;
 }

 public String getPhone() {
  return phone;
 }

 public void setPhone(String phone) {
  this.phone = phone;
 }

 public String getAddress() {
  return address;
 }

 public void setAddress(String address) {
  this.address = address;
 }

}

Create EmployeeRowMapper to map "(ResultSet to Employee object"


package com.shubh.jdbctemplate.example;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class EmployeeRowMapper implements RowMapper {

 public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
  Employee employee = new Employee();
  employee.setEmpId(rs.getInt("EMP_ID"));
  employee.setName(rs.getString("NAME"));
  employee.setAge(rs.getInt("AGE"));
  employee.setEmail(rs.getString("email"));
  employee.setAddress(rs.getString("address"));
  employee.setPhone(rs.getString("telephone"));
  return employee;
 }

}

Create EmployeeDAO interface


package com.shubh.jdbctemplate.example;

import java.util.List;

public interface EmployeeDAO {
 
 public String getEmployeeName(int id);
 public List findAllEmployee();
 
 // add other methods 
 //.......
 //........
 //.......
}

Create EmployeeDAOImpl implementation of EmployeeDAO. In the class i have written different way to use CRUD operation.


package com.shubh.jdbctemplate.example;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Transactional
@Repository
public class EmployeeDAOImpl implements EmployeeDAO {

 @Autowired
 private JdbcTemplate jdbcTemplate;

 // Way-1 insert data
 public void addEmployee() {
  // INSERT Employee data
  String sqlInsertQuery = "INSERT INTO Employee (name, email, address, telephone) VALUES (?, ?, ?, ?)";
  jdbcTemplate.update(sqlInsertQuery, "TestUser00",
    "testuseremail@gmail.com", "Test User Address", "011789632145");

 }

 // Way-2 insert data
 public int insert(Employee employee) {
  return jdbcTemplate.update("insert into Employee (id, name, age) "
    + "values(?,  ?, ?)", new Object[] { employee.getEmpId(),
    employee.getName(), employee.getAge() });
 }

 // Way-1 Update record
 public void updateEmployee() {
  // UPDATE Employee data
  String sqlUpdateQuery = "UPDATE Employee set email=? where name=?";
  jdbcTemplate
    .update(sqlUpdateQuery, "test123@gmail.com", "Employee 101");

 }

 // Way-2 Update record
 public int update(Employee employee) {
  return jdbcTemplate.update("update Employee "
    + " set name = ?, age = ? " + " where id = ?", new Object[] {
    employee.getName(), employee.getAge(), employee.getEmpId() });
 }

 // Way-1 delete record
 public String deleteEmployee() {
  // DELETE employee
  try {
   String sqlDeleteQuery = "DELETE FROM Employee where name=?";
   jdbcTemplate.update(sqlDeleteQuery, "Employee 104");
  } catch (DataAccessException exObj) {
   exObj.printStackTrace();
  }
  return "Record has been deleted successfylly.";
 }

 // Way-2 delete record
 public int deleteById(long id) {
  return jdbcTemplate.update("delete from Employee where Emp_id=?",
    new Object[] { id });
 }

 public List getEmployees() {

  // get all employee list
  String sqlSelectQuery = "SELECT name, email, address, telephone FROM Employee";
  List listEmployees = jdbcTemplate.query(sqlSelectQuery,
    new RowMapper() {
     public Employee mapRow(ResultSet rs, int rowNum)
       throws SQLException {
      Employee employee = new Employee();
      employee.setName(rs.getString("name"));
      employee.setEmail(rs.getString("email"));
      employee.setAddress(rs.getString("address"));
      employee.setPhone(rs.getString("telephone"));
      return employee;
     }
    });
  return listEmployees;

 }

 public String getEmployeeName(int empId) {
  String sql = "select name from employee where emp_id = ?";
  String name = jdbcTemplate.queryForObject(sql, new Object[] { empId },
    String.class);
  return name;
 }

 // Way-1 to get single record
 public Employee findByEmployeeId(int empId) {

  String sql = "SELECT * FROM Employee WHERE EMP_ID = ?";
  Employee employee = (Employee) jdbcTemplate.queryForObject(sql,
    new Object[] { empId }, new EmployeeRowMapper());
  return employee;
 }

 // Way-2 to get single record
 public Employee findByEmployeeId2(int empId) {

  String sql = "SELECT * FROM Employee WHERE EMP_ID = ?";
  Employee employee = jdbcTemplate.queryForObject(sql,
    new Object[] { empId }, new BeanPropertyRowMapper(
      Employee.class));

  return employee;
 }

 // Way-1 to get all employee records
 public List findAllEmployee() {

  String sql = "SELECT * FROM Employee";
  List employeeList = new ArrayList();

  List> rows = jdbcTemplate.queryForList(sql);
  for (Map row : rows) {
   Employee employee = new Employee();
   employee.setEmpId((Integer) (row.get("EMP_ID")));
   // employee.setEmpId((Long)(row.get("EMP_ID")));
   employee.setName((String) row.get("NAME"));
   employee.setAge((Integer) row.get("AGE"));
   employeeList.add(employee);
  }

  return employeeList;
 }

 // Way-2 to get all employee records
 public List findAllEmployee2() {

  String sql = "SELECT * FROM Employee";
  List employee = jdbcTemplate.query(sql,
    new BeanPropertyRowMapper(Employee.class));
  return employee;
 }

 public List findAll() {
  return jdbcTemplate.query("select * from Employee",
    new EmployeeRowMapper());
 }

 // get single column name
 public String findEmployeeNameById(int empId) {

  String sql = "SELECT NAME FROM Employee WHERE EMP_ID = ?";
  String name = jdbcTemplate.queryForObject(sql, new Object[] { empId },
    String.class);

  return name;
 }

 // get employee count
 /*public int getEmployeeCount() {

  String sql = "SELECT COUNT(*) FROM Employee";

  @SuppressWarnings("deprecation")
  int total = jdbcTemplate.queryForInt(sql);
  return total;
 }*/
  

}
}

Create EmployeeController


package com.shubh.jdbctemplate.example;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


@RestController
public class EmployeeController {

    @Autowired
    public EmployeeDAO dao;

    @RequestMapping("/getAllEmployee")
    public List getAllEmployee() {
        List employee = dao.findAllEmployee(); 
        return employee;
    }
}

Pages

Search

Tuesday, July 2, 2019

Spring Boot JdbcTemplate Example

No comments:

Post a Comment