в 17:43, 31 мая 2010

2010-05-31T17:43:48Z

Admin: 1 версия

2010-05-31T14:47:07Z

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>

← Предыдущая	Версия 17:43, 31 мая 2010
(нет различий)

Java by API/java.sql/Statement - История изменений

в 17:43, 31 мая 2010

Admin: 1 версия