Calling Stored Procedures or Functions from Code Java

To invoke a stored procedure or function  from within an entity object or an application module, you need to follow below steps:
1. Create a CallableStatement with the PL/SQL block containing the stored procedure or function invocation
2. Bind any parameters ( IN or OUT )
3. Execute the statement.
4. Optionally retrieve the values of any OUT parameters or return value of function
5. Close the statement. 

I will illustrate previous steps in below examples

1-Stored Procedure with OUT parameter 

 public void testStoredProcedure() {  
   
     OADBTransaction txn = getDBTransaction();  
   
     CallableStatement callableStatement =  
   
       txn.createCallableStatement("begin xxx_procedure(:1, :2); end;",OADBTransaction.DEFAULT);  
   
     try {  
   
       callableStatement.registerOutParameter(2, Types.VARCHAR);  
   
       callableStatement.setString(1, "mahmoud");  
   
       String outParamValue = null;  
   
       callableStatement.execute();  
   
       outParamValue = callableStatement.getString(1);  
   
       callableStatement.close();  
   
     } catch (SQLException sqle) {  
   
       callableStatement.close();  
   
     }  
   }   

2- Stored Function

 public void testFunction() {  
     OADBTransaction txn = getDBTransaction();  
   
     CallableStatement callableStatement =  
   
       txn.createCallableStatement("begin :1 := xx_function(:2); end;",OADBTransaction.DEFAULT);  
   
     try {  
   
       callableStatement.registerOutParameter(1, Types.VARCHAR);  
   
       callableStatement.setString(1, "mahmoud");  
   
       String outParamValue = null;  
   
       callableStatement.execute();  
   
       outParamValue = callableStatement.getString(1);  
   
       callableStatement.close();  
   
     } catch (SQLException sqle) {  
   
       callableStatement.close();  
     }  
   }  

Thinking in ROWID

We hear ‘ROWID’ many times from database developers, So we should think about what purpose it serves. 

Oracle says, ‘They are the fastest way to access a single row’

ROWID gives us the location of the data for a given row in a table. The datafile – then the position of the row in the block, then the data block in the datafile. 

As you can imagine, this data is UNIQUE. So, each row in your table will have a unique ROWID. 

This is only because the data for 2 rows can’t exist in the same space.

It’s not uncommon for a table to lack any unique columns or PKs or unique indexes. But, if you know the ROWID, then you will have at least 1 unique differentiator for that record.

It’s also important to remember that a record’s ROWID is not guaranteed to remain unchanged.

Since it’s a PSEUDO column, it’s not actually stored in the database. That means we can’t INSERT, UPDATE, DELETE  ROWID. But, we can use it in a SELECT or WHERE.

Oracle is saying ROWIDs aren’t stored in a table. They can and WILL be stored in an INDEX. Indexes allow us to quickly jump to the records we are looking for. However, the ROWID is dynamically assembled as you retrieve that data.  

Main majors use of ROWIDs

1. Use it to delete duplicate rows from your table.
2. Use it to edit SELECT results in Toad for Oracle

Deleting Records
Add ‘ROWID’ to your SELECT string. Once you’ve identified the records you want to delete, just record those values. Then issue your ‘DELETE FROM TABLE WHERE ROWID in (…).

select emp.ROWID, empno , ename

from scott.emp;

If you query a view and include ROWID, you may get a 

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Your view could be pulling data from multiple source tables. These tables need a PK apparently. This Oracle’s way of saying that it can’t determine the the ROWID based on what you are querying. Also, your row could be pulling from multiple sources, so there would be theoretically multiple ROWIDs

Example Struts 2 + Hibernate + Spring intergration

Hi friends!

Finally after a long time, we are back. Here we’ve yet another example, as the title says, it is an Integration example in which we are going to see how Struts, Spring and Hibernate are getting integrated together.

   

    You create project and  execute follow step by step:

1. I use database Oracle 11g

create table: 

-- Create table

create table TBL_PHARMACYADMIN

(

  USERNAME VARCHAR2(4000),

  PASSWORD VARCHAR2(4000),

  ID       NUMBER

)

and create sequence

CREATE SEQUENCE PHARMACYADMIN_seq

 START WITH     1000

 INCREMENT BY   1

 NOCACHE

 NOCYCLE;

2. Create Project dynamic web application pharmacyprj

- web.xml:

<filter>

  <filter-name>struts2</filter-name>

  <!-- filter-class>org.apache.struts2.dispatcher.FilterDispatcher</filter-class> -->

  <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>

 </filter>

 <filter-mapping>

  <filter-name>struts2</filter-name>

  <url-pattern>/*</url-pattern>

 </filter-mapping>

 <session-config>

  <session-timeout>30</session-timeout>

 </session-config>

 <listener> 

<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> 

</listener>   

 <context-param>

  <param-name>contextConfigLocation</param-name>

  <param-value>classpath:applicationContext*.xml</param-value>

  <!-- WEB-INF/applicationContext.xml -->

 </context-param>

 <welcome-file-list>

    <welcome-file>index.jsp</welcome-file>

 </welcome-file-list>

- hibernate.cfg.xml:

<session-factory>

<property name="hbm2ddl.auto">update</property>

<property name="dialect">

           org.hibernate.dialect.OracleDialect

       </property>

       <property name="connection.driver_class">

         oracle.jdbc.driver.OracleDriver

       </property>

       <property name="connection.url">

         jdbc:oracle:thin:@127.0.0.1:1521:ST2

       </property>

       <property name="connection.username">st</property>

       <property name="connection.password">123456</property>      

 

       <property name="hibernate.show_sql">true</property>

<property name="current_session_context_class">thread</property>

<property name="hibernate.connection.autocommit">true</property>

       <mapping resource="pharmacyAdmin.hbm.xml"/>

</session-factory>

- applicationContext.xml:

   <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">

<property name="configLocation" value="classpath:hibernate.cfg.xml"/>

</bean>

<bean id="testSpring" class="com.fiz.model.TestSpring">

<property name="message" value="Hello Trung"></property>

</bean>

- pharmacyAdmin.hbm.xml

     <class name="com.fiz.model.pharmacyAdmin" table="tbl_pharmacyadmin">

    <id name="id" column="id" type="long">

    <generator class="sequence-identity">

    <param name="sequence">PHARMACYADMIN_SEQ</param>

    </generator>

    </id>

    <property name="username" column="username" insert="true" update="true" type="java.lang.String"></property>

    <property name="password" column="password" insert="true" update="true" type="java.lang.String"></property>

    </class>

- struts.xml:

                <action name="testaction" class="com.fiz.model.TestSpring">

<result name="success">index.jsp</result>

</action>

- Create class java HibernateSessionFactory.java

package com.fiz.utils;

import org.hibernate.HibernateException;

import org.hibernate.Session;

import org.hibernate.cfg.Configuration;

public class HibernateSessionFactory {

private static String CONFIG_FILE_LOCATION="hibernate.cfg.xml";

private static final ThreadLocal<Session> threadLocal=new ThreadLocal<Session>();

private static Configuration configuration=new Configuration();

private static org.hibernate.SessionFactory sessionFactory;

private static String configFile=CONFIG_FILE_LOCATION;

static{

try{

configuration.configure(configFile);

sessionFactory=configuration.buildSessionFactory();

}catch (Exception e) {

// TODO: handle exception

System.out.println("Error Creating SessionFactory");

e.printStackTrace();

}

}

private HibernateSessionFactory(){

}

public static Session getSession()throws HibernateException{

Session session=(Session)threadLocal.get();

if(session==null || !session.isOpen()){

if(sessionFactory==null){

rebuildSessionFactory();

}

session=(sessionFactory!=null)?sessionFactory.openSession():null;

threadLocal.set(session);

}

return session;

}

public static void rebuildSessionFactory(){

try{

configuration.configure(configFile);

sessionFactory=configuration.buildSessionFactory();

}catch (Exception e) {

// TODO: handle exception

}

}

public static void closeSession()throws HibernateException{

Session session=(Session)threadLocal.get();

threadLocal.set(null);

if(session!=null){

session.close();

}

}

public static Configuration getConfiguration() {

return configuration;

}

public static org.hibernate.SessionFactory getSessionFactory() {

return sessionFactory;

}

public static void setConfigFile(String configFile) {

HibernateSessionFactory.configFile = configFile;

sessionFactory=null;

}

}

- Create interface userDao:

public interface userDao {

public List getAllUsers()throws Exception;

public List getUsersByPage(int pageNo, int pageSize)throws Exception;

public pharmacyAdmin findByUsernameAndPassword(String username, String password);

public Page findByPageNo(int pageNo, int pageSize);

public int addUser(pharmacyAdmin pmcyAdmin);

public int removeUser(pharmacyAdmin pmcyAdmin);

}

- Create class UserDaoImpl.java inherited Interface userDao:

private Session session=HibernateSessionFactory.getSession();

@Override

public List getAllUsers() throws Exception {

// TODO Auto-generated method stub

String sql="from tbl_pharmacyadmin";

Query query=session.createQuery(sql);

return query.list();

}

@Override

public List getUsersByPage(int pageNo, int pageSize) throws Exception {

// TODO Auto-generated method stub

String sql="from tbl_pharmacyadmin";

return null;

}

- In part web, you create files follow below:

Page index.jsp

<body>

<div id="header">

<div id="navigation">

    <ul>

        <li><a href="index.jsp">Home</a></li>

           <li><a href="#">About Us</a></li>

           <li><a href="#">ADMINISTRATOR </a></li>

           <li><a href="#">DISTRIBUTER </a></li>

           <li><a href="#">CUST0MER</a></li>

           <li><a href="#">Contact Us</a></li>

       </ul>

   </div>

</div>

<table width="960px" border="0" cellpadding="0" cellspacing="10" align="center"></table>

<div id="content">

<table width="960" border="0" cellspacing="0" cellpadding="0">

 <tr align="justify">

   <td width="200" valign="top">

    <img src="images/hioxindia-pharmacy_19.jpg" alt="" border="0" />    </td>

   <td width="760" valign="top">

    <h1>Welcome to Pharmaceutical Company </h1>

       <p>This is free Pharmaceutical Company   for your online website, blog or web page which is under Health and Medicine category. Anyone wants good, professional,   for their pharmacy, drug store, medical store then this   would be the best one. This free pharmaceutical company   is designed by professional designers to make your task much easier to customize it.</p><br />

<p>There are many other   categories like Arts, Agriculture, Computers, Satellite, Cars, Finance, Nature, Music, Personal Website, Religious, Fashion, Furniture, Holiday, Travel, Night club are available in this site. Have a look at the collection of HTML  s, pick the right one, preview and download it easily.</p>

   </td>

 </tr>

</table>

</div>

<div id="footer">

<div id="footer_1">

    <ul>

        <li><a href="#"></a></li>

            <li><a href="#"></a></li>

            <li><a href="#"></a></li>

            <li><a href="#"></a></li>

            <li><a href="#"></a></li>

        </ul> <br />

   <span id="design">Designed by <a id="dum" href="http://www.dacsanthanhhoa.com" target="_blank">TrungLee</a></span>

   <script type="text/javascript">

   document.onload = ctck();

   </script>

  </div>

</div>

</body>

- Layout.css:

@CHARSET "UTF-8";

/* CSS Document */

body{

margin:0;

color:#232323;

padding-top: 0px;

padding-right: 0;

padding-bottom: 0;

padding-left: 0;

background:#f0f3f0;

width:100%;

}

div, h1, h2, h3, h4, p, form, label, input, textarea, img, span{

margin:0; padding:0;

}

ul{

margin:0; 

padding:0; 

list-style-type:none;

font-size:0;

line-height:0;

}

.spacer{

clear:both; 

font-size:0; 

line-height:0;

}

h1

{

font:bold 14px/40px Arial, Helvetica, sans-serif;

}

p

{

font:normal 11px/18px Arial, Helvetica, sans-serif;

padding:0 15px 0 0;

}

.more

{

font:normal 12px/18px Arial, Helvetica, sans-serif;

color:#2e3192;

text-decoration:underline;

}

/* header */

#header

{

margin:0 auto;

background:url(images/hioxindia-pharmacy_02.jpg) 0 0 repeat-x;

}

#navigation

{

margin:0 auto;

background:url(images/hioxindia-pharmacy_04.jpg) 0 0 no-repeat;

height:446px;

width:960px;

}

#navigation ul

{

list-style:none;

}

#navigation ul li

{

font:normal 14px/40px Arial, Helvetica, sans-serif;

display:block;

padding:0 30px;

margin-top:410px;

float:left;

text-align:center;

}

#navigation ul li a

{

text-decoration:none;

display:block;

color:#034ea2;

}

#navigation ul li a:hover

{

text-decoration:none;

display:block;

color:#FFFFFF;

}

#content

{

margin:0 auto;

width:960px;

}

HAVE A NICE DAY!

About namespace in Struts

Example

http://<server>:<port>/<webapp>/<namespace>/<action>.action

you want access action when add more namespace in file Struts

<package name="default" namespace="/login" extends="struts-default">

            <action name="index" class="com.webapp.action.CoreAction">

                  <result name="success">/index.jsp</result>

            </action>

      </package>

after we have screen http://localhost:8080/StrutsDemo/login/index.action

Have a good time!