Solved

Passing textfield info to connection string

Posted on 2011-02-27
7
606 Views
Last Modified: 2013-11-11
I have been working on a side project (as practice) and could use some advice.

I have been trying to create a database table In Oracle 11g using Java.  For that part, except for a minor issue, I have been successful.  I am able to create a table.  The problem I am having now is that I want to use credentials based on user input (as opposed to using static text) - and to keep that same info private.

I get the same results every time: first, an invalid login error without waiting for user input and second, the user input is not accepted.  

The minor issue has more to do with an sql error than anything else: prior to the table's creation, I drop it. I know I could do a "create or replace table" but that's not what I want.  Is there a for java to know whether or not a table exists? I know this in PL/SQL returns t(true) or f(false) for whatever tbale you are looking for:
 
SELECT DECODE(count(*),0,'F','T')
  FROM
    user_tables
  WHERE
    table_name = 'table name';

Open in new window

The question is however, how to translate that into seomthing java understands so that I can use it in a condition statement.

In any case here is what I have so far.  It's not perfect and probably not very efficient or secure.  I welcome any suggestions, thank you.

/*
 * Glen A. Currier
 * Video DB Project
 * "VideoLdrMain.java"
 * 26.02.2011
 */
package Loader;

import java.sql.*;
import javax.swing.JFrame;

public class VideoLdrMain {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        JFrame frame = new DBAuthenticate();
        frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        frame.setTitle("Database Authentication");
        frame.setResizable(false);
        frame.setLocationRelativeTo(null);
        frame.pack();
        frame.setVisible(true);
        
        new DBCreateTable();

    }
}

Open in new window

VideoLdrMain.java

package Loader;

public class VideoDBTable extends DBAuthenticate{

    public static final String table_maint = "drop table videos";
    public static final String video_table =
            "create table videos ("
            + "id INT PRIMARY KEY not null,"
            + "type   varchar2(20),"
            + "genre1 VARCHAR(20),"
            + "genre2 VARCHAR(20),"
            + "genre3 VARCHAR(20),"
            + "title  VARCHAR(20), "
            + "year   VARCHAR(20),"
            + "video  BLOB,"
            + "uploaded date"
            + ")";
}

Open in new window

VideoDBTable.java

package Loader;

import java.awt.BorderLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JTextArea;
import javax.swing.JTextField;

public class DBAuthenticate extends JFrame implements ActionListener  {

    private static JTextField userAuth;
    private static JPasswordField pwordAuth;
    private static JLabel userName, passWord;
    private static JPanel helpPanel, authPanel, okPanel, combinedPanel;
    private static JTextArea helpText;
    private JButton okButton;
    static String getUser, getPass;


    public DBAuthenticate() {

        //create panels
        helpPanel = new JPanel(new GridLayout(1, 1));
        authPanel = new JPanel(new GridLayout(2, 2));
        okPanel = new JPanel(new GridLayout(1, 1));
        combinedPanel = new JPanel(new BorderLayout());

        //create items that go into panels
        helpText = new JTextArea("Please enter your credentials and press \"OK\"");

        userName = new JLabel("User:      ");
        userAuth = new JTextField();
        passWord = new JLabel("Password:  ");
        pwordAuth = new JPasswordField();

        okButton = new JButton("OK");
        okButton.addActionListener((ActionListener) this);

        //add items to panels
        helpPanel.add(helpText);

        authPanel.add(userName);
        authPanel.add(userAuth);
        authPanel.add(passWord);
        authPanel.add(pwordAuth);

        okPanel.add(okButton);

        //add panels to combinedPanel
        combinedPanel.add(helpPanel, BorderLayout.NORTH);
        combinedPanel.add(authPanel, BorderLayout.CENTER);
        combinedPanel.add(okPanel, BorderLayout.SOUTH);

        //add combinedPanel to JFrame
        add(combinedPanel);

        //actionListener
//        ListenerClass listen = new ListenerClass();
//        okButton.addActionListener(listen);

    
        
    }
    
    
    public void actionPerformed(ActionEvent e){
        if(e.getSource()==okButton){
            this.getUser = userAuth.getText();
            this.getPass = pwordAuth.getSelectedText();
            
            try {
                getDBConnection();
            } catch (Exception ee) {
                System.out.println("Error during button click event");
                ee.printStackTrace();
            }            
        }
    }
    
    public Connection getDBConnection() throws ClassNotFoundException, SQLException {
        
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521/orcl";
        String user = getUser;
        String password = getPass;
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, user, password);
        conn.setAutoCommit(false);
        return conn;
    }

    
//    public class DBConnection{
//
//        private String user, password;
//
//        public void DBConnection(){}
//
//        public void DBConnection(String user,String password){
//            setUser(user);
//            setPass(password);
//        }
//
//        String getUser(String user){
//            return this.user;
//        }
//
//        String getPass(String password){
//            return this.password;
//        }
//
//        void setUser(){
//            this.user = user;
//        }
//
//        void setPass(){
//            this.password = password;
//        }
//
//    }

}

Open in new window

DBAuthenticate.java

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

package Loader;


import java.sql.Connection;
import java.sql.SQLException;
//import java.sql.ClassNotFoundException;
import java.sql.Statement;

/**
 *
 * @author gcurrier
 */
public class DBCreateTable extends DBAuthenticate{

    public DBCreateTable()throws ClassNotFoundException,SQLException {
        Connection conn = super.getDBConnection();
        Statement stmt = null;
        VideoDBTable vTable = null;
        try {
            //create a connection
            //conn = new super.getDBConnection();
            //prepare a statement
            stmt = conn.createStatement();
            //drop table

            /*
             * error if no table previously defined - program exits (need a fix here)
             */
            
            stmt.execute(vTable.table_maint);
            //create table
            stmt.executeUpdate(vTable.video_table);
            //commit changes
            conn.commit();
            System.out.println("Create table \"videos\" successful.");
        }
//        catch (ClassNotFoundException e) {
//            System.err.println("Failed to load the Oracle driver.");
//            e.printStackTrace();
//        }
        catch (SQLException e) {
            System.err.println("Failed to create a connection to the database.");
            e.printStackTrace();
        } catch (Exception e) {
            System.err.println("other error:");
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (Exception e) {
                System.err.println("Unsuccessful attempt to close connections.");
                e.printStackTrace();
            }
        }
    }

}

Open in new window

DBCreateTable.java    
0
Comment
Question by:g_currier
  • 3
  • 3
7 Comments
 
LVL 5

Assisted Solution

by:jaiminpsoni
jaiminpsoni earned 150 total points
ID: 34992312
To check if the table exists or not...

you can do this...

after creating a statement object, (Where you have commented block)

do this....

Statement stmt = conn.createStatement() ;

ResultSet rs = stmt.executeQuery( "select count(*) from user_tables where table_name = <TABLENAME>" ) ;

while (rs.next()){
          count = res.getInt(1);
}

The above query will always return one record... (The count of 0 or 1 depending on if the table exists)

Your nextflow will go like...

if(count == 0)
{
  //Table not exist
}
else
{
 //table exist
}
0
 
LVL 47

Expert Comment

by:for_yan
ID: 34992352

Well, maybe you post the details of error which you observe - the exception name
and the line which is output by printStackTrace().

There is no reason why you should not be able to use any Strings
in connection string in place of user and password - either jsut something
in the quotes, or static String, or just any other string - so, please locate the details
abut the error - with printStacktrace or with putting System.out.println before
exception - we should definitely figure out this by doing normal step by step
debugging

There are also many ways to determin if table exists -
you can "select table_name from cat where table_name = 'TABLE_NAME'"
and check if you get anything return

You can select * from all_objects where object_name = 'TABLE_NAME" and object_type = 'TABLE'"
(check that the name of filed is "object_type" filrst by selecting * from all_objects,
I'm just writing it from memory)

Then you can just do your select from tthe table which you want to do and catch and analyze
type of SQL  Exception - firts just print stac trace - you'll see what type of SQL exception
you need to look for.

Or you can doe "create table ..." and catch exception, if it cannot create - then you just go forward
as your table already exists, if it can, then - you already created yourr table

In the end why not to "create and replace table...", as SQL gives you this nice option?
I see not great problems with any of that.




 



0
 

Author Comment

by:g_currier
ID: 34992621
This is the error that appears BEFORE the authnetication window pops up:
 
Exception in thread "main" java.sql.SQLException: ORA-01017: invalid username/password; logon denied

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:388)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:381)
        at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:564)
        at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:431)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:366)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:752)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:359)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:185)
        at Loader.DBAuthenticate.getDBConnection(DBAuthenticate.java:96)
        at Loader.DBCreateTable.<init>(DBCreateTable.java:21)
        at Loader.VideoLdrMain.main(VideoLdrMain.java:24)

Open in new window


This is the error I get after inserting user name and password into the text fields and clicking "OK":
 
Error during button click event
java.sql.SQLException: ORA-01017: invalid username/password; logon denied

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:388)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:381)
        at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:564)
        at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:431)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:366)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:752)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:359)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:185)
        at Loader.DBAuthenticate.getDBConnection(DBAuthenticate.java:96)
        at Loader.DBAuthenticate.actionPerformed(DBAuthenticate.java:81)
        at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
        at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
        at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
        at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
        at java.awt.Component.processMouseEvent(Component.java:6267)
        at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
        at java.awt.Component.processEvent(Component.java:6032)
        at java.awt.Container.processEvent(Container.java:2041)
        at java.awt.Component.dispatchEventImpl(Component.java:4630)
        at java.awt.Container.dispatchEventImpl(Container.java:2099)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4577)
        at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
        at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
        at java.awt.Container.dispatchEventImpl(Container.java:2085)
        at java.awt.Window.dispatchEventImpl(Window.java:2478)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

Open in new window


From what I understand of this, the program is trying to connect before the user name and password are entered.  Then when they are entered, the information from the textfields are not being passed as they need to be.

I appreciate the help.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 47

Expert Comment

by:for_yan
ID: 34992662
Forget about the secod one - you should not see the first one in the first place.

You should not even go there to SQL/JDBC  before you input username and password
So I guess it says in the first one that the problem occurs in line 96 of DBauthenticate - what is
in that line and why you happen to be there before you input user/password?
0
 

Author Comment

by:g_currier
ID: 34992677
This is from line 89 to 99:
public Connection getDBConnection() throws ClassNotFoundException, SQLException {
        
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521/orcl";
        String user = getUser;
        String password = getPass;
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, user, password);
        conn.setAutoCommit(false);
        return conn;
    }

Open in new window


Line 96 is this:
 
Connection conn = DriverManager.getConnection(url, user, password);

Open in new window


I think that this means I am connecting before the pop up window is created?
0
 
LVL 47

Accepted Solution

by:
for_yan earned 350 total points
ID: 34992705
Definitely, you have some logic and ordering  of what is happening incorrectly.
You should perhapps create your visual stuff in costructor,
but should not go to database, untill
you read the creadentials perhaps somewhere in the action method
(didn't go through all your code - this is more general what I'm saying).
You can still have connection as your instance variable
so you can then store it in  you class in first action whe you
read the user password and connect first time  and use it in following actions,
but you should make sure you don't touch databse
before you read user and password provided by the user input.
0
 

Author Closing Comment

by:g_currier
ID: 34992833
I will rework what I have and put database work into one class, graphics into another
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question