23 Getting Started with Working with Databases
This chapter includes the following sections:
About Working with Databases
JDeveloper enables you to work with Oracle and non-Oracle databases directly, and to design, create, and edit databases by working with offline database definitions.
To get started quickly using Oracle databases in JDeveloper, see:
- Using Oracle Database 21c Express Edition. See Getting Started With Oracle Database Express Edition.
- Creating connections to Oracle and non-Oracle databases. See Connecting to Databases.
- Working in the Databases window. See Using the Databases Window.
- Database Development with JDeveloper. See "Database Development" at
http://docs.oracle.com/cd/E18941_01/tutorials/toc.htm.
Connecting to and Working with Databases
Usually you start working with a database by creating a connection to it, or by importing an existing connection. JDeveloper helps you quickly to create connections to Oracle databases, and you can also connect to and work with a number of non-Oracle databases. Once you have a database connection you can search for database objects in the Databases window, or use the search tools to find specific objects, or compare databases and their contents. You can also edit data and import and export data, and you can create reports about the database and objects in it.
Designing Databases
You can work directly with databases through a database connection using the integrated tools in JDeveloper which include SQL Worksheet and the database object editors. Alternatively, you can create an offline database and working either in the Applications window or the database modeler you can work with offline database definitions to model the database and then generate the results to a database through a database connection.
Database connections can be listed in the Applications window or Databases window, where they are available to applications you are working on, or in the Resources window, where they are available for reuse in other applications.
Once you have a database connection, you can:
- Browse and search databases for specific objects.
- Produce reports about databases and their contents.
- Import and export data.
- Copy, compare and export databases.
You can work with offline databases, which you can model on the database modeler or work with in the Applications window.
You can create, edit, and drop objects in a database or in an offline database.
You can write and execute Java programs using JDBC that access Oracle and non-Oracle databases.
If you are new to using databases with JDeveloper, one of the easiest ways to get started is to try out Oracle Database 21c Express Edition (Oracle Database XE).
Getting Started With Oracle Database Express Edition
If you are new to using databases with JDeveloper, one of the easiest ways to get started is to try out Oracle Database 21c Express Edition (Oracle Database XE).
Oracle Database XE is an entry-level, small-footprint version of Oracle Database 21c. It is free to develop, deploy, and distribute; fast to download; and simple to administer. Use it to start learning about using databases with JDeveloper. You can download it from Getting Started: Oracle Database Express Edition (XE), which is available at http://www.oracle.com/technetwork/database/express-edition/overview/index.html
After you have downloaded and installed Oracle Database XE, use the Oracle Database Express Edition and Oracle Application Express documentation to create and administer users, and unlock the sample user, HR. You may want to grant additional privileges, for example to create tables and materialized views. Now you can create a database connection from JDeveloper to the sample user. How?
In the Create Database Connection dialog, use the following values. Leave blank any fields that are not mentioned.
Table 23-1 Connection details for Oracle Database Express Edition
| Field | Value |
|---|---|
| Create Connection In | Choose Application resources. The connection will be displayed in the Applications window, under Application Resources. |
| Connection Name | Enter a meaningful name for this connection. |
| Connection Type | Oracle (JDBC) (default).
|
| Username | HR to use the sample user. If you have created a new database user, enter the name of that user.
|
| Password | Enter the password you entered when you unlocked the sample user or created a new user. |
| Save Password | Selected (default). |
| Driver | thin (default)
|
| Host Name | When Oracle Database XE is installed on the local system use the default of localhost or 127.0.0.1. Otherwise enter the IP address or resolvable hostname of the machine where it is installed.
|
| JDBC Port | 1521 (default)
|
| SID | XE (default)
|
Click Test Connection at the bottom of the dialog. Success! indicates that you have a connection to the database. If you get any other message, check that you have entered the values above correctly, and check that the Oracle Database XE has started.
How to Manage Database Preferences and Properties
There are a number of preferences that allows you to control how to use the database functionality in JDeveloper.
The preferences are available in the Preferences dialog, available from the Tools menu:
Table 23-2
| Node in Preferences Dialog | Values that can be set |
|---|---|
|
Database |
Choose not to have date and time default values validated. Set the default path for export DDL files. |
|
Database: Advanced |
Set options such as the SQL array fetch size and display options for null values. |
|
Database: Autotrace/Explain Plan |
Specify the information to be displayed on the Autotrace and Explain Plan pages in the SQL Worksheet. |
|
Database: Drag and Drop |
Specify the type of SQL statement created in the SQL Worksheet when you drag an object from the Databases window into the SQL Worksheet. |
|
Database: Licensing |
Some JDeveloper features require that licenses for specific Oracle Database options be in effect for the database connection that will use the feature. This page enables you to specify, for each defined connection, whether the database has the Oracle Change Management Pack, the Oracle Tuning Pack, and the Oracle Diagnostics Pack. For each cell in this display (combination of license and connection), the value can be true (checked box), false (cleared box), or unspecified (solid-filled box). If an option is specified as true for a connection in this pane, you will not be prompted with a message about the option being required when you use that connection for a feature that requires the option. |
|
Database: Navigation Filter |
Specify the type of SQL statement created in the SQL Worksheet when you drag an object from the Databases window into the SQL Worksheet. Control the types of objects that appear in the Databases window for connections to Oracle and third-party databases. Select Enable Navigation Tree Filtering to choose the tab for the database type you want. For each type you can select the types of objects to appear in the hierarchy for connections to that type of database. |
|
Database: JDBC Driver Options |
Register and manage JDBC drivers for the BI JDBC driver, and the WebLogic JDBC drivers for DB2, Informix, SQL Server and Sybase. |
|
Database: NLS |
Specify globalization support parameters, such as the language, territory, sort preference, and date format. |
|
Database: ObjectViewer |
Specify whether to freeze object viewer windows, and display options for the output. |
|
Database: PL/SQL Compiler |
Specify options for compilation of PL/SQL subprograms. |
|
Database: Reports |
Choose that database reports in JDeveloper are closed when the database is disconnected. Select the limit for the number of rows for a chart. The default is |
|
Database: SQL*Plus |
Set the path to the SQL*Plus command line tool. |
|
Database: SQL Editor Code Templates |
View, add, and remove templates for editing SQL and PL/SQL code. Code templates assist you in writing code more quickly and efficiently by inserting text for commonly used statements. |
|
Database: SQL Formatter |
Allows you to control how statements in the SQL Worksheet are formatted. |
|
Database: User Defined Extensions |
(Not used by JDeveloper.) |
|
Database: Utilities |
Provides default values for utility wizards and editors |
|
Database: Worksheet |
Specify options for the SQL Worksheet. |
|
Diagrams: Database |
Set preferences that control how diagrams are displayed. |
To manage database preferences in the Preferences dialog:
-
Choose Tools > Preferences.
-
From the Preferences page, select the page you want. For more information at any time, press F1 or click Help from within the dialog.
To manage properties in the Project Properties dialog:
- Choose Application > Project Properties (to change or specify a property for just the current project), or Default Project Properties (to set default properties).
- In the dialog, choose the page you want. For more information at any time, press F1 or click Help from within the dialog.
As well as managing these preferences and properties, you can also filter schemas or objects in a database connection to just see the ones you want.