Required external JAR packages:

mysql-connector-java
druid

Configuring Druid Connection Pool

<!--Database connection pool configuration-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="url" value="jdbc:mysql://localhost:3306/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    </bean>

Configuring JdbcTemplate Object with DataSource Injection

<!--JdbcTemplate object-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--Inject DataSource-->
        <property name="dataSource" ref="dataSource"/>
    </bean>

Creating Service and DAO Classes with Dependency Injection

After enabling component scanning:

Service Class Creation with DAO Injection

@Service
public class BookService {
    //Inject DAO
    @Autowired
    private BookDao bookDao;
}

DAO Class with JdbcTemplate Injection for Database Operations

@Repository
public class BookDaoImpl implements BookDao {
    //Inject jdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
}

Using JdbcTemplate for Database Operations

Add Operation

Create a database and a table within it:

create table t_book
(
    user_id int not null primary key auto_increment,
    username varchar(100) not null ,
    ustatus varchar(50) not null
)

Create an Entity Class Corresponding to the Database Table

public class Book {
    private String userId;
    private String username;
    private String ustatus;
​
    public String getUserId() {
        return userId;
    }
​
    public String getUsername() {
        return username;
    }
​
    public String getUstatus() {
        return ustatus;
    }
​
    public void setUserId(String userId) {
        this.userId = userId;
    }
​
    public void setUsername(String username) {
        this.username = username;
    }
​
    public void setUstatus(String ustatus) {
        this.ustatus = ustatus;
    }
}

Implementing Data Addition in Service and DAO Layers

Use the update() method of the jdbcTemplate object to perform database insertion operations.

The method takes two parameters: the SQL statement and variable parameters (values for the SQL statement).

Implementing Data Addition:

@Repository
public class BookDaoImpl implements BookDao {
    //Inject jdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
​
    @Override
    public void add(Book book) {
        //Create SQL statement
        String sql = "insert into t_book(username,ustatus) values(?,?)";
        //Call method to execute update.(sql,args)
        int update = jdbcTemplate.update(sql, book.getUsername(), book.getUstatus());
        System.out.println(update);
    }
}

Writing a Test Class (for adding data)

public class testBook {
    @Test
    public void testJdbcTemplate(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = (BookService)context.getBean("bookService");
        //Create book object
        Book book = new Book();
        //Set values in the object
        book.setUsername("Test User");
        book.setUstatus("Success");
        //Call addition method
        bookService.addBook(book);
    }
}

Test Result (Data successfully written)

July 13, 2020 4:35:17 PM com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
Info: {dataSource-1} inited
1

Check the Database Table

select * from t_book

Database Addition Operation (Completed)

Implementing Data Modification

Creating the Modification Method

@Override
public void update(Book book) {
    String SQL = "update t_book set username=?,ustatus=? where user_id=?";
    int update = jdbcTemplate.update(SQL, book.getUsername(), book.getUstatus(), book.getUserId());
    System.out.println(update);
}

Writing a Test Class (for modifying data)

public class testBook {
    @Test
    public void testJdbcTemplate(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = (BookService)context.getBean("bookService");
        Book book = new Book();
        book.setUserId("1");
        book.setUsername("Modified Test");
        book.setUstatus("yes~");
        //Call modification method
        bookService.updateBook(book);
    }
}

Test Result (Data successfully modified)

July 13, 2020 4:47:24 PM com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
Info: {dataSource-1} inited
1

Check the Database Table

select * from t_book

Database Modification Operation (Completed)

Implementing Data Deletion

Cerating the Deletion Method

@Override
public void delete(String id) {
    String SQL = "delete from t_book where user_id=?";
    int update = jdbcTemplate.update(SQL, id);
    System.out.println(update);
}

Writing a Test Class (for deleting data)

public class testBook {
    @Test
    public void testJdbcTemplate(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = (BookService)context.getBean("bookService");
        bookService.deleteBook("1");
    }
}

Test Result (Data successfully deleted)

July 13, 2020 4:56:58 PM com.alibaba.druid.support.logging.JakartaCommonsLoggingImpl info
Info: {dataSource-1} inited
1

Check the Database Table

select * from t_book

Database Deletion Operation (Completed)

The above demonstrates CRUD operations using JdbcTemplate~

Below is the complete source code

Bean.xml Configuration File

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/context  http://www.springframework.org/schema/context/spring-context.xsd">
​
    <!--Enable component scanning-->
    <context:component-scan base-package="com.minelsg"/>
    <!--Database connection pool-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="url" value="jdbc:mysql://localhost:3306/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="rzt123123"/>
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    </bean>
​
    <!--JDBC Template object-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--Inject DataSource-->
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

DAO Layer Files

Interface

public interface BookDao {
    //Add method
    void add(Book book);
​
    void update(Book book);
​
    void delete(String id);
}

Implementation Class

@Repository
public class BookDaoImpl implements BookDao {
    //Inject jdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
    //Add operation
    @Override
    public void add(Book book) {
        //Create SQL statement
        String sql = "insert into t_book(username,ustatus) values(?,?)";
        //Call method to execute update.(sql,args)
        int update = jdbcTemplate.update(sql, book.getUsername(), book.getUstatus());
        System.out.println(update);
    }
    //Update operation
    @Override
    public void update(Book book) {
        String SQL = "update t_book set username=?,ustatus=? where user_id=?";
        int update = jdbcTemplate.update(SQL, book.getUsername(), book.getUstatus(), book.getUserId());
        System.out.println(update);
    }
    //Delete operation
    @Override
    public void delete(String id) {
        String SQL = "delete from t_book where user_id=?";
        int update = jdbcTemplate.update(SQL, id);
        System.out.println(update);
    }
}

Entity Layer Files

public class Book {
    private String userId;
    private String username;
    private String ustatus;
​
    public String getUserId() {
        return userId;
    }
​
    public String getUsername() {
        return username;
    }
​
    public String getUstatus() {
        return ustatus;
    }
​
    public void setUserId(String userId) {
        this.userId = userId;
    }
​
    public void setUsername(String username) {
        this.username = username;
    }
​
    public void setUstatus(String ustatus) {
        this.ustatus = ustatus;
    }
}

Service Layer Files

@Service
public class BookService {
    //Inject DAO
    @Autowired
    private BookDao bookDao;
    //Add method
    public void addBook(Book book){
        bookDao.add(book);
    }
    //Update method
    public void updateBook(Book book){
        bookDao.update(book);
    }
    //Delete method
    public void deleteBook(String id){
        bookDao.delete(id);
    }
}

Test Class

public class testBook {
    @Test
    public void testJdbcTemplate(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        BookService bookService = (BookService)context.getBean("bookService");
        Book book = new Book();
        //book.setUsername("Test User");
        //book.setUstatus("Success");
        //bookService.addBook(book);
        //book.setUserId("1");
        //book.setUsername("Modified Test");
        //book.setUstatus("yes~");
        bookService.deleteBook("1");
    }
}

Completed~