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 run 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 agrees with 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). Through 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 difference between function calls between different ODBC database drives. Provides a unified SQL programming interface for users.

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 will alter the application's request so that the request conforms to the grammar supported by the relevant DBMS.

Data source: Consists of the data that the user wants to access and the operating system, DBMS, and network platform that is used for accessing 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 runs ODBC function calls and mates with the database.

So when the 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. Make the necessary translations for the design of the analysis or data source. and return the results to the application system.

2. The birth of JDBC

Since the launch of the Java language in May 1995, Java has swept the world. A large number of programs written in the Java language appear. It also contains database applications. Because there is no Java-language API, programmers have to add C-language ODBC function calls in Java programs. This makes a lot of Java outstanding features can not be fully played, such as platform-independent, object-oriented features and so on. 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 the development of Java programs, and the requirements of the API for access to the Java language interface are increasingly strong. Also because ODBC has its shortcomings. It is not easy to use, for example. Without object-oriented features and so on, Sun decided to develop a Java language interface for the development of database applications.

In JDK1. In the x version number, JDBC is just an optional part. to the JDK1. When 1 was released, the SQL Class package (aka JDBCAPI) became the standard part of the Java language.

Second, JDBC Technical Overview

JDBC is a javaapi (applicationprogramminginterface) that can be used to run SQL statements. Application design Interface).

By using JDBC, developers can easily send SQL statements to almost any database.

In other words, developers have no need to write a program to access Sybase, and write a program to interview Oracle. Write a program to access Microsoft SQL Server.

A program written in JDBC is able to transmit SQL statements to the corresponding database management system (DBMS) on its own initiative. In addition, applications written in Java can be executed on any Java-enabled platform without the need to write different applications on different platforms. The combination of Java and JDBC allows developers to really implement "writeonce" when developing database applications. Runeverywhere. ”

Java has features such as robustness, security, ease of use, and so on. and support their own initiative on-line download, is essentially a very good database application programming language. What it needs is how Java applications connect with a wide variety of databases, and JDBC is the key to achieving such a connection.

JDBC extends the capabilities of Java. If you use Java and JDBCAPI, you can publish a Web page with an AP plet that can access the remote database. Or the enterprise is able to use JDBC to get all the employees (they have access to different operating systems). such as Windwos. Machintosh and Unix) are connected to several global databases on the in Tranet, and these global databases can be not the same. As more and more program developers use the Java language. The need for Java access to database accessibility is growing.

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 very important to (let our program) operate the database through the network, and JDBC technology is one of the core technologies of Java.

All the programs that need to operate the database with the network, JDBC programming has a problem, everything is useless.

Third, JDBC technical and use specific explanations

Can be seen from the structure diagram. Using the JDBC API to enable our Java applications to connect to the JDBC driver using Jdbcdriver MANAGER[JDBC Drive management], we are now importing drive jar packages in project.

What is a driver?

Different database vendors or different database version numbers from the same vendor will be provided with various drivers. No matter what application is using this driver to operate a specific vendor, a specific version number 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 today's 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, and the JDBC call is converted into a middleware vendor protocol. The middleware then transforms 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: Manual Drive (only once)

2nd Step: Establish a connection (Connection)

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

4th step: Run the statement

5th step: Processing Running results (RESULTSET)

6th step: Freeing up resources

Use JDBC First step: Load driver

There are three ways to drive a brochure:

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

This is not a recommended way to rely on a detailed driver class.

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

will be dependent on the detailed driver class.

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

Although it is not dependent on the detailed driver class, it is not convenient to register. So very little use

Use JDBC Step Two: Establish a connection

A connection is established through connection, and connection is an interface class. Its function is to connect to the database (session).

To establish an connection interface class object:

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

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 a Running object

Running the object statement is responsible for running the SQL statement. 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: Run SQL Statement

The Run Object statement provides two frequently used methods to run SQL statements.

ExecuteQuery (Stringsql), which is used to run SQL statements that implement query functionality. The return type is resultset (result set).

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

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

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

Use JDBC Fifth step: Working with running results

ResultSet Object

The ResultSet object is responsible for saving the query results generated after the statement run.

The result set resultset is manipulated by cursors.

A cursor is a controllable pointer that can point to a random record.

With this pointer we can easily point out which of the records in the result set we want to change, 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.

SQL statements need to be run multiple times to be able to use PreparedStatement.

PreparedStatement ability to precompile SQL statements

And can be stored in the PreparedStatement object. Improves efficiency when running 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? 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 run SQL stored procedures.

Running 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 that you want to run

[<arg1>,<arg2>, ...] : The number of parameters 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.

However, the ResultSet function is limited, assuming that we want to get the number of columns in the query result set, the name of the column is what the Resultsetmeta interface must be used.

Resultsetmeta is the metadata for resultset.

Metadata is data, "data about data" or "data describing descriptive 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 running SQL statements using statement

Statement stmt = Con.creatstatement ();//load-in, no number of references. Do not compile SQL statements

String sql = "Selete * from EMP";

ResultSet rs = stmt.executequery (sql);//compile SQL statements at run time. Return query result set

(2) Sending and running SQL statements using Preparstatement

String sql = "Selete * from EMP";

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

ResultSet rs = Ps.executequery ();//When running here, you do not need to pass

And there is.

Statement stmt; Support capable of string concatenation (to pass parameters), such as Stringdeletestu = "Delete from student where id=" +ID; Easysql injection

PreparedStatement PS; String stitching not supported, provided? Wildcard pass-through 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): Refers to a transaction is a non-cutting unit of work, the operations in the transaction will either occur. It's either not happening.

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

3. The isolation of the isolation (isolation) transaction is that the operation of one transaction cannot be disturbed by other transactions.

4. Persistence (durability) persistence refers to a transaction being committed once. It has a permanent change to the data in the database, and the next 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. Data changes are previewed before they are submitted, and logically related actions are grouped.

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

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

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

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

Assume that you can call rollback () When an exception occurs, and that the method roll back the transaction.

JTA Transactions

In practical applications, it often involves different connections of multiple data sources, 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 the case of distributed JTA is the Web container, the familiar tomcat can not implement the JTA operation.

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

JTA into two-phase commit

The first step. 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.