Cách kết nối Java với SQL server

[nholoiemnoi]

Ngồi cả buổi cuối cùng cũng kết nối được. Share cho các bạn nè, cài từ đầu nhé (ko có gì cả)

1. Cài Netbean.
2. Cài SQL server 2005 (Mình dùng bản Express có sẵn ở trường)
Chú ý trong lúc cài có cái bước như hình vẽ. Nhớ đặt pass cho nó. Mình đặt pass là 123456.



3. Cài xong. Chỉnh port 1433 như hình vẽ




4. Copi cái Microsoft SQL Server JDBC Driver 2.0 vào C:\Program Files\
Cái driver này có thể vào trang chủ microsoft để tải bản mới nhất. Hoặc dùng bản của mình mới down cũng đc: sqljdbc_2.0.1803.100_enu
http://www.mediafire.com/?wdyrvmn1mqn

chú ý: giải nén xong có cái file sqljdbc.jar quan trọng nhất, nhớ đấy.

5. Mở SQL lên, tạo 1 database tùy ý. tớ đặt tên là: J2EE1_Demo.



Chạy thử chương trình này xem đã kết nối được chưa.

import java.sql.*;
public class Connect{
    public static void main (String[] args){
        Connection con = null;
        try{
            String userName = "sa";
            String password = "123456";
            String url = "jdbc:sqlserver://localhost:1433;databaseName=J2EE1_Demo;";
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = java.sql.DriverManager.getConnection(url,userName,password);
            System.out.println ("Da ket noi CSDL");
            }
        catch (Exception e){
        System.err.println("KHONG KET NOI DUOC");
           }
finally{
    if (con != null){
        try{
            con.close ();
            System.out.println ("Dong ket noi");
            }
        catch (Exception e) { /* bo qua loi luc dong csdl */ }
            }
        }
    }
}

Kết quả kết nối được là:

Code:
run:
Da ket noi CSDL
Dong ket noi
BUILD SUCCESSFUL (total time: 0 seconds)

Hoặc

1. Kết nối thông qua ODBC

a. Dùng Data Source Name
Thiết lập DataSource như sau :
Trên windows Vào menu Start->Settings->Control Panel->Administrative Tools->Data Sources (ODBC), kết quả như hình

Nhấn Nút Add, Tìm đến SQL Server, Nhấn Finish.
Nhập vào tên của Data Source, ví dụ là MyDSN, chọn server: nếu server ở localhost thì có thể gõ vào dấu ., nếu có nhiều instances của sqlserver thì phải gõ vào tên_máy_tính/tên_instance. Nhấn Next 2 lần
Chọn Database cần nối:

Nhấn Next, Nhấn Finish.
Nhấn nút “Test Data Source” để kiểm tra kết nối, nếu thành công sẽ hiển thị dòng “TEST COMPLETED SUCCESSFULLY!” Nhấn OK để kết thúc. Ta cần ghi nhớ tên của DSN.
Code để kết nối đến SQL server sử dụng data source như sau:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectODBC_DSN {
private Connection con=null;

public ConnectODBC_DSN() throws Exception{

String url=”sun.jdbc.odbc.JdbcOdbcDriver”;
Class.forName(url);
String dbUrl=”jdbc[IMG]data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABAQMAAAA l21bKAAAAA1BMVEXh5PJm+yKVAAAAAXRSTlMAQObYZgAAAApJR EFUCNdjYAAAAAIAAeIhvDMAAAAASUVORK5CYII=[/IMG]dbc:myDSN”;
con=DriverManager.getConnection(dbUrl);
}

public ResultSet GetResultSet(String tableName)throws SQLException {
ResultSet rs=null;
Statement stmt=con.createStatement();
String sql=”select * from “+tableName;
rs=stmt.executeQuery(sql);
return rs;
}

public void Close()throws Exception {
con.close();
}

//Thử chương trình
public static void main(String[] args) {
try {
ConnectODBC_DSN conDSN=new ConnectODBC_DSN();
ResultSet rs=conDSN.GetResultSet(”EmployeeInfo”);
while(rs.next()) {
System.out.println(rs.getString(”EmpName”));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close ();
System.out.println (“Dong ket noi”);
}
catch (Exception e) { }
}
}
}
}

b. Dùng chuỗi kết nối

Ở đây việc kết nối đến SQL Server cũng thông qua ODBC nhưng chúng ta không cần phải khai báo DSN, ta có thể dùng chuỗi kết nối trực tiếp thông qua ODBC để nối đến SQL Server. Chuỗi kết nối có dạng
String dbUrl=”jdbc[IMG]data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABAQMAAAA l21bKAAAAA1BMVEXh5PJm+yKVAAAAAXRSTlMAQObYZgAAAApJR EFUCNdjYAAAAAIAAeIhvDMAAAAASUVORK5CYII=[/IMG]dbc: driver={SQL Server};Server=.;DatabaseName=Employee;UserName=sa ;Password=”;
Lưu ý với thuộc tính Server: nếu chỉ có 1 server trên máy local thì dùng dấu chấm hoặc chỉ rõ tên máy hoặc IP address. Nếu trên máy có nhiều instance của sql server thì phải chỉ định rõ dùng instance nào. Ví dụ trên máy cài instance có tên SQLEXPRESS thì Server phải là: Server=ComputerName/InstanceName
Code kết nối như sau :

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectODBC_ConString {

private Connection con=null;

public ConnectODBC_ConString() throws Exception{

String url=”sun.jdbc.odbc.JdbcOdbcDriver”;
Class.forName(url);
String dbUrl=”jdbc[IMG]data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABAQMAAAA l21bKAAAAA1BMVEXh5PJm+yKVAAAAAXRSTlMAQObYZgAAAApJR EFUCNdjYAAAAAIAAeIhvDMAAAAASUVORK5CYII=[/IMG]dbc: driver={SQL Server};Server=.;Database=Employee;UserName=sa;Pas sword=”;
con=DriverManager.getConnection(dbUrl);
}
}

2. Kết nối trực tiếp dùng driver

Ở đây việc kết nối thông qua 1 driver do nhà cung cấp hệ quản trị csdl hoặc 1 hãng trung gian thứ 3 cung cấp. Có rất nhiều driver khác nhau có thể làm việc với SQL server nhưng ở đây tôi muốn đề cập đến driver của microsoft. Bạn có thể download nó tại đây.
Sau khi download về, bạn giải nén ra 1 thư mục bất kỳ, giả sử là C:\javaSoft\mssqlJDBCDriver. Bạn chú ý đến tập tin sqljdbc.jar. Copy tập tin này vào project của bạn để có thể sử dụng cho project đó hoặc bạn copy nó vào thư mục %JAVA_HOME%\jre\lib\ext để có thể sử dụng cho tất cả các project có trong máy của bạn.
Bây giờ bạn có thể viết code kết nối như sau

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ConnectWithDriver {
private Connection con=null;
public ConnectWithDriver()throws Exception {
Class.forName(”com.microsoft.sqlserver.jdbc.SQLS erverDriver”);
String url=”jdbc:sqlserver://localhost:1433;databaseName=Employee”;
con=DriverManager.getConnection(url,”sa”,”") ;
}
}

3. Kết nối dùng driver thông qua Application Server
Ví dụ ở đây tôi dùng Jboss 4.2.3GA
Bạn phải tạo 1 file có tên mssql-ds.xml trong thư mục %JBOSS-HOME%\ server\default\deploy\ có nội dung như sau

<?xml version=”1.0″ encoding=”UTF-8″?>
<datasources>
<local-tx-datasource>
<jndi-name>MyDSJNDI_Name</jndi-name>
<connection-url>
jdbc:sqlserver://localhost:1433;databaseName=Tên_CSDL_của_bạn
</connection-url>
<driver-class>
com.microsoft.sqlserver.jdbc.SQLServerDriver
</driver-class>
<user-name>sa</user-name>
<password></password>
<metadata>
<type-mapping>MS SQLSERVER2000</type-mapping>
</metadata>
</local-tx-datasource>
<datasources>

Copy sqljdbc.jar vào thư mục mục %JBOSS-HOME%\server\default\lib\ext
Start server và bạn có thể dùng JNDI để lookup như sau:


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class Connect_JNDI {
private Connection con=null;

public Connect_JNDI() throws Exception{
DataSource ds=null;
Context ctx=new InitialContext();
ds=(DataSource)ctx.lookup(”java://MyDSJNDI_Name”);
con=ds.getConnection();
}
}

[Ví dụ] JDBC Demo - Các thao tác với CSDL

/* ================================
JDBC Programming Examples
================================

Author: Hemanth. Balaji
--------------------------

1. Substitute your driver's JDBC URL for the generic JDBC URL
   that appears in the code. In other words, put your driver's JDBC URL
   between the quotation marks in the follwoing line:

        String url = "jdbc:mySubprotocol:myDataSource";

   The documentation for your driver should give you this URL.

2. Substitute the driver's full class name for "myDriver.ClassName" in
   the following line:

            Class.forName("myDriver.ClassName");

3. Substitute the username and password you use for your database
   in the following:

           "userid", "password"


*/


import javax.swing.JOptionPane;
import java.sql.*;
public class JdbcDemo
{
    public static void main(String args[])
    {
        JOptionPane.showMessageDialog(null,"Welcome to JDBC Demo");
        int choice = -1;
        String userid="scott";
        String password = "tiger";

        do
        {
            choice = getChoice();
            if (choice != 0)
            {
                getSelected(choice, userid, password);
            }
        }
        while ( choice !=  0);
        System.exit(0);
    }

    public static int getChoice()
    {
        String choice;
        int ch;
        choice = JOptionPane.showInputDialog(null,
            "1. Create Coffees Table
"+
            "2. Insert Values into Coffees Table
"+
            "3. Create Suppliers Table
"+
            "4. Insert Values into Suppliers Table
"+
            "5. Update Table Example on Coffees Table
"+
            "6. A PreparedStatement Demo On Coffees Table
"+
            "7. A PreparedStatement Demo On Coffees Table using a FOR Statement
"+
            "8. List of the coffees he buys from Acme, Inc [Supplier]
"+
            "9. Using Transactions Demo"+
            "10. Creating a Stored Procedue Demo
"+
            "11. Using Callable Statement to call a Stored Procedure
"+
            "12. Batch Update Demo
"+
            "0. Exit

"+
            "Enter your choice");
        ch = Integer.parseInt(choice);
        return ch;

    }

    public static void getSelected(int choice, String userid, String password)
    {
        if(choice==1)
        {
            createCoffees(userid, password);
        }
        else if(choice==2)
        {
            insertCoffees(userid, password);
        }
        else if(choice==3)
        {
            createSuppliers(userid, password);
        }
        else if(choice==4)
        {
            insertSuppliers(userid, password);
        }
        else if(choice==5)
        {
            updateCoffees(userid, password);
        }
        else if(choice==6)
        {
            prepare1Demo(userid, password);
        }
        else if(choice==7)
        {
            prepare2Demo(userid, password);
        }
        else if(choice==8)
        {
            joinDemo(userid, password);
        }
        else if(choice==9)
        {
            transDemo(userid, password);
        }
        else if(choice==10)
        {
            createProcedure1(userid, password);
        }
        else if(choice==11)
        {
            callableDemo(userid, password);
        }
        else if(choice==12)
        {
            batchUpdateDemo(userid, password);
        }
    }

    // Create Coffees Table
    public static void createCoffees(String userid, String password)
    {
        String url = "jdbc:odbc:bob";       // String url = "jdbc:mySubprotocol:myDataSource"; ?
                                            //               jdbc:subprotocol:subname
        Connection con;
        String createString;
        createString = "create table COFFEES " +
                            "(COF_NAME varchar(32), " +
                            "SUP_ID int, " +
                            "PRICE float, " +
                            "SALES int, " +
                            "TOTAL int)";
        Statement stmt;

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  //Class.forName("myDriver.ClassName"); ?

        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }

        try {
            con = DriverManager.getConnection(url,
                                     "userid", "password");

            stmt = con.createStatement();
            stmt.executeUpdate(createString);

            stmt.close();
            con.close();

        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
    }

    // Insert values into Coffees Table
    public static void insertCoffees(String userid, String password)
    {
        String url = "jdbc:odbc:bob";
        Connection con;
        Statement stmt;
        String query = "select COF_NAME, PRICE from COFFEES";

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }

        try {

                con = DriverManager.getConnection(url,
                                         "userid", "password");

                stmt = con.createStatement();

                stmt.executeUpdate("insert into COFFEES " +
                     "values('Colombian', 00101, 7.99, 0, 0)");

                stmt.executeUpdate("insert into COFFEES " +
                     "values('French_Roast', 00049, 8.99, 0, 0)");

                stmt.executeUpdate("insert into COFFEES " +
                     "values('Espresso', 00150, 9.99, 0, 0)");

                stmt.executeUpdate("insert into COFFEES " +
                     "values('Colombian_Decaf', 00101, 8.99, 0, 0)");

                stmt.executeUpdate("insert into COFFEES " +
                     "values('French_Roast_Decaf', 00049, 9.99, 0, 0)");

                ResultSet rs = stmt.executeQuery(query);

                System.out.println("Coffee Break Coffees and Prices:");
                while (rs.next()) {
                    String s = rs.getString("COF_NAME");    // OR rs.getString(1);
                    float f = rs.getFloat("PRICE");         // OR rs.getFloat(3);
                    System.out.println(s + "   " + f);
                }

                stmt.close();
                con.close();

            } catch(SQLException ex) {
                System.err.println("SQLException: " + ex.getMessage());
            }
    }


    // Create Suppliers Table
        public static void createSuppliers(String userid, String password)
        {
            String url = "jdbc:odbc:bob";
            Connection con;
            String createString;
            createString = "create table SUPPLIERS " +
                        "(SUP_ID int, " +
                        "SUP_NAME varchar(40), " +
                        "STREET varchar(40), " +
                        "CITY varchar(20), " +
                        "STATE char(2), ZIP char(5))";

            Statement stmt;

            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            } catch(java.lang.ClassNotFoundException e) {
                System.err.print("ClassNotFoundException: ");
                System.err.println(e.getMessage());
            }

            try {
                con = DriverManager.getConnection(url,
                                         "userid", "password");

                stmt = con.createStatement();
                stmt.executeUpdate(createString);

                stmt.close();
                con.close();

            } catch(SQLException ex) {
                System.err.println("SQLException: " + ex.getMessage());
            }
        }

    // Insert values into Coffees Table
    public static void insertSuppliers(String userid, String password)
    {
        String url = "jdbc:odbc:bob";
        Connection con;
        Statement stmt;
        String query = "select SUP_NAME, SUP_ID from SUPPLIERS";

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: ");
            System.err.println(e.getMessage());
        }

        try {
                con = DriverManager.getConnection(url,
                                         "userid", "password");

                stmt = con.createStatement();

                stmt.executeUpdate("insert into SUPPLIERS " +
                         "values(49, 'Superior Coffee', '1 Party Place', " +
                     "'Mendocino', 'CA', '95460')");

                stmt.executeUpdate("insert into SUPPLIERS " +
                    "values(101, 'Acme, Inc.', '99 Market Street', " +
                    "'Groundsville', 'CA', '95199')");

                stmt.executeUpdate("insert into SUPPLIERS " +
                         "values(150, 'The High Ground', '100 Coffee Lane', " +
                     "'Meadows', 'CA', '93966')");

                ResultSet rs = stmt.executeQuery(query);

                System.out.println("Suppliers and their ID Numbers:");
                while (rs.next()) {
                    String s = rs.getString("SUP_NAME");
                    int n = rs.getInt("SUP_ID");
                    System.out.println(s + "   " + n);
                }

                stmt.close();
                con.close();

            } catch(SQLException ex) {
                System.err.println("SQLException: " + ex.getMessage());
            }
    }


        // Update Coffees Table
        public static void updateCoffees(String userid, String password)
        {
            String url = "jdbc:odbc:bob";
            Connection con;
            String updateString;
            updateString = "UPDATE COFFEES " +
                "SET SALES = 75 " +
                "WHERE COF_NAME LIKE 'Colombian'";

            String query = "SELECT COF_NAME, SALES FROM COFFEES " +
                       "WHERE COF_NAME LIKE 'Colombian'";

            Statement stmt;

            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            } catch(java.lang.ClassNotFoundException e) {
                System.err.print("ClassNotFoundException: ");
                System.err.println(e.getMessage());
            }

            try {
                con = DriverManager.getConnection(url,
                                         "userid", "password");

                stmt = con.createStatement();
                stmt.executeUpdate(updateString);

                ResultSet rs = stmt.executeQuery(query);
                while (rs.next()) {
                        String s = rs.getString("COF_NAME");  //1
                        int n = rs.getInt("SALES");           //2
                        System.out.println(n + " pounds of " + s +
                                          " sold this week.");
                }

                stmt.close();
                con.close();

            } catch(SQLException ex) {
                System.err.println("SQLException: " + ex.getMessage());
            }
        }

        // Update Coffees Table using a prepared Statement
        public static void prepare1Demo(String userid, String password)
        {
            String url = "jdbc:odbc:bob";
            Connection con;

            Statement stmt=null;
            String query = "SELECT COF_NAME, SALES FROM COFFEES ";


            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            } catch(java.lang.ClassNotFoundException e) {
                System.err.print("ClassNotFoundException: ");
                System.err.println(e.getMessage());
            }

            try {
                con = DriverManager.getConnection(url,
                                         "userid", "password");

                PreparedStatement updateSales = con.prepareStatement(
                    "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
                updateSales.setInt(1, 75);
                updateSales.setString(2, "Colombian");
                updateSales.executeUpdate();


                ResultSet rs = stmt.executeQuery(query);
                while (rs.next()) {
                        String s = rs.getString("COF_NAME");  //1
                        int n = rs.getInt("SALES");           //2
                        System.out.println(s + "   " + n);
                }

                stmt.close();
                con.close();

            } catch(SQLException ex) {
                System.err.println("SQLException: " + ex.getMessage());
            }
        }

        // Update Coffees Table using a prepared Statement
        public static void prepare2Demo(String userid, String password)
        {
            String url = "jdbc:odbc:bob";
            Connection con;

            Statement stmt=null;
            String query = "SELECT COF_NAME, SALES FROM COFFEES ";


            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            } catch(java.lang.ClassNotFoundException e) {
                System.err.print("ClassNotFoundException: ");
                System.err.println(e.getMessage());
            }

            try {
                con = DriverManager.getConnection(url,
                                         "userid", "password");

                PreparedStatement updateSales;
                String updateString = "update COFFEES " +
                              "set SALES = ? where COF_NAME like ?";
                updateSales = con.prepareStatement(updateString);
                int [] salesForWeek = {175, 150, 60, 155, 90};
                String [] coffees = {"Colombian", "French_Roast", "Espresso",
                             "Colombian_Decaf", "French_Roast_Decaf"};
                int len = coffees.length;
                for(int i = 0; i < len; i++) {
                        updateSales.setInt(1, salesForWeek[i]);
                        updateSales.setString(2, coffees[i]);
                        updateSales.executeUpdate();

                // int n= updateSales.executeUpdate() to find out how may rows have been updated
                    }



                ResultSet rs = stmt.executeQuery(query);
                while (rs.next()) {
                        String s = rs.getString("COF_NAME");  //1
                        int n = rs.getInt("SALES");           //2
                        System.out.println(s + "   " + n);
                }

                stmt.close();
                con.close();

            } catch(SQLException ex) {
                System.err.println("SQLException: " + ex.getMessage());
            }
        }

        //Using join on 2 table to retrieve results
        public static void joinDemo(String userid, String password)
        {
            String url = "jdbc:odbc:bob";
            Connection con;
            String query = "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
                           "from COFFEES, SUPPLIERS " +
                           "where SUPPLIERS.SUP_NAME like 'Acme, Inc.' and " +
                           "SUPPLIERS.SUP_ID = COFFEES.SUP_ID";
            Statement stmt;

            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            } catch(java.lang.ClassNotFoundException e) {
                System.err.print("ClassNotFoundException: ");
                System.err.println(e.getMessage());
            }

            try {
                con = DriverManager.getConnection (url,
                                         "userid", "password");

                stmt = con.createStatement();

                ResultSet rs = stmt.executeQuery(query);
                System.out.println("Supplier, Coffee:");
                while (rs.next()) {
                    String supName = rs.getString(1);
                    String cofName = rs.getString(2);
                    System.out.println("    " + supName + ", " + cofName);
                }

                stmt.close();
                con.close();

            } catch(SQLException ex) {
                System.err.print("SQLException: ");
                System.err.println(ex.getMessage());
            }
        }

        // Using Transaction Autocommit Option
        public static void transDemo(String userid, String password)
        {
            String url = "jdbc:odbc:bob";
            Connection con=null;
            Statement stmt;
            PreparedStatement updateSales;
            PreparedStatement updateTotal;
            String updateString = "update COFFEES " +
                            "set SALES = ? where COF_NAME = ?";

            String updateStatement = "update COFFEES " +
                    "set TOTAL = TOTAL + ? where COF_NAME = ?";
            String query = "select COF_NAME, SALES, TOTAL from COFFEES";

            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            } catch(java.lang.ClassNotFoundException e) {
                System.err.print("ClassNotFoundException: ");
                System.err.println(e.getMessage());
            }

            try {

                con = DriverManager.getConnection(url,
                                         "userid", "password");

                updateSales = con.prepareStatement(updateString);
                updateTotal = con.prepareStatement(updateStatement);
                int [] salesForWeek = {175, 150, 60, 155, 90};
                String [] coffees = {"Colombian", "French_Roast",
                                    "Espresso", "Colombian_Decaf",
                                    "French_Roast_Decaf"};
                int len = coffees.length;
                con.setAutoCommit(false);
                for (int i = 0; i < len; i++) {
                    updateSales.setInt(1, salesForWeek[i]);
                    updateSales.setString(2, coffees[i]);
                    updateSales.executeUpdate();

                    updateTotal.setInt(1, salesForWeek[i]);
                    updateTotal.setString(2, coffees[i]);
                    updateTotal.executeUpdate();
                    con.commit();
                }

                con.setAutoCommit(true);

                updateSales.close();
                updateTotal.close();

                stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(query);

                while (rs.next()) {
                    String c = rs.getString("COF_NAME");
                    int s = rs.getInt("SALES");
                    int t = rs.getInt("TOTAL");
                    System.out.println(c + "     " +  s + "    " + t);
                }

                stmt.close();
                con.close();

            } catch(SQLException ex) {
                System.err.println("SQLException: " + ex.getMessage());
                if (con != null) {
                    try {
                        System.err.print("Transaction is being ");
                        System.err.println("rolled back");
                        con.rollback();
                    } catch(SQLException excep) {
                        System.err.print("SQLException: ");
                        System.err.println(excep.getMessage());
                    }
                }
            }
        }

        /*Creating a Stored procedure involving the coffees and the suppliers table

                    create procedure SHOW_SUPPLIERS
                    as
                    select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
                    from SUPPLIERS, COFFEES
                    where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
                    order by SUP_NAME
        */

        public static void createProcedure1(String userid, String password)
        {
            String url = "jdbc:odbc:bob";
            Connection con;
            Statement stmt;
            String createProcedure = "create procedure SHOW_SUPPLIERS " +
                         "as " +
                         "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
                         "from SUPPLIERS, COFFEES " +
                         "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
                         "order by SUP_NAME";

            try {
                    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                } catch(java.lang.ClassNotFoundException e) {
                    System.err.print("ClassNotFoundException: ");
                    System.err.println(e.getMessage());
                }
            try {
                    con = DriverManager.getConnection(url,
                                 "DPST_TRNG", "DPST_TRNG4321");

                stmt = con.createStatement();
                stmt.executeUpdate(createProcedure);
                stmt.close();
                con.close();

                } catch(SQLException ex) {
                    System.err.println("SQLException: " + ex.getMessage());
                }

        }


        //Using Callable Statement to call a Stored Procedure
        public static void callableDemo(String userid, String password)
        {
            String url = "jdbc:odbc:bob";
            Connection con;
            Statement stmt=null;

            try {
                    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                } catch(java.lang.ClassNotFoundException e) {
                    System.err.print("ClassNotFoundException: ");
                    System.err.println(e.getMessage());
                }
            try {
                    con = DriverManager.getConnection(url,
                                 "DPST_TRNG", "DPST_TRNG4321");


                    CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
                    ResultSet rs = cs.executeQuery();

                    while (rs.next()) {
                        String c = rs.getString("SUP_NAME");
                        String s = rs.getString("COF_NAME");
                        System.out.println(c + "     " +  s );
                    }

                    stmt.close();
                    con.close();

                } catch(SQLException ex) {
                    System.err.println("SQLException: " + ex.getMessage());
                }

        }

        //A code showing the Batch Update Syntax
        public static void batchUpdateDemo(String userid, String password)
        {
            ResultSet rs = null;
            PreparedStatement ps = null;

            String url = "jdbc:odbc:bob";

            Connection con;
            Statement stmt;
            try {

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            } catch(java.lang.ClassNotFoundException e) {
                System.err.print("ClassNotFoundException: ");
                System.err.println(e.getMessage());
            }

            try {

                con = DriverManager.getConnection(url,
                                        "userid", "password");
                con.setAutoCommit(false);

                stmt = con.createStatement();

                stmt.addBatch("INSERT INTO COFFEES " +
                     "VALUES('Amaretto', 49, 9.99, 0, 0)");
                stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Hazelnut', 49, 9.99, 0, 0)");
                stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
                stmt.addBatch("INSERT INTO COFFEES " +
                    "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
                int [] updateCounts = stmt.executeBatch();
                con.commit();
                con.setAutoCommit(true);

                ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

                System.out.println("Table COFFEES after insertion:");
                while (uprs.next()) {
                    String name = uprs.getString("COF_NAME");
                    int id = uprs.getInt("SUP_ID");
                    float price = uprs.getFloat("PRICE");
                    int sales = uprs.getInt("SALES");
                    int total = uprs.getInt("TOTAL");
                    System.out.print(name + "   " + id + "   " + price);
                    System.out.println("   " + sales + "   " + total);
                }

                uprs.close();
                stmt.close();
                con.close();

            } catch(BatchUpdateException b) {
                System.err.println("-----BatchUpdateException-----");
                System.err.println("SQLState:  " + b.getSQLState());
                System.err.println("Message:  " + b.getMessage());
                System.err.println("Vendor:  " + b.getErrorCode());
                System.err.print("Update counts:  ");
                int [] updateCounts = b.getUpdateCounts();
                for (int i = 0; i < updateCounts.length; i++) {
                    System.err.print(updateCounts[i] + "   ");
                }
                System.err.println("");

            } catch(SQLException ex) {
                System.err.println("-----SQLException-----");
                System.err.println("SQLState:  " + ex.getSQLState());
                System.err.println("Message:  " + ex.getMessage());
                System.err.println("Vendor:  " + ex.getErrorCode());
            }
        }


}//End of class

nguồn: www.tracuu.org
​

[chungcunhavuong]

Thank..bài viết rất bổ ích cho newbie[IMG]data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABAQMAAAA l21bKAAAAA1BMVEXh5PJm+yKVAAAAAXRSTlMAQObYZgAAAApJR EFUCNdjYAAAAAIAAeIhvDMAAAAASUVORK5CYII=[/IMG]