JDBC

1. Functions of JDBC module:
Responsible for database resource management and error management, which simplifies the operation of developers on the database.
2,JdbcTemplate:
The Spring framework provides this class, which is the basis of the data abstraction layer of the Spring framework and the core class of Spring JDBC.
3. The JDBC module consists of four packages:
core package; dataSource data source package; Object package; support package;

Imported jar package

pom.xml:

 <!--Mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.6</version>
        </dependency>

        <!--Connection pool-->
        <!-- https://mvnrepository.com/artifact/com.zaxxer/HikariCP-java7 -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP-java7</artifactId>
            <version>2.4.13</version>
        </dependency>

        <!--spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.9.RELEASE</version>
        </dependency>

Configuring data sources and jdbc templates

application.xml:

<?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"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        https://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop
        https://www.springframework.org/schema/aop/spring-aop.xsd">

    <!--Enable annotation based Bean Manage dependency injection-->
    <context:component-scan base-package="com.hisoft"/>

    <!--Configure data sources-->
    <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <property name="jdbcUrl" value="jdbc:mysql:///book_db?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC"/>
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    </bean>


    <!--structure spring jdbc template-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource"/>
    </bean>


</beans>

Connect with jdbc.properties

jdbc.properties:

db.username=root
db.password=123456
db.url=jdbc:mysql://localhost:3306/book_db?userUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
db.driver=com.mysql.cj.jdbc.Driver

application.xml:

//Configure data sources
 <context:property-placeholder location="jdbc.properties"/>
    <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
        <property name="username" value="${db.username}"/>
        <property name="password" value="${db.password}"/>
        <property name="jdbcUrl" value="${db.url}"/>
        <property name="driverClassName" value="${db.driver}"/>
    </bean>

BookDaoImpl instance

BookDaoImpl:

package com.hisoft.dao.Impl;

import com.hisoft.dao.BookDao;
import com.hisoft.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

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

@Repository
public class BookDaoImpl implements BookDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;



    public List<Book> findAll(){
        String sql = "select id,bookname,author,publisher from book";
        return jdbcTemplate.query(sql,new BeanRowMapper());
    }

    public Long getCount() {
        String sql = "select count(*) from book";
        return jdbcTemplate.queryForObject(sql, new RowMapper<Long>() {
            public Long mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getLong(1);
            }
        });
    }

    public void save(Book book) {
        String sql = "insert into book(bookname,author,publisher) values (?,?,?)";
        jdbcTemplate.update(sql,book.getBookName(),book.getAuthor(),book.getPublisher());
    }

    public Book findById(int id){
        String sql = "select id,bookname,author,publisher from book where id = ?";

        List<Book> bookList = jdbcTemplate.query(sql, new BeanRowMapper(), id);
        if(bookList.size() > 0){
            return bookList.get(0);
        }
        return null;

//        return jdbcTemplate.query(sql,new BeanRowMapper(),id).get(0);
//        return jdbcTemplate.queryForObject(sql,new BeanRowMapper(),id);
    }


    public class BeanRowMapper implements RowMapper<Book> {

        public Book mapRow(ResultSet rs, int row) throws SQLException {
            Book book = new Book();
            book.setId(rs.getInt("id"));
            book.setBookName(rs.getString("bookname"));
            book.setAuthor(rs.getString("author"));
            book.setPublisher(rs.getString("publisher"));
            return book;
        }
    }
}

BookServiceImpl:

package com.hisoft.service.Impl;

import com.hisoft.dao.BookDao;
import com.hisoft.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class BookServiceImpl {
    @Autowired
    private BookDao bookDao;
    public void save(Book book){
        bookDao.save(book);
    }

    public Book findById(int id){
       return bookDao.findById(id);
    }

    public List<Book> findAll(){
        return bookDao.findAll();
    }

    public Long getCount(){
        return bookDao.getCount();
    }
}

Test class

import com.hisoft.entity.Book;
import com.hisoft.service.Impl.BookServiceImpl;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

public class Test {
    public static void main(String[] args) {

        AbstractApplicationContext context = new ClassPathXmlApplicationContext("application.xml");

        BookServiceImpl bookServiceImpl = (BookServiceImpl) context.getBean("bookServiceImpl");
        Book book = new Book();
       /* book.setBookName("Dream of Red Mansions ");
        book.setAuthor("Cao Xueqin ");
        book.setPublisher("Beijing Publishing House ");
        bookServiceImpl.save(book);*/

        Book book1 = bookServiceImpl.findById(88);
        System.out.println(book1.toString());

        List<Book> bookList = bookServiceImpl.findAll();
        System.out.println(bookList.size()); //Total number
        /*for (Book book1 : bookList) {
            System.out.println(book1.toString());
        }*/

        Long count = bookServiceImpl.getCount();
        System.out.println(count);//Total number


        context.close();//close
//        context.registerShutdownHook();
    }
}

Spring transaction isolation level

• DEFAULT uses the DEFAULT isolation level of the database
• READ_UNCOMMITTED allows reading of uncommitted data. It may result in dirty, unreal, or non repeatable reads.
• READ_COMMITTED allows reading from concurrent transactions that have been committed. Dirty reads can be prevented, but phantom reads and non repeatable reads will still occur.
• REPEATABLE_READ multiple reads of the same field will result in the same result unless the data is changed by the current transaction. Dirty reads and non repeatable reads can be prevented, but phantom reads still occur.
• SERIALIZABLE fully complies with the isolation level of ACID to ensure that dirty reads, phantom reads and non repeatable reads do not occur.
• dirty read: one transaction reads data that is not committed by another transaction.
• non repeatable reading: in the same transaction, reading the same data multiple times returns different results.
• phantom read: one transaction reads a transaction that has been committed by another transaction.

Spring transaction propagation properties

• REQUIRED business methods need to run in a transaction. If the method is already in a transaction when it runs, join the transaction, otherwise create a transaction by yourself. (used in most cases)
• not supported declares that the method requires a transaction. If the method is not associated with a transaction, the container will open a transaction for it. If the method is called in a transaction, the transaction will be suspended. After the method call is completed, the original transaction will resume execution.
• the required new business method must run in its own transaction. A new transaction will be started, and if a transaction is running, the transaction will be suspended. After the method runs, the execution of the new transaction ends, and the original transaction will resume.
• MANDATORY this method must run in an existing transaction and cannot create a transaction by itself. If the method is called without a transaction, an exception will be thrown.
• SUPPORTS if the method runs in a transaction environment, it runs in the transaction. If it is called outside the transaction scope, it runs in an environment without transaction.
• NEVER indicates that the method cannot be run in a transaction environment. If it is called in a transaction environment, an exception will be thrown
• NESTED if an active transaction exists, it runs in a NESTED transaction. If there is no active transaction, it is executed in the REQUIRED transaction mode. The transaction can be committed or rolled back independently. If rolled back, it will not affect the peripheral transaction

Four characteristics of transactions ACID

Atomic ity: the processing statements that make up transactions form a logical unit, which is the smallest execution unit;
Consistency: data is Consistent before and after transaction execution;
Isolated: the processing of one transaction has no impact on another transaction;
Durable: when the transaction is successful, the result is permanently recorded in the database