Kohei Nozaki's blog 

Entries tagged [sql]

BeanShell recipies

Posted on Sunday Jan 24, 2016 at 04:00PM in Technology

BeanShell is a handy lightweight scripting language for Java. In this entry, I’ll introduce you some useful snippets powered by BeanShell, and some recipies about it.

Setup and hello world

Grab a copy of bsh-2.0b4.jar from http://www.beanshell.org and put following shell script named bsh into your PATH:

#!/bin/sh
BEANSHELL_JAR=$HOME/Downloads/bsh-2.0b4.jar # replace path to suit your environment
java -cp $BEANSHELL_JAR bsh.Interpreter $@

Then fire up bsh from your console then just put print("hello, world!"); to confirm it works.

$ bsh
BeanShell 2.0b4 - by Pat Niemeyer (pat@pat.net)
bsh % print("hello, world!");
hello, world!

Hit Ctrl+D to exit interpreter.

You can launch your BeanShell script in a file as follows:

$ echo 'print("hello, world!");' > hello.bsh
$ bsh hello.bsh
hello, world!

Stdin

Text filtering script can be written as follows:

Scanner scanner = new Scanner(System.in);
while (scanner.hasNextLine()) {
  String line = scanner.nextLine();
  System.out.println(line.toUpperCase());
}

Save this script as toUpperCase.bsh . The script can be executed as follows:

$ echo foo | bsh toUpperCase.bsh
FOO

Command line arguments

Command line arguments can be used as follows:

sb = new StringBuilder();
for (arg : bsh.args) {
    sb.append(arg);
}
print(sb);

Save this script as args.bsh. The script can be executed as follows:

$ bsh args.bsh foo bar
foobar

Use of external jar

Any external jar can be added via addClassPath clause dynamically. For example, a SQL beautifier script powered by a Hibernate class can be written as follows:

addClassPath("/path/to/hibernate-core-4.3.7.Final.jar"); // replace path to suit your environment
import org.hibernate.engine.jdbc.internal.BasicFormatterImpl;

scanner = new Scanner(System.in);
sb = new StringBuilder();
while (scanner.hasNextLine()) {
  sb.append(scanner.nextLine()).append('\n');
}

beautifized = new BasicFormatterImpl().format(sb.toString());
print(beautifized);

Save this script as sql-beautifier.bsh then execute following command:

$ SQL="SELECT t0.content AS a2, t0.contenttype AS a3, t0.email AS a4 FROM roller_comment t0, weblogentry t1 WHERE ((t1.websiteid = 'f0588427-f2ca-4843-ac87-bbb31aa6013c') AND (t1.id = t0.entryid)) ORDER BY t0.posttime DESC LIMIT 31 OFFSET 0;"
$ echo $SQL | bsh sql-beautifier.bsh

This yields nicely formatted SQL:

SELECT
    t0.content AS a2,
    t0.contenttype AS a3,
    t0.email AS a4
FROM
    roller_comment t0,
    weblogentry t1
WHERE
    (
        (
            t1.websiteid = 'f0588427-f2ca-4843-ac87-bbb31aa6013c'
        )
        AND (
            t1.id = t0.entryid
        )
    )
ORDER BY
    t0.posttime DESC LIMIT 31 OFFSET 0;

Maven plugin

If you have Maven installed, you can execute any BeanShell script without obtaining bsh-2.0b4.jar by hand. Maven and the beanshell-maven-plugin takes care of it instead of you:

$ mvn com.github.genthaler:beanshell-maven-plugin:1.0:run -Dbsh.file="hello.bsh"
...
[INFO] --- beanshell-maven-plugin:1.0:run (default-cli) @ standalone-pom ---
[INFO] Executing Script
[INFO] file class java.lang.String
[INFO] script class java.lang.Object
[INFO] interpreting file hello.bsh
hello, world!

Note that you don’t need to create pom.xml to execute a simple BeanShell script.

For managing complex dependencies, you can leave that duty to Maven with pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>sql-beautifier</groupId>
    <artifactId>sql-beautifier</artifactId>
    <version>1.0-SNAPSHOT</version>
    <build>
        <plugins>
            <plugin>
                <groupId>com.github.genthaler</groupId>
                <artifactId>beanshell-maven-plugin</artifactId>
                <version>1.0</version>
                <configuration>
                    <script><![CDATA[
                    import java.nio.charset.Charset;
                    import org.apache.commons.io.FileUtils;
                    import org.hibernate.engine.jdbc.internal.BasicFormatterImpl;

                    file = new File(System.getProperty("sql"));
                    sql = FileUtils.readFileToString(file, Charset.defaultCharset());
                    result = new BasicFormatterImpl().format(sql);

                    print(result);
         ]]></script>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>org.hibernate</groupId>
                        <artifactId>hibernate-core</artifactId>
                        <version>4.3.7.Final</version>
                    </dependency>
                    <dependency>
                        <groupId>commons-io</groupId>
                        <artifactId>commons-io</artifactId>
                        <version>2.4</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>
</project>

Save the SQL you want to beautify as original.sql and executing following command yields similar result:

$ mvn bsh:run -Dsql=original.sql

jEdit integration

jEdit has pretty nice integration with BeanShell. You can integrate that SQL beautifier as a jEdit macro. Put following snippet as ~/Library/jEdit/macros/FormatSQL.bsh (for OS X) or create it with Macros → New Macro from jEdit menu bar:

addClassPath("/path/to/hibernate-core-4.3.7.Final.jar"); // replace path to suit your environment
import org.hibernate.engine.jdbc.internal.BasicFormatterImpl;

sql = textArea.getSelectedText();
beautifized = new BasicFormatterImpl().format(sql);
textArea.setSelectedText(beautifized);

Paste SQL to any jEdit buffer, and select SQL statement and execute the macro with Macros → FormatSQL to trigger formatting.

References

Comments [0]

Tags: beanshell jedit maven sql

Notes about using UPSERT on RDBMS

Posted on Saturday Nov 07, 2015 at 12:15PM in Technology

Recently I’ve investigated some ways to implement UPSERT which gets the following job done without problems of race conditions:

  • INSERT a row if there’s no duplicate one with same ID

  • UPDATE a row otherwise

Also another slightly differ requirement:

  • INSERT a row if there’s no duplicate one with same ID

  • Do nothing otherwise

  • Application needs to know whether the query has inserted a row because one is not exist

The above two requirements are needed to implement an application that works with an Amazon SQS which is configured as the destination of Amazon SES notification.

Table to use for experimentation

CREATE TABLE mytable (id INTEGER PRIMARY KEY, cnt INTEGER);

Solution for MySQL (5.6.x)

There’s an easy solution that uses a MySQL specific clause INSERT INTO …​ ON DUPLICATE KEY UPDATE …​. For detail check http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

With the mytable, I’ve done some experimentation (on MySQL 5.6.27) as follows:

  1. Launch two instances of mysql

  2. Execute begin; on both so let a transaction begin for each instances

  3. Execute INSERT INTO mytable (id, cnt) VALUES (1, 1) ON DUPLICATE KEY UPDATE cnt=cnt+1; on both. Note that the following execution will be blocked due to the preceding transaction is about to insert a row which has same ID

  4. Execute commit; on the instance which executes the statement first

  5. Execute commit; on the another instance which has been blocked

Then execute select * from mytable; you will see the desired result:

+----+------+
| id | cnt  |
+----+------+
|  1 |    2 |
+----+------+
1 row in set (0.00 sec)

If you don’t want to update any values if duplicated one already exists, Use following SQL instead:

INSERT INTO mytable (id, cnt) VALUES (1, 1) ON DUPLICATE KEY UPDATE id=id;

Also note that if you’re using JDBC to communicate with MySQL, You need to add useAffectedRows=true parameter to the JDBC URL so that executeUpdate() method will return the number of affected rows instead of found rows. For detail check https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

Another solution

I found an interesting attempt that seems to work with generic SQL:

http://www.xaprb.com/blog/2005/09/25/insert-if-not-exists-queries-in-mysql/ http://stackoverflow.com/a/17067131/3591946

And I’ve confirmed it works as I expected. I’ve done the following experiment on MySQL 5.6.x:

  1. Launch two instances of mysql

  2. Execute begin; on both so let a transaction begin for each instances

  3. Execute INSERT INTO mytable (id, cnt) SELECT 1, 0 FROM (select 0 as i) mutex LEFT JOIN mytable ON id = 1 WHERE i = 0 AND id IS NULL;. Note that the following execution will be blocked as well

  4. Execute UPDATE mytable SET cnt = cnt + 1 WHERE id = 1; on the instance which executes the statement first, if incrementation is needed

  5. Execute commit; on the instance which executes the statement first

  6. Execute UPDATE mytable SET cnt = cnt + 1 WHERE id = 1; on the instance which executes the statement second as well

  7. Execute commit; on the another instance

Note that I’ve tried the experimentation for PostgreSQL 9.3.4 as well but doesn’t work. It blocks the following query but produces ERROR: duplicate key value violates unique constraint "mytable_pkey" after issuing commit of the preceding transaction.

I have no idea why it doesn’t work for PostgreSQL (To be honest, I don’t exactly know why it does work for MySQL). If you know why, Let me know via posting a comment to this entry that would be greatly appreciated.

UPSERT functionality will be in the PostgreSQL 9.5 release (citation from https://wiki.postgresql.org/wiki/SQL_MERGE).

Comments [0]

Tags: mysql postgresql ses sql sqs

Hot tags

Below are the most often used tags in the blog. Hover over a tag to see a count of entries, click a tag to see the most recent posts with the tag.