TomEE: Java EE server database configuration

Configure database access

I use Apache TomEE server and therefore I need to configure it for database purpose before first use.

Install database driver

Drop database driver jar file in tomee/lib folder.

Configure datasource

Resources are usually defined in  server configuration file.

Add datasource resource definition in configuration file located in  tomee/conf/tomee.xml.

<?xml version="1.0" encoding="UTF-8"?>
<tomee>  
  <Resource id="jdbc/db" type="javax.sql.DataSource">
    jdbcDriver org.postgresql.Driver
    jdbcUrl jdbc:postgresql://localhost:5432/bsg_taskmgr_db
    userName postgres
    password postgres
  </Resource>
</tomee>

Verify configuration

After server (tomee service) restart, search for your datasource in log (example log file: tomee/logs/catalina.2016-11-26.log). You will find log entry with your resource id there:

Configuring Service(id=jdbc/db, type=Resource, provider-id=Default JDBC Database)

If you restart server from inside netbeans,  just search in output window where log entries are shown.

Inject datasource in java code and use it

To inject instance of datasource where connection is needed you simply add annotation “@Resource” above variable declaration:

@Resource(name = "jdbc/db")
private DataSource ds;

Let’s see whole example with select statement (jOOQ):

package com.bisaga.demo;

import static com.bisaga.demo.generated.tables.Book.BOOK;
import com.bisaga.demo.generated.tables.records.BookRecord;
import java.sql.SQLException;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.exception.DataAccessException;
import org.jooq.impl.DSL;
/**
 * @author igorb
 */
public class HelloService {

    @Resource(name = "jdbc/db")
    private DataSource ds;    

    public String createHelloMessage(String name) {
        String allBooks = "";
        try {
            DSLContext db = DSL.using(ds, SQLDialect.POSTGRES);
            for(BookRecord b : db.selectFrom(BOOK).fetch() ) {
                allBooks = allBooks.equals("") ? "My books: " : allBooks+";";
                allBooks += b.getTitle();
            }
        } catch (DataAccessException e) {
            System.out.println(e.toString());
        }
        return allBooks;
    }    
}

This example code is called from REST JSON service and result in the browser looks like this:

2016-11-26-16_38_26-localhost_8080_helloworld_webresources_hello

JOOQ – database access layer

Add Java object oriented querying – JOOQ – to project

I love “database first” principle when talking to database. Why would I sacrifice majority of SQL powers, just to talk to database with some ORM technology ? Of course it depend on so many factors, but if you need something very close to database, JOOQ is a way to go.

JOOQ is open source if you use it with open source databases (MySql, Postgres, etc.) !

Configuration and setup

You need to download jooq library and unzip it to some folder (H:\JavaLib\jOOQ-3.8.6).

Code generation config

You need to add jooq config xml file to your project. I added jooq.xml to the root folder.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.8.0.xsd">
  <!-- Configure the database connection here -->
  <jdbc>
    <driver>org.postgresql.Driver</driver>
    <url>jdbc:postgresql://localhost:5432/bsg_taskmgr_db</url>
    <user>postgres</user>
    <password>postgres</password>
  </jdbc>

  <generator>
    <!-- The default code generator. You can override this one, to generate your own code style.
         Supported generators:
         - org.jooq.util.JavaGenerator
         - org.jooq.util.ScalaGenerator
         Defaults to org.jooq.util.JavaGenerator -->
    <name>org.jooq.util.JavaGenerator</name>

    <database>
      <!-- The database type. The format here is:
           org.util.[database].[database]Database -->
      <name>org.jooq.util.postgres.PostgresDatabase</name>

      <!-- The database schema (or in the absence of schema support, in your RDBMS this
           can be the owner, user, database name) to be generated -->
           
      <inputSchema>public</inputSchema>
 		
      <!-- All elements that are generated from your schema
           (A Java regular expression. Use the pipe to separate several expressions)
           Watch out for case-sensitivity. Depending on your database, this might be important! -->
      <includes>.*</includes>

      <!-- All elements that are excluded from your schema
           (A Java regular expression. Use the pipe to separate several expressions).
           Excludes match before includes -->
      <excludes></excludes>
    </database>

    <target>
      <!-- The destination package of your generated classes (within the destination directory) -->
      <packageName>com.bisaga.demo.generated</packageName>

      <!-- The destination directory of your generated classes. Using Maven directory layout here -->
      <directory>./src/java</directory>
      <encoding>UTF-8</encoding>
    </target>
  </generator>
</configuration>

Run code generator

The simplest way is with bash command, this way will always work, even without any special build tool or IDE.

#!/bin/sh
DIR="C:/Programs/jOOQ-3.10.1/jOOQ-lib/"
DRIVER="C:/Programs/drivers/postgresql-42.1.4.jar"
VER="3.10.1"
CP=$DIR"jooq-"$VER".jar"
CP=$DIR"jooq-meta-"$VER".jar;"$CP
CP=$DIR"jooq-codegen-"$VER".jar;"$CP
CP=$DRIVER";"$CP
echo $CP
java -classpath $CP org.jooq.util.GenerationTool jooq.xml

This script is compatible with git-bash on windows. The libraries and folders are of latest version, change to your specific version before run.

“jooq” script is saved in the “bin” folder which is on the path. Now I can run it from everywhere.

Inside Netbeans, open the terminal and type jooq command, you should be in project root folder, where configuration for jooq is.

Generated code

Jooq will generate code for all your tables. You will find new files in packages with name “*.generated.*” :

2016-11-25-17_44_00-start

 

 

 

 

 

 

Flyway – database migration tool

How to setup migration tool for any IDE

There are many ways how to setup migration tool for a specific project, here I will use a command line approach.

As you already know by now, flyway just runs your SQL files with migration DDL commands.  You create new file for each change, incrementally as your database evolve.

We need a folder where we will put files under the project tree.

File names should follow special naming convention for versioned migrations.

Command “fw”

Because I use bash (cygwin) environment inside Netbeans IDE, i will first enable “flyway” tool as a command inside cygwin bash terminal. I create “fw” command file and put it in the “bin” folder which is on the PATH.

#!/bin/sh
/cygdrive/c/Programs/flyway-4.0.3/flyway $@

Netbeans project structure

I have simple hello world java web project. I added a resources, db and migrations folders for data migration files:

├── resources
│   └── db
│       └── migrations
│           ├── V201611222253__Create_book_table.sql
│           └── V201611222338__Add_field_book_table.sql

Configuraton

In root folder, where I will run “fw” command, I added configuration file with the name flyway.conf. This file contain important config settings for flyway to work.

flyway.url=jdbc:postgresql://localhost:5432/bsg_taskmgr_db
flyway.user=postgres
flyway.password=postgres
flyway.schemas=public
flyway.locations=filesystem:./resources/db/migrations

There are many possible settings. You can take prepared flyway.conf file as a base for your configuration file.  Look at conf folder under flyway installation folder, there is sample conf file with comments around every possible configuration key.

Final structure

In simplified view (I omit lot of files here) the structure of a project folder looks like :

.
├── build.xml
├── flyway.conf
├── library.xml
├── build
├── dist
├── nbproject
├── resources
│   └── db
│       └── migrations
│           ├── V201611222253__Create_book_table.sql
│           └── V201611222338__Add_field_book_table.sql
├── src
│   ├── conf
│   │   └── MANIFEST.MF
│   └── java
│       ├── com
│       │   └── bisaga
│       │       └── demo
│       │           ├── generated
│       │           ├── HelloResource.java
│       │           └── HelloService.java
│       └── org
│           └── netbeans
│               └── rest
│                   └── application
│                       └── config
│                           └── ApplicationConfig.java
├── test
└── web
    ├── calc.js
    ├── index.html
    ├── META-INF
    │   └── context.xml
    └── WEB-INF
        ├── beans.xml
        └── web.xml

Migrations SQL files

This two files are simple migration files:

create table book (
    id serial not null,
    title text not null,

    constraint book_pk primary key (id)
);

and second file where we add new field to the table :

alter table book add column author text not null;

Running migrations

There are only few commands. For start, you need only two (info and  migrate). After you write your first migration file, just try out “info” command. You should see pending migration record for your SQL file.

$ fw info
$ fw info

Database: jdbc:postgresql://localhost:5432/bsg_taskmgr_db (PostgreSQL 9.4)

+--------------+-------------------+---------------------+---------+
| Version      | Description       | Installed on        | State   |
+--------------+-------------------+---------------------+---------+
| 201611222253 | Create book table |                     | Pending |
+--------------+-------------------+---------------------+---------+

Now you can migrate first file:

$ fw migrate
$ fw migrate
$ Flyway 4.0.3 by Boxfuse

Database: jdbc:postgresql://localhost:5432/bsg_taskmgr_db (PostgreSQL 9.4)
Successfully validated 1 migration (execution time 00:00.012s)
Creating Metadata table: "public"."schema_version"
Current version of schema "public": << Empty Schema >>
Migrating schema "public" to version 201611222253 - Create book table
Successfully applied 1 migration to schema "public" (execution time 00:01.265s).

After adding next SQL file in “migrations” folder you can again try status of current migrations with “info” comand:

$ fw info
$ Flyway 4.0.3 by Boxfuse

Database: jdbc:postgresql://localhost:5432/bsg_taskmgr_db (PostgreSQL 9.4)

+--------------+----------------------+---------------------+---------+
| Version      | Description          | Installed on        | State   |
+--------------+----------------------+---------------------+---------+
| 201611222253 | Create book table    | 2016-11-22 23:36:10 | Success |
| 201611222338 | Add field book table |                     | Pending |
+--------------+----------------------+---------------------+---------+

You see there is a Pending change from your last migration file. After another “migrate” command, again this migrations will all be marked as “Installed”.

Ok, that’s it ! Migrations are now in full working state !

How to change “current” folder to “project” folder

For successful execution of any flyway command we need to be in proper folder first. The proper folder is folder where flyway.conf file reside. This is project root folder in my example.

Don’t forget to install “TerminalExtras” plugin for Netbeans, then you can just press Alt+. when you staying on the project root node. Change directory (cd) command will be executed inside terminal window and focus will be set to terminal window.

2016-11-23-00_56_35-helloworld-netbeans-ide-8-2

 

 

Prepare JavaEE/JavaScript development environment

Environment

My current environment consist of windows 10 on HP Pavillion i7 notebook with 8GB RAM, 2 x 500 GB SATA disk.

Java

Install java JDK and set JAVA_HOME and JRE_HOME environment variables.

JAVA_HOME = C:\Program Files\Java\jdk1.8.0_101
JRE_HOME = %JAVA_HOME%\jre

IDE: Netbeans 8.2

Download and install Netbeans for Java EE development.
Netbeans IDE have very small font for menus and project browser etc. You can change size of  base font with parameters on netbeans program start :

"C:\Program Files\NetBeans 8.2\bin\netbeans64.exe" --fontsize 16

You can add this parameter in etc\netbeans.conf file like this :

netbeans_default_options="<old-options> --fontsize 16"

Netbeans plugins

QuickOpener plugin

Open OS shell (windows command window) on the location of the selected file is important feature.

Open /Tools/Plugins and search and install QuickOpener plugin.

Select a file in a project tree and hit Alt+1, command window will open.

Markdown

Markdown is a text-to-HTML conversion tool for web writers. Markdown allows you to write using an easy-to-read, easy-to-write plain text format, then convert it to structurally valid XHTML (or HTML).
This plugin is installed in Netbeans 8.2 as downloaded installation  “nbm” file and installed from there.

NB MindMap Editor

The Plugin keeps information in formatted markdown compatible text files (*.MMD) and show the information content as graphs. This plugin is installed from the netbeans plugin repository.

 

Java EE Server: Apache TomEE Plume 7.0.2

Download and install TomEE Plume server, unzip to some folder, for example:

C:\Programs\apache-tomee-plume-7.0.2

and install as service (with a batch file command).

Prepare tomcat_users.xml file

Username and password are written in tomcat-users.xml file under conf folder.

    <role rolename="tomee-admin"/>
    <role rolename="manager-gui"/>
    <role rolename="manager-script"/>
    <user password="xxxxxxxxxxxxxxx" roles="manager-gui,manager-script,tomee-admin" username="igorb"/>
    <user password="admin123" roles="manager-gui,manager-script,tomee-admin,admin" username="admin"/>

Install TomEE server as windows service

Run as administrator: service.install.as.admin.bat

After installation check all service settings with “tomee.exe” application. In Java tab you should have :

Java virtual machine:
C:\Program Files\Java\jdk1.8.0_101\jre\bin\server\jvm.dll

Java Classpath:
C:\Programs\apache-tomee-plume-7.0.2\bin\bootstrap.jar;C:\Programs\apache-tomee-plume-7.0.2\bin\tomcat-juli.jar;C:\Programs\apache-tomee-plume-7.0.2\bin\tomcat-juli.jar

Java Options:
-javaagent:C:\Programs\apache-tomee-plume-7.0.2\lib\openejb-javaagent.jar
-Dcatalina.base=C:\Programs\apache-tomee-plume-7.0.2
-Dcatalina.home=C:\Programs\apache-tomee-plume-7.0.2
-Djava.endorsed.dirs=C:\Programs\apache-tomee-plume-7.0.2\endorsed
-Djava.io.tmpdir=C:\Programs\apache-tomee-plume-7.0.2\temp
-Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager
-Djava.util.logging.config.file=C:\Programs\apache-tomee-plume-7.0.2\conf\logging.properties
-Djava.awt.headless=true
-XX:+UseParallelGC
-XX:MaxPermSize=256M

Screen captures:

2016-11-21-21_16_07-c__programs_apache-tomee-plume-7-0-2_bin 2016-11-21-21_16_30-c__programs_apache-tomee-plume-7-0-2_bin 2016-11-21-21_16_56-apache-tomee-properties 2016-11-21-21_17_10-c__programs_apache-tomee-plume-7-0-2_bin 2016-11-21-21_17_24-apache-tomee-properties 2016-11-21-21_17_35-c__programs_apache-tomee-plume-7-0-2_binIf service didn’t want to start as service (windows/services), check log files and react accordingly.

Correct IDE proxy setting in server properties

If TomEE server will not want to start or stop propertly from inside Netbeans IDE environment,  then you need to change proxy settings.

Go to:
Servers> (Apache Tomcat EE server)> plataform > “used ide proxy settings”

Uncheck “used ide proxy settings” !

Why use TomeEE Plume server ?

Hot deployment !

TomEE is highly compatible with other JavaEE servers (JBoss, WildFly, etc.) but is simpler for development because of build in hot deployment support ! Immediately after changes in resources (java files, html files, javascript files ..etc.) there is no need to rerun and restart  server. Browser window opened in the debug mode (in chrome with netbeans extension) will refresh automatically.

You need to run application in Debug mode to be able to benefit from hot deploy and unfortunately not all changes in java files are allowed.

 

WildFly/JBoss server for production

For production application server I will use WildFly.
Download ZIP formated file (wildfly-10.1.0.Final.zip) and unzip it to some working folder where your standalone server wil run.  You can also download RedHat JBoss EAP product, it is essentially the same but with some additions.

It is a good idea to have one instance of production Java EE server installed to be able to test applications under production server.

Setup chrome browser

For debugging you will need latest chrome browser.

Add it to the PATH
Add folder of “chrome.exe” program to the environment variable  PATH (for example: “C:\Program Files (x86)\Google\Chrome\Application” )
You can check if chrome folder is already in the path with this windows power-shell command :

    PS C:\> $env:PATH

Debugging Java and JavaScript

When you run web application with “Chrome with Netbeans Connector” for the first time and you don’t have connector installed, you will get dialog for installing chrome extension.  After selecting install options in the dialog, chrome will open and proper extension will be already prepared for installation. Just add extension to chrome and rerun web application under debug.

Program will now stop at any breakpoint in java or javascript files.

Gradle build environment

Download binary distribution file of gradle and unzip it to some folder (C:\Programs\gradle-3.2).

In the Netbeans go to Tools/Plugins and install gradle plugin.  After netbeans restart go to Tools/Options/Miscellaneous/Gradle in in Gradle Installation change gradle installation directory to local folder to where you install gradle before. Put same folder for gradle home directory to.

2016-11-21-23_27_41-helloworld-netbeans-ide-8-2

Flyway database migrations tool

Go to flyway web site and download it. Unzip it to some location (C:\Programs\flyway-4.0.3), now you can config it directly in installed folder or do some extra work to configure it locally inside your java project.

Cygwin environment

If you are using windows, you will probably install cygwin.  After download and run installation program, just check under “Shell” group if you have bash shell selected. You can add additional packages, for example “bash command completion” package.

You can always add additional packages to an existing cygwin installation, just repeat installation procedure again.

Additional packages

All/Utils/tree: Display graphical directory tree

 

Netbeans will open bash terminal if you open Window/IDE Tools/Terminal window menu command.

Home folder for your locally installed cygwin will be at the location of environment variable “HOME”.  Set this variable at desired location on your disk. Create “home” folder and subfolder with your user name.

create windows environment variable 
HOME=h:\home\igorb

Create additional ‘bin’ folder inside home folder and add it to the path.  You will be able to write automated bash scripts and put them there and use them as instantly accessible commands.

mkdir H:\HOME\igorb\bin

add "%HOME%\bin;" to windows PATH

No spaces in PATH variable

You should check PATH  environment variable in windows, cygwin doesn’t convert path correctly if you put a blank space between  folders. Path will still work in windows, but not in cygwin !

For example the path in example will be converted as

PATH=C:\WINDOWS\;C:\WINDOWS\SYSTEM32;
$ echo $PATH
/cygdrive/c/WINDOWS:/cygdrive/c/WINDOWS/SYSTEM32

If any part of the path is not properly converted to /cygdrive/ mappings, you will not be able to rely on the system path.

Netbeans – TerminalExtras plugin

This plugin enable shortcut keys to change current folder in already open terminal in to the node’s directory (with Alt+.)

2016-11-23-00_53_54-pluginsTo be able to change current folder with “Alt+.” command you need to have:

  • Opened local “Terminal” window
  • selected node in the project tree (any node)
  • press “Alt” and “dot” key
    igorb@Pavilion /cygdrive/c/Program Files/NetBeans 8.2
    $ cd "C:\Users\igorb\Documents\NetBeansProjects\HelloWorld\src\java"

    After key command on the “Source Packages” projects node, “cd” command will be executed and focus will move to the terminal.

 

 

Define JRE version in maven project

Define JRE version in maven project definition

When you create new maven project in Eclipse, project is created with java JRE 1.5.  If you wish to change that to something else, you need to add compiler plugin with desired JRE source and target version.

In maven project configuration file (pom.xml) add this “maven-compiler-plugin” with setting for source and target version:

<build>
     <plugins>
        <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.5.1</version>
                <configuration>
                   <source>1.8</source>
                   <target>1.8</target>
                </configuration>
        </plugin>
    </plugins>
</build>

More detailed informations

Refresh Eclipse project

After changing maven project configuration file (pom.xml) you need to refresh eclipse project.

At project level, with right click open Maven submenu and select Update project…

2016-08-25 21_14_37-Jump List for Mozilla Firefox

Other sources :

Maven for building Java applications – Tutorial