ตอนที่ 11 : การเขียน Java Application ร่วมกับ SQL Database บน Windows Azure

import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;

String connectionString =
    "jdbc:sqlserver://bc6hela9fr.database.windows.net:1433" + ";" +  
        "database=thaicreate-db" + ";" + 
        "user=thaicreate-user@bc6hela9fr" + ";" +  
        "password=password@123";

import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;

public class HelloSQLAzure {

    public static void main(String[] args) 
    {

        // Connection string for your SQL Database server.
        // Change the values assigned to your_server, 
        // your_user@your_server,
        // and your_password.
	String connectionString =
	    "jdbc:sqlserver://bc6hela9fr.database.windows.net:1433" + ";" +  
		"database=thaicreate-db" + ";" + 
		"user=thaicreate-user@bc6hela9fr" + ";" +  
		"password=password@123";

        // The types for the following variables are
        // defined in the java.sql library.
        Connection connection = null;  // For making the connection
        Statement statement = null;    // For the SQL statement
        ResultSet resultSet = null;    // For the result set, if applicable

        try
        {
            // Ensure the SQL Server driver class is available.
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            // Establish the connection.
            connection = DriverManager.getConnection(connectionString);

            // Define the SQL string.
            String sqlString = 
		"CREATE TABLE customer ( " +
		"[CustomerID] [varchar](4) NOT NULL, " +
		"[Name] [varchar](50) NULL, " +
		"[Email] [varchar](50) NULL, " +
		"[CountryCode] [varchar](2) NULL, " +
		"[Budget] [float] NULL, " +
		"[Used] [float] NULL, " +
		"	 CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED  " +
		"	( " +
		"		[CustomerID] ASC " +
		"	) " +
		"); " +

            // Use the connection to create the SQL statement.
            statement = connection.createStatement();

            // Execute the statement.
            statement.executeUpdate(sqlString);

            // Provide a message when processing is complete.
            System.out.println("Processing complete.");

        }
        // Exception handling
        catch (ClassNotFoundException cnfe)  
        {

            System.out.println("ClassNotFoundException " +
                               cnfe.getMessage());
        }
        catch (Exception e)
        {
            System.out.println("Exception " + e.getMessage());
            e.printStackTrace();
        }
        finally
        {
            try
            {
                // Close resources.
                if (null != connection) connection.close();
                if (null != statement) statement.close();
                if (null != resultSet) resultSet.close();
            }
            catch (SQLException sqlException)
            {
                // No additional action if close() statements fail.
            }
        }

    }

}

// Connection string for your SQL Database server.
// Change the values assigned to your_server, 
// your_user@your_server,
// and your_password.
String connectionString =
    "jdbc:sqlserver://bc6hela9fr.database.windows.net:1433" + ";" +  
	"database=thaicreate-db" + ";" + 
	"user=thaicreate-user@bc6hela9fr" + ";" +  
	"password=password@123";

// The types for the following variables are
// defined in the java.sql library.
Connection connection = null;  // For making the connection
Statement statement = null;    // For the SQL statement
ResultSet resultSet = null;    // For the result set, if applicable

try
{
    // Ensure the SQL Server driver class is available.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    // Establish the connection.
    connection = DriverManager.getConnection(connectionString);

    // Define the SQL string.
    String sqlString = 
        "CREATE CLUSTERED INDEX index1 " + "ON customer (Email)";

    // Use the connection to create the SQL statement.
    statement = connection.createStatement();

    // Execute the statement.
    statement.executeUpdate(sqlString);

    // Provide a message when processing is complete.
    System.out.println("Processing complete.");

}
// Exception handling and resource closing not shown...

// Connection string for your SQL Database server.
// Change the values assigned to your_server, 
// your_user@your_server,
// and your_password.
String connectionString =
    "jdbc:sqlserver://bc6hela9fr.database.windows.net:1433" + ";" +  
	"database=thaicreate-db" + ";" + 
	"user=thaicreate-user@bc6hela9fr" + ";" +  
	"password=password@123";

// The types for the following variables are
// defined in the java.sql library.
Connection connection = null;  // For making the connection
Statement statement = null;    // For the SQL statement
ResultSet resultSet = null;    // For the result set, if applicable

try
{
    // Ensure the SQL Server driver class is available.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    // Establish the connection.
    connection = DriverManager.getConnection(connectionString);

    // Define the SQL string.
    String sqlString = 
        "SET IDENTITY_INSERT customer ON " + 
            "INSERT INTO customer " + 
            "(CustomerID, Name, Email, CountryCode, Budget, Used) " + 
            "VALUES('C005', 'Surachai Sirisart', '[email protected]'," +
	    " 'TH','2000000','0') ";

    // Use the connection to create the SQL statement.
    statement = connection.createStatement();

    // Execute the statement.
    statement.executeUpdate(sqlString);

    // Provide a message when processing is complete.
    System.out.println("Processing complete.");

}
// Exception handling and resource closing not shown...

// Connection string for your SQL Database server.
// Change the values assigned to your_server, 
// your_user@your_server,
// and your_password.
String connectionString =
    "jdbc:sqlserver://bc6hela9fr.database.windows.net:1433" + ";" +  
	"database=thaicreate-db" + ";" + 
	"user=thaicreate-user@bc6hela9fr" + ";" +  
	"password=password@123";

// The types for the following variables are
// defined in the java.sql library.
Connection connection = null;  // For making the connection
Statement statement = null;    // For the SQL statement
ResultSet resultSet = null;    // For the result set, if applicable

try
{
    // Ensure the SQL Server driver class is available.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    // Establish the connection.
    connection = DriverManager.getConnection(connectionString);

    // Define the SQL string.
    String sqlString = "SELECT * FROM customer";

    // Use the connection to create the SQL statement.
    statement = connection.createStatement();

    // Execute the statement.
    resultSet = statement.executeQuery(sqlString);

    // Loop through the results
    while (resultSet.next())
    {
        // Print out the row data
        System.out.println(
            "CustomerID : " + resultSet.getString("CustomerID") + 
            "Name : " + resultSet.getString("Name") + 
            "Email : " + resultSet.getString("Email") + 
            "CountryCode : " + resultSet.getString("CountryCode") + 
            "Budget : " + resultSet.getString("Budget") + 
            "Used : " + resultSet.getString("Used"));
        }

    // Provide a message when processing is complete.
    System.out.println("Processing complete.");

}
// Exception handling and resource closing not shown...

// Connection string for your SQL Database server.
// Change the values assigned to your_server, 
// your_user@your_server,
// and your_password.
String connectionString =
    "jdbc:sqlserver://bc6hela9fr.database.windows.net:1433" + ";" +  
	"database=thaicreate-db" + ";" + 
	"user=thaicreate-user@bc6hela9fr" + ";" +  
	"password=password@123";

// The types for the following variables are
// defined in the java.sql library.
Connection connection = null;  // For making the connection
Statement statement = null;    // For the SQL statement
ResultSet resultSet = null;    // For the result set, if applicable

try
{
    // Ensure the SQL Server driver class is available.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    // Establish the connection.
    connection = DriverManager.getConnection(connectionString);

// Define the SQL string.
    String sqlString = "SELECT COUNT (CustomerID) FROM customer";

    // Use the connection to create the SQL statement.
    statement = connection.createStatement();

    // Execute the statement.
    resultSet = statement.executeQuery(sqlString);

    // Print out the returned number of rows.
    while (resultSet.next())
    {
        System.out.println("There were " + 
                         resultSet.getInt(1) +
                         " rows returned.");
    }

    // Provide a message when processing is complete.
    System.out.println("Processing complete.");

}
// Exception handling and resource closing not shown...

// Connection string for your SQL Database server.
// Change the values assigned to your_server, 
// your_user@your_server,
// and your_password.
String connectionString =
    "jdbc:sqlserver://bc6hela9fr.database.windows.net:1433" + ";" +  
	"database=thaicreate-db" + ";" + 
	"user=thaicreate-user@bc6hela9fr" + ";" +  
	"password=password@123";

// The types for the following variables are
// defined in the java.sql library.
Connection connection = null;  // For making the connection
Statement statement = null;    // For the SQL statement
ResultSet resultSet = null;    // For the result set, if applicable

try
{
    // Ensure the SQL Server driver class is available.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    // Establish the connection.
    connection = DriverManager.getConnection(connectionString);

    // Define the SQL string.
    String sqlString = 
        "UPDATE customer " + "SET Name = 'Adisorn Bunsong' " + "WHERE CustomerID='C005'";

    // Use the connection to create the SQL statement.
    statement = connection.createStatement();

    // Execute the statement.
    statement.executeUpdate(sqlString);

    // Provide a message when processing is complete.
    System.out.println("Processing complete.");

}// Exception handling and resource closing not shown...

// Connection string for your SQL Database server.
// Change the values assigned to your_server, 
// your_user@your_server,
// and your_password.
String connectionString =
    "jdbc:sqlserver://bc6hela9fr.database.windows.net:1433" + ";" +  
	"database=thaicreate-db" + ";" + 
	"user=thaicreate-user@bc6hela9fr" + ";" +  
	"password=password@123";

// The types for the following variables are
// defined in the java.sql library.
Connection connection = null;  // For making the connection
Statement statement = null;    // For the SQL statement
ResultSet resultSet = null;    // For the result set, if applicable

try
{
    // Ensure the SQL Server driver class is available.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    // Establish the connection.
    connection = DriverManager.getConnection(connectionString);

    // Define the SQL string.
    String sqlString = 
        "DELETE from customer " + 
            "WHERE CustomerID='C005' ";

    // Use the connection to create the SQL statement.
    statement = connection.createStatement();

    // Execute the statement.
    statement.executeUpdate(sqlString);

    // Provide a message when processing is complete.
    System.out.println("Processing complete.");

}
// Exception handling and resource closing not shown...

// Connection string for your SQL Database server.
// Change the values assigned to your_server, 
// your_user@your_server,
// and your_password.
String connectionString =
    "jdbc:sqlserver://bc6hela9fr.database.windows.net:1433" + ";" +  
	"database=thaicreate-db" + ";" + 
	"user=thaicreate-user@bc6hela9fr" + ";" +  
	"password=password@123";

// The types for the following variables are
// defined in the java.sql library.
Connection connection = null;  // For making the connection
Statement statement = null;    // For the SQL statement
ResultSet resultSet = null;    // For the result set, if applicable

try
{
    // Ensure the SQL Server driver class is available.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    // Establish the connection.
    connection = DriverManager.getConnection(connectionString);

    // Define the SQL string.
    String sqlString = 
        "IF EXISTS (SELECT 1 " +
            "FROM sysobjects " + 
            "WHERE xtype='u' AND name='customer') " +
            "SELECT 'customer table exists.'" +
            "ELSE  " +
            "SELECT 'customer table does not exist.'";

    // Use the connection to create the SQL statement.
    statement = connection.createStatement();

    // Execute the statement.
    resultSet = statement.executeQuery(sqlString);

    // Display the result.
    while (resultSet.next())
    {
        System.out.println(resultSet.getString(1));
    }

    // Provide a message when processing is complete.
    System.out.println("Processing complete.");

}
// Exception handling and resource closing not shown...

// Connection string for your SQL Database server.
// Change the values assigned to your_server, 
// your_user@your_server,
// and your_password.
String connectionString =
    "jdbc:sqlserver://bc6hela9fr.database.windows.net:1433" + ";" +  
	"database=thaicreate-db" + ";" + 
	"user=thaicreate-user@bc6hela9fr" + ";" +  
	"password=password@123";

// The types for the following variables are
// defined in the java.sql library.
Connection connection = null;  // For making the connection
Statement statement = null;    // For the SQL statement
ResultSet resultSet = null;    // For the result set, if applicable

try
{
    // Ensure the SQL Server driver class is available.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    // Establish the connection.
    connection = DriverManager.getConnection(connectionString);

    // Define the SQL string.
    String sqlString = 
        "DROP INDEX index1 " + 
            "ON customer";

    // Use the connection to create the SQL statement.
    statement = connection.createStatement();

    // Execute the statement.
    statement.executeUpdate(sqlString);

    // Provide a message when processing is complete.
    System.out.println("Processing complete.");

}
// Exception handling and resource closing not shown...

// Connection string for your SQL Database server.
// Change the values assigned to your_server, 
// your_user@your_server,
// and your_password.
String connectionString =
    "jdbc:sqlserver://bc6hela9fr.database.windows.net:1433" + ";" +  
	"database=thaicreate-db" + ";" + 
	"user=thaicreate-user@bc6hela9fr" + ";" +  
	"password=password@123";

// The types for the following variables are
// defined in the java.sql library.
Connection connection = null;  // For making the connection
Statement statement = null;    // For the SQL statement
ResultSet resultSet = null;    // For the result set, if applicable

try
{
    // Ensure the SQL Server driver class is available.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    // Establish the connection.
    connection = DriverManager.getConnection(connectionString);

    // Define the SQL string.
    String sqlString = "DROP TABLE customer";

    // Use the connection to create the SQL statement.
    statement = connection.createStatement();

    // Execute the statement.
    statement.executeUpdate(sqlString);

    // Provide a message when processing is complete.
    System.out.println("Processing complete.");

}
// Exception handling and resource closing not shown...