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
