Meh

jdbc vs jdbi

/*
 * Copyright 2010 Ning, Inc.
 *
 * Ning licenses this file to you under the Apache License, version 2.0
 * (the "License"); you may not use this file except in compliance with the
 * License.  You may obtain a copy of the License at:
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.  See the
 * License for the specific language governing permissions and limitations
 * under the License.
 */

package com.ning.metrics.goodwill.store;

import com.google.inject.Inject;
import com.ning.metrics.goodwill.access.GoodwillSchema;
import com.ning.metrics.goodwill.access.GoodwillSchemaField;
import com.ning.metrics.goodwill.binder.config.GoodwillConfig;
import org.apache.log4j.Logger;
import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.Handle;
import org.skife.jdbi.v2.IDBI;
import org.skife.jdbi.v2.PreparedBatch;
import org.skife.jdbi.v2.StatementContext;
import org.skife.jdbi.v2.TransactionCallback;
import org.skife.jdbi.v2.TransactionStatus;
import org.skife.jdbi.v2.tweak.ResultSetMapper;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

public class MySQLStore extends GoodwillStore
{
    private static Logger log = Logger.getLogger(MySQLStore.class);

    private final String TABLE_STRING_DESCRIPTOR = "" +
        "  event_type    = :event_type" +
        ", field_id      = :field_id" +
        ", field_type    = :field_type" +
        ", field_name    = :field_name" +
        ", sql_type      = :sql_type" +
        ", sql_length    = :sql_length" +
        ", sql_precision = :sql_precision " +
        ", sql_scale     = :sql_scale" +
        ", description   = :description ";

    private static final String SELECT_ALL = "" +
        "SELECT" +
        "  id" +
        ", event_type" +
        ", field_name" +
        ", field_type" +
        ", field_id" +
        ", description" +
        ", sql_type" +
        ", sql_length" +
        ", sql_scale" +
        ", sql_precision " +
        "FROM thrift_types " +
        "ORDER BY field_id ASC";

    private static final String SELECT_ALL_BY_NAME = "" +
        "SELECT" +
        "  id" +
        ", field_name" +
        "FROM thrift_types " +
        "WHERE event_type = :event_type";

    private Connection connection;
    private final String tableName;
    private final String DBHost;
    private final int DBPort;
    private final String DBName;
    private final String DBUsername;
    private final String DBPassword;

    private final IDBI dbi;
    private static final String SCHEMA_NAME_FIELD = "event_type";
    private static final String SCHEMA_FIELD_ID_FIELD = "field_id";
    private static final String SCHEMA_FIELD_TYPE_FIELD = "field_type";
    private static final String SCHEMA_FIELD_NAME_FIELD = "field_name";
    private static final String SCHEMA_FIELD_SQL_TYPE_FIELD = "sql_type";
    private static final String SCHEMA_FIELD_SQL_LENGTH_FIELD = "sql_length";
    private static final String SCHEMA_FIELD_SQL_PRECISION_FIELD = "sql_precision";
    private static final String SCHEMA_FIELD_SQL_SCALE_FIELD = "sql_scale";
    private static final String SCHEMA_FIELD_DESCRIPTION_FIELD = "description";

    @Inject
    public MySQLStore(
        GoodwillConfig config
    ) throws SQLException, IOException, ClassNotFoundException
    {
        this(config.getStoreDBHost(), config.getStoreDBPort(), config.getStoreDBName(), config.getStoreDBUsername(), config.getStoreDBPassword(), config.getStoreDBThriftTableName());
    }

    public MySQLStore(
        String DBHost,
        int DBPort,
        String DBName,
        String DBUsername,
        String DBPassword,
        String DBTableName
    ) throws SQLException, IOException, ClassNotFoundException
    {
        tableName = DBTableName;
        this.DBHost = DBHost;
        this.DBPort = DBPort;
        this.DBName = DBName;
        this.DBUsername = DBUsername;
        this.DBPassword = DBPassword;

        dbi = new DBI(String.format("jdbc:mysql://%s:%d/%s", DBHost, DBPort, DBName), DBUsername, DBPassword);
        buildGoodwillSchemaList();
    }

    @Override
    public Collection<GoodwillSchema> getTypes() throws IOException
    {
        try {
            buildGoodwillSchemaList();
        }
        catch (SQLException e) {
            throw new IOException(e);
        }

        final ArrayList<GoodwillSchema> thriftTypesList = new ArrayList(goodwillSchemata.values());
        Collections.sort(thriftTypesList, new Comparator<GoodwillSchema>()
        {
            @Override
            public int compare(GoodwillSchema o, GoodwillSchema o1)
            {
                return o.getName().compareTo(o1.getName());
            }
        });

        if (sink != null) {
            for (int i = 0; i < thriftTypesList.size(); i++) {
                GoodwillSchema schema = thriftTypesList.get(i);
                schema.setSinkAddInfo(sink.addTypeInfo(schema));
                thriftTypesList.set(i, schema);

            }
        }

        return thriftTypesList;
    }


    public class GoodwillSchemaFieldMapper implements ResultSetMapper<GoodwillSchemaField>
    {
        /**
         * Map the row the result set is at when passed in. This method should not cause the result
         * set to advance, allow jDBI to do that, please.
         *
         * @param index  which row of the result set we are at, starts at 0
         * @param result the result set being iterated
         * @param ctx    context
         * @return the value to return for this row
         * @throws java.sql.SQLException if anythign goes wrong go ahead and let this percolate, jDBI will handle it
         */
        @Override
        public GoodwillSchemaField map(int index, ResultSet result, StatementContext ctx) throws SQLException
        {
            String schemaType = result.getString(SCHEMA_NAME_FIELD);

            // Don't convert int from NULL to 0
            Integer sqlLength = result.getInt(SCHEMA_FIELD_SQL_LENGTH_FIELD);
            if (result.wasNull()) {
                sqlLength = null;
            }
            Integer sqlScale = result.getInt(SCHEMA_FIELD_SQL_SCALE_FIELD);
            if (result.wasNull()) {
                sqlScale = null;
            }
            Integer sqlPrecision = result.getInt(SCHEMA_FIELD_SQL_PRECISION_FIELD);
            if (result.wasNull()) {
                sqlPrecision = null;
            }
            return new GoodwillSchemaField(result.getString(SCHEMA_FIELD_NAME_FIELD), result.getString(SCHEMA_FIELD_TYPE_FIELD), result.getShort(SCHEMA_FIELD_ID_FIELD),
                    result.getString(SCHEMA_FIELD_DESCRIPTION_FIELD), result.getString(SCHEMA_FIELD_SQL_TYPE_FIELD), sqlLength, sqlScale, sqlPrecision);
        }
    }

    private void buildGoodwillSchemaList() throws IOException, SQLException
    {
        dbi.inTransaction(new TransactionCallback<Void>()
        {
            @Override
            public Void inTransaction(Handle handle, TransactionStatus status) throws Exception
            {
                handle.createQuery(SELECT_ALL)
                    .map(new GoodwillSchemaFieldMapper())
                    .list();

                return null;
            }
        });
    }
}