SpringBootMySqlConnectorNTierApp

[SankalpHP]

@akash-coded
my projects run successfully also connected with database successfully.
but when to hit request in postman its shows nothing no error but its not show a data in database when hit a request http://localhost:8082/api/worker/all

*Runs and Connect to database Successfully image

*while hitting a request a in postman getting an exception
of "Exception occurred while fetching all worker records!
java.sql.SQLDataException: Cannot convert string '2014-02-20 09:00:00' to java.sql.Timestamp value"

postman request image while hitting or send request

*Mydatabase is also okay containing some workers records in it
IMAGE OF DB

CODE
Base package.

package com.spring.boot.mysql.connector.demo;

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

@SpringBootApplication
public class SpringBootMysqlConnectorNTierApp {

	public static void main(String[] args) {
		SpringApplication.run(SpringBootMysqlConnectorNTierApp.class, args);
		System.out.println("Spring boot n-tier app with JDBC connectivity and MySQL works!");
	}

}

com.spring.boot.mysql.connector.demo.utils;

package com.spring.boot.mysql.connector.demo.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.annotation.PostConstruct;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
@Component
public class DatabaseConnection {
	private Connection connection = null;
	
	DatabaseConnection(
			@Value("${db.url}")String url,
			@Value("${db.username}")String username,
			@Value("${db.password}")String password) throws SQLException {
		super();
		this.connection = DriverManager.getConnection(url, username, password);
	}
	
	@PostConstruct
	public void init()throws SQLException {
		printConnectionDetails();
	}
	
	@SuppressWarnings("unused")
	private void printConnectionDetails() throws SQLException {
		System.out.println("Connected to database server "
                + this.connection.getMetaData().getDatabaseProductName()
                + " version: "
                + this.connection.getMetaData().getDatabaseProductVersion()
				+ "\n");
	}

	public Connection getConnection() {
		return connection;
	}
}

com.spring.boot.mysql.connector.demo.model

package com.spring.boot.mysql.connector.demo.model;

import java.sql.Timestamp;
import java.util.Objects;

/**
 * 
 * @author Akash
 *
 * The Worker model class represents the structure of the worker table
 * 
 * One object of Worker model class will represent one record of the worker table
 * 
 */
public class Worker implements Comparable<Worker> {
	Integer workerId;
	String firstName;
	String lastName;
	Integer salary;
	Timestamp joiningDate;
	String department;
	
	public Worker() {
		super();
	}
	
	public Worker(Integer workerId, String firstName, String lastName, Integer salary, String department) {
		super();
		this.workerId = workerId;
		this.firstName = firstName;
		this.lastName = lastName;
		this.salary = salary;
		this.department = department;
	}

	public Worker(Integer workerId, String firstName, String lastName, Integer salary, Timestamp joiningDate,
			String department) {
		super();
		this.workerId = workerId;
		this.firstName = firstName;
		this.lastName = lastName;
		this.salary = salary;
		this.joiningDate = joiningDate;
		this.department = department;
	}

	public Integer getWorkerId() {
		return workerId;
	}

	public void setWorkerId(Integer workerId) {
		this.workerId = workerId;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public Integer getSalary() {
		return salary;
	}

	public void setSalary(Integer salary) {
		this.salary = salary;
	}

	public Timestamp getJoiningDate() {
		return joiningDate;
	}

	public void setJoiningDate(Timestamp joiningDate) {
		this.joiningDate = joiningDate;
	}

	public String getDepartment() {
		return department;
	}

	public void setDepartment(String department) {
		this.department = department;
	}

	@Override
	public int hashCode() {
		return Objects.hash(department, workerId);
	}

	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Worker other = (Worker) obj;
		return Objects.equals(department, other.department) && Objects.equals(workerId, other.workerId);
	}
	
    @Override
    public int compareTo(Worker o) {
        if (this.workerId < o.workerId) {
            return -1;
        }
        if (this.workerId > o.workerId) {
            return 1;
        }
        if (this.equals(o)) {
            return 0;
        }
        return this.department.compareTo(o.department);
    }

	@Override
	public String toString() {
		return "Worker [workerId=" + workerId + ", firstName=" + firstName + ", lastName=" + lastName + ", salary="
				+ salary + ", joiningDate=" + joiningDate + ", department=" + department + "]";
	}
}

com.spring.boot.mysql.connector.demo.dao

package com.spring.boot.mysql.connector.demo.dao;

import java.sql.SQLException;
import java.util.List;

import com.spring.boot.mysql.connector.demo.model.Worker;


public interface WorkerDao {
	// Create Operation
	Integer createWorker(Worker worker) throws SQLException;
	
	// Read/Retrieve Operations
	Worker findWorkerById(Integer id) throws SQLException;
	List<Worker> findAllWorkers() throws SQLException;
	
	// Update Operations
	Integer updateWorker(Worker worker) throws SQLException;
	Integer updateSalaryByDepartment(String department, Integer bonusFactor) throws SQLException;
	
	// Delete Operations
	Integer deleteWorkerById(Integer id) throws SQLException;	
}

com.spring.boot.mysql.connector.demo.repository

package com.spring.boot.mysql.connector.demo.repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.spring.boot.mysql.connector.demo.dao.WorkerDao;
import com.spring.boot.mysql.connector.demo.model.Worker;
import com.spring.boot.mysql.connector.demo.utils.DatabaseConnection;


@Repository
public class WorkerRepository implements WorkerDao {
	private final Connection connection;
    
	@Autowired
	public WorkerRepository(DatabaseConnection connection) {
		super();
		this.connection = connection.getConnection();
	}

	@Override
	public Integer createWorker(Worker worker) throws SQLException {
		String insertWorkerFormat = """
				INSERT INTO Worker (
				    WORKER_ID,
				    FIRST_NAME,
				    LAST_NAME,
				    SALARY,
				    JOINING_DATE,
				    DEPARTMENT)
				VALUES (
				    ?, ?, ?, ?, NOW(), ?
				    )""";

		try (PreparedStatement preparedStatement = connection.prepareStatement(insertWorkerFormat);) {
			preparedStatement.setInt(1, worker.getWorkerId());
			preparedStatement.setString(2, worker.getFirstName());
			preparedStatement.setString(3, worker.getLastName());
			preparedStatement.setInt(4, worker.getSalary());
			preparedStatement.setString(5, worker.getDepartment());
			return preparedStatement.executeUpdate();
		}
	}

	@Override
	public Worker findWorkerById(Integer id) throws SQLException {
		// Read (Retrieve) Operation using PreparedStatement
		String getWorkerFormat = """
				SELECT *
				FROM
				    Worker
				WHERE
				    WORKER_ID = ?""";
		try (PreparedStatement preparedStatement = connection.prepareStatement(getWorkerFormat);) {
			preparedStatement.setInt(1, id);
			ResultSet rs = preparedStatement.executeQuery();

			if (!rs.isBeforeFirst()) {
				return null;
			}

			Worker worker = new Worker();
			while (rs.next()) {
				worker.setWorkerId(rs.getInt("WORKER_ID"));
				worker.setFirstName(rs.getString("FIRST_NAME"));
				worker.setLastName(rs.getString("LAST_NAME"));
				worker.setSalary(rs.getInt("SALARY"));
				worker.setJoiningDate(rs.getTimestamp("JOINING_DATE"));
				worker.setDepartment(rs.getString("DEPARTMENT"));
			}
			return worker;
		}
	}

	@Override
	public List<Worker> findAllWorkers() throws SQLException {
		String getWorkersFormat = """
				SELECT *
				FROM
				    Worker""";
		try (PreparedStatement preparedStatement = connection.prepareStatement(getWorkersFormat);) {
			ResultSet rs = preparedStatement.executeQuery();
			List<Worker> workers = new ArrayList<>();

			if (!rs.isBeforeFirst()) {
				return workers;
			}

			while (rs.next()) {
				Worker worker = new Worker();
				worker.setWorkerId(rs.getInt("WORKER_ID"));
				worker.setFirstName(rs.getString("FIRST_NAME"));
				worker.setLastName(rs.getString("LAST_NAME"));
				worker.setSalary(rs.getInt("SALARY"));
				worker.setJoiningDate(rs.getTimestamp("JOINING_DATE"));
				worker.setDepartment(rs.getString("DEPARTMENT"));

				workers.add(worker);
			}

			return workers;
		}
	}

	@Override
	public Integer updateWorker(Worker worker) throws SQLException {
		String updateWorkerFormat = """
				UPDATE Worker
				SET
				    WORKER_ID = ?,
				    FIRST_NAME = ?,
				    LAST_NAME = ?,
				    SALARY = ?,
				    JOINING_DATE = ?,
				    DEPARTMENT = ?  
				WHERE
				    WORKER_ID = ?""";

		try (PreparedStatement preparedStatement = connection.prepareStatement(updateWorkerFormat);) {
			preparedStatement.setInt(1, worker.getWorkerId());
			preparedStatement.setString(2, worker.getFirstName());
			preparedStatement.setString(3, worker.getLastName());
			preparedStatement.setInt(4, worker.getSalary());
			preparedStatement.setTimestamp(5, worker.getJoiningDate());
			preparedStatement.setString(6, worker.getDepartment());
			preparedStatement.setInt(7, worker.getWorkerId());
			return preparedStatement.executeUpdate();
		}
	}

	@Override
	public Integer updateSalaryByDepartment(String department, Integer bonusFactor) throws SQLException {
		return 0;
	}

	@Override
	public Integer deleteWorkerById(Integer id) throws SQLException {
        String deleteWorkerFormat = """
                DELETE FROM Worker
                WHERE
                    WORKER_ID = ?""";

		try (PreparedStatement preparedStatement = connection.prepareStatement(deleteWorkerFormat);) {
			preparedStatement.setInt(1, 10);
			return preparedStatement.executeUpdate();
		}
	}
}

com.spring.boot.mysql.connector.demo.service

package com.spring.boot.mysql.connector.demo.service;

import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.spring.boot.mysql.connector.demo.dao.WorkerDao;
import com.spring.boot.mysql.connector.demo.model.Worker;
@Service
public class WorkerService {
	@Autowired
	private WorkerDao workerRepository;
	
	WorkerService() {
		super();
		
	}
	
	public Boolean addWorker(Worker worker) throws SQLException {
		Integer recordsInserted = this.workerRepository.createWorker(worker);
		
		return recordsInserted > 0;
	}
	
	public Worker getWorker(Integer id) throws SQLException {
		return this.workerRepository.findWorkerById(id);
	}
	
	public List<Worker> getAllWorkers() throws SQLException {
		return this.workerRepository.findAllWorkers();
	}
	
	public Boolean updateWorker(String department, Integer id) throws SQLException {
		Worker worker = this.workerRepository.findWorkerById(id);
		worker.setDepartment(department);
		Integer recordsUpdated = this.workerRepository.updateWorker(worker);
		
		return recordsUpdated > 0;
	}
	
	public Boolean deleteWorker(Integer id) throws SQLException {
		Integer recordsDeleted = this.workerRepository.deleteWorkerById(id);
		
		return recordsDeleted > 0;
	}
}

com.spring.boot.mysql.connector.demo.controller

package com.spring.boot.mysql.connector.demo.controller;

import java.sql.SQLException;
import java.util.List;

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

import com.spring.boot.mysql.connector.demo.model.Worker;
import com.spring.boot.mysql.connector.demo.service.WorkerService;

@RestController
@RequestMapping("/api/worker")
public class WorkerController {
	@Autowired
	private WorkerService workerService;
	
	WorkerController() {
		super();
	}
	
	@PostMapping("/create")
	public String create(@RequestBody Worker worker) {
		
		try {
			if (Boolean.TRUE.equals(this.workerService.addWorker(worker))) {
				return "New worker record added successfully";
			} 
		} catch (SQLException ex) {
			System.out.println("Exception occurred while inserting a new worker record!\n" + ex);
		}
		
		return "Failure in inserting new worker record!";
	}
	
	public String get(Integer id) {
		try {
			Worker worker = this.workerService.getWorker(id);
			return worker == null ? "Empty set!" : worker.toString();
		} catch (SQLException ex) {
			System.out.println("Exception occurred while fetching the record of worker #" + id + "!\n" + ex);
		}
		
		return null;
	}
	
	@GetMapping("/all")
	public String getAll() {
		try {
			List<Worker> workers = this.workerService.getAllWorkers();
			
			return workers.isEmpty() ? "Empty set!" : workers.toString();
		} catch (SQLException ex) {
			System.out.println("Exception occurred while fetching all worker records!\n" + ex);
		}
		
		return null;
	}
	
	public String update(String department, Integer id) {
		try {
			if (Boolean.TRUE.equals(this.workerService.updateWorker(department, id))) {
				return String.format("Record of worker #%d updated successfully", id);
			} 
		} catch (SQLException ex) {
			System.out.println("Exception occurred while updating the record of worker #" + id + "!\n" + ex);
		}
		
		return String.format("Failure in updating the record of worker #%d!", id);
	}
	
	public String delete(Integer id) {
		try {
			if (Boolean.TRUE.equals(this.workerService.deleteWorker(id))) {
				return String.format("Record of worker #%d deleted successfully", id);
			} 
		} catch (SQLException ex) {
			System.out.println("Exception occurred while deleting the record of worker #" + id + "!\n" + ex);
		}
		
		return String.format("Failure in deleting the record of worker #%d!", id);
	}
}

application.properties

server.port=8082

db.url=jdbc:mysql://localhost:3306/school
db.username=root
db.password=Password

GITHUB LINK FOR PROJECT

https://github.com/SankalpHP/spring-boot-mysql-connector-n-tier-app.git