SQL - Wyszukanie kluczy obcych

[SQL]
Wyszukanie wszystkich kluczy obcych:

SELECT  obj.name AS FK_NAME,
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
    order by obj.name

Java - wyrażenia regularne regex/maski

Przykładowa walidacja/maski za pomocą wyrażeń regularnych:

public class RegExTest {
    public static void main(String[] args) {
       
        System.out.print("\\a{5}\\d{15} ");
        String path = "abcde123456789012345";
        String regularExpression = "([A-Za-z]{5}+[0-9]{15})";
        Pattern pattern = Pattern.compile(regularExpression);
        System.out.print(pattern.matches(regularExpression, path));
        regularExpression = "([A-Za-z]{5}\\d{15})";
        pattern = Pattern.compile(regularExpression);
        System.out.println(pattern.matches(regularExpression, path));
       
        System.out.print("\\a{2,} ");
        path = "ab";
        regularExpression = "[A-Za-z]{2,}";
        pattern = Pattern.compile(regularExpression);
        System.out.println(pattern.matches(regularExpression, path));

        System.out.print("\\w{3,} ");
        path = "a0_";
        regularExpression = "[A-Za-z0-9_]{3,}";
        regularExpression = "\\w{3,}";
        pattern = Pattern.compile(regularExpression);
        System.out.println(pattern.matches(regularExpression, path));
       
        System.out.print("\\d{11} ");
        path = "12345678901";
        regularExpression = "[0-9]{11}";
        pattern = Pattern.compile(regularExpression);
        System.out.print(pattern.matches(regularExpression, path)+"/");
       
        regularExpression = "\\d{11}";
        pattern = Pattern.compile(regularExpression);
        System.out.println(pattern.matches(regularExpression, path));
       
        System.out.print("[tTnN]{1} ");
        path = "t";
        regularExpression = "[tTnN]{1}";
        pattern = Pattern.compile(regularExpression);
        System.out.println(pattern.matches(regularExpression, path));
       
        System.out.print("[\\w\\d[:punct:]] ");
        path = "1";
        regularExpression = "[\\w\\d\\p{Punct}]{1,}";
        pattern = Pattern.compile(regularExpression);
        System.out.println(pattern.matches(regularExpression, path));

        System.out.print("\\d{2}-\\d{3} ");
        path = "21-010";
        regularExpression = "\\d{2}-\\d{3}";
        pattern = Pattern.compile(regularExpression);
        System.out.println(pattern.matches(regularExpression, path));
       
        System.out.print("\\w ");
        path = "gfasg4wer454351235Fw_";
        regularExpression = "[A-Za-z0-9_]{1,}";
        pattern = Pattern.compile(regularExpression);
        System.out.print(pattern.matches(regularExpression, path)+"/");
       
        regularExpression = "\\w{1,}";
        pattern = Pattern.compile(regularExpression);
        System.out.println(pattern.matches(regularExpression, path));

        System.out.print("^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$ ");
        path = "konto@komena.pl";
        regularExpression = "^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$";
        pattern = Pattern.compile(regularExpression);
        System.out.println(pattern.matches(regularExpression, path));
       
        System.out.print("\\d{9} ");
        path = "123456789";
        regularExpression = "\\d{9}";
        pattern = Pattern.compile(regularExpression);
        System.out.println(": "+pattern.matches(regularExpression, path));

        String input = "linea\nlineb\nlinec";
        Pattern p = Pattern.compile("(?i)lineb", Pattern.DOTALL);
        Matcher m = p.matcher(input);
        boolean b = m.find();
        System.out.println("match found: " + b);
    }
}

Zmiana nazwy ciastka sesyjnego z domyślnego jsessionid

Zmiana nazwy ciastka sesyjnego z domyślnego jsessionid na MYJSESSIONID.

W pliku web.xml należy dodać poniższy wpis: 

<session-config>

<cookie-config>

<name>MYJSESSIONID</name>

</cookie-config>

</session-config>

SQL - Zapis wyniku SQL do pliku

Poniżej prosty sposób na zapis wyniku zapytania do pliku. Kolejne wywołania będą dopisywały wynik na końcu pliku.

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
set colsep ,
spool C:\export_my_table.txt
select * from my_table;
spool off

JAVA/SQL - Wywołanie procedury SQL z poziomu Java/hibernateTemplate

Kiedy niedawno potrzebowałem wywołać kilka procedur/funkcji z poziomu kodu JAVA z u życiem 'HibernateTemplate' napotkałem na problem kiedy funkcja miała zwracać jakąś wartość. 

Poniżej kilka przykładów wywołań: 

1) Wywołanie funkcji zwracającej liczbę osób o podanym imieniu i nazwisku:

create or replace PACKAGE EXAMPLE_PACKAGE AS

  FUNCTION  COUNT_USERS ( fname VARCHAR2, lname VARCHAR2 ) RETURN NUMBER;

END EXAMPLE_PACKAGE;

  

create or replace PACKAGE BODY EXAMPLE_PACKAGE AS

 

  FUNCTION  COUNT_USERS ( fname VARCHAR2, lname VARCHAR2 ) RETURN NUMBER AS

     users_count NUMBER(12);

  BEGIN

    begin

      select count(id) into users_count from USERS where f_name = fname and l_name = lname;

    exception

    when NO_DATA_FOUND then

      users_count := 0;

    end;

    return users_count;  

END EXAMPLE_PACKAGE;

public long countUsersByName(final String fname,

final String lname) {

Long result = (Long) hibernateTemplate.execute(new HibernateCallback() {

Long count= null;

public Object doInHibernate(final Session session)

throws HibernateException, SQLException {

session.doWork(new Work() {

  public void execute(Connection conn) throws SQLException  {

     CallableStatement stmt = conn.prepareCall("CALL EXAMPLE_PACKAGE.COUNT_USERS (?,?) into ?");

     stmt.setString(1, fname);

     stmt.setString(2, lname);

     stmt.registerOutParameter(3, OracleTypes.VARCHAR);

     stmt.execute();

     count= Long.valueOf(stmt.getString(3));

  }

});

return count;

}

});

return result;

}

2) Wywołanie procedury niezwracającej wyniku:

public void updateUsers(final String oldUsername,final String newUsername) {

hibernateTemplate.execute(new HibernateCallback() {

public Object doInHibernate(Session session) throws HibernateException {

Query query = session.createSQLQuery("CALL pck_example.update_username(" +

":old_username, :new_username)");

query.setParameter("old_usernamename", oldUsername);

query.setParameter("new_username", newUsername);

return  query.executeUpdate();

}

});

}

Apache Camel - obsługa wyjątków

Krótki opis jak można realizować oraz zarządzać obsługą wyjątków w Apache Camel.

1) Definicja ErrorHandler - definiowany na poziomie RouteContext:

public class MyDistributeRouteBuilder extends RouteBuilder {
private static final Logger log = Logger.getLogger(MyDistributeRouteBuilder.class);

public void configure() throws Exception {
errorHandler(deadLetterChannel("direct:distributeError")
// punkt przechowywania
.useOriginalMessage().maximumRedeliveries(2)
// liczba wznowień
.onRedelivery(new MyRedeliverProcessor("MyDistributeRouteBuilder")).redeliveryDelay(3000)
// czas opóźnienia
.backOffMultiplier(2)
// mnożnik opóźnienia między próbami
.logStackTrace(true).loggingLevel(LoggingLevel.INFO)
.logRetryStackTrace(true).logHandled(true));

from("direct:distributeError")
.log(LoggingLevel.INFO, "Forward to a route error handling")
.errorHandler(noErrorHandler())
.to("direct:errorHandling").end();
}
}

2) Definicja doTry - definiowana na wybranym fragmencie trasy:

<route>
<from uri="direct:myroute" />
<log message="myroute - BEGIN"
loggingLevel="INFO" logName="com.blogspot.programmingmt" />
<doTry>
<bean ref="someBean" method="prepareException" />
<doCatch>
<!-- catch multiple exceptions -->
              <exception>java.lang.Exception</exception>
                 <handled>
                         <constant>true</constant>
                    </handled>
            <camel:setBody>
<!--             <simple resultType="java.lang.Boolean">false</simple> -->
<simple resultType="java.lang.String">Caught exception message: ${exception.message}</simple>
            </camel:setBody>
            <log message="myroute - EXCEPTION"
loggingLevel="ERROR" logName="com.blogspot.programmingmt" />
        </doCatch>
</doTry>
</route>

3) Definicja noErrorHandler - definiowana na poziomie trasy. Wyłącza obsługę błędów.
Java DSL:
from("direct:distributeError")
.log(LoggingLevel.INFO, "Forward to a route error handling")
.errorHandler(noErrorHandler())
.to("direct:errorHandling").end();

Spring DSL:
<route errorHandlerRef="noErrorHandler">
<from uri="direct:myroute" />
<log message="myroute - BEGIN"
loggingLevel="INFO" logName="com.blogspot.programmingmt" />
<from uri="mock:delete" />
</route>

Weryfikacja sumy kontrolnej pliku/InputStream - md5

Weryfikacja sumy kontrolnej pliku/InputStream z wykożystaniem md5
//filename - nazwa pliku
//attachmentChecksum - suma kontrolna przekazana z plikiem

String checksum = DigestUtils.md5Hex(inputStream);
if(!checksum.equalsIgnoreCase(attachment.getChecksum()) ) {
Formatter formatter = new Formatter();
formatter.format("Checksum verification for file %s. Expected: '%s' but was '%s'", filename, attachmentChecksum, checksum);
throw new Exception(formatter.toString());
}

Apache Camel - Wczytanie XML / JAXB

 [Apache Camel 2.8.0, JAXB, Jboss 6]
Przykład zaczytanie pliku XML do obiektu JAVA, z walidacją względem pliku XSD.

Wymagane dependency:
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-jaxb</artifactId>
            <version>${camel-version}</version>
        </dependency>

//W serwisie dodajemy metodę do uruchomienia pobierania danych z XML:
    public void getBookXML() {
        log.info("Import z XML BEGIN");   
        BookXML bookXML = null;
        Exchange exchFile;
        List<BookDTO> books = new ArrayList<BookDTO>();
        ProducerTemplate producer = camelContext.createProducerTemplate();
        ConsumerTemplate consumer = camelContext.createConsumerTemplate();
        try {
            String endpoint = MyUtils.getParameterOrNull("endpoint.file");
            log.info("endpoint: "+endpoint);
            exchFile = consumer.receive(endpoint, 2000L);
            if (exchFile != null && exchFile.getIn() != null
                    && exchFile.getIn().getBody() != null) {
                log.info("Pobrano z: "
                        + exchFile.getIn().getBody().getClass().toString());
                log.info("ret: " + exchFile.getIn().getBody().toString());

               
                Object validRet = producer.sendBody("direct:unmarshal",
                        ExchangePattern.InOut, exchFile.getIn().getBody());
                if(validRet instanceof Boolean
                        && (Boolean)validRet){
                    bookXML = (BookXML) producer.sendBody("direct:readXmlBook",
                            ExchangePattern.InOut, exchFile.getIn().getBody());
                }else{
                    throw new MyException("B\u0142\u0105d walidacji XML");
                }               
                books = prepareHashMapBooks(bookXML);
                consumer.doneUoW(exchFile);
            } else {
                log.info("Brak pliku do importu");
            }
            log.info("Pobrano: "+books.size());
        } catch (Exception e) {
            e.printStackTrace();
        }   
        log.info("Import z XML END");
    }

//BUILDER TRAS APACHE CAMEL
import org.apache.camel.builder.RouteBuilder;
import org.apache.camel.model.dataformat.JaxbDataFormat;

public class MyRouteBuilderFile extends RouteBuilder{

    @Override
    public void configure() throws Exception {       
        JaxbDataFormat dataFormat = new JaxbDataFormat(false);
        dataFormat.setContextPath("com.bloger.programmingmt.example");
        //przeksztalcenie na z XML na DTO
        from("direct:unmarshalFile")
        .unmarshal(dataFormat)
        .to("log: IN ==>");

        //Parsowania XML na podstawie XSD
        from("direct:unmarshal")
        .convertBodyTo(org.w3c.dom.Document.class)
        .bean(ParserXMLFileProcessor .class, "process")
        .end();
    }

}

//PROCESOR WALIDUJACY STRUKTURE PLIKU XML NA PODSTAWIE XSD
public class ParserXMLFileProcessor implements Processor {
   
    private static final Logger log = Logger.getLogger(ParserXMLFileProcessor.class);
   
    @Autowired
    private CamelContext camelContext;

   
    @Override
    public void process(Exchange exchange) throws Exception {
        Exchange exchFile = null;
        try {
            SchemaFactory factory = SchemaFactory
                    .newInstance(XMLConstants.W3C_XML_SCHEMA_NS_URI);
           
            ConsumerTemplate consumer = camelContext.createConsumerTemplate();
            //Wskazanie pliku XSD do walidacji
            String schemaFileEndpoint = Utils.getParameterOrNull("endpoint.book.file.validator");
            log.info("schemaFileEndpoint: "+schemaFileEndpoint);
            try {
                if(schemaFileEndpoint!=null
                        && schemaFileEndpoint.length()>0){
                exchFile = consumer.receive(schemaFileEndpoint, 2000L);
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new MyException("Wystąpił wyjątek podczas pobierania pliku XSD dla walidacji: "+e.getMessage());
            }
            if (exchFile != null && exchFile.getIn() != null
                    && exchFile.getIn().getBody() != null) {
                log.info("Pobrano z: "
                        + exchFile.getIn().getBody().getClass().toString());
                log.info("ret: " + exchFile.getIn().getBody().toString());
                Schema schema = factory.newSchema((File)((GenericFile)exchFile.getIn().getBody()).getFile());
   
                Validator validator = schema.newValidator();
                validator.validate(new DOMSource((Document)exchange.getIn().getBody()));
                System.out.println("...passed...");
                consumer.doneUoW(exchFile);
            } else {
                log.info("Brak pliku do walidacji struktury");
            }
            exchange.getIn().setBody(Boolean.TRUE);
            exchange.getOut().setBody(Boolean.TRUE);
        } catch (Exception e) {
            e.printStackTrace();
            exchange.getIn().setBody(Boolean.FALSE);
            exchange.getOut().setBody(Boolean.FALSE);
        }
    }
   
    public void setCamelContext(
            CamelContext camelContext) {
        this.camelContext = camelContext;
    }
}

Hibernate - HSQL/SQL - prosty przykład

Zapytania HSQL można definiować w obiektach których te zapytania dotyczą. Poniżej przykłąd zapytań dla klasy Book. Zapytania są zdefiniowane z użyciem adnotacji @NamedQueries i @NamedQuery

1) Dafinicja z użyciem adnotacji @NamedQueries i @NamedQuery

@Entity(name = "Book")
@NamedQueries( {
        @NamedQuery(name = "Book.getElements", query = "SELECT b FROM Book b"),
        @NamedQuery(name = "Book.get", query = "SELECT b FROM Book WHERE b.id = :id")})
@Table(name = "BOOK")
public class Book extends Base {
   ...
}

Przykłady użycia HSQL:
Query q = session.getNamedQuery("Book.getElements");
q = sessionz.createQuery("");
q.setParameter(0, "param");
q.list();
q.executeUpdate();
q.uniqueResult();
q.setMaxResults(max)

2)) Wywołanie zapytań realizuje się wewnątrz DAO. Przykładowe wywołania poniżej:

public class BookDAOImpl implements BookDAO {

private HibernateTemplate hibernateTemplate;

public void setSessionFactory(SessionFactory sessionFactory) {
hibernateTemplate = new HibernateTemplate(sessionFactory);
}
   ...
//Zapytanie definiowane bezpośrednio w DAO
public Book findByName(String name) {
@SuppressWarnings("unchecked")
List<Book> result = (List<Book>) hibernateTemplate.findByNamedParam(
"SELECT t FROM Book t where name= :name"
, "name",
name);
if (!result.isEmpty()) {
return result.get(0);
}
return null;
}

//aktualizacja obiektu z użyciem zapytania zdefiniopwanego w klasie Book
public void updateBookName(final Book book) {
getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
                                Query updateQuery = session.getNamedQuery("Book.updateName");
query.setParameter("name", book.getName());
Integer result = query.executeUpdate();
return result;
}
});
}
}

JAVA - serwis RMI

Przykładowe udostępnienie serwisu RMI. Należy pamiętać że interfejs musi rozszerzać 'Remote' a metody serwisu muszą rzucać wyjątek 'RemoteException'.

1) Interfejs:
public interface IntegrationServiceRMI extends Remote {

void saveUpdateBook(Book   book) throws RemoteException;

}

2) Serwis:
public class IntegrationServiceRMIImpl  implements  IntegrationServiceRMI {
@Override
public void saveUpdateBook(Book book) throws Exception {
             ....
}
}

3) Klient serwisu RMI:
<bean id="integrationService" class="org.springframework.remoting.rmi.RmiProxyFactoryBean">
<property name="serviceUrl">
<value>${rmi.host}</value>
</property>
<property name="serviceInterface" value="com.blogger.programmingmt.interface. IntegrationServiceRMI " />
<property name="lookupStubOnStartup" value="false"/>
</bean>


4) Testowanie połączenia do serwisu RMI:

Registry registry;
IntegrationServiceBO stub = null;
try {
   registry = LocateRegistry.getRegistry("localhost", 1099);
   stub = ( IntegrationServiceRMI ) registry.lookup("integrationService");
} catch (RemoteException e1) {
    e1.printStackTrace();
} catch (NotBoundException e) {
    e.printStackTrace();
}
try {
    stub.saveUpdateBook(book); 
} catch (RemoteException e) {
    e.printStackTrace();
} catch (Exception e) {
    e.printStackTrace();
}

JAVA - kopiowanie obiektów

Przed kopiowaniem/klonowaniem obiektu należy zastanowić się czy chcemy uzyskać kopię wraz z referencją czy nie.


1) Kopiowanie z referencją:

public class MyDTO implements Cloneable{

    public MyDTO(){
    }

 
    ... 

    protected Object clone() throws CloneNotSupportedException {
        return super.clone();
    }
}

2) Kopiowanie bez referencji:
 Najprostszym sposobem jest wykorzystanie klasy 'org.apache.commons.lang.SerializationUtils'
       MyDTO newMyDTO= (MyDTO )SerializationUtils.clone(oldMyDTO);

JNDI / iBatis - konfiguracja połączenie do bazy danych

[Jboss, Oracle, iBatis]

Połączenie do bazy danych można skonfigurować w samej aplikacji podając odpowiednie parametry. Można również podpiąć się do połączenia skonfigurowanego na Jboss.

Drugi sposób wydaje się być lepszy z powodu większej elastyczności.
Poniższy przykład ma jedynie pokazywać jak wszystko spiąć - zdaję sobie sprawę iż zawiera pewne niedociągnięcia :)

Połączenie do Oracle skonfigurowane na Jboss:

Plik konfiguracyjny: JBoss\server\default\deploy\MyApp-ds.xml:
   <datasources>
   <local-tx-datasource>
   <jndi-name>MyAppDS</jndi-name>
   <connection-url>jdbc:oracle:thin:@localhost:1521:myapp</connection-url>
   <driver-class>oracle.jdbc.OracleDriver</driver-class>
   <user-name>myapp</user-name>
   <password>myapp</password>
   <min-pool-size>0</min-pool-size>
   <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
   </local-tx-datasource>
   </datasources>

Konfiguracja spring:
    <bean id="myAppDataBase" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName"><value>java:MyAppDS</value></property>
    </bean>

Połączenie do Oracle skonfigurowane wewnątrz aplikacji:

Konfiguracja spring:
     <bean id="myAppDataBase" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="oracle.jdbc.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@localhost:1521:myapp" />
        <property name="username" value="myapp" />
        <property name="password" value="myapp" />       
    </bean>

Część wspólna konfiguracji:

Konfiguracja spring: 
    <bean class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"
        id="myAppSqlClient">
        <property name="configLocation" value="classpath:/spring/ibatis/sqlMapConfigMyApp.xml" />
        <property name="dataSource" ref="myAppDataBase"/>
    </bean>

    <bean class="org.springframework.orm.ibatis.SqlMapClientTemplate"
        id="myAppSqlMapClientTemplate">
        <property name="sqlMapClient" ref="myAppSqlClient" />
    </bean>

    <bean class="com.blogger.programmingmt.myapp.dao.MyAppDAO"
        id="myappDAO">
        <property name="sqlMapClientTemplate" ref="myAppSqlMapClientTemplate" />
    </bean> 

sqlMapConfigMyApp.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
    <sqlMap resource="spring/ibatis/sqlmaps/myApp.xml"/>
</sqlMapConfig>

myApp.xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="MyApp">

    <resultMap class="com.blogger.programmingmt.myapp.dto.MyTestDTO"
        id="MyTestResultMap">
        <result column="ID" property="id" />
        <result column="IMIE" property="imie" />
        <result column="NAZWISKO" property="nazwisko" />
    </resultMap>

    <select id="findMyTestDTO" resultMap="MyTestResultMap">
    <![CDATA[
      select
          ID    as    ID
      ,    IMIE    as    IMIE
      ,    NAZWISKO    as    NAZWISKO
      from MyApp
    ]]>
    </select>
   
    <update id="updateMyTestDTO" parameterClass="com.blogger.programmingmt.myapp.dto.MyTestDTO">
    <![CDATA[
    update MyApp set
      IMIE = #imie#
    where
      ID = #id#
    ]]>
    </update>
</sqlMap>

Definicja DAO:
public class MyAppDAO extends SqlMapClientDaoSupport {
   
    @SuppressWarnings("unchecked")
    public List<MyTestDTO> findMyTestDTO() throws SQLException{
        return (List<MyTestDTO>) getSqlMapClient().queryForList("findMyTestDTO");
    }
   
    public int updateMyTestDTO(MyTestDTO toUpdate) throws SQLException{
        return getSqlMapClient().update("updateMyTestDTO",toUpdate);
    }

}

Apache Camel - klastrowanie Quartz.

 [Apache Camel 2.8.0, Quartz 1.8.4, Jboss 6]


Konfiguracja Quartz w Spring dla środowiska klastrowego.

Wymagane zależności Maven:

        <dependency>
            <groupId>org.quartz-scheduler</groupId>
            <artifactId>quartz-oracle</artifactId>
            <version>1.8.4</version>          
        </dependency>
        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-quartz</artifactId>
            <version>2.8.0</version>
        </dependency>   

W pliku Context.xml umieszczamy:

    <bean id="quartz" class="org.apache.camel.component.quartz.QuartzComponent">
        <property name="scheduler" ref="scheduler"/>
        <property name="autoStartScheduler" value="true"/>
    </bean>
   
    <bean id="scheduler" class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
        <property name="overwriteExistingJobs" value="true"/>
        <property name="applicationContextSchedulerContextKey" value="applicationContext" />

        <property name="autoStartup" value="false"/>
        <property name="schedulerContextAsMap">
            <map>
                <entry key="CamelQuartzCamelContext" value-ref="camelContextCore"/>
            </map>
        </property>
        <property name="quartzProperties">
            <props>
                <prop key="org.quartz.scheduler.instanceName">${org.quartz.scheduler.instanceName}</prop>
                <prop key="org.quartz.scheduler.instanceId">${org.quartz.scheduler.instanceId}</prop>
                <prop key="org.quartz.scheduler.skipUpdateCheck">${org.quartz.scheduler.skipUpdateCheck}</prop>               
               
                <prop key="org.quartz.threadPool.class">${org.quartz.threadPool.class}</prop>
                <prop key="org.quartz.threadPool.threadCount">${org.quartz.threadPool.threadCount}</prop>
                <prop key="org.quartz.threadPool.threadPriority">${org.quartz.threadPool.threadPriority}</prop>
               
                <prop key="org.quartz.jobStore.misfireThreshold">${org.quartz.jobStore.misfireThreshold}</prop>
                <prop key="org.quartz.jobStore.class">${org.quartz.jobStore.class}</prop>
                <prop key="org.quartz.jobStore.driverDelegateClass">${org.quartz.jobStore.driverDelegateClass}</prop>
                <prop key="org.quartz.jobStore.useProperties">${org.quartz.jobStore.useProperties}</prop>
                <prop key="org.quartz.jobStore.dataSource">${org.quartz.jobStore.dataSource}</prop>
                <prop key="org.quartz.jobStore.tablePrefix">${org.quartz.jobStore.tablePrefix}</prop>
                <prop key="org.quartz.jobStore.isClustered">${org.quartz.jobStore.isClustered}</prop>
                <prop key="org.quartz.jobStore.clusterCheckinInterval">${org.quartz.jobStore.clusterCheckinInterval}</prop>

                <prop key="org.quartz.dataSource.jobScheduler.jndiURL">${org.quartz.dataSource.jobScheduler.jndiURL}
                </prop>
                <prop key="org.quartz.dataSource.jobScheduler.validationQuery">
${org.quartz.dataSource.jobScheduler.validationQuery}
                </prop>
            </props>
        </property>
    </bean>

Plik quartz.properties powinien wyglądać następująco:

#============================================================================
# Configure Main Scheduler Properties
#============================================================================
org.quartz.scheduler.instanceName = ClusteredScheduler
org.quartz.scheduler.instanceId = AUTO
#============================================================================
# Configure ThreadPool
#============================================================================
org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount = 25
org.quartz.threadPool.threadPriority = 5
#============================================================================
# Configure JobStore
#============================================================================
org.quartz.jobStore.misfireThreshold = 60000
org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.oracle.OracleDelegate
org.quartz.jobStore.useProperties = false
org.quartz.jobStore.dataSource = jobScheduler
org.quartz.jobStore.tablePrefix = MY_QRTZ_

org.quartz.jobStore.isClustered = true
org.quartz.jobStore.clusterCheckinInterval = 20000

#============================================================================
# Configure Datasources
#============================================================================
org.quartz.dataSource.jobScheduler.jndiURL = java:MyDS
org.quartz.dataSource.jobScheduler.validationQuery=select 0 from dual
#Konfiguracja do polaczenia bez uzycia jndiName
#org.quartz.dataSource.jobScheduler.driver = oracle.jdbc.driver.OracleDriver
#org.quartz.dataSource.jobScheduler.URL = jdbc:oracle:thin:@localhost:1521:ssid
#org.quartz.dataSource.jobScheduler.user = tp2
#org.quartz.dataSource.jobScheduler.password = tp2
#org.quartz.dataSource.jobScheduler.maxConnections = 5
#org.quartz.dataSource.jobScheduler.validationQuery=select 0 from dual

Skrypt SQL do wykonania na wskazanym schemacie bazy danych:

DELETE FROM MY_QRTZ_JOB_LISTENERS;
DELETE FROM MY_QRTZ_TRIGGER_LISTENERS;
DELETE FROM MY_QRTZ_FIRED_TRIGGERS;
DELETE FROM MY_QRTZ_SIMPLE_TRIGGERS;
DELETE FROM MY_QRTZ_CRON_TRIGGERS;
DELETE FROM MY_QRTZ_BLOB_TRIGGERS;
DELETE FROM MY_QRTZ_TRIGGERS;
DELETE FROM MY_QRTZ_JOB_DETAILS;
DELETE FROM MY_QRTZ_CALENDARS;
DELETE FROM MY_QRTZ_PAUSED_TRIGGER_GRPS;
DELETE FROM MY_QRTZ_LOCKS;
DELETE FROM MY_QRTZ_SCHEDULER_STATE;

DROP TABLE MY_QRTZ_CALENDARS;
DROP TABLE MY_QRTZ_FIRED_TRIGGERS;
DROP TABLE MY_QRTZ_TRIGGER_LISTENERS;
DROP TABLE MY_QRTZ_BLOB_TRIGGERS;
DROP TABLE MY_QRTZ_CRON_TRIGGERS;
DROP TABLE MY_QRTZ_SIMPLE_TRIGGERS;
DROP TABLE MY_QRTZ_TRIGGERS;
DROP TABLE MY_QRTZ_JOB_LISTENERS;
DROP TABLE MY_QRTZ_JOB_DETAILS;
DROP TABLE MY_QRTZ_PAUSED_TRIGGER_GRPS;
DROP TABLE MY_QRTZ_LOCKS;
DROP TABLE MY_QRTZ_SCHEDULER_STATE;


CREATE TABLE MY_QRTZ_JOB_DETAILS
  (
    JOB_NAME  VARCHAR2(80) NOT NULL,
    JOB_GROUP VARCHAR2(80) NOT NULL,
    DESCRIPTION VARCHAR2(120) NULL,
    JOB_CLASS_NAME   VARCHAR2(128) NOT NULL,
    IS_DURABLE VARCHAR2(1) NOT NULL,
    IS_VOLATILE VARCHAR2(1) NOT NULL,
    IS_STATEFUL VARCHAR2(1) NOT NULL,
    REQUESTS_RECOVERY VARCHAR2(1) NOT NULL,
    JOB_DATA BLOB NULL,
    PRIMARY KEY (JOB_NAME,JOB_GROUP)
);
CREATE TABLE MY_QRTZ_JOB_LISTENERS
  (
    JOB_NAME  VARCHAR2(80) NOT NULL,
    JOB_GROUP VARCHAR2(80) NOT NULL,
    JOB_LISTENER VARCHAR2(80) NOT NULL,
    PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER),
    FOREIGN KEY (JOB_NAME,JOB_GROUP)
    REFERENCES MY_QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP)
);
CREATE TABLE MY_QRTZ_TRIGGERS
  (
    TRIGGER_NAME VARCHAR2(80) NOT NULL,
    TRIGGER_GROUP VARCHAR2(80) NOT NULL,
    JOB_NAME  VARCHAR2(80) NOT NULL,
    JOB_GROUP VARCHAR2(80) NOT NULL,
    IS_VOLATILE VARCHAR2(1) NOT NULL,
    DESCRIPTION VARCHAR2(120) NULL,
    NEXT_FIRE_TIME NUMBER(13) NULL,
    PREV_FIRE_TIME NUMBER(13) NULL,
    TRIGGER_STATE VARCHAR2(16) NOT NULL,
    TRIGGER_TYPE VARCHAR2(8) NOT NULL,
    START_TIME NUMBER(13) NOT NULL,
    END_TIME NUMBER(13) NULL,
    CALENDAR_NAME VARCHAR2(80) NULL,
    MISFIRE_INSTR NUMBER(2) NULL,
    JOB_DATA BLOB NULL,
    PRIORITY NUMBER(13) NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (JOB_NAME,JOB_GROUP)
    REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP)
);
CREATE TABLE MY_QRTZ_SIMPLE_TRIGGERS
  (
    TRIGGER_NAME VARCHAR2(80) NOT NULL,
    TRIGGER_GROUP VARCHAR2(80) NOT NULL,
    REPEAT_COUNT NUMBER(7) NOT NULL,
    REPEAT_INTERVAL NUMBER(12) NOT NULL,
    TIMES_TRIGGERED NUMBER(7) NOT NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
    REFERENCES MY_QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE MY_QRTZ_CRON_TRIGGERS
  (
    TRIGGER_NAME VARCHAR2(80) NOT NULL,
    TRIGGER_GROUP VARCHAR2(80) NOT NULL,
    CRON_EXPRESSION VARCHAR2(80) NOT NULL,
    TIME_ZONE_ID VARCHAR2(80),
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
    REFERENCES MY_QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE MY_QRTZ_BLOB_TRIGGERS
  (
    TRIGGER_NAME VARCHAR2(80) NOT NULL,
    TRIGGER_GROUP VARCHAR2(80) NOT NULL,
    BLOB_DATA BLOB NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES MY_QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE MY_QRTZ_TRIGGER_LISTENERS
  (
    TRIGGER_NAME  VARCHAR2(80) NOT NULL,
    TRIGGER_GROUP VARCHAR2(80) NOT NULL,
    TRIGGER_LISTENER VARCHAR2(80) NOT NULL,
    PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER),
    FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
    REFERENCES MY_QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE MY_QRTZ_CALENDARS
  (
    CALENDAR_NAME  VARCHAR2(80) NOT NULL,
    CALENDAR BLOB NOT NULL,
    PRIMARY KEY (CALENDAR_NAME)
);
CREATE TABLE MY_QRTZ_PAUSED_TRIGGER_GRPS
  (
    TRIGGER_GROUP  VARCHAR2(80) NOT NULL,
    PRIMARY KEY (TRIGGER_GROUP)
);
CREATE TABLE MY_QRTZ_FIRED_TRIGGERS
  (
    ENTRY_ID VARCHAR2(95) NOT NULL,
    TRIGGER_NAME VARCHAR2(80) NOT NULL,
    TRIGGER_GROUP VARCHAR2(80) NOT NULL,
    IS_VOLATILE VARCHAR2(1) NOT NULL,
    INSTANCE_NAME VARCHAR2(80) NOT NULL,
    FIRED_TIME NUMBER(13) NOT NULL,
    STATE VARCHAR2(16) NOT NULL,
    JOB_NAME VARCHAR2(80) NULL,
    JOB_GROUP VARCHAR2(80) NULL,
    IS_STATEFUL VARCHAR2(1) NULL,
    REQUESTS_RECOVERY VARCHAR2(1) NULL,
    PRIORITY NUMBER(13) NULL,
    PRIMARY KEY (ENTRY_ID)
);
CREATE TABLE MY_QRTZ_SCHEDULER_STATE
  (
    INSTANCE_NAME VARCHAR2(80) NOT NULL,
    LAST_CHECKIN_TIME NUMBER(13) NOT NULL,
    CHECKIN_INTERVAL NUMBER(13) NOT NULL,
    RECOVERER VARCHAR2(80) NULL,
    PRIMARY KEY (INSTANCE_NAME)
);
CREATE TABLE MY_QRTZ_LOCKS
  (
    LOCK_NAME  VARCHAR2(40) NOT NULL,
    PRIMARY KEY (LOCK_NAME)
);
INSERT INTO MY_QRTZ_LOCKS VALUES('TRIGGER_ACCESS');
INSERT INTO MY_QRTZ_LOCKS VALUES('JOB_ACCESS');
INSERT INTO MY_QRTZ_LOCKS VALUES('CALENDAR_ACCESS');
INSERT INTO MY_QRTZ_LOCKS VALUES('STATE_ACCESS');
INSERT INTO MY_QRTZ_LOCKS VALUES('MISFIRE_ACCESS');

Napotkane problemy:

Wygląda na to że w wersji Camel 2.8.0 jest problem z CronScheduledRoutePolicy dla klastrowania.
Przy restarcie serwera/re-deploy aplikacji pojawia się wyjątek: org.quartz.ObjectAlreadyExistsException: Unable to store Job with name: 'job-...' and group: 'jobGroup-...', because one already exists with this identification.
Niestety na tą chwilę nie znalazłem innego sposobu jak napisanie klasy zarządzającej wskazaną trasą. Start/Stop odbywa się na zasadzie dwóch oddzielnych Quartz które podaję jako parametr. Zapewne ktoś powie że to mało eleganckie ... - nie twierdzę że to szczyt profesjonalizmu. Jednak wybrałem tą opcję po przeanalizowaniu za i przeciw.

Java - polskie znaki

Polskie znaki:

# ą - \u0105
# ć - \u0107
# ę - \u0119
# ł - \u0142
# ń - \u0144
# ó - \u00f3
# ś - \u015b
# ź - \u017a
# ż - \u017c

Spring - injection

Wstrzykiwanie zależności w Spring - temat ogólnie znany ale ...

Wstrzykiwanie do konstruktora:

package com.blogspot.programmingmt.spring.injection;
import org.apache.camel.CamelContext;
import org.apache.log4j.Logger;
public class MyTest {
    private static final Logger log = Logger.getLogger(MyTest.class);
    public MyTest(String param1, CamelContext param2, Long param3) {
        if(param1==null)
            log.error("'param1' is NULL");
       
        if(param2==null)
            log.error("'param2' is NULL");
       
        if(param3==null)
            log.error("'param3' is NULL");       
    }
}

Konfiguracja spring:
<bean id="myTest" class="com.blogspot.programmingmt.spring.injection.MyTest">
        <constructor-arg type="java.lang.String" value="..." />
        <constructor-arg type="org.apache.camel.CamelContext" ref="myCamelContext" />
        <constructor-arg type="java.lang.Long" value="60" />
</bean>


Wstrzykiwanie do zmiennych:
package com.blogspot.programmingmt.spring.injection;

import org.apache.camel.CamelContext;
import org.apache.log4j.Logger;

public class MyTest2 {
    private static final Logger log = Logger
    .getLogger(MyTest2.class);
   
    private String param1;
    private CamelContext param2;
    private Long param3;
   
    public String getParam1() {
        return param1;
    }

    public void setParam1(String param1) {
        this.param1 = param1;
    }

    public CamelContext getParam2() {
        return param2;
    }

    public void setParam2(CamelContext param2) {
        this.param2 = param2;
    }

    public Long getParam3() {
        return param3;
    }

    public void setParam3(Long param3) {
        this.param3 = param3;
    }

    public void check() {
        if(param1==null) log.error("'param1' is NULL");
        if(param2==null) log.error("'param2' is NULL");
        if(param3==null) log.error("'param3' is NULL");       
    }
}

Konfiguracja spring:
<bean id="myTest2" class="com.blogspot.programmingmt.spring.injection.MyTest2">
        <property name="param1" value="..." />
        <property name="param2" ref="myCamelContext" />   
        <property name="param3" value="60" />
</bean>

Apache Camel - wysyłanie wiadomości e-mail smtp

 [Apache Camel 2.8.0]
PRzykłąd procesora wysyłającego wiadomość e-mail z uzyciem ApacheCamel

@Component("MyMailingProcessor")
public class MyMailingProcessor implements Processor {

@Override
public void process(Exchange exchange) throws Exception {
String accountpassword = "accountpassword";
String accountuser = "accountuser";
//Po ':' nalezy podac port
String accountsmtp = "127.0.0.1";

ProducerTemplate producer = exchange.getContext().createProducerTemplate();
StringBuffer mailMessage = new StringBuffer();

mailMessage.append("Tresc wiadomosci e-mail.");
if(exchange.getIn()!=null){
mailMessage.append("\n\nObiekt: ");
mailMessage.append(exchange.getIn().getBody().toString());
}else{
mailMessage.append("\n\nObiekt: brak");
}


if(accountuser!=null && accountuser.length()>0
&& accountsmtp!=null && accountsmtp.length()>0){
Map<String, Object> map = new HashMap<String, Object>();
map.put("To", "userTo@domail.com");
map.put("From", "userfrom@domail.com");
map.put("Subject", "subject");
map.put(Exchange.CONTENT_TYPE, "text/plain; charset=UTF-8");

try {
String endpoint = "smtp://"+accountsmtp+"?username="+accountuser+"&password="+accountpassword;
producer.sendBodyAndHeaders(endpoint
, mailMessage.toString(), map);
} catch (Exception e) {
log.error("Nie udalo sie wyslac wiadomosci: "
+ e.getMessage());
}
}
}

}