How to retrieve data from 10g in java servlet & JSP

Question

Retrieve data from database 10g in Servlet & JSP

Answer 1

I assume you mean an Oracle 10g database, if so JDBC is the answer, start here (general) and here (Oracle JDBC drivers).

Answer 2

There are countless ways of skinning this particular cat.

For one it depends on what Web framework (if any) you use. Personally I'm a huge fan of using Spring regardless of which Web framework you choose. It just makes so many things that much easier. Lightweight persistence frameworks include Spring JDBC and, my favourite, Ibatis.

In fact I wrote a tutorial on using Spring and Ibatis. In fact, it even uses Oracle 10g Express Edition ("Oracle XE").

Answer 3

Use (the ordering is my preference)

• Hibernate - Spring's Hibernate Template (with annotations, hbm.xml is old school),
• JPA with Toplink
• iBatis
• JDO.

Don't use direct JDBC unless you just have a bunch of extra time.

Answer 4

• don't retrieve data in JSP, use an MVC architecture or at least retrieve the data in the servlet
• use Spring
• write some DAO classes or if you prefer am ORM use iBatis or Hibernate
• refine your question if you need more specific information, as it is it's a bit vague regarding what exactly you need to know

Answer 5

Other answers have listed the best technologies that one should definitely pursue to accomplish this. But to directly answer the question, perhaps the most straight forward answer is with an example of plain old JDBC:

private void getYourData() {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
        Context initContext = new InitialContext();
        Context envContext = (Context) initContext.lookup("java:/comp/env");
        DataSource ds = (DataSource) envContext.lookup("jdbc/yourDatabase");
        conn = ds.getConnection();
        pstmt = conn.prepareStatement(
                "select yourdata " +
                "  from yourtable " +
                "  where yourkey = ? "
                );

        pstmt.setInt(1, yourKeyValue);
        rset = pstmt.executeQuery();
        while (rset.next()) {
            String yourData = rset.getString("yourdata");
        }

        conn.commit();

    } catch (NamingException ne) {
        log.error(ne.getMessage());
    } catch (SQLException se) {
        log.error(se.getMessage());
    } finally {
        if (rset != null) {
            try {
                rset.close();
            } catch (Exception e) {
                log.error(e.getMessage());
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (Exception e) {
                log.error(e.getMessage());
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                log.error(e.getMessage());
            }
        }
    }
}