JDBC Database connection Technology

"Learning notes" JDBC Database connection technology (Java DB Connectivity)

I. Introduction of JDBC

Java is the use of JDBC technology to achieve access to various databases, JDBC is the Java database connection technology abbreviation. It can persist data and is a persistent mechanism.

1. Persistence

Persistence is the mechanism by which data in a program is transformed between the instantaneous state and the persistent state.

2.JDBC API

Connection Connection Interface

Statement interface

ResultSet result set interface

Perparedstatement interface

3.JDBC Driver Manager (Driver Manager)

Provided by Sun, it is the backbone of the JDBC architecture and is responsible for managing a variety of different drives (java.sql packages)

4.JDBC Drive

The JDBC driver is provided by various database vendors or third-party middleware vendors and is responsible for connecting various databases.

When developing Java applications, we only need to properly load the JDBC driver and properly call the JDBC API to make database access.

Second, the JDBC API

The 1.JDBC API does three things: establish a connection to the database, send SQL statements, and process the results.

DriverManager class: Loads the driver and provides support for creating a new database connection.

Connection class: Responsible for connecting to the database and acting as the data transfer task.

Statement class: Generated by the connection class, responsible for executing SQL statements.

ResultSet class: Responsible for saving and handling the results resulting from statement execution.

PreparedStatement class: Statement sub-interface, with high security, high performance, high readability and high maintainability of the advantages.

Third, JDBC access to the database steps

1. Load the JDBC Driver

Use the Class.forName () method to load a given JDBC driver class into a Java virtual machine. If a given class does not exist in the system, a ClassNotFoundException exception is thrown.

2. Establish a connection to the database

The DriverManager class is the management layer of JDBC, which acts between the user and the driver. Tracks the available drivers and establishes a connection between the database and the appropriate driver. When the getconnection () method is called, the DriverManager class first finds a driver from the list of loaded drivers that can receive the database URL, and then connects to the database with related data, creates the connection object, and returns the reference.

3. Send the SOL statement and get the return result

Once the connection is established, the object of the statement interface is created using the link and the statement is passed to the database to which it is connected: The query operation returns a result set of type resultset.

4. Handling return Results

Result set:

while (Rs.next ()) {

int id = rs.gitint ("id");

String name = rs.getstring ("name");

System.out.println (id+ "\ t" +name);

}

Four, two common driving modes

1.JDBC-ODBC Bridging mode (Driver class: "Sun.jdbc.odbc.JdbcOdbcDriver" database connection string starts with "JDBC:ODBC:" followed by the data source name)

Download ODBC driver

Configure an ODBC data source

2. Connect to a database using a pure Java approach

Common errors are in the following categories:

The name of the JDBC driver class is written incorrectly, and a classnotfoundexception exception occurs;

Database connection string, database user name, password write error, SqlException exception occurred;

After the database operation has ended, the database connection is not closed, resulting in system resources still being occupied.

The statement that closes the database connection is not placed in the finally statement, causing the statement to not be executed.

3. In the actual project development, in order to avoid the possible garbled problem, the establishment of the database connection encoding set for Utf-8,url connection:

URL = jdbc:mysql://126.0.0.1:3306/epet?useunicode=true&characterencoding=utf-8;

V. Methods of Statement,resultset,preparedstatement Class

1.Connection Interface Common Methods

Close (); Immediately releases the database and JDBC resources for this object

Statement createstatement (); Create an object to send SQL statements to the database

PreparedStatement preparestatement (String sql); Create an object to send a parameterized SQL statement to the database

Boolean isClosed () query whether the object is closed

2.Statement interface method

ResultSet executeQuery (String sql); Execute SQL query and get ResultSet object

int executeupdate (String sql), can perform insert, delete, update operation

Boolean execute (String sql), executes any SQL statement, returns TRUE if the result is a ResultSet object, or False if the update count or no result exists;

3.ResultSet interface method

Boolean next (); next line

Boolean previous (); previous line

void Close (); Close ResultSet Object

Int/float/string getxxx (int/string columnindex); Gets the result set in the form of xxx specified column-good values

int GetRow (); The current line number

Boolean adsolute (int row), cursor moves to the specified line

4.PreparedStatement interface method

Boolean execute (); Execute SQL statement on this object, result is ResultSet object returns True, other return False

ResultSet executeQuery (); return result set

int executeupdate (); Execute SQL statement (DML statement insert,update,delete; or DDL statement with no content returned), the return value is the number of rows affected by the operation

void Setint (int index,int x);

void setfloat (int index,float x);

void setdouble (int index,double x); Sets the setting parameter to the given Java int value.

void SetObject (int index,object x); Sets the value of the specified parameter with the given object

Vi. basic steps to operate a database using PreparedStatement

1. Create a PreparedStatement object

PreparedStatement pstmt = con.preparedstatement ("Update dog Set health=?,love=?") where id=? ");

The SQL statement has one or more input parameters: The values of these input parameters are not specified when the SQL statement was created, but instead each input parameter uses a placeholder? Alternative

2. Set the value of each input parameter

Call the Setxxx () method

3. Execute SQL statements

After you have set the parameters, you can invoke the execution method of the PreparedStatement interface to execute the SQL statement

[ResultSet executeQuery (), int executeupdate (), Boolean execute ()]

Seven, preparedstatement than statement good where

1. Improved readability and maintainability of the code. Avoid troublesome and error-prone stitching.

2. Improved performance of SQL statement execution.

Creating a statement object without using SQL statements for arguments, parsing and compiling SQL statements, parsing and compiling each time the method executes the SQL statement (same operation)

When you create a PreparedStatement object by using an SQL statement to make arguments, the statements are parsed and compiled. You can also use SQL statements with placeholders as parameters, and execute SQL statements without having to parse and compile again after assigning values through the Setxxx () method. Performing the same operation multiple times can greatly improve performance.

3. Improved security.

With precompiled statements, any parameters passed in will not be spliced with pre-compiled SQL statements, avoiding SQL injection attacks.

JDBC Database connection Technology