Some common techniques in Java database programming

Programming | skills | data | Database 1, JAVA database Operation basic process

2, a few commonly used important skills:

Scrollable, updated recordsets

Batch Update

Transaction processing

Java database Operation basic process: Get database connection-Execute SQL statement-Process Execution result-release database connection

1. Get the database connection

1) using DriverManager to Access 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 (Java naming and directory services)

Example

String Jndi = "jdbc/db";
Context CTX = (context) new InitialContext (). Lookup ("java:comp/env");
DataSource ds = (DataSource) ctx.lookup (JNDI);
Connection cn = Ds.getconnection ();

More used in JSP

2. Execute SQL statement

1 using statement to execute SQL statements

String SQL;
Statement sm = cn.createstatement ();
Sm.executequery (SQL); Execute a Data query statement (SELECT)
Sm.executeupdate (SQL); Execute Data UPDATE statements (delete, update, insert, DROP, etc.) Statement.close ();

2 using PreparedStatement to execute SQL statements

String SQL;
sql = "INSERT 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 the results of implementation

Query statement that returns a recordset resultset.

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

The ResultSet method:

1, Next (), move the cursor back one row, or False if it returns true successfully.

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

3, release the connection.

Cn.close ();

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

Scrollable, updated recordsets

1, to create a scrollable, updated statement

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

The resultset obtained by the statement is 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, insert, 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 the parameters of multiple executions, one update.

Handling of transactions

1, turn off the 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 (insert into user ...);
Sm.executeupdate ();
Sm.close ();

SM = cn.createstatement ("INSERT INTO corp ...");
Sm.executeupdate ();
Sm.close ();

3. Submit

Cn.commit ();

4, if an exception occurs, then rollback

Cn.rollback ();