Archive for the ‘SQL’ Category

The best DROP-script for Mysql!

Posted: July 30, 2013 in MySQL, SQL, Ubuntu

There are many situations in which you want to drop all tables in a MySQL database. You can easily drop the schema, but then all the permissions are lost too. There are several solutions out there, but they all require some manual effort. I wanted to get rid off it once and for all and constructed this easy script. With the following evil shell script you can easily drop all tables in your database.

Premisse: it uses a group on table_catalog which may not always be the correct grouping in your database. Please, feel free to adapt!

Just create a textfile like drop.sh and give it appropriate rights to execute. The paste the following content in the file and save it.

#!/bin/bash
echo Dropping database usr:$1, pwd:$2, db:$3
echo "set group_concat_max_len = 5000; select concat('set foreign_key_checks=0; ', group_concat(concat('drop table if exists ', table_name, ';') separator ' ')) from information_schema.tables where table_schema = '"$3"' group by table_catalog;" | mysql -u $1 -p$2 --disable-column-names $3 | mysql -u $1 -p$2 $3

Use ./drop.sh root root my_db et voilá…. all tables are gone.

Off course, things can be improved. I need to check the parameters and add some documentation…

JPA, Criteria and custom objects

Posted: April 15, 2013 in Glassfish, JPA, SQL

Suppose you have a SQL-statement where you select custom fields and or the result of some functions. To make it more interesting, we want to combine this with a group-by statement and an ordering. Let’s us for example say you want to execute the following query in MySQL:

SELECT uuid, GROUP_CONCAT(field1, ':', field2),
       MIN(ss_date_started), 
       MAX(ss_date_ended) 
FROM my_table 
WHERE key = 'xxx' 
GROUP BY group_key 
ORDER BY id ASC

The functions GROUP_CONCAT, MIN and MAX are availble in MySQL. The intention of the GROUP_CONCAT is to get some information out of all the group-rows with issuing additional selects. The question is how we can create the criteria for this query and how we can retrieve the results.

We want the results to be stored in the following object:

public class MyGroup implements Serializable { 
    private String uuid;
    private Map map = new HashMap();
    private Date dateStarted;
    private Date dateEnded; 

    public MyGroup() {
    }
}

In the map we want to store the GROUP_CONCAT strings as a map to avoid other SELECTs. First of all, we get the CriteriaBuilder and the root of the query. The MyEntityForTheTable is the entity which is mapped to the table. This object will act as the base to map our fields.

final CriteriaBuilder cb =
   this.getEntityManager().getCriteriaBuilder();

final CriteriaQuery cq = ...; // we'll talk about this later

final Root root = 
  cq.from(MyEntityForTheTable.class); 

The WHERE clause is pretty simple:

cq.where(cb.equal(root.get(MyEntityForTheTable_.key), key));

The GROUP-BY and the ORDER-BY is even more simple:

cq.groupBy(root.get(MyEntityForTheTable_.groupKey)).
   orderBy(cb.asc(root.get(MyEntityForTheTable_.id)));

Now we need to construct the function calls. We extract the MIN and MAX date out of the group using by writing the following expressions:

cb.function("MIN", Date.class, root.get(MyEntityForTheTable_.dateStarted));

cb.function("MAX", Date.class, 
   root.get(MyEntityForTheTable_.dateEnded)) 

We start with the GROUP_CONCAT in MySQL which can be written as:

cb.function("GROUP_CONCAT", byte[].class, 
    root.get(MyEntityForTheTable_.field1), 
    cb.literal(":"), 
    root.get(MyEntityForTheTable_.field2))

First of all, we cannot take String.class to read out the results. MySQL returns the string as byte-code, so we need to map it to a byte[]. The following arguments are the parameters for the function. The first and the last is the result of the columns. We also want to have a semicolon between the two values which can be achieved by cb.literal(String) function.

Now we need to execute the queries and map it to an object. We can use the construct() method to instantiate a new object inside our query. Unfortunately, our main domain object does not have the proper constructor. So we wrap the POJO class in a new class and add the specific constructor.

public static class WrappedGroup extends MyGroup {

  public WrappedGroup() { }
  public WrappedGroup(
     final String uuid,
     final byte[] serviceStatus,
     final Date dateStarted,
     final Date dateEnded) { }
}

We make sure that this constructor has the byte[] parameter. In this constructor we can convert the byte-array to a string. The you can convert the string to a map of your choice. So, we are almost done. Here is the complete code:

final CriteriaBuilder cb = 
  this.getEntityManager().getCriteriaBuilder();

final CriteriaQuery cq = 
  cb.createQuery(WrappedGroup.class);

final Root root = 
  cq.from(MyEntityForTheTable.class);

And the select part looks like;

cq.select(cb.construct(WrappedGroup.class,
   root.get(MyEntityForTheTable_.uuid), 
   cb.function("GROUP_CONCAT", byte[].class,
      root.get(MyEntityForTheTable_.field1), 
      cb.literal(":"), 
      root.get(MyEntityForTheTable_.field2)),
   cb.function("MIN", Date.class, 
      root.get(MyEntityForTheTable_.dateStarted)),
   cb.function("MAX", Date.class, 
      root.get(MyEntityForTheTable_.dateEnded)))); 

We then use a TypedQuery to get the results, and we are done:

final TypedQuery query =
   this.getEntityManager().createQuery(cq).
   setFirstResult(startResult).
   setMaxResults(maxResults);

The resulting list can be cast to List.

Et voilá, we have a very dynamic query which we can easily refactor and adapt.