1) Preparing Databases
Create the database that stores the country name and total population data. Use Oracle for the database.
1-1) Creating the Database Environment
Create the environment for the Oracle using settings like those shown below. If using an environment where the setup items are different, substitute those setup items in the explanation below. For details on the setup method, refer to the Oracle manual.
Item | Content |
|---|---|
Product | Oracle Database 11g |
Server name | myhost |
SID | COUNTRYDATA |
Port number for remote connection | 1521 |
User name | STUDIO |
Password | Password corresponding to the user name |
Schema | STUDIO |
1-2) Installing the JDBC Driver
Install the Oracle JDBC driver in order to connect to the Oracle from the workbench. For details on the installation method, refer to the Oracle manual.
2) Connecting to the Database
2-1) Switching Perspectives
From the menu bar, select [Window] > [Open Perspective] > [Other]. The [Open Perspective] dialog box is displayed. Select [Database Development] from the [Open Perspective] dialog box, then click [OK].
2-2) Creating the Connection Profile
Select [Databases] in the Data Source Explorer view, then right-click to display the context menu. Select [New] from the context menu to display the [New Connection Profile] wizard.
From the [Connection Profile Types] list on the [Connection Profile] page, select [Oracle].
Set the following information, then click [Next].
Setup Items | Setup Content |
|---|---|
Name | Oracle_Studio |
The [Specify a Driver and Connection Details] page is displayed.
Click 
(New Driver Definition) to the right of [Drivers] to display the [New Driver Definition] dialog box. In the [Name/Type] tab, from the [Available driver templates] list, select [Database] > [Oracle Thin Driver]. Check and enter the other items as shown below. After the following information is set, click [OK].
[Name/Type] tab
Setup Items | Setup Content |
|---|---|
Name | Oracle Thin Driver |
System Vendor | Oracle |
System Version | 11 |
[Jar List] tab
Setup Items | Setup Content |
|---|---|
Driver files | <JDBC driver storage destination directory>\ojdbc6.jar |
In the [Specify a Driver and Connection Details] page, set the [Properties] group items as shown below. Click [Next] to display the [Summary] page.
[General] tab of [Properties] group
Setup Items | Setup Content |
|---|---|
SID | COUNTRYDATA |
Host | myhost |
Port number | 1521 |
User name | STUDIO |
Password | Password corresponding to the user name |
The entered information can be checked on the [Summary] page.
Check the following information, then click [Finish].
Item to Check | Contents to Check |
|---|---|
Name | Oracle_Studio |
Description | None |
Auto connect at startup | false |
Auto connect on finish | true |
SID | COUNTRYDATA |
Host | myhost |
Prot number | 1521 |
User name | STUDIO |
Save password | false |
URL | jdbc:oracle:thin:@myhost:1521:COUNTRYDATA |
2-3) Connecting to the Database
In the [New Connection Profile] wizard, on the [Specify a Driver and Connection Details] page, [Connect when the wizard completes] is selected by default. In this state the database is automatically connected to when the wizard completes.
If connecting manually, select the connection profile [Oracle_Studio] created in the Data Source Explorer view. Right-click to display the context menu, and select [Connect].
3) Creating Tables
3-1) Switching Perspectives
Select [Resource] from the [Open Perspective] dialog box, then click [OK].
3-2) Creating the Project
Create a project to place the SQL file in. If the SQL file is to be placed in an existing project, a project does not need to be created. Select [File] > [New] > [Project] from the menu bar to display the [New Project] wizard. Select [General] > [Project] from the New Project wizard, then click [Next].
Check and enter the following setup item. After the following information is set, click [Finish].
Setup Items | Setup Content |
|---|---|
Project name | DBSample |
3-3) Creating the SQL File
Directly under the project, create the SQL file that creates the table. Select the [DBSample] project in the Project Explorer view, then right-click to display the context menu. Select [New] > [Other] from the context menu. Select [SQL Development] > [SQL File] from the [New] wizard, then select [Next]. The [New SQL File] wizard is displayed.
Check and enter the following setup items. After the following information is set, click [Finish].
Setup Items | Setup Content |
|---|---|
File name | countrydata.sql |
Database server type | Oracle_11 |
Connection profile name | Oracle_Studio |
Database name | COUNTRYDATA |
3-4) Editing the SQL File
In the created SQL file, code the table definitions and data insertion as shown below.
SQL File Definitions
CREATE TABLE C_DATA ( ID NUMERIC PRIMARY KEY NOT NULL, C_NAME VARCHAR2(80), T_POP NUMERIC(10) ); INSERT INTO C_DATA VALUES(1, 'China', 1330000000); INSERT INTO C_DATA VALUES(2, 'India', 1140000000); INSERT INTO C_DATA VALUES(3, 'U.S.A.', 300000000); INSERT INTO C_DATA VALUES(4, 'Indonesia', 230000000); INSERT INTO C_DATA VALUES(5, 'Brazil', 190000000); INSERT INTO C_DATA VALUES(6, 'Pakistan', 160000000); INSERT INTO C_DATA VALUES(7, 'Bangladesh', 150000000); INSERT INTO C_DATA VALUES(8, 'Russia', 140000000); INSERT INTO C_DATA VALUES(9, 'Nigeria', 140000000); INSERT INTO C_DATA VALUES(10, 'Japan', 130000000); |
3-5) Executing SQL
Open the SQL file created in the SQL File Editor and right-click in the editor area. Select [Execute All] from the context menu.
Check the SQL execution results in the [SQL Results] view.
4) Checking Tables
4-1) Switching Perspectives
Select [Database Development] in the [Open Perspective] dialog box, then click [OK].
4-2) Checking Table Definitions
The created table can be checked in the Data Source Explorer view. To change the contents, select the connection profile [Oracle_Studio], then right-click to display the context menu. Select [Refresh].
4-3) Referencing Table Data
The created table data can be referenced using the Table Data editor. In the Data Source Explorer view, select [Databases] > [Oracle_Studio] > [COUNTRYDATA] > [Schemas] > [STUDIO] > [Tables] > [C_DATA], then right-click to display the context menu. Select [Data] > [Edit] from the context menu to launch the Table Data editor.
Point
Values can be changed by editing the editor cells. In addition, editing can be performed by selecting a cell, then selecting operations such as row deletion, row addition, and so on, from the context menu.
