Sample code on retreiving data from an oracle db and displaying using JSP

hello,
I am helping out on a local school web site. The idea is to create a website that shows the teachers experience and credentials on a web page. trying to get this to tehm in the next few days since they have their annual review coming up.
Basically retreive around 10 fields from a db and display on a web page.
can you send me pointers to any sample code to do this. I will need the java beans, classes that do db calls, the also the class that interface with jsp. also want something low maintenence because they will be on their own once i set them up with it. quick reply appreciated.
makam_75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

objectsCommented:
0
vikraman_bCommented:
hi..here is the code example

for getting connection..put it seperate class DBUtils.java


import javax.naming.*;
import javax.sql.DataSource;
import javax.transaction.NotSupportedException;
import javax.transaction.SystemException;
import javax.transaction.UserTransaction;
import java.sql.*;
import java.util.*;
import javax.mail.*;

import org.apache.log4j.Logger;
public class DBUtils {

      private Context ctx = null;
      private static DBUtils dbUtils = null;
      private DataSource ds = null;
      private UserTransaction tx = null;
      private Session session = null;
      
      static Logger logger      = Logger.getLogger(DBUtils.class);

      private DBUtils() {
            super();
      }

      private void initialiseContext () throws NamingException {
            Hashtable env = new Hashtable();
 
            env.put(Context.INITIAL_CONTEXT_FACTORY,
                         "weblogic.jndi.WLInitialContextFactory");
 
            ctx = new InitialContext(env);
      }

      public static DBUtils getInstance() {
            if (dbUtils == null)
                  dbUtils = new DBUtils();
            return dbUtils;
      }

      public UserTransaction getUserTransaction () throws NamingException {
            if (ctx == null) this.initialiseContext();
            if(tx == null){
                  tx = (UserTransaction) ctx.lookup(Constants.USER_TRANSACTION);
            }
            return tx;
      }

      public DataSource getDataSource () throws NamingException {
            if (ctx == null) this.initialiseContext();
            if(ds == null){
                  ds = (javax.sql.DataSource) ctx.lookup (Constants.DATA_SOURCE);
            }
            return ds;
      }

      public Connection getConnection () throws NamingException, SQLException {
            return getDataSource().getConnection();      
      }
      
      public void closeConnection (ResultSet rs, Statement stmt, Connection conn) {
            try {
                  if (rs != null) rs.close();
            } catch (Exception e) {  
            }  
            try {
                  if (stmt != null) stmt.close();
            } catch (Exception e) {  
            }
            try {
                  if (conn != null) conn.close();
            } catch (Exception e) {
            }
      }
      
      public Session getMailSession () throws NamingException {
            if (ctx == null) this.initialiseContext();
            if(session == null){
                  session = (Session) ctx.lookup(Constants.MAIL_SESSION);
            }
            return session;      
      }
      
      public UserTransaction startNewUserTransaction() throws EXXXException {
            UserTransaction tx = null;
             
            // Get Transaction
            try {
                  tx = DBUtils.getInstance().getUserTransaction();
            } catch (NamingException ne){
                  throw new EXXXException ("error.transaction.lookup",ne);
            }
      
            // Begin Transaction
            try {
                  tx.begin();
            } catch (SystemException se) {
                  throw new EXXXException ("error.transaction.begin",se);
            } catch (NotSupportedException nse){
                  throw new EXXXException ("error.transaction.begin",nse);                                    
            }            
            
            return tx;
      }
      
      public void rollbackTransaction (UserTransaction tx) throws EXXXException{
            try {
                  tx.rollback();
                  logger.debug ("Transaction rolled back.");
            } catch (SystemException se) {
                  throw new EXXXException ("error.transaction.rollback",se);
            }
      }
      
      public void commitTransaction (UserTransaction tx) throws EXXXException{
            try {
                  tx.commit();
                  logger.debug ("Transaction committed.");            
            } catch (Exception e) {
                  throw new EXXXException ("error.transaction.commit",e);
            }
      }
}


then for DAO operations...

import javax.naming.*;

import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
 *
 */
public class CreateLoginDAO{

      static CreateLoginDAOCreateLoginDAO= null;
      Logger logger = Logger.getLogger(this.getClass().getName());

      public static CreateLoginDAOgetInstance() {
            if (CreateLoginDAO== null) {
                  CreateLoginDAO= new CreateLoginAccDAO();
            }

            return createLoginAccDAO;
      }

      /**
       *
       */
      private CreateLoginAccDAO() {
      }


      /*
       * INSERT INTO USERS ( OBJID,FEDEXID,FIRSTNAME,LASTNAME,EMAIL,ROLE_NAME,STATUS ) VALUES ('77','FENO1234','VIKRAM','B','vik@EMP.com','MGR','0')
       */
      void insertUserAccount(LoginDTOuserAcc, int seqno) throws NamingException, SQLException {
            String insertUser_SQL =
                  "INSERT INTO USERS ( "
                        + "OBJID, ID,      PASSWORD,      FIRSTNAME,            LASTNAME,            EMAIL,                  ROLE_NAME,"
                        + "STATUS,USER2COUNTRY ) VALUES (?,?,?,?,?,?,?,?,?)";
            
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;

            try {

                  conn = DBUtils.getInstance().getConnection();
                  stmt = conn.prepareStatement(insertUser_SQL);

                  stmt.setInt(1, seqno);
                  stmt.setString(2, userAcc.getUserId());
                  stmt.setString(3, userAcc.getPassword());
                  stmt.setString(4, userAcc.getFirstName());
                  stmt.setString(5, userAcc.getLastName());
                  stmt.setString(6, userAcc.getEmail());
                  stmt.setString(7, userAcc.getUserRole());
                  stmt.setInt(8, userAcc.getStatus());
                  stmt.setInt(9, userAcc.getCountry());
                  stmt.executeUpdate();

            } catch (SQLException e) {
                  throw e;
            } finally {
                  try {
                        if (rs != null)
                              rs.close();
                  } catch (Exception e) {
                  }
                  try {
                        if (stmt != null)
                              stmt.close();
                  } catch (Exception e) {
                  }
                  try {
                        if (conn != null)
                              conn.close();
                  } catch (Exception e) {
                  }
            }
      }
      /* select team_name from team where team2country = '9' */
      ArrayList loadTList(int countryID) throws NamingException, SQLException {

            String SQL = " select distinct TEAM_NAME, OBJID " + " from TEAM where TEAM2COUNTRY = " + countryID;

            ArrayList arrayList = new ArrayList();

            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            logger.debug("loadTList query:" + SQL);

            try {
                  conn = DBUtils.getInstance().getConnection();
                  stmt = conn.createStatement();

                  stmt.execute(SQL);
                  rs = stmt.getResultSet();

                  while (rs.next()) {
                        String teamName = rs.getString(1);
                        int objID = rs.getInt(2);
                        arrayList.add(new KeyValueBean(objID, teamName));

                  }
            } catch (SQLException e) {
                  throw e;
            } finally {
                  try {
                        if (rs != null)
                              rs.close();
                  } catch (Exception e) {
                  }
                  try {
                        if (stmt != null)
                              stmt.close();
                  } catch (Exception e) {
                  }
                  try {
                        if (conn != null)
                              conn.close();
                  } catch (Exception e) {
                  }
            }
            return arrayList;
      }
      public void updateLocMgr(LoginDTOuserAcc, int seqno, int CountryID)
            throws NamingException, SQLException {

            String updateLocalMgr_SQL =
                  " UPDATE LOCATION      set LOC2MANAGER = ? " + " WHERE LOC2COUNTRY = " + CountryID + " AND " + " OBJID = ?";
            //LOC_NAME objid
            logger.debug("updateLocalMgr_SQL  Query:" + updateLocalMgr_SQL);
            logger.debug("LOC2MANAGER :" + seqno);
            logger.debug("OBJID :" + userAcc.getLocation());
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;

            try {
                  conn = DBUtils.getInstance().getConnection();
                  stmt = conn.prepareStatement(updateLocalMgr_SQL);
                  stmt.setInt(1, seqno);
                  stmt.setInt(2, userAcc.getLocation());
                  stmt.executeUpdate();
            } catch (SQLException e) {
                  throw e;
            } finally {
                  try {
                        if (rs != null)
                              rs.close();
                  } catch (Exception e) {
                  }
                  try {
                        if (stmt != null)
                              stmt.close();
                  } catch (Exception e) {
                  }
                  try {
                        if (conn != null)
                              conn.close();
                  } catch (Exception e) {
                  }
            }

      }

}
 so write a jsp usebean  & usebean in the name of LoginDTOuserAcc put all ur get/ set methods ..just call it...

All the best

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

gnoonCommented:
Using Jakarta DBTags Tag Library with Tomcat is easier way for displaying data from db.

1. Downloads the lastest Tomcat from here: http://jakarta.apache.org/site/binindex.cgi, then installs it on your OS.
2. Downloads oracle driver from here: http://download.oracle.com/otn/utilities_drivers/jdbc/8171/classes12.zip.
3. Places the oracle driver in your jsp server's class-path.  The easiest way is copy it to folder 'ext' in jre path, C:\j2sdk1.4.2\jre\lib\ext commonly.
4. Downloads the library from here: http://cvs.apache.org/builds/jakarta-taglibs/nightly/projects/dbtags/jakarta-taglibs-dbtags-20040928.zip.
5. Installs the library to work with tomcat by following configuration: http://jakarta.apache.org/taglibs/doc/dbtags-doc/dbtags-1.0/index.html#config.
6. Now, it's ready to retreive data from an oracle db and display using JSP.

An example of JSP code:

<!--  Report.jsp  -->
<%@page contentType="text/html;charset=UTF-8"%>
<%@taglib uri="dbtags" prefix="sql" %>

<sql:connection id="conn1">
    <sql:url>jdbc:oracle:thin:@oracle_server:1521:orcl</sql:url>
    <sql:driver>oracle.jdbc.driver.OracleDriver</sql:driver>
    <sql:userId>DBusername</sql:userId>
    <sql:password>DBpassword</sql:password>
</sql:connection>

<html>
<body>

<table border=1 width="90%">
<tr>
    <th>Teacher Name</td>
    <th>Credentials</td>
    <th>Experience</td>
</tr>

<sql:statement id="stm1" conn="conn1">

    <sql:query>
         select name,credentials,experience from teachers
    </sql:query>

    <sql:resultSet>
         <tr>
         <td align=center><sql:getColumn colName="name"></td>
         <td align=center><sql:getColumn colName="credentials"></td>
         <td align=center><sql:getColumn colName="experience "></td>
         </tr>
    </sql:resultSet>

    <sql:wasEmpty>
         <tr><td colspan=3 align=center>No such record found !</td></tr>
    </sql:wasEmpty>

    <sql:wasNotEmpty>
         <tr><td colspan=3><sql:rowCount/> rows retrieved.</td></tr>
    </sql:wasNotEmpty>

</sql:statement>

</table>

</body>
</html>
0
gnoonCommented:
Sorry, with the configuration from site http://jakarta.apache.org/taglibs/doc/dbtags-doc/dbtags-1.0/index.html#config

<%@taglib uri="dbtags" prefix="sql" %>

should be

<%@taglib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql"%>
0
makam_75Author Commented:
Using the DBTags Tag Library  makes it not easy in future if they want to add additional functionality to the web page, like transactions or calculations with the data, correct?
0
makam_75Author Commented:
Vikraman,
can you explain what DBUtils class does? I am guessing I dont need it for my current requirement which is simply retrieving data from oracle and displaying.
Also, the database is already loaded with teachers info across the state, so I dont have to worry about insert.
what is DAO. Thanks.
0
gnoonCommented:
makam_75,

You can do transaction and calculation with DBTags Tag Library.

For calculation, you just store the value from db to an attribute rather than the JSP output. For example

<sql:statement id="stm1" conn="conn1">
    <sql:query>
         select name,credentials,experience from teachers
    </sql:query>
    <sql:getColumn colName="name" to="nameAtt">
    <sql:getColumn colName="credentials" to="credAtt">
    <sql:getColumn colName="experience " to="expeAtt">
<%
    // Gets attributes, and does calculation here
    Object teacherName = pageContext.getAttribute("nameAtt");
    Object teacherCredential = pageContext.getAttribute("credAtt");
    Object teacherExperience = pageContext.getAttribute("expeAtt");
%>
    <sql:resultSet>
         <tr>
         <td align=center><%=teacherName%></td>
         <td align=center><%=teacherCredential%></td>
         <td align=center><%=teacherExperience%></td>
         </tr>
    </sql:resultSet>
</sql:statement>

For transaction, you just get the reference to the connection, then do transaction. For example

<%
Connection conn1 = (Connection) pageContext.getAttribute("conn1"); // get reference to the connection
if(conn1 != null)
    conn1.setAutoCommit(false); // set to manual commit
try
{
%>
  <sql:statement id="stm1" conn="conn1">
      <sql:execute ignoreErrors="false">
        insert into teachers values('John','No credential','No experience')
      </sql:execute>
      <sql:execute ignoreErrors="false">
        insert into teachers values('Smith','No credential','No experience')
      </sql:execute>
  </sql:statement>
<%
   if(conn1 != null)
      conn1.commit();  // commit transaction
}
catch(Exception ex)
{
   if(conn1 != null)
      conn1.rollback(); // rollback
}
%>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gnoonCommented:
Ooppp .... sorry, I forgot to close the <sql:getColumn/> tags, and they should be inside <sql:resultSet> tag.
0
vikraman_bCommented:
Vikraman,
can you explain what DBUtils class does? I am guessing I dont need it for my current requirement which is simply retrieving data from oracle and displaying.
Also, the database is already loaded with teachers info across the state, so I dont have to worry about insert.
what is DAO.

HI makam,
For ur case use only SELECT query(example i given above).

DBUtils is just to get the datasource conection to make it sepearte like not to mix with the DAO class.
for example u can write amethod u return the connection object.(it depends which Database ur using for example oracle:


 Connection connection = null;
    try {
        // Load the JDBC driver
        String driverName = "oracle.jdbc.driver.OracleDriver";
        Class.forName(driverName);
   
        // Create a connection to the database
        String serverName = "127.0.0.1";
        String portNumber = "1521";
        String sid = "mydatabase";
        String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
        String username = "username";
        String password = "password";
        connection = DriverManager.getConnection(url, username, password);
    } catch (ClassNotFoundException e) {
        // Could not find the database driver
    } catch (SQLException e) {
        // Could not connect to the database
    }



a procedure to  make it seperate..
In Projects...
they will decide to create package utils (utility classes)
and DAO just logical name(Data Access Object) given to the basic java class here it is singleton class(a plain java class only) .u will seperate ur business logic from DAO
like
also Business Object is encapsulated in BO classes(a basic java class which holds business logic) .
THe DAO contains basic data base operations like insert,update,delete and select.
BO encapsulates which method to call etc..

jsp -->bean ---->BO---->DAO---Database.
The code looks neat..nothing else

even u can do all ur stuff in the bean class itself..this way to code is to make the program loosely couples,so if u want to change ur database u can just change the code in DBUtils orDAO or both.
SLl the best..
do u need jsp and bean example too..
0
vikraman_bCommented:
My DBUtils i configured for weblogic to get the datasource
0
makam_75Author Commented:
Thankyou all. This info has helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.