Learn Java JDBC, look at this one is enough __java

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

JDBC is a Java API that can be used to execute SQL statements (applicationprogramming interface application design interfaces). 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, the development of ODBC to JDBC process

ODBC is a opendatabaseconnectivity English shorthand. It is a standard application data interface used in C language to access 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 interfaces used by each DBMS.

1. The 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 differences in function calls between different ODBC database drives, providing users with a unified SQL programming interface.

Drive Manager: Mounts the database drive for the application.

Database drive: A function call that implements ODBC to provide SQL requests to a specific data source. If necessary, the database drive modifies the application's request, allowing the request to conform to the grammar supported by the associated DBMS.

Data source: The data that the user wants to access and the operating system and DBMS associated with it, 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 performs an ODBC function call and is compatible with the database. Therefore, when an application makes a call to connect to the data source, the database drive manages the communication protocol. When a connection to a data source is established, the database drive can process requests from the application system to the DBMS, translate the necessary translations into the analysis or design from the data source, and return the results to the application system.

2. The birth of JDBC

Java has been popular all over the world since the Java language was officially published in May 1995. There are a number of programs written in the Java language, including database applications. Because there is no Java language API, programmers have to add a C-language ODBC function call to the Java program. This makes a lot of Java's outstanding features can not be fully played, such as platform independence, object-oriented characteristics. As more and more programmers become more and more interested in the Java language, more and more companies are devoting more and more effort to Java program development, and the API of accessing database of Java language interface is becoming more and more strong. And because ODBC has its drawbacks, such as its not easy to use, no object-oriented features, and so on, Sun decides to develop a database application development interface for the Java language interface. In JDK1. In the X version, JDBC is just an optional part, to the JDK1. When 1 is published, the SQL class package (i.e. JDBCAPI) becomes the standard part of the Java language.

Ii. Overview of JDBC Technology

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

By using JDBC, developers can easily deliver SQL statements to almost any database. That is, developers can not write a program to access Sybase, write another program to access Oracle, and then write a program to access Microsoft SQL Server. A program written with JDBC can automatically transmit SQL statements to the appropriate database management system (DBMS). Furthermore, applications written in Java can be run on any Java-enabled platform without the need to write different applications on different platforms. The combination of Java and JDBC allows developers to truly implement "Writeonce,runeverywhere" when developing database applications. ”

Java, with its robustness, security, ease of use, and support for automated online downloads, is essentially a good programming language for database applications. What it needs is how Java applications connect to a wide variety of databases, and JDBC is the key to achieving this connection.

JDBC extends Java capabilities, such as using Java and JDBCAPI to advertise a Web page with AP Plet with access to remote databases. Or the enterprise can connect to several global databases in the tranet via JDBC to all employees (who can use different operating systems, such as Windwos,machintosh and Unix), and these global databases can vary. As more and more program developers use the Java language, the need for Java access to databases is increasingly strong.

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

JDBC is used to (let our program) operate the database through the network, the function is very important, JDBC technology is also one of Java core technology. Any need to use the network Operation database program, JDBC programming problems, everything is in vain.

Third, JDBC technology and the use of detailed

As you can see from the structure diagram, the JDBC API allows our Java applications to connect to the JDBC driver using Jdbcdriver MANAGER[JDBC driver Management, which is embodied in our project to import the driver jar package.

What is drive.

Different database vendors or different database versions of the same vendor provide different drivers, which are used by any application to manipulate a specific vendor, a specific version of the database.

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

Classification of JDBC Drivers

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 partial JAVAAPI code driver that converts a JDBC call into a native call to the mainstream database API.

The third type of JDBC driver is a pure Java driver for database middleware, which is converted into a middleware vendor's protocol, and the middleware converts these calls to the database API.

The fourth type of JDBC driver is a pure Java driver that is directly oriented to the database .

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

operating the database through JDBC-step:

1th Step: Register the driver (only once)

2nd Step: Establish the connection (Connection)

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

Step 4th: Execute the statement

Step 5th: Process Execution Results (RESULTSET)

6th step: Releasing Resources

Using JDBC First step: Load Driver

There are three ways to register a driver:

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

This approach is recommended and does not rely on specific driver classes

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

is dependent on the specific driver class

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

Although not dependent on the specific driver class, it is not easy to register, so it is rarely used

Using JDBC Step two: establishing a connection

Establishing a connection through connection, connection is an interface class with the function of connecting to the database (session).

Establish connection interface class objects:

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

Where the format of the URL is required:

JDBC: Sub-Protocol: Sub-name/host name: Port/Database name. Property name = attribute value ....

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

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

Password is the password to login to the database, fill in the blank ""

Using JDBC Step three: Creating 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, which provide us with more powerful data access capabilities.

The syntax for creating statement is:

Statement st = Conn.createstatement ();

Using JDBC Step Fourth: Executing SQL statements

Executing an object statement provides two common methods for executing SQL statements.

ExecuteQuery (Stringsql), which executes the SQL statement that implements the query function, and returns the type resultset (result set).

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

Executeupdate (Stringsql), which executes the SQL statement for adding, deleting, and modifying functions, and returns the type int, that is, the number of rows affected.

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

Working with JDBC Step Fifth: Handling Execution results

ResultSet objects

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

The result set resultset is manipulated through cursors.

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

Using JDBC Step Sixth-releasing resources

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

Using the jdbc--template

Some important interfaces----------------------------------------------------------------------------------------------------

Preperedstatement interface

Preperedstatement expanded 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 can be more efficient when executing SQL statements more than once.

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

Create Preperedstatement

Preparedstatementstr=con.preparestatement ("Update user set id=?") where username=? "); /here? for wildcard characters

Other crud methods are basically consistent with statement.

CallableStatement interface

The CallableStatement class inherits the PreparedStatement class, which is used primarily for executing SQL stored procedures.

Executing SQL stored procedures in JDBC requires escaping.

The JDBC API provides an escape syntax for an 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 required parameter for the corresponding SQL stored procedure

Resultsetmeta interface

The objects in the ResultSet interface class are used earlier to obtain the data in the query result set.

But ResultSet functionality is limited, and if we want to get the number of columns in the query result set, what the column name is, we have to use the Resultsetmeta interface.

Resultsetmeta is the resultset metadata.

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

The use of Resultsetmeta

Getting metadata objects

ResultSetMetaData Rsmd=rst.getmetadata ();

Returns the number of all fields

public int Getcolumcount () throwssqlexception

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

Public String getcolumname (int colum) throws SQLException

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

Public String getcolumtype (int colum) throws SQLException

The difference between Preperedstatement interface and statement

(1) using statement to send and execute SQL statements

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

String sql = "Selete * from EMP";

ResultSet rs = stmt.executequery (SQL)//Compile SQL statement at execution time, return query result set

(2) using Preparstatement to send and execute SQL statements

String sql = "Selete * from EMP";

Preparstatement ps=con.preparestatement (SQL);//Compile SQL statement on load

ResultSet rs = ps.executequery ()//When executed here, no arguments are required

And there is

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

PreparedStatement PS; string concatenation is 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. Wildcard value is ID

JDBC Transaction

What is a transaction.

A transaction is a group of logical operational units that transform data from one state to another.

ACID Properties for transactions:

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

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

3. Isolation of isolation transactions means that the execution of a transaction cannot be interfered with by other transactions.

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

The commit and rollback statements are used in JDBC to implement transactions.

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

These two statements ensure data integrity, preview before data changes are submitted, and group logically related operations.

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

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

Invokes the Setautocommit (false) of the Connection object first; To cancel autocommit transactions.

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

If an exception is encountered, you can call rollback (), and cause the method to roll back the transaction.

JTA Affairs

In practical applications, it often involves the different connections of multiple data sources, and the operation of the data is in a transaction.

If you go to a bank to make a transfer, the two accounts that you transfer are different banks, and of course the data operates across the database, or it is called a different data source.

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

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

The currently supported JTA container has an enterprise-class Web application server, such as WebSphere, a very famous Bea company's WEBLOGIC,IBM company.

JTA split into two-stage submissions

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 ();