Learning content: learn the Spring framework (Day50)

1,Spring JDBC
2. Mysql transaction
3. Mysql trigger

1,Spring JDBC

(1) The required jar package is used for database connection using Hikaricp connection pool
mysql-connector-java.jar
HikariCP.jar
spring-jdbc.jar

(2) Configure the data source file db.properties, and set the time zone for the url, otherwise an error may be reported
When configuring the Driver, if mysql-connector-java.jar version is above 6, it is com.mysql.cj.jdbc.Driver; otherwise, it is com.mysql.jdbc.Driver

db.driver=com.mysql.cj.jdbc.Driver
db.userName=root
db.password=root
db.url=jdbc:mysql:///book_db?characterEncoding=utf8&serverTimezone=UTC

(3) In the applicationContext.xml file, get the configuration file and configure the data source. Use the JdbcTemplate class provided by spring-jdbc.jar to operate the database, and inject the data source into the JdbcTemplate class. This class is equivalent to the DBHelp class learned earlier, which provides methods to operate the database.

<?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"/>

    <!--Get profile-->
    <context:property-placeholder location="db.properties"/>
    <!--Configure data sources-->
    <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}"/>
        <!--driver You can import without configuration mysql-connector-java.jar It will be loaded automatically after-->
        <property name="driverClassName" value="${db.driver}"/>
    </bean>

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

(4) The BookDaoImpl class puts annotations into the spring container and automatically injects the JDBC template object

@Repository //The Dao layer registers bean s using the @ Repository annotation
public class BookDaoImpl implements BookDao {

    @Autowired  //Automatically inject JDBC template objects
    private JdbcTemplate jdbcTemplate;

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

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

    @Override
    public Long getCount() {
        String sql = "select count(*) from book";
        //Use local anonymous inner classes
        return jdbcTemplate.queryForObject(sql, new RowMapper<Long>() {
            @Override
            public Long mapRow(ResultSet rs, int i) throws SQLException {
                return rs.getLong(1);
            }
        });
    }

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

        //return jdbcTemplate.queryForObject(sql, new BookRowMapper(),id);

        List<Book> bookList = jdbcTemplate.query(sql,new BookRowMapper(),id);
        if(bookList.size() > 0){
            return bookList.get(0);
        }
        return null;
    }
    //You can write an internal class to implement spring's rowmapper < >
    private class BookRowMapper implements RowMapper<Book> {
        @Override
        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;
        }
    }
}

(5) The BookServiceImpl class puts annotations into the spring container and automatically injects the BookDao object

@Service("bookService")
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();
    }
}

(6) Testing

public class BookServiceTest {
    public static void main(String[] args) {
        AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        BookServiceImpl bookService = (BookServiceImpl) context.getBean("bookService");

        System.out.println(bookService.getCount());
        
        Book book = bookService.findById(101);
        System.out.println(book.toString());
        
        List<Book> all = bookService.findAll();
        System.out.println(all.size());

        Book book = new Book();
        book.setBookName("The Dream of Red Mansion");
        book.setPublisher("Beijing Publishing House");
        book.setAuthor("Cao Xueqin");
        bookService.save(book);
    }
}

2. Mysql transaction

(1) Four characteristics of a transaction (ACID attribute)
Atomic ity: the processing statements that make up the transaction 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.

(2) In MySQL, only databases or tables that use Innodb database engine support transactions. Transactions are used to manage insert, update and delete statements, and can be used to maintain database integrity to ensure that batch SQL operations are either fully executed or not executed at all. The key to transaction processing is to decompose SQL statement groups into logical blocks, And specify when the data should be rolled back and submitted.

(3) Terminology of transactions
Transaction: a set of SQL statements
Rollback: refers to the process of revoking a specified SQL statement
Commit: refers to writing the results of SQL statements that are not stored to the database
Savepoint: refers to the temporary placeholder set in transaction processing, which can be rolled back

(4) Use transaction
START TRANSACTION: START TRANSACTION

ROLLBACK transaction: ROLLBACK

/*Transaction presentation*/
SELECT * FROM t_table;

START TRANSACTION;/*Start transaction*/
DELETE FROM t_tableA WHERE id = 1;
ROLLBACK;/*Transaction rollback, deletion failed*/

SELECT * FROM t_table;

COMMIT transaction: COMMIT

/*Transaction presentation: commit*/
START TRANSACTION;
DELETE FROM t_tableA WHERE id = 1;
COMMIT; /*The transaction was committed and deleted successfully*/

SELECT * FROM t_tableA;

Use retention points:
SAVEPOINT s1
ROLLBACK TO s1

START TRANSACTION;
DELETE FROM t_tableA WHERE id = 4;
SAVEPOINT s1; /*Declare a reservation point*/
DELETE FROM t_tableA WHERE id = 5;
ROLLBACK TO s1;  /*Rollback to s1 retention point*/

3. Mysql trigger

(1) Trigger is an SQL statement automatically executed when MySQL responds to insert, update and delete statements. Only tables support triggers, but views do not.

(2) Information required for trigger
1. Unique trigger name (trigger name in a table is unique, not in a database)
2. Table associated with trigger
3. Events that the trigger should respond to (insert? update? delete?)
4. When does the trigger execute (before or after processing)
5. An event of a table can only have two triggers at most (before and after processing), so a table can have six triggers at most
6. If the trigger before the response fails to execute, the response will not be executed; If the trigger before the response or the response fails to execute, the trigger after the response will not execute

(3) inset trigger

/*trigger*/
CREATE TABLE t_tableA( /*Two test tables*/
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  val VARCHAR(20)
);
CREATE TABLE t_tableB(
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  val VARCHAR(20)
);
/*Create insert post trigger*/
DELIMITER //
CREATE TRIGGER tr_insert_tableA  /* tr_insert_tableA Is the name of the trigger*/
AFTER INSERT ON t_tableA /*A post (AFTER) INSERT (INSERT) in t_ Trigger on tableA table*/
FOR EACH ROW  /*Execute on all lines of code*/
INSERT INTO t_tableB(val) VALUES(new.val); 
END//
DELIMITER
/*Trigger content, put the data inserted in table a into table b, and the insert trigger will access a virtual table named new to obtain the value just inserted*/
/*Test trigger*/
INSERT INTO t_tableA(val) VALUES('abc')
SELECT * FROM t_tableA;
SELECT * FROM t_tableB;

1. In the Insert trigger, you can reference a virtual table named NEW to access the inserted row
2. In the before insert trigger, the value in NEW can also be updated (run to change the inserted value)
3. For the auto growth column, the value of NEW before the insert is executed is 0, and after the insert is executed, it is a NEW automatically generated value

/*The second trigger: get the auto generated primary key value just inserted*/
CREATE TRIGGER t_insert_pk_tableA
AFTER INSERT ON t_tableA
FOR EACH ROW SELECT new.id INTO @id;
/*Test trigger*/
INSERT INTO t_tableA(val) VALUES('abc');
SELECT @id;

(4) delete trigger
1. In the DELETE trigger code, you can reference an OLD virtual table to access the deleted rows
2. All values in the old table are read-only and cannot be updated

DELIMITER //
CREATE TRIGGER t_delete_tableA
AFTER DELETE ON t_tableA /*DELETE Post trigger*/
FOR EACH ROW 
BEGIN
  INSERT INTO t_tableB (val) VALUES(old.val);
END//
DELIMITER;

/*Test trigger*/
DELETE FROM t_tableA WHERE id = 2

(5) update trigger

/*Change the modified names in table a to uppercase*/
DELIMITER //
CREATE TRIGGER t_update_tableA
BEFORE UPDATE ON t_tableA
FOR EACH ROW
BEGIN
  SET new.val = UPPER(new.val); 
END//
DELIMITER;
/*Test trigger*/
UPDATE t_tableA SET val = 'xyz' WHERE id = 1;
SELECT * FROM t_tableA;

1. In the UPDATE trigger code, you can reference a virtual table named OLD to access the previous value, and a table named NEW to access the newly updated value
2. In the befor update trigger, the values in the new table are allowed to be updated (the values to be used in the update statement are allowed to be changed)
3. The values in the old table are read-only and cannot be changed

(6) Delete a trigger
DROP TRIGGER tr_insert_tableA