Assignment04


Prepared by   Mary Beth Mogensen   mary.mogensen@marquette.edu

Created October 27, 2013

Revised November 7, 2013

The Java source code for both programs (the shell and related team sql scripts) are located with my assignment 02.

Write a Java program using JDBC to show some of the information in your database.


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class teamDB {
	private Connection connect = null;
	private Statement statement = null;
	private ResultSet resultSet = null;

	public teamDB() throws Exception {
		try {

			//Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
			connect = DriverManager
					.getConnection("jdbc:derby:/Apache/db-derby-10.10.1.1-bin/bin/team9/;create=true");
			PreparedStatement statement = connect
					.prepareStatement("SELECT * from player");

			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int playerid = resultSet.getInt("playerid");
				String playername = resultSet.getString("playername");
				String playerlastname = resultSet.getString("playerlastname");

				System.out.println("ID: " + playerid);
				System.out.println("First Name: " + playername);
				System.out.println("Last Name: " + playerlastname);
				System.out.println();

			}
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}

	}

	private void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}

	public static void main(String[] args) throws Exception {
		teamDB dao = new teamDB();
	}

} 


Program output


ID: 11
First Name: Tyler
Last Name: Smith

ID: 6
First Name: Frank
Last Name: Taylords

ID: 9
First Name: Trey
Last Name: Meie

ID: 4
First Name: Parker
Last Name: Mogen

ID: 22
First Name: Gianni
Last Name: Hampton

ID: 31
First Name: Chris
Last Name: Cass

ID: 35
First Name: Kai
Last Name: Johnson

ID: 8
First Name: Patrick
Last Name: Cartersen

ID: 19
First Name: Danny
Last Name: Brown

Write a Java program using JDBC to enter some new information into your database.


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

public class team
{
    private static String dbURL = "jdbc:derby:/Apache/db-derby-10.10.1.1-bin/bin/team190/;create=true";
    private static String tableName = "player";
    // jdbc Connection
    private static Connection conn = null;
    private static Statement stmt = null;

    public static void main(String[] args)
    {
        createConnection();
        insertPlayer(93,13, "Billy", "Jones");
        insertPlayer(81,16, "Justin", "Pitt");
        insertPlayer(72,17, "Jacob", "Anderson");
        selectPlayer();
        shutdown();
    }
    
    private static void createConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection(dbURL); 
        }
        catch (Exception except)
        {
            except.printStackTrace();
        }
    }
    
    private static void insertPlayer(int playerid, int parentcontactid, String playername, String playerlastname)
    {
        try
        {
            stmt = conn.createStatement();
            stmt.execute("insert into " + tableName + " values (" +
                    playerid + "," + parentcontactid + ",'" + playername +"','" + playerlastname +"')");
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
    }
    
    private static void selectPlayer()
    {
        try
        {
            stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery("select * from " + tableName);
            ResultSetMetaData rsmd = results.getMetaData();
            int numberCols = rsmd.getColumnCount();
            for (int i=1; i<=numberCols; i++)
            {
                //print Column Names
                System.out.print(rsmd.getColumnLabel(i)+"\t\t");  
            }

            System.out.println("\n-------------------------------------------------------");

            while(results.next())
            {
                int playerid = results.getInt(1);
                int parentcontactid = results.getInt(2);
                String playername = results.getString(3);
                String playerlastname = results.getString(4);
                System.out.println(playerid + "\t\t"+parentcontactid + "\t\t"  + playername + "\t\t" + playerlastname);
            }
            results.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
    }
    
    private static void shutdown()
    {
        try
        {
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
            }           
        }
        catch (SQLException sqlExcept)
        {
            
        }

    }
}

Program Output


PLAYERID  PARENTCONTACTID  PLAYERNAME    PLAYERLASTNAME		
-------------------------------------------------------
11		1		Tyler		Smith
6		2		Frank		Taylords
9		3		Trey		Meie
4		4		Parker		Mogen
22		5		Gianni		Hampton
31		6		Chris		Cass
35		7		Kai		Johnson
8		8		Patrick		Cartersen
19		9		Danny		Brown
93		13		Billy		Jones
81		16		Justin		Pitt
72		17		Jacob		Anderson

Source for insert code: http://db.apache.org/derby/integrate/plugin_help/derby_app.html

Write a Java program using JDBC to show some of the information in your database(I did an extra program, just for fun).

I connected the player table to the parentcontact table (through the playerid) and I added the player's parent email address to the player's output result.


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

public class showplayer {

	private Connection connect = null;

	private Statement statement = null;

	private ResultSet resultSet = null;

       public showplayer() throws Exception {

		try {Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
		connect = DriverManager
				.getConnection("jdbc:derby:/Apache/db-derby-10.10.1.1-bin/bin/team190/;create=true");
		
				
		String selectString="SELECT e.playerid, e.playername,e.playerlastname,g.email FROM player e, parentcontact g where e.playerid=g.playerid";

		PreparedStatement statementQ = connect.prepareStatement(selectString);

		resultSet = statementQ.executeQuery();

		System.out.println("The player id, name and email address:");

		while (resultSet.next()) {

			int playerid = resultSet.getInt("playerid");

			String playername = resultSet.getString("playername");

			String playerlastname=resultSet.getString("playerlastname");

			String email=resultSet.getString("email");


			System.out.println("PlayerID: " + playerid+"   "+"Name: "+playername+" "+playerlastname+"   " +"Email: "+email);
		}

		} catch (Exception e) {

			throw e;

		} finally {

			close();
		}
	}
	private void close() {

		try {

			if (resultSet != null) {

				resultSet.close();

			}

			if (statement != null) {

				statement.close();

			}

			if (connect != null) {

				connect.close();

			}

		} catch (Exception e) {
	}

	}

	public static void main(String[] args) throws Exception {

		showplayer dao = new showplayer();
	}

	}

Program Output


The player id, name and email address:
PlayerID: 11   Name: Tyler Smith   	  Email :j.smith@aol.com
PlayerID:  6   Name: Frank Taylords       Email :james@bestbaseball.com
PlayerID:  9   Name: Trey Meie           Email :msmith@aol.com
PlayerID:  4   Name: Parker Mogen   	  Email :mmogensen@msn.com
PlayerID: 22   Name: Gianni Hampton   	  Email :phamora@aol.com
PlayerID: 31   Name: Chris Cass           Email :cassfamily@aol.com
PlayerID: 35   Name: Kai Johnson          Email :billjohnson@msn.com
PlayerID:  8   Name: Patrick Cartersen    Email :rcarter@aol.com
PlayerID: 19   Name: Danny Brown          Email :mbrownh@msn.com