Java/Database SQL JDBC/ResultSet Updatable
Содержание
- 1 Cancelling Updates to an Updatable Result Set
- 2 Creating an Updatable Result Set
- 3 Delete Row from Updatable ResultSet for MySQL
- 4 Deleting a Row from a Database Table Using an Updatable Result Set
- 5 Demo Updatable ResultSet
- 6 Determining If a Database Supports Updatable Result Sets: An updatable result set allows modification to data in a table through the result set.
- 7 Determining If a Result Set Is Updatable
- 8 If database support updatable result sets
- 9 Inserting a Row into a Database Table Using an Updatable Result Set
- 10 Insert Row to Updatable ResultSet from MySQL
- 11 Make updates in Updatable ResultSet
- 12 Refreshing a Row in an Updatable Result Set
- 13 Updatable resultset with Oracle Driver
- 14 Updating a Row in a Database Table Using an Updatable Result Set
- 15 Using UpdatableResultSet to insert a new row
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>
Creating 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);
// Create a statement that will return updatable result sets
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
// Primary key must be specified so that the result set is updatable
ResultSet resultSet = stmt.executeQuery("SELECT col_string FROM my_table");
}
}
</source>
Delete Row from Updatable ResultSet for MySQL
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DeleteRowUpdatableResultSet_MySQL {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/octopus";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
String query = "select id, name from employees";
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(query);
while (rs.next()) {
String id = rs.getString(1);
String name = rs.getString(2);
System.out.println("id=" + id + " name=" + name);
}
rs.first();
rs.deleteRow();
rs.beforeFirst();
while (rs.next()) {
String id = rs.getString(1);
String name = rs.getString(2);
System.out.println("id=" + id + " name=" + name);
}
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
} finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</source>
Deleting a Row from a Database Table Using 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);
// Create an updatable result set
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
// Delete the first row
resultSet.first();
resultSet.deleteRow();
}
}
</source>
Demo Updatable ResultSet
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DemoUpdatableResultSet {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "name";
String password = "password";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) {
ResultSet rs = null;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String query = "select id, name, age from employees where age > ?";
pstmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt.setInt(1, 20); // set input values
rs = pstmt.executeQuery(); // create an updatable ResultSet
// update a column value in the current row.
rs.absolute(2); // moves the cursor to the 2nd row of rs
rs.updateString("name", "newName"); // updates the "name" column of row 2 to newName
rs.updateRow(); // updates the row in the data source
// insert column values into the insert row.
rs.moveToInsertRow(); // moves cursor to the insert row
rs.updateInt(1, 1234); // 1st column id=1234
rs.updateString(2, "newName"); // updates the 2nd column
rs.updateInt(3, 99); // updates the 3rd column to 99
rs.insertRow();
rs.moveToCurrentRow();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</source>
Determining If a Database Supports Updatable Result Sets: An updatable result set allows modification to data in a table through the result set.
<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[] 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);
DatabaseMetaData dmd = connection.getMetaData();
if (dmd.supportsResultSetConcurrency(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)) {
System.out.println("Updatable result sets are supported");
} else {
System.out.println("Updatable result sets are not supported");
}
}
}
</source>
Determining If a Result Set 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[] 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);
// Create a statement that will return updatable result sets
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
// Primary key must be specified so that the result set is updatable
ResultSet resultSet = stmt.executeQuery("SELECT col_string FROM my_table");
int concurrency = resultSet.getConcurrency();
if (concurrency == ResultSet.CONCUR_UPDATABLE) {
System.out.println("Result set is updatable");
} else {
System.out.println("Result set is not updatable");
}
}
}
</source>
If database support updatable result sets
<source lang="java">
import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; public class Main {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/yourDatabase";
private static final String USERNAME = "root";
private static final String PASSWORD = "";
public static void main(String[] args) throws Exception {
Class.forName(DRIVER);
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();
boolean updatable = metadata.supportsResultSetConcurrency(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
System.out.println("Updatable ResultSet supported = " + updatable);
connection.close();
}
}
</source>
Inserting a Row into a Database Table Using 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 "insert row"
resultSet.moveToInsertRow();
// Set values for the new row.
resultSet.updateString("col_string", "new data");
// Insert the row
resultSet.insertRow();
}
}
</source>
Insert Row to Updatable ResultSet from MySQL
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class InsertRowUpdatableResultSet_MySQL {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
return DriverManager.getConnection(url, username, password);
}
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
String query = "select id, name from employees";
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(query);
while (rs.next()) {
String id = rs.getString(1);
String name = rs.getString(2);
System.out.println("id=" + id + " name=" + name);
}
// Move cursor to the "insert row"
rs.moveToInsertRow();
// Set values for the new row.
rs.updateString("id", "001");
rs.updateString("name", "newName");
// Insert the new row
rs.insertRow();
// scroll from the top again
rs.beforeFirst();
while (rs.next()) {
String id = rs.getString(1);
String name = rs.getString(2);
System.out.println("id=" + id + " name=" + name);
}
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
} finally {
// release database resources
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</source>
Make updates in 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 {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost/testdb", "root", "");
Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String query = "SELECT id, code, name, quantity, price FROM products";
ResultSet uprs = statement.executeQuery(query);
while (uprs.next()) {
System.out.println(uprs.getString("id") + ":" + uprs.getString("code") + ":"
+ uprs.getString("name") + ":" + uprs.getInt("quantity") + ":" + uprs.getDouble("price"));
}
uprs.first();
uprs.updateString("name", "Java");
uprs.updateRow();
uprs.next();
uprs.deleteRow();
uprs.moveToInsertRow();
uprs.updateString("code", "1");
uprs.updateString("name", "Data Structures");
uprs.updateInt("quantity", 1);
uprs.updateDouble("price", 5.99);
uprs.insertRow();
uprs.beforeFirst();
while (uprs.next()) {
System.out.println(uprs.getString("id") + "\t" + uprs.getString("code") + "\t"
+ uprs.getString("name") + "\t" + uprs.getInt("quantity") + "\t"
+ uprs.getDouble("price"));
}
connection.close();
}
}
</source>
Refreshing a Row in 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");
// Retrieve the current values of the row from the database
resultSet.refreshRow();
}
}
</source>
Updatable resultset with Oracle Driver
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class UpdateableRs {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd");
Statement stmt = conn.createStatement();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT ssn, name, salary FROM EMPLOYEES");
printRs(rs);
rs.beforeFirst();
while (rs.next()) {
double newSalary = rs.getDouble("salary") * 1.053;
rs.updateDouble("salary", newSalary);
rs.updateRow();
}
printRs(rs);
conn.close();
}
public static void printRs(ResultSet rs) throws SQLException {
rs.beforeFirst();
while (rs.next()) {
int ssn = rs.getInt("ssn");
String name = rs.getString("name");
double salary = rs.getDouble("salary");
System.out.print("Row Number=" + rs.getRow());
System.out.print(", SSN: " + ssn);
System.out.print(", Name: " + name);
System.out.println(", Salary: $" + salary);
}
System.out.println();
}
}
</source>
Updating a Row in a Database Table Using 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");
resultSet.first();
// Update the value of column col_string on that row
resultSet.updateString("col_string", "new data");
// Update the row; if auto-commit is enabled, update is committed
resultSet.updateRow();
}
}
</source>
Using UpdatableResultSet to insert a new row
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class PrintResultSet {
public static void main(String args[]) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:Contacts");
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select * from employee");
rs.moveToInsertRow();
rs.updateInt("Contact_ID", 150);
rs.updateString("First_Name", "Nigel");
rs.updateString("Last_Name", "Thornebury");
rs.insertRow();
}
}
</source>
