rJava : プログラミング(5) データベース連携

javaでデータベースに接続し情報を持ってきてそれをRに渡し結果を得るまでやってみました。

１．準備
環境設定(MySQL-connector) (http://mizunolab.sist.ac.jp/2015/11/linuxjavamysql.html)
前回のソース(http://mizunolab.sist.ac.jp/2016/01/rjava-4.html)

２．プログラム

今回はデータベースに接続し、１７件のデータを持ってきてRに渡し、ヒストグラムを描く。１７件はselect count(*)でやる方がいいけど、面倒だったので直書きです。

$ vi JRITest07.java
import org.rosuda.JRI.REXP;
import org.rosuda.JRI.Rengine;
import java.sql.*;
import java.util.Arrays;

public class JRITest07 {

        public static void main(String[] args)
        {
                MySQL mysql = new MySQL();
                Rengine re = new Rengine( new String[]{"--no-save"}, false, null );
                ResultSet rs = mysql.selectOperators();

                double result [] = new double[17];
                double sum = 0;
                int index = 0;
                try {
                        while(rs.next()){
                                sum += rs.getDouble("awareness");
                                sum += rs.getDouble("decision");
                                sum += rs.getDouble("communication");
                                sum += rs.getDouble("leadership");
                                result[index] = sum/48*100;
                                sum = 0;
                                index ++;
                        }
                } catch (SQLException e) {
                        e.printStackTrace();
                }
                System.out.println(Arrays.toString(result));

                re.eval("c<-c br="" result="">                REXP x = re.eval("summary(c)");
                System.out.println(x);
                double [] ary = x.asDoubleArray();
                for (int i = 0; i < ary.length; i++) {
                        System.out.println(ary[i]);
                }
                re.eval("png('hist02.png', 640, 480)");
                re.eval("hist(c)");
                re.eval("dev.off()");
                re.end();
        }
}
class MySQL {

        String driver;
        String server, dbname, url, user, password;
        Connection con;
        Statement stmt;
        ResultSet rs;

        public MySQL() {
                this.driver = "org.gjt.mm.mysql.Driver";
                this.server = "localhost";
                this.dbname = "naisyo";
                this.url = "jdbc:mysql://" + server + "/" + dbname + "?useUnicode=true&characterEncoding=UTF-8";
                this.user = "naisyo";
                this.password = "oshierumonka";
                try {
                        this.con = DriverManager.getConnection(url, user, password);
                        this.stmt = con.createStatement ();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block                                                     
                        e.printStackTrace();
                }
                try {
                        Class.forName (driver);
                } catch (ClassNotFoundException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
        }
        public void close(){
                try {
                        rs.close();
                        stmt.close();
                        con.close();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
        }

        public ResultSet selectOperators(){

                String sql = "SELECT * FROM operators WHERE awareness !=0 AND decision !=0 AND communication !=0 AND leadership !=0";
                ResultSet rs = null;
                try {
                        rs = stmt.executeQuery (sql);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rs;
        }

}

３．実行
[notss_analysis@ip-10-0-0-217 rjava]$ javac JRITest07.java
[notss_analysis@ip-10-0-0-217 rjava]$ java -Djava.library.path=/usr/lib64/R/library/rJava/jri/ JRITest07
[92.1875, 97.22222222222085, 90.70512820512708, 92.46794871795, 97.64492753623125, 88.5869565217375, 89.91228070175416, 87.73148148148331, 78.47222222222229, 76.33928571428582, 79.03225806451624, 62.05357142857145, 95.83333333333334, 70.83333333333334, 70.22058823529417, 61.111111111111036, 56.24999999999979]
[REAL* (56.25, 70.83, 87.73, 81.56, 92.19, 97.64)]
56.25
70.83
87.73
81.56
92.19
97.64

FTPで接続し、グラフ画像を確認する（そっけない）

一応ここまで出来たので、R配列の作り方やヒストグラムのオプションをもう少し綺麗にやりたい。