edu.stanford.junction.sample.sql.QueryHandler.java Source code

Introduction

Here is the source code for edu.stanford.junction.sample.sql.QueryHandler.java

Source

/*
 * Copyright (C) 2010 Stanford University
 *
 * Licensed 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 edu.stanford.junction.sample.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.json.JSONException;
import org.json.JSONObject;

import edu.stanford.junction.api.activity.JunctionActor;
import edu.stanford.junction.api.messaging.MessageHandler;
import edu.stanford.junction.api.messaging.MessageHeader;

public class QueryHandler extends MessageHandler {

    JunctionActor mActor;

    public QueryHandler(JunctionActor actor) {
        mActor = actor;
    }

    public static void main(String[] argv) throws JSONException {
        //JunctionQuery q = new JunctionQuery("sql","SELECT name,playcount FROM jz_nodes WHERE ptype='genre'");
        //new QueryHandler(null).onMessageReceived(null,q);

    }

    @Override
    public void onMessageReceived(MessageHeader header, JSONObject message) {

        //String query = q.getQueryText();
        String query = message.optString("query");

        query = query.toLowerCase();

        if (!query.contains("select"))
            return;
        if (query.contains("drop") || query.contains("delete"))
            return;
        System.out.println("Got query: " + query);

        Connection connection = null;
        try {
            // Load the JDBC driver
            String driverName = "com.mysql.jdbc.Driver"; // MySQL MM JDBC driver
            Class.forName(driverName);

            // Create a connection to the database
            //String serverName = "192.168.1.122";
            String serverName = "127.0.0.1";
            String mydatabase = "jinzora3";
            String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
            String username = "jinzora";
            String password = "jinzora";
            connection = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            // Could not find the database driver
            e.printStackTrace();
        } catch (SQLException e) {
            // Could not connect to the database
            e.printStackTrace();
        }

        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            ResultSetMetaData rsMetaData = rs.getMetaData();
            int cols = rsMetaData.getColumnCount();

            while (rs.next()) {

                JSONObject row = new JSONObject();
                try {
                    for (int i = 1; i <= cols; i++) { // stupid indexing
                        row.put(rsMetaData.getColumnName(i), rs.getObject(i));
                    }
                } catch (JSONException e) {
                    e.printStackTrace();
                }
                System.out.println("sending " + row);
                if (mActor != null) {
                    //mActor.getJunction().sendMessageToTarget(header.getReplyTarget(),row);
                    header.getReplyTarget().sendMessage(row);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println("closing stream.");
        //results.close();
    }
}