Calling procedure using SQL

In my case procedure does not need to return anything.
Calling it from DAO layer:

String queryString = "call myProcedure()";
Query query = getSession().createSQLQuery(queryString);
query.executeUpdate();

Associations via criteria

Fetch data from one table by criteria on linked table

Person has Address. (1:1)
Address has field street.
I want to fetch all Persons that has Address with with specific street.
DetachedCriteria criteria= DetachedCriteria.forClass(Person.class);
criteria.createCriteria("address").add(Restrictions.eq("street", "WANTED"));

SELECT *
    FROM
        PERSON p
    inner join
        ADDRESS a
            on p.addressID=a.ID 
  WHERE
a.street=?

Fetch data from one table by having foreign key of linked table

Object A has 1:1 relation to object B.
Table A has foreign key to table B.
Class A has B.
I want to fetch class A and I only have ID of class B.
How to construct criteria?
SELECT * FROM a WHERE b_ID=?
//createCriteria on class A
criteria.add(Restrictions.eq("b.id", b.getId()));
criteria.list();


Fetch data from 3 linked tables

DPT has 1:n relation to OFFCOURTPAYMENT,
OFFCOURTPAYMENT has 1:n relation to SUBJECTASSEMBLY
How to fetch DPTs that belong to specific SUBJECTASSEMBLY?
select d.* from DPT d
INNER JOIN OFFCOURTPAYMENT o ON D.ACTIVEOCPOID = O.OID
INNER JOIN SUBJECTASSEMBLY sa ON SA.OID = O.SUBJECTASSEMBLYOID
where d.COMPANYOID= coid (4)
and D.CURRENTSTATE = 'MIROVANJE'
and SA.RECORDNUMBER = '1'
and SA.SUBJECTABLETYPE = 'OCS_DPT'

SubjectAssembly sa is passed in method
DetachedCriteria crit = DetachedCriteria.forClass(DailyParkingTicket.class);
crit.add(Restrictions.eq("currentDPTState", DPTState.StateName.MIROVANJE));
DetachedCriteria saCrit = crit.createCriteria("activeOcp").createCriteria("subjectAssembly"); // 2 inner joins will be generated
saCrit.add(Restrictions.eq("recordNumber", sa.getRecordNumber()));
saCrit.add(Restrictions.eq("subjectableType", sa.getSubjectableType()));
criteria.getExecutableCriteria(getSession()).list();

Notes

I want to fetch (via join) objects associated with my source object A:
A has list of Bs and B has C.
DetachedCriteria criteria= DetachedCriteria.forClass(A.class);
criteria.setFetchMode("B", FetchMode.JOIN); //must be present!
criteria.setFetchMode("B.C", FetchMode.JOIN);


// without DISTINCT_ROOT_ENTITY @ManyToMany returns full cartesian join!
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

@Column annotation and constraints in db

@Column(unique = true, nullable = false)
private Date startDate;

This annotation is description on startDate column when reading java file.
It is not used to stop inserting or updating database with false values, if you do not have constraints on table!
It can be used for generating table from entity class.

1 exception to the above rule:
If you try insert null into not null field declared in annotation it will throw Exception!

Open file with default program

In my example I have pdf file that I want to be opened as soon as it is saved to disk.


public static void openFile(File fileObj)
{
    StringBuilder sb = new StringBuilder();
    sb.append("cmd.exe /C start ");
    sb.append(fileObj.getAbsolutePath());
    System.out.println("openFile: " + sb);
    try
    {
        Runtime.getRuntime().exec(sb.toString());
    }
    catch (IOException e)
    {
        System.out.println("EEExxxccc");
    }
    }


Hibernate -distinct (with disjunction) via criteria

How to write distinct using Hibernate criteria API?

DetachedCriteria crit = DetachedCriteria.forClass(clazz);

Disjunction dis = Restrictions.disjunction(); // OR
dis.add(Restrictions.eq("billType", BillType.RACUN));
dis.add(Restrictions.eq("billType", BillType.STORNO));
crit.add(dis);

crit.setProjection(Projections.distinct(Projections.property("billSubtype")));

Criteria criteria = crit.getExecutableCriteria(getSession());
return crit.list();

select distinct B.BILLSUBTYPE from bill b where (B.BILLTYPE = 'RACUN' or B.BILLTYPE = 'STORNO')


http://www.jairrillo.com/blog/2009/01/29/how-to-use-left-join-in-hibernate-criteria/

Web -parameter transmission

(from jsf page to bean)

.xhtml:
<h:commandLink value="clickToTransfer" actionListener="#{activeBackingBean.methodName}" title="Hover effect">
<f:param name="myParam" value="TakeMe" />
</h:commandLink>

.java:
String parametar = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get("myParam");

Ref:
4-ways-to-pass-parameter-from-jsf-page-to-backing-bean

Spring autowired behaviour

2 classes implement the same interface, 2 beans defined in configuration file for 2 different implementations.
eg. I have 2 beans of the same interface BillManager.

@Autowired
BillManager manager;

org.springframework.beans.factory.NoSuchBeanDefinitionException: No unique bean of type...

solution:
import org.springframework.beans.factory.annotation.Qualifier;

@Autowired
@Qualifier(value = "billManagerImpl_1") //write bean ID or name
BillManager manager;

Hibernate - SQL Query

This post is written while using Hibernate 3.x version

String sqlQuery =
"select max(to_number(POLJE)) from TABLICA where COMPANYID=:company ";
SQLQuery query = getSession().createSQLQuery(sqlQuery);
query.setParameter("company", 1008);
query.addScalar("max(to_number(POLJE))", Hibernate.LONG); //in db it is varchar
// you need to use StandardBasicTypes.LONG) in Hibernate v 4.3.x
Long l = (Long) query.uniqueResult();
if (l == null)
return 0L;
else return lL;
//if result is empty result set.. returned value will be null

Generated SQL:

select
    max(to_number(POLJE))
from
    TABLICA
WHERE
    COMPANYID=?


When using helper entity (that is not persisted) when data is fetched from many tables you can use ResultTransformer:
select B.BILLNUMBERPREFIX || B.BILLNUMBER AS billNumber, T.CREDIT, T.EVENTDATE ....
query.addScalar("billNumber").addScalar("credit", LongType.INSTANCE).addScalar("eventDate");
//necessary because property CREDIT is not the same as credit in Java (case sensitivity!) query.setResultTransformer(Transformers.aliasToBean(MyHelperClass.class));
log.info("\n\nSQL: " + query.getQueryString() + "\n\n");

How to map enum field?
import org.hibernate.impl.TypeLocatorImpl;
import org.hibernate.type.Type;
import org.hibernate.type.TypeResolver;
import org.hibernate.type.EnumType;

import org.hibernate.type.LongType;

Properties pmParams = new Properties();
pmParams.put("enumClass", "hr.samara.model.PaymentMethod");
pmParams.put("type", "12"); /* EnumType.STRING type = 12 */
Type pmType = new TypeLocatorImpl(new TypeResolver()).custom(EnumType.class, pmParams);

I removed default setter and made custom - bad :(
public void setSmsState(String smsState)
{
   this.smsState = SmsState.valueOf(smsState);
}


Ref:
http://www.journaldev.com/3422/hibernate-native-sql-example-addscalar-addentity-addjoin-parameter-example
https://stackoverflow.com/questions/9633337/hibernate-sql-transformation-fails-for-enum-field-type

Export to pdf

There is datatable and when user click on particular row it needs to be printed.

data table in xhtml:
 <ice:dataTable value="#{activeBackingBean.selectedEntity.subjectableList}" var="bill" ...

inside column put link component instead outputTex, which will pass bill number:
<ice:commandLink value="#{bill.GUINumber}" actionListener="#{activeBackingBean.billToPdf}" style="color:black;" title="Print bill">
   <f:param name="billNumber" value="#{bill.GUINumber}" />
</ice:commandLink>

In backing beanu there is (actionListener) method:
public void billToPdf(ActionEvent ae)
{
read request parametar and save object in class field for later use, call hidden button
String parameter = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get("billNumber");
log.debug("billToPdf: " + parameter);
//find appropriate object (bill)
JavascriptContext.addJavascriptCall(FacesContext.getCurrentInstance(), "document.getElementById('PrintForm:PrintButton').click();");
}

hidden button in xhtml:
<ice:form id="PrintForm">
<ice:commandButton id="PrintButton" visible="false" actionListener="#"
onclick="{location.href='#{downloadServlet}?fileCreator=null&amp;fileType=pdf&amp;fileAction=activeBackingBean.billToPdf'}" />
</ice:form>

Click will call servlet which will call appropriate method in bean via reflection (fileAction -our method which needs to be called):
ApplicationContext ctx = WebApplicationContextUtils.getWebApplicationContext(getServletContext());
ctx.getBean("activeBackingBean");
Object result = MethodUtils.invokeMethod(bean, methodName, iS); //iS is InputStream
ServletOutputStream stream = response.getOutputStream();  ...

in backing beanu method that exports to PDF:
public FileObject billToPdf() throws JRException, IOException
{
if (log.isDebugEnabled()) log.debug("monthBillToPdf: " + bill);
return billManager.billToPdf(bill);
}


Second approach:
call servlet with submitted oid. Via refection call bean method, fetch appropriate object and print it.

Web app security

Principal

• User, device or system that performs an action

Authentication

• Establishing that a principal's credential's are valid
• You are who you say you are? If so, you should know your password!

Authorization

• Deciding if a principal is allowed to perform an action? 
• Are you allowed to see this page? ( "to authorize" is to define access policy)

Secured item

• Resource that is being secured

Confidentiality (data privacy)

• Ensuring that an eavesdropper can’t read an HTTP message being sent from the client to the container

Data integrity

• Ensuring that a hacker can’t alter the contents of an HTTP message while it is in transit from the container to a client.

Spring web configuration

Configuration in web.xml

<!-- Loading Spring Application Context in web application-->
<context-param>
    <description>spring context</description>
    <param-name>contextConfigLocation</param-name>
    <param-value>/WEB-INF/myApp-spring-web.xml</param-value>
or
    <param-value>classpath:/applicationContext-web.xml</param-value>
</context-param>

<listener>
    <description>fetch spring configuration file</description>
    <listener-class>
           org.springframework.web.context.ContextLoaderListener
    </listener-class>
</listener>

Integration with JSF

Configuration in faces-config.xml

<application>

<!-- Enables injecting Spring beans as Faces managed properties -->

<el-resolver>org.springframework.web.jsf.el.SpringBeanFacesELResolver</el-resolver>

</application>

Spring with Hibernate configuration

Loads object from the data source

<bean id="someRepository">

    <property name="sessionFactory" ref="sessionFactory"/>

</bean>

Instructs the container to look for beans with @Transactional and decorate them

<tx:annotation-driven transaction-manager="transactionManager"/>

A Hibernate SessionFactory for mapping entities from object to relation tables

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

   <property name="dataSource" ref="dataSource"/>

   <property name="annotatedClasses">

      <list>

         <value>package.Entity1</value>

         <value>package.Entity2</value>

      </list>

   </property>

   <property name="hibernateProperties">

      <value>

         hibernate.format_sql=true

         hibernate.show_sql=true

      </value>

   </property>

</bean>

A transaction manager for working with Hibernate SessionFactories

<bean id="transactionManager"  

    class="org.springframework.orm.hibernate3.HibernateTransactionManager">

    <property name="sessionFactory" ref="sessionFactory" />

</bean>

Transparent Exception Handling

Annotate classes with @Repository

Define a Spring-provided BeanPostProcessor (eg. <context:annotation-config/> or context:component-scan)


Ref:
https://spring.io/blog/2012/04/06/migrating-to-spring-3-1-and-hibernate-4-1
http://sethuramanmurali.wordpress.com/2013/09/15/difference-between-and-annotation-config-and-component-scan/

Transactions

Motivated by two independent requirements:

• Cuncurrent database access
• Resilience to system failures

A transaction is a sequence of one or more SQL operations treated as a unit.

ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction. For example, a transfer of funds from one bank account to another, even though that might involve multiple changes (such as debiting one account and crediting another), is a single transaction.


Atomic
   Each unit of work is an all or nothing operation. (logging)
Consistent
   Database integrity constraints are never violated.
   It ensures the truthfulness of the database.
Isolated
   Isolating transactions from each other.
   Serializability - Operations may be interleaved, but execution must be equivalent to some sequential (serial) order of all transactions.
Durable
   Committed changes are permanent.
   (If system crashes after transaction commits, all effects of transaction remain in database.)


IMPLEMENTING
in class:
   @Transactional
(uses "transactionManager" by default)

By default, a transaction is rolled back if a RuntimeException has been thrown!
Default settings can be overridden with rollbackFor/noRollbackFor attributes.
After checked exception occurs transaction is commited.

in config file (xml):

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>

<tx:annotation-driven transaction-manager="transactionManager"/>
(Instructs the container to look for beans with @Transactional and decorate them)


Using transactions when only reading data allows Spring to optimize the transaction resources
e.g. 2 sql will be executed in 1 connection, prevents Hibernate from flushing its session, Oracle accepts only SELECT statements