Java and Database Connections

First we have to discuss SQL. This is the Structured Query Language that is used by almot all commercial and other databases to get informatino into and out of the database. The most common kind of DB is the relational database. This stores data as a set of tables. Here is a diagram of the tables in the student grade example and their relationships.
Grades example ER diagram

Here are some simple examples of SQL


get everything from all students
SELECT * FROM students;

get lastname and postal code for all students
SELECT lastname,postalcode FROM students;

get lastname from cs majors
SELECT lastname FROM students WHERE major = "cs";

get names of people in cs 212
SELECT lastname,firstname
FROM students s, classes c, studentclass a
WHERE c.classname = "cs 212" AND
c.classid = a.classid AND
a.studentid = s.studentid;

get all scores for cs212 order by assignment
SELECT lastname,firstname,a.assignmentdescription as asg,score
FROM classes c, students s,studentclasses x, results r,assignments a
WHERE c.classname = "cs 212" AND
x.classid = c.classid AND
s.studentid=x.studentid AND
r.studentid = s.studentid AND
a.classid=c.classid AND
r.assignmentid = a.assignmentid
ORDER BY r.assignmentid;

add 3 to all scores for assignmentid 1
select score from results where assignmentid=1;

update results set score = score + 3 where assignmentid=1;

select score from results where assignmentid=1;

JDBC

Java DataBase Connection, modelled after ODBC. It provides classes to describe the connection between the program and the database. Here is a sample program.


import java.sql.*;

/**
 * Quick test of the JDBC interface
 * 
 * @author Kent Archie 
 * @version 2/25/2002
 */
public class main
{
    public static void main(String[] args)
    {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection c = DriverManager.getConnection("jdbc:odbc:cs212db");
            Statement s = c.createStatement();
            String query = "SELECT * FROM Instructors";
            ResultSet r = s.executeQuery(query);
            while(r.next()) {
                System.out.print("id = " + r.getInt(1) + " ");
                System.out.print("name = " + r.getString(2) + " " );
                System.out.print("phone = " + r.getString(3) + " ");
                System.out.println("extension = " + r.getString(4));
            } // while

            // now add 3 to all cs 212 grades
            Statement checkit = c.createStatement();
            String squery = "SELECT score FROM results where assignmentid=1";

            r = s.executeQuery(squery);
            System.out.println("Before update");
            while(r.next()) {           
                System.out.println("score = " + r.getInt(1));
            } // while

            Statement u = c.createStatement();
            String uquery = "update results set score = score + 3 where assignmentid=1;";
            u.executeUpdate(uquery);

            System.out.println("After update");
            r = s.executeQuery(squery);
            while(r.next()) {           
                System.out.println("score = " + r.getInt(1));
            } // while
            c.close();
        }
        catch (Exception e) {
            System.out.println(e);
        }
    } // main
} // main class

Results


id = 1 name = Kent Archie phone = 6303931788 extension = 1
id = 2 name = Peter Parker phone = 6303931788 extension = 2
id = 3 name = Susan Ivanova phone = 6303931788 extension = 3
Before update
score = 99
score = 108
score = 120
score = 77
After update
score = 102
score = 111
score = 123
score = 80