Author: Mason Today, the group discussed whether DDL or DML. I'd better summarize their differences.

1. DDL – Data Definition Language

Database definition language: defines the structure of a database.

The main commands are CREATE, ALTER, DROP, etc., which are explained in detail with examples below. The language does not require a commit, so be careful.

CREATE – to create objects in the database

Example: CREATE DATABASE test; // Create a database named test

ALTER – alters the structure of the database

Example: ALTER TABLE test ADD birthday date; // Modify the test table and add a birthday column of date type

DROP – delete objects from the database

Example: DROP DATABASE test;// Delete test database

There are others:

TRUNCATE – TRUNCATE table contents (in development period, it is still very common)

COMMENT – adds a COMMENT to the data dictionary

2. DML – Data Manipulation Language

Database operation language: process the data in the database in SQL

The main commands include SELECT,INSERT,UPDATE,DELETE, etc. These examples are not introduced one by one. The language requires commit. There is also the commonly used LOCK TABLE. Remember to write the lock blog - portal

There are other unfamiliar:

CALL – calls a PL/SQL or Java subroutine EXPLAIN PLAN – parse data access path

3. DCL – Data Control Language

Database control language: authorization, role control, etc

GRANT – give users access

REVOKE – REVOKE authorization

4. TCL – Transaction Control Language

Transaction control language

COMMIT – save the completed work

SAVEPOINT – set a SAVEPOINT in the transaction, which can be rolled back here

ROLLBACK – ROLLBACK

SET TRANSACTION – change transaction options

Example: JDBC in Java encapsulates support for transactions. For example, let's first create a new table: test

test.sql

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
--  Table structure for `city`
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
  `id` int(11) NOT NULL DEFAULT '0' COMMENT 'city ID',
  `name` varchar(20) DEFAULT NULL COMMENT 'name',
  `state` varchar(20) DEFAULT NULL COMMENT 'state',
  `country` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
SET FOREIGN_KEY_CHECKS = 1;

JDBC transaction rollback first example - JDBC database transaction rollback:

/**
 * Description: JDBC database transaction rollback
 *
 * Created by bysocket on 16/6/6.
 */
public class TransactionRollBack extends BaseJDBC {
 
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        try {
            // Load database driver
            Class.forName(DRIVER);
            // Database connection
            conn = DriverManager.getConnection(URL,USER,PWD);
 
            // Turn off auto commit transaction mechanism
            conn.setAutoCommit(false);
            // Set transaction isolation level SERIALIZABLE
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
 
            Statement stmt = conn.createStatement();
            int rows = stmt.executeUpdate("INSERT INTO city VALUES (3,'china',1,'cc')");
            rows = stmt.executeUpdate("UPDATE city set country = 'TAIWAN' WHERE id = 4");
 
            // Commit transaction
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            // Rollback transaction
            if (conn != null) {
                conn.rollback();
            }
        } finally {
            /** Close database connection */
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Line 19: set the transaction isolation level to serial, and the underlying call is SET TRANSACTION of TCL language

Line 22: execute and insert data

Line 23: if the execution fails and there is no record with primary key 4, an exception is thrown directly

Line 31: transaction ROLLBACK encapsulates the ROLLBACK of the TCL statement

Take a break. One example is not enough. One more. The code is on the GitHub home page. https://github.com/JeffLi1993/jee-component-learning

The second example of JDBC transaction rollback - JDBC database transaction rollback to a specific savepoint:

/**
 * Description: the JDBC database transaction is rolled back to a specific savepoint
 *
 * Created by bysocket on 16/6/6.
 */
public class TransactionRollBack2 extends BaseJDBC {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        Savepoint svpt = null;
        try {
            // Load database driver
            Class.forName(DRIVER);
            // Database connection
            conn = DriverManager.getConnection(URL,USER,PWD);
 
            // Turn off auto commit transaction mechanism
            conn.setAutoCommit(false);
            // Set transaction isolation level SERIALIZABLE
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
 
            Statement stmt = conn.createStatement();
            int rows = stmt.executeUpdate("INSERT INTO city VALUES (3,'china',1,'cc')");
            // Set transaction savepoint
            svpt = conn.setSavepoint();
            rows = stmt.executeUpdate("UPDATE city set country = 'TAIWAN' WHERE id = 4");
 
            // Commit transaction
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            // Rollback transaction
            if (conn != null) {
                conn.rollback(svpt);
            }
        } finally {
            /** Close database connection */
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Repeat the first example.

Line 9: a savepoint is declared

Line 24: the savepoint is set

Line 33: roll back the transaction to the savepoint

The above code relates to SAVEPOINT in TCL language

Finally, the following figure summarizes:

If the above articles or links are helpful to you, don't forget to share them with your circle of friends and let more people read this article.