Skills in Java database programming

Time:2021-11-27

1. Basic process of Java database operation

2. Several common important skills:

Scrollable and updatable Recordset

Batch update

Transaction processing

Basic process of Java database operation: obtain database connection – execute SQL statement – process execution result – release database connection

1. Get database connection

1) use drivermanager to get 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) use JNDI (Java Naming and directory service) mode

Examples

  String jndi = “jdbc/db”;
  Context ctx = (Context) new InitialContext().lookup(“java:comp/env”);
  DataSource ds = (DataSource) ctx.lookup(jndi);
  Connection cn = ds.getConnection();

Mostly used in JSP

2. Execute SQL statement

1) execute SQL statement with statement

  String sql;
  Statement sm = cn.createStatement();
  sm.executeQuery(sql); // Execute data query statement (select)
  sm.executeUpdate(sql); // Execute data update statements (delete, update, insert, drop, etc.) statement. Close();

2) execute SQL statements with Preparedstatement

  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(); // query
  int c = ps.executeUpdate(); // to update

3. Processing execution results

Query statement that returns the recordset resultset.

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

Method of resultset:

1. Next(), move the cursor back one line, and return true if successful; Otherwise, false is returned.

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, and then the statement (or Preparedstatement); Finally, close the connection

Scrollable and updatable Recordset

1. Create a scrollable and 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 can be updated in batch after multiple SQL statements are executed. These statements can be delete, update, insert, etc., or both

  2、PreparedStatement

  PreparedStatement ps = cn.preparedStatement(sql);
  {
  ps.setXXX(1,xxx);
  …
  ps.addBatch();
  }
  ps.executeBatch();

A Preparedstatement can execute an SQL statement and transform parameters multiple times and update them once.

Transaction processing

1. Turn off automatic submission of connection

  cn.setAutoCommit(false);

2. Execute a series of SQL statements

Important: before each new SQL statement is executed, the statement (or preparedstate) of the last executed SQL statement must be closed first

  Statement sm ;
  sm = cn.createStatement(insert into user…);
  sm.executeUpdate();
  sm.close();

  sm = cn.createStatement(“insert into corp…);
  sm.executeUpdate();
  sm.close();

3. Submission

  cn.commit();

4. If an exception occurs, roll back

  cn.rollback();

Recommended Today

Android master notes – start optimization

The only way to start and open the app, the first experience, is related to core data such as user retention and conversion rate; Start analysis startup type Android vitals can monitor the application cold, hot and warm startup time. Via ADB shell am start – w Execute the command to start and print the […]