Java Tutorial/Database/ResultSet Updatable

Материал из Java эксперт
Перейти к: навигация, поиск

Cancelling Updates to an Updatable Result Set

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
       ResultSet.CONCUR_UPDATABLE);
   ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
   // Move cursor to the row to update
   resultSet.first();
   // Update the value of column col_string on that row
   resultSet.updateString("col_string", "new data");
   // Discard the update to the row
   resultSet.cancelRowUpdates();
 }

}</source>





Cancel Updates to an Updatable ResultSet

You cannot cancel updates after you have called updateRow().



   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getConnection();
   Statement st = conn
       .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
   st.executeUpdate("create table survey (id int,name varchar(30));");
   st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
   st.executeUpdate("insert into survey (id,name ) values (2,null)");
   st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   // Move cursor to the row to update
   rs.first();
   // Update the value of column column_1 on that row
   rs.updateString("name", "new data");
   // Discard the update to the row
   rs.cancelRowUpdates();
   rs.close();
   st.close();
   conn.close();
 }
 private static Connection getConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   String url = "jdbc:hsqldb:mem:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }

}</source>





Create an Updatable ResultSet

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getConnection();
   Statement st = conn
       .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
   st.executeUpdate("create table survey (pk_column int,name varchar(30));");
   st.executeUpdate("insert into survey (pk_column,name ) values (1,"nameValue")");
   st.executeUpdate("insert into survey (pk_column,name ) values (2,null)");
   st.executeUpdate("insert into survey (pk_column,name ) values (3,"Tom")");
   // Primary key pk_column must be specified
   // so that the result set is updatable
   ResultSet rs = st.executeQuery("SELECT pk_column FROM survey");
   rs.close();
   st.close();
   conn.close();
 }
 private static Connection getConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   String url = "jdbc:hsqldb:mem:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }

}</source>





Delete a Row from a Database Table Using an Updatable ResultSet

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getConnection();
   Statement st = conn
       .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
   st.executeUpdate("create table survey (id int,name varchar(30));");
   st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
   st.executeUpdate("insert into survey (id,name ) values (2,null)");
   st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
   ResultSet rs = st.executeQuery("SELECT id,name FROM survey");
   // Delete the first row
   rs.first();
   rs.deleteRow();
   rs.close();
   st.close();
   conn.close();
 }
 private static Connection getConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   String url = "jdbc:hsqldb:mem:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }

}</source>





Determine if a Database Supports Updatable ResultSets

   <source lang="java">

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getConnection();
   DatabaseMetaData dbmd = conn.getMetaData();
   if (dbmd.supportsResultSetConcurrency(ResultSet.TYPE_FORWARD_ONLY,
                                         ResultSet.CONCUR_UPDATABLE)) {
       System.out.println("Updatable ResultSets are supported");
   }
   else {
       System.out.println("Updatable ResultSets are not supported");
   }


   conn.close();
 }
 private static Connection getConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   String url = "jdbc:hsqldb:mem:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }

}</source>





Determine if a ResultSet is Updatable

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getConnection();
   Statement st = conn
       .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
   st.executeUpdate("create table survey (id int,name varchar(30));");
   st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
   st.executeUpdate("insert into survey (id,name ) values (2,null)");
   st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
   ResultSet resultSet = st.executeQuery("SELECT * FROM survey");
   // get concurrency of the ResultSet object
   int concurrency = resultSet.getConcurrency();
   if (concurrency == ResultSet.CONCUR_UPDATABLE) {
       System.out.println("ResultSet is updatable");
   }
   else {
       System.out.println("ResultSet is not updatable");
   }
   resultSet.close();
   st.close();
   conn.close();
 }
 private static Connection getConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   String url = "jdbc:hsqldb:mem:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }

}</source>





Insert a Row into a Database Table Using an Updatable ResultSet

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getConnection();
   Statement st = conn
       .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
   st.executeUpdate("create table survey (id int,name varchar(30));");
   st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
   st.executeUpdate("insert into survey (id,name ) values (2,null)");
   st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
   ResultSet rs = st.executeQuery("SELECT id,name FROM survey");
   rs.moveToInsertRow();
   rs.updateString("id", "66");
   rs.updateString("name", "H F");
   // Insert the row
   rs.insertRow();
   rs.close();
   st.close();
   conn.close();
 }
 private static Connection getConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   String url = "jdbc:hsqldb:mem:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }

}</source>





Refresh a Row in an Updatable ResultSet

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getConnection();
   Statement st = conn
       .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
   st.executeUpdate("create table survey (id int,name varchar(30));");
   st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
   st.executeUpdate("insert into survey (id,name ) values (2,null)");
   st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
   ResultSet rs = st.executeQuery("SELECT id,name FROM survey");
   // Use the result set
   // Retrieve the current values of the row from the database
   rs.refreshRow();
   rs.close();
   st.close();
   conn.close();
 }
 private static Connection getConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   String url = "jdbc:hsqldb:mem:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }

}</source>





Update a Row in a Database Table Using an Updatable Result Set

Updating the current row of an updatable result set involves calling the ResultSet.updateXXX() methods followed by a call to updateRow():



   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getConnection();
   Statement st = conn
       .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
   st.executeUpdate("create table survey (id int,name varchar(30));");
   st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
   st.executeUpdate("insert into survey (id,name ) values (2,null)");
   st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
   ResultSet rs = st.executeQuery("SELECT * FROM survey");
   // Move cursor to the row to update
   rs.first();
   // Update the value of column column_1 on that row
   rs.updateString("name", "new data");
   // Update the row; if autocommit is enabled,
   // update is committed
   rs.updateRow();
   rs.close();
   st.close();
   conn.close();
 }
 private static Connection getConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   String url = "jdbc:hsqldb:mem:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }

}</source>