---

Table of contents

1. Database Overview
2. run in postgres mode
3. H2 Database Engine Cheat Sheet
   1. Using H2
   2. Documentation
   3. Database URLs
      1. Embedded
         1. jdbc:h2:~/test ‘test’ in the user home directory
         2. jdbc:h2:/data/test ‘test’ in the directory /data
         3. jdbc:h2:./test ‘test’ in the current(!) working directory
      2. In-Memory
         1. jdbc:h2:mem:test multiple connections in one process, database is removed when all connections are closed
         2. jdbc:h2:mem:unnamed private; one connection
      3. Server Mode
         1. jdbc:h2:tcp://localhost/~/test user home dir
         2. jdbc:h2:tcp://localhost//data/test or jdbc:h2:tcp://localhost/D:/data/test absolute dir
         3. Server start: java -cp *.jar org.h2.tools.Server
      4. Settings
         1. jdbc:h2:..;MODE=MySQL;DATABASE_TO_LOWER=TRUE compatibility (or HSQLDB,…)
         2. jdbc:h2:..;TRACE_LEVEL_FILE=3 log to *.trace.db
   4. Using the JDBC API
   5. Connection Pool
   6. Maven 2
   7. Hibernate
   8. TopLink and Glassfish
   9. Run H2 Database in three different modes:
   10. execute sql scripts
       1. shutdown
       2. start and stop from maven
4. Queries
   1. Intersect
   2. join same object to query against 2 lists
   3. determine the length diff of a group concat
   4. EXAMPLE large query with teary / multi join/ and JSON extractor
   5. EXAMPLE calling method in an HQL statement
   6. EXAMPLE case in a where statement
   7. return all if null or empty
   8. subquery
   9. create tuple
   10. creative count
       1. add/concat chars get length
       2. get groupings where there may be nulls
       3. get list of digits
       4. get correct char but need to count column
          1. Not working need to show one number, find way to count column
   11. SELECT DISTINCT mag FROM Magazine mag
       1. may equivalently be expressed as follows, using the IN operator:
   12. Using HQL(hibernate query language) in findall
       1. USING HQL for execute query
   13. -HQL created using session-
   14. Groovy SQL
   15. pagination server side with PagedListHolder Object

---

Database Overview

---

run in postgres mode

jdbc:h2:mem:test;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE

jdbc:h2:~/siris;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH

---

H2 Database Engine Cheat Sheet

Using H2

• H2
  isopen source,free to use and distribute.

• Download:jar,installer (Windows),zip.

• To start theH2 Console tool, double click the jar file, or runjava -jar h2*.jar,
  h2.bat, orh2.sh.

• A new database is automatically createdby default
  if an embedded URL is used.

• Closing the last connection closes the database.

Documentation

Reference:SQL grammar,functions,data types,tools,API
Features:fulltext search,encryption,read-only(zip/jar),CSV,auto-reconnect,triggers,user functions

Database URLs

Embedded

jdbc:h2:~/test ‘test’ in the user home directory

jdbc:h2:/data/test ‘test’ in the directory /data

jdbc:h2:./test ‘test’ in the current(!) working directory

In-Memory

jdbc:h2:mem:test multiple connections in one process, database is removed when all connections are closed

####

jdbc:h2:mem:test;DB_CLOSE_DELAY=-1 multiple connections in one process, database in not removed when all connections are
closed (may create a memory leak)

jdbc:h2:mem:unnamed private; one connection

Server Mode

jdbc:h2:tcp://localhost/~/test user home dir

jdbc:h2:tcp://localhost//data/test or jdbc:h2:tcp://localhost/D:/data/test absolute dir

Server start: java -cp *.jar org.h2.tools.Server

Settings

jdbc:h2:..;MODE=MySQL;DATABASE_TO_LOWER=TRUE compatibility (or HSQLDB,…)

jdbc:h2:..;TRACE_LEVEL_FILE=3 log to *.trace.db

Using the JDBC API

public class JDBC {
   static closCon() {
      Connection conn = DriverManager.getConnection("jdbc:h2:~/test");
      conn.close();
   }

}

Connection Pool

import org.h2.jdbcx.JdbcConnectionPool;

public class ConnPool {
   static conPool() {
      JdbcConnectionPool cp = JdbcConnectionPool.
              create("jdbc:h2:~/test", "sa", "sa");
      Connection conn = cp.getConnection();
      conn.close();
      cp.dispose();
   }
}

Maven 2

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.220</version>
</dependency>

Hibernate

hibernate.cfg.xml (or use the HSQLDialect)

<property name="dialect">
    org.hibernate.dialect.H2Dialect
</property>

TopLink and Glassfish

Datasource class: org.h2.jdbcx.JdbcDataSource, oracle.toplink.essentials.platform, database.H2Platform

Run H2 Database in three different modes:

1. Server Mode jdbc:h2:tcp://localhost/~/test

   When using H2 db inserver mode(also known as client/server mode) all data is transferred over TCP/IP.
   Before application can use H2 Database in server mode, you need to start the H2 DB within
   the same or another machine.

   To run H2 Database in Server Mode you need the JAR file containing the DB Classes.
   You can download it
   from http://www.h2database.com/html/download.html
   You can then Start the DB in Server mode by executing the H2 DB Runnable JAR file:

   java -jar h2-.jar -webAllowOthers -tcpAllowOthers
   

   start programmatically

    import org.h2.tools.Server;
    public class StartServ {
        // start the H2 DB TCP Server
        Server server = Server.createTcpServer("-tcpPort", "9092", "-tcpAllowOthers").start();
        public static void stopServ() {
            // stop the H2 DB TCP Server
            server.stop();
        }
    }
   

   In server mode, you need to use one of the following JDBC URLs:

   _jdbc:h2:tcp://localhost/~/test_ connect to the ‘test’ database in the user home directory on the server (local computer).
   _jdbc:h2:tcp://192.168.1.3:9092 //data/test_ connect to the ‘test’ database in the /data directory on the remote server.

   Run H2 server in command line:

   java -cp h2-version.jar org.h2.tools.Server -tcp
   

   This will start H2 server program, listening for TCP connections. To know more options, type the following command:

   java -cp h2-version.jar org.h2.tools.Server -help
   

2. Embedded mode jdbc:h2:~/test

   H2 db in embedded mode will be faster, but the downside of it is that_no another process can access the Database
   In the connection string, the Data will be saved into the ‘test’ folder, under the user’s home directory

   To connect to an H2 embedded database, you need to use one of the following JDBC URLs:

   • _jdbc:h2:~/test_ the ‘test’ database in the user home directory

   • _jdbc:h2:./test_ the ‘test’ database in the current directory

   • _jdbc:h2:/data/test_ the ‘test’ database in the /data directory (Unix)

   • _jdbc:h2:D:/data/test_ the ‘test’ database in the D:/data directory (Windows)

3. Mixed mode jdbc:h2:/data/test;AUTO_SERVER=TRUE

   When using automatic mixed mode, you can share the JDBC URL for all applications using the DB.
   By default, the server uses any free TCP port.
   The port can be set manually
   using AUTO_SERVER_PORT=9090.

4. In-Memory Mode

   You can use an H2 database in in-memory mode, which means the database is created in memory when the first connection is established, and is
   removed when all connections are closed.
   The database engine runs in the same process of the application.

   Using H2 in-memory database is preferred for quick testing in which data is not needed to be stored permanently on disk.

   To connect to an H2 in-memory database, you need to use JDBC URLs

   • multiple connections in one process jdbc:h2:mem:test

   • unnamed private; one connection jdbc:h2:mem:

execute sql scripts

jdbc:h2:tcp://localhost/mem:elytron_jdbc_test;DB_CLOSE_DELAY=-1;DB_CLOSE_DELAY=-1;INIT=RUNSCRIPT FROM 'classpath:import.sql

shutdown

jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE

start and stop from maven

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>exec-maven-plugin</artifactId>
    <version>3.1.0</version>
    <executions>
        <execution>
            <!-- start H2 DB before integration tests -->
            <id>start</id>
            <phase>pre-integration-test</phase>
            <goals>
                <goal>java</goal>
            </goals>
            <configuration>
                <mainClass>com.mastertheboss.StartServer</mainClass>
            </configuration>
        </execution>
        <execution>
            <!-- stop H2 DB after integration tests -->
            <id>stop</id>
            <phase>post-integration-test</phase>
            <goals>
                <goal>java</goal>
            </goals>
            <configuration>
                <mainClass>com.mastertheboss.StopServer</mainClass>
            </configuration>
        </execution>
    </executions>
</plugin>

Queries

Intersect

select *
from LAB_TEST_SERVICES_POJO lts
where exists
    ( select lsm.inttestid from LAB_SPECIMEN_MAPPING lsm where lsm.status = 1 and lts.inttestid = lsm.inttestid )
  and exists
    ( select ltl.inttestid from LAB_TEST_LOCATION ltl where ltl.status = 1 and lts.inttestid = ltl.inttestid )

join same object to query against 2 lists

select o
from Object as o
         join o.otherObjects as otherObject
where otherObject in :allowedotherobjects
  and otherObject not in :excludedotherobjects

determine the length diff of a group concat

(CHAR_LENGTH (GROUP_CONCAT(CONCAT(user.id, manager.id))) - CHAR_LENGTH (REPLACE(GROUP_CONCAT(CONCAT(user.id, manager.id)), ',', '' )))

EXAMPLE large query with teary / multi join/ and JSON extractor

select USER.FIRSTNAME,
       USER.LASTNAME,
       USER.USERMETADATA,
       USER.LASTLOGIN,
       USER.PICTUREURL,
       MANAGER.ID,
       MANAGER.EMAIL,
       MANAGER.FIRSTNAME,
       MANAGER.LASTNAME,
       MANAGER.USERMETADATA,
       MANAGER.LASTLOGIN,
       MANAGER.PICTUREURL,
       MANAGER.EXTERNALEMPLOYEECODE,
       AO.ID,
       AO.COMPANYINTERVIEWNAME,
       AO.COMPLETEDDATE,
       AO.CATALOGDETAIL.ID,
       SM.ALIAS)
from ${User user} left outer join ${UserRelationship ur}
with ur.user.id = user.id or ur.manager.id = user.id left outer join ${ur.manager} manager inner join ${AssessmentOrder ao}
with ao.id = CAST ((CONCAT(FUNCTION ('JSON_EXTRACT', user.userMetadata, ' $.defaultAssessmentOrderId '))) as java.lang.Long) left outer join ${InterviewModel im}
with im.sourceId = ao.catalogDetail.interviewModelId and im.source = (case when ao.catalogDetail.type = ' AO6 ' then ' TBSIX ' when ao.catalogDetail.type = ' A05 ' then ' TBFIVE ' when ao.catalogDetail.type = ' P2P ' then ' TBFIVE ' end) left outer join ${ScoringModel sm}
on sm.id = (case when im.source = ' TBFIVE ' then (select s from ScoringModel s where s.interviewModelId = im.id) else (select s from ScoringModel s where s.sourceId = ao.catalogDetail.interviewModelId) end)
where user.clientSetupId = ${clientSetupId}
  and user.id in (${searchStrings?.lastName ? findAllIdsByFirstNameAndLastName(searchStrings.firstName.toString()
    , searchStrings.lastName.toString())*.getId().join(', ')

calling method

findAllByFirstNameOrLastNameOrEmail(searchStrings.firstName.toString())*.getId().join('')

select distinct NEW COM.TALENTBANK.CORE.USERMAP(USER.ID, USER.USERNAME, USER.CLIENTSETUPID, USER.EMAIL, USER.FIRSTNAME, USER.LASTNAME, USER.USERMETADATA, USER.LASTLOGIN, USER.PICTUREURL,
        MANAGER.ID, MANAGER.EMAIL, MANAGER.FIRSTNAME, MANAGER.LASTNAME, MANAGER.USERMETADATA, MANAGER.LASTLOGIN, MANAGER.PICTUREURL, coalesce(MANAGER.EXTERNALEMPLOYEECODE, 0),
        coalesce(AO.ID, 1), coalesce(AO.COMPANYINTERVIEWNAME, 'p'), coalesce(AO.COMPLETEDDATE, '00/00/00'), coalesce(AO.CATALOGDETAIL.ID, 1), coalesce(SM.ALIAS,  'p'))
from ${USER USER}
         left outer join "${USERRELATIONSHIP UR}" WITH ur.user.id = user.id or ur.manager.id = user.id
    left outer join ur.manager manager
    left outer join "${AssessmentOrder ao} "
with ao.id = CAST ((CONCAT( '', FUNCTION ('JSON_EXTRACT', user.userMetadata, '$.defaultAssessmentOrderId'), '')) as java.lang.Long) left outer join ${InterviewModel im}
with im.sourceId = ao.catalogDetail.interviewModelId and im.source = (case when ao.catalogDetail.type = 'AO6' then 'TBSIX' when ao.catalogDetail.type = 'A05' then 'TBFIVE' when ao.catalogDetail.type = 'P2P' then 'TBFIVE' end) left outer join ${ScoringModel sm}
on sm.id = (case when im.source = 'TBFIVE' then (select s from ScoringModel s where s.interviewModelId = im.id) else (select s from ScoringModel s where s.sourceId = ao.catalogDetail.interviewModelId) end)
where user.clientSetupId = "${clientSetupId} "

EXAMPLE calling method in an HQL statement

select distinct new COM.TALENTBANK.CORE.DTO.USERTEAM.TEAMSEARCHDTOMAP(USER.ID, USER.USERNAME, USER.CLIENTSETUPID, USER.EMAIL, USER.FIRSTNAME, USER.LASTNAME, USER.USERMETADATA, USER.LASTLOGIN, USER.PICTUREURL,
MANAGER.ID, MANAGER.EMAIL, MANAGER.FIRSTNAME, MANAGER.LASTNAME, MANAGER.USERMETADATA, MANAGER.LASTLOGIN, MANAGER.PICTUREURL,
MANAGER.EXTERNALEMPLOYEECODE)
from USER USER
         left outer join UserRelationship ur
with ur.user.id = user.id or ur.manager.id = user.id left outer join user manager
on manager.id = ur.manager.id
where user.clientSetupId = 2000
  and user.id in (${ findAllByFirstNameOrLastNameOrEmail(searchStrings.firstName)*.getId().join(' , ')})

EXAMPLE case in a where statement

select distinct new Map( USER.ID as user, MANAGER.ID as manager )
from USER USER
         left outer join UserRelationship ur
with ur.user.id = user.id or ur.manager.id = user.id left outer join user manager
on manager.id = ur.manager.id
where user.clientSetupId in (55
    , 2000)
  and (user.firstName like '%'||'${testSearch}'||'%'
   or user.lastName like '%'||'${testSearch}'||'%'
   or manager.firstName like case when ${searchManagerName} = true then ('%'||'${testSearch}'||'%') else '' end
   or manager.lastName like case when ${searchManagerName} = true then ('%'||'${testSearch}'||'%') else '' end )

return all if null or empty

select distinct new Map( USER.ID as user, MANAGER.ID as manager )
from USER USER
         left outer join UserRelationship ur
with ur.user.id = user.id or ur.manager.id = user.id left outer join user manager
on manager.id = ur.manager.id
where user.clientSetupId in (${clientSetUpIdList.join(' , ') ?: ClientSetup.all.id.join(' , ') })
  and (user.firstName like ${testSearch.user}
   or user.lastName like ${testSearch.user}
   or manager.firstName like ${testSearch.manager}
   or manager.lastName like ${testSearch.manager} )

subquery

SELECT u
FROM USER u
WHERE exists (SELECT 1
              FROM USER user
    LEFT OUTER JOIN UserRelationship UR WITH UR.USER.ID = USER.ID OR UR.MANAGER.ID = USER.ID
    left OUTER JOIN User manager WITH MANAGER.ID = UR.MANAGER.ID
    WHERE user = u
        AND USER.CLIENTSETUPID = 2000)

create tuple

SELECT CONCAT('[', USER.ID, ':', IFNULL(MANAGER.ID, 'null'), ']')
FROM USER USER
LEFT OUTER JOIN UserRelationship ur
WITH ur.user.id = USER.id OR ur.manager.id = USER.id
    LEFT OUTER JOIN USER manager
WITH manager.id = ur.manager.id
WHERE USER.clientSetupId = 2000

creative count

add/concat chars get length

SELECT LENGTH(CONCAT(FUNCTION('GROUP_CONCAT', ',')))
From User user
                         LEFT OUTER JOIN UserRelationship ur
with ur.user.id = user.id or ur.manager.id = user.id
    left OUTER JOIN User manager
with manager.id = ur.manager.id
where user.clientSetupId = 2000
group by user.id, manager.id

get groupings where there may be nulls

select NEW Map( max(USER.ID) as userId , (select concat('{', group_concat(concat(coalesce(UR1.ID, 'noRelationship'), ':[{' ,
                         USER.ID, ':' , coalesce(MANAGER1.ID, 'null'), '}]' )) , '}')
                         from USER USER
                         join UserRelationship ur1 with ur1.user.id = user.id or ur1.manager.id = user.id
                         join user manager1 with manager1.id = ur1.manager.id) as TUPLES )
from USER USER
         left outer join UserRelationship ur
with ur.user.id = user.id or ur.manager.id = user.id left outer join user manager
with manager.id = ur.manager.id
where user.clientSetupId = 2000
group by user.id, manager.id

get list of digits

select max(USER.ID),
       (select distinct concat(group_concat(1))
        from USER u
                 left outer join USERRELATIONSHIP ur with ur.user.id = u.id or ur.manager.id = u.id
    left outer join user m
        with m.id = ur.manager.id
        where u.clientSetupId = 2000 and manager.id = m.id)
from USER USER
         left outer join UserRelationship ur
with ur.user.id = user.id or ur.manager.id = user.id left outer join user manager
with manager.id = ur.manager.id
where user.clientSetupId = 2000
group by user, manager

get correct char but need to count column

Not working need to show one number, find way to count column

select (select count(u.id)
        from USER u
                 left outer join USERRELATIONSHIP ur with ur.user.id = u.id or ur.manager.id = u.id
    left outer join user m
        with m.id = ur.manager.id
        where u.id in ( select distinct CONCAT(''
            , GROUP_CONCAT(user.id
            , manager.id)
            , '') from u subu left outer join UserRelationship subur with subur.user.id = subu.id
           or subur.manager.id = subu.id left outer join user subm with subm.id = subur.manager.id where user.clientSetupId = 2000 group by CONCAT( user.id
            , IFNULL(manager.id
            , 666)) ))
from USER USER
         left outer join UserRelationship ur
with ur.user.id = user.id or ur.manager.id = user.id left outer join user manager
with manager.id = ur.manager.id
where user.clientSetupId = 2000
group by 'all'

select count(*),
       (select length(concat(group_concat('')))
        from USER u
                 left outer join USERRELATIONSHIP rel with rel.user.id = u.id or rel.manager.id = u.id
    left outer join user m
        with m.id = rel.manager.id
        where u.clientSetupId = 2000 and u.id = user.id and (m.id = manager.id or (u.id is not null and m.id is null)))
from USER USER
         left outer join UserRelationship ur
with ur.user.id = user.id or ur.manager.id = user.id left outer join user manager
with manager.id = ur.manager.id
where user.clientSetupId = 2000
group by user, manager

SELECT DISTINCT mag FROM Magazine mag

JOIN mag.articles art

JOIN art.author auth

WHERE auth.lastName = 'Grisham'

may equivalently be expressed as follows, using the IN operator:

SELECT DISTINCT mag FROM Magazine mag,
IN(mag.articles) art
WHERE art.author.lastName = 'Grisham'

Using HQL(hibernate query language) in findall

USING HQL for execute query

 query = """
          select NEW Map(ug.id as id, ug.NAME as NAME, ug.interviewModelId as interviewModelId, ug.visibility as visibility, ug.lastUpdated as lastUpdated, count(ugao.id) as assessmentCount )
          from USERGROUP ug
          left join USERGROUPASSESSMENTORDER ugao on ug.id = ugao.userGroupId
          where ug.userId = :userId
          and ug.type = :type
          group by ug.id
          order by ug.NAME
         """
def groups = UserGroup.executeQuery(query, [userId: principalUser?.id, type: UserGroupType.RESULTGROUP])

-HQL created using session-

-Full Example - Full dynamic HQL, with QueryImpl object

User.withSession { uSession ->
    def q = uSession.createQuery($/select distinct new COM.TALENTBANK.CORE.USERMAP(USER.ID,
                                                           USER.USERNAME, USER.CLIENTSETUPID,
                                                                          USER.EMAIL, USER.FIRSTNAME, USER.LASTNAME, USER.USERMETADATA, USER.LASTLOGIN,
                                                          USER.PICTUREURL,MANAGER.ID, MANAGER.EMAIL ,
                                                                          MANAGER.FIRSTNAME ,MANAGER.LASTNAME ,MANAGER.USERMETADATA, MANAGER.LASTLOGIN
                                                                         ,MANAGER.PICTUREURL
                                                                          ,MANAGER.EXTERNALEMPLOYEECODE)
                                                    from $ user user left join fetch $UserRelationship ur
                                                    on ur.user.id = user.id or ur.manager.id = user.idleft join ur.manager manager
                                                    where user.firstName like CONCAT('%'
                                                        , $searchString
                                                        , '%')
                                                       or user.lastName like CONCAT('%'
                                                        , $searchString
                                                        , '%')
                                                       or user.email like CONCAT('%'
                                                        , $searchString
                                                        , '%')
                                                       or manager.firstName like CONCAT('%'
                                                        , $searchString
                                                        , '%')
                                                       or manager.lastName like CONCAT('%'
                                                        , $searchString
                                                        , '%')
                                                /$)
   q.maxResults = 8
   q.firstResult = 2
   q.list()
}

Groovy SQL

List fetchUsersByNameOrManagerName(String searchString, params) {

   if (!params) return null
   def (firstNameSearch, lastNameSearch, rest) = searchString?.tokenize()
   //        DataSource dataSource = Holders.grailsApplication.mainContext.getBean('dataSource')
   //        Sql groovySql = new Sql(dataSource)
    String query = """select distinct user.id,
                    user.username,
                    user.client_setup_id,
                    user.email,
                    user.first_name,
                    user.last_name,
                    user.user_metadata,
                    user.last_login,
                    user.picture_url,
                    manager.first_name as mgr_first_name,
                    manager.last_name  as mgr_last_name
                  from user
                  left join user_relationship as userRelationship on userRelationship.user_id = user.id
                  left join user manager on userRelationship.manager_id = manager.id
                  where ((user.first_name like '%${searchString}%' || user.last_name like '%${searchString}%' || user.email like
                          '%${searchString}%') ||
                         (user.first_name like '%${firstNameSearch}%' && user.last_name like '%${lastNameSearch}%'))
                  union
                  select distinct user.id,
                    user.username,
                    user.client_setup_id,
                    user.email,
                    user.first_name,
                    user.last_name,
                    user.user_metadata,
                    user.last_login,
                    user.picture_url,
                    manager.first_name as mgr_first_name,
                    manager.last_name  as mgr_last_name
                  from user
                  left join user_relationship as userRelationship on userRelationship.user_id = user.id
                  left join user manager on userRelationship.manager_id = manager.id
                  where ((manager.first_name like '%${searchString}%' || manager.last_name like '%${searchString}%') || (manager.first_name like '%${firstNameSearch}%' && manager.last_name like '%${lastNameSearch}%'))"""
   groovySql.rows(query, 0, 15)
}

pagination server side with PagedListHolder Object

def queryResults = userDataService.searchForUsersWhereNameOrEmailLike(searchString)
def pages = new PagedListHolder(queryResults)
pages.setPage(params.off) //set current page number
pages.setPageSize(params.max) // set the size of page