Welcome

This blog’s tagline is adapted from the Emacs Org-Mode motto. It seemed appropriate, as I seem to have spent most of my life writing novels and short stories (of which you can find out more at tonyballantyne.com) or teaching computer coding.

I’ve amassed a lot of material over the years, and I wanted to share it with people who may not have had the same access to education as people living in my country are lucky enough to have. If you want to change the world, become a teacher.

As the the teaching of coding seems to be coming back into fashion, I’ve also included my thoughts on the pedagogy of this subject.

All comments are gratefully received.

Code is Poetry

Brian Bilston has written a History of Modern Art in Poetry.  I  wondered what it would be like to do something similar in various programming languages.

Here’s the original poem:

Roses are red
Violets are blue
Sugar is sweet
And so are you

Haskell

Here’s the poem constructed using a zip statement in Haskell

Prelude> zip ["roses","violets","sugar","you"]["red","blue","sweet","sweet"]
[("roses","red"),("violets","blue"),("sugar","sweet"),("you","sweet")]

The list produced holds the relationship that sugar is sweet and you are sweet. The comparison between “you” and sugar is not made clear.

Lisp

Here’s the poem stored as an alist in Lisp

(setq poem '(("roses" . "red") ("violets" . "blue") ("sugar" . "sweet")("you" . "sweet")))
(mapcar (lambda (x) (concat (car x) " are " (cdr x))) poem)

I’ve gone one stage further here, using a mapcar function to produce something that looks a little bit more like the original poem, however we’re still missing the connection between “you” and sugar.

("roses are red" "violets are blue" "sugar are sweet" "you are sweet")

Python

Of course, sugar are sweet isn’t right.   Let’s try some Python.

poem = {"roses":"red","violets":"blue","sugar":"sweet","you":"sweet"}

for key, value in poem.items():
    if key == "sugar":
        print(key, "is" ,value)
    else:
        print(key, "are", value)

This output is at least grammatically correct.

roses are red
violets are blue
sugar is sweet
you are sweet

Java

Java can do something similar using a HashMap

Map<String, String> poem = new HashMap<String, String>();

        poem.put("roses", "red");
        poem.put("violets", "blue");
        poem.put("sugar", "sweet");
        poem.put("you", "sweet");

        for (Map.Entry<String, String> entry : poem.entrySet()) {
            if(entry.getKey().equals("sugar")){
                System.out.println(entry.getKey() + " is " + entry.getValue());
            } else{
                System.out.println(entry.getKey() + " are " + entry.getValue());
            }
            
        }

But we’re still no closer to conveying the connection between “you” being sweet, just like sugar is sweet.

Fortunately, Java allows us to use some object oriented design to better convey the meaning of the poem.

In the example below I’ve used an interface to allow sweetness to be applied to both sugar and to the special one to whom the poem refers.  The comparison is at last made clear.  As there can only be one true love, it seemed reasonable to make a singleton class for TheOne, inherited from a regular person.

Run the code and the poem is printed out properly, just like the original.  More importantly though, the concepts to which the poem refers are properly encapsulated and related.

The original poem was only 4 lines long.  My implementation takes 80 lines, but I think you’ll agree I’ve done a rather better job, providing clarity and removing any ambiguity.

public class Love {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        Flower [] rose = new Flower[12]; // 12 roses in a bunch
        Flower [] violet = new Flower[30]; // more violets in bunch
        Sugar sugar = new Sugar();
        TheOne myLove = TheOne.getInstance();  // Singleton class
        // There can only be one true love
        
        rose[0] = new Flower();
        rose[0].setColour("red");  // colour is static so only need
                                    // to instantiate one here
        
        violet[0] = new Flower();
        violet[0].setColour("blue");
        
        System.out.println("Roses are " + rose[0].getColour());
        System.out.println("Violets are " + violet[0].getColour());
        System.out.println(sugar.sweet());
        System.out.println(myLove.sweet());
    }
    
}

class Flower {
    private static String colour;
    
    public void setColour(String colour){
        this.colour = colour;
    }
    
    public String getColour (){
        return colour;
    }
}

class Sugar implements Sweetness {

    @Override
    public String sweet() {
        return "Sugar is sweet";
    }
    
}

class Person {
    public String sweet()
    {
        return "Not sweet";
    }
}

class TheOne extends Person implements Sweetness{
    private static TheOne instance = null;
    
    private TheOne()
    {
        
    }
    
    public static TheOne getInstance()
    {
        if(instance == null)
            instance = new TheOne();
        
        return instance;
    }

    @Override
    public String sweet() {
         return "And so are you";
    }
}

interface Sweetness {
    String sweet();
}

Functions and Parameters (Level 6)

Sample Code

def hello(name):
    answer = "Hello " + name
    return answer

print(hello("George"))
print(hello("Gill"))
def AreaRect(length, width):
    return length*width

l = int(input("Enter the length"))
w = int(input ("Enter the width"))
print(AreaRect(l, w))
PI = 3.1415

def main():
    radius = 4
    print("The area of a circle radius ", radius, " is ", Area(radius))     


def Area(r):
    return PI*r*r

main()

Exercises

  1. Write a function that accepts a string and returns “Pleased to meet you, ” + string
  2. Write a function that accepts a number and returns “Child” if the number is <18 and “Adult” otherwise
  3. Write a function that accepts a number and returns “Grade A” if the number is >20, “Grade B” if the number is >15, “Grade C” if the number is >10 and “Fail” otherwise.
  4. Write a function that accepts two numbers and returns the average of the numbers
  5. Write a function that accepts three integers and returns the average of the numbers.
  6. Write a function that accepts the length and width of a rectangle and returns the perimeter of the rectangle
  7. Write a function that accepts the base and height of a triangle and returns the area of the triangle
  8. Write a function that accepts a list and returns the sum of the list

Extension

  1. Write a function that returns the hypotenuse of a triangle when the other two sides are int a and int b. (Remember: hypotenuse squared equals a squared plus b squared)
  2. The scalar product of u=(u1,u2,u3) and v=(v1,v2,v3) is defined to be u1v1+u2v2+u3v3. Write a function that accepts two int tuples as parameters and returns an int representing the scalar product of those two tuples
  3. If A = (a1,a2, …an) and B = (b1,b2, …bn) then the vector sum of the two tuples A + B = (a1+b1, a2+b2, … , an+bn). Write a function that accepts two tuples as parameters and returns an array representing the vector sum of those two tuples
  4. The Euclidean distance between two points A = (a1,a2, …an) and B = (b1,b2, …bn) is defined as sqrt((a1-b1)2 + (a2-b2)2 +… + (an-bn)2). Write a function that accepts two int tuples representing A and B as parameters and returns a double representing the Euclidean distance between them.

While Loops (Level 5)

Question Loop Examples

A Basic Question Loop

Keep asking the question until the correct answer is entered

answer = ""

while (answer != "Paris"):
    answer = input("What is the capital of Paris?")

print("Correct!")

A Question Loop with a Count

Keep a count of how many attempts were made

answer = ""
count = 0

while (answer != "Paris"):
    answer = input("What is the capital of Paris?")
    count = count + 1

print("Correct!")
print("You took ", count , "goes")

A Question Loop with a Flag

finished = False
number = 0
print("Denary to Binary Converter")
print("Enter -1 to finish")

while (finished == False):
    number = int(input("Enter a number in Denary"))
    if (number == -1):
    finished = True
    else:
    print("{:b}".format(number))

A Question Loop with a Count and a Flag

finished = False
correct = False
answer = ""
tries = 3

while (finished == False):
    password = input("Enter the password: ")
    if (password == "p455w0rd"):
    finished = True
    correct = True
    elif (tries == 1):
    finished = True
    correct = False
    else:
    tries = tries - 1
    print("Wrong.  You have", tries, "tries remaining")


if(correct == True):
    print("You're in!")
else:
    print("Locked out!")

Exercise

  1. Write a program that asks “Are we there yet?” and prompts the user to enter an answer. The program loops until the user enters “Yes”. The program then outputs “Hooray!”
  2. Write a program that asks the user to guess a number between 1 and 10. The program loops until the user enters the correct answer [7]. The program then outputs then the number of guesses made.
  3. Modify the program from question 2 so that the user now has to guess a number between 1 and 100. The program outputs “Too low” if the guess is lower the number, “Too high” if the guess is highter than the number and “Correct” if the guess is correct. The program then terminates
  4. The following code converts pounds to kilograms. Write a program that prompts the user to enter a weight in pounds or -1 to terminate. The program will output the weight in kilograms. If -1 is entered the program will print “Goodbye”
pounds = 4
kilograms = pounds * 0.453592
print(kilograms)
  1. A house alarm system is triggered when the front door is open. The user has three attempts to enter a four digit code. If the user enters the correct code the system outputs “Deactivated!”. If the user enters the incorrect code the system outputs the number of attempts remaining. If the user does not enter the correct code within three attempts the system outputs “Alarm!”

Write a program to simulate the above.

Extension

Write a quiz program that asks the user 5 questions. The user is allowed 2 attempts at each question. At the end the program prints out the users score out of 5

Data Types: int and float (Level 3)

Remember that the input command reads strings. You have to convert strings to integers (whole numbers) or floats (decimals) if you want to use them to perform calculations.

Examples

1) Write a program that prompts the user to enter the length and width of a rectangle. Output the area of the rectangle

length = int(input("Enter the length of the rectangle"))
width = int(input("Enter the width of the rectangle"))
area = length*width
print("The rectangle has an area of " + str(area))

2) To convert miles to kilometers, you multiply the number of miles by 1.609. Write a program that prompts the user to enter the number of miles, and then output the answer converted to kilometers

Note the use of float as a user may input 12.5 miles, for example.

miles = float(input("Enter the number of miles"))
kilometers = miles * 1.609
print(str(miles) + " miles = " + str(kilometers) + " kilometers")

Exercise

  1. Write a program with two variables, length and width, that outputs the perimeter of a rectangle. Test it with length = 5 and width = 4.
  2. At the time of writing, the exchange rate for pounds to euros is 1 GBP = 1.19984 Euros. Write a program that will convert pounds to euros. Test it using the data GBP4.50 (Don’t forget to convert the input to a float!)
  3. Now write a program to convert euros to pounds. Test it using the data Euro 7.40
  4. Prompt the user to input a number. Output the square of that number.
  5. Prompt the user to input two numbers. Output the average of those two numbers.
  6. Prompt the user to input three numbers. Output the sum and the average of those three numbers.
  7. Assume pi = 3.1415. Prompt the user to input the radius of a circle. Output the circumference and the diameter of that circle

Extension: Fahrenheit to Celsius

Here are the formulas to convert from Fahrenheit to Celsius and back again.

°F to °C Deduct 32, then multiply by 5, then divide by 9 °C to °F Multiply by 9, then divide by 5, then add 32

  1. Now write a program to convert Celsius to Fahrenheit. Use the test data to check your program.
  2. Write a program to convert Fahrenheit to Celsius. Again, use the test data below to check your program.

Test data

C F
0 32
12 54
100 212
-3 27
-18 0
-23 -10

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();
    }

}