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