org.jooq.java8.goodies.sql.SQLGoodies.java Source code

Introduction

Here is the source code for org.jooq.java8.goodies.sql.SQLGoodies.java

Source

/**
 * Copyright (c) 2011-2014, Data Geekery GmbH, contact@datageekery.com
 * All rights reserved.
 *
 * This software is licensed to you under the Apache License, Version 2.0
 * (the "License"); You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * . Redistributions of source code must retain the above copyright notice, this
 *   list of conditions and the following disclaimer.
 *
 * . Redistributions in binary form must reproduce the above copyright notice,
 *   this list of conditions and the following disclaimer in the documentation
 *   and/or other materials provided with the distribution.
 *
 * . Neither the name "jOOU" nor the names of its contributors may be
 *   used to endorse or promote products derived from this software without
 *   specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */
package org.jooq.java8.goodies.sql;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import static java.sql.DriverManager.getConnection;

/**
 * Created by Lukas on 23.01.14.
 */
public class SQLGoodies {
    static class Schema {
        final String schemaName;
        final boolean isDefault;

        Schema(String schemaName, boolean isDefault) {
            this.schemaName = schemaName;
            this.isDefault = isDefault;
        }

        @Override
        public String toString() {
            return "Schema{" + "schemaName='" + schemaName + '\'' + ", isDefault=" + isDefault + '}';
        }
    }

    public static void main(String[] args) throws Exception {

        Class.forName("org.h2.Driver");
        try (Connection c = getConnection("jdbc:h2:~/test", "sa", "")) {
            String sql = "select schema_name, is_default from information_schema.schemata order by schema_name";

            System.out.println("Fetching data into a with JDBC / Java 7 syntax");
            System.out.println("----------------------------------------------");
            try (PreparedStatement stmt = c.prepareStatement(sql); ResultSet rs = stmt.executeQuery()) {

                while (rs.next()) {
                    System.out.println(new Schema(rs.getString("SCHEMA_NAME"), rs.getBoolean("IS_DEFAULT")));
                }
            }

            System.out.println();
            System.out.println("Fetching data into a lambda expression with jOOQ");
            System.out.println("------------------------------------------------");
            DSL.using(c).fetch(sql)
                    .map(r -> new Schema(r.getValue("SCHEMA_NAME", String.class),
                            r.getValue("IS_DEFAULT", boolean.class)))
                    // could also be written as
                    // .into(Schema.class)
                    .forEach(System.out::println);

            System.out.println();
            System.out.println("Fetching data into a lambda expression with Spring JDBC");
            System.out.println("-------------------------------------------------------");
            new JdbcTemplate(new SingleConnectionDataSource(c, true))
                    .query(sql,
                            (rs, rowNum) -> new Schema(rs.getString("SCHEMA_NAME"), rs.getBoolean("IS_DEFAULT")))
                    .forEach(System.out::println);

            System.out.println();
            System.out.println("Fetching data into a lambda expression with Apache DbUtils");
            System.out.println("-------------------------------------------------------");
            new QueryRunner().query(c, sql, new ArrayListHandler()).stream()
                    .map(array -> new Schema((String) array[0], (Boolean) array[1])).forEach(System.out::println);
        }
    }

    public static Transaction tx(Connection c) {
        return new Transaction(DSL.using(c));
    }

    static class Transaction implements AutoCloseable {
        final DSLContext ctx;

        Transaction(DSLContext ctx) {
            this.ctx = ctx;
        }

        @Override
        public void close() {

        }
    }
}