Java Tutorial/Database/SqlServer — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 08:05, 1 июня 2010
Содержание
- 1 Call a stored procedure with no parameters and return value.
- 2 Calling a Stored Procedure in a Database with no parameters
- 3 Connect to a database and read from table
- 4 Connect to database and call stored procedure
- 5 Create a sensitive scrollable result set
- 6 Creating a SQLServer Table to Store Java Types
- 7 Determining If a Database Supports Updatable Result Sets: An updatable result set allows modification to data in a table through the result set.
- 8 Get all table catalogs
- 9 Get all table schemas
- 10 Getting the Number of Rows in a Table Using a Scrollable Result Set
- 11 Load driver for SQL Server
- 12 Updating a Row in a Database Table Using an Updatable Result Set
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>