In-depth analysis of JavaWeb Item27 -- JDBC Learning

In-depth analysis of JavaWeb Item27 -- JDBC Learning
1. Introduction to JDBC concepts1.1. database-driven

The concept of the driver here is the same as that of the driver that you hear at the same time. For example, if you buy a sound card at ordinary times, you cannot directly Insert the NIC into the computer, the sound card and network card must be installed after the corresponding driver is installed. Similarly, after we have installed the database, our applications cannot directly use the database, you must use the corresponding database driver to deal with the database, as shown below:

　　

1.2 introduction to JDBC

To simplify and unify database operations, SUN defines a set of Java Database Operation Specifications (interfaces) called JDBC. This interface is implemented by the database vendor. In this way, developers only need to learn the jdbc interface and load the specific driver through jdbc to operate the database.

As shown in:

　　

JDBC is called Java Data Base Connectivity (java database connection). It is mainly composed of interfaces.
Two JDBC packages:
Java. SQL
Javax. SQL
To develop a JDBC application, you must import the corresponding JDBC database implementation (that is, the database driver) in addition to the support of the above two packages ).

2. Compile a JDBC Program

2.1 build an experimental environment

1. Create a database in mysql, create a user table, and insert Table data.

The SQL script is as follows:

create database jdbcStudy character set utf8 collate utf8_general_ci;use jdbcStudy;create table users(    id int primary key,    name varchar(40),    password varchar(40),    email varchar(60),    birthday date);insert into users(id,name,password,email,birthday) values(1,'zhansan','123456','[email protected]','1980-12-04');insert into users(id,name,password,email,birthday) values(2,'lisi','123456','[email protected]','1981-12-04');insert into users(id,name,password,email,birthday) values(3,'wangwu','123456','[email protected]','1979-12-04');

2. Create a Java project and import the data driver.
　　
　　

3. Write a program to read data from the user table and print the data in the command line window.
The Code is as follows:

Package me. gacl. demo; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. statement; public class JdbcFirstDemo {public static void main (String [] args) throws Exception {// URL of the database to be connected String url = "jdbc: mysql: // localhost: 3306/jdbcStudy "; // username String username =" root "when connecting to the database; // password String password =" XDP "when connecting to the database "; // 1. load the driver // DriverManager. registerDriver (new com. mysql. jdbc. driver (); this method is not recommended to load the Driver Class. forName ("com. mysql. jdbc. driver "); // This method is recommended to load the Driver // 2. obtain the Connection with the database. conn = DriverManager. getConnection (url, username, password); // 3. obtains the statement Statement st = conn used to send SQL statements to the database. createStatement (); String SQL = "select id, name, password, email, birthday from users"; // 4. send an SQL statement to the database and obtain the resultset ResultSet rs = st.exe cuteQuery (SQL) that represents the result set. // 5. retrieve the result set data while (rs. next () {System. out. println ("id =" + rs. getObject ("id"); System. out. println ("name =" + rs. getObject ("name"); System. out. println ("password =" + rs. getObject ("password"); System. out. println ("email =" + rs. getObject ("email"); System. out. println ("birthday =" + rs. getObject ("birthday");} // 6. close the link and release the resource rs. close (); st. close (); conn. close ();}}

The running result is as follows:

　　

2.2 DriverManager

The DriverManager In the Jdbc program is used to load the driver and create a connection to the database. The common method of this API is as follows:

DriverManager.registerDriver(new Driver())DriverManager.getConnection(url, user, password)，

Note:The registerDriver method is not recommended in actual development.There are two reasons:
1. Check the Driver source code. If this method is used, the Driver will be registered twice, that is, there will be two Driver objects in the memory.
2. The program depends on the mysql api and cannot be compiled without the mysql jar package. It will be very troublesome to switch the program to the underlying database in the future.

Recommended Methods:Class. forName ("com. mysql. jdbc. Driver ");
Using this method will not cause the drive object to repeat in the memory, and using this method, the program only needs a string and does not need to depend on a specific driver, so that the program is more flexible.

2.3 Database URL explanation

The URL is used to identify the location of the database. The URL address is used to tell the database to which the JDBC program connects. The URL is written as follows:

　　

Common Database URL:

Oracle statement:jdbc:oracle:thin:@localhost:1521:sid
SQL Server statement:jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sid
MySql statement:jdbc:mysql://localhost:3306/sid

If the connection is a local Mysql database and the port used for the connection is 3306, the url address can be abbreviated:Jdbc: mysql: // Database

2.4 Connection

The Connection in the Jdbc program is used to represent the connection of the database. Collection is the most important object in database programming. All the interactions between the client and the database are completed through the Connection object. The common method of this object is as follows:

CreateStatement (): Creates a statement object that sends SQL statements to the database. PrepareStatement (SQL): Creates a PrepareSatement object that sends pre-compiled SQL statements to the database. PrepareCall (SQL): Creates a callableStatement object that executes the stored procedure. SetAutoCommit (boolean autoCommit): sets whether a transaction is automatically committed. Commit (): Submit the transaction on the link. Rollback (): rolls back the transaction on this link.

2.5 Statement

The Statement object in the Jdbc program is used to send SQL statements to the database. Common Methods of the Statement object are as follows:

ExecuteQuery (String SQL): used to send query statements to Data. ExecuteUpdate (String SQL): used to send an insert, update, or delete statement to a database execute (String SQL): used to send any SQL statement to the database addBatch (String SQL ): put Multiple SQL statements in one batch. ExecuteBatch (): sends a batch of SQL statements to the database for execution.

2.6 ResultSet class explanation

The ResultSet In the Jdbc program is used to represent the execution result of the SQL statement. When the Resultset encapsulates the execution result, it adopts a table-like approach. The ResultSet object maintains a cursor pointing to the table data row. Initially, the cursor is called before the first row. the next () method allows the cursor to point to a specific data row and call the method to obtain the data of the row.
Since the ResultSet is used to encapsulate execution results, this object provides the get method for getting data:
Obtain any type of data
　　　　

getObject(int index)　　　　getObject(string columnName)

Obtain data of the specified type, for example:
　　　　

getString(int index)　　　　getString(String columnName)

ResultSet also provides a method to scroll the result set:

Next (): Move to the next row Previous (): Move to the first row absolute (int row): Move to the specified row beforeFirst (): Move the beginning of the resultSet. AfterLast (): Move to the end of the resultSet. 2.7 release resources

After the Jdbc program is run, remember to release the objects created during the running process to interact with the database. These objects are generally ResultSet, Statement, and Connection objects, especially Connection objects, it is a rare resource and must be released immediately after use. If the Connection cannot be closed in a timely and correct manner, it can easily cause system downtime. The usage principle of Connection is to create a Connection as late as possible and release it as early as possible.
To ensure that the resource release code can run, the resource release code must also be placed in the finally statement.

finally{    if (rs != null) {        try {            rs.close();        } catch (SQLException sqlEx) {            // You'd probably want to log this . . .        }        rs = null;    }    if (stmt != null) {        try {            stmt.close();        } catch (SQLException sqlEx) {            // You'd probably want to log this as well . . .        }        stmt= null;    }    if (conn != null) {        try {            conn.close();            }        } catch (SQLException sqlEx) {            throw sqlEx;        }        conn = null;    }