Spring JDBC 6 CRUD Operations
Tags: spring, jdbc, crud, maven
Published: 2023-05-18 10:54:16


For Step By Step Process watch the video ::

{% embed https://youtu.be/rm2p6WP3Fhc %}


step 1 : create maven project
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w1jezlyohcoicddfazky.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/377id4pkhtlf86k7qh9n.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kqbx7jjau2acpa7xwjzg.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e59hw1kwhs8qmfmdyz2q.png)

step 2 : create the classes according to directory structure


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cd8mu7h7a6c5qtkwup65.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dbsh88eoewgkyijmvcqr.png)



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;
	}
}

```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iso093i7bckp6ny1gpud.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cgjuavo65elvlegh29pl.png)


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;


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lr4o9yxovm25bbdm9i0j.png)


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);

	}
}

```