Tuesday, November 3, 2015

Java Jdbc Test for UTF8 and Default Value for TIMESTAMP

Just a quick MySQL test on how to use UTF8 encoding with JDBC connection string. Also a test on how to set TIMESTAMP default values.

package zemian.jdbc;

import org.junit.Test;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**

 -- drop table ztest_issues;
 CREATE TABLE ztest_issues(
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 , title VARCHAR(64) NOT NULL
 , summary TEXT NULL
 , priority INT NOT NULL DEFAULT 5
 , cdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
 );

 INSERT INTO ztest_issues(title) VALUES('test');
 INSERT INTO ztest_issues(title, summary) VALUES('test2', 'Just a test');
 INSERT INTO ztest_issues(title, summary, priority) VALUES('test2', 'Just a test', 1);
 INSERT INTO ztest_issues(title, summary, priority, cdate) VALUES('test2', 'Just a test', 1, '2010-12-31 08:00:00');

 --INSERT INTO ztest_issues(title, summary) VALUES('locale test1', LOAD_FILE('C:/data/tmp/test.xml'));
 --INSERT INTO ztest_issues(title, summary) VALUES('locale test2', LOAD_FILE('C:/data/tmp/test2.xml'));
 --INSERT INTO ztest_issues(title, summary) VALUES('locale test3', LOAD_FILE('C:/data/tmp/test3.xml'));

 SELECT * FROM ztest_issues;

 NOTE:
 CURRENT_TIMESTAMP is version specific and is now allowed for DATETIME columns as of version 5.6 http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

 For 5.6 <, use TIMESTAMP for cdate field instead.

 cdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 cdate DATETIME NULL

 NOTE2:
 cdate DATETIME NOT NULL DEFAULT 0

 If you were to use default to ZERO, then you would need this properties in conn string. Else you will fail to get ZERO date value into Java.

 You need to tell the JDBC driver to convert them to NULL. This is done by passing a connection property name zeroDateTimeBehavior with the value convertToNull

 For more details see the manual: http://dev.mysql.com/doc/refman/4.1/en/connector-j-installing-upgrading.html

 */
public class ZtestIssuesJdbcTest {
//    String url = "jdbc:mysql://localhost/test";
//    String url = "jdbc:mysql://localhost/test?zeroDateTimeBehavior=convertToNull";
    String url = "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=UTF-8&connectionCollation=utf8_general_ci&zeroDateTimeBehavior=convertToNull";
    String username = "test";
    String password = "test123";

    @Test
    public void testShowTableLocale() throws Exception {
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            String sql = "SHOW VARIABLES LIKE 'char%'";
            System.out.println("Sql:" + sql);
            Statement sm = conn.createStatement();
            ResultSet rs = sm.executeQuery(sql);
            while (rs.next()) {
                System.out.printf("%s\t%s\n", rs.getObject(1), rs.getObject(2));
            }

            sql = "SHOW CREATE TABLE ztest_issues";
            System.out.println("Sql:" + sql);
            sm = conn.createStatement();
            rs = sm.executeQuery(sql);
            while (rs.next()) {
                System.out.printf("%s\t%s\n", rs.getObject(1), rs.getObject(2));
            }
        }
    }

    @Test
    public void testQuery() throws Exception {
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            String sql = "SELECT id, cdate, title, summary FROM ztest_issues";
            System.out.println("Sql:" + sql);
            Statement sm = conn.createStatement();
            ResultSet rs = sm.executeQuery(sql);
            while (rs.next()) {
                System.out.printf("%d\t%s\t%s\t%s\n", rs.getObject(1), rs.getObject(2), rs.getObject(3), rs.getObject(4));
            }
        }
    }

    @Test
    public void testInsert() throws Exception {
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            String sql = "INSERT INTO ztest_issues(title, summary, cdate) VALUES(?, ?, ?)";
            System.out.println("Sql: " + sql);
            PreparedStatement ps = conn.prepareStatement(sql);

            String testId = "" + System.currentTimeMillis();

            ps.setObject(1, "English Locale " + testId);
            ps.setObject(2, "Just a test");
            ps.setObject(3, new Date());
            int result = ps.executeUpdate();
            System.out.println("Insert Result: " + result);

            ps.setObject(1, "Chinese Locale " + testId);
            ps.setObject(2, "只是一個測試");
            ps.setObject(3, new Date());
            result = ps.executeUpdate();
            System.out.println("Insert Result: " + result);

            ps.setObject(1, "Spanish Locale " + testId);
            ps.setObject(2, "Sólo una prueba");
            ps.setObject(3, new Date());
            result = ps.executeUpdate();
            System.out.println("Insert Result: " + result);
        }
    }
}

Monday, November 2, 2015

ConEmu - a Terminal Windows or Tab Manager

Just learned about ConEmu project at https://conemu.github.io and it's pretty awesome!

Thanks Onur for the tips!

Sunday, October 25, 2015

mac: How to view Unix man pages with browser

I have come across this wonderful project called Bwana (https://www.bruji.com/bwana/) for Mac. Install it and you can view any man page on the browser. For example try typing the following address on the Safari:

man:find

And you will see something like this:


Thursday, October 22, 2015

python: How to setup a new Trac issue tracking system

Here is how I setup a local instance of Trac (a python based issue tracking web application) on my Mac.

Prerequisite: MySQL 5.6 and Python 2.7 (Python3 will not work with Trac yet!)

Step1: Setup a Trac database and a user

sql> CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
sql> CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev123';
sql> GRANT ALL ON trac.* TO 'dev'@'localhost';

Step2: Install MySQL adaptor for Python and Trac

bash> pip install Genshi trac mysqlclient
Step3: Setup a Track instance

bash> trac-admin /Users/zemian/dev/mytrac intent
bash> # Above will prompt you to enter a backend string. Use
bash> # this connection string: 
bash> #   mysql://dev:dev123@localhost:3306/trac

Step3: Create a Trac admin user
bash> htpasswd -c /Users/zemian/dev/mytrac/.htpasswd admin

Step4: Run Track
bash> tracd -p 8000 --basic-auth="mytrac,/Users/zemian/dev/mytrac/.htpasswd,mytrac" /Users/zemian/dev/metric

Saturday, October 17, 2015

python: mysqlclient gives "Library not loaded: libmysqlclient.18.dylib" error

If you want to use python MySQLdb module (eg: if you run Trac with MySQL backend), you would need first install MySQL server on MacOSX, then install the mysqlclient python package using pip. However upon verifying it, you may encounter error like this:

(mypy-test)Zemians-Air:dev zemian$ pip install mysqlclient
Collecting mysqlclient
  Using cached mysqlclient-1.3.6.tar.gz
Building wheels for collected packages: mysqlclient
  Running setup.py bdist_wheel for mysqlclient
  Stored in directory: /Users/zemian/Library/Caches/pip/wheels/9c/3b/73/8f16f45dc76999dafc2af06b0d6e1e669bc0e1594f41fcc2e8
Successfully built mysqlclient
Installing collected packages: mysqlclient
Successfully installed mysqlclient-1.3.6
(mypy-test)Zemians-Air:dev zemian$ python
Python 2.7.10 (default, Aug 22 2015, 20:33:39) 
[GCC 4.2.1 Compatible Apple LLVM 7.0.0 (clang-700.0.59.1)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/zemian/dev/mypy-test/lib/python2.7/site-packages/MySQLdb/__init__.py", line 19, in <module>
    import _mysql
ImportError: dlopen(/Users/zemian/dev/mypy-test/lib/python2.7/site-packages/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib
  Referenced from: /Users/zemian/dev/mypy-test/lib/python2.7/site-packages/_mysql.so
  Reason: image not found

To resolve this, you need to add the following to your shell environment

export DYLD_LIBRARY_PATH=/usr/local/mysql/lib

Update 2016-06-17
To install mysqlclient on Ubuntu 14, you might need to first install the mysql_config first:
  bash> sudo apt-get install libmysqlclient-dev

Monday, October 12, 2015

mysql: How to specify SAME target table for update in FROM clause

Have you tried updating something simple as following?

update category_tmp set last_update=NOW() 
where category_id in (
  select category_id from category_tmp where name like 'A%'
);

In MySQL you will get an error like this:

Error Code: 1093. You can't specify target table 'category_tmp' for update in FROM clause.

So it says that you can't use the same update TABLE name within the sub query in where condition. The trick to get around this is to use another sub query in the where clause so it won't see the TABLE name being used! Here is a workaround:

update category_tmp set last_update=NOW() 
where category_id in (
  select category_id from (
    select category_id from category_tmp where name like 'A%'
  ) ID_LIST
);

Noticed that in MySQL, you must name your sub query result such as ID_LIST, in order for it to be re-select it again on the outer query! Otherwise it will error out with this:

Error Code: 1248. Every derived table must have its own alias


Sunday, October 11, 2015

mac: MacBook Air is not displaying scroll bar on Finder

For odd reason that I don't see scrollbar shows up on Finder in MacBook Air when I have a long list of files. Can't scroll down with my trackpad, but I can do it with "Fn" + Down Arrow key though. To fix this, I have to go into System Preference, General and set "Show scroll bar" to "Always".

python: Setup Python on Mac

Setup Python 2

The default MacOS should already come with latest Python 2.7. It will be pre-installed along with some pyobjc packages as well.

If you like to play around with python and external packages, it's best not to touch the original Python install from the System. Unfortunately, this Python 2.7 does not come with pip nor virtualenv packages to help setup separate environment! So it's okay to install these two into the system first. The easiest to setup is simply download get-pip.py from https://bootstrap.pypa.io/get-pip.py, then run

bash> sudo python get-pip.py

With this, you may now able install virtualenv


bash> sudo /usr/local/bin/pip install virtualenv

Now you can setup Python2 virtual env on a separate directory.

bash> python -m virtualenv mypy
bash> source mypy/bin/activate
(mypy) bash> python -c 'import sys; print sys.path'

You should able to verify the output above that you are indeed using your own setup of an isolated  Python2 env, and it even has your own pip installed list, so you won't mess up the built-in System version of python!

Setup Python 3

The MacOS comes with latest Python 2.7, but no Python3. You can download the latest Python3 from python.org site and install it. It will give you a new 'python3' command so not to conflict with your existing Python. (NOTE: It's best not to remove or override the default Python2.7 that comes with the system!) But if you want to use 'python' command for Python3 for dev, then I suggest you create a new virtual environment of your own. The Python3 install should come with a 'pyvenv' command. For Example

bash> pyvenv mypy3
bash> source mypy3/bin/activate
(mypy3) bash> python -c 'import sys; print(sys.path)'



python: Checking MySQL database connection with mysql-connector-python

First install the MySQL package

bash>pip install --allow-all-external mysql-connector-python

Now you may test the database connection with this Python code

from mysql.connector import connect

conn = connect(user='dev', password='dev123', database='mysql')
cur = conn.cursor()
cur.execute('select 1+1')
for row in cur:
print(row)

Monday, October 5, 2015

WLS provisioning: Setup MySQL DataSource

Here is a WLST script to setup MySQL DataSource in WebLogic server after you have created a domain. The script will also assign this DataSource to one or more servers you pass in at the end of arguments.

# Update an existing domain to setup a DataSource and assign it to servers
# Example
#   wlst.cmd setupDataSource.py C:\data\wls11g_domains\dev MYTESTDB jdbc/MYTESTDB com.mysql.jdbc.Driver jdbc:mysql://localhost/mytestdb test test123 AdminServer

import os, sys, os.path
(domain_home, ds_name, jndi_name, driver_name, url, user, password) = sys.argv[1:8]
servers = sys.argv[8:]

domain_home = os.path.abspath(domain_home)

readDomain(domain_home)

print("Setting up WLS DataSource: %s" % ds_name)
create(ds_name, 'JDBCSystemResource')
cd('/JDBCSystemResource/%s/JdbcResource/%s' %(ds_name, ds_name))
create('myJdbcDriverParams','JDBCDriverParams')
cd('JDBCDriverParams/NO_NAME_0')
set('DriverName',driver_name)
set('URL', url)
set('PasswordEncrypted', password)
set('UseXADataSourceInterface', 'false')
create('myProps','Properties')
cd('Properties/NO_NAME_0')
create('user', 'Property')
create('characterEncoding', 'Property')
create('connectionCollation', 'Property')
create('useUnicode', 'Property')
cd('Property/user')
cmo.setValue(user)
cd('../characterEncoding')
cmo.setValue('utf-8')
cd('../connectionCollation')
cmo.setValue('utf8_general_ci')
cd('../useUnicode')
cmo.setValue('true')

cd('/JDBCSystemResource/%s/JdbcResource/%s' %(ds_name, ds_name))
create('myJdbcDataSourceParams','JDBCDataSourceParams')
cd('JDBCDataSourceParams/NO_NAME_0')
set('JNDIName', java.lang.String(jndi_name))

cd('/')
for server in servers:
print("Assigning DS to server: %s" % server)
assign('JDBCSystemResource', ds_name, 'Target', server)

updateDomain()
exit()

Saturday, October 3, 2015

WLS provisioning: Creating new WebLogic domain using WLST script

Here is how to creating new WebLogic domain using WLST script without starting up a WebLogic server instance. The default AdminServer console login username is 'weblogic', and the script allow you to change its password and a port number for the AdminServer instance.

# Usage: wlst.cmd createDomain.py <domain_home> <port> <password>
# Example: 
#   cd D:\apps\wls1036_dev\wlserver\common\bin
#   wlst.cmd scripts\createDomain.py C:\data\wls11g_domains\dev 7001 weblogic1

import os, sys, os.path
domain_home, port_s, password = sys.argv[1:]

port = int(port_s)
domain_home = os.path.abspath(domain_home)
domain_name = os.path.basename(os.path.normpath(domain_home))
template = "%s/wlserver/common/templates/domains/wls.jar" % os.environ['MW_HOME']


print("Creating WLS domain: %s" % domain_name)
# MW_HOME should be auto set by wlst.cmd script
readTemplate(template)

# Set domain name
set('Name', domain_name)

# Set AdminServer port 
cd('Servers/AdminServer')
set('ListenAddress','')
set('ListenPort', port)

# Set weblogic password
cd('/Security/%s/User/weblogic' % domain_name)
cmo.setPassword(password)

# Write domain
#setOption('OverwriteDomain', 'true')
writeDomain(domain_home)
closeTemplate()
exit()

Saturday, September 26, 2015

Create simple WLS domain using WLST and built-in template jar

You can easily start up a WebLogic Server on an empty directory and it will create a domain. Here is another way to create WLS domain structure folder without actually starting up a WLS server.

# Usage: wlst.cmd createDomain.py <domain_home> <port> <password>
# NOTE: the <domain_home> must be a absolute path.
# Example: C:\wls12130\wlserver\common\bin\wlst.cmd scripts\createDomain.py C:\data\wls12c_domains\dev 7001 weblogic1

import os, sys
domain_home = sys.argv[1]
domain_name = 'mydomain'
port = int(sys.argv[2])
password = sys.argv[3]
readTemplate(os.environ['MW_HOME'] + "/wlserver/common/templates/wls/wls.jar")
cd('/Server/AdminServer')
set('Name', domain_name + '-admin')
set('ListenAddress','')
set('ListenPort', port)
cd('/Security/base_domain/User/weblogic')
cmo.setPassword(password)
cd('/')
set('Name', domain_name)
writeDomain(domain_home)
closeTemplate()
exit()

Monday, August 24, 2015

Be aware of the count=0 when using Python's re.sub() to find and replace text

I have an xml file and I want to remove a whole section of element without actually parsing the XML file. I wrote an Python script to do just that. I first tested it out like this:

import re
input_text = open('my.xml').read()
result = re.findall(r'<library-ref>.*</library-ref>', input_text, re.S)
print(result)


The result IS what I wanted, so I thought I can quickly replace it with something like this:

result = re.sub(r'<library-ref>.*</library-ref>', '', input_text, re.S)

And to my surprise this does not work. It took me a while to notice that I mis used the method according to the documentation, which has this signature:

re.sub(pattern, repl, string, count=0, flags=0)

At first glace, I thought that count is already default to zero and I do not need to set it, but because that is a positional parameter, you must include it! So you suppose to call it explicit like this:

result = re.sub(r'<library-ref>.*</library-ref>', '', input_text, 0, re.S)

Or, if you like, you can use the "flags" parameter name explicitly without the odd looking zero.

result = re.sub(r'<library-ref>.*</library-ref>', '', input_text, flags=re.S)

So here is an example of flexible dynamic typing of optional parameters can bite if you are not careful.

Saturday, August 22, 2015

View and terminate Oracle Database user sessions

Verify sessions:
SELECT USERNAME FROM V$SESSION;

SELECT USERNAME, SID, SERIAL#, STATUS
  FROM V$SESSION
  WHERE USERNAME like 'SCOTT%';

Generate SQL to terminate sessions:
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';'
  FROM V$SESSION
  WHERE USERNAME like 'SCOTT%';

Wednesday, August 19, 2015

How to import a Oracle database dump file

Here is an example on how to import a Oracle database dump file (a binary file that's exported from a Oracle database using the Oracle data pump utility). For this instruction example, let's say I was given a myapp.dmp.gz file.

NOTE: If you do not use the same db user/tablespace for export and import, then you need to know its names used during export to perform the remapping during import.

Step 1: If you don't have an Oracle database instance to work with, create one first. Or ask your DBA to create or allocate a instance for your import purpose.

Step 2: Create a new tablespace and a DB user in an Oracle database instance. (For this example, I will call my new user MYAPP08182015, password Password1, and tablespace named MY_TABLE_SPACE.)

create tablespace MY_TABLE_SPACE datafile
  '/opt/myapp_datafiles/MY_TABLE_SPACE.dat'
  size 500m autoextend on next 500m
  extent management local
  segment space management auto;

create user MYAPP08182015 identified by Password1
  default tablespace MY_TABLE_SPACE
  temporary tablespace TEMP
  quota unlimited on MYTABLESPACE;

grant create session, grant any privilege to MYAPP08182015;
grant IMP_FULL_DATABASE to 
MYAPP08182015;

create directory MY_DATADUMP_DIR as '/opt/myapp_datadump';

Note that user must have IMP_FULL_DATABASE privilege to be able to used by import tool. And if you don't want to use default import data directory, then you would need to create the directory object to be used by import as shown above.

Note also that your TEMP tablespace might not have enough disk space for large import. In this case, you may increase it like this:

alter tablespace temp add tempfile
  '/opt/myapp_datafiles/TEMP2.dat'
  size 500m autoextend on next 500m;

Step 3: Now you have a new db User (Schema) and password, you may start the data import using the command line prompt. Ssh into the database server where you can find the impdp command utility.

scp myapp.dmp.gz zemian@mydbserver:/opt/myapp_datadump
ssh zemian@mydbserver
cd /opt/myapp_datafiles
gunzip myapp.dmp.gz
/app/oracle/dbhome/bin/impdp MYAPP08182015/Password1 DIRECTORY=MY_DATADUMP_DIR REMAP_SCHEMA=MYAPP:MYAPP08182015 REMAP_TABLESPACE=USERS:MY_TABLE_SPACE LOGFILE=myapp-import.log DUMPFILE=myapp.dmp

References:
http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_overview.htm

Tuesday, August 18, 2015

How to check Oracle Database tablespace

When creating a new users in Oracle database (new schema), you need to verify the existing tablespace availability. This query will show you what's there and how much space are free to use.

SELECT df.tablespace_name "Tablespace",
  totalusedspace "Used MB",
  (df.totalspace - tu.totalusedspace) "Free MB",
  df.totalspace "Total MB",
  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace
  FROM dba_data_files
  GROUP BY tablespace_name
  ) df,
  (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
    tablespace_name
  FROM dba_segments
  GROUP BY tablespace_name
  ) tu
WHERE df.tablespace_name = tu.tablespace_name;

Also, this query will show where the tablespace file are located:

SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME
   FROM DBA_DATA_FILES;

Here are some references on how Oracle manages user, schema and tablespace.



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 solution. Store your SQL queries in XML inside CDATA:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sqlMap>
    <sqls>
        <entry>
            <key>getUser</key>
            <value><![CDATA[
SELECT *
FROM USERS
WHERE ID = ?
            ]]></value>
        </entry>
        <entry>
            <key>getSpecialCodeByUserId</key>
            <value><![CDATA[
SELECT u.EMAIL, p.ID as PROFILEID, p.SPECIALCODE, a.MANAGERID
FROM USERS u
  LEFT JOIN PROFILE p ON p.USERID = u.ID
  LEFT JOIN ACCOUNT a ON a.PROFILEID = p.ID
WHERE u.ID = ?  ]]></value>
        </entry>  </sqls>
</sqlMap>

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;

@XmlRootElement
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 {
        InputStream inStream = Thread.currentThread().getContextClassLoader().getResourceAsStream(name);
        SqlMap sqlMap = JAXB.unmarshal(inStream, SqlMap.class);
        return sqlMap;
    }
}


Another way is to simply 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 "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>XML Props</comment>
    <!-- Foo entry -->
    <entry key="foo">bar</entry>
    <!-- Query entry -->
    <entry key="query">
        <![CDATA[
                SELECT * FROM USERS
        ]]>
    </entry>
</properties>

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= m.group(1);
            String newId = lookup(id);
            String rep = "productId=" + newId + "'";
            m.appendReplacement(newHtml, rep);
        }
        m.appendTail(newHtml);
        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++) {
            row.map.put(meta.getColumnName(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(rs.next()) {
                    result.add(toRowMap(rs));
                }
            }
        }
        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) {
        super(dataSource);
    }
   
    public void printAccountProfiles() {
        try(Connection conn = dataSource.getConnection()) {
            String sql = "SELECT ACCOUNTID FROM USERS WHERE USERNAME=?";
            Row row = queryRow(conn, sql, "zemian");
           
            String accountId = row.get("ACCOUNTID");
            String department = "IT";
            sql = "SELECT * FROM ACCOUNTPROFILES WHERE ID=? AND DEPARTMENT=?";
            List<Row> rowList = queryRowList(conn, sql, accountId, department);
           
            for (Row row : rowList) {
                System.out.println("Got profile: " + row.map);
            }
        }
    }
}



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:

ProjectX
+-ModuleLibA
+-ModuleLibB
+-ModuleCdiLibC
+-ModuleCdiLibC2
+-ModuleLibD
+-ModuleCdiLibE
+-ModuleCdiLibE2
+-ModuleCdiLibE3
+-ModuleLibF
+-ModuleLibG
+-ModuleWebAppX

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:
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <!--
                    We want to package skinny war to avoid third party jars -->
                    <packagingExcludes>
                        WEB-INF/lib/*.jar
                    </packagingExcludes>
                    <archiveClasses>true</archiveClasses>
                </configuration>
            </plugin>


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:


            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <!--
                    We want to package skinny war to avoid third party jars, but we do want the classes from
                    this project to be included -->
                    <packagingExcludes>
                        %regex[WEB-INF/lib/(?!my-project-artifact-name-.*\.jar).*\.jar]
                    </packagingExcludes>
                    <archiveClasses>true</archiveClasses>
                </configuration>
            </plugin>


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: http://www.regexplanet.com/advanced/java/index.html

Thursday, May 21, 2015

Getting version string from a Maven based web application

When you package a maven project, it will automatically generate a pom.properties 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/pom.properties";
            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 pom.properties 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 pom.properties from classpath!

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

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <archiveClasses>true</archiveClasses>
                </configuration>
            </plugin>


This will tell maven to jar up your classes along with the pom.properties in a separate file, then place it in WEB-INF/lib folder instead of using the unpacked WEB-INF/classes version. This forces the pom.properties 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:

http://stackoverflow.com/questions/13669237/netbeans-java-how-to-add-as-library-another-project-with-dependencies

https://netbeans.org/bugzilla/show_bug.cgi?id=47507

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.

Friday, April 24, 2015

How to create multiple workspaces with NetBeans

Examples

  • Windows:
    netbeans.exe --userdir C:\MyOtherUserdir --cachedir "%HOME%\Locale Settings\Application Data\NetBeans\7.1\cache"
  • Unix:
    ./netbeans --userdir ~/my-other-userdir
  • Mac OS:
    /Applications/NetBeans.app/Contents/MacOS/executable --userdir ~/my-other-userdir
Ref: http://wiki.netbeans.org/FaqAlternateUserdir

Saturday, February 7, 2015

EE JSP: Generating Dynamic Content with JSTL Tag Libraries

Besides writing your own Custom Tags in JSP, you will find that Java EE actually provides a set of Java Standard Tag Library (JSTL) ready for you to use. These built-in tags include repeating (for-loop) tags, if condition tags, variable declaration and output tags etc. The Libraries also come with many utility functions and international message formatting tags. Here is an example how it looks like.

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="x" uri="http://java.sun.com/jsp/jstl/xml" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<!DOCTYPE html>
<html>
    <body>
        <h1>JSTL Examples</h1>
        <h2>List of Application Context: ${applicationScope}</h2>
        <table>
            <c:forEach var="entry" items="${applicationScope}">
            <tr>
                <td>${entry.key}</td>
                <td>
                    <c:out value="${entry.value}"/>
                </td>
            </tr>
            </c:forEach>
        </table>
       
        <h2>List of Session Context: ${sessionScope}</h2>
        <table>
            <c:forEach var="entry" items="${sessionScope}">
            <tr>
                <td>${entry.key}</td>
                <td>
                    <c:out value="${entry.value}"/>
                </td>
            </tr>
            </c:forEach>
        </table>
       
        <h2>List of Page Context: ${pageScope}</h2>
        <table>
            <c:forEach var="entry" items="${pageScope}">
            <tr>
                <td>${entry.key}</td>
                <td>
                    <c:out value="${entry.value}"/>
                </td>
            </tr>
            </c:forEach>
        </table>
       
        <h2>List of Request Context: ${requestSope}</h2>
        <table>
            <c:forEach var="entry" items="${requestSope}">
            <tr>
                <td>${entry.key}</td>
                <td>
                    <c:out value="${entry.value}"/>
                </td>
            </tr>
            </c:forEach>
        </table>
       
        <h2>List of Query Parameters: ${param}</h2>
        <table>
            <c:forEach var="entry" items="${param}">
            <tr>
                <td>${entry.key}</td>
                <td>
                    <c:out value="${entry.value}"/>
                </td>
            </tr>
            </c:forEach>
        </table>
       
        <h2>List of Header Parameters: ${header}</h2>
        <table>
            <c:forEach var="entry" items="${header}">
            <tr>
                <td>${entry.key}</td>
                <td>
                    <c:out value="${entry.value}"/>
                </td>
            </tr>
            </c:forEach>
        </table>       
       
        <h2>List of Cookies: ${cookie}</h2>
        <table>
            <c:forEach var="entry" items="${cookie}">
            <tr>
                <td>${entry.key}</td>
                <td>
                    <c:out value="${entry.value}"/>
                </td>
            </tr>
            </c:forEach>
        </table>
    </body>
</html>


I used the core tag here to display map entries of few implicit variables. You may explore more on those tags declarations I have define on top of the example page from the Spec. These code are from the jsp-example from GitHub.

GLASSFISH NOTE: When deploying above example in GlassFish 3/4, you will run into a NullPointerException and causing the page resulted in error. It turns out that GF server added a internal variable named com.sun.jsp.taglibraryCache  in ServletContext (Application Scope) that throws NPE when its toString() is called! To workaround this, I created jstl-example2.jsp that wraps the ${entry.value} into a custom JSTL function so that it guarantees to return an output without throwing exception. So the lesson learned is that you should always return a String and not throw Exception when overriding toString() of a Java class. It's just bad practice.