Java Tutorial/Database/SqlServer

Материал из Java эксперт
Перейти к: навигация, поиск

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>


Источник — «http://www.jexp.ru/index.php?title=Java_Tutorial/Database/SqlServer&oldid=4770»