Java Database Connectivity

Objective

JDBC is an API for executing SQL in a Java program that provides a set of interfaces and classes for the Java Connection database.

Body JDBC Use steps

The process of connecting to a database: Java APP->JDBC API->driver;

Preparing data for connecting to the database

1. Get the user name and password for the current database connection

2. Obtain the address (IP) of the database server

3. Get the port number of the database connection Oracle default is 1521

4. Get the instance SID of the library, which is the database name

Get the connection string

Url=jdbc:oracle:thin: @ip:p Ort:sid

Get the driver for the corresponding database

Classes12.jar or Ojdbc14.jar

Writing JDBC Program load driver

Import the drive Oracle.jdbc.OracleDriver or oracle.jdbc.driver.OracleDriver of the Oracle database

Class.forName ("Oracle.jdbc.OracleDriver");//Create an instance of Oracledriver in memory by loading the driver through reflection

Obtaining a Connection object from the drive manager

Connection conn=drivermanager.getconnection () url,user,password;;/ /Get the Connection object through the drive manager

Create a statement or Preparestatement object

Build the statement implementation class object to compile SQL and send SQL statements to the database

Statement Stmt=conn.createstatemenr ();

Mode two: PreparedStatement pstmt=conn.preparestatement (SQL);//sql character type no semicolon to end

Execute SQL, receive return results

ResultSet rs=stmt.executequery (SQL);;

Mode two: ResultSet rs=pstmt.executequery ();

Looping through result sets

while (Rs.next ()) {

int Empno=rs.getint (String columnName);

}

Close a resource in finally

if (rs!=null) {rs.close ();}

if (pstmt!=null) {pstmt.close ();}

if (conn!=null) {rconnclose ();}

Note:

1.prepareCall (String sql); Used to invoke a stored procedure

2.conn.setautocommit (false);//Set the current JDBC food handling set to Manual

Conn.rollback ();

Conn.commit ();

Differences between the 3preparedStatement and statement interfaces

PreparedStatement allows the database to precompile SQL statements, which saves time in subsequent runs and increases the readability of the query; statement the SQL statement is compiled every time the database that executes the SQL statement is executed.

The JDBC code sample encapsulates the JDBC code sample

Pre-compiled SQL

Precompiled SQL will pre-compile the SQL statement and pass parameters directly when executing, without having to compile again.

PreparedStatement is the sub-interface of statement, which differs from statement:

1. Better readability and maintainability of the program

2. More secure

3. High execution efficiency

Pagination

Select x.* from (select E.*, RowNum RR from emp e) x where x.rr between start and end;

Paging has query-based paging and cache-based paging.

Paging based on query

Through the database of shoddy, each query part of the data returned.

1. Features

1) paging operation for large data volumes

2) The paging execution is inefficient and interacts with the database frequently.

Query-based pagination code example

Paging based on cache

Executes SQL once, queries all the data of the result set, stores it in the buffer of the current application server, and then displays the paging through the scrollable result set;

1. Features

1) will put the result set all into memory, for the memory pressure is large

2) Paging operations that are not suitable for large data volumes

3) When paging, the first execution efficiency is slow, the execution efficiency is high later.

Result set ResultSet

ResultSet is only traversed forward by default and can only be traversed once.

Constants of the ResultSet

1.static int Type_scroll_insensitive
The constant indicating the type for a ResultSet object that's scrollable but generally isn't sensitive to changes to the D ATA that underlies the ResultSet. Setting the resulting set of results is scrollable and insensitive (data changes in the database are not reflected in a timely manner to the result set) and are not affected by ResultSet underlying data changes, whereas sensitivity refers to the rowid of the SQL result set in the cache, which is displayed by rowID query.

2.static int Type_scroll_sensitive
The constant indicating the type for a ResultSet object which is scrollable and generally sensitive to changes to the data That underlies the ResultSet.

3.static int Concur_read_only
The constant indicating the concurrency mode for a ResultSet object, and not being updated. Set result set read-only

4.static int concur_updatable
The constant indicating the concurrency mode for a ResultSet object, can be updated. Sets the updatable result set.

1). Update

1)) void updatestring (int columnindex, String x)
Updates the designated column with a String value. Update fields for the current record
2)) void Updatestring (String ColumnLabel, String x)
Updates the designated column with a String value.
3)) void UpdateRow ()
Updates to the underlying database with the new contents of the current row of this ResultSet object. Update record to databases (synchronous)

2). Positioning

1)) int GetRow ()
Retrieves the current row number. Get line number
2)) void Beforefirst ()
Moves the cursor to the front of this ResultSet object, just before the first row. Before you navigate to the first record
3)) void Afterlast ()
Moves the cursor to the end of this ResultSet object, just after the last row. After you navigate to the last record
4)) void DeleteRow ()
Deletes the current row from this ResultSet object and from the underlying database. Deletes the record of the active line and synchronizes it to the databases.

3). Insert

1)) void Movetoinsertrow ()
Moves the cursor to the insert row. Point the pointer to the row where the data will be inserted
2)) void Updateint (int columnindex, int x)
Updates the designated column with a int value.
3)) void InsertRow ()
Inserts the contents of the insert row into this ResultSet object and into the database.

Scrollable result set code example

Database Connection Pool

Database connection pooling saves time for creating connections, and reduces the resources consumed by connection opening and closing.

Import a database connection pool-dependent jar package

Commons-dbcp-1.4.jar and Commons-pool.jar

Import a database driver package

Ojdbc14.jar

Writing a connector to create a connection pool object

Basicdatasource dbs=new Basicdatasource ();

Dbs.seturl (jdbc:oracle:thin: @ip:p ort:sid);

Dbs.setusername ("username");

Dbs.setpassword ("password");

Dbs.setdriverclassname ("Oracle.jdbc.OracleDriver");

Getting a database connection from a connection pool object

Connection conn=bds.getconnection ();

Database Connection Pool code example

JDBC Batch Processing

Large-scale execution of SQL, batch processing for increased efficiency

JDBC Batch code sample

Summarize

1.JDBC has a set of application APIs for developing Java-connected databases, and the JDBC driver API is provided to the database vendor, which is responsible for implementing the underlying encoding.

2. Summarize the interfaces and classes used by the classes and interfaces that are involved in JDBC
1) Java.sql.Connection Interface

1)) createstatement () Get statement
2)) preparestatement (String sql) Get PreparedStatement
3)) Preparecall (String sql) call stored procedure

2) Java.sql.DriverManager class

1)) getconnection (string URL, string user, string password)

3) Java.sql.Statement Interface

1)) ExecuteQuery (String SQL) Execute DQL statement
2)) executeupdate (String SQL) execution DML statement
3)) ExecuteBatch () batch execution sql

4) Java.sql.PreparedStatement Interface

1)) The Setxxx method is used to dynamically transmit parameters

5) Java.sql.ResultSet Interface

1) Next () The default ResultSet object points to the record cursor before the first one
2)) GetXXX (string/int) Get the value of the corresponding field
3)) GetInt () Receive integer type
4)) getdouble () receive floating point data
5)) GetString () Receive String type
6)) SQL. Date getDate () receives the Date type data field