在NetBeans中使用MySQL创建简单Web应用程序(0)

Connecting to a MySQL Database in NetBeans IDE

Contributed and maintained byTroy Giunipero .

This document demonstrates how to configure MySQL on your system and set up a connection to the database from NetBeans IDE. Once connected, you can begin working with MySQL in the IDE's Database explorer by creating new tables, populating tables with data, and running SQL queries on database structures and content. This tutorial is designed for beginners with a basic understanding of database management and application development, who want to apply their knowledge in working with MySQL in NetBeans IDE.

MySQL is a popular Open Source database management system commonly used in web applications due to its speed, flexibility and reliability. MySQL employs SQL, or Structured Query Language, for accessing and processing data contained in databases.

Expected duration: 25 minutes

The following topics are covered below:

• Getting the Software
• Installing and Configuring the Database
• Creating a Database Instance
• Registering the Database in NetBeans IDE
• Creating Database Tables
• Working with Table Data
• Running an SQL Script
• Next Steps

Getting the Software

Before you begin, make sure you have the following software installed on your computer:

1. NetBeans IDE 5.5 (download)
2. Java SE Development Kit (JDK™) version 5.0 or higher (download)
3. MySQL database (download)
4. JDBC Driver for MySQL (download)

Note: As an alternative to downloading the IDE and JDK separately, consider downloading the Java SE Development Kit 6u1 with NetBeans IDE 5.5 Bundle.

Installing and Configuring the Database

If you already have the MySQL database set up and running on your machine, please skip ahead to Registering the Database in NetBeans IDE. By way of example, this tutorial demonstrates how to run the MySQL Community Server on Windows XP. To get the MySQL server running:

1. Run the self-extracting file. The MySQL Setup Wizard opens to guide you through the installation process. Accept all default settings.
2. Upon completing installation, allow the MySQL wizard to immediately configure the server by making sure the Configure the MySQL Server Now checkbox is selected. This will allow an instance of the server to be created, which will run as a Windows service on your machine.
3. In the MySQL Server Instance Configuration wizard, select Standard Configuration. Click Next. When you arrive at the step allowing you to set Windows options, select the Include Bin Directory in Windows PATH checkbox. This will later allow you to perform a simple check to make sure the MySQL service is up and running:
   
   
4. Set the root password to: nbuser. Finally, click Execute to allow the wizard to generate the server instance on your computer. If you encounter any problems, refer to the MySQL Reference Manual included in your installation or the online documentation.

Before continuing further, it is important to understand the components found in MySQL's root directory:

• The bin subdirectory contains the scripts for executing utilities and setting up the environment.
• The data subdirectory contains all database instances, including their data.
• The Docs subdirectory contains the MySQL Reference Manual.
• The share subdirectory contains localization files, including character sets and language packages.
• The my.ini file is the configuration file that was generated by the Configuration wizard, and contains information such as the port being listened on, path to installation directory, path to database root, and default character set.

Creating a Database Instance

Now that the MySQL database server is installed and configured, you can create a database instance which you will later connect to in NetBeans IDE. You need to have a database instance already created in order to connect to the MySQL database server in NetBeans IDE. To create a new database instance:

1. Open up the MySQL Command Line Client from your Start > All Programs > MySQL menu. The Command Line client displays prompting you to enter your root password.
2. Enter the password specified above (nbuser), and a brief welcome message displays, introducing you to the MySQL monitor.
3. Type create database MyNewDatabase; at the prompt. You should receive a Query OK message, indicating that the new database has been created.
4. To verify that MyNewDatabase has indeed been created, type show databases; at the prompt. A list is displayed, showing all databases maintained by the server. Note that MyNewDatabase is listed:
   
   

Registering the Database in NetBeans IDE

Now that you have the database server installed and configured, and have created a new database, you can register the MySQL server in NetBeans IDE. Begin by examining the functionality offered by the Database explorer located in the IDE's Runtime window (Ctrl+5). The Database explorer is represented by the Databases node (). From this interface you can connect to databases, view current connections, add database drivers, as well as create, browse or edit database structures.

You can use the Database explorer to register MySQL in the IDE. There are two simple steps that need to be performed:

1. Adding the Driver to the IDE
2. Creating a Database Connection

Adding the Driver to the IDE

In order to allow NetBeans IDE to communicate with a database, you need to employ a Java-based driver. Generally speaking, drivers in NetBeans IDE use the JDBC (Java Database Connectivity) API to communicate with databases supporting SQL. The JDBC API is contained in the java.sql Java package. A driver therefore serves as an interface that converts JDBC calls directly or indirectly into a specific database protocol.

In this tutorial, you are using the MySQL Connector/J driver, which is a pure Java implementation of the JDBC API, and communicates directly with the MySQL server using the MySQL protocol. To add the driver to the IDE:

1. If you have just downloaded the driver, first extract it to a location on your computer. Set the root directory to: C:/mysql-connector-java-5.0.5.
2. In the IDE, in the Database explorer from the Runtime window (Ctrl+5) expand the Databases node and right-click the Drivers node. Choose New Driver. The New JDBC Driver dialog opens.
3. Click the Add button in the top right corner. Navigate to the driver's root directory and select the driver's jar file (e.g. mysql-connector-java-5.0.5-bin.jar). Click Open. The New JDBC Driver dialog should look like this:
   
   
4. Click OK. In the Runtime window expand the Databases > Drivers nodes and note that a new MySQL driver node is displayed:
   
   

Note: While you just made the database driver available to the IDE, you have not yet made it available to any specific application. At this stage, you can use the IDE to access and modify the database, but cannot do so from an application yet. This is covered in the follow-up tutorial Creating a Simple Web Application in NetBeans IDE using MySQL.

Creating a Database Connection

You can now set up a connection to the database through the appropriate driver. Make sure your database service is running prior to continuing. You can do so by performing the following simple test:

1. Open a Windows command shell (from the Start menu, choose Run and type cmd in the text field). A command line window displays.
2. At the prompt, type sc query MySQL and press Enter. The Windows sc command allows you to query the state of services. The output should indicate that the current state of the MySQL service is RUNNING:
   
   
   
   If the service is STOPPED, you can start it by either typing sc start MySQL at the command line prompt, or using the Windows Services GUI (Start > Control Panel > Administrative Tools > Services).

Now, in NetBeans IDE, create a connection to MyNewDatabase:

1. In the Runtime window (Ctrl+5) choose Connect Using from the right-click menu of the driver you just added. The New Database Connection dialog opens.
2. In the Basic Setting tab, enter the Database's URL in the corresponding text field. The URL is used to identify the type and location of a database server. In this example, you need to specify the host name (i.e. the location of the server), the port number on which the database communicates, and the name of the database instance being used. In this case you can enter: jdbc:mysql://localhost:3306/MyNewDatabase.
3. For User Name and Password, enter root, and nbuser respectively:
   
   
4. Click OK, then click OK again to exit the dialog. A new Connection node displays in the Runtime window's Database explorer under the Databases node:
   
   

You are now connected to MyNewDatabase in the IDE. Note that the new connection node icon appears whole () when you are connected to a database. Likewise, it appears broken () when there is no connection.

At later stages, when working with databases through the Database explorer, you may need to manually connect to a database. You can do so by right-clicking the broken database connection node and choosing Connect.

Creating Database Tables

Now that you have connected to the database, you can begin exploring how to create tables, populate them with data, and modify data maintained in tables. This allows you to take a closer look at the functionality offered by the Database explorer, as well as NetBeans IDE's support for SQL files. As an example, you can prepare the database for use in the web application developed in the follow-up tutorial Creating a Simple Web Application in NetBeans IDE using MySQL.

The MyNewDatabase database that you are using is currently empty. In NetBeans IDE it is possible to add a database table by either using the Create Table dialog, or by inputting an SQL query and running it directly from the SQL editor. Here you can explore both methods:

1. Using the Create Table Dialog
2. Using the SQL Editor

Using the Create Table Dialog

1. In the Database explorer, expand the MyNewDatabase connection node () and note that there are three subfolders: Tables, Views and Procedures. Right-click the Tables node and choose Create Table. The Create Table dialog opens.
2. In the Table Name text field, type subjects.
3. In the first row displayed, select the Key check box. You are specifying the primary key for your table. All tables found in relational databases must contain a primary key. Note that when you select the Key check box, the Index and Unique check boxes are also automatically selected and the Null check box is deselected. This is because primary keys are used to identify a unique row in the database, and by default form the table index. Because all rows need to be identified, primary keys cannot contain a Null value.
4. For Column Name, enter id. For Data Type, select SMALLINT from the drop-down list. Then click the Add Column button.
5. Repeat this procedure by specifying all remaining fields, as shown in the table below:
   
   Key Index Null Unique Column name Data type Size [checked] [checked]   [checked] id SMALLINT 0     [checked]   name VARCHAR 50     [checked]   description VARCHAR 500     [checked]   counselors_idfk SMALLINT 0
6. You are creating a table named subjects that will hold the following data for each record:
   
   
   • Name
   • Description
   • Counselor ID
   
   When you are sure that your Create Table dialog contains the same specifications as those shown above, click OK. The IDE generates the subjects table in the database, and you see a new subjects table node () display under Tables in the Database explorer. Beneath the table node there are the columns (fields) you created, starting with the primary key ( ):
   
   
   
   

Using the SQL Editor

1. In the Database explorer, right-click the Tables node beneath the MyNewDatabase connection node and choose Execute Command. A blank canvas opens in the SQL Editor in the main window.
2. In the SQL Editor, type in the following query. This is a table definition for the counselors table you are about to create:

   CREATE TABLE counselors (    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,    first_name VARCHAR (50),    nick_name VARCHAR (50),    last_name VARCHAR (50),    telephone VARCHAR (25),    email VARCHAR (50),    member_since DATE DEFAULT '0000-00-00',    PRIMARY KEY (id));                    

   Note: Queries formed in the SQL Editor are parsed in Structured Query Language (SQL). SQL adheres to strict syntax rules which you should be familiar with when working in the IDE's Editor. Upon running a query, feedback from the SQL engine is generated in the Output window indicating whether execution was successful or not.
   
   
3. Click the Run SQL () button in the task bar at the top (or, press Ctrl+Shift+E to execute the query). You should receive the following message in the Output window:
   
   
   
   
4. To verify changes, right-click the Tables node in the Database explorer and choose Refresh. The Refresh option updates the Database explorer's UI component to the current status of the specified database. This step is necessary when running queries from the SQL Editor in NetBeans IDE. Note that the new counselors table node () now displays under Tables in the Database explorer.

Working with Table Data

In order to work with table data, you can make use of the SQL Editor in NetBeans IDE. By running SQL queries on a database, you can add, modify and delete data maintained in database structures. To add a new record (row) to the counselors table, do the following:

1. Choose Execute Command from the counselors table node in the Database explorer. A blank canvas opens in the SQL Editor in the main window.
2. In the SQL Editor, type in the following query:

   INSERT INTO counselors    VALUES (1, 'Ricky', '"The Dragon"', 'Steamboat','334 612-5678', '[e-mail]', '1996-01-01')

3. Click the Run SQL () button in the task bar at the top, or press Ctrl+Shift+E to execute the query. You should receive a message in the Output window indicating that the query was executed successfully.
4. To verify that the new record has been added to the counselors table, in the Database explorer, right-click the counselors table node and choose View Data. A new SQL Editor pane opens in the main window. When you choose View Data, a query to select all the data from the table is automatically generated in the upper region of the SQL Editor. The results of the statement are displayed in a table view in the lower region. In this example, the counselors table displays. Note that a new row has been added with the data you just supplied from the SQL query:
   
   
   
   

Running an SQL Script

Another way to manage table data in NetBeans IDE is by running an external SQL script directly in the IDE. If you have created an SQL script elsewhere, you can simply open it in NetBeans IDE and run it in the SQL Editor.

For demonstrative purposes, download ifpwafcad.sql and save it to a location on your computer. This script creates two tables similar to what you just created above (counselors and subjects), and immediately populates them with data. To run this script on MyNewDatabase:

1. Choose File > Open File (Ctrl+O) from the IDE's main menu. In the file browser navigate to the location where you previously saved ifpwafcad.sql and click Open. The script automatically opens in the SQL Editor.
2. Make sure your connection to MyNewDatabase is selected from the Connection drop-down box in the tool bar at the top of the Editor:
   
   
   
   
3. Click the Run SQL () button in the SQL Editor's task bar. The script is executed against the selected database, and any feedback is generated in the Output window.
4. To verify changes, right-click the MyNewDatabase connection node in the Runtime window and choose Refresh. The Refresh option updates the Database explorer's UI component to the current status of the specified database. Note that the two new tables from the SQL script now display as a table nodes under MyNewDatabase in the Database explorer.
5. To view the data contained in the new tables, choose View Data from the right-click menu of a selected table node. In this manner, you can compare the tabular data with the data contained in the SQL script to see that they match.