JDBC Basic and database transaction __java basics

JDBC:

is a java API for executing SQL statementsthat provides uniform access to a variety of relational databases.

Function: Establish a connection with the database, send SQL statements, and process the results.

Display database: show databases;

Use database: User library name; (such as: User MySQL;)

Display database table: show tables;

Display table structure: describe table name; (such as: describe Time_zone;)

Build database: Create DB Library name; (such as: Create Database School;)

Creating a table in the Database: Create TABLE table name (field definition);

As  User School; Not less

(id int (3) NOT null primary key,

Name Char (10),

Address varchar (50),

Year date);

Add record: INSERT into table name values (each field name);

such as: INSERT into teacher values (001, ' abc ', ' Hbes ', ' 1975-03-03 ');

Query record: Select field from table name where condition;

For example: SELECT * from teacher;

Select name from teacher where id = 001;

Delete Record: Delete from table name where condition;

such as: Delete from teacher where id = 002;

Delete from teacher;

Update record: Update table name set field name = value where condition;

such as: Update teacher Set address = ' Wust ' WHERE id = ' 001 ';

Delete table: drop table table name;

Delete database: Drop library name;

MySQL Chinese processing:

Run First: Set Names GBK;

CREATE TABLE Teacher

(id int (3) NOT null primary key,

Name Char (10),

Address varchar (50),

Year date)

Default character set GBK;

You can add and display Chinese character data

Interface Description:

Java.sql.DriverManager: Handles the driver's transfer and management.

Java.sql.Connection: Its object represents a connection to a particular database.

Java.sql.Statement: A specific container used to execute SQL statements.

Java.sql.ResultSet: The object represents a collection of results after the execution of a SELECT statement.

Java access to the database basic steps:

1. JDBC Driver for loading database:

Class.forName ("Org.gjt.mm.mysql.Driver"). newinstance ();

2, the establishment and the database amount connection: Connection conn = Drivermanager.getconnectiton (URL, username, password);

MySQL connection string: "jdbc:mysql://Host: Port number/database name"

For example: String url = "Jdbc:mysql://localhost:3306/school";

3, establish the statement object, prepare to execute the SQL statement:

Statement stmt = Conn.createstatement ();

4. Execute SQL statement:

Common 3 ways to execute SQL statements:

1) executequery ()--for the SELECT statement, returns the result set (ResultSet).

String sql = "SELECT * from table name";

ResultSet rs = stmt.executequery (SQL);

2 executeupdate ()--for creating and updating tables (such as Update/insert/delete/create), returns the number of affected rows (int).

String sql = "Delete from table name where ...";

int rscount = stmt.executeupdate (sql);

3 Execute ()--for executing any SQL statement, returns a Boolean value (whether the execution was successful).

5, processing resultset result set (mainly query result set)

while (Rs.next ()) {

String S1 = rs.getstring ("name");//value by column name

...

or string s2 = rs.getstring (2);//Based on column number (starting from 1)

or int id = rs.getint (1);

}

6, turn off ResultSet, statement and connection objects in turn:

Rs.close (); Stmt.close (); Conn.close ();

Import java.io.*; Import java.sql.*;

public class Test {

public static void Main (string[] args) throws Exception {

String url = "Jdbc:mysql://localhost:3306/school"; Database connection string

Class.forName ("Org.gjt.mm.mysql.Driver"). newinstance (); Load Driver

Connection conn= drivermanager.getconnection (URL, "root", "DBA"); Establish a connection

Statement stmt=conn.createstatement (); Create SQL Container

String sql= "SELECT * from teacher"; Table is teacher

ResultSet rs=stmt.executequery (SQL); Get result set

while (Rs.next ()) {//Processing result sets

System.out.print (rs.getstring ("id") + "");

System.out.print (rs.getstring ("name") + "");

System.out.print (rs.getstring ("address") + "");

System.out.print (Rs.getstring ("year") + "\ n");

}

Rs.close ();    Stmt.close ();    Conn.close (); Close Order

}

}

Note the MySQL driver problem;

Add this driver to the current project:

Right-click the current project;

Select Build Path→add External archieves→ to introduce Mysql-connector-java-5.1.10-bin.jar.

Conditional query:

String name = (new Scanner (system.in)). Nextline ();

String sql = "SELECT * from teacher where name = ' +name+ '";

ResultSet rs = stmt.executequery (SQL);

Working with query result sets

Basic statement:

String sql= "Update teacher set name= ' Amy ' where id= ' 002 '";

int rowcount=stmt.executeupdate (SQL);

String sql= "INSERT into teacher values (002, ' Hgdamy ', ' wust ', ' 1974-02-03 ')";

int rowcount=stmt.executeupdate (SQL);

String sql= "Delete from teacher where id= ' 002 '";

int rowcount=stmt.executeupdate (SQL);

and then query

Sql= "SELECT * from teacher";

ResultSet rs=stmt.executequery (SQL);

Database transactions:

In a database, a transaction refers to a set of logical unit of operations that transforms data from one state to another.

To ensure consistency of data in the database, data manipulation should be a discrete group of logical units: when it is all complete, the consistency of the data can be maintained, and when a part of the unit fails, the entire transaction should all be treated as an error, and all operations from the start point should all fall back to the start state.
Transaction operations: First define the start of a transaction, and then modify the data, then if the commit, these changes are permanently saved, if the rollback (ROLLBACK), the database management system will discard all the changes made to the state of the start of the transaction.

ACID Properties for transactions:

1, atomicity (atomicity): Refers to the transaction is an indivisible unit of work, the operation of the transaction either occurs, or does not occur.

2, Consistency (consistency): The transaction must transform the database from one consistency state to another consistent state.

3, Isolation (isolation): Refers to the execution of a transaction can not be interfered by other transactions, that is, the internal operation of a transaction and the use of data to other concurrent transactions are isolated, the concurrent execution of the various transactions can not interfere with each other.

4, persistence (durability): means that once a transaction is committed, it is permanent to change the data in the database, and subsequent operations and database failures should not have any impact on it.

Transactions: The set of operations that comprise a single logical unit of work
Transaction processing: Ensure that all transactions are performed as a unit of work, and that this execution cannot be changed even if a failure occurs. When multiple operations are performed in a transaction, either all transactions are committed (commit), or the entire transaction is rolled back (rollback) to its original state.
When a connection object is created, the transaction is automatically committed by default: Every time an SQL statement executes, if the execution succeeds, it is automatically committed to the database and cannot be rolled back.

When the JDBC program obtains a connection object to the database, by default the connection object automatically submits the SQL statement sent on it to the database. If you want to turn off this default submission and have multiple SQL execute in a single transaction, and ensure that the statements are executed together at the same time, we should define a transaction for the multiple statements.

To set the submission for a transaction to be not autocommit:

Conn.setautocommit (FALSE);

Next, you put the code that needs to add the transaction into the Try,catch block. Then, add the commit operation of the transaction within the try block, indicating no exception to the operation, committing the transaction:

Conn.commit ();

Adds a rollback transaction within a catch block, indicating an exception to the operation, revoking the transaction:

Conn.rollback ();

Finally, set up the transaction submission method for autocommit:

Conn.setautocommit (TRUE);

Isolation level of the database:

For multiple transactions running concurrently, when these transactions access the same data in the database, if the necessary isolation mechanism is not taken, it can cause various concurrency problems:
dirty reads: for two things T1, T2, T1 read fields that have been T2 updated but have not yet been committed. After that, if the T2 is rolled back, the contents of the T1 read are temporary and invalid.
non-repeatable reads: for two things T1, T2, T1 read a field, and T2 updated the field. After that, T1 reads the same field again, and the value is different.
Phantom reads: for two things T1, T2, T1 reads a field from a table, and then T2 inserts some new rows into the table. After that, if T1 reads the same table again, there will be a few more lines.
The isolation of database transactions: The database system must have the ability to isolate and run transactions concurrently, so that they do not interact with each other and avoid various concurrency problems.
The degree to which a transaction is isolated from other transactions is called the isolation level. The database provides a variety of transaction isolation levels, with different isolation levels corresponding to different levels of interference, the higher the isolation level, the better the data consistency, but the weaker the concurrency.

4 transaction isolation levels provided by the database:

2 transaction isolation levels supported by Oracle: READ commited, SERIALIZABLE. The Oracle default transaction isolation level is: READ commited
Mysql supports transaction isolation levels in 4. Mysql defaults to the transaction ISOLATION level: Repeatable READ