Learn Java JDBC, this is enough.

JDBC (Java DB Connection)---Java database connection

JDBC is a Java API (applicationprogramming interface Application design interface) that can be used to execute SQL statements. It consists of classes and interfaces written in some Java languages.

JDBC provides a standard application design interface for database application developers and database foreground tool developers, enabling developers to write complete database applications in a pure Java language.

JDBC represents a Java database connection. It is a software layer that allows developers to write client/server applications in Java.

First, ODBC to the JDBC the development process

ODBC is a shorthand for opendatabaseconnectivity in English. It is a standard application data interface implemented in C language for accessing data in related or unrelated database management systems (DBMS). With ODBCAPI, applications can access data stored in a variety of different database management systems (DBMS), regardless of the data storage format and programming interface used by each DBMS.

1. Structure Model of ODBC

The structure of ODBC consists of four main parts: The application interface, the drive manager, the database drive, and the data source.

Application interface: Masks the differences between function calls between different ODBC database drives and provides users with a unified SQL programming interface.

Drive Manager: Mount the database drive for your application.

Database drive: A function call that implements ODBC to provide SQL requests to a specific data source. If required, the database drive modifies the application's request so that the request conforms to the grammar supported by the relevant DBMS.

Data source: The data that the user wants to access and the operating system associated with it, the DBMS, and the network platform used to access the DBMS.

Although the primary purpose of the ODBC drive Manager is to load the database drive for ODBC function calls, the database drive itself also performs ODBC function calls and mates with the database. Therefore, when an application makes a call to connect to the data source, the database drive can manage the communication protocol. When a connection to a data source is established, the database drive can handle requests made by the application system to the DBMS, perform necessary translations of the analysis or design from the data source, and return the results to the application system.

2. The birth of JDBC

Since the Java language was officially published in May 1995, Java has swept the world. There are a large number of programs written in the Java language, including database applications. Since there is no Java-language API, programmers have to add C-language ODBC function calls to Java programs. This makes a lot of Java's outstanding features can not be fully played, such as platform-independent, object-oriented features and so on. As more and more programmers are becoming more and more interested in the Java language, more and more companies are devoting more and more effort to the development of Java programming, and the requirement of accessing database API for Java language interface becomes more and more strong. Because of the shortcomings of ODBC, such as it is not easy to use, no object-oriented features and so on, Sun decided to develop a Java language for the interface of the database application development interface. In JDK1. In the X version, JDBC is just an optional part, to the JDK1. 1 when published, the SQL class package (aka JDBCAPI) becomes the standard part of the Java language.

Second, JDBC Technical Overview

JDBC is a javaapi (applicationprogramminginterface, application design interface) that can be used to execute SQL statements.

By using JDBC, developers can easily pass SQL statements to almost any kind of database. That is, developers can not have to write a program to access Sybase, write another program to access Oracle, and then write a program to access Microsoft SQL Server. Programs written in JDBC are able to automatically pass SQL statements to the appropriate database management system (DBMS). In addition, applications written in Java can run on any Java-enabled platform without having to write different applications on different platforms. The combination of Java and JDBC allows developers to actually implement "writeonce,runeverywhere!" when developing database applications. ”

Java is robust, secure, easy to use, and supports automatic Web download, which is essentially a good programming language for database applications. What it needs is how the Java application connects to a wide variety of databases, and JDBC is the key to achieving this connection.

JDBC extends the capabilities of Java, such as using Java and JDBCAPI to advertise a Web page with AP Plet that can access the remote database. Or the enterprise can use JDBC to allow all employees (they can have different operating systems, such as Windwos,machintosh and Unix) to connect to several global databases on tranet, and these global databases can be dissimilar. As more and more program developers use the Java language, there is a growing need for Java access to database operability.

The JDBC API defines a set of interfaces and classes for communicating with a database. These interfaces and classes are located in the java.sql package.

JDBC is an important part of using the network to operate the database, and JDBC is one of the core Java technologies. All the programs that need to operate the database with the network, JDBC programming has a problem, everything is useless.

Third, JDBC technology and use of the detailed

As you can see from the structure diagram, the JDBC API allows our Java application to connect to the JDBC driver using Jdbcdriver MANAGER[JDBC Drive management], which is what we are doing in our project, which is to import the drive jar package.

What is a driver?

Different database vendors or different database versions of the same vendor provide different drivers, and any application is driven to operate a specific vendor, specific version of the database.

The first step in using JDBC is to register (load) this driver.

Classification of the JDBC driver

The first type of JDBC driver is the JDBC-ODBC Bridge plus an ODBC driver.

This type of drive is generally not used in current programming applications.

The second type of JDBC driver is a driver for some JAVAAPI code that transforms a JDBC call into a native call to the main database API.

The third type of JDBC driver is a pure Java driver for database middleware, the JDBC call is transformed into a middleware vendor's protocol, and the middleware then translates these calls into the database API.

The fourth type of JDBC driver is a direct database-oriented, pure Java driver.

is the preferred way to access the database using the JDBC driver

through JDBC manipulating Databases -- steps:

1th Step: Register driver (only once)

2nd Step: Establish a connection (Connection)

3rd Step: Create a statement that executes SQL (Statement)

4th step: Execute the statement

5th step: Processing Execution results (RESULTSET)

6th step: Freeing up resources

Use JDBC First step: Load driver

There are three ways to register a drive:

1. Class.forName ("Com.mysql.jdbc.Driver");

This is not a recommended way to rely on specific driver classes

2. Drivermanager.registerdriver (Com.mysql.jdbc.Driver);

will be dependent on the specific driver class

3. System.setproperty ("Jdbc.drivers", "Driver1:driver2");

Although there is no dependency on the specific driver class, registration is not easy, so it is seldom used

Use JDBC Step Two: Establish a connection

To establish a connection through connection, connection is an interface class whose function is to connect to the database (session).

To establish an connection interface class object:

Connection conn =drivermanager.getconnection (URL, user, password);

Where the format requirements for URLs are:

JDBC: Sub-Protocol: Sub-name//hostname: Port/database name? Property name = attribute value &.

such as: "Jdbc:mysql://localhost:3306/test"

User is the username of the login database, such as root

Password is the password for the login database, empty to fill ""

Use JDBC Step Three: Create an Execution object

The Execution object statement is responsible for executing the SQL statement, which is generated by the connection object.

The statement interface class also derives two interface classes PreparedStatement and CallableStatement, both of which provide us with more powerful data access capabilities.

The syntax for creating statement is:

Statement st = Conn.createstatement ();

Use JDBC Fourth Step: Execute SQL Statement

The execution object statement provides two common methods to execute SQL statements.

ExecuteQuery (Stringsql), which executes an SQL statement that implements the query function, with a return type of resultset (result set).

such as: ResultSet RS =st.executequery (SQL);

Executeupdate (Stringsql), which executes SQL statements that implement add, delete, and change functions, with a return type of int, which is the number of rows affected.

such as: int flag = st.executeupdate (SQL);

Use JDBC Fifth step: Processing execution Results

ResultSet Object

The ResultSet object is responsible for saving query results resulting from statement execution.

The result set resultset is manipulated by cursors.

A cursor is a controllable pointer that can point to any record. With this pointer we can easily point out which of the records in the result set we want to modify, delete, or which record to insert the data before. Only one cursor is included in a result set object.

Use JDBC Sixth Step -- Freeing Resources

The Close method of the Connection object is used to close the connection and to release and connect related resources.

Using the jdbc--template

Some of the important interfaces----------------------------------------------------------------------------------------------------

preperedstatement Interface

Preperedstatement is extended from statement.

You need to execute SQL statements multiple times, and you can use PreparedStatement.

PreparedStatement can precompile SQL statements

And can be stored in the PreparedStatement object, which improves efficiency when executing SQL statements more than once.

As a subclass of statement, PreparedStatement inherits all functions of statement.

Create Preperedstatement

Preparedstatementstr=con.preparestatement ("Update user set id=?") where username=? "); /here? As a wildcard character

Other crud methods are basically consistent with statement.

CallableStatement Interface

The CallableStatement class inherits the PreparedStatement class, which is primarily used to execute SQL stored procedures.

Execution of SQL stored procedures in JDBC needs to be escaped.

The JDBC API provides an escape syntax for a SQL stored procedure:

{call<procedure-name>[<arg1>,<arg2>, ...]}

Procedure-name: Is the name of the SQL stored procedure to be executed

[<arg1>,<arg2>, ...] : is the parameter required for the corresponding SQL stored procedure

Resultsetmeta Interface

The object in front of the ResultSet interface class was used to obtain the data in the query result set.

But ResultSet function is limited, if we want to get such as how many columns in the query result set, the column name is what each must use the Resultsetmeta interface.

Resultsetmeta is the metadata for resultset.

Metadata is data, "data about data" or "data that describes data."

Use of Resultsetmeta

Get metadata objects

ResultSetMetaData Rsmd=rst.getmetadata ();

Returns the number of all fields

public int Getcolumcount () throwssqlexception

Gets the name of the field based on the index value of the field

Public String getcolumname (int colum) throws SQLException

Gets the type of the field based on the index value of the field

Public String getcolumtype (int colum) throws SQLException

preperedstatement Interfaces and Statement the Difference

(1) Sending and executing SQL statements using statement

Statement stmt = Con.creatstatement ();//load, no arguments, no SQL statements compiled

String sql = "Selete * from EMP";

ResultSet rs = stmt.executequery (sql);//Compile SQL statement when executing, return query result set

(2) Sending and executing SQL statements using Preparstatement

String sql = "Selete * from EMP";

Preparstatement ps=con.preparestatement (SQL);//compile SQL statements on load

ResultSet rs = Ps.executequery ();//When executed here, no parameters are required

And there is.

Statement stmt; Support can be string concatenation (to pass parameters), such as Stringdeletestu = "Delete from student where id=" +ID; but easy SQL injection

PreparedStatement PS; String stitching not supported, provided? Wildcard pass parameters, more secure, such as String deletestu = "Delete from student where id=?";

Ps.setint (1, id); Set the first one? A wildcard value is an ID

JDBC Transactions

What is a transaction?

A transaction is a set of logical operating units that transforms data from one state to another.

ACID properties of the transaction:

1. atomicity (atomicity): A transaction is an indivisible unit of work that either occurs in a transaction or does not occur.

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

3. Isolation of an isolated (isolation) transaction means that the execution of a transaction cannot be disturbed by other transactions.

4. Persistence (durability) persistence refers to the fact that once a transaction is committed, its changes to the data in the database are permanent, and the subsequent operations and database failures should not have any effect on it.

Use commit and rollback statements in JDBC to implement transactions.

Commit is used to commit a transaction, and rollback is used for database rollback.

These two statements ensure data integrity, preview data changes before they are committed, and group logically related actions.

Rollback statement data changes are canceled so that the state of the data before the modification of the transaction can be resumed.

In order for multiple SQL statements to be executed as a single transaction, the basic steps are:

The Setautocommit (false) of the Connection object is called first; To cancel the automatic commit transaction.

Then, after all the SQL statements have been executed successfully, commit () is called;

If an exception occurs, you can call rollback (), and the method rolls back the transaction.

JTA Transactions

In real-world applications, different connections to multiple data sources are often involved, and the operation of the data is in one transaction.

If you go to a bank to transfer funds, two accounts are different banks, of course, the operation of the data across the database, or in different data sources.

Transactions that span multiple data sources need to be implemented using the JTA container.

In a distributed case, JTA is a Web container, and a very familiar Tomcat cannot implement JTA operations.

Currently supporting JTA's container has a very well-known Bea company's WEBLOGIC,IBM Company's WebSphere Enterprise-class Web application server.

JTA into two-phase commit

The first step is to find related resources in a distributed environment through JNDI.

Javax.transaction.UserTransactiontx = (usertransaction) ctx.lookup ("Jndiname");

Tx.begin ()

Step Two, submit

Tx.commit ();

Learn Java JDBC, this is enough.