JDBC 8: The Complete Code

package dbase2016;

/**
 *
 * @author ajb
 */
public class DBase2016 {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
    GUIAddStudent  addStudent = new GUIAddStudent();
    addStudent.setVisible(true);
    }

}
package dbase2016;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;

/**
 *
 * @author ajb
 */
public class DBase {

    private static Connection con;

    DBase() {
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
    } catch (Exception ex) {
        System.err.println(ex);
    }

    }

    public static void makeConnection() {
    try {
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bluecoat", "root", "");
    } catch (SQLException ex) {
        System.err.println(ex);
    }

    }

    public static void printPupils() {

    Statement statement;
    makeConnection();
    try {
        statement = con.createStatement();
        ResultSet rs = statement.executeQuery("SELECT * FROM Student");

        while (rs.next()) {
        System.out.println("Forename: " + rs.getString("Forename") 
                  + " Surname: " + rs.getString("Surname")
                  + " Gender: " + rs.getString("Gender") 
                  + " Form: " + rs.getString("Form"));
        }

        con.close();

    } catch (SQLException ex) {
        System.err.println(ex);
    }
    }

    public static ArrayList<Student> getPupils()
    {
    ArrayList<Student> students = new ArrayList();
    Statement statement;
    makeConnection();
    try {
        statement = con.createStatement();
        ResultSet rs = statement.executeQuery("SELECT * FROM Student");

        while (rs.next()) {

        Student student = new Student();
        student.setForename(rs.getString("Forename")); 
        student.setSurname(rs.getString("Surname"));
        student.setGender(rs.getString("Gender"));
        student.setForm(rs.getString("Form"));
        students.add(student);
        }

        con.close();

    } catch (SQLException ex) {
        System.err.println(ex);
    }

    return students;
    }

    public static void addStudent() {

    makeConnection();
    try {
        PreparedStatement prep = con.prepareStatement("INSERT INTO Student (Forename, Surname, Gender, Form) VALUES (?,?,?,?)");
        prep.setString(1, "Last");
        prep.setString(2, "James");
        prep.setString(3, "M");
        prep.setString(4, "9C");

        prep.executeUpdate();

        con.close();

    } catch (SQLException ex) {
        System.err.println(ex);
    }

    }

    public static void addStudent(Student student) {

    makeConnection();
    try {
        PreparedStatement prep = con.prepareStatement("INSERT INTO Student (Forename, Surname, Gender, Form) VALUES (?,?,?,?)");
        prep.setString(1, student.getForename());
        prep.setString(2, student.getSurname());
        prep.setString(3, student.getGender());
        prep.setString(4, student.getForm());

        prep.executeUpdate();

        con.close();

    } catch (SQLException ex) {
        System.err.println(ex);
    }

    }

}
package dbase2016;

/**
 *
 * @author ajb
 */
public class Student {
    private String forename;
    private String surname;
    private String gender;
    private String form;

    /**
     * @return the forename
     */
    public String getForename() {
    return forename;
    }

    /**
     * @param forename the forename to set
     */
    public void setForename(String forename) {
    this.forename = forename;
    }

    /**
     * @return the surname
     */
    public String getSurname() {
    return surname;
    }

    /**
     * @param surname the surname to set
     */
    public void setSurname(String surname) {
    this.surname = surname;
    }

    /**
     * @return the gender
     */
    public String getGender() {
    return gender;
    }

    /**
     * @param gender the gender to set
     */
    public void setGender(String gender) {
    this.gender = gender;
    }

    /**
     * @return the form
     */
    public String getForm() {
    return form;
    }

    /**
     * @param form the form to set
     */
    public void setForm(String form) {
    this.form = form;
    }

}
package dbase2016;

import java.util.ArrayList;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;

/**
 *
 * @author ajb
 */
public class GUIDisplayStudents extends JFrame {

    JTextArea textArea;
    JScrollPane scrollPane;

    GUIDisplayStudents()
    {
    this.setSize(300,200);
    this.setLocationRelativeTo(null);
    this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    textArea = new JTextArea("Sample Text");
    scrollPane = new JScrollPane(textArea);
    add(scrollPane);

    }

    GUIDisplayStudents(ArrayList<Student> students)
    {
    this.setSize(300,200);
    this.setLocationRelativeTo(null);
    this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    textArea = new JTextArea();
    scrollPane = new JScrollPane(textArea);
    for(Student student : students)
    {            
        textArea.append(student.getForename() + " " + student.getSurname() + "\n");
    }
    add(scrollPane);

    }

}
package dbase2016;

import java.awt.Container;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.Box;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JTextField;

/**
 *
 * @author ajb
 */
public class GUIAddStudent extends JFrame implements ActionListener{
    
    JLabel lblForename = new JLabel("Forename");
    JLabel lblSurname = new JLabel("Surname");
    JLabel lblGender = new JLabel("Gender");
    JLabel lblForm = new JLabel("Form");
    
    JTextField txtForename = new JTextField("");
    JTextField txtSurname = new JTextField("");
    JTextField txtGender = new JTextField("");
    JTextField txtForm = new JTextField("");
    
    JButton OK = new JButton("OK");
   
    
    GUIAddStudent()
    {
        Container vert = Box.createVerticalBox();
        vert.add(lblForename);
        vert.add(txtForename);
        vert.add(lblSurname);
        vert.add(txtSurname);
        vert.add(lblGender);
        vert.add(txtGender);
        vert.add(lblForm);
        vert.add(txtForm);
        
        vert.add(OK);
        
        OK.addActionListener(this);
        add(vert);
        
        this.setSize(300,250);
        this.setLocationRelativeTo(null);
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); 
        
    }

    @Override
    public void actionPerformed(ActionEvent ae) {
        Student student = new Student();
        student.setForename(txtForename.getText());
        student.setSurname(txtSurname.getText());
        student.setGender(txtGender.getText());
        student.setForm(txtForm.getText());
        
        DBase.addStudent(student);
        
        GUIDisplayStudents  displayPupils = new GUIDisplayStudents(DBase.getPupils());
        displayPupils.setVisible(true);
    }
    
}

JDBC 7: Adding Students using a GUI

Create DBase.addStudent(Student student) Method

public static void addStudent(Student student) {
makeConnection();
try {
PreparedStatement prep = con.prepareStatement("INSERT INTO Student (Forename, Surname, Gender, Form) VALUES (?,?,?,?)");
prep.setString(1, student.getForename());
prep.setString(2, student.getSurname());
prep.setString(3, student.getGender());
prep.setString(4, student.getForm());
prep.executeUpdate();
con.close();
} catch (SQLException ex) {
System.err.println(ex);
}
}

Create GUIAddStudent Class

The following code will display the simple GUI shown

package dbase2016;
import java.awt.Container;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.Box;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JTextField;
/**
*
* @author ajb
*/
public class GUIAddStudent extends JFrame implements ActionListener{
JLabel lblForename = new JLabel("Forename");
JLabel lblSurname = new JLabel("Surname");
JLabel lblGender = new JLabel("Gender");
JLabel lblForm = new JLabel("Form");
JTextField txtForename = new JTextField("");
JTextField txtSurname = new JTextField("");
JTextField txtGender = new JTextField("");
JTextField txtForm = new JTextField("");
JButton OK = new JButton("OK");
GUIAddStudent()
{
Container vert = Box.createVerticalBox();
vert.add(lblForename);
vert.add(txtForename);
vert.add(lblSurname);
vert.add(txtSurname);
vert.add(lblGender);
vert.add(txtGender);
vert.add(lblForm);
vert.add(txtForm);
vert.add(OK);
OK.addActionListener(this);
add(vert);
this.setSize(300,250);
this.setLocationRelativeTo(null);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
@Override
public void actionPerformed(ActionEvent ae) {
Student student = new Student();
student.setForename(txtForename.getText());
student.setSurname(txtSurname.getText());
student.setGender(txtGender.getText());
student.setForm(txtForm.getText());
DBase.addStudent(student);
GUIDisplayStudents displayPupils = new GUIDisplayStudents(DBase.getPupils());
displayPupils.setVisible(true);
}
}

JDBC 6: Get Pupils and Display in GUI

Create a DBase.getPupils() method

A method to fetch all the pupils. They are wrapped in the Student class and then returned as an ArrayList

public static ArrayList<Student> getPupils()
    {
    ArrayList<Student> students = new ArrayList();
    Statement statement;
    makeConnection();
    try {
        statement = con.createStatement();
        ResultSet rs = statement.executeQuery("SELECT * FROM Student");

        while (rs.next()) {

        Student student = new Student();
        student.setForename(rs.getString("Forename")); 
        student.setSurname(rs.getString("Surname"));
        student.setGender(rs.getString("Gender"));
        student.setForm(rs.getString("Form"));
        students.add(student);
        }

        con.close();

    } catch (SQLException ex) {
        System.err.println(ex);
    }

    return students;
    }

Use the getPupils() method in the GUI

package dbase2016;

import java.util.ArrayList;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;

/**
 *
 * @author ajb
 */
public class GUIDisplayStudents extends JFrame {

    JTextArea textArea;
    JScrollPane scrollPane;

    GUIDisplayStudents(ArrayList<Student> students)
    {
    this.setSize(300,200);
    this.setLocationRelativeTo(null);
    this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    textArea = new JTextArea();
    scrollPane = new JScrollPane(textArea);
    for(Student student : students)
    {            
        textArea.append(student.getForename() + " " + student.getSurname() + "\n");
    }
    add(scrollPane);

    }

}
package dbase2016;

/**
 *
 * @author ajb
 */
public class DBase2016 {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
    GUIDisplayStudents  displayPupils = new GUIDisplayStudents(DBase.getPupils());
    displayPupils.setVisible(true);
    }

}

JDBC 5: Creating a GUI

A simple GUI to display database contents

package dbase2016;

import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;

/**
 *
 * @author ajb
 */
public class GUIDisplayStudents extends JFrame {

    JTextArea textArea;
    JScrollPane scrollPane;

    GUIDisplayStudents()
    {
    this.setSize(300,200);
    this.setLocationRelativeTo(null);
    this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    textArea = new JTextArea("Sample Text");
    scrollPane = new JScrollPane(textArea);
    add(scrollPane);

    }

}

Student Class

This will be used to wrap the data retrieved from the database

package dbase2016;

/**
 *
 * @author ajb
 */
public class Student {
    private String forename;
    private String surname;
    private String gender;
    private String form;

    /**
     * @return the forename
     */
    public String getForename() {
    return forename;
    }

    /**
     * @param forename the forename to set
     */
    public void setForename(String forename) {
    this.forename = forename;
    }

    /**
     * @return the surname
     */
    public String getSurname() {
    return surname;
    }

    /**
     * @param surname the surname to set
     */
    public void setSurname(String surname) {
    this.surname = surname;
    }

    /**
     * @return the gender
     */
    public String getGender() {
    return gender;
    }

    /**
     * @param gender the gender to set
     */
    public void setGender(String gender) {
    this.gender = gender;
    }

    /**
     * @return the form
     */
    public String getForm() {
    return form;
    }

    /**
     * @param form the form to set
     */
    public void setForm(String form) {
    this.form = form;
    }

}

JDBC 4: The Code so Far…

The Code so Far

package dbase2016;

/**
 *
 * @author ajb
 */
public class DBase2016 {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
    DBase.addStudent();
    DBase.printPupils();
    }

}
package dbase2016;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author ajb
 */
public class DBase {

    private static Connection con;

    DBase() {
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
    } catch (Exception ex) {
        System.err.println(ex);
    }

    }

    public static void makeConnection() {
    try {
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bluecoat", "root", "");
    } catch (SQLException ex) {
        System.err.println(ex);
    }

    }

    public static void printPupils() {

    Statement statement;
    makeConnection();
    try {
        statement = con.createStatement();
        ResultSet rs = statement.executeQuery("SELECT * FROM Student");

        while (rs.next()) {
        System.out.println("Forename: " + rs.getString("Forename") 
                  + " Surname: " + rs.getString("Surname")
                  + " Gender: " + rs.getString("Gender") 
                  + " Form: " + rs.getString("Form"));
        }

        con.close();

    } catch (SQLException ex) {
        System.err.println(ex);
    }
    }

    public static void addStudent() {

    makeConnection();
    try {
        PreparedStatement prep = con.prepareStatement("INSERT INTO Student (Forename, Surname, Gender, Form) VALUES (?,?,?,?)");
        prep.setString(1, "Last");
        prep.setString(2, "James");
        prep.setString(3, "M");
        prep.setString(4, "9C");

        prep.executeUpdate();

        con.close();

    } catch (SQLException ex) {
        System.err.println(ex);
    }

    }

}

JDBC 3: Queries

Fetch ResultSet

Once you have a connection, you can now fetch data from the database. Add the following method to the DBase class.

  • The method creates a statement attached to the connection con.
  • The statement executes a simple MySQL query. The query results are stored in a ResultSet rs.
  • A pointer is set to before the first record in the ResultSet.
  • A while statement is used to traverse the set.
  • Finally, the connection is closed.
public static void printPupils() {

    Statement statement;
    makeConnection();
    try {
        statement = con.createStatement();
        ResultSet rs = statement.executeQuery("SELECT * FROM Student");

        while (rs.next()) {
        System.out.println("Forename: " + rs.getString("Forename") 
                  + " Surname: " + rs.getString("Surname")
                  + " Gender: " + rs.getString("Gender") 
                  + " Form: " + rs.getString("Form"));
        }

        con.close();

    } catch (SQLException ex) {
        System.err.println(ex);
    }
}
package dbase2016;

/**
 *
 * @author ajb
 */
public class DBase2016 {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {

    DBase.printPupils();
    }

}

Add a record using a PreparedStatement

Note that we use executeQuery() to retrieve data and executeUpdate() to update the database.  Here we’ve hard coded the student we’re adding.   A later section will show how to add any student.

public static void addStudent() {

       makeConnection();
       try {
       PreparedStatement prep 
           = con.prepareStatement("INSERT INTO Student
        (Forename, Surname, Gender, Form) 
           VALUES (?,?,?,?)");
       prep.setString(1, "Last");
       prep.setString(2, "James");
       prep.setString(3, "M");
       prep.setString(4, "9C");

       prep.executeUpdate();

       con.close();

       } catch (SQLException ex) {
       System.err.println(ex);
       }

   }
package dbase2016;

/**
 *
 * @author ajb
 */
public class DBase2016 {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
    DBase.addStudent();
    DBase.printPupils();
    }

}

JDBC 2: Database Class

The following is the class I’m going to use for the main part of this tutorial. It’s just the Quick Start code broken down into methods.

package dbase2016;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author ajb
 */
public class DBase {

    private static Connection con;

    DBase() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {
            System.err.println(ex);
        }

    }

    public static void makeConnection() {
        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bluecoat", "root", "");
        } catch (SQLException ex) {
            System.err.println(ex);
        }

    }


    }

}

Check the connection by calling the makeConnection() method as follows. If everything is okay the program will simply terminate. If an exception is thrown, check your port number, username and password.

package dbase2016;

/**
 *
 * @author ajb
 */
public class DBase2016 {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
    DBase.makeConnection();
    }

}

JDBC 1: Set up

Create a Database to query

First, set up a MySQL database. For the purposes of this demonstration I set up a simple database called bluecoat with one table: student. The table has four columns of type varchar: Forename, Surname, Gender and Form.

Add mysql-connector JAR

You will need to download the mysql-connector JAR and add it to your classpath. Consult your IDE’s documentation on how to do this.

You can find the JAR here: https://dev.mysql.com/downloads/connector/j/

If you’re using Netbeans, right click on the Libraries folder and choose Add JAR/Folder

The JAR is installed by default in the ext folder on windows:

 

Quick Start

The following code will connect to a database called bluecoat running on a mysql server on port 3306 of localhost. I used XAMPP to write this tutorial: by default the username of the database was root, the password was left blank.

try {
//Load mysql jdbc Driver      
Class.forName("com.mysql.jdbc.Driver").newInstance();
// Connect to Database      
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bluecoat", "root", "");
} catch (Exception ex) {
      System.err.println(ex);
}

Run the code. If it doesn’t throw an exception, you’re successfully connected!