JDBC Database connection (MySQL for example)

1. What is JDBC? What's the role?

Java data Base Connectivity Java Database Connection protocol

is a Java API for executing SQL statements that provides unified access to a variety of relational databases.

He provides a benchmark to build more advanced tools and interfaces that enable developers to write database applications

With JDBC, it's easy to send SQL statements to various relational data.

In other words, with the JDBC API, you don't have to write a program specifically to access the Oracle database.

or write a program specifically for accessing the MySQL database.

2. What are the steps that the Java app uses to connect to the database and process the data?

Add the path of the driver package to the Classpath class path variable

(e.g. copy the Mysql-connector-java-5.1.5-bin.jar to D:\javasoft and then "D:/javasoft/mysql-connector-java-5.1.5-bin.jar" Added to the value of the CLASSPATH variable)

Steps:

1) Load Driver Drive

2) Create a database connection object connection

3) Create a Routing object that can send SQL commands to the database and return results statement

4) Execute the SQL command and process the returned results

5) After processing, if there is a return result set, close the result set object resultset

6) Close the corresponding Statement object

7) Close the Connection object connection

3. Case Study 1

// 1, load driver Com.mysql.jdbc.Driver is the path of the driver class Class.forName ("Com.mysql.jdbc.Driver");  

// 2. Create a database Connection object NULL  nullnull= "Jdbc:mysql://localhost:3306/test";  // localhost indicates that native 3306 is the default port test for the database name String username = "root";   // define the user name and password to connect to the database String password = "root"= drivermanager.getconnection (Url,username,password);

// 3. Create a delivery object that can send SQL commands to the database and return results statementstmt = Conn.createstatement ();

// 4. Transfer SQL command to database execution via SQL Transfer object statement, and return result String sql = "SELECT * from user"= stmt.executequery (sql);

ExecuteQuery () is used to execute a query statement that returns a result set, which is actually a table that satisfies the query criteria

RS result set using pointers to specify which data is currently

Calling the next method, the pointer points to the next data, and returns True if there is data

// 5. Processing result Sets  while (Rs.next ()) {    println (rs.getstring ("username"));    Pringln (Rs.getint ("password"));}

There is no declaration to throw an exception, need to put in try catch try inside, or throw also line, if put in try inside, want to put catchsqlexception ...

Finally to put the Conn stmt rs are close because these are the external resources of the JVM, and the same as IO to be manually shut down, it is not within the management of the JVM

4. Case Analysis 2 (Increase and deletion)

Additions, modifications, and deletions are data operations, unlike data queries:

No query results, no need to use resultset

The method of execution is executeupdate () not executequery ()

The Executeupdate () method also has a return value, but not a resultset, but an int, which indicates how many data have been updated and generally does not handle

5. What is a transaction?

is a set of atomic database operations that refers to a set of database operations that either succeed or are unsuccessful.

A classic example: transfer

Transfer $100.00 to 100002 accounts from 100001 accounts:
Update account1 set money=money-100.00 where code= ' 100001 ';
Update account1 set money=money+100.00 where code= ' 100002 ';

How is transaction management performed?

The default is auto-commit, the transaction management should be set to manually commit the first

When the program is running normally, the commit method of the connection object is called to commit the transaction.

When an exception occurs, the Rollback method of the connection object is called to rollback the transaction

JDBC Database connection (MySQL for example)