Admin: 1 версия

2010-06-01T05:05:51Z

1 версия

в 17:44, 31 мая 2010

2010-05-31T17:44:27Z

Новая страница

== Call a stored procedure with no parameters and return value. ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
public static void main(String[] argv) throws Exception {
Connection con = null;
CallableStatement proc_stmt = null;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE",
"USERID", "PASSWORD");
proc_stmt = con.prepareCall("{ call someStoredProc() }");
proc_stmt.executeQuery();
}
}</source>



== Calling a Stored Procedure in a Database with no parameters ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
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);
CallableStatement cs = connection.prepareCall("{call myproc}");
cs.execute();
}
}</source>



== Connect to a database and read from table ==



<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[] args) throws Exception {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection m_Connection = DriverManager.getConnection(
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=MyDatabase", "userid", "password");
Statement m_Statement = m_Connection.createStatement();
String query = "SELECT * FROM MyTable";
ResultSet m_ResultSet = m_Statement.executeQuery(query);
while (m_ResultSet.next()) {
System.out.println(m_ResultSet.getString(1) + ", " + m_ResultSet.getString(2) + ", "
+ m_ResultSet.getString(3));
}
}
}</source>



== Connect to database and call stored procedure ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
public static void main(String[] argv) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE",
"USERID", "PASSWORD");
CallableStatement proc_stmt = con.prepareCall("{ call generateID(?) }");
proc_stmt.setString(1, "employee");
ResultSet rs = proc_stmt.executeQuery();
if (rs.next()) {
int employeeId = rs.getInt(1);
System.out.println("Generated employeeId: " + employeeId);
} else {
System.out.println("Stored procedure couldn"t generate new Id");
}
}
}</source>



== Create a sensitive scrollable result set ==



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

Statement stmt = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
}
}</source>



== Creating a SQLServer Table to Store Java Types ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
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);
Statement stmt = connection.createStatement();
// Column Name SQLServer Type Java Type
String sql = "CREATE TABLE sqlserver_all_table("
+ "col_boolean BIT, " // boolean
+ "col_byte TINYINT, " // byte
+ "col_short SMALLINT, " // short
+ "col_int INTEGER, " // int
+ "col_float REAL, " // float
+ "col_double DOUBLE PRECISION, " // double
+ "col_bigdecimal DECIMAL(13,0), " // BigDecimal; can also be NUMERIC(p,s)
+ "col_string VARCHAR(254), " // String
+ "col_date DATETIME, " // Date
+ "col_time DATETIME, " // Time
+ "col_timestamp TIMESTAMP, " // Timestamp
+ "col_characterstream TEXT, " // CharacterStream or AsciiStream (< 2 GBytes)
+ "col_binarystream IMAGE)"; // BinaryStream (< 2 GBytes)
stmt.executeUpdate(sql);
}
}</source>



== Determining If a Database Supports Updatable Result Sets: An updatable result set allows modification to data in a table through the result set. ==



<source lang="java">
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
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);
DatabaseMetaData dmd = connection.getMetaData();
if (dmd.supportsResultSetConcurrency(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)) {
System.out.println("Updatable result sets are supported");
} else {
System.out.println("Updatable result sets are not supported");
}
}
}</source>



== Get all table catalogs ==



<source lang="java">
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
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);
DatabaseMetaData dbmd = connection.getMetaData();
String[] types = { "TABLE" };
ResultSet resultSet = dbmd.getTables(null, null, "%", types);
while (resultSet.next()) {
String tableName = resultSet.getString(3);
String tableCatalog = resultSet.getString(1);
String tableSchema = resultSet.getString(2);
}
}
}</source>



== Get all table schemas ==



<source lang="java">
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
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);
DatabaseMetaData dbmd = connection.getMetaData();
String[] types = { "TABLE" };
ResultSet resultSet = dbmd.getTables(null, null, "%", types);
while (resultSet.next()) {
String tableName = resultSet.getString(3);
String tableCatalog = resultSet.getString(1);
String tableSchema = resultSet.getString(2);
}
}
}</source>



== Getting the Number of Rows in a Table Using a Scrollable Result Set ==



<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);
// Create a scrollable result set
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
// Move to the end of the result set
resultSet.last();
// Get the row number of the last row which is also the row count
int rowCount = resultSet.getRow();
}
}</source>



== Load driver for SQL Server ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
public static void main(String[] argv) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE",
"USERID", "PASSWORD");
CallableStatement proc_stmt = con.prepareCall("{ call generateID(?) }");
proc_stmt.setString(1, "employee");
ResultSet rs = proc_stmt.executeQuery();
if (rs.next()) {
int employeeId = rs.getInt(1);
System.out.println("Generated employeeId: " + employeeId);
} else {
System.out.println("Stored procedure couldn"t generate new Id");
}
}
}</source>



== Updating a Row in a Database Table Using an Updatable Result Set ==



<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);
Statement stmt = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
resultSet.first();
// Update the value of column col_string on that row
resultSet.updateString("col_string", "new data");
// Update the row; if auto-commit is enabled, update is committed
resultSet.updateRow();
}
}</source>

← Предыдущая	Версия 05:05, 1 июня 2010
(нет различий)

Java Tutorial/Database/SqlServer - История изменений

Admin: 1 версия

в 17:44, 31 мая 2010