Thursday, December 27, 2012

Reading https url from a self-signed cert

Groovy has made fetching data from URL a snap:
println(new URL("http://www.google.com").text)
But have you ever try to get data from an https of an site that's using a self signed certificate? A browser will prompt you a risk warning, but it let you trust it and still continue. But it's much more hassle if we want to fetch the data programmatically. For example, try fetching https://www.pcwebshop.co.uk and you will see that it failed miserably:
Caught: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at com.sun.net.ssl.internal.ssl.Alerts.getSSLException(Alerts.java:174)
    at com.sun.net.ssl.internal.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1762)
    at com.sun.net.ssl.internal.ssl.Handshaker.fatalSE(Handshaker.java:241)
    at com.sun.net.ssl.internal.ssl.Handshaker.fatalSE(Handshaker.java:235)
    at com.sun.net.ssl.internal.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1206)
    at com.sun.net.ssl.internal.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:136)
    at com.sun.net.ssl.internal.ssl.Handshaker.processLoop(Handshaker.java:593)
    at com.sun.net.ssl.internal.ssl.Handshaker.process_record(Handshaker.java:529)
    at com.sun.net.ssl.internal.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:958)
    at com.sun.net.ssl.internal.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1203)
    at com.sun.net.ssl.internal.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1230)
    at com.sun.net.ssl.internal.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1214)
    at test.run(test.groovy:2)
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at com.sun.net.ssl.internal.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:126)
    at com.sun.net.ssl.internal.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:209)
    at com.sun.net.ssl.internal.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:249)
    at com.sun.net.ssl.internal.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1185)
    ... 8 more
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    ... 12 more
This is due to Java API won't let you get data if your certs is not in the keystore or "trusted". Now if you have your own testing site and did a self-signed like above, then sure you just want to trust it temporary and simply want to fetch the data. However configuring the Java API to do such simple task is a nightmare.
Today, I found a library that allow you to perform exactly this: https://github.com/kevinsawicki/http-request Trying this out using Groovy is pretty sweet:
@Grab('com.github.kevinsawicki:http-request:3.1')
import com.github.kevinsawicki.http.*
def req = HttpRequest.get("https://www.pcwebshop.co.uk")
req.trustAllCerts()
req.trustAllHosts()
println(req.body())
And Voila! We read the URL context without the pesky exception. This http-request is awesome! Now I wish JDK let you do simple thing like this. This is extremely useful for testing.

Friday, December 21, 2012

A strange case of Java generic and inheritage parameter passing

I came a cross some strange Java code and I would like to share it here. Take a look few of classes I have here:

// file: AFoo.java
package atest.deng;
public abstract class AFoo<T> {
}

// file: Foo.java
package atest.deng;
public class Foo extends AFoo<String> {
}

// file: FooProcessor.java
package atest.deng;
public class FooProcessor<T> {
    public void process(Class<AFoo<?>> cls) {
        System.out.println(cls);
    }
}

// file: FooMain.java
package atest.deng;
public class FooMain {
    public static void main(String[] args) {
        new FooProcessor().process(Foo.class);
    }
}

bash> mvn compile
bash> [INFO] BUILD SUCCESS

I tried this with JDK6 + Maven and it compiles without problem. But try to remove the <T> part from FooProcessor and it will fail to compile!

// file: FooProcessor.java
package atest.deng;
public class FooProcessor {
    public void process(Class<AFoo<?>> cls) {
        System.out.println(cls);
    }
}

bash> mvn clean compile
bash> [ERROR] java-demo\src\main\java test\deng\FooMain.java:[4,26] process(java.lang.Class<atest.deng.AFoo<?>>) in atest.deng.FooProcessor cannot be applied to (java.lang.Class<atest.deng.Foo>)

Without the <T> the code won't compile, and yet we are not even using it in this case. How and why <T> affects the method parameters invocation?

Now, we can improve the FooProcessor in this way so that the presence of <T> doesn't have any affect.

package atest.deng;
public class FooProcessor {
    public void process(Class<? extends AFoo<?>> cls) {
        System.out.println(cls);
    }
}

That's a more proper way to write the generic parameter anyway. But despite a better solution, the puzzle is that the original code compiled under the compiler, but only with the <T> presented, and yet it's not used. Wouldn't you consider this as a Java compiler bug?

Wednesday, December 19, 2012

A simple Groovy issue tracker using file system

It will be a chaos not to track bugs and feature requests when you developing software. Having a simple issue tracker would make managing the project much more successful. Now I like simple stuff, and I think for small project, having this tracker right inside the source control (especially with DSVC like Mercurial/Git etc) repository is not only doable, but very convenient as well. You don't have to go crazy with all the fancy features, but just enough to track issues are fine. I would like to propose this layout for you.

Let's say you have a project that looks like this

project
 +- src/main/java/Hello.java
 +- issues/issue-001.md
 +- pom.xml

All I need is a simple directory issues to get going. Now I have a place to track my issue! First issue issue-000.md should be what your project is about. For example:

/id=issue-001
/createdon=2012-12-16 18:07:08
/type=bug
/status=new
/resolution=
/from=Zemian
/to=
/found=
/fixed=
/subject=A simple Java Hello program

# Updated on 2012-12-16 18:07:08

We want to create a Maven based Hello world program. It should print "Hello World."

I choose .md as file extension for intending to write comments in Markdown format. Since it's a text file, you do what you want. To be more structured, I have added some headers metadata for issue tracking. Let's define some here. I would propose to use these and formatting:


 /id=issue-<NUM>
 /createdon=<TIMESTAMP>
 /type=feature|bug|question
 /status=new|reviewing|working|onhold|testing|resolved
 /resolution=fixed|rejected|duplicated
 /from=<REPORTER_FROM_NAME>
 /to=<ASSIGNEE_TO_NAME>
 /found=<VERSION_FOUND>
 /fixed=<VERSION_FIXED>

That should cover most of the bug and feature development issues. It's not cool to write software without a history of changes, including these issues created. So let's use a source control. I highly recommend you to use Mercurial hg. You can create and initialize a new repository like this.

bash> cd project
bash> hg init
bash> hg add
bash> hg commit -m "My hello world project"

Now your project is created and we have a place to track your issues. Now it's simple text file, so use your favorite text editor and edit away. However, creating new issue with those header tags is boring. It will be nice to have a script that manage it a little. I have a Groovy script issue.groovy (see at the end of this article) that let you run reports and create new issues. You can add this script into your project/issues directory and you can instantly creating new issue and querying reports! Here is an example output on my PC:

bash> cd project
bash> groovy scripts/issue.groovy

Searching for issues with /status!=resolved
Issue: /id=issue-001 /status=new /subject=A simple Java Hello program
1 issues found.

bash> groovy scripts/issue.groovy --new /type=feature /subject='Add a unit test.'

project/issues/issue-002.md created.
/id=issue-002
/createdon=2012-12-16 19:10:00
/type=feature
/status=new
/resolution=
/from=Zemian
/to=
/found=
/fixed=
/subject=Add a unit test.

bash> groovy scripts/issue.groovy

Searching for issues with /status!=resolved
Issue: /id=issue-000 /status=new /subject=A simple Java Hello program
Issue: /id=issue-002 /status=new /subject=Add a unit test.
2 issues found.

bash> groovy scripts/issue.groovy --details /id=002

Searching for issues with /id=002
Issue: /id=issue-002
  /createdon=2012-12-16 19:10:00 /found= /from=Zemian /resolution= /status=new /type=feature
  /subject=Add a unit test.
1 issues found.

bash> groovy scripts/issue.groovy --update /id=001 /status=resolved /resolution=fixed 'I fixed this thang.'
Updating issue /id=issue-001
Updating /status=resolved
Updating /resolution=fixed

Update issue-001 completed.

The script give you some quick and consistent way to create/update/search issues. But they are just plain text files! You can just as well fire up your favorite text editor and change any any thing you want. Save and even commit it into your source repository. All will not lost.

I hope this issue tracking script can get your next project started quickly. Let me know what you do you think!

Enjoy!

Zemian

And here is my issue.groovy script.


Oh, and of course I eat my own *dog food. Here are few issues that I started to track the issue.grooy itself.

Saturday, December 15, 2012

Getting started with Quartz Scheduler on MySQL database

Here are some simple steps to get you fully started with Quartz Scheduler on MySQL database using Groovy. The script below will allow you to quickly experiment different Quartz configuration settings using an external file.

First step is to setup the database with tables. Assuming you already have installed MySQL and have access to create database and tables.

bash> mysql -u root -p

sql> create database quartz2;
sql> create user 'quartz2'@'localhost' identified by 'quartz2123';
sql> grant all privileges on quartz2.* to 'quartz2'@'localhost';
sql> exit;

bash> mysql -u root -p quartz2 < /path/to/quartz-dist/docs/dbTables/tables_mysql.sql
The tables_mysql.sql can be found from Quartz distribution download, or directly from their source here. Once the database is up, you need to write some code to start up the Quartz Scheduler. Here is a simply Groovy script quartzServer.groovy that will run as a tiny scheduler server.
// Run Quartz Scheduler as a server
// Author: Author: Zemian Deng, Date: 2012-12-15_16:46:09
@GrabConfig(systemClassLoader=true)
@Grab('mysql:mysql-connector-java:5.1.22')
@Grab('org.slf4j:slf4j-simple:1.7.1')
@Grab('org.quartz-scheduler:quartz:2.1.6')
import org.quartz.*
import org.quartz.impl.*
import org.quartz.jobs.*

config = args.length > 0 ? args[0] : "quartz.properties"
scheduler = new StdSchedulerFactory(config).getScheduler()
scheduler.start()

// Register shutdown
addShutdownHook {
  scheduler.shutdown()
}

// Quartz has its own thread, so now put this script thread to sleep until
// user hit CTRL+C
while (!scheduler.isShutdown()) {
 Thread.sleep(Long.MAX_VALUE)
}
And now you just need a config file quartz-mysql.properties that looks like this:
# Main Quartz configuration
org.quartz.scheduler.skipUpdateCheck = true
org.quartz.scheduler.instanceName = DatabaseScheduler
org.quartz.scheduler.instanceId = NON_CLUSTERED
org.quartz.scheduler.jobFactory.class = org.quartz.simpl.SimpleJobFactory
org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.dataSource = quartzDataSource
org.quartz.jobStore.tablePrefix = QRTZ_
org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount = 5

# JobStore: JDBC jobStoreTX
org.quartz.dataSource.quartzDataSource.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.quartzDataSource.URL = jdbc:mysql://localhost:3306/quartz2
org.quartz.dataSource.quartzDataSource.user = quartz2
org.quartz.dataSource.quartzDataSource.password = quartz2123
org.quartz.dataSource.quartzDataSource.maxConnections = 8
You can run the Groovy script as usual
bash> groovy quartzServer.groovy quartz-mysql.properties
Dec 15, 2012 6:20:26 PM com.mchange.v2.log.MLog 
INFO: MLog clients using java 1.4+ standard logging.
Dec 15, 2012 6:20:27 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.1.1 [built 15-March-2007 01:32:31; debug? true; trace:10]
[main] INFO org.quartz.impl.StdSchedulerFactory - Using default implementation for ThreadExecutor
[main] INFO org.quartz.core.SchedulerSignalerImpl - Initialized Scheduler Signaller of type: class org.quartz.core.SchedulerSignalerImpl
[main] INFO org.quartz.core.QuartzScheduler - Quartz Scheduler v.2.1.6 created.
[main] INFO org.quartz.core.QuartzScheduler - JobFactory set to: org.quartz.simpl.SimpleJobFactory@1a40247
[main] INFO org.quartz.impl.jdbcjobstore.JobStoreTX - Using thread monitor-based data access locking (synchronization).
[main] INFO org.quartz.impl.jdbcjobstore.JobStoreTX - JobStoreTX initialized.
[main] INFO org.quartz.core.QuartzScheduler - Scheduler meta-data: Quartz Scheduler (v2.1.6) 'DatabaseScheduler' with instanceId 'NON_CLUSTERED'
  Scheduler class: 'org.quartz.core.QuartzScheduler' - running locally.
  NOT STARTED.
  Currently in standby mode.
  Number of jobs executed: 0
  Using thread pool 'org.quartz.simpl.SimpleThreadPool' - with 5 threads.
  Using job-store 'org.quartz.impl.jdbcjobstore.JobStoreTX' - which supports persistence. and is not clustered.

[main] INFO org.quartz.impl.StdSchedulerFactory - Quartz scheduler 'DatabaseScheduler' initialized from the specified file : 'quartz-mysql.properties' from the class resource path.
[main] INFO org.quartz.impl.StdSchedulerFactory - Quartz scheduler version: 2.1.6
Dec 15, 2012 6:20:27 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts
-> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge16k8r18mveoq1iqtotg|1486306, debugUnreturnedConnectionStackTraces -> fals
e, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge16k8r18mveoq1iqtotg|1486306, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/quartz2, lastAcquisitionFailureDefaultUser -> null, maxAdministrativeTaskTime -> 0
, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 8, maxStatements -> 0, maxStatementsPerConnection -> 120, minPoolSize -> 1, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, pref
erredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
[main] INFO org.quartz.impl.jdbcjobstore.JobStoreTX - Freed 0 triggers from 'acquired' / 'blocked' state.[main] INFO org.quartz.impl.jdbcjobstore.JobStoreTX - Recovering 0 jobs that were in-progress at the time of the last shut-down.
[main] INFO org.quartz.impl.jdbcjobstore.JobStoreTX - Recovery complete.
[main] INFO org.quartz.impl.jdbcjobstore.JobStoreTX - Removed 0 'complete' triggers.
[main] INFO org.quartz.impl.jdbcjobstore.JobStoreTX - Removed 0 stale fired job entries.
[main] INFO org.quartz.core.QuartzScheduler - Scheduler DatabaseScheduler_$_NON_CLUSTERED started.


... CTRL+C
[Thread-6] INFO org.quartz.core.QuartzScheduler - Scheduler DatabaseScheduler_$_NON_CLUSTERED shutting down.
[Thread-6] INFO org.quartz.core.QuartzScheduler - Scheduler DatabaseScheduler_$_NON_CLUSTERED paused.
[Thread-6] INFO org.quartz.core.QuartzScheduler - Scheduler DatabaseScheduler_$_NON_CLUSTERED shutdown complete.
That's a full run of above setup. Go ahead and play with different config. Read http://quartz-scheduler.org/documentation/quartz-2.1.x/configuration for more details.
Here I will post couple more easy config that will get you started in a commonly used config set.

A MySQL cluster enabled configuration. With this, you can start one or more shell terminal and run different instance of quartzServer.groovy with the same config. All the quartz scheduler instances should cluster themselve and distribute your jobs evenly.

# Main Quartz configuration
org.quartz.scheduler.skipUpdateCheck = true
org.quartz.scheduler.instanceName = DatabaseClusteredScheduler
org.quartz.scheduler.instanceId = AUTO
org.quartz.scheduler.jobFactory.class = org.quartz.simpl.SimpleJobFactory
org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.dataSource = quartzDataSource
org.quartz.jobStore.tablePrefix = QRTZ_
org.quartz.jobStore.isClustered = true
org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount = 5

# JobStore: JDBC jobStoreTX
org.quartz.dataSource.quartzDataSource.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.quartzDataSource.URL = jdbc:mysql://localhost:3306/quartz2
org.quartz.dataSource.quartzDataSource.user = quartz2
org.quartz.dataSource.quartzDataSource.password = quartz2123
org.quartz.dataSource.quartzDataSource.maxConnections = 8
Here is another config set for a simple in-memory scheduler.
# Main Quartz configuration
org.quartz.scheduler.skipUpdateCheck = true
org.quartz.scheduler.instanceName = InMemoryScheduler
org.quartz.scheduler.jobFactory.class = org.quartz.simpl.SimpleJobFactory
org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount = 5

Now, if you need more fancy UI management of Quartz, give MySchedule a try.

Happy scheduling!

Friday, December 14, 2012

Cygwin tips: open anything with cygstart

Cygwin has this cool command called cygstart, and I usually alias it as open.
alias open='cygstart'
Here are few things I use often:
 
# bring up Windows Explore with folder open
bash> open /cygdrive/c/temp
bash> open "$USERPROFILE"
 
# bring up browser on a url
bash> open http://google.com
 
# bring up any windows program by file extension
bash> open /cygdrive/c/my.docx

Because I use a Cygwin terminal shell a lot, I have many alias shortcuts to many different directories. The open . allows me to jump to any directory and starts the Windows Explore with that directory expanded quickly. You can see more options with open --help.

Thursday, December 13, 2012

A command to remove all Windows mapped drives

If you work in a Corp env and having a Laptop, you will likely go home with it still running and it must has all the network drives still mapped. And then later when you are at home and try to use your Laptop, you will see considerable delay. The Windows Explore and many Windows application that access File Browser dialog will try relentlessly in reconnecting those drives without sucess. This also affect your Cygwin shell too.

Here is a DOS command to remove them all.

C:> net use * /delete /y
If you are in Cygwin bash, then you must quote the '*'.

Wednesday, December 12, 2012

Intellij IDEA is pretty awesome!

Did you know IntelliJ IDEA has a free Community Edition you may download? I've been test driving it, and I will post few things I learned so far. Here are few things that I usually change from out of the default installed settings.
  1. File > Settings > Editor => Uncheck "Allow placement of caret after end of line" -- This is the most annoying thing for me in IDE. It's just not for me.
  2. File > Settings > Editor > Appearance => Check "Show line numbers" -- I tend to relate errors and navigate content better with line number displayed.
  3. File > Settings > Appearance > Theme => Select "Darcula" -- This is latest version 12.0 feature. Dark screen is pretty eye pleasing compare to WHITE.
  4. File > Settings > Compiler => Check "Make project automatically" -- Another new 12.0 feature that would perform incremental compile.

Now the IDE is ready for me to work on any Java projects. If you work with Maven, then you will be happy to know that IDEA supports it out of box. You can get an existing project started with following steps:

  1. File > Import Project => Browse to your project pom.xml. Even if you have multi Maven modules, you only need to pick the top level pom.xml and it will generate the multi IDEA modules for you automatically. Then click "Ok" button.
  2. In "Import project from external model" select "Maven", and then "Next".
  3. You can accept all default settings on this prompt, and simply go "Next"
  4. Check all maven project to import
  5. Next screen is selecting a SDK for this project (meaning pick a JDK) to use. If this is your first time setup, then create a new one by click on the Grean plus icon, then browse to your JAVA_HOME directory and click OK. Click "Next" to go next step.
  6. Simply click "Next" to accept default project name. Then you are at last prompt. Simply click "Finish" button and you should have your project ready to go.

It might seems like a lot of steps to import a project, but IDEA is really nice. After your project has completely imported and compiled, you should able open any of your Java class (use CTRL+N) that has main method or any JUnit classes. Right click the content editor and select "Run ..." to execute it (or CTRL+SHIFT+F10 to run any single unit test method).

I have tried a decent size Java projects (few thousand source files) and it handles it very gracefully. The UI such as Menu items, Control buttons and tool bars are very user friendly and intuitive to use. Also, right click on most places should give you a "Context" menu with related options. Like any IDE, it supports the CTRL+Space that auto complete code as you type. There is the ALT+ENTER would suggest hints on how to fix things when you got an error with red squiggle line. Pressing CTRL+B will jump into methods or variable declarations.

Another tips I have for you is the IDEA uses many icon symbols to represent many things. These give you quick visual status on files, progress and states of your Classes etc. It's helpful to see what they mean using this http://www.jetbrains.com/idea/webhelp/symbols.html

So overall, I think IntelliJ IDEA is pretty good and awesome in many way.

Saturday, December 8, 2012

Checking DB Connection using Java

For complete sake, here is a Java version of the Groovy post to test your Oracle Database connection.
package atest;
import java.sql.*;
/**
 * Run arguments sample:
 * jdbc:oracle:thin:@localhost:1521:XE system mypassword123 oracle.jdbc.driver.OracleDriver
 */
public class DbConn {
    public static void main(String[] args) throws Exception {
        String url = args[0];
        String username = args[1];
        String password = args[2];
        String driver = args[3];

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, username, password);
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery("SELECT SYSDATE FROM DUAL");
            while(rs.next()) {
                System.out.println(rs.getObject(1));
            }
        } finally {
            conn.close();
        }
    }
}

Friday, December 7, 2012

Creating Oracle Stored Procedure using Java

Did you know you can write Oracle database stored procedure in Java? Give this a try in your sqlplus prompt.
sql> create or replace and compile java source named "MyJavaDbProcedure" as
sql> public class MyJavaDbProcedure {
sql>   public static String upcase(String text) {
sql>     return text.toUpperCase();
sql>   }
sql> };
sql> /
sql> 
sql> create or replace function upcase (s in varchar2)
sql>   return varchar2
sql> as language java
sql>   name 'MyJavaDbProcedure.upcase(java.lang.String) return java.lang.String';
sql> /
sql> 
sql> select upcase('hello') from dual;
sql> /

I let the database compile a Java source directly, but there is also the java class PL/SQL that you can load Java binary .class file as well. I am sure your DBA will fight all their might to prevent you doing stuff like this. But it's cool to see that this option is available.

Thursday, December 6, 2012

Checking DB connection using Groovy

Here is a simple Groovy script to verify Oracle database connection using JDBC.

@GrabConfig(systemClassLoader=true)
@Grab('com.oracle:ojdbc6:11g')
url= "jdbc:oracle:thin:@localhost:1521:XE"
username = "system"
password = "mypassword123"
driver = "oracle.jdbc.driver.OracleDriver"

// Groovy Sql connection test
import groovy.sql.*
sql = Sql.newInstance(url, username, password, driver)
try {
  sql.eachRow('select sysdate from dual'){ row ->
    println row
  }
} finally {
  sql.close()
}

This script should let you test connection and perform any quick ad hoc queries programmatically. However, when you first run it, it would likely failed without finding the Maven dependency for JDBC driver jar. In this case, you would need to first install the Oracle JDBC jar into maven local repository. This is due to Oracle has not publish their JDBC jar into any public Maven repository. So we are left with manually steps by installing it. Here are the onetime setup steps:

1. Download Oracle JDBC jar from their site: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html.

2. Unzip the file into C:/ojdbc directory.

3. Now you can install the jar file into Maven local repository using Cygwin.

bash> cd /cygdrive/c/ojdbc
bash> mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11g -Dpackaging=jar -Dfile=ojdbc6-11g.jar

That should make your script run successfully. The Groovy way of using Sql has many sugarcoated methods that you let you quickly query and see data on screens. You can see more Groovy feature by studying their API doc.

Note that you would need systemClassLoader=true to make Groovy load the JDBC jar into classpath and use it properly.

Oh, BTW, if you are using Oracle DB production, you will likely using a RAC configuration. The JDBC url connection string for that should look something like this:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MY_DB)))

Update: 12/07/2012

It appears that the groovy.sql.Sql class has a static withInstance method. This let you run onetime DB work without writing try/finally block. See this example:

@GrabConfig(systemClassLoader=true)
@Grab('com.oracle:ojdbc6:11g')
url= "jdbc:oracle:thin:@localhost:1521:XE"
username = "system"
password = "mypassword123"
driver = "oracle.jdbc.driver.OracleDriver"

import groovy.sql.*
Sql.withInstance(url, username, password, driver) { sql ->
  sql.eachRow('select sysdate from dual'){ row ->
    println row
  }
}

It's much more compact. But be aware of performance if you run it multiple times, because you will open and close the a java.sql.Connection per each call!


I have also collected couple other popular databases connection test examples. These should have their driver jars already in Maven central, so Groovy Grab should able to grab them just fine.
// MySQL database test
@GrabConfig(systemClassLoader=true)
@Grab('mysql:mysql-connector-java:5.1.22')
import groovy.sql.*
Sql.withInstance("jdbc:mysql://localhost:3306/mysql", "root", "mypassword123", "com.mysql.jdbc.Driver"){ sql -> 
  sql.eachRow('SELECT * FROM USER'){ row ->
    println row
  }
}
// H2Database
@GrabConfig(systemClassLoader=true)
@Grab('com.h2database:h2:1.3.170')
import groovy.sql.*
Sql.withInstance("jdbc:h2:~/test", "sa", "", "org.h2.Driver"){ sql -> 
  sql.eachRow('SELECT * FROM INFORMATION_SCHEMA.TABLES'){ row ->
    println row
  }
}

Wednesday, December 5, 2012

Changing Oracle XE apex webapp port

If you use Oracle XE for development, then you know it has a simple web interface through http://localhost:8080/apex. Now port 8080 is a common port used by Tomcat server, so letting Oracle XE permanently have taken it is not convenient. But you can change it like this:
 sql> select dbms_xdb.gethttpport from dual;
 sql> exec dbms_xdb.sethttpport('8081');
 sql> commit;

 bash> open http://localhost:8081/apex
Also, if you ever deleted the HR sample database on the XE installation, you can restore it like this:
 bash> cd /c/oraclexe/app/oracle/product/10.2.0/server/demo/schema/human_resources
 bash> sqlplus system < hr_main.sql

Tuesday, December 4, 2012

Inspecting your Oracle database

Some quick tips on how to inspect what you have in your Oracle database.

-- show all schemas
select distinct owner from dba_segments where owner in
     (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'));

-- show all tables from a schema/owner
select * from all_tables where owner = 'HR';

-- show table description
desc HR.EMPLOYEES;

-- show all users
select * from all_users;
select username, * from dba_users;


-- See who is taking up a DB lock
select c.*, b.* from v$lock a, dba_locks b, v$session c 
  where a.id1 = b.lock_id1 and b.session_id = c.sid

-- See internal SQL id
select * from v$sql

-- See Oracle latches:
select * from v$latch

-- See Library Cache of SGA (System Global Area) like buffer cache size:
select * from v$sgastat

-- See tx locks (TX) and DML locks (TM):
select * from v$lock where type in ('TX', 'TM')
select * from dba_locks where lock_type in ('Transaction', 'DML')