Spring JDBC 6 CRUD Operations

For Step By Step Process watch the video ::

step 1 : create maven project

step 2 : create the classes according to directory structure

step 3 : add spring jdbc, spring context, lombok , mysql connector j dependencies

pom.xml

<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/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.test</groupId>
    <artifactId>SpringJDBC</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>SpringJDBC</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>6.0.6</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>6.0.6</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
            <scope>provided</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.0.32</version>
        </dependency>


    </dependencies>
</project>

step 4: create schema in mysql workbench

step 5 :
AppConfig.java

package com.test.config;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@ComponentScan(basePackages =  "com.test")
@Configuration
public class AppConfig {


    @Bean
    public DataSource getDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/new");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        return dataSource;
    }

    @Bean
    public JdbcTemplate getJdbctemplate() {
        JdbcTemplate jt = new JdbcTemplate();
        jt.setDataSource(getDataSource());
        return jt;
    }
}

step 6 : Create entity

package com.test.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@data
@AllArgsConstructor
@NoArgsConstructor
public class Student {

private Integer sid;

private String sname;

private String sadd;

}

step 7 : create table with the name "studenttab" in "new" schema

CREATE TABLE studenttab (
sid int NOT NULL AUTO_INCREMENT,
sname varchar(45) DEFAULT NULL,
sadd varchar(45) DEFAULT NULL,
PRIMARY KEY (sid)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

step 8 :
StudentService

package com.test.service;

import java.util.List;

import com.test.entity.Student;

public interface StudentService {

    Integer insert(Student st);

    Integer updateStuDetails(Student st);

    Integer deleteStuById(Integer id);

    Student getStuById(Integer id);

    List<Student> getAllStudents();
}

StudentServiceImpl

package com.test.service;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.test.entity.Student;

@Component("stuServ")
public class StudentServiceImpl implements StudentService{

    private static final String INSERT_QUERY = "insert into studenttab(sname,sadd) values(?,?)";

    private static final String UPDATE_QUERY = "update studenttab set sname=?,sadd=? where sid=?";

    private static final String DELETE_QUERY = "delete from studenttab where sid=?";

    private static final String SELECT_QUERY = "select * from studenttab where sid=?";

    private static final String SELECT_ALL_QUERY = "select * from studenttab";

    @Autowired
    private JdbcTemplate jt;

    public Integer insert(Student st) {
        Integer count = jt.update(INSERT_QUERY,st.getSname(),st.getSadd());
        return count;
    }

    public Integer updateStuDetails(Student st) {
        Integer count = jt.update(UPDATE_QUERY,st.getSname(),st.getSadd(),st.getSid());
        return count;
    }

    public Integer deleteStuById(Integer id) {
        Integer count = jt.update(DELETE_QUERY,id);
        return count;
    }

    public Student getStuById(Integer id) {
        RowMapper rowMapper = new RowMapper() {

            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

                Student st = new Student();
                st.setSid(rs.getInt(1));
                st.setSname(rs.getString(2));
                st.setSadd(rs.getString(3));
                return st;
            }
        };
        Student st = jt.queryForObject(SELECT_QUERY, rowMapper,id);
        return st;
    }

    public List<Student> getAllStudents() {
        RowMapper rowMapper = new RowMapper() {

            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

                Student st = new Student();
                st.setSid(rs.getInt(1));
                st.setSname(rs.getString(2));
                st.setSadd(rs.getString(3));
                return st;
            }
        };
        List<Student> list = jt.query(SELECT_ALL_QUERY, rowMapper);
        return list;
    }

}

step 9 : test

execute insert,update, delete , select one by one

App.java

package com.test;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import com.test.config.AppConfig;
import com.test.entity.Student;
import com.test.service.StudentService;
import com.test.service.StudentServiceImpl;

public class App {
    public static void main(String[] args) {
        ApplicationContext context = new AnnotationConfigApplicationContext(AppConfig.class);

        StudentService service = context.getBean("stuServ", StudentServiceImpl.class);

        // insert operation

        /*
         * int count = service.insert(new Student(1,"sam","Mumbai"));
         * System.out.println(count); int count1 = service.insert(new
         * Student(2,"jhon","Pune")); System.out.println(count1); int count2 =
         * service.insert(new Student(3,"brock","Nagpur")); System.out.println(count2);
         */

        // update Operation
        /*
         * int count = service.updateStuDetails(new Student(1,"sam","delhi"));
         * System.out.println(count);
         */

        // delete operation
        /*
         * int count = service.deleteStuById(3); System.out.println(count);
         */

        //select student by id
        /*
        Student st = service.getStuById(2);
        System.out.println(st);
        */

        //select all students
        List<Student> list = service.getAllStudents();
        System.out.println(list);

    }
}