Shulou(Shulou.com)06/01 Report--

A brief introduction to JDBC

JDBC,Java Ddatabase Connection,Java database connection.

In order to simplify and unify the operation of the database, Sun has defined a set of specifications for Java to operate the database, which is called JDBC.

What is a driver? The two devices need to communicate and meet a certain communication data format, which is prescribed by the equipment provider. The equipment provider provides driver software for the device, which can communicate with the device through the software.

If there is no JDBC,Java programmer who needs to program for each database driver interface, the development is complex; sun company provides a set of unified JDBC interface specification, Java programs only need to use JDBC to operate any database, and JDBC implementation classes are provided by various database vendors.

1. The two packages that make up JDBC: java.sql and javax.sql.

DriverManger driver management class, Connection connection interface, Statement (PreparedStatement, CallableStatement) database operation, ResultSet result set.

two。 The development of jdbc applications requires not only the above two packages, but also the database implementation of the corresponding JDBC.

Second, getting started with JDBC

Create a user table

Create table user (id int primary key auto_increment, username varchar (20) unique not null, password varchar (20) not null, email varchar (40) not null); package cn.test;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import com.mysql.jdbc.Driver Public class Test1 {public static void main (String [] args) throws Exception {/ / load database driver DriverManager.registerDriver (new Driver ()); / / get connection Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/day15", "root", "root") / / create a Statement object to send SQL to the database and send SQL Statement stat = conn.createStatement (); / / fetch data from the result set String sql = "select * from user"; ResultSet rs = stat.executeQuery (sql) While (rs.next ()) {System.out.println (rs.getInt (1) + "," + rs.getString (2) + "," + rs.getString (3) + "," + rs.getString (4));} / / disconnect the database and release related resources rs.close () Conn.close ();}}

Third, JDBC programming steps

IV. The process of JDBC accessing the database

5. Detailed explanation of JDBC API

5.1DriverManager class

DriverManager in the jdbc program is used to load the driver and create a connection to the database. This is a common method of API:

DriverManager.registerDriver (new Driver); DriverManager.getConnection (url,username,password)

Note that it is not recommended to use the registerDriver method to register drivers in actual development for two reasons:

1) looking at the source code of Driver, you can see that if you use this approach, the driver will be registered twice, that is, there will be two Driver objects in memory.

Package com.mysql.jdbc;import java.sql.DriverManager;import java.sql.SQLException;public class Driver extends NonRegisteringDriver implements java.sql.Driver {static {DriverManager.registerDriver (new Driver ());} catch (SQLException E) {throw new RuntimeException ("Can't register driver!");}

2) the program depends on the API of MySQL, without the jar package of MySQL, the program will not be compiled, and it will be very troublesome for the program to switch the underlying database in the future.

Recommended method: class.forName ("com.mysql.jdbc.Driver")

In this way, the driver object will not be repeated in memory, and in this way, the program only needs a string and does not need to rely on the specific driver, which makes the program more flexible.

Similarly, it is not recommended to use a specific driver type to point to the Connection object returned by the getConnection method in development.

5.2 Database URL

URL is used to identify the location of the database. The programmer tells the JDBC program to connect to that database through the URL address. URL is written as follows:

The address of the common database URL is written as follows:

Oracle jdbc:oracle:thin:@localhost:1521:sidmysql jdbc:mysql://localhost:3306/sid

Common attribute: useUnicode=true&characterEncoding=UTF-8

5.3Connection connection interface

Application 1: get the operation object of SQL

Statement stat = conn.createSteatement () this object can send SQL to the database to execute PreparedStatement pstmt = conn.prepareStatement (String sql) to precompile SQL statements to prevent SQL from injecting CallableStatement cstmt = conn.prepareCall (String sql) this object can call stored procedures in the database

Application 2: manage database transactions

Conn.setAutoCommit (boolean flag) sets whether transactions commit automatically conn.commit () commit database transactions conn.rollback () rollback database transactions

5.4Statement is used to send SQL to the database to get the operation result.

The Statement object in the jdbc program is used to send SQL statements to the database. The common methods of Statement objects are:

ExecuteQuery (String sql) to send query statements to data executeUpdate (String sql) to send insert, update or delete statements to the database execute (String sql) to send arbitrary SQL statements to the database addBatch (String sql) to put multiple SQL statements into one batch executeBatch () to send batch execution to the database

5.5ResultSet

The ResultSet used in the jdbc program is used to represent the execution result of the SQL statement. ResultSet encapsulates the execution results in a table-like manner. The ResultSet object maintains a cursor pointing to the data row of the table. When initializing, the cursor before the first row calls the next () method, which makes the cursor point to a specific data row, and then calls the method to get that data.

Since resultSet is used to encapsulate the execution result, most of the methods provided by this object use the get method that gets the data:

Get any type of data

GetObject (int index) getObject (String columnName)

Get data of the specified type

GetString (int index) getString (String columnName)

5.6 release resources

After the jdbc program is running, remember to release the objects created by the program that interact with the database, usually ResultSet, Statement, and Connection objects.

In particular, the Connection object is a very rare resource, which must be released immediately after use. If the Connection is not shut down in time and correctly, it can easily lead to system downtime. The principle of using Connection is to create it as late as possible and release it as early as possible.

To ensure that the resource release code runs, the resource release code must also be placed in the finally statement.

6. JDBC completes the CRUD example

Add:

Private static void create () throws Exception {/ / load database driver Class.forName ("com.mysql.jdbc.Driver"); / / get connection Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/day15", "root", "root") / / create a Statement object to send SQL to the database and send SQL Statement stat = conn.createStatement (); String sql = "insert into user (username,password,email) values ('', '','hah@163.com')"; int rows = stat.executeUpdate (sql) If (rows > 0) {System.out.println ("execution added");} / / disconnect the database and release related resources conn.close ();}

Delete

Private static void delete () throws Exception {/ / load database driver Class.forName ("com.mysql.jdbc.Driver"); / / get connection Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/day15", "root", "root") / / create a Statement object to send SQL to the database and send SQL Statement stat = conn.createStatement (); String sql = "delete from user where username = '';"; int rows = stat.executeUpdate (sql) If (rows > 0) {System.out.println ("deletion completed");} / / disconnect the database and release related resources conn.close ();}

Modify

Private static void update () throws Exception {/ / load database driver Class.forName ("com.mysql.jdbc.Driver"); / / get connection Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/day15", "root", "root") / / create a Statement object to send SQL to the database, and send SQL Statement stat = conn.createStatement (); String sql = "update user set email = '123 records 163.com' where id = 1"; int rows = stat.executeUpdate (sql) If (rows > 0) {System.out.println ("modify completed");} / / disconnect the database and release related resources conn.close ();}

Query

/ / load the database driver Class.forName ("com.mysql.jdbc.Driver"); / / get the connection Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/day15", "root", "root"); / / create a Statement object for sending SQL to the database, and send SQL Statement stat = conn.createStatement () / / pull data from the result set String sql = "select * from user"; ResultSet rs = stat.executeQuery (sql); while (rs.next ()) {System.out.println (rs.getInt (1) + "," + rs.getString (2) + "," + rs.getString (3) + "," + rs.getString (4)) } / / disconnect the database and release related resources rs.close (); conn.close ()

7. DAO model

DAO mode (Data Access Object database access object) completely encapsulates the data source operation through DAO, and the business layer completes the operation to the data source through the Java object.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.