Java by API/java.sql/Statement

Материал из Java эксперт
Версия от 17:47, 31 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Statement: addBatch(String sql)

   <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 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();
   }
 }

} 


 </source>



Statement: executeBatch()

   <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 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();
   }
 }

} 


 </source>



Statement.EXECUTE_FAILED

   <source lang="java">

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", "");
 }

} 

 </source>



Statement: executeQuery(String sql)

   <source lang="java">

/* 

* */

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);
   }
 }

} 


 </source>



Statement: getFetchSize()

   <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);
   // 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);
 }

} 

 </source>



Statement: getGeneratedKeys()

   <source lang="java">

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();
 }

} 

 </source>



Statement: getMaxFieldSize()

   <source lang="java">

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);
 }

} 


 </source>



Statement: getMaxRows()

   <source lang="java">

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);
 }

} 


 </source>



Statement: getQueryTimeout()

   <source lang="java">

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);
 }

} 


 </source>



Statement.RETURN_GENERATED_KEYS

   <source lang="java">

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();
 }

} 

 </source>



Statement: setFetchSize(int rows)

   <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);
   // 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);
 }

} 

 </source>



Statement.SUCCESS_NO_INFO

   <source lang="java">

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", "");
 }

} 

 </source>



WebRowSet: execute(Connection conn)

   <source lang="java">

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;
 }

} 

 </source>



WebRowSet: setCommand(String cmd)

   <source lang="java">

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;
 }

} 

 </source>
Источник — «http://jexp.ru/index.php?title=Java_by_API/java.sql/Statement&oldid=2473»