Java by API/java.sql/Statement — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 20:43, 31 мая 2010
Содержание
- 1 Statement: addBatch(String sql)
- 2 Statement: executeBatch()
- 3 Statement.EXECUTE_FAILED
- 4 Statement: executeQuery(String sql)
- 5 Statement: getFetchSize()
- 6 Statement: getGeneratedKeys()
- 7 Statement: getMaxFieldSize()
- 8 Statement: getMaxRows()
- 9 Statement: getQueryTimeout()
- 10 Statement.RETURN_GENERATED_KEYS
- 11 Statement: setFetchSize(int rows)
- 12 Statement.SUCCESS_NO_INFO
- 13 WebRowSet: execute(Connection conn)
- 14 WebRowSet: setCommand(String cmd)
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>