Using Spring Boot and JDBC for Mysql Connection SOLVED

In this tutorial I share with you some snippets explained in this video tutorial


application.properties

  

server.port=9001
spring.datasource.url=jdbc:mysql://localhost:3306/springboot?autoReconnect=true
spring.datasource.username=springboot
spring.datasource.password=springboot
spring.datasource.platform=mysql

  
  

HelloMySql2Application

    package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

@SpringBootApplication
public class HelloMySql2Application {
 
 @Autowired
    StudentService  studentService;
 
 public static void main(String[] args) {
  //SpringApplication.run(HelloMySqlApplication.class, args);
  ApplicationContext context = SpringApplication.run(HelloMySql2Application.class, args);
  StudentService  studentService = context.getBean(StudentService.class);

   Student student = new Student();
   student.setStid(4);
   student.setName("I got it");
   student.setProgramme("Spring Booter");
   
   //Insert a student
   studentService.insertStudent(student);
   
   //Get student Stid = 1
   studentService.getStudentByStid(1);
   
   //Get all Students
   studentService.getAllStudents();
  
 }
}
  

Student

package com.example.demo;

public class Student {
   int Stid;
   String Name;
   String Programme;
   
public int getStid() {
 return Stid;
}
public void setStid(int stid) {
 Stid = stid;
}
public String getName() {
 return Name;
}
public void setName(String name) {
 Name = name;
}
public String getProgramme() {
 return Programme;
}
public void setProgramme(String programme) {
 Programme = programme;
}

@Override
public String toString() {
 return "Student [Stid=" + Stid + ", Name=" + Name + ", Programme=" + Programme + "]";
}
   
   
}

  

StudentDAO

 package com.example.demo;

import java.util.List;

public interface StudentDAO {

  void insertStudent(Student std);
  Student getStudentByStid(int stid);
  List getAllStudents();
}


  

StudentDAOImplementation

package com.example.demo;

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

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

@Repository
public class StudentDAOImplementation extends JdbcDaoSupport implements StudentDAO {

 @Autowired 
    DataSource dataSource;
 
 @PostConstruct
 private void initialize(){
  setDataSource(dataSource);
 }
 
 
 @Override
 public void insertStudent(Student std) {
  String query = "INSERT INTO student (Stid, Name, Programme) VALUES (?, ?,?)" ;
  getJdbcTemplate().update(query, new Object[]{ std.getStid(), std.getName(), std.getProgramme()});
    
 }

 @Override
 public Student getStudentByStid(int stid) {
  String query = "SELECT * FROM student WHERE Stid = ?";
  return (Student)getJdbcTemplate().queryForObject(query, new Object[]{stid}, new RowMapper(){
   @Override
   public Student mapRow(ResultSet resultSet, int rwNumber) throws SQLException {
    Student std = new Student();
    std.setStid(resultSet.getInt("Stid"));
    std.setName(resultSet.getString("Name"));
    std.setProgramme(resultSet.getString("Programme"));
    return std;
   }
  });
 }

 @Override
 public List getAllStudents() {
  String query = "SELECT * FROM student";
  List> studentRow = getJdbcTemplate().queryForList(query);
  
  List students = new ArrayList();
  for(Map row:studentRow){
   Student student = new Student();
   student.setStid((int)row.get("Stid"));
   student.setName((String)row.get("Name"));
   student.setProgramme((String)row.get("Programme"));
   students.add(student);
  }
  
  return students;
 }

 
}
  

StudentService

package com.example.demo;

public interface StudentService {
 void insertStudent(Student emp);
 void getStudentByStid(int stid);
 void getAllStudents();
}

  

StudentServiceImplementation

 package com.example.demo;

import java.util.List;

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

@Service
public class StudentServiceImplementation implements StudentService{

 @Autowired
    StudentDAO studentDAO;
 @Override
 public void insertStudent(Student std) {
       studentDAO.insertStudent(std);  
 }

 @Override
 public void getStudentByStid(int stid) {
          Student student = studentDAO.getStudentByStid(stid);  
       System.out.println(student);
 }

 @Override
 public void getAllStudents() {
      List students = studentDAO.getAllStudents();
       students.forEach(student -> System.out.println(student.toString()));
 }

}