| NetBeans Developing Applications with NetBeans IDE Release 8.0 E50452-06 |
|
|
PDF · Mobi · ePub |
| NetBeans Developing Applications with NetBeans IDE Release 8.0 E50452-06 |
|
|
PDF · Mobi · ePub |
This chapter describes how to create connections to Java DB databases and Oracle Database, and how to work with MySQL. It describes how to create and work with database objects including tables and views, and how to execute SQL statements.
This chapter contains the following sections:
The NetBeans IDE allows you to connect to databases and view their content, how to modify database objects, and how to view and change data.
It provides drivers for the Java DB, MySQL, Oracle, and PostgreSQL database servers so that you can connect to these databases very easily. You can also register any other JDBC driver with the IDE, so that you can explore any database that provides a JDBC driver.
When you open the Services window, you see the Databases node used to perform operations related to JDBC-compliant databases within the IDE.
Within the Databases node you can do the following:
Connect to a database.
View current database connections.
Select or add a driver for your database.
Enter SQL statements and see the results immediately.
Run SQL scripts on a connected database.
Migrate table schemas across databases from different vendors.
Create, browse and edit database structures by running SQL statements or using a graphic view.
Databases consist of tables, views and stored procedures, each of which represents a list of columns.
When a connection is established, you can expand the database connection node ( 
) to see the subnodes representing tables, views, and procedures.
In the Services window, expand the node for a connected database.
Right-click the Tables node to reveal the individual tables.
Expand the Tables node to reveal the individual tables.
Expand a table node ( 
) to see the table's columns, an Indexes node and Foreign keys node.
This table lists Services window column icons and their descriptions.
| Icon | Description |
|---|---|
|
|
Table |
|
|
Column |
|
|
Column (Primary Key) |
|
|
Column (Unique key) |
|
|
Index |
|
|
Foreign key |
|
|
Column in foreign key |
|
|
View |
Click on an index node to see the list of the columns contained by that index.
Click on a foreign key node to see the list of the columns contained by that foreign key. A foreign key contains columns that reference another table's columns (usually primary keys).
Click the Views node to see the views on a given database. View columns are dimmed to indicate that you cannot change the physical characteristics of the columns in a view. However, you can change a view by dropping the view and subsequently recreating it.
Select a Procedures node to view stored procedure names and their in and out parameters.
When connected to a database, you can use the IDE to create tables in a database in the following ways:
Use the Create Tables dialog by right-clicking the Tables node in the Services window and choosing Create Table. You can create a table in a graphic form.
Use a table definition to re-create a table.
Run an SQL script in the SQL Editor.
To create a new table using the Create Tables dialog:
In the Services window, expand the node for a connected database.
Right-click the Tables node and choose Create Table.
Enter the table name.
This name must follow conventions for quoted table names in your database. Case is usually significant and you can use spaces. However, it is recommended that table names be entered in upper case and without spaces.
Modify the table with Add Column and Remove.
Specify the column type (required) and column size. Column size is optional for some data types.
Specify additional properties for the column (Optional).
Click OK to create the table.
In the Services window, expand the node for a connected database.
Expand the Tables node, right-click the table you want to re-create and choose Grab Structure.
In the Grab Table dialog, type a name and navigate to where you want to save the file and click Save.
When you click Save, a snapshot of the definition of the selected table is saved in the selected location. You can use the snapshot to re-create the table in the current database or in a different database.
To re-create a table from a table definition:
In the Services window, expand the node for a connected database.
Right-click the Tables node (or the node of any table under the Tables node) and choose Recreate Table.
Locate and select the table definition file you want to use to re-create a table.
Specify the table name in the dialog and click OK to re-create the table. If you want to modify the table definition command, click the Edit table script button and edit the table definition command.
To recreate a table in a different database:
You can save the structure of a table from one database and recreate the table in a different database, even if the two databases have a different SQL syntax. You can thus create database-independent schemas. For example, you can develop a complete application with a database from one vendor and then for deployment, you can transfer the table structures to a different database by a different vendor. You do not need to edit the SQL.
The Database Explorer enables you to create SQL views that are part of the database itself and are available to application programs and other clients of the database.
In the Services window, expand the node for a connected database.
Right-click Views and choose Create View.
In the Create View dialog, type the name of the view in View Name.
In the text area, enter the SQL (SELECT) statement that will define the new view and click OK. The IDE adds a node for the new view ( 
) under the Views node for the database.
Once the view is saved, you can see the data it contains by right-clicking the view in the Services window and choosing View Data. Choose Execute Command to run an SQL command on this view.
When connected to a database, the database tables can be modified directly through the IDE.
In the Services window, expand the node for a connected database ( ).
Expand the Tables node to display the list of tables.
Right-click the table you want to modify and choose Add Column.
Enter the values for the column in the dialog.
Enter the name of the column.
Provide the Type of the column by selecting a type from the Type drop-down menu.
Provide the size of the column in Size.
Provide a scale value for numeric or decimal data type.
Provide a default value for the column in Default.
Select constraints you want for column values (Primary key, and Unique or Null).
If you select Primary key, the column is included in the primary key and in most cases the database server will generate an index for it.
If you want to add the new column to an existing index, select Index and select an index from the drop-down menu.
(Optional) Select Check to enter constraints to be checked while inserting or updating data in that column.
Click OK.
Note:
The ability to add or copy columns depends on the database you use. Your database system might not allow you to alter tables containing data.
When you add a column to a table, the new column appears under the table node ( ) in the Services window. After creating a column, you may need to recapture the database schema to use the new column in your project.
When connected to a database, you can delete data objects from the database schema and remove the corresponding node from the folder.
In the Services window, expand the node for a connected database.
Locate and right-click on the object you wish to delete.
Choose Delete from the pop-up menu or press the Delete key on the keyboard to delete an object.
When a database is connected, you can view and modify the data contained in a table in the built-in graphical representation of the SQL Editor.
In the Services window, expand the node for a connected database ( ).
Expand the Tables or Views node.
Right-click the node of the table or view and choose View Data.
When you choose View Data, an SQL query to select all the data from the table is automatically generated in the upper pane of the SQL Editor. A visual representation of the table and the data it contains is displayed in the lower pane of the SQL Editor.
You can use this GUI representation of the table to add, modify, and delete table data, for example:
Insert new records. To add new records, click the Insert Records button ( 
). This opens the Insert Records dialog window, in which you enter new records. You can click the Show SQL button in the Insert Records dialog window to view the SQL statement(s) that will be applied upon initiating the action. Click OK to add the new records to the database table.
Modify records. To edit existing records, double-click a record in a table cell and modify its content. Modified entries are shown in green. Click the Commit Record button ( 
) to commit changes to the actual database. Similarly, click the Cancel Edits button ( 
) to cancel any edits you have made.
Delete selected records. Select a row in the table and choose the Delete Selected Record button ( 
). You choose multiple rows simultaneously by holding Ctrl.
Delete all records. To delete all records in a table, click the Truncate Table button ( 
).
If the displayed data needs to be resynchronized with the actual database, you can click the Refresh Records ( 
) button.
For more on using the SQL Editor, see Section 23.2.7, "How to Execute SQL Statements."
Tip:
Each column node has the same View Data item in its pop-up menu. This feature is similar to the feature for tables; however, when you view tables, the SQL statement for viewing data fetches only the one column rather than the entire set of columns that the IDE displays when you choose View Data.
You can use the SQL Editor to write and execute SQL statements and SQL scripts within the IDE. The SQL statement or script is executed on the database that is selected in the Connection drop-down list in the toolbar. If the database connection is closed, the IDE opens the connection to the database. In the Connection drop-down list, you can change the database on which you want to run the SQL statement.
You can use the SQL Editor to create, edit and execute any SQL files in your project. To open an SQL file, right-click the SQL file ( 
) in the Projects or Files window and choose Open. You can also double-click the icon to open the file in the SQL Editor.
You can run SQL statements directly from an SQL file, without opening it.
Confirm that the target database server is running.
Open the Favorites window by choosing Window > Favorites.
Right-click inside the Favorites window and choose Add to Favorites. Add the directory that contains the SQL file you want to run.
Right-click the SQL file and choose Run file.
Select a database from the list of registered database connections. Click OK. The SQL script will be executed on the selected database connection.
To create and execute a SQL statement or script:
Expand the Databases node in the Services window.
Right-click the node for the connected database and choose Execute Command from the pop-up menu to open the SQL Editor.
Make sure your current database connection is selected in the Connection list. To see the status of the listed connections, click the Select Connection in Explorer icon ( 
) in the toolbar. When you click Select Connection in Explorer, the selected connection is highlighted in the Services window.
Enter a DDL or DML statement in the SQL Editor window.
Click Run SQL ( 
) in the toolbar or right-click in the SQL Editor and choose Run Statement from the pop-up menu.
Note:
When you execute SQL in the SQL Editor, normally the results tabs from the previous execution are closed. You can change this behavior so that the previous tab remains open. To do this, click Keep Prior Results ( 
) in the tool bar. This setting is saved for all subsequent SQL executions, or until you toggle it off again.
When you execute an SQL statement or script, you see the following:
The status of the SQL execution is displayed in the Output window. Any errors when executing the SQL statement or script are displayed in the Output window.
The output of the statement appears in the results pane in the lower half of the SQL Editor. If the statement has no output, the number of affected rows is displayed in the Output window of the IDE.
Notes:
You can run selected text in the SQL Editor by right-clicking the selected text and choosing Run Selection from the pop-up menu.
You can access the Execute Command pop-up menu item from any subnode of a connected database ( ) in the Services window.
To view the data in a table, right-click a table in the Services window and choose View Data from the pop-up menu.
Click SQL History ( 
) in the toolbar to show the list of all SQL commands that you executed.
The IDE allows you to capture and create a persistent database schema which can then be used in other projects.
To connect to a database to retrieve a schema, the database driver first needs to be added to the IDE. Database drivers can be added in the Services window. For more information, see Section 23.3.1, "How to Add a JDBC Driver."
Make sure the database server is running.
Choose File > New File to open the New File wizard.
Select the Project where the schema should be saved.
Select Persistence in the Categories pane.
Select Database Schema in the File Types pane and click Next.
For File Name, provide a name for the schema.
Click Browse to specify the folder where to save the schema. The default location is src within the specified project. You can choose any other location for the schema file.
Click Next.
Select an existing database connection from the list or select New Database Connection to create a new connection. For more information, see Section 23.3, "Setting up a Database Connection."
Click Next.
You may be prompted by a Connect dialog to supply a username and password to connect to the database, if required. Enter the user name and password in the Basic setting tab and click OK.
When connecting to the database, the schema with the same name as the user name is set as default, but if a schema with that name does not exist, you are automatically switched to the Advanced Tab to select a schema. Choose the correct schema and click OK.
Select the tables and views you want to capture in the Available Tables and Views list on the left, then click Add to add them to your list of Selected Tables and Views on the right. Alternatively, you can click Add All to specify all tables and views.
If you specify a table that references a table that you have not chosen to capture, both tables are captured.
Click Finish to complete capturing the schema.
When you modify your database, such as by adding or deleting fields or changing relationships, you may need to recapture the database schema so that the changes are also available to your project.
To recapture the database schema from the database:
Make sure the database server is running.
Locate the database schema node ( 
). The database schema can be found in the following locations:
in the Source Packages node in the Projects window
in the specified location within the project structure in the Files window
Right-click the database schema node and choose Recapture Schema from Database.
Enter the username and password, if necessary.
Click OK.
The Recapturing Database Schema progress box opens and displays the recapturing progress, including the table and view information it is capturing. After recapturing the database schema, you can:
Explore the structure of the new database schema by expanding the schema in the Projects or Files windows. For more information, see Section 23.2.1, "How to Browse Database Structures."
Explore the recaptured schema and view the data in the Services window. For more information, see Section 23.2.6, "How to View and Modify Data in a Database."
A database connection is represented by a database connection node ( ) under the Databases node in the Services window. To connect to a database, you need to provide a valid JDBC driver for your database in the NetBeans IDE.
When you create a database connection, you supply the details needed to connect to a specific database. These details include the location of the database, which driver to use, and the username and password information.
Ensure that your database is running.
Right-click the Databases node and choose New Connection. Alternatively, you can initiate a connection directly from the list of registered drivers. If the database driver is already listed under the Drivers node, right-click this driver's instance node and choose Connect Using.
In the first step of the New Connection wizard, choose the driver for the database you are connecting to from the Driver drop-down list and confirm that the correct driver's file is provided in Driver File. Click Next.
You are passing the same steps when registering a new driver in the IDE. For more information, see Section 23.3.1, "How to Add a JDBC Driver."
In the Customize Connection dialog of the New Connection wizard, confirm that the correct driver is selected for your database.
Depending on what database you are connecting to, provide the connection details for this database, such as database location (hostname or IP address), port, username and password, database name, service name, etc. For more information, see:
To help you get started, the IDE fills the default details for some databases for you. If in doubt, refer to the documentation for the driver that you are using.
(Optional) Click Remember Password to have the IDE remember your password. If selected, you will not be prompted for the user name and password on subsequent connections to the database.
To set the Remember Password property to False, right-click the database connection node in the Services window and choose Properties. Modify the Remember Password property. Note that you can do this only when the connection you are modifying is in the disconnected state. When the connection is enabled, you cannot change this property.
Click Test Connection to test the connection to the database.
To connect to a database from within the IDE, you must have an appropriate JDBC driver for this database and register this driver with the IDE. Registered drivers (
) are listed under Drivers under the Databases node in the Services window.
The drivers are of the following kind:
JDBC drivers that are registered and bundled with the NetBeans IDE. These are drivers for MySQL and PostgreSQL. You can establish a connection to these databases right away, without downloading them separately.
JDBC drivers that are registered with the IDE by default, but not bundled with the IDE. These are drivers for Oracle Database and Java DB. For example, the Java DB drivers are located in the Java DB installation directory and NetBeans recognizes them automatically at their default location. As for the drivers for Oracle Database, you need to download them manually from the Oracle website.
If you need another JDBC driver that is different from those mentioned above, you need to manually add it to the IDE.
Tips:
Adding a new driver just creates a template for new connections. The IDE does not actually try to use that driver or check it to ensure it is correct until a connection is made using the driver.
You can add the driver directly at the first step when setting up a connection. If you want just to add a driver, without connecting to the database, right-click the Drivers node and choose New Driver. For more information, see Section 23.3.3, "How to Connect to Oracle Database"
An uninstalled driver node ( 
) is displayed in the Services window if a driver is registered with the IDE but the archive JAR file for the driver is not currently on the IDE's class path. In this case, you should remove the driver or modify the driver's properties to specify the correct location of the driver JAR file.
Right-click the driver node in the Services window and choose Delete.
To modify driver's properties:
Right-click the driver's node and choose Customize.
The Java DB database is a distribution of the open source Apache Derby database. Java DB is a fully transactional, secure, standards-based database server, written fully in the Java programming language, and fully supports SQL, JDBC API, and Java EE technology. The Java DB database server is bundled with the GlassFish application server.
If you install the GlassFish application server during the IDE installation, the Java DB database server is automatically registered in the IDE.
To enable the Java DB database server in the IDE, do one of the following:
Register an instance of the GlassFish application server. This registers the installation of the Java DB database server that is packaged with the application server. The connection to the sample Java DB database is automatically displayed in the Services window. The default user name and password for the sample database is app. For more information, see Section 22.3.1, "How to Register a Server Instance."
Register an existing Java DB or Apache Derby installation. If you already have one of these database servers installed on your computer, you can register the database with the IDE by doing the following:
In the Services window, right-click the Java DB node and choose Properties.
In the dialog, set the location of the Java DB installation directory.
Set the folder where your Java DB databases are stored in the Database Location property and click OK.
Start the Java DB server.
Depending on your installation, you can start the server in the Services window by right-clicking the Java DB node and choosing Start Server. In some cases you might need to start the server from the command line prompt.
Once your Java DB database is registered with the IDE, the Java DB Database menu item appears under the Databases Node in the Services window. This menu item enables you to easily start and stop the Java DB database server and to create a new database.
To create a new Java DB database:
In the Services window, right-click the Java DB node and choose Create Database.
In the Create Java DB Database dialog, enter a name for the database, a user name, and a password.
By default, the IDE creates the database in the .netbeans-derby folder of your home directory. To change the default location, click Properties in the Create Java DB Database dialog, or in the Services window, right-click the Java DB node and choose Properties. Type the new database location in the appropriate field.
To connect to an existing Java DB database:
In the Services window, right-click the Databases node and choose New Connection.
In the Locate Driver step of the New Connection wizard, choose one of the following Java DB drivers from the drop-down menu:
Java DB (Embedded). Use this driver when you are sure your application will access the database from a single JVM. A typical example of this scenario is a single-user Swing JDBC application using a Java DB database as a data store.
Java DB (Network). Use this driver when you need to connect to the database from multiple JVMs. An example of this scenario is when you need to connect from the IDE and from a Java EE application on the application server. Using the network driver, you can also access the database from remote computers.
In the Customize Connection dialog of the New Connection wizard, enter the database name to which you want to connect.
Provide the database user name and password.
(Optional) Click Test Connection to see if the connection can be established.
Click OK. The IDE displays the connection to the database under the Databases node in the Services window.
Notes:
Selecting Remember Password will store the password for the current IDE session. If left unselected, a dialog appears prompting you for the password every time you connect to the database.
The URL for the sample database is jdbc:derby://localhost:1527/sample.
The default location for the sample database is in the .netbeans-derby directory in your home directory.
To change the default location for the Java DB database directory, in the Services window, right-click the Java DB node in the Services window and choose Properties. Enter the new location.
You can establish a connection to Oracle Database from the NetBeans IDE. For example, you can use Oracle Database 11g Express Edition (Oracle Database XE), a lightweight database that is free to develop, deploy, and distribute.
Before connecting to Oracle Database, ensure that you have downloaded the JDBC driver for the database. The recommended driver is ojdbc6.jar. For more information about Oracle JDBC drivers, see the JDBC page at the Oracle Technology Network (http://www.oracle.com/technetwork/database/features/jdbc).
To connect to the Oracle Database XE from the IDE:
In the Services window, right-click the Databases node and choose New Connection.
In the Locate Driver step of the New Connection wizard, choose one of the following drivers for Oracle Database:
Oracle Thin. Oracle's JDBC Thin driver uses Java sockets to connect directly to Oracle Database. Because it is written in Java, this driver is platform independent and can also run from a Web Browser (applets).
Oracle OCI. Oracle's JDBC OCI drivers use Oracle Call Interface (OCI) and native libraries to interact with an Oracle database. The required libraries are part of the Oracle Database Instant Client, which needs to be installed and configured on your system.
Click Add to specify the location of the ojdbc6.jar file. This file contains packages for both types of Oracle DB drivers. However, if you want to use the OCI driver, you also need to have Oracle Instant Client installed on your machine. Click Next.
In the Customize Connection dialog of the New Connection wizard, choose the driver name and how you want to specify the database: by a SID name (Oracle System ID), Service Name, or a TNS name.
Then, enter the hostname or IP address to which you want to connect. If you are connecting to a locally installed database instance, enter localhost or 127.0.0.1.
Provide the port number. The default port is 1521.
Depending on your choice of the driver, enter either the SID name, Service, or TNS name. For example, the default SID for Oracle Database XE is XE.
Provide the database user name and password.
(Optional) Click Test Connection to see if the connection can be established.
Click Next and on the Customize Connection page choose the schema you want to use.
(Optional) Click Next and change the connection name. The default name is similar to jdbc:oracle:thin@host:port:SID [connection on schema]
Click Finish. The IDE displays the connection to the database under the Databases node in the Services window.
For more information, see Connecting to Oracle Database from NetBeans IDE available on the NetBeans site at https://netbeans.org/kb/docs/ide/oracle-db.html.
Notes:
SID, or System ID, is a unique name of an Oracle database instance. For example, the default SID for Oracle Database XE is XE.
You can find the SID in the tnsnames.ora file, which is a configuration file that specifies various database properties used to connect to the database. The default location of tnsnames.ora is %ORACLE_HOME%\NETWORK\ADMIN directory.
You can also find the SID and Service Name values through the web-based database management interface. For example, go to Database Home Page for Oracle Database XE, choose Administration > About Database > Parameters. Find the values of the following properties: db_name, which is the database SID; service_names is the database Service Name. You must have database administrator rights to access this information.
The sample URL to the Oracle Database XE is jdbc:oracle:thin:@//localhost:1521/XE
The IDE comes with MySQL database support. In the Services window, a MySQL node appears under the Databases node when the IDE detects a running MySQL server on the local machine at the default port. The node is also registered automatically under the following conditions:
If the IDE detects a recognized installation of MySQL. This is currently MAMP on Macintosh and XAMPP on Windows.
If a MySQL connection is registered, the IDE auto-registers the MySQL node using the properties defined on the connection.
If the user creates a new connection to MySQL, the IDE auto-registers the MySQL node at that time.
When you right-click the MySQL node, you see the following options:
Create Database Brings up a dialog for creating a database, including sample databases.
Note:
When using MySQL 5.0 with Windows, some foreign characters may not be used when creating a database.
Start Starts the database server. If the user chooses this action and no start or stop command is set, then they are prompted to set the command.
Stop Stops the database server. If the user chooses this action and no start or stop command is set, then they are prompted to set the command.
Connect Connects the IDE to the MySQL database server.
Disconnect Disconnects the IDE from the MySQL database server.
Delete Removes the MySQL node. It also marks the node as removed, so that next time the user starts up the IDE will not auto-register the node again. The user will need to manually register the node using Databases > Register MySQL Server.
Refresh Refreshes the list of databases based on what's currently on the server.
Run Administration Tool Brings up the administration tool that is registered with the runtime. The provider either invokes the admin tool as a separate system process, or if it is a web-based tool, brings up the provided URL in the default browser. If the administer command has not been set, then when the user picks the action, they are prompted to set the path to the command.
Properties Brings up a dialog that allows the user to configure important settings for the server. There are two tabs, one for basic settings and one for setting command paths to administrative commands (start, stop, administer)
When you expand the MySQL node, MySQL databases detected by the IDE appear under the node. By right-clicking a database, you can either connect to or delete the database.
If the IDE does not auto-detect and register the MySQL node, you can manually register it. In this case, the Databases node has a new subnode designed to register the server. When you right-click the Databases node, you see the following options:
New Connection When you choose New Connection, the New Database Connection dialog appears.
Register MySQL Database If the IDE does not auto-detect and register the MySQL node, the user can manually register it. There will be a new action under Databases to register the server
Enable Debug Once debug mode is enabled, the IDE sends SQL statements to the Output window, where you can view the log of statements. Debug mode does not list all SQL statements, only those which you cannot see. For example, SQL queries for viewing data are not logged.
Disable Debug This terminates the debugging function.
Enable debug mode to see SQL statements that are being sent to the database. By default, debug mode is disabled.
Choose Window > Services from the main menu to open the Services window.
In the Services window, right-click the Databases node and choose Enable Debug.
Once debug mode is enabled, the IDE sends SQL statements to the Output window, where you can view the log of statements. Debug mode does not list all SQL statements, only those which you cannot see. For example, SQL queries for viewing data are not logged.
|
 Copyright © 2013, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
