SQL Introduction with MySQL and JDBC

by Mujtaba Talebi

This tutorial is designed to be a quick start guide. The instructions are written to be used on a Windows machine, but the download links also have other operating system source code and binaries to use. Please visit the links to other resources for more information.

About SQL:

Name: SQL (Pronounced one letter at a time)
Year created: 1970
Created by: IBM, Donald D. Chamberlin, Raymond F. Boyce
Paradigm: Declarative
Domain: Database
Advantages:
  • Simple and easy to use.
  • There is a diverse array of SQL implementations, many of them free to use like MySQL.
  • Considered to be great for handling very large database tasks.
Disadvantages:
  • Porting SQL code from one major database system to another requires major modifications due to SQL's diverse implementations.
  • The SQL standard is large and complex, so most databases do not implement the entire standard.
  • Some areas of the SQL standards such as semantics are ambiguous.
  • It is possible to get stuck with a company's SQL systems because they (probably intentionally) make their systems incompatible with others.
Specification: MySQL Implementation
Grammar: SQL92 Grammar

Step 1. Download and install the latest stable version of MySQL.

Step 2. Download and install the latest stable version of MySQL Administrator. MySQL Administrator is a graphical user interface tool which lets you administrate your MySQL databases.

Step 3. Download and install the latest stable version of the MySQL JDBC Connector. JDBC (JAVA Database Connectors) allow you to manipulate databases from a JAVA program.

Step 4. Run the MySQL Command Line Client and create a database.

//create table to contain csc1800 language tutorials, the students responsible for them, and
//the date of their presentations
create table languagetutorial(languagename varchar(15) not null, studentname varchar(15) not null, presentationdate varchar(25) not null);

//inserts entries for each project and student
insert into languagetutorial values('JAVA', 'Dr. Way', 'October 21, 2005');
insert into languagetutorial values('Cold Fusion', 'Lindsay', 'November 7, 2005');
insert into languagetutorial values('Cold Fusion', 'Rob', 'November 7, 2005');
insert into languagetutorial values('C#', 'Amy', 'November 7, 2005');
insert into languagetutorial values('C#', 'John', 'November 7, 2005');
insert into languagetutorial values('Python', 'Todd', 'November 9, 2005');
insert into languagetutorial values('Python', 'Mike', 'November 9, 2005');
insert into languagetutorial values('Prolog', 'Rich', 'November 9, 2005');
insert into languagetutorial values('Fortran', 'Joe', 'November 11, 2005');
insert into languagetutorial values('SQL', 'Mujtaba', 'November 11, 2005');
insert into languagetutorial values('Perl System', 'Alex', 'November 14, 2005');
insert into languagetutorial values('PHP', 'Matt', 'November 14, 2005');
insert into languagetutorial values('PHP', 'Dublas', 'November 14, 2005');
insert into languagetutorial values('Perl CGI', 'Lou', 'November 16, 2005');
insert into languagetutorial values('Perl CGI', 'Jason', 'November 16, 2005');
insert into languagetutorial values('BASIC', 'Brendan', 'November 16, 2005');
insert into languagetutorial values('BASIC', 'Tunde', 'November 16, 2005');
insert into languagetutorial values('Smalltalk', 'Dan K.', 'November 18, 2005');
insert into languagetutorial values('Ruby', 'George', 'November 18, 2005');
insert into languagetutorial values('XML', 'Lauren', 'November 21, 2005');
insert into languagetutorial values('XML', 'Zack', 'November 21, 2005');
insert into languagetutorial values('C++', 'Ryan', 'November 21, 2005');
insert into languagetutorial values('C++', 'Dan L.', 'November 21, 2005');

/*Demonstration of different commands*/

/*to get all entries from table*/

/*select - used to get data from a table*/
select * from languagetutorial;

/*distinct - used to isolate results*/
select distinct languagename from languagetutorial;
select distinct presentationdate from languagetutorial;

/*where - used to specify some condition on the data. ie - entries where purchases > $100*/
select languagename, studentname from languagetutorial where presentationdate = 'November 11, 2005';

Step 5. Create a JAVA program and use JDBC to interact with it.

//************************************************
// JDBCIntro.java Author: Mujtaba Talebi
// Description: Gives a basic introduction for
// using the MySQL JDBC.
//************************************************
import java.sql.*;

public class JDBCIntro {

public static void main(String[] args) throws SQLException, ClassNotFoundException {

//Load the JDBC driver
Class.forName("com.mysql.jdbc.Driver");
System.out.println("MySQL JDBC Driver Loaded.");

//Establish a connection
Connection connection = DriverManager.getConnection
("jdbc:mysql://localhost/csc1800", "root", "mysql");
System.out.println("Database connected.");

//Create a statement
Statement statement = connection.createStatement();

//Execute a statement
ResultSet resultSet = statement.executeQuery
("select * from languagetutorial");

//Iterate through the results and prints them
while (resultSet.next())
System.out.println(resultSet.getString(2) + " is presenting " +
resultSet.getString(1) + " on " + resultSet.getString(3));

// Close the connection
connection.close();
}
}

 Learning more                                                                                                                     
Here are some sources that can help you learn more: