Hdip Software Project

MySQL and Database Connectivity through

Java

Dr Martina Naughton
Room: A1.15
E-mail: [email protected]
 Databases Topics covered this
week
 Aims:
 MySQL - Intro to the SQL query language

 Jdbc connectivity - how to manipulate your

database through Java (JavaBeans/JSP’s)
 Part 1: MySQL
 About MySQL
 Connecting to MySQL
 Overview of basic commands
 Inserting data
 Selecting certain entries
 Altering existing entries
 Deleting data
 Database: recap

 A Database is simply a collection of data. In relational

database, data is organized into tables.

Students Table
student_id first_name last_name
1 Martina Naughton
2 Joe Bloggs
3 Bary MCCarthy

 Database Management System (DBMS) is software to

maintain and utilize the collections of data (e.g., Oracle,
DB2, MySQL)
 About MySQL

 MySQL = Free open source SQL (Structured

Query Language) database server.

 SQL language defines how to insert, retrieve,

modify and delete data

 Your BEST Friend: MySQL 5.0 Reference

Manual
 http://dev.mysql.com/doc/refman/5.0/en/index.html
 Connecting to MySQL

 Use mysql on csserver.ucd.ie

 Everyone have been granted:
 An account on csserver
 Username = FirstName_initial + Surname
 E.g., Martina Naughton = mnaughton
 Password = randomly generated (will be handed out after lecture)
 A MySQL account
 Username = same as above
 Password = same as above
 Connecting to
csserver.ucd.ie
 Mac/Linux: open a terminal window and login
using:
ssh [email protected]
 Windows: download putty connection manager
 Download the putty from:
http://the.earth.li/~sgtatham/putty/latest/x86/putty.exe
and save it on your desktop.
 When you double click on putty, you will get the
following:
 Putty -- Connection Manager

1. Enter csserver.ucd.ie as the host name 2. Select ‘SSH’

3. Click ‘Open’
 Connecting to MySQL

 Start mysql by typing the following command:

 mysql -u username -p

 You give your username with -u, and the -p option

tells MySQL to prompt you for a password.

 You will be prompted for your password -

 Remember: this is the same password as the one you use
to log into your csserver.ucd.ie account.
 Connecting to MySQL cont’d

 If everything is successful, you should see a

MySQL command prompt that looks like this:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 688
Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the

buffer.

mysql>

The version of MySQL that you are using

 Creating a Database

 On csserver.ucd.ie, each user has

permission to create one database.
 This feature is unique to csserver (administrator’s
choice)

 All the tables you create will be put in here.

 The name of the database MUST be the same as

your username for csserver.ucd.ie.
 Again, this feature is unique to csserver
 Creating a Database cont’d

 To create your database, type the following command at the

MySQL command prompt:

mysql> CREATE DATABASE database_name;

 You can now specify that this is the database that you want
to operate on with this command:

mysql> USE username; NB: Don’t forget semi-colons!

 Any other commands you give will now be run on this

database.
 Creating Tables

 Inside your database, you will need a

number of tables to hold your data.

 Following slide contains a command to

create tables to hold information about
students.
 for this simple example, we're only going to
store their first and last names
 Student Example

mysql> CREATE TABLE students (

student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30),
last_name VARCHAR(30)
);
 Student Example cont’d

mysql> CREATE TABLE students (

student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30),
last_name VARCHAR(30)
);

CREATE TABLE is the name of the command we're trying to run.

 Student Example cont’d

mysql> CREATE TABLE students (

student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30),
last_name VARCHAR(30)
);

Every table must have a primary key - indicated by “PRIMARY KEY”

 Student Example cont’d

mysql> CREATE TABLE students (

student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30),
last_name VARCHAR(30)
);

“AUTO_INCREMENT” - useful feature for ids that are numbers

If a student is entered into the database with no id number,

MySQL will automatically give them a student_id that is
one greater than the last student to be put in.
 Student Example cont’d

mysql> CREATE TABLE students (

student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30),
last_name VARCHAR(30)
);

Names of the fields that the table should have

 Student Example cont’d

mysql> CREATE TABLE students (

student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30),
last_name VARCHAR(30)
);

The INT datatype indicates that the student_id must be an integer.

VARCHAR(30) says that first_name and last_name are

variable-length strings of characters.

The (30) refers to the maximum length that any first_name or

last_name can be.
 More on creating tables

 A database can hold multiple tables

 Lets also create another table in the same way, to hold
info about grades

mysql> CREATE TABLE grades (student_id INT,

class_id INT, grade FLOAT, PRIMARY KEY(student_id,
class_id));
 More on creating tables

 A database can hold multiple tables

 Lets also create another table in the same way,
to hold info about grades

mysql> CREATE TABLE grades (student_id INT,

class_id INT, grade FLOAT, PRIMARY KEY(student_id,
class_id));

NOTE: difference in how primary key is defined here

 Finding out info about your
database
 Two useful commands
 SHOW TABLES command will give you the names of
all the tables that are currently present in your database.

mysql> show tables;

+---------------------+
| Tables_in_mnaughton |
+---------------------+
| classes |
| grades |
| students |
+---------------------+
3 rows in set (0.00 sec)
 Finding out info about your
database cont’d
 Two useful commands
 To find out more about the fields in a particular table,
use the DESCRIBE command. This will show the fields
in your table, the datatypes, primary keys, etc.
 Syntax: mysql> DESCRIBE table_name;

mysql> DESCRIBE students;

+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| student_id | int(11) | NO | PRI | NULL | auto_increment |
| first_name | varchar(30) | YES | | NULL | |
| last_name | varchar(30) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 Finding out info about your
database cont’d
 This command (DESCRIBE) is particularly
important when you want to insert or load
data into your database.

 For these commands, it is necessary to know what

order the fields should be in.

 For the student table above, the first should be

student_id, followed by first_name and
finally last_name.
 Loading data into your database

 Many ways to achieve this

 Load from a data file
 Insert data one entry/item at a time
 This is more than likely what you will be doing
 i.e. take data from a webform and enter it into the
database via java program, one item at a time (an
example will follow in later slides)
 Loading from a file

 Instead of writing an SQL statement to insert each

item into your database, you can instead create a
tab-delimited file.

 Each file should have one record per line, with the
fields on each line delimited by a tab character.

 The fields should be in the same order as they are

in your table definition.
 Example File: Students.txt

1 Martina Naughton
2 Tina Woods A <tab> separates each field
3 Bary MCCarthy
4 Jeff Phelan
5 Marcus Alvers
6 Michael Amadore
7 Carl Gaffney
8 Rachel Mooney
9 Lisa Fowler
10 August Makalakalane
 Loading Data from a file

 Use LOAD DATA INFILE command

 More info available at:
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

 Example:
mysql> LOAD DATA LOCAL INFILE 'students.txt'
INTO TABLE students;

Expected Output:
Query OK, 10 rows affected (0.00 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
 Entering data one item at a
time
 Use INSERT command
 See http://dev.mysql.com/doc/refman/5.0/en/insert.html for more
information
 Syntax: INSERT INTO table_name (field_name1,
field_name2) VALUES(val1,val2,val3);

 Examples:
insert into students (student_id, first_name,
last_name) values (1, "Martina", "Naughton");
OR
insert into students (student_id, first_name,
last_name) values (null, ”joe", ”Bloggs");

If the field is set to be “AUTO_INCREMENT” - mysql will assign appropriate value

 Selecting data

 Use SELECT statement - possibly the most

complex you'll come across
 More information available at:
http://dev.mysql.com/doc/refman/5.0/en/select.html

 This is the command you need to fetch data

from your database.
 Selecting Data cont’d

 First, a simple example - selecting everything from

a table (students table):
mysql> SELECT * FROM students;

The * indicates that you want all the fields in the table to be selected.
 Your response should look something like:
+------------+------------+-----------+
| student_id | first_name | last_name |
+------------+------------+-----------+
| 1 | Martina | Naughton |
| 2 | Joe | Bloggs |
+------------+------------+-----------+
 Selecting Data cont’d

 You could also specify particular fields if you

didn't want everything to be selected:
mysql> SELECT first_name, last_name FROM students;

 Your response should look something like:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Martina | Naughton |
| Joe | Bloggs |
+------------+-----------+
2 rows in set (0.00 sec)
 Selecting Data cont’d

 By default, the specified rows will be selected from

every row in your table.
 Adding a WHERE clause to your statement can
cause only particular rows to be selected:
mysql> SELECT * FROM students WHERE student_id=2;

+------------+------------+-----------+
| student_id | first_name | last_name |
+------------+------------+-----------+
| 2 | Joe | Bloggs |
+------------+------------+-----------+
1 row in set (0.00 sec)
 Selecting Data cont’d

 You may get a result list such as:

+------------+--------------+
| first_name | last_name |
+------------+--------------+
| Martina | Naughton |
| Marcus | Alvers |
| Marcus | Alvers | Duplicates]
| Michael | Amadore |
| Rachel | Mooney |
| August | Makalakalane |
| August | Makalakalane |
+------------+--------------+

To remove duplicates, use select distinct from… instead of select from…

 Selecting Data cont’d

 You can also set a limit to the number of records

that should be selected.
 For example, to select the first 5 records in the
students table:
mysql> SELECT * FROM students LIMIT 5;
+------------+------------+-----------+
| student_id | first_name | last_name |
+------------+------------+-----------+
| 1 | Martina | Naughton |
| 2 | Joe | Bloggs |
| 3 | Bary | MCCarthy |
| 4 | Jeff | Phelan |
| 5 | Marcus | Alvers |
+------------+------------+-----------+
 Selecting Data cont’d

 You can also control the order in which the records

are returned.
 For example, in ascending order by last name:
mysql> SELECT * FROM students order by last_name ASC
LIMIT 5;
+------------+------------+-----------+
| student_id | first_name | last_name |
+------------+------------+-----------+
| 5 | Marcus | Alvers |
| 6 | Michael | Amadore |
| 2 | Joe | Bloggs |
| 9 | Lisa | Fowler |
| 7 | Carl | Gaffney |
+------------+------------+-----------+
 Selecting Data cont’d

 You can also control the order in which the records

are returned.
 For example, in ascending order by last name:
mysql> SELECT * FROM students order by last_name ASC
LIMIT 5;
+------------+------------+-----------+
| student_id | first_name | last_name |
ASC = Ascending order
+------------+------------+-----------+
| 5 | Marcus DESC|= Descending
Alvers | order
| 6 | Michael | Amadore |
| 2 | Joe | Bloggs |
| 9 | Lisa | Fowler |
| 7 | Carl | Gaffney |
+------------+------------+-----------+
 More complex SELECT
queries
 For more complex queries, you'll have to
JOIN tables together to get what you're
looking for
 You'll find the relevant part of the manual here:
http://dev.mysql.com/doc/refman/5.0/en/join.html
 Join Example

 Example - to get all the grades relating to a

particular student, you could use the join clause as
follows:
mysql> SELECT * FROM students JOIN grades
USING(student_id) WHERE student_id=1;

 A couple of points to note on this:

 The JOIN we use here causes every row in the students
table to be linked with every row in the grades table
where the student_id values are the same.
 The WHERE clause says that we're only interested in
the student with a student_id of 1.
 Join Example cont’d

 Expected output:
+------------+------------+-----------+------------+----------+-------+
| student_id | first_name | last_name | student_id | class_id | grade |
+------------+------------+-----------+------------+----------+-------+
| 1 | John | Falkner | 1 | 1 | 25.5 |
| 1 | John | Falkner | 1 | 2 | 74.2 |
+------------+------------+-----------+------------+----------+-------+
2 rows in set (0.01 sec)
 Updating Data

 Updating data basically means changing

data that's already in the database.

 This is done with the UPDATE command

 More information available at:
http://dev.mysql.com/doc/refman/5.0/en/update.html
 Updating Data cont’d

 For example, we need to change some of the

names of the students in our class. Suppose we've
been notified that "Carl Gaffney" spells his name
with a "K".
mysql> UPDATE students SET first_name='Karl'
WHERE student_id=7;

 Multiple fields can be changed at the same time

too. From our data, it looks as if Barry McCarthy's
name was typed in a hurry. To correct it:
mysql> UPDATE students SET first_name='Barry',
last_name='McCarthy' WHERE student_id=3;
 Updating Data cont’d

 In these examples, we have used the primary

key to identify students that should be
updated, so only one student's information at
a time is being changed.

 However you should note that any student

that matches the WHERE clause will have
their data changed.
 Deleting Data

 Deletion of data from a table is done with

the DELETE command
 More information available at:
http://dev.mysql.com/doc/refman/5.0/en/delete.html
 For example, to delete a student with the
student id "15", we would use this:
mysql> DELETE FROM students WHERE student_id=15;
 Deleting Data cont’d

 The DELETE command can delete more than one

record at a time, so care is needed. Any record that
matches the WHERE clause will be deleted.
 Supposing the repeat exams are coming up, so we
want to replace all fail grades with the grade
students got in their repeats. Deleting all grades
under 40% can be done like so:
mysql> DELETE FROM grades WHERE grade<40;
 Deleting Data cont’d

 You can also delete all the data in a table really

easy (so be careful!) with the following commands:
mysql> DELETE FROM students;
 Deletes all entries from students table

mysql> DELETE FROM grades;

 Deletes all entries from grades table

mysql> DELETE FROM classes;

 Deletes all entries from classes table
The End… for today
 Part 2: Manipulating your database
through Java (JavaBeans/JSP’s)

 Cover on Wednesday..