PROJECT - Java Database (jdbc)  Solution

                                               
Objective        To create a database, insert records into it then retrieve data from it for evaluation.

PROJECT DESCRIPTION

You will create an inventory database to store inventory items (or record data) into a table. Then you will retrieve the records and evaluate the inventory items in a Last In First Out or LIFO manner to determine the Cost of Goods Sold for the particular company and Gross Profit Margin.
 
Information about This Project

            This program illustrates an example of Java database processing and a stack based class.

Steps To Complete This Project

STEP 1             Start Eclipse   

Create a Java Project in Eclipse and name it Inventory.


STEP 2             Add a class for jdbc processing

            Create a class called JDBC in your project. You will add in code shortly to create

            a database table within an existing database called Inventory located on the                                            papanet server.

 

STEP 3             Download and install mysql driver

 

            Go to the following website to download and install the jdbc windows driver. This                                    driver is necessary to allow you to connect a mysql database off a server.

                                    http://dev.mysql.com/downloads/connector/j/

            Choose Microsoft Windows under the dropbox where it suggest you to Select a                                Platform (shown below).  If you are a Mac user, choose from the drop down menu

            Platform Independent.

 

            Click Download at this point.

 



 

                       The site will take you to a page to create a free Oracle Web Account. You can                                           bypass this by clicking the link at the bottom of the page where it says

                             No thanks, just start my download. 

 

(Windows users): At this point save the mysql-connector-java-gpl-5.1.33.msi file to your download folder. When the download is complete go to your download folder and double click onto your file to install the msi file.  The msi installation places a jar file- mysql-connector-java-5.1.33-bin.jar (you’ll need to add this to your project momentarily, in a lib folder for your project) located at the following path:

           C:\Program Files (x86)\MySQL\Connector J

           

(Mac users)- you need to install the driver (.jar) file as well and place it into your lib folder as

per the specs above. At this point choose to download the .tar file or .zip file. Choose either one as both should open on the latest Mac platform. When the folder either untars or unzips depending on what you choose as a file type to download, you should see the needed 

mysql-connector-java-5.1.33-bin.jar file for jdbc connectivity.

 

(All platforms): Right click on your mysql-connector-java-5.1.33-bin.jar file and choose Copy. Then go to your to your Inventory project in Eclipse and right click on the Inventory root folder and choose New Folder. Type in lib in the Folder name: area as shown below. Click Finish when complete. 

 

 



            Now right click on your lib folder and choose Paste. You will now notice your .jar                                     file has been copied in.

 

            Now to officially add this jar to your build path so its recognized at compile time,                                                 right click on your Inventory root folder and choose Properties. On the left hand                                      side click on Java Build Path. Then click on the Libraries tab at the top center area                                   then click on Add JARS... as shown below...



                       

            At the JAR Selection dialog, drill down your Inventory folder to your lib folder and            click on your mysql-connector-java-5.1.33-bin.jar file. Click OK at this point.  Click       OK as well again to close out of your Properties dialog box.

 



Now onto the next step for some coding.

 

STEP 4             Open up your JDBC class file

 

            In your JDBC code enter the following code in to create a database table:

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

 

public class JDBC {

         private Connection connect = null;

         private Statement statement = null;

 

         public void createDataBase() throws Exception {

           try {

             // This will load the MySQL driver, each DB has its own driver

             Class.forName("com.mysql.jdbc.Driver");

             // Setup the connection with the DB

             connect = DriverManager

                 .getConnection("jdbc:mysql://www.papademas.net/Inventory?"

                     + "user=root&password=jamesp");

            

             //create table

          

             statement = connect.createStatement();

            

             String sql = "CREATE TABLE jpapaInventory " +

                          "(id INTEGER not NULL AUTO_INCREMENT, " +

                          " cost INTEGER, " +

                          " PRIMARY KEY ( id ))";

 

             statement.executeUpdate(sql);

             System.out.println("Created table in given database...");

 

              //end create table

           } catch (Exception e) {

              System.out.println(e.getMessage()); 

           } 

           }

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

               JDBC dao = new JDBC();

               dao.createDataBase();

             }

}

 

NOTE- PLEASE REPLACE IN THE CODE ABOVE, THE TABLE NAME. INCLUDE YOUR FIRST INTIAL

+ YOUR FIRST 4 LETTERS OF YOUR LAST NAME + INVENTORY AS YOUR TABLE NAME.

NOTICE MY EXAMPLE ABOVE WAS  jpapaInventory. PLEASE CHANGE THIS TO YOUR

INFORMATION BEFORE RUNNING YOUR APP INITIALLY!
 

STEP 5             Run your initial application.

 

Run your program and see if your table was created. If it was proceed to the next           step otherwise correct your syntax/sql statements for any apparent errors. Check        also if any firewalls maybe blocking you when you are creating the table if so as a   thought.

 

Snapshot your output window when complete.

 

STEP 6             Insert records into your table.

           

                        After successfully creating your table, you can now comment out the line in main                                  that calls your createDataBase() method.

 

                        Create the following method, insertIntoDataBase(), in your class to allow for inserts                        to be added to your database. Starter code is as follows:

 

public void insertIntoDataBase() throws Exception {

           try {

               // This will load the MySQL driver, each DB has its own driver

              Class.forName("com.mysql.jdbc.Driver");

              // Setup the connection with the DB

               connect = DriverManager

                        .getConnection("jdbc:mysql://www.papademas.net/Inventory?"

                                   + "user=root&password=jamesp");

                          

                System.out.println("Inserting records into the table...");

                statement = connect.createStatement();

                          

                String sql = "INSERT INTO jpapaInventory(cost) " +

                             "VALUES (400)";

                statement.executeUpdate(sql);

                sql = "INSERT INTO jpapaInventory(cost) " +

                      "VALUES (400)";

                statement.executeUpdate(sql);

                          

                System.out.println("Inserted records into the table...");

      

                } catch (Exception e) {

                  System.out.println(e.getMessage()); 

                } 

         }

**OF COURSE, ONCE AGAIN, REPLACE MY EXAMPLE TABLE NAME IN THE INSERT SQL   STATEMENTS WITH YOUR TABLE NAME.  

 

In a similar manner as above, go back into your code and also add in three more records             for the cost field, in the amounts of 500, 500 and 600 respectively within your method.

 

To finish with your class file, add in a call to your new method in main via your dao object             created in main, save your work and run your application. You should be seeing that the records have been inserted successfully in your Console window. If not please check your        SQL statements carefully for any errors.

 

Additional help on sql statements, for selects, updates, inserts, deletes, etc. can be found            at this rockin’ site:

 

http://www.tutalspoint.com/jdbc

 

Snapshot your output window when complete.

 

Now onto the next step to process your inventory items in a new class file.

 

STEP 7             Create class file to process inventory items by cost.

 

Create a new class called LIFO.  This class will read from the database and determine     the profit and value of the inventory based on the LIFO method.

 

For a company’s accounting and financial & tax statements, inventory items can be          evaluated and represented with either the LIFO or FIFO valuation method.

 

Consider the following accounting scenario comparing LIFO and FIFO which will serve as the logic for your LIFO class...

 

Your company bought 5 identical computers during the year at prices: $400, $400, $500, $500, $600; and then they sold 3 of them for $800 each. What is the profit? What is the          value of the inventory?  The answer depends on which computers were sold. Although the     computers are physically identical, their accounting values are different. The following are            different calculations using FIFO and LIFO accounting.

 

 
LIFO
FIFO
Costs of Goods Sold
600 + 500 + 500 = 1600
400 + 400 + 500 = 1300
Profit
2400 – 1600 = 800
2400 – 1300 = 1100
Ending Inventory
400 + 400 = 800
500 + 600 = 1100
 

Notice under the LIFO method, the last items in (3 in this case) are evaluated first           given 3 items sold. The FIFO method evaluates the first 3 items in. Notice how Cost of Goods Sold (COGS), Profit, and Ending Inventory are calculated under each method. You            can see that under the FIFO method more profit is generated.  Can you guess why? Note    that although FIFO generates more profit, there will be more profit subject to tax.

 

Add in the following starter code to your LIFO class:

 

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.util.Arrays;

 

import com.mysql.jdbc.Connection;

import com.mysql.jdbc.Statement;

 

 

public class LIFO <T

{       

      private static Connection connect = null;

      private static Statement statement = null;

      private static ResultSet resultSet = null;

  

      private static int count;

      private T[] data;

 

    public LIFO()

    {

        data = (T[]) new Object[5];

        count = 0;

    }

 

    void expandCapacity()

    {

        data = Arrays.copyOf(data, data.length * 2);

    }

 

    void push(T e)

    {

        if (count == data.length)

            expandCapacity();

        data[count++] = e;

    }

 

    T pop() throws Exception

    {

        if (count <= 0)

        {

            throw new Exception("stack empty");

        }

        count--;

        return data[count];

    }

 

    boolean isEmpty()

    {

        return count == 0;

    }

 

  

 static int size()

    {

        return count;

    }

 

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

    {

        LIFO<Integer s = new LIFO<Integer();

        

        try {

               // This will load the MySQL driver, each DB has its own driver

                    Class.forName("com.mysql.jdbc.Driver");

                    // Setup the connection with the DB

                    connect = (Connection) DriverManager

                        .getConnection("jdbc:mysql://www.papademas.net/Inventory?"

                            + "user=root&password=jamesp");

                    // Statements allow to issue SQL queries to the database

                    statement = (Statement) connect.createStatement();

                    // Result set gets the result of the SQL query

                    resultSet = statement

                        .executeQuery("select cost from jpapaInventory");

                   // ResultSet is initially before the first data set                   

                   while (resultSet.next()) {

                     /* column data may be retrieved via name

                     e.g. resultSet.getString("name");

                     or via the column number which starts at 1

                     e.g. resultSet.getString(1); */

                      int cost = resultSet.getInt(1);  //retrieve cost

                      s.push(cost); //push cost value onto stack

                          

                    System.out.println("Push item : " + s.count + " " + cost);

                     }

                  

                     while (!s.isEmpty())//pop values from stack

                     System.out.println("Pop iem : " + s.count + " " + s.pop());

 

           } catch (Exception e) {

              System.out.println(e.getMessage()); 

           } 

 

    }

}

**PLEASE MAKE SURE TO CHANGE THE TABLE NAME AGAIN TO YOUR TABLE NAME IN THE SELECT QUERY STATEMENT (SHOWN BELOW)

 

             resultSet = statement

                        .executeQuery("select cost from jpapaInventory");

 

 

Run your LIFO application and verify your results against a sample running application     below.

 

 



                        Notice how the stack process works here for our LIFO class from the snapshot above which                     really represents a stack’s logic namely to push values onto a stack one by one, but when                        we get or pop values from the stack the last items in are actually the first popped. This is                  perfect for our inventory valuation for the LIFO method! 

 

                        So you see when we call the push() method in our code we are adding onto the                                    class stack (stored into the array variable data which can handle any type thus a                                   generic type T is given (array declaration shown below) for each record value we retrieve                      from the resultSet query.

                       

                        private T[] data;

 

                        Then after the loop ends we call the pop() method to view each item added into the class                      stack.

 

                        Notice the outcomes or order how the cost items are pushed in the stack then popped or                         reversed when displayed. We got it right!

 

Okay –please study over the code mechanics!  This is a beautiful, classic example of a     stack. Especially notice the push and pop methods. Each serve an obvious purpose.  Push         takes in a value (a setter method really) and pop returns a given value (a getter method).   Notice that a count variable MUST be included to keep track of the array’s elements     position to determine where the values should be pushed onto the stack if possible and             where they are to be popped from the stack if possible.  Further notice the creation of a      generic object type in main namely for the object s as shown below,

 

       LIFO<Integer s = new LIFO<Integer();

 

Notice any feasible reference type can be used to grab data for the class. In our example we just will use the Integer type to retrieve the cost value to store from our table. Of course we didn’t have to use generics for this exercise, but a working knowledge of generics is very useful for code efficiency, thus we have incorporated this into our example!

 

Take a snapshot of the Console window to show the stacks’ pushed and popped values thus far as a checkpoint.
 

Okay now onto our last steps to modify a few things!

 

STEP 8             Modify your LIFO class to properly evaluate your inventory items.

 

Okay now you have printed out your stack, you can comment out those lines as you know           how things work and that you were successful in grabbing data then displaying it for each      cost item added to the class stack.

 

Go back to your LIFO class code and include any additional variables and calculations to

determine the following information for your company:

 

1.     Determine the Goods Sold (COGS) under the LIFO method

2.     Determine the Profit under the LIFO method

3.     Determine the Ending Inventory under the LIFO method

4.     Determine also the Inventory Turnover (which is a ratio) for the company which helps measure the number of times that the company sold its inventory during the year.

           

                        Note the Inventory Turnover formula is as follows:

 

                        Inventory Turnover = COGS/Average Inventory

 

                        where

 

                        Average Inventory = (Beginning Inventory amounts + Ending Inventory amounts)/2

 

                        Read over the scenario given again in step 7 to refresh your thinking on how to create your                     calculations. Remember the company orignally purchased 2400 in merchandise items                               (serves as your Begnning Inventory), but sold x amount of those items, which depending                        on the inventory evaluation method chosen, leads us to a different COGS scenario under                              LIFO vs. FIFO, which of course leads us to a different profit and thus ending inventory                              amounts.

 

Run your app and test your results and verify that your are displaying output for each calculation mentioned above for COGS, Profit, Ending Inventory and the Inventory Turnover ratio.

 

 

Take a screen snapshot of your results produced.
 

 

STEP 9             Submission Requirements the Application

 

Submit for full credit all your screen snapshots as well as your completed program code for          each java file listed for this lab.

 

 
Powered by