Think Only Java

Friday, October 26, 2012

Maven Configuration

Download the maven from http://maventest.apache.org/download.html (download the latest zip file)

Extract he zip file to say D:\Maven(optional)

Create a environment variable M2_HOME with value D:\Maven\apache-maven-3.0.4

Modify the Path variable; add the entry %M2_HOME%\bin

Create a .M2 (dotM2) folder in your profile (optional, useful once you start use maven repository for your project)

Ex: D:\Documents and Settings\Manu\.m2

Note: as . (Dot) is not allowed as first character in folder name, you need to create a folder from command prompt

Go to command prompt and run mvn –version to know maven is configured

Jboss AS 7 Configuration for Oracle

Jboss Configuration

Download the Jboss server setup from http://www.jboss.org/as7

Extract the zip to your local folder say it will be extracted to D:\Jboss

And rename the folder as jboss 7.1.0 (optional)

Jboss Configuration for Oracle

Open the below path jboss 7.1.0\modules\com

Create a new folder oracledatabase

Open folder oracledatabase and create a new folder oracle

Open folder oracle and create a new folder main

Open folder main and place an ojdbc6.jar

And create an xml file module.xml

Copy the below xml content to module.xml

<?xml version="1.0" encoding="UTF-8"?>

<module xmlns="urn:jboss:module:1.0" name="com.oracledatabase.oracle">

<resources>

<resource-root path="ojdbc6.jar"/>

</resources>

<dependencies>

<module name="javax.api"/>

</dependencies>

</module>

Open folder D:\Jboss\jboss 7.1.0\standalone\configuration

Open standalone.xml and add the below driver entry

<drivers>

<driver name="oracle" module="com.oracledatabase.oracle">

<xa-datasource-class>oracle.jdbc.OracleDriver</xa-datasource-class>

</driver>

</drivers>

Add JBOSS_HOME in environment variable and value as D:\Jboss\jboss 7.1.0

Now your Jboss is configured to access Oracle database

You can start the Jboss server by running D:\Jboss\jboss 7.1.0\bin\standalone.bat

Tuesday, October 23, 2012

Importance of Query Language

From searching to sorting, analytic and business intelligence, efficiently moving data from the database to the application and presenting it to the user is a regular part of enterprise development. Doing so requires the ability to issue bulk queries against the database and interpret the results for the application.

Object Query Language

Developer want the query results to be converted to entities so that the query results are used directly by application logic.
Domain model are abstraction of the physical model (tables) in ORM, so Object Query Language like JPA QL are abstraction over the native SQL

JPA QL – Features

•Single and multiple value result types

•Aggregate functions, with sorting and grouping clauses

•A more natural join syntax, including support for both inner and outer joins

•Conditional expressions involving subqueries

•Update and delete queries for bulk data changes

Result projection into non-persistent classes

JPA QL

•Supports two methods for expressing queries

–Query Language

–Criteria API

And also, we can use native SQL queries

JPA QL in its simplest form

•SQL

–Select EMP_ID, EMP_NAME from EMPLOYEE

•JPA QL

–from Employee

–Select e from Employee e(uses an alias mostly useful in joins)

Select e.empName from Employee e

Querying Employee Objects

@PersistenceContext

EntityManager em;

List<Employees> findAllEmployees()

{

Query query = newEm.createQuery("from Employee");

return query.getResultList();

}

Note: employees is a collection of Employee Entity Objects

Expression in JPA QL

•from Employee emp where emp.empAge between 25 and 30

•from Employee emp where emp.empAge > 26

•from Employee emp where emp.deptName in (‘BIDW', ‘JAVA')

•from Employee e where e.deptName like ‘J%‘

•from Employee e where e.deptName not like ‘J%‘

•from Employee e where ( e.empAge / 20) > 0

•from Employee e

where (e.empName like 'K%' and e.empId = 109 ) or e.empAge in (25, 30 )

Ordering Query Results

•from User u order by u.username

•from User u order by u.username desc

Distinct Rows

•select distinct item.description from Item item

Parameter Binding (Prepared Statements)

•Building queries using String concatenation is buggy and difficult to maintain.

–"from Item i where i.description like '" + search + "'";

•A cleaner approach to building queries is to use parameter binding.

•JPA Supports 2 types

–Named Parameter Binding – Preferred Approach

"from Item item where item.description like :search";

Query q = em.createQuery(queryString)

.setParameter("search", searchString);

–Positional Parameter Binding – Not Recommended

"from Item item where item.description like ?1 and item.date > ?2";

Query q = em.createQuery(queryString)

.setParameter(1, searchString)

.setParameter(2, minDate, TemporalType.DATE);

Pagination using JPA QL

•What is Pagination?

•Approaches

–Fetch complete data from the table and store in session and subsequently retrieve from session. (No query change)

–Fetch data as required from the database. (Query needs to contain the info like number of records to fetch and from which record number for each page).

•JPA QL support

Query query = newEm.createQuery("from Employee");

query.setFirstResult(1);

query.setMaxResults(5);

List employees =

query.getResultList();

Named Queries

•JPA QL queries embedded within Java Code makes the code unreadable and reduces maintainability

•Externalizing queries as metadata using annotations helps resolve the above issues.

•Two Steps

–Externalize the query in your Entity class using @NamedQuery and give it a name

@NamedQueries({

@NamedQuery(

name = "findEmployees",

query = "from Employee")})

–Refer to the query by its name from the Java code.

Query query =

em.createNamedQuery(" findEmployees")

Note: Query names are global and hence must be unique.

Aggregate Functions

•count(), min(), max(), sum() and avg()

select count(i) from Item I

•Using group by

select u.lastname, count(u) from User u

group by u.lastname

When aggregate functions are used the query.getResultList() method returns a list of Object arrays with the values in order as specified in the select clause

Criteria API

•Constructing queries using programming API.

•Simple Example

–SELECT e FROM Employee e WHERE e.name = 'John Smith'

–Equivalent query constructed using the Criteria API

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
c.select(emp) .where(cb.equal(emp.get("name"), "John Smith"));

Saturday, October 6, 2012

iBatis

iBatis data mapping tool assists Java Developers with data access. It is a light weight data mapping JDBC framework which abstracts low level details involved in database communication and helps generating a database persistence layer for your Java application ( which involves mapping of objects to SQL calls, data type conversion, support for static queries as well as dynamic queries).

Why iBatis

Now the question comes,

can we use both technologies together?

The answer is YES, we can.

Working of iBatis Frame work

A set of XML encoded SQL Map files–one for each database table–holds SQL templates that are executed as prepared statements and map the expected results to Java domain classes. From application code, a layer of iBatis Data Access Objects (DAO) acts as the API that executes the SQL Map templates and assigns the results to the corresponding Java domain classes. In other words we can say that iBatis use XML to loosely couple Java objects and SQL commands

Layers of iBatis

The iBatis Layer consists of the following two libraries:

•iBatis Data Access Objects

•iBatis Data Mapper

iBatis Data Mapper: This library makes iBatis special. It allows developers to flexibly map pure SQL to Java collections or custom domain objects. In doing so, development responsibilities are cleanly divided. DBAs can work with pure SQL devoid of language-specific constructs, and business developers can write pure OO business logic without regard for database semantics

iBatis Data Access objects: Commonly known as DAO is used to abstract the communication with Persistence Layer. But this layer is so flexible it can be switched to Hibernate, JDO etc.

Installation

DownLoad:

To install iBatis for use with Java, download "iBatis Java {version} Binaries, Source, and documentation" from here and unzip it. This will result in a directory name "ibatis-2".

• The doc directory contains a PDF version of the Developer Guide and zipped javadoc HTML files.

• The lib directory contains the only JAR file needed, ibatis-{version}.jar.

ClassPath:

To compile and run code that uses iBatis, the following JARs must be in the classpath.

• ibatis-{version}.jar - in iBatis lib directory

• JAR containing the JDBC driver to be used

Sample

Our Database is having a table Employee with Employee Id, Employee name and Employee Department as columns and a table Department with columns Department Id and Department Name.

We will be selecting data from tables based on employee name or Department Name, updating table records for employee department and deleting records from the database tables.

We will be interacting with DB using iBatis with SQL queries written in SQL Maps as well as written in Stored Procedures.

Tables

Create table Employee and Department as mentioned below:

Create table Employee

(

EmpId number not null auto_increment,

EmpName varchar2 not null,

deptId number,

primary key (EmpId)

);

Create table Department

(

DeptId number not null auto_ increment,

DeptName varchar2 not null,

primary key (DeptId)

);

Stored Procedure

/* * Package specification */

CREATE OR REPLACE PACKAGE Employee

TYPE refCursor REF CURSOR; //to get the output list.

PROCEDURE addEmployee (name IN VARCHAR2, dept IN VARCHAR2,

retSts OUT VARCHAR2);

PROCEDURE selAllEmp(EmpList out refCursor);

PROCEDURE selEmpbyId (id IN NUMBER, EmpName OUT VARCHAR2);

PROCEDURE selEmpbyDept (DeptName IN VARCHAR2, EmpList out refCursor);

PROCEDURE delEmployee (EmpName IN, retSts OUT VARCHAR2);

END Employee

/* * Package specification */

CREATE OR REPLACE PACKAGE Department

TYPE refCursor REF CURSOR; //to get the output list.

PROCEDURE addDept (name IN VARCHAR2, retSts OUT VARCHAR2);

PROCEDURE selAllDept(DeptList out refCursor);

PROCEDURE selDeptforEmp (EmpName IN VARCHAR2, DeptName OUT VARCHAR2);

PROCEDURE delDept (DeptName IN, retSts OUT VARCHAR2);

END Employee;

Note: you need complete the body part of the Procedure, here is just outline is given for your reference

iBatis Configuration

Configuration details for the iBatis are mentioned in the SqlMapConfig.xml file.

Before explaining the configuration file I will like to mention the following:

Ø Database will be accessed using JDBC.

Ø Namespace prefixes will be used when referring to "mapped statements" in our Java code.

Ø All the mappings are done through the files Employee_SqlMap.xml and Department_SqlMap.xml.

Ø We will use SqlMapConfig.properties file to write all the database properties so that configuration file can be used independent of database.

Properties File

driver = com.oracle.jdbc.driver.OracleDriver

url = jdbc:oracle:thin:@localhost:8082:Employee

username = username

password = password

Configuration File

<? xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

</dataSource>

</transactionManager> # any xml file created for mapping needs to be added here

</sqlMapConfig>

SQL Maps

Mapped statements give ids to SQL statements. These ids are referenced in the code that invokes those using methods in the org.ibatis.sqlmap.client.SqlMapClient interface such as queryForObject and queryForList. Because we specified that we wanted to "useStatementNamespaces" in our configuration file, our references to statements will have the syntax "{namespace}.{statement-id}".

Mapped statements are defined in XML files that are referenced from the SqlMapConfig.xml file To use “statementNamespaces”

All the SQL Maps mentioned in Configuration file will be compiled into one file after code compiles.

Employee_SqlMap.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">

</parameterMap>

<procedure id=“emp.delemp"

parameterMap=“emp.delEmpParams">

{ call Employee.delEmp(?,?) }

</procedure>

</parameterMap>

<procedure id=“emp.addEmp"

parameterMap=“emp.addEmpParams">

{call Employee.addEmployee(?,?) }

</procedure>

</resultMap>

</parameterMap>

<procedure id=“emp.selAllEmp"

parameterMap=“emp.selAllEmpParams“

resultMap=“emp.selEmpResults">

{call Employee.selAllEmp(?) }

</procedure>

</parameterMap>

<procedure id=“emp.selEmpByDept"

parameterMap=" emp.selEmpByDeptParams"

resultMap=" emp.selEmpResults">

{call Employee.selEmpByDept(?,?) }

</procedure>

</sqlMap>

Department_SqlMap.xml

<? xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">

</resultMap>

</parameterMap>

<procedure id=“dept.selDeptforEmp"

parameterMap="dept.selEmpByDeptParams"

resultMap=" dept.selDeptResults ">

{call Employee.selEmpByDept(?,?) }

</procedure>

</sqlMap>

Call to SqlMaps

•import com.ibatis.dao.client.DaoManager;

•import com.ibatis.dao.client.template.SqlMapDaoTemplate;

private void delEmp(EmpName) throws SQLException {

HashMap params = new HashMap();

params.add(“EmpName”, EmpName);

queryForObject(“emp.delEmp”,params);

String returnStatus = new String("");

returnStatus = (String)params.get("retSts");

}

private void addEmployee(String empName, String deptName) throws SQLException {

HashMap params = new HashMap();

Params.put(“name”, empName);

Params.put(“Emp_id”, deptName);

queryForObject(“emp.addEmp”,params);

String returnStatus = new String("");

returnStatus = (String)params.get("retSts");

}

private List getEmp() throws SQLException {

HashMap params = new HashMap();

List EmpList = queryForList(“emp.selAllEmp”,params);

return EmpList;

}

.....Will be Continued