JDBC Experience-1-the first program to speak carefully

1. In order to simplify and unify the operation of the database, Sun has defined a set of Java Operations Database specification, called JDBC.

JDBC is all called: Java Data Base Connectivity (Java database connection), which is mainly composed of interfaces. An API consisting of interfaces and classes

2 packages that make up JDBC:

L java.sql

L javax.sql

The development of JDBC applications requires the support of the above 2 packages, as well as the implementation of the corresponding JDBC database (that is, database driven).

2. First JDBC Program:

programmatically reads data from a table and prints it in a command line window.

first, build the experimental environment:

1, create a library in MySQL, and create the user table and insert the table data.

2, create a new Java project, and import data driven.

Second, write the program, loading the database driver in the program

class.forname ("Com.mysql.jdbc.Driver") (recommended);

drivermanager.registerdriver (Driver Driver)

Third, establish the connection (Connection)

Connection con=drivermanager.getconnection (url,user,pass);

Iv. Create a statement object to send SQL to the database and send SQL

Statement st = Con.createstatement ();

ResultSet rs = st.excutequery (sql);

remove data from the resultset representing the result set and print to the command line window

Vi. Disconnect from the database and release related resources

Example:

package Com.hbsi.jdbcdemo;

import java.sql.*;

Public class Demo1 {

Public staticvoid Main (string[] args) throws Exception {

//1. Load Drive

//drivermanager.registerdriver (New Com.mysql.jdbc.Driver ());

class.forname ("Com.mysql.jdbc.Driver")/Recommended Use

//2. Create a connection

stringurl= "jdbc:mysql://localhost:3306/jdbcdb";

Connection con= drivermanager.getconnection (URL, "root", "root");

//3. Create a statement object

Statement st=con.createstatement ();

//4 Execute SQL statement

String sql= "selectid,name,password,email,birthday from users";

ResultSet rs=st.executequery (SQL);

//5 calendar result set

While (Rs.next ()) {

System.out.print (rs.getobject ("id"));

System.out.print ("T" +rs.getobject ("name")); System.out.print ("T" +rs.getobject ("password"));

System.out.print ("T" +rs.getobject ("email"));  System.out.println ("T" +rs.getobject ("Birthday"));

          }

//release of resources

rs.close ();

st.close ();

con.close ();

  }

}

3. the DriverManager in the JDBC program is used to load drivers and create links to the database, a common method of this API:

• Drivermanager.registerdriver (Newdriver ())

• drivermanager.getconnection (url,user, password)

Note: It is not recommended to use the Registerdriver method to register the driver in actual development. There are two reasons:

first, see driver source code can be seen, if this way, will cause the driver registered two times (driver itself with a driver object), that is, in memory there will be two driver objects.

second, the program relies on the MySQL API, from the MySQL jar package, the program will not compile, the future program to switch the underlying database will be very troublesome.

Recommended way: Class.forName ("Com.mysql.jdbc.Driver");

This approach does not cause the drive object to recur in memory, and in this way, the program only needs a string, does not need to rely on a specific driver, making the program more flexible.

L JDBC Driver type (four types)

(1) JDBC-ODBC Bridge JABC-ODBC Drive mode

(2) Native-api-bridge Java local API mode

(3) Java-middleware JDBC to network protocol mode

(4) Pure Java driver JDBC Direct database schema (focus of this chapter)

4.URL identifies the location of the database, and the programmer uses the URL address to tell the JDBC program which database to connect to:

jdbc:mysql:[]//localhost:3306/test Argument name = parameter value

Protocol Sub-Protocol host: Port number database

L the usual database URL address:

• Oracle: Jdbc:oracle:thin: @localhost: 1521:sid

• Sqlserver-jdbc:microsoft:sqlserver://localhost:1433;databasename=sid

• Mysql-jdbc:mysql://localhost:3306/sid

L MySQL URL address shorthand form: Jdbc:mysql:///sid

L Common Properties: Useunicode=true&characterencoding=utf-8

5.Connectioncon = drivermanager.getconnection (url, user, password);

connection in the JDBC program, which is used to represent the links of the database, collection is the most important object in database programming, and all interactions between client and database are done through connection objects, the common method of this object:

• createstatement (): Creates a statement object that sends SQL to the database.

• preparestatement (SQL): Creates a Preparesatement object that sends precompiled SQL to the database.

• preparecall (SQL): Creates the CallableStatement object that executes the stored procedure.

• Setautocommit (Boolean autocommit): Sets whether transactions are automatically committed.

• commit (): Commit the transaction on the link.

• rollback (): ROLLBACK transaction on this link.

6. Statement St =conn.createstatement ();

the statement object in the JDBC program is used to send SQL statements to the database, statement common methods for objects:

• executequery (String sql): Used to send query statements to data.

• executeupdate (String sql): Used to send INSERT, UPDATE, or DELETE statements to the database

• Execute (stringsql): Used to send arbitrary SQL statements to the database

• addbatch (String sql): Put multiple SQL statements into a batch.

• executebatch (): Sends a batch of SQL statement execution to the database.

7.ResultSetrs = st.executequery (sql);

the resultset in the JDBC program is used to represent the execution results of the SQL statement. ResultSet encapsulates execution results in a table-like manner. The ResultSet object maintains a cursor that points to the table data row, and at the beginning, the cursor calls the Resultset.next () method before the first row, allowing the cursor to point to a specific data row, making the calling method fetch the data for the row.

L resultset is used to encapsulate execution results, so this object provides a get method for fetching data:

• get data of any type

L getObject (int index)

L getObject (string columnName)

• get data of the specified type, for example:

L getString (int index)

L getString (String columnName)

While (Rs.next ()) {

rs.getstring ("Col_name");

rs.getint ("Col_name");

//...

}

8. Common data type conversion table

SQL Type	jdbc Corresponding method	return type
bit (1) bit	Getboolean getBytes ()	Boolean byte[]
TINYINT	getbyte ()	Byte
SMALLINT	Getshort ()	Short
Int	getInt ()	Int
BIGINT	Getlong ()	Long
Char,varchar,longvarchar	getString ()	String
Text (CLOB) Blob	Getclob GetBlob ()	Clob Blob
DATE	getDate ()	java.sql.Date
Time	getTime ()	Java.sql.Time
TIMESTAMP	Gettimestamp ()	Java.sql.Timestamp

The 9.ResultSet also provides a way to scroll the result set:

• Next (): Move to the next line

• Previous (): Move to previous line

• Absolute (introw): Move to specified line

• Beforefirst (): Move the front of the resultset.

• afterlast (): Move to the last side of resultset.

10. Release of resources

after the JDBC program finishes running, be sure to release the objects that are created by the program during the run that interact with the database, usually the resultset,statement and connection objects.

L especially Connection object, it is very rare resources, after use must immediately release, if connection not timely, correct shutdown, very easy to cause system downtime. The principle of connection is to create as late as possible and release as early as possible.

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