Solved

Passing textfield info to connection string

Posted on 2011-02-27
7
587 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
one-way data "masking" MD5 sql 26 103
bitbucket vs gitbucket 3 32
history tablespace temp usage 2 17
Best RAID for a BDD Oracle 4 24
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now