Example usage for java.sql Connection setAutoCommit

Introduction

In this page you can find the example usage for java.sql Connection setAutoCommit.

Prototype

void setAutoCommit(boolean autoCommit) throws SQLException;

Source Link

Document

Sets this connection's auto-commit mode to the given state.

Usage

From source file:BatchUpdate.java

public static void main(String args[]) throws SQLException {

    ResultSet rs = null;/*ww  w. j  a v  a 2  s .  co  m*/
    PreparedStatement ps = null;

    String url = "jdbc:mySubprotocol:myDataSource";

    Connection con;
    Statement stmt;
    try {

        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");
        con.setAutoCommit(false);

        stmt = con.createStatement();

        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

        int[] updateCounts = stmt.executeBatch();
        con.commit();
        con.setAutoCommit(true);

        ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

        System.out.println("Table COFFEES after insertion:");
        while (uprs.next()) {
            String name = uprs.getString("COF_NAME");
            int id = uprs.getInt("SUP_ID");
            float price = uprs.getFloat("PRICE");
            int sales = uprs.getInt("SALES");
            int total = uprs.getInt("TOTAL");
            System.out.print(name + "   " + id + "   " + price);
            System.out.println("   " + sales + "   " + total);
        }

        uprs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
        System.err.println("");

    } catch (SQLException ex) {
        System.err.println("-----SQLException-----");
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Message:  " + ex.getMessage());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

From source file:UpdateLogic.java

public static void main(String args[]) {
    Connection con = null;

    if (args.length != 2) {
        System.out.println("Syntax: <java UpdateLogic [number] [string]>");
        return;//from  w w  w  .  j a va  2s.co  m
    }
    try {
        String driver = "com.imaginary.sql.msql.MsqlDriver";

        Class.forName(driver).newInstance();
        String url = "jdbc:msql://carthage.imaginary.com/ora";
        Statement s;

        con = DriverManager.getConnection(url, "borg", "");
        con.setAutoCommit(false); // make sure auto commit is off!
        s = con.createStatement();// create the first statement
        s.executeUpdate("INSERT INTO test (test_id, test_val) " + "VALUES(" + args[0] + ", '" + args[1] + "')");
        s.close(); // close the first statement
        s = con.createStatement(); // create the second statement
        s.executeUpdate("INSERT into test_desc (test_id, test_desc) " + "VALUES(" + args[0]
                + ", 'This describes the test.')");
        con.commit(); // commit the two statements
        System.out.println("Insert succeeded.");
        s.close(); // close the second statement
    } catch (Exception e) {
        if (con != null) {
            try {
                con.rollback();
            } // rollback on error
            catch (SQLException e2) {
            }
        }
        e.printStackTrace();
    } finally {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);/*  www.  j a  va2 s. c  om*/
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);

    PreparedStatement preparedStatementInsert = null;
    PreparedStatement preparedStatementUpdate = null;

    String insertTableSQL = "INSERT INTO Person" + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
            + "(?,?,?,?)";

    String updateTableSQL = "UPDATE Person SET USERNAME =? " + "WHERE USER_ID = ?";

    java.util.Date today = new java.util.Date();
    dbConnection.setAutoCommit(false);

    preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
    preparedStatementInsert.setInt(1, 9);
    preparedStatementInsert.setString(2, "101");
    preparedStatementInsert.setString(3, "system");
    preparedStatementInsert.setTimestamp(4, new java.sql.Timestamp(today.getTime()));
    preparedStatementInsert.executeUpdate();

    preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
    preparedStatementUpdate.setString(1, "new string");
    preparedStatementUpdate.setInt(2, 999);
    preparedStatementUpdate.executeUpdate();

    dbConnection.commit();
    dbConnection.close();
}

From source file:SimpleProgramToAccessOracleDatabase.java

public static void main(String[] args) throws SQLException {
    Connection conn = null; // connection object
    Statement stmt = null; // statement object
    ResultSet rs = null; // result set object
    try {/*  w  w w  . ja va 2s . c o m*/
        conn = getConnection(); // without Connection, can not do much
        // create a statement: This object will be used for executing
        // a static SQL statement and returning the results it produces.
        stmt = conn.createStatement();
        // start a transaction
        conn.setAutoCommit(false);

        // create a table called cats_tricks
        stmt.executeUpdate("CREATE TABLE cats_tricks " + "(name VARCHAR2(30), trick VARCHAR2(30))");
        // insert two new records to the cats_tricks table
        stmt.executeUpdate("INSERT INTO cats_tricks VALUES('mono', 'r')");
        stmt.executeUpdate("INSERT INTO cats_tricks VALUES('mono', 'j')");

        // commit the transaction
        conn.commit();

        // set auto commit to true (from now on every single
        // statement will be treated as a single transaction
        conn.setAutoCommit(true);

        // get all of the the records from the cats_tricks table
        rs = stmt.executeQuery("SELECT name, trick FROM cats_tricks");

        // iterate the result set and get one row at a time
        while (rs.next()) {
            String name = rs.getString(1); // 1st column in query
            String trick = rs.getString(2); // 2nd column in query
            System.out.println("name=" + name);
            System.out.println("trick=" + trick);
            System.out.println("==========");
        }
    } catch (ClassNotFoundException ce) {
        // if the driver class not found, then we will be here
        System.out.println(ce.getMessage());
    } catch (SQLException e) {
        // something went wrong, we are handling the exception here
        if (conn != null) {
            conn.rollback();
            conn.setAutoCommit(true);
        }

        System.out.println("--- SQLException caught ---");
        // iterate and get all of the errors as much as possible.
        while (e != null) {
            System.out.println("Message   : " + e.getMessage());
            System.out.println("SQLState  : " + e.getSQLState());
            System.out.println("ErrorCode : " + e.getErrorCode());
            System.out.println("---");
            e = e.getNextException();
        }
    } finally { // close db resources
        try {
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
        }

    }
}

From source file:BatchUpdate.java

public static void main(String args[]) {
    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;
    Statement stmt;//w w  w. jav  a2 s .  co  m

    try {
        Class.forName("myDriver.ClassName");
    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {
        con = DriverManager.getConnection(url, "myLogin", "myPassword");
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        con.setAutoCommit(false);
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
        int[] updateCounts = stmt.executeBatch();
        ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");
        System.out.println("Table COFFEES after insertion:");

        while (uprs.next()) {
            String name = uprs.getString("COF_NAME");
            int id = uprs.getInt("SUP_ID");
            float price = uprs.getFloat("PRICE");
            int sales = uprs.getInt("SALES");
            int total = uprs.getInt("TOTAL");
            System.out.print(name + " " + id + " " + price);
            System.out.println(" " + sales + " " + total);
        }
        uprs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("SQLException: " + b.getMessage());
        System.err.println("SQLState: " + b.getSQLState());
        System.err.println("Message: " + b.getMessage());
        System.err.println("Vendor: " + b.getErrorCode());
        System.err.print("Update counts: ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + " ");
        }

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState: " + ex.getSQLState());
        System.err.println("Message: " + ex.getMessage());
        System.err.println("Vendor: " + ex.getErrorCode());
    }
}

From source file:InsertStores.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;
    Statement stmt;/*from  www  . j  a  v a2s  .  c  o  m*/
    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        stmt = con.createStatement();
        con.setAutoCommit(false);

        String insertStore1 = "INSERT INTO STORES VALUES (" + "100001, "
                + "ADDRESS(888, 'Main_Street', 'Rancho_Alegre', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore1);

        String insertStore2 = "INSERT INTO STORES VALUES (" + "100002, "
                + "ADDRESS(1560, 'Alder', 'Ochos_Pinos', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore2);

        String insertStore3 = "INSERT INTO STORES VALUES (" + "100003, "
                + "ADDRESS(4344, 'First_Street', 'Verona', " + "'CA', '94545'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore3);

        String insertStore4 = "INSERT INTO STORES VALUES (" + "100004, "
                + "ADDRESS(321, 'Sandy_Way', 'La_Playa', " + "'CA', '94544'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore4);

        String insertStore5 = "INSERT INTO STORES VALUES (" + "100005, "
                + "ADDRESS(1000, 'Clover_Road', 'Happyville', " + "'CA', '90566'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000003))";

        stmt.addBatch(insertStore5);

        int[] updateCounts = stmt.executeBatch();

        ResultSet rs = stmt.executeQuery("SELECT * FROM STORES");

        System.out.println("Table STORES after insertion:");
        System.out.println("STORE_NO  LOCATION          COF_TYPE     MGR");
        while (rs.next()) {
            int storeNo = rs.getInt("STORE_NO");
            Struct location = (Struct) rs.getObject("LOCATION");
            Object[] locAttrs = location.getAttributes();
            Array coffeeTypes = rs.getArray("COF_TYPE");
            String[] cofTypes = (String[]) coffeeTypes.getArray();

            Ref managerRef = rs.getRef("MGR");
            PreparedStatement pstmt = con.prepareStatement("SELECT MANAGER FROM MANAGERS WHERE OID = ?");
            pstmt.setRef(1, managerRef);
            ResultSet rs2 = pstmt.executeQuery();
            rs2.next();
            Struct manager = (Struct) rs2.getObject("MANAGER");
            Object[] manAttrs = manager.getAttributes();

            System.out.print(storeNo + "   ");
            System.out.print(locAttrs[0] + " " + locAttrs[1] + " " + locAttrs[2] + ", " + locAttrs[3] + "  "
                    + locAttrs[4] + " ");
            for (int i = 0; i < cofTypes.length; i++)
                System.out.print(cofTypes[i] + " ");
            System.out.println(manAttrs[1] + ", " + manAttrs[2]);

            rs2.close();
            pstmt.close();
        }

        rs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
        System.err.println("");

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Message:  " + ex.getMessage());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

From source file:DemoPreparedStatementSetBinaryStream.java

public static void main(String[] args) throws Exception {
    String smallFileName = "smallFileName.dat";
    String largeFileName = "largeFileName.dat";
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {/*from www. ja  va2 s  . c o  m*/
        conn = getConnection();
        File smallFile = new File(smallFileName);
        int smallFileLength = (int) smallFile.length();
        InputStream smallStream = (InputStream) new FileInputStream(smallFile);
        File largeFile = new File(largeFileName);
        int largeFileLength = (int) largeFile.length();
        InputStream largeStream = (InputStream) new FileInputStream(largeFile);

        String query = "insert into binary_table(id, raw_column, long_raw_column) values(?, ?, ?)";

        conn.setAutoCommit(false);
        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, "0001");
        pstmt.setBinaryStream(2, smallStream, smallFileLength);
        pstmt.setBinaryStream(3, largeStream, largeFileLength);

        // execute query, and return number of rows created
        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
        conn.commit();
    } finally {
        pstmt.close();
        conn.close();
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Class.forName("org.sqlite.JDBC");

    Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");

    Statement stat = conn.createStatement();
    stat.executeUpdate("drop table if exists people;");
    stat.executeUpdate("create table people (name, occupation);");
    PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");

    prep.setString(1, "G");
    prep.setString(2, "politics");
    prep.addBatch();/*ww  w  .j  a v  a  2 s .  c  o  m*/
    prep.setString(1, "Turing");
    prep.setString(2, "computers");
    prep.addBatch();
    prep.setString(1, "W");
    prep.setString(2, "Tester");
    prep.addBatch();

    conn.setAutoCommit(false);
    prep.executeBatch();
    conn.setAutoCommit(true);

    ResultSet rs = stat.executeQuery("select * from people;");
    while (rs.next()) {
        System.out.println("name = " + rs.getString("name"));
        System.out.println("job = " + rs.getString("occupation"));
    }
    rs.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] args) {
    long begTime = System.currentTimeMillis();

    String driver = DEFAULT_DRIVER;
    String url = DEFAULT_URL;
    String username = DEFAULT_USERNAME;
    String password = DEFAULT_PASSWORD;

    Connection connection = null;

    try {//from  w ww .  j a  va  2  s  .  c om
        connection = createConnection(driver, url, username, password);
        DatabaseMetaData meta = connection.getMetaData();
        System.out.println(meta.getDatabaseProductName());
        System.out.println(meta.getDatabaseProductVersion());

        String sqlQuery = "SELECT PERSON_ID, FIRST_NAME, LAST_NAME FROM PERSON";
        System.out.println("before insert: " + query(connection, sqlQuery, Collections.EMPTY_LIST));

        connection.setAutoCommit(false);
        String sqlUpdate = "INSERT INTO PERSON(FIRST_NAME, LAST_NAME) VALUES(?,?)";
        List parameters = Arrays.asList("Foo", "Bar");
        int numRowsUpdated = update(connection, sqlUpdate, parameters);
        connection.commit();

        System.out.println("# rows inserted: " + numRowsUpdated);
        System.out.println("after insert: " + query(connection, sqlQuery, Collections.EMPTY_LIST));
    } catch (Exception e) {
        rollback(connection);
        e.printStackTrace();
    } finally {
        close(connection);
        long endTime = System.currentTimeMillis();
        System.out.println("wall time: " + (endTime - begTime) + " ms");
    }
}

From source file:DemoPreparedStatementSetBlob.java

public static void main(String[] args) throws Exception {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;//  w  w w . j  a v a  2 s .  c  om
    java.sql.Blob blob = null;
    try {
        conn = getConnection();
        // prepare blob object from an existing binary column
        pstmt = conn.prepareStatement("select photo from my_pictures where id = ?");
        pstmt.setString(1, "0001");
        rs = pstmt.executeQuery();
        rs.next();
        blob = rs.getBlob(1);

        // prepare SQL query for inserting a new row using setBlob()
        String query = "insert into blob_table(id, blob_column) values(?, ?)";
        // begin transaction
        conn.setAutoCommit(false);

        pstmt = conn.prepareStatement(query);
        pstmt.setString(1, "0002");
        pstmt.setBlob(2, blob);

        int rowCount = pstmt.executeUpdate();
        System.out.println("rowCount=" + rowCount);
        // end transaction
        conn.commit();
    } finally {
        rs.close();
        pstmt.close();
        conn.close();
    }
}