Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-11-03
12
Medium Priority
?
357 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:makam_75
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 92

Expert Comment

by:objects
ID: 12489108
0
 
LVL 3

Assisted Solution

by:vikraman_b
vikraman_b earned 1000 total points
ID: 12489523
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:gnoon
ID: 12489825
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
 
LVL 16

Expert Comment

by:gnoon
ID: 12489903
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
 

Author Comment

by:makam_75
ID: 12496288
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
 

Author Comment

by:makam_75
ID: 12496327
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
 
LVL 16

Accepted Solution

by:
gnoon earned 1000 total points
ID: 12500835
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
 
LVL 16

Expert Comment

by:gnoon
ID: 12500865
Ooppp .... sorry, I forgot to close the <sql:getColumn/> tags, and they should be inside <sql:resultSet> tag.
0
 
LVL 3

Expert Comment

by:vikraman_b
ID: 12501668
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
 
LVL 3

Expert Comment

by:vikraman_b
ID: 12501678
My DBUtils i configured for weblogic to get the datasource
0
 

Author Comment

by:makam_75
ID: 12670213
Thankyou all. This info has helped.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Suggested Courses

578 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