This website requires JavaScript.

Java: 使用jdbc 连接MySQL数据库(四) 事务处理及元数据

默认情况下数据连接,其事务是默认提交的.可以关掉

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

image

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条评论
avatar