Today’s sharing started, please give us more advice ~

I. JDBC Overview

(1)JDBC API

SQL is a set of classes and interfaces written in the Java language and contained in two packages java.sql and javax. SQL.

The java.sql package is the core package included in J2SE. The Javax. SQL package extends the functionality of the JDBC API and becomes a basic part of J2EE.

It can be divided into two levels:

  • The underlying JDBC Driver API is mainly used to develop the underlying drivers for database vendors

  • JDBC API for programmers

(2)JDBC API architecture

(3)JDBC API tasks

The JDBC API for programmers can accomplish the following major tasks

  • First establish a connection to the data source

  • SQL commands such as queries and modifications are then delivered to it

  • Finally, the results of the SQL execution returned by the data source are processed

(4) Important interfaces and classes in the JDBC API

(5)JDBC program development steps

Set up the environment and introduce the appropriate JDBC classes

Select the appropriate JDBC driver and load it

Assign a Connection object

Allocate a Statement object

You can use the Statement object to perform operations such as querying

Retrieves data from the returned ResultSet object

Close the Connection

Two, set the environment

Install Java and JDBC apis (automatically installed when you install the JDK)

Install the database driver (install the JDBC-ODBC bridge driver automatically with the JDK installation)

Install the DBMS

Create a database and register the data source

Third, establish a connection

The next step is to establish a connection to the DBMS. There are two steps:

Loading drive, with the Class. The class.forname explicitly load the driver, establish a connection to the database, called DriverManager. GetConnection () method.

Load driver

Explicitly load the driver with the class.forname method, as in:

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

Loads the Class, taking the full Java Class name string as an argument, and returns a Class object describing the Class.

Executing the above code automatically creates an instance of the driver class and registers it by calling the RegisterDriver method in the DriverManager class DriverManager.

“Sun. JDBC. Odbc. JdbcOdbcDriver” here is the name of the driver class, you can get from the driver’s documentation.

Note that using this method may throw a ClassNotFoundException if there is a chance that the drive class does not exist, so catch this exception:

Establish a connection

Establish connection to the database, called DriverManager. GetConnection () method. For example, to connect to the Access database PIMS, the following statement would be used:

Connection con = DriverManager.getConnection(“jdbc:odbc:PIMS”, “test”,”1234″);

The connection to the specified database is returned

This method takes three string arguments:

1. The first is the JDBC URL in the format of JDBC: subprotocol: subname

  • Jdbc stands for protocol. The protocol in Jdbc URL is always Jdbc.

  • The subprotocol is the drive name;

  • The subname is the name of the database, and if it is a database on a remote server, it should also include the network address, // hostname: port/database name.

2. The second is the user name needed to access the database.

3. The third is the user password.

Connection is an interface that represents a Connection to a specified database.

The DriverManager class resides at the JDBC management level between the user and the driver. It is responsible for keeping track of all JDBC drivers available in a system and establishing connections between the database and the corresponding drivers.

Four, to operate the database

After establishing a connection to the database, you can perform operations on the database, which generally include the following three steps:

Create a Statement object using the Connection object

Execute SQL commands using the Statement object

Extract the execution result from the ResultSet object returned in the previous step

(1) Create a Statement object

The Connection interface has three methods for creating objects that send SQL statements to the database:

CreateStatement:

Create a Statement object that sends SQL statements to the database for simple SQL statements.

Statement stmt = con.createStatement();

prepareStatement:

Create a PreparedStatement object that sends SQL statements to the database for SQL statements with one or more parameters. These parameters are assigned before the SQL statement is executed

prepareCall:

Create a CallableStatement object that sends SQL statements to the database to invoke stored procedures in the database

(2) Execute statements using Statement objects

The Statement interface provides three methods for executing SQL statements. The method used depends on the content produced by the SQL Statement:

executeQuery

A statement used to produce a single result set, such as a SELECT statement.

ResultSet rs = stmt.executeQuery(“Select * From Person”);

executeUpdate

Used to execute INSERT, UPDATE, or DELETE statements, and CREATE TABLE.

Stmt.executeupdate (“DELETE FROM Person WHERE Name=’ SQL ‘”);

The return value is an integer indicating the number of rows affected (that is, the update count), such as how many rows were modified, deleted, and so on. For statements such as CREATE TABLE, the return value of executeUpdate is always zero because there are no row operations involved.

Execute

Used to execute a statement that returns multiple result sets (ResultSet objects), multiple update counts, or a combination of both. For example, when executing a stored procedure or executing SQL dynamically, it is possible to have multiple results.

(3) Extract the execution results

After the query results are returned as a ResultSet object, we can extract the results from the ResultSet object

Using the next method

The ResultSet object contains retrieved rows with an indicator that points to the currently operable row, initially before the first row.

The function of the next method is to move the indicator down one line, so the first call to the next method points the indicator to the first line, and every subsequent successful call to next moves the indicator down one line.

Use the getXXX method

Different types of data can be extracted from the specified column of the current row using the corresponding type of getXXX method. For example, the getString method is used to extract VARCHAR data, while the getFloat method is used to extract FLOAT data.

Column names or column ordinals are allowed as arguments to the getXXX method

String s = rs.getString(“Name”);

Extract the data in the Name column of the current row, convert it from THE SQL VARCHAR type to the Java String type, and assign it to the object S.

String s = rs.getString(2); // Extract the second column of the current row

The column ordinal here refers to the column ordinal in the result set, not the column ordinal in the original table.

5. Application examples

Access the PIMS database using JDBC to query and add data:

The execution result is

1 zhang SAN

2 li si

3 fifty

4 Korea six

After adding data, the following information is displayed:

1 zhang SAN

2 li si

3 fifty

4 Korea six

9 cemetery

After deleting data, the following information is displayed:

1 zhang SAN

2 li si

3 fifty

4 Korea six

Today’s share has ended, please forgive and give advice!

How do I get it?

Forward to share this article, background private letter xiaobian: “1” can be obtained. (Note: Forwarding sharing, thank you)