Java Tutorial/Database/ResultSet Updatable
Содержание
- 1 Cancelling Updates to an Updatable Result Set
- 2 Cancel Updates to an Updatable ResultSet
- 3 Create an Updatable ResultSet
- 4 Delete a Row from a Database Table Using an Updatable ResultSet
- 5 Determine if a Database Supports Updatable ResultSets
- 6 Determine if a ResultSet is Updatable
- 7 Insert a Row into a Database Table Using an Updatable ResultSet
- 8 Refresh a Row in an Updatable ResultSet
- 9 Update a Row in a Database Table Using an Updatable Result Set
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>