Home  | 

 

ONLINE SESSION D 

 

                               Verify that Derby is correctly installed on your computer  - IF YOU HAVE A PROBLEM READ THE DERBY INSTALL NOTES BELOW

// ALL YOU HAVE TO DO IS REPLACE THE SELECT CLAUSE AND REPLACE THE printf STATEMENT

//**********************************************************************************
// REPLACE THE FOLLOWING SELECT WITH THE QUERY YOU WRITE FROM THE FORTA TEXT
   String sqlStatement     =   "SELECT ProdId, ProdPrice FROM Products " ;

//**********************************************************************************

//***********************************************************************************
// REPLACE THE FOLLOWING CODE WITH THE CORRECT FORMATS AND DATA NAMES FROM YOUR QUERY
   System.out.printf("%10s %7.2f\n",
                result.getString("ProdId"),
                result.getDouble("ProdPrice"));
//***********************************************************************************

 

// YOU WILL HAVE TO LOOK AT THE MysqlCreateDB JAVA PROGRAM TO GET THE NEEDED;
// FIELD NAMES FOR THE SELECT AND FIELD TYPES AND FIELD SIZES FOR THE printf


 

 Run the following listed queries from the FORTA textbook using JDBC:

 

chapter 4 - p30, p32, p35 [1st in middle]

 

chapter 5 - p38, p39, p40, p41

 

chapter 6 - p47 [both], p48 [both], p50

 

chapter 7 - p53, p54, p56, p57, p58, p59

 

chapter 8 - p62,  p63 [1st in middle]

 

chapter 12 - p100, p102, p103 [both], p104, p105, p106, p107, p108

 

CRITICAL INFORMATION FOR WEB D SUBMISSION TO GUYNES.

I sent you the 3 files you need to solve WEB D.

1. the create database file

2. the single query example for test purposes

3. the   GuynesMysql.java   program you use to submit to me

after you write each of your individual queries and test them with the single query example program,
YOU MUST PUT THEM ALL IN A SINGLE JAVA PROGRAM WHICH YOU WILL EMAIL TO ME TO GRADE [NOT ANNA]

SIMPLY USE THE GuynesMysql.java program as a starting point and insert all of your queries in it as shown in the program.

YOU MUST CHANGE THE NAME OF THE JAVA PROGRAM AND THE NAME OF THE public class,
AS I SHOW YOU IN THE GuynesMysql.java CODE.

REMEMBER - if you wish you can use VMWARE to access the DERBY database in the labs
or just install it on your own computer. 

 

DERBY INSTALL NOTES

IF YOU WANT TO TRY TO USE THE JAVA VERSION YOU HAVE ON YOUR OWN COMPUTER YOU CAN TRY THE FOLLOWING


1. Open your browser and go the following address:

                http://db.apache.org/derby/derby_downloads.html

2. Click: For Java 9 and Higher

3. Click on the first link ending with bin.zip.

4. Save it [do not open yet]

           The ZIP file that you just downloaded contains a folder with a name 'similiar to'    db-derby-10.15.1.3-bin.

2. Extract that folder and its contents to YOUR C: DRIVE giving:                                       C:\db-derby-10.15.1.3-bin.

3. You will need to add a CLASSPATH environment as follows:

                                                                                                                                                           C:\db-derby-10.15.1.3-bin\lib\derby.jar

4. Test your install with the following java programs.

                                 FIRST: MysqlCreateDB.java
                                  THEN: MysqlExample.java

Following is the     GuynesMysql.java     code for you to look at - I sent it to you earlier.

// all you have to do is add SELECT CLAUSES and replace the printf STATEMENTS

// YOU WILL HAVE TO LOOK AT THE "MysqlCreateDB" JAVA PROGRAM TO GET THE NEEDED
// FIELD NAMES FOR THE SELECTS AND FIELD TYPES AND FIELD SIZES FOR THE printf statements

// you must name the class your LAST NAME with Mysql - for example - GuynesMysql
// and the name of the java program to match - for example GuynesMysql.java

import java.util.Scanner;
import java.sql.*;
public class GuynesMysql
{
public static void main(String[] args)
{
// Create a named constant for the URL.
final String DB_URL = "jdbc:derby:MysqlDB";
try
{
// Create a connection to the database.
Connection conn = DriverManager.getConnection(DB_URL);
// Create a Statement object.
Statement stmt = conn.createStatement();
// leave the above code alone except for renaming the public class statement




// replace the following SELECT with the 1ST QUERY and name it sqlStatement1
String sqlStatement1 =
"SELECT COUNT(*) AS num_item, MIN(prodprice) AS price_min, MAX(prodprice) AS price_max, AVG(prodprice) AS price_avg FROM products" ;

// name the results from the query result1
ResultSet result1 = stmt.executeQuery(sqlStatement1);
System.out.println(" ");
System.out.println("following is the output from result1");
while (result1.next())
{
// REPLACE THE FOLLOWING CODE WITH THE CORRECT FORMATS AND DATA NAMES FROM YOUR QUERY
System.out.printf("%25s %25s %25s %25s\n",
result1.getString("num_item"),
result1.getString("price_min"),
result1.getString("price_max"),
result1.getString("price_avg"));
}




// replace the following SELECT with the 2ND QUERY and name it sqlStatement2
String sqlStatement2 =
"SELECT ProdId, ProdPrice FROM Products " ;

// name the results from the query result2
ResultSet result2 = stmt.executeQuery(sqlStatement2);
System.out.println(" ");
System.out.println("following is the output from result2");
while (result2.next())
{
// REPLACE THE FOLLOWING CODE WITH THE CORRECT FORMATS AND DATA NAMES FROM YOUR QUERY
System.out.printf("%10s %8.2f\n",
result2.getString("ProdId"),
result2.getDouble("ProdPrice"));
}




// LEAVE THE REST OF THE CODE ALONE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
conn.close();
}
catch(Exception ex)
{
System.out.println("ERROR: " + ex.getMessage());
}
}
}