Using datasource for login bean & login page?

[Jia Bin Tang]

I happened to see this code from one of the books "Making use of JSP", and is sort of what I need for the login bean for my java page:

import java.io.*;
import java.sql.*;

public class BankAccount
{
	private String sAccountID=" ";
	private String sPin=" ";
	private boolean AccountValidate;
	Connection connect = null;
	Statement state = null;
	ResultSet result = null;


        public void setsAccountID(String sAccountID)
		{
   			System.out.println("Inside setter method"+sAccountID);
			this.sAccountID=sAccountID;
		}

	public void setsPin(String sPin)
		{
			this.sPin=sPin;
		}

	public String getsAccountID()
		{
   		return this.sAccountID;
		}

	public String getsPin()
		{
          		return sPin;
		}


	public BankAccount() throws ClassNotFoundException
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		}




		public boolean AccountValidate()
		{
				boolean  validate=false;
				String sPinNo="";

			try{

				sAccountID=getsAccountID();
				sPin=getsPin();
				System.out.println("Inside VALIDATE method"+sAccountID);
				connect=DriverManager.getConnection("jdbc:odbc:MyDataSource","sa","");
				String strQuery = "Select cPin_no from Login where cAccount_id='"+sAccountID+"'" ;
				System.out.println("Query: "+strQuery);
				state = connect.createStatement();
				ResultSet result = state.executeQuery(strQuery);
				while(result.next())
				{

					sPinNo = result.getString(1);
					System.out.println("Inside Result method"+sPinNo+sPin);

				}
				sPinNo=sPinNo.trim();
				sPin=sPin.trim();
				if(sPinNo.equals(sPin))
					{
						validate = true;
					}

			}
			catch(Exception e)
			{
				System.out.println("SQL Exception is caught.");
			}

			return validate;
		}
}

One qn I'd like to ask: How to I change this to datasource? As I've been using datasource for my project, so I'd like to keep it constant.


Oh ya, and can someone interpret, what does this line means?

sPinNo = result.getString(1);

Thanks a million!

[ujjwal soni]

Hi,

The code seems to be working. Well, here are the answers to your questions.

1) How to I change this to datasource?

A) You can create a datasource by clicking on startmenu -> run -> type odbcad32.exe
If you are having an existing datasource then simply change the bolded word with the name of your datasource

connect=DriverManager.getConnection("jdbcdbc:MyDataSource","sa","");


2) Can someone interpret, what does this line means?

A) A result set contains the results of a SQL query. The results are kept in a set of rows, one of which is designated the current row. A row must be made current before data can be retrieved from it. The result set maintains a reference to the current row called the cursor.
The cursor is positioned before the first row when a result set is created. When a result set's next() method is called, the cursor moves to the first row of the result set and that row becomes the current row.

sPinNo = result.getString(1);

You can also specify the name of the column

sPinNo = result.getString("SPIN");  

"HELPING HANDS ARE BETTER THAN PRAYING LIPS........................."

[Vikas Kapoor]

sPinNo = result.getString(1);

I also prefer column name instead of column number. It gives more readability to your code.
And the above line can be interpreted as, You are fetching value of first column which seems to be of String type and storing it into sPinNo(in addition to what ujjwal mentioned).

[Bauke Scholtz]

ODBC is a bad choice. Use a real JDBC driver which should be shipped along the DB in question.
Regarding to the ResultSet API: go through a decent JDBC tutorial as well.

[Jia Bin Tang]

ujjwal soni wrote:Hi,

The code seems to be working. Well, here are the answers to your questions.

1) How to I change this to datasource?

A) You can create a datasource by clicking on startmenu -> run -> type odbcad32.exe
If you are having an existing datasource then simply change the bolded word with the name of your datasource

connect=DriverManager.getConnection("jdbc:odbc:MyDataSource","sa","");

Oh sorry dude, what I meant was this: connecting using with JNDI as well. say like:

// Imports
// =======

import java.util.Vector;

//Needed for JDBC work
import java.sql.*;

//For Datasources
//Make sure jdbc2_0-stdext.jar & jndi.jar is in classpath
//(see build.xml)
//Alternatively, can use j2ee.jar (contains everything)
import javax.sql.*;
import javax.naming.*;

.....

// Attributes
  // ==========

  // JNDI attributes needed to connect to datasource
  // These attributes are set from EmployeeAccessBean
  // which is set from the included JSP fragment setDbDetails.jsf
  private String jndiName;

  // Constructors
  // ============

  /**
   *  Constructor
   */
  public EmployeeDAO(String jndiName) {

     // Initialize attributes
     this.jndiName        = jndiName;
  }


  // Public methods
  // ==============

  /**
   *  Find all 
   *
   *  @return list
   *
   *  @throws java.lang.Exception
   */
  public Vector findAll() throws Exception   {

     Connection dbConnection = null;

     try {
        // Get a database connection
        dbConnection = getConnection();

        // Perform the work
        return findAllEmp(dbConnection);

     } catch (SQLException e) {

        //    Here we are catching exceptions only to print them for
        //    debugging purposes
        //    May be we could log them to a file
        //    Once caught, we are re-throwing them once again to a higher level
        //    If you are not doing anything when catching the exceptions
        //    then don't catch them
        System.out.println(e.getMessage());
        // Print any other nested exceptions
        while ((e = e.getNextException()) != null) {
           System.out.println("   next " + e.getMessage());
        }

        throw e; // Re-throw the SQLException

     } finally {

        // Close the DB even if an Exception has occurred
        // Otherwise it will be left open
        // SHOULD be in the finally block
        closeConnection(dbConnection);
     }

.
.
.
.
/**
   *  Gets a datasource connection
   *  Different from getting a databse connection using DriverManager
   *
   *  @return Connection object to connect to datasource
   *
   *  @throws java.lang.Exception
   */
  private Connection getConnection()
  throws Exception {

     String jndiPrefix = "java:comp/env/jdbc/";   // Works with Tomcat datasource
                                                  // Doesn't work with jBoss datasource
     //String jndiPrefix = "java:/";              // Works with jBoss datasource
                                                  // Doesn't work with Tomcat datasource


     Context initCtx = null;
     try {
        // Obtain the initial JNDI context
        initCtx = new InitialContext();

        // Perform JNDI lookup to obtain resource manager connection factory
        DataSource ds =
           (javax.sql.DataSource)
           initCtx.lookup(jndiPrefix + jndiName);

        // Invoke factory to obtain a connection
        return ds.getConnection();

     } catch (SQLException e) {

       System.out.println(e.getMessage());
       // Print any other nested exceptions
       while ((e = e.getNextException()) != null) {
          System.out.println("   next " + e.getMessage());
       }
       throw e; // Re-throw the SQLException

     } catch (NamingException e) {

       System.out.println("Cannot find datasource " + jndiPrefix + jndiName);
       System.out.println(e.getMessage());
       throw e; // Re-throw the NamingException

     } finally {
        // Don't forget to close the naming context
        if (initCtx != null) {
           initCtx.close();
        }
     }

  } // getConnection()


  /**
   *  Close datasource connection
   *  Similar to closing database connection
   *
   *  @param conn Connection object to close
   *
   *  @throws java.lang.Exception
   */
  private void closeConnection (Connection conn)
  throws Exception {
     conn.close();
  } // closeConnection()
}

possible to use this (datasource) method? If so, how?
Anyway thanks for answering the other qn.