Accessing a Java Database

[WMain00]

Hi there, first time poster here looking for some really good help!

My problem is this. Our project is a Java program that allows a user to choose parts for a wind turbine, calculate the total cost of the parts, and save/output this contract information as a .txt file for printing later. The parts exist on a precreated database, that can be updated via this program as well. The database is a Java Database that was created in NetBeans using the JDB tools. The entire program is in NetBeans.

Now, here comes the question! When the user wants to create a new contract, he presses the new contract button as displayed via a JMenu; this part functions without problem. A JInternalFrame shows up showing the user four key sections; the name/address panel, the contract panel, the button panel, and the parts chosen panel. The user types in the address details.

With the parts panel though I want it to access the database, entitled PartsData. Inside PartsData are three columns; Parts Code, Description, Price. What i want the program to do is when the user chooses the part code, which is currently a JComboBox, the code number is displayed in the box, and then the corresponding part description and price to that code are entered into the JTextFields that they belong to within the panel. Is this at all possible?

So to summarise:

-I have a Java Database table entitled PartsData
-I have a panel entitled PartsPanel
-I want to know how to link the database into my PartsPanel
-I want to then be able to load in the corresponding descriptions etc with the Parts Code that is chosen in the ComboBox
-All this is being done in NetBeans

Here is the code for PartsPanel.java to help. Since the rest of the stuff (The JFrame, internal frames setup) doesn't manipulate anything in the code other than to set up the display of the screen, i figure this is where the code to get the database implemented needs to go. Any help would be absolutely fantastic, as the Project is running out of time

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.weces.programParts;

/**
 *
 * @author 
 */

//Wind Turbines Project 2009 
//Date Amended: 9th July 2009
//Current Author: William A. Main

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import java.sql.*;

public class PartsPanel extends JPanel {
    

    private JLabel partName;
    private JComboBox partBox;
    private JPanel partPanel;

    private JLabel partCode;
    public JTextField partCodeField;
    private JPanel partCodePanel;

    private JLabel partDescription;
    public JTextField partDescriptionField;
    private JPanel partDescriptionPanel;

    private JLabel priceLabel;
    public JTextField priceField;
    private JPanel pricePanel;

    private JLabel totalPrice;
    private JLabel totalPriceField;
    public JPanel totalPricePanel;

    private JLabel quantityLabel;
    private JTextField quantityField;
    private JPanel quantityPanel;
    private JPanel tidyPanel;

    public PartsPanel()
    {
        tidyPanel = new JPanel();
        tidyPanel.setLayout(new GridLayout(6,0));
        partName = new JLabel("Part Code: ");
        partBox = new JComboBox(
                );
        partPanel = new JPanel();
        partPanel.setLayout(new BorderLayout());
        partPanel.add(partName, BorderLayout.WEST);
        partPanel.add(partBox, BorderLayout.EAST);
        tidyPanel.add(partPanel);

        partCode = new JLabel("Part Name: ");
        partCodeField = new JTextField();
        partCodeField.setEditable(true);
        partCodeField.setPreferredSize(new Dimension(200, 10));
        partCodePanel = new JPanel();
        partCodePanel.setLayout(new BorderLayout());
        partCodePanel.add(partCode, BorderLayout.WEST);
        partCodePanel.add(partCodeField, BorderLayout.EAST);
        tidyPanel.add(partCodePanel);


        partDescription = new JLabel("Part Description: ");
        partDescriptionField = new JTextField();
        partDescriptionField.setEditable(false);
        partDescriptionField.setPreferredSize(new Dimension(200, 10));
        partDescriptionPanel = new JPanel();
        partDescriptionPanel.setLayout(new BorderLayout());
        partDescriptionPanel.add(partDescription, BorderLayout.WEST);
        partDescriptionPanel.add(partDescriptionField, BorderLayout.EAST);
        tidyPanel.add(partDescriptionPanel);

        priceLabel = new JLabel("Price: ");
        //BEING USED IN PARTS EDITING FRAME AS WELL
        priceField = new JTextField();
        priceField.setEditable(false);
        priceField.setPreferredSize(new Dimension(200, 10));
        //BEING USED IN PARTS EDITING FRAME AS WELL
        pricePanel = new JPanel();
        pricePanel.setLayout(new BorderLayout());
        pricePanel.add(priceLabel, BorderLayout.WEST);
        pricePanel.add(priceField, BorderLayout.EAST);
        tidyPanel.add(pricePanel);

        quantityLabel = new JLabel("Quantity: ");
        quantityField = new JTextField();
        quantityField.setEditable(true);
        quantityField.setPreferredSize(new Dimension(200, 10));
        quantityPanel = new JPanel();
        quantityPanel.setLayout(new BorderLayout());
        quantityPanel.add(quantityLabel, BorderLayout.WEST);
        quantityPanel.add(quantityField, BorderLayout.EAST);
        tidyPanel.add(quantityPanel);

        totalPrice = new JLabel("Total Price: "); // sum of each part times the quantity required of each part
        totalPriceField = new JLabel();
        totalPriceField.setPreferredSize(new Dimension(200, 10));
        totalPricePanel = new JPanel();
        totalPricePanel.setLayout(new BorderLayout());
        totalPricePanel.add(totalPrice, BorderLayout.WEST);
        totalPricePanel.add(totalPriceField, BorderLayout.EAST);
        tidyPanel.add(totalPricePanel);



        setLayout(new BorderLayout());
        add(tidyPanel, BorderLayout.CENTER);
        tidyPanel.setVisible(true);
        setVisible(true);

    }

}

[compavalanche]

I don't think there is such a thing as a 'Java Database.' Is it starting an instance of something else?

Can you give an example of how you are currently interacting with it?

[keang]

WMain00 I suggest you google for and read some of the online tutorials such as this one. If you still have problems post the code you are using to connect to and access the DB and someone will help.

I don't think there is such a thing as a 'Java Database.'

There's a relational database called Derby which is written in Java which I believe is integrated into Netbeans, so I guess, this is what the OP is referring to.

[WMain00]

Yes i'm using Derby. Should have probably made that clearer.

The link given i've read, but there are parts in it i do not understand. For instance where do i place the Class.ForName code?

I will however try and do it there way. If i can make that program function, i might be able to incorporate it into what i'm doing.

Thanks.

[keang]

For instance where do i place the Class.ForName code?

This code loads the database driver and so needs to called before you attempt to connect to the database. It's probably best done during application initialisation but as for where you do it, that depends on your design.

[WMain00]

I have hit a problem. Using the Address Book example as given earlier, i have successfuly managed to edit it and change it to what i need, and it is functioning fully without problems. My problem though is that i need not just one of these books, but two. One for storing the customer information, the other for storing product parts. My intention being (presuming it's even possible) to make both version JInternalFrame's and place them into a DesktopPane main class.

I have attempted to create a new package and re-edit it as neccessary to represent Parts. This includes changing the variables to "Part Name", "Part Description", etc. When i attempt to run it though, i receive the following error:

java.sql.SQLException: Table/View 'APP.PARTS' does not exist.

Similarly i am unable to use the term APP.ADDRESS either. I don't know what i'm doing wrong, as the code seems to be correct. I noticed that if i were to change the name to say APP.RUBBISH in the working address book version, the same error occurs, until i change it back to what it was originally.

How do i go about resolving this problem?

[dlorde]

Are you sure you're using the correct names in your application for the database tables & columns you're trying to use?

Computer Science is a science of abstraction -creating the right model for a problem and devising the appropriate mechanizable techniques to solve it...
A. Aho and J. Ullman

[WMain00]

Are you sure you're using the correct names in your application for the database tables & columns you're trying to use?

Computer Science is a science of abstraction -creating the right model for a problem and devising the appropriate mechanizable techniques to solve it...
A. Aho and J. Ullman

I believe so, yes.

[WMain00]

After doing a debug, it seems to all go pear shaped here:

    public boolean connect() {
        String dbUrl = getDatabaseUrl();
        try {
            dbConnection1 = DriverManager.getConnection(dbUrl, dbProperties1);
            stmtSaveNewRecord = dbConnection1.prepareStatement(strSaveAddress, Statement.RETURN_GENERATED_KEYS);
            stmtUpdateExistingRecord = dbConnection1.prepareStatement(strUpdateAddress);
            stmtGetAddress = dbConnection1.prepareStatement(strGetAddress);
            stmtDeleteAddress = dbConnection1.prepareStatement(strDeleteAddress);   
            isConnected = dbConnection1 != null;
        } catch (SQLException ex) {
            isConnected = false;
        }
        return isConnected;
    }

Specifically here:

stmtSaveNewRecord = dbConnection1.prepareStatement(strSaveAddress, Statement.RETURN_GENERATED_KEYS);

The next line it goes to is the catch below it:

        } catch (SQLException ex) {
            isConnected = false;
        }
        return isConnected;
    }

Which returns the exception.

EDIT:

On further study the reason for this seems to be that it's attempting to insert data into a table that doesn't exist, because for some reason the table is not being created at this part:

    private boolean createTables(Connection dbConnection) {
        boolean bCreatedTables = false;
        Statement statement = null;
        try {
            statement = dbConnection.createStatement();
            statement.execute(strCreatePartsTable);
            bCreatedTables = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return bCreatedTables;

That part should refer to the SQL and create a table named APP.PARTS, but it doesn't. Studying the debug at the moment.

[dlorde]

If you post the strCreatePartsTable string and the full exception message and stack trace, we might be able to suggest something...

The value of a prototype is in the education it gives you, not in the code itself...
A. Cooper

[WMain00]

The strCreatePartsTable is this:

  private static final String strCreatePartsTable =
            "create table APP.PARTS (" +
            "    ID          INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
            "    PARTCODE    VARCHAR(30), " +
            "    PARTNAME   VARCHAR(30), " +
            "    PARTDESCRIPTION  VARCHAR(30), " +
            "    PARTPRICE       VARCHAR(20), " +
            "    PARTQUANTITY       VARCHAR(30), " +
            "    PARTCOUNTRY    VARCHAR(30), " +
            ")";

The exception is this:

init:
deps-jar:
compile-single:
run-single:
java.sql.SQLException: Table/View 'APP.PARTS' does not exist.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
        at DataFiles.PartsData.getListEntries(PartsData.java:229)
        at org.weces.partsData.PartsFrame.<init>(PartsFrame.java:36)
        at org.weces.partsData.PartsFrame.main(PartsFrame.java:162)
Caused by: ERROR 42X05: Table/View 'APP.PARTS' does not exist.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)
        at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)
        at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
        at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
        at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
        ... 5 more
BUILD SUCCESSFUL (total time: 2 seconds)

[dlorde]

I think the problem may be that an ordinary identifier like a table or column name must begin with a letter and contain only letters, underscore characters, and digits. See Rules for SQL92 identifiers.

I suggest you try replacing the '.' in the name with an underscore ('_').

If you don't think carefully, you might believe that programming is just typing statements in a programming language...
W. Cunningham

[WMain00]

That changes the error to:

init:
deps-jar:
compile-single:
run-single:
java.sql.SQLException: Schema 'TURBINEUSER' does not exist
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
        at DataFiles.CustData.getListEntries(CustData.java:243)
        at org.weces.customerData.CustomerFrame.<init>(CustomerFrame.java:36)
        at org.weces.customerData.CustomerFrame.main(CustomerFrame.java:166)
Caused by: ERROR 42Y07: Schema 'TURBINEUSER' does not exist
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSchemaDescriptor(Unknown Source)
        at org.apache.derby.impl.sql.compile.QueryTreeNode.getSchemaDescriptor(Unknown Source)
        at org.apache.derby.impl.sql.compile.QueryTreeNode.getSchemaDescriptor(Unknown Source)
        at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)
        at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)
        at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
        at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
        at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
        ... 5 more
BUILD SUCCESSFUL (total time: 8 seconds)

APP being a schema.


EDIT:

Could it be something to do with the Configuration.properties file? This was included in the original address book package and i moved it on and change it to represent what i want my database to do, but i'm noticing that if i change it absolute rubbish, the customer database still runs fine.

The file contains this

# Sample ResourceBundle properties file
user=turbineuser
password=turbineuser
derby.driver=org.apache.derby.jdbc.EmbeddedDriver
derby.url=jdbc:derby:
db.table=ADDRESS
db.table=PARTS
db.schema=APP

Resource bundle file?

[dlorde]

It will probably be quicker if you check the Derby docs before posting here. See the Derby FAQ.

If I had eight hours to chop down a tree, I would spend 6 hours sharpening an axe...
Anonymous

[WMain00]

Ok but i'm a novice to this. Could you explain to me exactly how i create a schema?