Spring+JDBC實例

1- 介紹

本篇教程是一個簡單的Spring+JDBC的操作,作爲一個入門級來實現讀取MySQL(可選:Oracle或SQL Server)數據中Department表的所有數據。這篇文章是基於:

  • Spring 4
  • Eclipse 4.4 (LUNA)

在本文中,連接的是 MySQL數據庫。您可以創建數據庫DB類型,如Oracle,MySQL和SQLServer:

  • 附SQL腳本

2- 創建Maven工程

  • File/New/Other...
    Spring+JDBC實例

輸入:

  • Group Id: com.yiibai
  • Artifact Id: SpringJDBC

Spring+JDBC實例
工程被創建後如下所示:
Spring+JDBC實例

3- 配置Maven

在這篇文章中,將指導你訪問幾種常見數據庫的類型,如下:

  • Oracle
  • MySQL
  • SQLServer

       所以在Maven中,將配置庫用於以上三種類型的數據庫。在現實中,只需要配置一個使用的數據庫類型(如:MySQL)。

  • pom.xml

4.0.0
com.yiibai
SpringJDBCTutorial
0.0.1-SNAPSHOT

1.7 UTF-8 UTF-8 codelds https://code.lds.org/nexus/content/groups/main-repo
   <!-- Spring framework -->
   <!-- http://mvnrepository.com/artifact/org.springframework/spring-core -->
   <dependency>
       <groupId>org.springframework</groupId>
       <artifactId>spring-core</artifactId>
       <version>4.1.4.RELEASE</version>
   </dependency>

   <!-- http://mvnrepository.com/artifact/org.springframework/spring-context -->
   <dependency>
       <groupId>org.springframework</groupId>
       <artifactId>spring-context</artifactId>
       <version>4.1.4.RELEASE</version>
   </dependency>

   <!-- http://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
   <dependency>
       <groupId>org.springframework</groupId>
       <artifactId>spring-jdbc</artifactId>
       <version>4.1.4.RELEASE</version>
   </dependency>

   <!-- MySQL database driver -->
   <!-- http://mvnrepository.com/artifact/mysql/mysql-connector-java -->
   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.34</version>
   </dependency>

   <!-- Oracle database driver -->
   <dependency>
       <groupId>com.oracle</groupId>
       <artifactId>ojdbc6</artifactId>
       <version>11.2.0.3</version>
   </dependency>

   <!-- SQLServer database driver (JTDS) -->
   <!-- http://mvnrepository.com/artifact/net.sourceforge.jtds/jtds -->
   <dependency>
       <groupId>net.sourceforge.jtds</groupId>
       <artifactId>jtds</artifactId>
       <version>1.3.1</version>
   </dependency>

4- Java類

Spring+JDBC實例

  • Department.java

package com.yiibai.springjdbc.bean;

public class Department {

private Long deptId;
private String deptNo;
private String deptName;

public Department() {

}

public Department(Long deptId, String deptNo, String deptName) {
this.deptId = deptId;
this.deptNo = deptNo;
this.deptName = deptName;
}

public Long getDeptId() {
return deptId;
}

public void setDeptId(Long deptId) {
this.deptId = deptId;
}

public String getDeptNo() {
return deptNo;
}

public void setDeptNo(String deptNo) {
this.deptNo = deptNo;
}

public String getDeptName() {
return deptName;
}

public void setDeptName(String deptName) {
this.deptName = deptName;
}
}

  • DepartmentDAO.java

package com.yiibai.springjdbc.dao;

import java.util.List;

import com.yiibai.springjdbc.bean.Department;

public interface DepartmentDAO {

public List queryDepartment() throws Exception ;

}

  • DepartmentImplDAO.java

package com.yiibai.springjdbc.daoimpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.yiibai.springjdbc.bean.Department;
import com.yiibai.springjdbc.dao.DepartmentDAO;

public class DepartmentImplDAO implements DepartmentDAO {

private DataSource dataSource;

public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}

@Override
public List queryDepartment() throws SQLException {
Connection conn = dataSource.getConnection();

   String sql = "Select d.dept\_id, d.dept\_no, d.dept\_name from department d";
   Statement smt = conn.createStatement();

   ResultSet rs = smt.executeQuery(sql);
   List<Department> list = new ArrayList<Department>();
   while (rs.next()) {
       Long deptId = rs.getLong("dept\_id");
       String deptNo = rs.getString("dept\_no");
       String deptName = rs.getString("dept\_name");
       Department dept = new Department(deptId, deptNo, deptName);
       list.add(dept);
   }
   return list;

}

}

  • MainDemo.java

package com.yiibai.springjdbc;

import java.util.List;

import com.yiibai.springjdbc.bean.Department;
import com.yiibai.springjdbc.dao.DepartmentDAO;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MainDemo {

public static void main(String[] args) throws Exception {
ApplicationContext context = new ClassPathXmlApplicationContext(
"spring-module.xml");

   DepartmentDAO deptDAO = (DepartmentDAO) context
           .getBean("departmentDAO");

   List<Department> depts = deptDAO.queryDepartment();

   for (Department dept : depts) {
       System.out.println("Dept ID " + dept.getDeptId());
       System.out.println("Dept No " + dept.getDeptNo());
       System.out.println("Dept Name " + dept.getDeptName());
   }

}

}

5- 配置Spring


Spring+JDBC實例

  • spring-datasource-oracle.xml

   <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
   <property name="url" value="jdbc:oracle:thin:@localhost:1521:db11g" />
   <property name="username" value="simplehr" />
   <property name="password" value="1234" />
  • spring-datasource-mysql.xml

   <property name="driverClassName" value="com.mysql.jdbc.Driver" />
   <property name="url" value="jdbc:mysql://localhost:3306/yiibai" />
   <property name="username" value="root" />
   <property name="password" value="" />
  • spring-datasource-sqlserver.xml

   <!-- Using JDBC Driver: JTDS -->
   <property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver" />
   <property name="url"
       value="jdbc:jtds:sqlserver://localhost:1433/simplehr;instance=SQLEXPRESS" />
   <property name="username" value="sa" />
   <property name="password" value="1234" />
  • spring-department.xml



  • spring-module.xml

6- 運行MainDemo主程序

運行主類示例結果如下:

Spring+JDBC實例

附:數據庫腳本

2.1- Oracle 創建 DB 腳本

  • ORACLE SCRIPT:

    create table DEPARTMENT (

     DEPT\_ID number(10,0) not null,
     DEPT\_NAME varchar2(255 char) not null,
     DEPT\_NO varchar2(20 char) not null unique,
     LOCATION varchar2(255 char),
     primary key (DEPT\_ID)

    );

    create table EMPLOYEE (

     EMP\_ID number(19,0) not null,
     EMP\_NAME varchar2(50 char) not null,
     EMP\_NO varchar2(20 char) not null unique,
     HIRE\_DATE date not null,
     IMAGE blob,
     JOB varchar2(30 char) not null,
     SALARY float not null,
     DEPT\_ID number(10,0) not null,
     MNG\_ID number(19,0),
     primary key (EMP\_ID)

    );

    create table SALARY_GRADE (

     GRADE number(10,0) not null,
     HIGH\_SALARY float not null,
     LOW\_SALARY float not null,
     primary key (GRADE)

    );

    create table TIMEKEEPER (

     Timekeeper\_Id varchar2(36 char) not null,
     Date\_Time timestamp not null,
     In\_Out char(1 char) not null,
     EMP\_ID number(19,0) not null,
     primary key (Timekeeper\_Id)

    );

    alter table EMPLOYEE

     add constraint FK75C8D6AE269A3C9
     foreign key (DEPT\_ID)
     references DEPARTMENT;

    alter table EMPLOYEE

     add constraint FK75C8D6AE6106A42
     foreign key (EMP\_ID)
     references EMPLOYEE;

    alter table EMPLOYEE

     add constraint FK75C8D6AE13C12F64
     foreign key (MNG\_ID)
     references EMPLOYEE;

    alter table TIMEKEEPER

     add constraint FK744D9BFF6106A42
     foreign key (EMP\_ID)
     references EMPLOYEE;
  • INSERT DATA (ORACLE)

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (10, 'ACCOUNTING', 'D10', 'NEW YORK');

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (20, 'RESEARCH', 'D20', 'DALLAS');

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (30, 'SALES', 'D30', 'CHICAGO');

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (40, 'OPERATIONS', 'D40', 'BOSTON');


insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7839, 'KING', 'E7839', to_date('17-11-1981', 'dd-mm-yyyy'), 'PRESIDENT', 5000, 10, null);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7566, 'JONES', 'E7566', to_date('02-04-1981', 'dd-mm-yyyy'), 'MANAGER', 2975, 20, 7839);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7902, 'FORD', 'E7902', to_date('03-12-1981', 'dd-mm-yyyy'), 'ANALYST', 3000, 20, 7566);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7369, 'SMITH', 'E7369', to_date('17-12-1980', 'dd-mm-yyyy'), 'CLERK', 800, 20, 7902);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7698, 'BLAKE', 'E7698', to_date('01-05-1981', 'dd-mm-yyyy'), 'MANAGER', 2850, 30, 7839);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7499, 'ALLEN', 'E7499', to_date('20-02-1981', 'dd-mm-yyyy'), 'SALESMAN', 1600, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7521, 'WARD', 'E7521', to_date('22-02-1981', 'dd-mm-yyyy'), 'SALESMAN', 1250, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7654, 'MARTIN', 'E7654', to_date('28-09-1981', 'dd-mm-yyyy'), 'SALESMAN', 1250, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7782, 'CLARK', 'E7782', to_date('09-06-1981', 'dd-mm-yyyy'), 'MANAGER', 2450, 30, 7839);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7788, 'SCOTT', 'E7788', to_date('19-04-1987', 'dd-mm-yyyy'), 'ANALYST', 3000, 20, 7566);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7844, 'TURNER', 'E7844', to_date('08-09-1981', 'dd-mm-yyyy'), 'SALESMAN', 1500, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7876, 'ADAMS', 'E7876', to_date('23-05-1987', 'dd-mm-yyyy'), 'CLERK', 1100, 20, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7900, 'ADAMS', 'E7900', to_date('03-12-1981', 'dd-mm-yyyy'), 'CLERK', 950, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7934, 'MILLER', 'E7934', to_date('23-01-1982', 'dd-mm-yyyy'), 'CLERK', 1300, 10, 7698);


insert into Salary_Grade (GRADE, HIGH_SALARY, LOW_SALARY)
values (1, 9999, 3001);

2.2- MySQL創建數據庫腳本

  • MYSQL SCRIPT:

create table DEPARTMENT (
DEPT_ID integer not null,
DEPT_NAME varchar(255) not null,
DEPT_NO varchar(20) not null,
LOCATION varchar(255),
primary key (DEPT_ID),
unique (DEPT_NO)
);

create table EMPLOYEE (
EMP_ID bigint not null,
EMP_NAME varchar(50) not null,
EMP_NO varchar(20) not null,
HIRE_DATE date not null,
IMAGE longblob,
JOB varchar(30) not null,
SALARY float not null,
DEPT_ID integer not null,
MNG_ID bigint,
primary key (EMP_ID),
unique (EMP_NO)
);

create table SALARY_GRADE (
GRADE integer not null,
HIGH_SALARY float not null,
LOW_SALARY float not null,
primary key (GRADE)
);

create table TIMEKEEPER (
Timekeeper_Id varchar(36) not null,
Date_Time datetime not null,
In_Out char(1) not null,
EMP_ID bigint not null,
primary key (Timekeeper_Id)
);

alter table EMPLOYEE
add index FK75C8D6AE269A3C9 (DEPT_ID),
add constraint FK75C8D6AE269A3C9
foreign key (DEPT_ID)
references DEPARTMENT (DEPT_ID);

alter table EMPLOYEE
add index FK75C8D6AE6106A42 (EMP_ID),
add constraint FK75C8D6AE6106A42
foreign key (EMP_ID)
references EMPLOYEE (EMP_ID);

alter table EMPLOYEE
add index FK75C8D6AE13C12F64 (MNG_ID),
add constraint FK75C8D6AE13C12F64
foreign key (MNG_ID)
references EMPLOYEE (EMP_ID);

alter table TIMEKEEPER
add index FK744D9BFF6106A42 (EMP_ID),
add constraint FK744D9BFF6106A42
foreign key (EMP_ID)
references EMPLOYEE (EMP_ID);

  • INSERT DATA (MYSQL)

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (10, 'ACCOUNTING', 'D10', 'NEW YORK');

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (20, 'RESEARCH', 'D20', 'DALLAS');

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (30, 'SALES', 'D30', 'CHICAGO');

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (40, 'OPERATIONS', 'D40', 'BOSTON');


insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7839, 'KING', 'E7839', Str_To_Date('17-11-1981', '%d-%m-%Y'), 'PRESIDENT', 5000, 10, null);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7566, 'JONES', 'E7566', Str_To_Date('02-04-1981', '%d-%m-%Y'), 'MANAGER', 2975, 20, 7839);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7902, 'FORD', 'E7902', Str_To_Date('03-12-1981', '%d-%m-%Y'), 'ANALYST', 3000, 20, 7566);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7369, 'SMITH', 'E7369', Str_To_Date('17-12-1980', '%d-%m-%Y'), 'CLERK', 800, 20, 7902);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7698, 'BLAKE', 'E7698', Str_To_Date('01-05-1981', '%d-%m-%Y'), 'MANAGER', 2850, 30, 7839);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7499, 'ALLEN', 'E7499', Str_To_Date('20-02-1981', '%d-%m-%Y'), 'SALESMAN', 1600, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7521, 'WARD', 'E7521', Str_To_Date('22-02-1981', '%d-%m-%Y'), 'SALESMAN', 1250, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7654, 'MARTIN', 'E7654', Str_To_Date('28-09-1981', '%d-%m-%Y'), 'SALESMAN', 1250, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7782, 'CLARK', 'E7782', Str_To_Date('09-06-1981', '%d-%m-%Y'), 'MANAGER', 2450, 30, 7839);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7788, 'SCOTT', 'E7788', Str_To_Date('19-04-1987', '%d-%m-%Y'), 'ANALYST', 3000, 20, 7566);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7844, 'TURNER', 'E7844', Str_To_Date('08-09-1981', '%d-%m-%Y'), 'SALESMAN', 1500, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7876, 'ADAMS', 'E7876', Str_To_Date('23-05-1987', '%d-%m-%Y'), 'CLERK', 1100, 20, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7900, 'ADAMS', 'E7900', Str_To_Date('03-12-1981', '%d-%m-%Y'), 'CLERK', 950, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7934, 'MILLER', 'E7934', Str_To_Date('23-01-1982', '%d-%m-%Y'), 'CLERK', 1300, 10, 7698);


insert into Salary_Grade (GRADE, HIGH_SALARY, LOW_SALARY)
values (1, 9999, 3001);

2.3- SQL Server 創建 DB 腳本

  • SQL SERVER SCRIPT:

create table DEPARTMENT (
DEPT_ID int not null,
DEPT_NAME varchar(255) not null,
DEPT_NO varchar(20) not null,
LOCATION varchar(255),
primary key (DEPT_ID),
unique (DEPT_NO)
);

create table EMPLOYEE (
EMP_ID numeric(19,0) not null,
EMP_NAME varchar(50) not null,
EMP_NO varchar(20) not null,
HIRE_DATE datetime not null,
IMAGE image,
JOB varchar(30) not null,
SALARY float not null,
DEPT_ID int not null,
MNG_ID numeric(19,0),
primary key (EMP_ID),
unique (EMP_NO)
);

create table SALARY_GRADE (
GRADE int not null,
HIGH_SALARY float not null,
LOW_SALARY float not null,
primary key (GRADE)
);

create table TIMEKEEPER (
Timekeeper_Id varchar(36) not null,
Date_Time datetime not null,
In_Out char(1) not null,
EMP_ID numeric(19,0) not null,
primary key (Timekeeper_Id)
);

alter table EMPLOYEE
add constraint FK75C8D6AE269A3C9
foreign key (DEPT_ID)
references DEPARTMENT;

alter table EMPLOYEE
add constraint FK75C8D6AE6106A42
foreign key (EMP_ID)
references EMPLOYEE;

alter table EMPLOYEE
add constraint FK75C8D6AE13C12F64
foreign key (MNG_ID)
references EMPLOYEE;

alter table TIMEKEEPER
add constraint FK744D9BFF6106A42
foreign key (EMP_ID)
references EMPLOYEE;

  • INSERT DATA (SQL SERVER)

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (10, 'ACCOUNTING', 'D10', 'NEW YORK');

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (20, 'RESEARCH', 'D20', 'DALLAS');

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (30, 'SALES', 'D30', 'CHICAGO');

insert into Department (DEPT_ID, DEPT_NAME, DEPT_NO, LOCATION)
values (40, 'OPERATIONS', 'D40', 'BOSTON');


insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7839, 'KING', 'E7839', Convert(Datetime,'17-11-1981', 105), 'PRESIDENT', 5000, 10, null);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7566, 'JONES', 'E7566', Convert(Datetime,'02-04-1981', 105), 'MANAGER', 2975, 20, 7839);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7902, 'FORD', 'E7902', Convert(Datetime,'03-12-1981', 105), 'ANALYST', 3000, 20, 7566);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7369, 'SMITH', 'E7369', Convert(Datetime,'17-12-1980', 105), 'CLERK', 800, 20, 7902);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7698, 'BLAKE', 'E7698', Convert(Datetime,'01-05-1981', 105), 'MANAGER', 2850, 30, 7839);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7499, 'ALLEN', 'E7499', Convert(Datetime,'20-02-1981', 105), 'SALESMAN', 1600, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7521, 'WARD', 'E7521', Convert(Datetime,'22-02-1981', 105), 'SALESMAN', 1250, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7654, 'MARTIN', 'E7654', Convert(Datetime,'28-09-1981', 105), 'SALESMAN', 1250, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7782, 'CLARK', 'E7782', Convert(Datetime,'09-06-1981', 105), 'MANAGER', 2450, 30, 7839);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7788, 'SCOTT', 'E7788', Convert(Datetime,'19-04-1987', 105), 'ANALYST', 3000, 20, 7566);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7844, 'TURNER', 'E7844', Convert(Datetime,'08-09-1981', 105), 'SALESMAN', 1500, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7876, 'ADAMS', 'E7876', Convert(Datetime,'23-05-1987', 105), 'CLERK', 1100, 20, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7900, 'ADAMS', 'E7900', Convert(Datetime,'03-12-1981', 105), 'CLERK', 950, 30, 7698);

insert into Employee (EMP_ID, EMP_NAME, EMP_NO, HIRE_DATE, JOB, SALARY, DEPT_ID, MNG_ID)
values (7934, 'MILLER', 'E7934', Convert(Datetime,'23-01-1982', 105), 'CLERK', 1300, 10, 7698);


insert into Salary_Grade (GRADE, HIGH_SALARY, LOW_SALARY)
values (1, 9999, 3001);

代碼下載: http://pan.baidu.com/s/1skyvdKh