Common techniques in Java database programming

1. Basic process of Java database operation

2, a few common important skills:

Scrollable, updated recordset

Batch Update

Transaction processing

Basic process of Java database operation: Get database connection-Execute SQL statement-Process Execution Result-free database connection

1. Get the database connection

1) Use DriverManager to fetch the database connection

Example:

String classname,url,uid,pwd;

ClassName = "Oracle.jdbc.driver.OracleDriver";

url = "JDBC:ORACLE:THIN:@127.0.0.1:1521:ORASVR;

UID = "System";

PWD = "Manager";

Class.forName (ClassName);

Connection cn = drivermanager.getconnection (URL,UID,PWD);

2) using Jndi (naming and directory services for Java)

Example

String Jndi = "jdbc/db";

Context CTX = (context) new InitialContext (). Lookup ("java:comp/env");

DataSource ds = (DataSource) ctx.lookup (JNDI);

Connection cn = Ds.getconnection ();

Used more in JSP

2. Execute SQL statements

1) Execute SQL statements with statement

String SQL;

Statement sm = cn.createstatement ();

Sm.executequery (SQL); Execute Data query statement (SELECT)

Sm.executeupdate (SQL); Execute Data UPDATE statement (delete, update, Inssert, DROP, etc.) Statement.close ();

2) Execute SQL statements with PreparedStatement

String SQL;

sql = "Inssert into user (Id,name) VALUES (?,?)";

PreparedStatement PS = cn.preparestatement (SQL);

Ps.setint (1,XXX);

Ps.setstring (2,XXX);

...

ResultSet rs = Ps.executequery (); Inquire

int c = Ps.executeupdate (); Update

3. Processing execution Results

A query statement that returns the recordset resultset.

UPDATE statement, which returns a number indicating the number of records affected by the update.

Methods of ResultSet:

1, Next (), moves the cursor back one line, or False if it returns true if it succeeds.

2, GETINT ("id") or getsting ("name"), returns the value of a field under the current cursor.

3, release the connection.

Cn.close ();

In general, close the resultset first, then close statement (or PreparedStatement), and finally close the connection

Scrollable, updated recordset

1. Create scrollable, updated statement

Statement sm = cn.createstatement (resultset.type_scroll_ensitive,resultset.concur_read_only);

The resultset obtained by the statement is a scrollable

2. Specify parameters when creating PreparedStatement

Preparedstatemet PS = cn.preparestatement (sql,resultset.type_scroll_insensitive,resultset.concur_read_only);

Resultset.absolute (9000);

Batch Update

1, Statement

Statement sm = cn.createstatement ();

Sm.addbatch (SQL1);

Sm.addbatch (SQL2);

...

Sm.executebatch ()

A statement object that can execute multiple SQL statements after a batch update. The multiple statements can be delete, update, Inssert, etc., or both

2, PreparedStatement

PreparedStatement PS = cn.preparedstatement (SQL);

{

Ps.setxxx (1,XXX);

...

Ps.addbatch ();

}

Ps.executebatch ();

A PreparedStatement, you can put an SQL statement, transform parameters to execute multiple times, one update.

Handling of transactions

1. Turn off automatic submission of connection

Cn.setautocommit (FALSE);

2. Execute a series of SQL statements

Important: Before executing each new SQL statement, the last statement (or Preparedstatemet) that executed the SQL statement must first close

Statement SM;

SM = cn.createstatement (Inssert into user ...);

Sm.executeupdate ();

Sm.close ();

SM = cn.createstatement ("Inssert into Corp ...);

Sm.executeupdate ();

Sm.close ();

3. Submit

Cn.commit ();

4, if an exception occurs, then rollback

Cn.rollback ();

Common techniques in Java database programming