Bash script template with parameter evaluation

just a handy bash script template:

#!/bin/bash

scriptname="script_template"

### functions
function usage
{
    echo -e "usage: $scriptname MANDATORY [OPTION]\n"
    echo -e "MANDATORY mandatory parameter:"
    echo -e "  -m,  --mandatory  VAL  The desc of the mandatory parameter\n"
    echo -e "OPTION optional parameter:\n"
    echo -e "  -h,  --help            Prints this help"
    echo -e "  -o1, --optional1       The optional1 parameter"
    echo -e "  -o2, --optional2 VAL   The optional2 parameter\n"
    echo -e "EXAMPLE:"
    echo -e "  $scriptname -m mandatory -o1 opt1"
}

# more...
#####

### Main

if [ $# == 0 ]; then
    echo "Called with no parameter!"
    usage
    exit 1
fi

mand=
opt1="false" # or use 1 and 0, with default 0
opt2="defaultValue"

# parameter while-loop
while [ "$1" != "" ];
do
    case $1 in
   -m  | --mandatory )    shift
                          mand=$1
                ;;
   -o1 | --optional1 )    opt1="true"
                          ;;
   -o2 | --optional2 )    shift
                          opt2=$1
                ;;
   -h  | --help )         usage
                          exit
                ;;
   *)                     usage
                          echo "The parameter $1 is not allowed"
                          exit 1 # error
                ;;
    esac
    shift
done


if [ "$mand" == "" ]; then
    echo "mandatroy parameter is missing"
    usage
   
    exit 1 # error
fi

echo "params: $mand, $opt1, $opt2"
#####

Comparing JPQL, Criteria string-based and typesafe queries

The last two evenings I stucked in searching for more or less complex examples of different JPA 2 queries. By complex I mean, constructing constructor queries with multiple joins, group by and an average function (avg). The JPQL solution (using a normal query defined as string) wasn't to hard to find, compared to the Criteria API version (introduced in the JPA 2 specification).

In the following article I'm going to describe three semantically identical queries using:

  1. JPQL query
  2. Criteria API string based Query
  3. Criteria API dynamic, typesafe Query (using a canonical Metamodel)

I don't want to go to much in details by explaining Criteria API here, because there are obviously enough resources on the net (see references).  To construct a complex query, I use three Entity Beans: Car, CarModel, CarManufacturer. The dependencies between the classes are straightforward: a car has a reference to a model and the model refer to a car manufacturer. The car also has a property mileage (Double), consider a used car. Furthermore, both, car model and manufacturer, also have a name property (String). Here is the graphical representation of this class structure.

All subsequent queries correspond to the following SQL-Statement:

SELECT cmf.name, AVG(c.mileage)
FROM Car c
INNER JOIN CarModel cm
  ON c.carModel = cm.uuid
INNER JOIN CarManufacturer cmf
  ON cm.manufacturer = cmf.uuid
GROUP BY cmf.name

meaning: Get all car manufacturer name and the belonging average mileage for all cars of the manufacturer.

Before getting started with the interesting stuff, I must introduce an additional class that is essential for this examples. The problem is, if you want to compound your Query result, you have to construct a value object class like CompoudElement I use. It is important that you have a constructor that explicitly matches your Query Selection-clause. So, I want to receive a String (name) and a Double (avg value) for my query, so the class looks something like this:

public class CompoundElement {
   private String name;
   private Double dValue;

   public CompoundElement(String name, Double value) {
      this.name = name;
      this.dValue = value;
   }

    /* needed for Criteria query */
   public CompoundElement(String name, Object num) {
        this.name = name;
        this.dbValue = (Double)num;
   }

    /* getter an setter */
}

The JPQL query isn't so far away from the SQL one. This query is also called constructor query, because of the previous mentioned CompoundElement class you need.

public class SomeClassJPQL {
   @PersistenceContext
   private EntityManager em;

   public List<CompoundElement> findAvgMileageForManufacturer() {
      Query query = em.createQuery(
         "SELECT NEW aw.queries.api.vo.CompoundElement(cmfJoin.name, AVG(c.mileage)) " +
         "FROM Car c " +
         "JOIN c.carModel cm " +
         "JOIN cm.manufacturer cmfJoin " +
         "GROUP BY cmfJoin.name"
         );

      return (List<CompoundElement>)query.getResultList();
   }
}

While the query is executed, the JPA creates a List of CompoundElements, adds a new CompoundElement for every result tuple and returns the constructed result list (see getResultList).

Now lets view the Criteria API code for that string based query. In contrast to the JPQL code, the query here uses the

CompoundElement(String, Object)

constructor. So we have to cast the Object to Double inside the constructor (see CompoundElement definition).

public class SomeClassStrings {
   @PersistenceContext
   private EntityManager em;

   public List<CompoundElement> findAvgMileageForManufacturer() {
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<CompoundElement> q = cb.createQuery(CompoundElement.class);
       
      Root<Car> c = q.from(Car.class);
      Join<Car, CarModel> cmJoin = c.join("carModel");
      Join<CarModel, CarManufacturer> cmfJoin  = cmJoin.join("manufacturer");
       
      Expression<Double> mileage = c.get("mileage");
       
      CompoundSelection<CompoundElement> cSelect = cb.construct(CompoundElement.class, cmfJoin.get("name"), cb.avg(mileage));
      q.select(cSelect);
      Expression<String> manuName = cmfJoin.get("name");
      q.groupBy(manuName);

      TypedQuery<CompoundElement> query = em.createQuery(q);

      return (List<CompoundElement>)query.getResultList();
   }
}

The last example uses the Criteria API typesafe query based on a metamodel which you have to define [JavaEE6Tut]. This is obviously a very annoying task, thats why eclipseLink provides you a metamodel generator [genCanonicalModel]. I had some strange experiences with this generation process using maven and NetBeans 6.8.

Using the standard canonical classes definition results in: Car_, CarModel_ and CarManufacturer_. The query for the typesafe version looks like this:

public class SomeClassDynamic {
   @PersistenceContext
   private EntityManager em;

   public List<CompoundElement> findAvgMileageForManufacturer() {
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<CompoundElement> q = cb.createQuery(CompoundElement.class);
       
      Root<Car> c = q.from(Car.class);
      Join<Car, CarModel> cmJoin = c.join(Car_.carModel);
      Join<CarModel, CarManufacturer> cmfJoin  = cmJoin.join(CarModel_.manufacturer);
 
      CompoundSelection<CompoundElement> cSelect =
            cb.construct(CompoundElement.class, cmfJoin.get(CarManufacturer_.name), cb.avg(c.get(Car_.mileage)));
      q.select(cSelect);
      q.groupBy(cmfJoin.get(CarManufacturer_.name));

      TypedQuery<CompoundElement> query = em.createQuery(q);

      return (List<CompoundElement>)query.getResultList();
   }
}

References:

  • [1] IBM developer works - Dynamic, typesafe queries in JPA 2.0
  • [2] Oracle (Sun) - The Java EE 6 Tutorial Part I
  • [3] In Relation to ... - A typesafe criteria query API for JPA
  • [4] The little island of excitement - Generating JPA 2.0 Criteria canonical metamodels ...
  • [5] Sirius ICT - A Look at JPA 2.0 Criteria API

Short node on (B)Lobs in Entity Beans

Have you ever thought about storing files in an Entity-Table, using the JPA? Well here is the solution:

@Entity
@Table(name = "histories")
@NamedQueries({
   @NamedQuery(name = History.FIND_ALL, query = "SELECT his FROM History his")
})
public class History extends EntityBase {
   public static final String FIND_ALL = "findAllHistories";
   @Temporal(TemporalType.TIMESTAMP)
   @Column(name="import_date")
   private Calendar importDate;
   ...
   /* BEGIN here */
   @Column(name="excel_file", length=20000000, columnDefinition="longblob")
   @Lob
   private byte[] excelFile;
   /* END here */
}

As usual the name in the Column annotation specifies the corresponding Column-name in the destination table.   The length property specifies the length of the Column. Actually, this is important, because the default length, provided by the JPA, is 4096, I think.  Obviously, the columnDefinition defines the datatype, which should be created from the JPA. At least add the @Lob annotation.

The resulting column in the database table should look smth like:

  • name: excel_file
  • nulls allowed: no
  • data type: LONGVARBINARY
  • column size: 2147483647
  • ...

A paper made tux

Actually, I really have to learn for my exam ... but then I found this:

But I think 15 minutes are indeed very optimistic - I took an hour or so. Here is my result of tux:

Happy New Year 2010!

Binz / Rügen - 01.01.2010: