Các thao tác cơ bản trên DB2

Phần 1 :

File code tạo table trong cơ sở dữ liệu DB2

File này có tên là testdb.sql

1:  CREATE TABLE EMPLOYEE(  
2:       ID SMALLINT NOT NULL,  
3:       NAME VARCHAR(9),  
4:       DEPT SMALLINT CHECK (DEPT BETWEEN 10 AND 100),  
5:       JOB CHAR(5) CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')),  
6:       SALARY DECIMAL(7,2),  
7:       COMM DECIMAL(7,2),  
8:       PRIMARY KEY (ID)  
9:  );  

Phần 2 :

Các lệnh cơ bản trong DB2

1:  start database :  
2:                 DB2 START  
3:  create database :  
4:                 DB2 CREATE DATABASE TESTDB3 AUTOMATIC STORAGE YES  
5:  list database  :  
6:                 DB2 LIST DB DIRECTORY  
7:  connect with database :  
8:                 DB2 CONNECT TO TESTDB02  
9:  list of tables         :  
10:                 DB2 LIST TABLES  
11:  create tables from files:  
12:                 db2 -tvf sql_file_name  
13:  insert value to table:  
14:                 INSERT INTO EMPLOYEE VALUES ( 1, 'DucNguyen', 11, 'Mgr', 3200, 4500)  
15:                 INSERT INTO EMPLOYEE VALUES ( 3, 'ThienPham', 13, 'Clerk', 3500, 4000)  
16:                 INSERT INTO EMPLOYEE VALUES ( 5, 'TungPham', 13, 'Sales', 3600, 4530)  
17:                 INSERT INTO EMPLOYEE(ID, NAME, DEPT, JOB, SALARY, COMM) VALUES (2,'VuTruong',11,'Sales',3500,4000)  
18:  select all rows from table:  
19:                 SELECT * FROM EMPLOYEE  

Phần 3 : Code Java kết nối DB2 File name : EzJava.java

1:  import java.sql.*;  
2:  public class EzJava   
3:  {  
4:   public static void main(String[] args)   
5:   {  
6:    String urlPrefix = "jdbc:db2:";  
7:    String url= urlPrefix +"//127.0.0.1:50000/TESTDB02";  
8:    String user="db2inst1";  
9:    String password="123456";  
10:    String empNo = null;  
11:    String empName =null;                         
12:    Connection con=null;  
13:    Statement stmt=null;  
14:    ResultSet rs=null;  
15:    System.out.println ("**** Enter class EzJava");  
16:    try   
17:    {                                      
18:          // Load the driver  
19:          Class.forName("com.ibm.db2.jcc.DB2Driver");                
20:          System.out.println("**** Loaded the JDBC driver");  
21:          // Create the connection using the IBM Data Server Driver for JDBC and SQLJ  
22:          con = DriverManager.getConnection (url, user, password);          
23:          // Commit changes manually  
24:          con.setAutoCommit(false);  
25:          System.out.println("**** Created a JDBC connection to the data source");  
26:          // Create the Statement  
27:          stmt = con.createStatement();                       
28:          System.out.println("**** Created JDBC Statement object");  
29:          // Execute a query and generate a ResultSet instance  
30:          rs = stmt.executeQuery("SELECT ID FROM EMPLOYEE");           
31:          System.out.println("**** Created JDBC ResultSet object");  
32:          // Print all of the employee numbers to standard output device  
33:          while (rs.next()) {  
34:                 empNo = rs.getString(1);  
35:                 empName = rs.getString(2);  
36:                 System.out.println("ID : " + empNo+" | Name : " + empName);  
37:          }  
38:          System.out.println("**** Fetched all rows from JDBC ResultSet");  
39:          // Close the ResultSet  
40:          rs.close();  
41:          System.out.println("**** Closed JDBC ResultSet");  
42:          // Close the Statement  
43:          stmt.close();  
44:          System.out.println("**** Closed JDBC Statement");  
45:          // Connection must be on a unit-of-work boundary to allow close  
46:          con.commit();  
47:          System.out.println ( "**** Transaction committed" );  
48:          // Close the connection  
49:          con.close();                                
50:          System.out.println("**** Disconnected from data source");  
51:          System.out.println("**** JDBC Exit from class EzJava - no errors");  
52:    }  
53:    catch (ClassNotFoundException e)  
54:    {  
55:          System.err.println("Could not load JDBC driver");  
56:          System.out.println("Exception: " + e);  
57:          e.printStackTrace();  
58:    }  
59:    catch(SQLException ex)                            
60:    {  
61:          System.err.println("SQLException information");  
62:          while(ex!=null) {  
63:                 System.err.println ("Error msg: " + ex.getMessage());  
64:                 System.err.println ("SQLSTATE: " + ex.getSQLState());  
65:                 System.err.println ("Error code: " + ex.getErrorCode());  
66:                 ex.printStackTrace();  
67:                 ex = ex.getNextException(); // For drivers that support chained exceptions  
68:          }  
69:    }  
70:   } // End main  
71:  }  // End EzJava