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

From Script | Spoken-Tutorial
Revision as of 18:11, 27 November 2012 by Chandrika (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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
  1. Configuring MySQL server properties
  2. Starting the MySQL server
  3. Creating and connecting to the database


Lesson Outline (cont'd)

Slide 3

4. Creating database tables,

under which we will explore two methods:

  1. using the sql editor,
  2. using the create table dialogue

and, finally,

  1. 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 dialogue box.

In the MySQL Server Properties dialogue 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 dialogue 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 dialogue box >> type >> mynewdatabase In the Create Database dialogue 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 dialogue opens.

In Create Table dialogue >> in the name field >> type >> Subject In the Table name text field, type Subject
In the RHS of the Create Table dialogue >> click on >> Add Column Click Add Column
In Add Column dialogue >> in the Name field >> type >> id In the Add Column dialogue, 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 dialogue 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,
  1. configure MySQL on your computer
  2. set up a connection to the database server from the IDE
  3. create, delete, modify data and
  4. 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

Retrieved from "https://script.spoken-tutorial.org/index.php?title=Netbeans/C3/Connecting-to-a-MySQL-Database/English&oldid=227"