Java by API/java.sql/Statement

Statement: addBatch(String sql)

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    Connection con;
    Statement stmt;
    ResultSet rs;
    try {
      Class.forName(driver);
      con = DriverManager.getConnection("jdbc:odbc:databaseName", "student", "student");
      // Start a transaction
      con.setAutoCommit(false);
      stmt = con.createStatement();
      stmt.addBatch("UPDATE EMP SET JOB = 1");
      // Submit the batch of commands for this statement to the database
      stmt.executeBatch();
      // Commit the transaction
      con.rumit();
      // Close the existing to be safe before opening a new one
      stmt.close();
      // Print out the Employees
      stmt = con.createStatement();
      rs = stmt.executeQuery("SELECT * FROM EMP");
      // Loop through and print the employee number, job, and hiredate
      while (rs.next()) {
        int id = rs.getInt("EMPNO");
        int job = rs.getInt("JOB");
        String hireDate = rs.getString("HIREDATE");
        System.out.println(id + ":" + job + ":" + hireDate);
      }
      con.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }
}

Statement: executeBatch()

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    Connection con;
    Statement stmt;
    ResultSet rs;
    try {
      Class.forName(driver);
      con = DriverManager.getConnection("jdbc:odbc:databaseName", "student", "student");
      // Start a transaction
      con.setAutoCommit(false);
      stmt = con.createStatement();
      stmt.addBatch("UPDATE EMP SET JOB = 1");
      // Submit the batch of commands for this statement to the database
      stmt.executeBatch();
      // Commit the transaction
      con.rumit();
      // Close the existing to be safe before opening a new one
      stmt.close();
      // Print out the Employees
      stmt = con.createStatement();
      rs = stmt.executeQuery("SELECT * FROM EMP");
      // Loop through and print the employee number, job, and hiredate
      while (rs.next()) {
        int id = rs.getInt("EMPNO");
        int job = rs.getInt("JOB");
        String hireDate = rs.getString("HIREDATE");
        System.out.println(id + ":" + job + ":" + hireDate);
      }
      con.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }
}

Statement.EXECUTE_FAILED

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);
    Statement st = conn .createStatement();
    st.executeUpdate("create table survey (id int, name VARCHAR(30) );");
    String INSERT_RECORD = "insert into survey(id, name) values(?,?)";
    
    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    pstmt.setString(2, "name1");
    pstmt.addBatch();
    
    pstmt.setString(1, "2");
    pstmt.setString(2, "name2");
    pstmt.addBatch();
    int[] updateCounts = pstmt.executeBatch();
    checkUpdateCounts(updateCounts);
    conn.rumit();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);
    rs.close();
    st.close();
    conn.close();
  }
  public static void checkUpdateCounts(int[] updateCounts) {
    for (int i=0; i<updateCounts.length; i++) {
        if (updateCounts[i] >= 0) {
            System.out.println("OK; updateCount="+updateCounts[i]);
        }
        else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
            System.out.println("OK; updateCount=Statement.SUCCESS_NO_INFO");
        }
        else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
            System.out.println("Failure; updateCount=Statement.EXECUTE_FAILED");
        }
    }
}  
  
  private static void outputResultSet(ResultSet rs) throws Exception {
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    for (int i = 1; i < numberOfColumns + 1; i++) {
      String columnName = rsMetaData.getColumnName(i);
      System.out.print(columnName + "   ");
    }
    while (rs.next()) {
      for (int i = 1; i < numberOfColumns + 1; i++) {
        System.out.print(rs.getString(i) + "   ");
      }
      System.out.println();
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Statement: executeQuery(String sql)

 
/* 
 * */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
public class Main {
  public static void main(String[] args) throws Exception {
    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    String sqlQuery = "SELECT uid, name, duration from EVENTS";
    ResultSet rs = stmt.executeQuery(sqlQuery);
    while (rs.next()) {
      rs.updateString("Name", "new Name");
      rs.updateRow();
    }
    rs.first();
    while (rs.next()) {
      String name = rs.getString(2);
      Timestamp hireDate = rs.getTimestamp(5);
      System.out.println("Name: " + name + " Hire Date: " + hireDate);
    }
    rs.close();
  }
  static Connection conn;
  static Statement st;
  static {
    try {
      // Step 1: Load the JDBC driver.
      Class.forName("org.hsqldb.jdbcDriver");
      System.out.println("Driver Loaded.");
      // Step 2: Establish the connection to the database.
      String url = "jdbc:hsqldb:data/tutorial";
      conn = DriverManager.getConnection(url, "sa", "");
      System.out.println("Got Connection.");
      st = conn.createStatement();
    } catch (Exception e) {
      System.err.println("Got an exception! ");
      e.printStackTrace();
      System.exit(0);
    }
  }
}

Statement: getFetchSize()

 

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);
    // Get the fetch size of a statement
    Statement stmt = connection.createStatement ();
    int fetchSize = stmt.getFetchSize();
    // Set the fetch size on the statement
    stmt.setFetchSize(100);
    // Create a result set
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Change the fetch size on the result set
    resultSet.setFetchSize(100);
  }
}

Statement: getGeneratedKeys()

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
  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);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    Statement stmt = null;
    ResultSet rs = null;
    conn = getConnection();
    stmt = conn.createStatement();
    stmt.executeUpdate("insert into animals_table (name) values("newName")");
    rs = stmt.getGeneratedKeys();
    while (rs.next()) {
      ResultSetMetaData rsMetaData = rs.getMetaData();
      int columnCount = rsMetaData.getColumnCount();
      for (int i = 1; i <= columnCount; i++) {
        String key = rs.getString(i);
        System.out.println("key " + i + " is " + key);
      }
    }
    rs.close();
    stmt.close();
    conn.close();
  }
}

Statement: getMaxFieldSize()

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Statement statement = null;
    String url = "jdbc:odbc:databaseName";
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String username = "guest";
    String password = "guest";
    Class.forName(driver);
    Connection connection = DriverManager.getConnection(url, username, password);
    statement = connection.createStatement();
    System.out.println("Driver          :  " + driver);
    // Put each method call in a separate try block to execute them all
    System.out.print("\nMaximum rows    :");
    int maxRows = statement.getMaxRows();
    System.out.print(maxRows == 0 ? " No limit" : " " + maxRows);
    System.out.print("\nMax field size  :");
    int maxFieldSize = statement.getMaxFieldSize();
    System.out.print(maxFieldSize == 0 ? " No limit" : " " + maxFieldSize);
    System.out.print("\nTimeout          :");
    int queryTimeout = statement.getQueryTimeout();
    System.out.print(queryTimeout == 0 ? " No limit" : " " + queryTimeout);
  }
}

Statement: getMaxRows()

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Statement statement = null;
    String url = "jdbc:odbc:databaseName";
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String username = "guest";
    String password = "guest";
    Class.forName(driver);
    Connection connection = DriverManager.getConnection(url, username, password);
    statement = connection.createStatement();
    System.out.println("Driver          :  " + driver);
    // Put each method call in a separate try block to execute them all
    System.out.print("\nMaximum rows    :");
    int maxRows = statement.getMaxRows();
    System.out.print(maxRows == 0 ? " No limit" : " " + maxRows);
    System.out.print("\nMax field size  :");
    int maxFieldSize = statement.getMaxFieldSize();
    System.out.print(maxFieldSize == 0 ? " No limit" : " " + maxFieldSize);
    System.out.print("\nTimeout          :");
    int queryTimeout = statement.getQueryTimeout();
    System.out.print(queryTimeout == 0 ? " No limit" : " " + queryTimeout);
  }
}

Statement: getQueryTimeout()

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {

  public static void main(String[] args) throws Exception {
    Statement statement = null;
    String url = "jdbc:odbc:databaseName";
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String username = "guest";
    String password = "guest";
    Class.forName(driver);
    Connection connection = DriverManager.getConnection(url, username, password);
    statement = connection.createStatement();
    System.out.println("Driver          :  " + driver);
    // Put each method call in a separate try block to execute them all
    System.out.print("\nMaximum rows    :");
    int maxRows = statement.getMaxRows();
    System.out.print(maxRows == 0 ? " No limit" : " " + maxRows);
    System.out.print("\nMax field size  :");
    int maxFieldSize = statement.getMaxFieldSize();
    System.out.print(maxFieldSize == 0 ? " No limit" : " " + maxFieldSize);
    System.out.print("\nTimeout          :");
    int queryTimeout = statement.getQueryTimeout();
    System.out.print(queryTimeout == 0 ? " No limit" : " " + queryTimeout);
  }
}

Statement.RETURN_GENERATED_KEYS

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  private static final String URL = "jdbc:mysql://localhost/testdb";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    String insert = "INSERT INTO orders (username, order_date) VALUES ("foobar", "2007-12-13")";
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(insert, Statement.RETURN_GENERATED_KEYS);
    ResultSet keys = stmt.getGeneratedKeys();
    int lastKey = 1;
    while (keys.next()) {
      lastKey = keys.getInt(1);
    }
    System.out.println("Last Key: " + lastKey);
    conn.close();
  }
}

Statement: setFetchSize(int rows)

 

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);
    // Get the fetch size of a statement
    Statement stmt = connection.createStatement ();
    int fetchSize = stmt.getFetchSize();
    // Set the fetch size on the statement
    stmt.setFetchSize(100);
    // Create a result set
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Change the fetch size on the result set
    resultSet.setFetchSize(100);
  }
}

Statement.SUCCESS_NO_INFO

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);
    Statement st = conn .createStatement();
    st.executeUpdate("create table survey (id int, name VARCHAR(30) );");
    String INSERT_RECORD = "insert into survey(id, name) values(?,?)";
    
    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    pstmt.setString(2, "name1");
    pstmt.addBatch();
    
    pstmt.setString(1, "2");
    pstmt.setString(2, "name2");
    pstmt.addBatch();
    int[] updateCounts = pstmt.executeBatch();
    checkUpdateCounts(updateCounts);
    conn.rumit();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);
    rs.close();
    st.close();
    conn.close();
  }
  public static void checkUpdateCounts(int[] updateCounts) {
    for (int i=0; i<updateCounts.length; i++) {
        if (updateCounts[i] >= 0) {
            System.out.println("OK; updateCount="+updateCounts[i]);
        }
        else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
            System.out.println("OK; updateCount=Statement.SUCCESS_NO_INFO");
        }
        else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
            System.out.println("Failure; updateCount=Statement.EXECUTE_FAILED");
        }
    }
}  
  
  private static void outputResultSet(ResultSet rs) throws Exception {
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    for (int i = 1; i < numberOfColumns + 1; i++) {
      String columnName = rsMetaData.getColumnName(i);
      System.out.print(columnName + "   ");
    }
    while (rs.next()) {
      for (int i = 1; i < numberOfColumns + 1; i++) {
        System.out.print(rs.getString(i) + "   ");
      }
      System.out.println();
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

WebRowSet: execute(Connection conn)

 
import java.io.File;
import java.io.FileWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.sql.rowset.WebRowSet;
import com.sun.rowset.WebRowSetImpl;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,"anotherValue")");    
    
    Statement stmt = conn.createStatement();
    String sqlQuery = "SELECT * FROM survey WHERE id="1"";
    WebRowSet webRS = new WebRowSetImpl();
    webRS.setCommand(sqlQuery);
    webRS.execute(conn);
    File file = new File("1.xml");
    FileWriter fw = new FileWriter(file);
    System.out.println("Writing db data to file " + file.getAbsolutePath());
    webRS.writeXml(fw);
    StringWriter sw = new StringWriter();
    webRS.writeXml(sw);
    System.out.println(sw.toString());
    fw.flush();
    fw.close();
    stmt.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "userName";
    String password = "password";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

WebRowSet: setCommand(String cmd)

 
import java.io.File;
import java.io.FileWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.sql.rowset.WebRowSet;
import com.sun.rowset.WebRowSetImpl;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,"anotherValue")");    
    
    Statement stmt = conn.createStatement();
    String sqlQuery = "SELECT * FROM survey WHERE id="1"";
    WebRowSet webRS = new WebRowSetImpl();
    webRS.setCommand(sqlQuery);
    webRS.execute(conn);
    File file = new File("1.xml");
    FileWriter fw = new FileWriter(file);
    System.out.println("Writing db data to file " + file.getAbsolutePath());
    webRS.writeXml(fw);
    StringWriter sw = new StringWriter();
    webRS.writeXml(sw);
    System.out.println(sw.toString());
    fw.flush();
    fw.close();
    stmt.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "userName";
    String password = "password";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}