Netbeans/C3/Connecting-to-a-MySQL-Database/English

Title of script: Connecting to a MySQL Database

Author: Sindhu

Keywords: mysql, database, netbeans, sql, query, video tutorial

Resources for "Connecting to a MySQL Database"

Visual Cue	Narration
Welcome Slide 1	Hello everyone, Welcome to the tutorial on 'Connecting to a MySQL Database'
Lesson Outline Slide 2	In this tutorial, we will look at Configuring MySQL server properties Starting the MySQL server Creating and connecting to the database
Lesson Outline (cont'd) Slide 3	4. Creating database tables, under which we will explore two methods: using the sql editor, using the create table dialog and, finally, 5. Running an SQL script
System Setup Slide 4	For this demonstration, I am using the Linux Operating System Ubuntu v12.04, and Netbeans IDE v7.1.1 You also need, the Java Development Kit (JDK) v6 and MySQL database server
Prerequisites Slide 5 To know more, watch PHPandMySQL spoken tutorials on http://spoken-tutorial.org	To learn this tutorial, basic understanding of database management is necessary. To know more, watch PHPandMySQL spoken tutorials on the link shown. Other standard programming terminologies have been used in this tutorial.
Introduction Slide 6 We will work with MySQL in the IDE's Database Explorer”	This tutorial demonstrates how to setup a connection to a MySQL database from the Netbeans IDE. Once connected, we will work with MySQL in the IDE's Database Explorer. Let us switch to the IDE now.
Switch to Netbeans IDE Focus >> Services window on LHS of IDE	Netbeans IDE comes bundled with support for the MySQL RDBMS.
Narration Only	Before you access MySQL database server in Netbeans, you must configure the MySQL server properties.
Services window >> right-click on Databases node >> choose Register MySQL Server	Right-click the Databases node in the Services window. Choose Register MySQL Server to open the MySQL server properties dialog box.
In the MySQL Server Properties dialog box >> in the Basic Properties tab >> point to localhost (server host name) and 3306 (server port number)	Confirm that the server host name and the port are correct. Notice that the IDE enters localhost as the default server host name. 3306 is the default server port number.
Point to >> Administrator username	Enter the Administrator username (if not displayed) On my system, the Administrator username is root
Point to >> Administrator password	Enter the Administrator password. On my system, the password is blank.
Click Admin Properties tab on top of MySQL Server Properties dialog box	Click the Admin Properties tab at the top of the dialog box. This allows you to enter information for controlling the MySQL server.
Point to >> Path/URL to admin tool: >> type >> /usr/bin/mysqladmin	In the Path/URL to admin tool: field, type or browse to the location of your MySQL Administration application. On my system, the location to the tool is /usr/bin/mysqladmin
Point to Arguments field >> this can be left blank	Type any arguments for the admin tool in the Arguments field. This can also be left blank.
Point to >> Path to start command >> type >> /usr/bin/mysqld_safe	In the Path to start command: field type or browse to the location of the MySQL start command. On my system it is /usr/bin/mysqld_safe
Point to Arguments: >> type -u root start	Type any arguments for the start command in the Arguments field. Here, I will type -u root start
Point to >> Path to stop command >> type >> /usr/bin/mysqladmin	In the Path to stop command: type or browse to the location of the MySQL stop command. This is usually the path to mysqladmin in the bin folder of the MySQL installation directory. On my system this is /usr/bin/mysqladmin
Point to Arguments: >> type -u root stop	If the command is mysqladmin, in the Arguments field, type -u root stop.
Show Admin Properties >> Click OK	When finished, the Admin Properties tab should resemble what is shown on the screen. Click OK.
Starting the MySQL Server Point to Services window >> Databases >> MySQL server at localhost:3306 [root]	First ensure that the MySQL database server is running on your machine. The MySQL server node in the Service window indicates whether the MySQL database server is connected.
Right-click on >> MySQL server at localhost:3306 [root] >> click >> Connect	After making sure that it is running, right-click the Databases >> MySQL server node and choose Connect.
Expand MySQL server node >> point to available databases	When expanded, the MySQL server node displays all the available MySQL databases.
Creating and Connecting to the Database Instance Narration Only	A common way of interacting with databases is through an SQL Editor. Netbeans has a built-in SQL Editor for this purpose.
Narration Only	You can access this by right-clicking on the connection node.
Narration Only	Let us now create a new database instance using the SQL Editor.
In the Services window >> right-click on >> MySQL server at localhost:3306 [root] >> choose >> Create Database	In the Services window, right-click the MySQL server node and choose Create Database.
In the Create Database dialog box >> type >> mynewdatabase	In the Create Database dialog box, type the name of the new database. I will name this mynewdatabase.
Point to >> Grant Full Access To: checkbox >> select checkbox >> drop-down list	You can also grant full access to a given user. By default, only the admin user has the permissions to perform certain commands. The drop-down list allows you to assign these permissions to a specified user.
Narration Only	It is a good practice to grant users most permissions, except to drop tables. And allow users to modify only those databases that are created by their application.
Point to >> Grant Full Access To: checkbox >> unselect checkbox >> Click OK	But for now, we will leave the checkbox unselected. Click OK.
Creating Database Tables Narration Only	Let us now create tables, populate them with data, and modify the data maintained in tables.
Narration Only	mynewdatabase is currently empty. Let us explore the first method to input data in the tables.
Using the SQL Editor Expand >> jdbc:mysql connection node >> expand >> mynewdatabase connection node >> point to >> Tables, View and Procedures sub folders	In the Database explorer, expand the mynewdatabase connection node. There are three sub folders: Tables, Views and Procedures.
Right-click on Tables folder >> choose >> Execute Command	Right-click the Tables folder and choose Execute Command.
Focus on >> SQL Command 1 canvas in workspace	A blank canvas opens in the SQL Editor in the main window.
Focus on >> SQL Editor >> type sql query >> show complete code	Let us type a simple query in this SQL editor. CREATE TABLE Counselor ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, firstname VARCHAR (50), nickname VARCHAR (50), lastname VARCHAR (50), telephone VARCHAR (25), email VARCHAR (50), memberSince DATE DEFAULT '0000-00-00', PRIMARY KEY (id) ); I have now typed a simple query in the SQL editor.
Point to sql query	This is a table definition for the Counselor table we are about to create.
From the task bar on top >> focus on Run SQL icon	To execute this query, either right-click the Run SQL icon in the task bar at the top.
Right-click in SQL editor >> click on >> Run Statement	Or right-click within the SQL Editor and choose Run Statement.
Point to Output window	The IDE generates the Counselor table in the database. You can see this message in the Output window, which says that the command was executed successfully,
Expand mynewdatabase node >> Right-click on >> Tables node >> click on >> Refresh	To verify these changes, right-click the Tables node in the Database Explorer. Choose Refresh.
Narration Only	This updates the current status of the specified database.
Expand Tables >> point to Counselor table	The new Counselor table now displays under the Tables option.
Expand Counselor table >> point to columns created	If you expand the table node, you can see the columns that you created.
Narration Only	Let us now explore the next method to input data in the tables, i.e. Using the Create Table Dialog
In the Database Explorer >> expand >> mynewdatabase >> right-click on >> Tables >> click on Create Table	In the Database Explorer, right-click the Tables node, and choose Create Table. The Create Table dialog opens.
In Create Table dialog >> in the name field >> type >> Subject	In the Table name text field, type Subject
In the RHS of the Create Table dialog >> click on >> Add Column	Click Add Column
In Add Column dialog >> in the Name field >> type >> id	In the Add Column dialog, type id in the Name field.
In the Type drop-down list >> choose >> SMALLINT	Choose SMALLINT for data-type from the Type drop-down menu.
Select the Primary Key check box	Select the Primary Key checkbox in the Add Column dialog box. This is to specify the primary key for your table.
Point to >> Index and Unique checkboxes >> Click OK	Note that when you select the Key check box, the Index and Unique check boxes are also automatically selected; Also the Null check box is deselected. This is because primary keys are used to identify a unique row in the database. Click OK.
Show the other added columns	Repeat this procedure to add the remaining columns, as shown on the screen.
Point to completed table >> the >> click OK	We have now created a table named Subject that will hold data for Name, Description, and Counselor ID Click OK.
Working with table data Narration Only	By running SQL queries on a database, we can add, modify and delete data maintained in database structures. Let us add a new record to the Counselor table.
Right-click on Tables >> Click on Execute Command	Choose Execute Command from the Tables' node context menu. A new SQL Editor opens in the main window.
In the SQL editor >> type >> the query being shown	In the SQL Editor, let us type a simple query: INSERT INTO Counselor VALUES (1, 'David', '”Dave”', 'Thomas', '333 612-5678', 'dave_thomas@ifpwadcad.com', '1996-01-01')
Right-click in source editor >> click Run Statement	To execute this query, right-click within the source editor, and choose Run Statement
Right-click on Counselor table >> click View Data	Let us now verify if the new record has been added to the table. Right-click the Counselor table, and choose View Data.
Focus on the new SQL editor in main window >> highlight the query	A new SQL Editor opens in the main window. A query to select all data from the table is automatically generated.
Show the table below the workspace	The results of this statement are displayed in a table view below the workspace.
Point to the new row which has just been added	Note that a new row has been added with the data we just supplied.
Running an SQL Script Narration Only	We can also run an external SQL script directly in the IDE.
Show SQL query in gedit	I have a SQL query here for demonstrative purposes.
Point to Counselor and Subject tables	This script creates two tables similar to the ones we have just created. i.e. Counselor and Subject
Narration Only	Because the script overwrites these tables, we will delete these two tables if they already exist.
Switch to the IDE >> right-click on Counselor table >> click on Delete	To delete tables, right-click on the Counselor table and choose Delete.
Click Yes	Click Yes in the Confirm Object Deletion dialog box.
Delete the Subject table	Repeat the same for the Subject table
In the IDE >> go to File >> Open File >> browse to, and open >> ifpwafcad.sql	Now, open the existing SQL query file from your system. From the File menu, choose Open File. Browse to the location containing this file.
Focus on the SQL editor with ifpwafcad.sql	The script automatically opens in the SQL editor.
Point to connection drop down list >> select and click on >> jdbc:mysql://localhost:3306/mynewdatabase	Make sure the connection to mynewdatabase is selected. Check this from the connection drop-down in the toolbar at the top of the editor.
Click >> Run SQL button >> from task bar	Click the Run SQL button in the task bar. And the script is executed against the selected database.
Right-click on >> mynewdatabase >> click Refresh	Right-click the mynewdatabase connection node and choose Refresh. This updates the database component to the current status of the specified database.
For ex: Right-click on >> Counselor table >> click on >> View Data	Right-click on any of these tables now and choose View Data. And below the workspace, you can see the data contained in the new tables.
Slide 7 & 8 Summary	In this tutorial you learnt to, configure MySQL on your computer set up a connection to the database server from the IDE create, delete, modify data and run SQL queries
Slide 9 Assignment	As an assignment, Create another database instance with tables Populate these tables with necessary data to maintain your personal book library Run these SQL statements to view data
Show solved assignment	I have created a similar database which maintains details of my personal movie library. Your assignment should resemble this.
Slide 10 About the Spoken Tutorial Project	Watch the video at the link shown on the screen. It summarizes the Spoken Tutorial project. If you do not have good bandwidth, you can download and watch it.
Slide 11 Spoken Tutorial Workshops	The Spoken Tutorial Project Team Conducts workshops using Spoken Tutorials Gives certificates to those who pass an online test For more details, please write to contact@spoken-tutorial.org
Slide 12 Acknowledgements	Spoken Tutorial Project is a part of the Talk to a Teacher Project. It is supported by the National Mission on Education through ICT, MHRD, Government of India. More information on this Mission is available at the link provided here.
Slide 13 About the Contributor	This tutorial has been contributed by IT for Change. Thank you

Contributors and Content Editors

Chandrika, PoojaMoolya