Quartz with Spring and embedded database

I'm was testing quartz at localhost with H2 embedded database.
In this case I need quartz tables prepared.

In spring config define database and wha script to load:
<jdbc:embedded-database id="dataSource" type="H2">
<jdbc:script location="classpath:recycle/quartz-h2.sql" />
</jdbc:embedded-database>


Example of spring 3.2 integration with quart 1.8:

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

<beans xmlns      = "http://www.springframework.org/schema/beans"
       xmlns:xsi  = "http://www.w3.org/2001/XMLSchema-instance"
       xmlns:task = "http://www.springframework.org/schema/task"

       xsi:schemaLocation = "http://www.springframework.org/schema/beans
                             http://www.springframework.org/schema/beans/spring-beans.xsd
                             http://www.springframework.org/schema/task
                             http://www.springframework.org/schema/task/spring-task.xsd">
                           
<!-- Setting pool to 10 threads -->
    <task:scheduler id="taskScheduler" pool-size="10"/>

    <!-- Scheduler -->
    <bean id="scheduler" class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
        <property name="configLocation" value="recycle/quartz.properties"/>
        <property name="dataSource" ref="dataSource"/>
        <property name="transactionManager" ref="transactionManager" />
        <property name="triggers">
        <list>
                <ref bean="txRecycleTrigger" />
            </list>
        </property>
        <!-- Fill the schedulerFactoryBeans scheduleContextMap with ALL spring
beans that shall be available for the Job implementations. The SpringBeanJobFactory will inject these beans into newly instantiated workers if they have a setter that corresponds to the beans key. This makes spring beans available even for (persisted and) deserialized Jobs. NOTE: This mechanism isn't supported by Spring 3.0 for the latest Quartz version 2.0.1 but for the previous version 1.8.5 -->
<property name="schedulerContextAsMap">
<map>
<entry key="recycleSimService" value-ref="RecycleSimService" />
<entry key="logEnabled" value="${job.logEnabled}" />
</map>
</property>
    </bean>
 
    <!-- Property ""MISFIRE_INSTRUCTION_FIRE_ONCE_NOW" means that The job is executed immediately after the scheduler discovers misfire situation.  -->
 
    <bean id="txRecycleTrigger" class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">
        <property name="jobDetail"      ref  ="recycleSimJob" />
        <property name="cronExpression" value="${job.recycleScheduler.cronExpression}" />
        <property name="misfireInstructionName" value="MISFIRE_INSTRUCTION_FIRE_ONCE_NOW" />
    </bean>
    <bean id="recycleSimJob" class="org.springframework.scheduling.quartz.JobDetailFactoryBean">
      <property name="jobClass" value="com.samara.job.RecycleSimJob" />
    </bean>
 
</beans>



Job implementation

import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.quartz.StatefulJob;
import org.springframework.scheduling.quartz.QuartzJobBean;

public class RecycleSimJob extends QuartzJobBean implements StatefulJob
{
 private transient static final Logger log = LoggerFactory.getLogger(RecycleSimJob.class);

 private transient RecycleSimService recycleSimService;

 @Override
 protected void executeInternal(JobExecutionContext context) throws JobExecutionException
 {
  if (getRecycleSimService() == null)
  {
   log.warn("recycleSimService is not set!");
   return;
  }
  getRecycleSimService().recycleSims(new Date());
 }

 public RecycleSimService getRecycleSimService()
 {
  return recycleSimService;
 }

 public void setRecycleSimService(RecycleSimService recycleSimService)
 {
  this.recycleSimService = recycleSimService;
 }

}

Schedule job in real time

I'm using injected org.springframework.scheduling.quartz.SchedulerFactoryBean.

JobDetail jobDetail = new JobDetail("deactivation_" + orderId, DeactivateOrderJob.class);
Map<String, Long> params = new HashMap<String, Long>();
params.put("orderId", orderId);
jobDetail.setJobDataMap(new JobDataMap(params));
final SimpleTrigger simpleTrigger = new SimpleTrigger("deactivationTrigger_" + orderId, expiryTime);
schedulerFactory.getScheduler().scheduleJob(jobDetail, simpleTrigger);

DeactivateOrderJob will be waken and it will read orderId from map.

@Override
protected void executeInternal(JobExecutionContext context) throws JobExecutionException
{
     Map params = context.getJobDetail().getJobDataMap();
     Long orderId = (Long) params.get("orderId");
     .....
}


SELECT FOR UPDATE

We have 2 machines with active active configuration and there is table with column for counters.
Db is on 3rd machine.
How to update these columns?


Example from code:

public InitialStateEntity findByIdWithLock(Long id)
{
DetachedCriteria criteria = DetachedCriteria.forClass(InitialStateEntity.class);
criteria.add(Restrictions.idEq(id));
criteria.setLockMode(LockMode.PESSIMISTIC_WRITE);
return (InitialStateEntity) criteria.getExecutableCriteria(getSession()).uniqueResult();
}


Ref:
https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/transactions.html

Change constraint

Table simmapping has foreign key to msisdn on 2 columns msisdn and providerId.
Change key so you can do update on providerId
Database: postgres.

ALTER TABLE public.simmapping
DROP CONSTRAINT fk_simmapping_msisdn,
ADD CONSTRAINT fk_simmapping_msisdn
   FOREIGN KEY (msisdn, msisdn_providerid)
   REFERENCES msisdn(msisdn, providerid)
   ON UPDATE CASCADE;

Redis notes

KEYS * - Returns all keys matching pattern.
FLUSHDB - Delete all the keys of the currently selected DB
FLUSHALL - Delete all the keys of all the existing databases, not just the currently selected one.

Hashes

Hashes are maps between string fields and string values, so they are the perfect data type to represent objects
(eg: A User with a number of fields like name, surname, age, and so forth)
HGETALL key - lists all fields

Sets

Sets are an unordered collection of Strings.
SMEMBERS key - Get all the members in a set

Sorted Sets

Sorted Sets are, similarly to Redis Sets, non repeating collections of Strings. The difference is that every member of a Sorted Set is associated with score, that is used in order to take the sorted set ordered, from the smallest to the greatest score. While members are unique, scores may be repeated.
ZCARD key - Get the number of members in a sorted set
ZRANGE key start stop [WITHSCORES] - Return a range of members in a sorted set, by index

Ref:
Redis homepage
Redis for the real world (IBM - Java development 2.0)

Hibernate/persistence annotations

Some useful annotations when using
hibernate.hbm2ddl.auto to update | create | create-drop
If hbm2ddl.auto is set to update it will not change type of field!
     postgres:  ALTER TABLE users ALTER COLUMN role TYPE varchar(16);

javax.persistence.Lob with/without org.hibernate.annotations.Type

Using postgres and omitting Type for text lobs will cause numbers in database tables. 

Like operator will not work!

Solution:

@Lob

@Type(type = "org.hibernate.type.TextType")

If there was version without @Type and you want to update/migrate, please share your expirience.
(If you add @Type, old records will be read as plain strings.)
// This was tested with hibernate 3.6.10. and 5.1.0

org.hibernate.annotations.ForeignKey

Very useful for not having generated names as foreign keys.

In JPA 2.1 you can use javax.persistence.ForeignKey
@JoinColumn(foreignKey = @ForeignKey(name = "FK_ORDER_CUSTOMER"))

javax.persistence.Enumerated(javax.persistence.EnumType.STRING)

When using enum, store it in db as string, not a number

javax.persistence.Id with javax.persistence.Column

This combination will cause a lot of problems with hsqldb (column annotation is same like Id constraint). Postgres will not complain.

@Id

@Column(name = "ID", nullable = false, unique = true)

Solution:

remove nullable and unique

Tested on hsqldb 2.3.2 (as in memory) and postgres 9.2/9.3

Ref:

http://www.shredzone.de/cilla/page/299/string-lobs-on-postgresql-with-hibernate-36.html
http://java.dzone.com/articles/postgres-and-oracle

Postgres tips

Log to database

psql -p port -U user database

Show databases

\l

Show tables

\d

Describe table

\d+ table_name

Exit

\q

Import script from terminal

(when database was exported in sql file)

psql -p port -U user -d dest_db -a -f fileName.sql
   (psql -U prov -d prov -a -f model-prov.sql)

Select TOP 5 records, char_length

SELECT string1, char_length(string1) FROM table1 ORDER BY createDate DESC LIMIT 5;

Dump (export) database

pg_dump -U user source_db -p port -f fileName.sql

List sequences

\ds
or by using SQL:
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

list sequence dependencies

SELECT p.relname, a.adsrc FROM pg_class p JOIN pg_attrdef a ON (p.relfilenode = a.adrelid);

To get last value of a sequence use the following query:

SELECT last_value FROM test_id_seq;

remove a sequence

DROP SEQUENCE sequenceName;

Change column type

ALTER TABLE ad_hoc_history ALTER COLUMN parameters TYPE text;
(http://www.postgresql.org/docs/9.3/static/sql-altertable.html)

* change the size of a varchar column:
ALTER TABLE simlocation ALTER COLUMN location TYPE varchar(30);

drop constrain (NOT NULL)

ALTER TABLE table ALTER COLUMN column DROP NOT NULL;

Is some range present in system

Table is msisdn and msisdn is key, but it is string.
select count(*) from msisdn where to_number(msisdn,'9999999999999') >=4366305707030 and to_number(msisdn,'9999999999999') <=4366305712029;

Ref:
http://www.postgresqlforbeginners.com/2010/11/interacting-with-postgresql-psql.html
http://www.postgresql.org/docs/9.3/static/app-psql.html
http://www.postgresql.org/docs/9.3/static/functions-string.html
dump:
http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/
http://www.postgresql.org/docs/9.3/static/app-pgdump.html
sequences:
http://www.neilconway.org/docs/sequences/
http://blog.sensible.io/2013/08/24/postgresql-sequences-and-array-column-types.html http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/PostgreSQL_x14316_001.htm
http://www.techonthenet.com/postgresql/functions/to_number.php

Oracle functions

System functions

SYS_GUID()

SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

Ref: http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/functions153.htm

SYSDATE

SYSDATE returns the current date and time set for the operating system on which the database resides.

Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions172.htm

Example:

insert into TABLE2 (oid, created) VALUES(sys_guid(), sysdate)

To_Date function

In Oracle/PLSQL, the to_date function converts a string to a date.
The syntax for the to_date function is:

to_date( string1, [ format_mask ], [ nls_language ] )

string1 is the string that will be converted to a date.
format_mask is optional. This is the format that will be used to convert string1 to a date.
nls_language is optional. This is the nls language used to convert string1 to a date.

Example:
to_date('1.9.2012','dd.mm.yyyy')
to_date('1.9.2012 23:59:59','dd.mm.yyyy HH24:mi:ss')

Between condition

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates

SELECT *

FROM orders

WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')

AND to_date ('2003/12/31', 'yyyy/mm/dd');

is equal to 

WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')

AND order_date <= to_date('2003/12/31','yyyy/mm/dd');

Decode function

In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.
The syntax for the decode function is:

decode( expression , search , result [, search , result]... [, default] )

expression is the value to compare.
search is the value that is compared against expression.
result is the value returned, if expression is equal to search.
default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

For Example:
You could use the decode function in an SQL statement as follows:

SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
     result := 'IBM';
ELSIF supplier_id = 10001 THEN
    result := 'Microsoft';
ELSIF supplier_id = 10002 THEN
    result := 'Hewlett Packard';
ELSE
    result := 'Gateway';
END IF;

The decode function will compare each supplier_id value, one by one.
http://www.techonthenet.com/oracle/functions/decode.php

NVL Function

In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.

The syntax for the NVL function is:

nvl( string1, replace_with )

string1 is the string to test for a null value.

replace_with is the value returned if string1 is null.

Example:

select nvl(commission, 0)

from sales;

This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commission field.

http://www.techonthenet.com/oracle/functions/nvl.php

Pseudocolumns (sequence)

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns:

CURRVAL	returns the current value of a sequence.
NEXTVAL	increments the sequence and returns the next value.

Insert into table that has not-null sequence:

INSERT into terminal (terminal_id, terminal_sequence_number)

VALUES('someId', terminal_seq.NEXTVAL);

Get sequence current value:
SELECT empseq.currval FROM DUAL;

Ref

Basic Elements of Oracle SQL, 6 of 10

Limiting result

MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.

E.g. Return 2 results:

SELECT * FROM table_name WHERE ROWNUM <= 2;

Adding ORDER BY clause will not return proper data!
Solution is to use

SELECT * FROM ( your_query_here ) WHERE ROWNUM <= N.

Ref

https://www.w3schools.com/sql/sql_top.asp
Ask Tom: On ROWNUM and Limiting Results

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