Thursday, July 23, 2015

How to manage SQL statements more effectively with Java

If you work with plain Java JDBC without any external libraries, you will need to manage your own SQL statements. Unfortunately Java String does not support muti-lines construct, and you have to use many "quotes" + "concatenation" and makes the SQL very hard to read and manage. This makes it hard to maintain and test (try to copy a SQL from Java code into your SQL client). It would be so nice to keep the entire SQL block of text intact without these Java noise.

Here is a quick solution. Store your SQL queries in XML inside CDATA:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
WHERE u.ID = ?  ]]></value>
        </entry>  </sqls>

Now you just need to read it. One way to do this is with built-in JAXB

import javax.xml.bind.annotation.XmlRootElement;
import java.util.HashMap;
import java.util.Map;

public class SqlMap {
    Map<String, String> sqls = new HashMap<>();

    public Map<String, String> getSqls() {
        return sqls;

    public void setSqls(Map<String, String> sqls) {
        this.sqls = sqls;

    public String getSql(String name) {
        return sqls.get(name);

    public static SqlMap load(String name) throws Exception {
        String xml = Utils.loadString(name);
        SqlMap sqlMap = unmarshallXML(xml );
        return sqlMap;

Another way is to use java.util.Properties#loadFromXML by following their schema DTD. Here is an example of XML:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "">
    <comment>XML Props</comment>
    <!-- Foo entry -->
    <entry key="foo">bar</entry>
    <!-- Query entry -->
    <entry key="query">
                SELECT * FROM USERS

Both of these are built-in from your JDK!

Saturday, June 13, 2015

Java RegEx: How to replace all with pre-processing on a captured group

Need to replace all occurances of a pattern text and replace it with a captured group? Something like this in Java works nicely:

        String html = "<a href='myurl?id=1123'>myurl</a>\n" +
                "<a href='myurl2?id=2123'>myurl2</a>\n" +
                "<a href='myurl3?id=3123'>myurl3</a>";
        html = html.replaceAll("id=(\\w+)'?", "productId=$1'");

Here I swapped the query name from "id" to "productId" on all the links that matched my criteria. But what happen if I needed to pre-process the captured ID value before replacing it? Let's say now I want to do a lookup and transform the ID value to something else?

This extra requirement would lead us to dig deeper into Java RegEx package. Here is what I come up with:

import java.util.regex.*;
    public String replaceAndLookupIds(String html) {
        StringBuffer newHtml = new StringBuffer();
        Pattern p = Pattern.compile("id=(\\w+)'?");
        Matcher m = p.matcher(html);
        while (m.find()) {
            String id=;
            String newId = lookup(id);
            String rep = "productId=" + newId + "'";
            m.appendReplacement(newHtml, rep);
        return newHtml.toString();

Saturday, June 6, 2015

Example of plain Jdbc Support Class

Need to fetch some data from DB quickly with Java? Here is a quick example of plain JDBC JdbcSupport Class to help you.

package zemian.jdbcexample

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public abstract class JdbcSupport {
    private static final Logger LOGGER = LoggerFactory.getLogger(JdbcSupport.class);

    protected DataSource dataSource;

    public JdbcSupport(DataSource dataSource) throws Exception {
        this.dataSource = dataSource;

    protected Row toRowMap(ResultSet rs) throws Exception {
        Row row = new Row();
        ResultSetMetaData meta = rs.getMetaData();
        int count = meta.getColumnCount();
        for (int i=1; i <= count; i++) {
  , rs.getObject(i));
        return row;

    protected List<Row> queryRowList(Connection conn, String sql, Object... params) throws Exception {
        LOGGER.debug("Query sql={}, params={}", sql, Arrays.asList(params));
        List<Row> result = new ArrayList<>();
        try(PreparedStatement st = conn.prepareStatement(sql)) {
            for (int i = 1; i <= params.length; i++) {
                st.setObject(i, params[i -1]);
            try (ResultSet rs = st.executeQuery()) {
                while( {
        return result;

    protected Row queryRow(Connection conn, String sql, Object... params) throws Exception {
        List<Row> rowList = queryRowList(conn, sql, params);
        if (rowList.size() != 1) {
            throw new RuntimeException("No unique result from query.");
        return rowList.get(0);
    public static class Row {
        public Map<String, Object> map = new HashMap<>();

        public <T> T get(String name) {
            T result = (T)map.get(name);
            return result;

        public <T> T get(String name, T defVal) {
            T result = (T)map.get(name);
            if (result == null)
                return defVal;
            return result;

To use it, one may do something like this:

public class AccountStore extends JdbcSupport {
    public AccountStore(DataSource dataSource) {
    public void printAccountProfiles() {
        try(Connection conn = dataSource.getConnection()) {
            Row row = queryRow(conn, sql, "zemian");
            String accountId = row.get("ACCOUNTID");
            String department = "IT";
            List<Row> rowList = queryRowList(conn, sql, accountId, department);
            for (Row row : rowList) {
                System.out.println("Got profile: " +;

Saturday, May 30, 2015

How to setup Intellij IDE war exploded artifact with multiple CDI dependent projects

I have a large Java project with many sub modules, and they have simple top down dependencies like this:


Each of these modules has their own third party dependency jars. When I say top down, it simply means Module from bottom have all the one above sub module and its third party dependencies as well. The project is large, and with many files, but the structure is straight forward. It does have large amount of third party jars though. At the end, the webapp would have over 100 jars packaged in WEB-INF/lib folder!

When you create this project structure in IntelliJ IDE (no I do not have the luxury of using Maven in this case), all the third party dependencies are nicely exported and managed from one Module to another as I create my Project with existing source and third parties jars. I do not need to re-define any redudant jars libraries definitions between Modules. When it come to define ModuleWebAppX at the end, all I have to do is to add ModuleLibG as a project dependency, and it brings all the other "transitives" dependent jars in! This is all done by IntelliJ IDE, which is very nice!

IntelliJ IDE also let you setup Artifacts from your project to prepare for package and deployment that can run inside your IDE servers. By default, any web application will have an option to create a war:exploded artifact definition, and the IDE will automatically copy and update your project build artifacts into this output folder, and it can be deploy/redeploy into any EE server as exploded mode nicely.

All these work really smoothly, until there is one problem that hit hard! The way IntelliJ IDE package default war:exploded artifact is that it will copy all the .class files generated from each Modules into a single "out/artifact/ProjectX_war_exploded" output folder. This works most of the time when our Java package and classes are unique, but not so with resource files that's not unique! My project uses several dependent CDI based modules. As you might know, each CDI module suppose to define their own, one and single location at META-INF/beans.xml to enable it and to customize CDI behavior. But becuase IntelliJ IDE flatten everything into a single output directory, I've lost the unique beans.xml file per each Module!

This problem is hard to troubleshoot since it doesn't produce any error at first, nor it stops the web app from running. It just not able to load certain CDI beans that you have customized in the beans.xml!!!

To resolve this, I have to make the IntelliJIDE artifact dependent modules to generate it's JAR instead of all copy into a single output. But we still want it to auto copy generated build files into the JAR archive automatically when we make a change. Lukcly IntelliJ has this feature. This is how I do it:

1. Open your project settings then select Artifacts on left.
2. Choose your war:exploded artifacts and look to your right.
3. Under OutputLayout tab, expand WEB-INF/lib, then right clik and "Create Archive" > Enter your moduleX name.jar.
4. Right click this newly created archive moduleX.jar name, then "Add Copy of" > "Module Output" and select one of your dependent module.
5. Repeat for each of the CDI based Modules!

I wish there is a easier way to do across all Modules for this, but at least this manual solution works!

Saturday, May 23, 2015

How to package skinny war with plain maven-war-plugin

If you are not using maven EAR plugin, then you can also use plain maven-war-plugin to package a Skinny war package like this:
                    We want to package skinny war to avoid third party jars -->

However, if you ran into the problem I described in last post, then you want a Skinny war, but still want to include the jar it produced from your own web project. In this case, you can try this:

                    We want to package skinny war to avoid third party jars, but we do want the classes from
                    this project to be included -->

The plugin would accept a REGEX expression for exclusion as well, but getting it to work might take you a few tries! If you need more than this, try this online Java REGEX testing tool:

Thursday, May 21, 2015

Getting version string from a Maven based web application

When you package a maven project, it will automatically generate a file inside that will contains the version, artifactId and groupId information. These are handy to have and to display for your web application at runtime. One can use a method like following to retrive it.

public class Application {
     private String version;

     public String getVersion() {
        if (version == null) {
            String res = "META-INF/maven/myapp/";
            URL url = Thread.currentThread().getContextClassLoader().getResource(res);
            if (url == null) {
                version = "SNAPSHOT." + Utils.timestamp();
            } else {
                Properties props = Utils.loadProperties(res);
                version = props.getProperty("version");
        return version;


Sounds good? Not too fast! Turns out you have to do little more trick to have this working properly for deployment. By default the maven war plugin will package your classes files into the WEB-INF/classes, but the are in META-INF at the same level, and not in WEB-INF/classes/META-INF! This resulted the above code not finding your resource from classpath!

To fix this, you need to add the following to your pom.xml file:


This will tell maven to jar up your classes along with the in a separate file, then place it in WEB-INF/lib folder instead of using the unpacked WEB-INF/classes version. This forces the to be properly added and read by our getVersion() method.

Thursday, May 14, 2015

NetBeans projects dependencies

With all the coolness of NetBeans, one of the feature I feel it's lacking is managing project denpendencies natively in IDE. Currently the only way to get this feature is if your Java projects are Maven based. If you have Ant based projects, then you would have to manually do this with hacks, and for each projects/sub-projects! Take a look at these two links:

With today's EE projects, you likely going to have many sub projects, and manually handling their dependencies on each project within the IDE is not fun, and hard to maintain. Basically the time you used to setup third party libraries on a ProjectA is wasted and will not automatically exported to ProjectB if it depends on it within the IDE. NetBeans seems to only use project dependencies as way to link related projects together, and to export the main proejct jar/classes, not its third party libraries. A transitive dependencies management feature is needed. Some of user's responses on this topic seem to simply suggest one should go with Maven. Well, as much as I like to use Maven myself, I don't think it acceptable to tell users to convert their existing projects from Ant to Maven just to use an IDE. The IDE tool should be more transparent in this regard.

It's worth to note that both Eclipse and Intellij have this feature and it's productive. I wish NetBeans can improve on this and provide a solution in near future.