Java: 使用jdbc 连接MySQL数据库(四) 事务处理及元数据
默认分类
2020-01-15
303
0
默认情况下数据连接,其事务是默认提交的.可以关掉
myConn.setAutoCommit(false);控制commit或者rollback语句如下:
myConn.commit(); myConn.rollback();
Defining JDBC Transactions
import java.sql.*; import java.util.Scanner; public class TransactionDemo { public static void main(String[] args) throws SQLException { Connection myConn = null; Statement myStmt = null; try { // 1\. Get a connection to database myConn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/demo", "student", "student"); // Turn off auto commit myConn.setAutoCommit(false); // Show salaries BEFORE System.out.println("Salaries BEFORE\n"); showSalaries(myConn, "HR"); showSalaries(myConn, "Engineering"); // Transaction Step 1: Delete all HR employees myStmt = myConn.createStatement(); myStmt.executeUpdate("delete from employees where department='HR'"); // Transaction Step 2: Set salaries to 300000 for all Engineering // employees myStmt.executeUpdate("update employees set salary=300000 where department='Engineering'"); System.out.println("\n>> Transaction steps are ready.\n"); // Ask user if it is okay to save boolean ok = askUserIfOkToSave(); if (ok) { // store in database myConn.commit(); System.out.println("\n>> Transaction COMMITTED.\n"); } else { // discard myConn.rollback(); System.out.println("\n>> Transaction ROLLED BACK.\n"); } // Show salaries AFTER System.out.println("Salaries AFTER\n"); showSalaries(myConn, "HR"); showSalaries(myConn, "Engineering"); } catch (Exception exc) { exc.printStackTrace(); } finally { close(myConn, myStmt, null); } } /** * Prompts the user. Return true if they enter "yes", false otherwise * * @return */ private static boolean askUserIfOkToSave() { Scanner scanner = new Scanner(System.in); System.out.println("Is it okay to save? yes/no: "); String input = scanner.nextLine(); scanner.close(); return input.equalsIgnoreCase("yes"); } private static void showSalaries(Connection myConn, String theDepartment) throws SQLException { PreparedStatement myStmt = null; ResultSet myRs = null; System.out.println("Show Salaries for Department: " + theDepartment); try { // Prepare statement myStmt = myConn .prepareStatement("select * from employees where department=?"); myStmt.setString(1, theDepartment); // Execute SQL query myRs = myStmt.executeQuery(); // Process result set while (myRs.next()) { String lastName = myRs.getString("last_name"); String firstName = myRs.getString("first_name"); double salary = myRs.getDouble("salary"); String department = myRs.getString("department"); System.out.printf("%s, %s, %s, %.2f\n", lastName, firstName, department, salary); } System.out.println(); } catch (Exception exc) { exc.printStackTrace(); } finally { close(myStmt, myRs); } } private static void close(Connection myConn, Statement myStmt, ResultSet myRs) throws SQLException { if (myRs != null) { myRs.close(); } if (myStmt != null) { myStmt.close(); } if (myConn != null) { myConn.close(); } } private static void close(Statement myStmt, ResultSet myRs) throws SQLException { close(null, myStmt, myRs); } }
Accessing Database MetaData
import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.SQLException;public class MetaDataBasicInfo {
public static void main(String[] args) throws SQLException { Connection myConn = null; try { // 1\. Get a connection to database myConn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/demo", "student", "student"); // 2\. Get metadata DatabaseMetaData databaseMetaData = myConn.getMetaData(); // 3\. Display info about database System.out.println("Product name: " + databaseMetaData.getDatabaseProductName()); System.out.println("Product version: " + databaseMetaData.getDatabaseProductVersion()); System.out.println(); // 4\. Display info about JDBC Driver System.out.println("JDBC Driver name: " + databaseMetaData.getDriverName()); System.out.println("JDBC Driver version: " + databaseMetaData.getDriverVersion()); } catch (Exception exc) { exc.printStackTrace(); } finally { close(myConn); } } private static void close(Connection myConn) throws SQLException { if (myConn != null) { myConn.close(); } }
}
import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; public class SchemaInfo { public static void main(String[] args) throws SQLException { String catalog = null; String schemaPattern = null; String tableNamePattern = null; String columnNamePattern = null; String[] types = null; Connection myConn = null; ResultSet myRs = null; try { // 1\. Get a connection to database myConn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/demo", "student", "student"); // 2\. Get metadata DatabaseMetaData databaseMetaData = myConn.getMetaData(); // 3\. Get list of tables System.out.println("List of Tables"); System.out.println("--------------"); myRs = databaseMetaData.getTables(catalog, schemaPattern, tableNamePattern, types); while (myRs.next()) { System.out.println(myRs.getString("TABLE_NAME")); } // 4\. Get list of columns System.out.println("\n\nList of Columns"); System.out.println("--------------"); myRs = databaseMetaData.getColumns(catalog, schemaPattern, "employees", columnNamePattern); while (myRs.next()) { System.out.println(myRs.getString("COLUMN_NAME")); } } catch (Exception exc) { exc.printStackTrace(); } finally { close(myConn, myRs); } } private static void close(Connection myConn, ResultSet myRs) throws SQLException { if (myRs != null) { myRs.close(); } if (myConn != null) { myConn.close(); } } }
Reading ResultSet MetaData
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; public class ResultSetDemo { public static void main(String[] args) throws SQLException { Connection myConn = null; Statement myStmt = null; ResultSet myRs = null; try { // 1\. Get a connection to database myConn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/demo", "student", "student"); // 2\. Run query myStmt = myConn.createStatement(); myRs = myStmt.executeQuery("select id, last_name, first_name, salary from employees"); // 3\. Get result set metadata ResultSetMetaData rsMetaData = myRs.getMetaData(); // 4\. Display info int columnCount = rsMetaData.getColumnCount(); System.out.println("Column count: " + columnCount + "\n"); for (int column=1; column <= columnCount; column++) { System.out.println("Column name: " + rsMetaData.getColumnName(column)); System.out.println("Column type name: " + rsMetaData.getColumnTypeName(column)); System.out.println("Is Nullable: " + rsMetaData.isNullable(column)); System.out.println("Is Auto Increment: " + rsMetaData.isAutoIncrement(column) + "\n"); } } catch (Exception exc) { exc.printStackTrace(); } finally { close(myConn, myStmt, myRs); } } private static void close(Connection myConn, Statement myStmt, ResultSet myRs) throws SQLException { if (myRs != null) { myRs.close(); } if (myStmt != null) { myStmt.close(); } if (myConn != null) { myConn.close(); } } }
0条评论