com.ibm.research.rdf.store.runtime.service.sql.UpdateHelper.java Source code

Java tutorial

Introduction

Here is the source code for com.ibm.research.rdf.store.runtime.service.sql.UpdateHelper.java

Source

/******************************************************************************
 * Copyright (c) 2015 IBM Corporation.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 *
 * Contributors:
 *     IBM Corporation - initial API and implementation
 *****************************************************************************/
package com.ibm.research.rdf.store.runtime.service.sql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.ibm.research.rdf.store.Store;

public class UpdateHelper {

    private static final Log log = LogFactory.getLog(UpdateHelper.class);

    private static final String db2_sp_addTriple = "CALL DB2INST1.ADDTRIPLE(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String db2_sp_deleteTriple = "CALL DB2INST1.DELETETRIPLE(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    public static boolean addTriple(Connection conn, String backend, String schemaName, String datasetName,
            String sub, String pred, String obj, int obj_datatype, int dft_dcol, int dft_rcol, String gid,
            int lock_mode) {
        if (backend.equalsIgnoreCase(Store.Backend.db2.name())) {
            String ret = executeCall(conn, db2_sp_addTriple, 11, schemaName, datasetName, sub, obj, pred,
                    obj_datatype, dft_dcol, dft_rcol, gid, lock_mode);
            System.out.println("---Add Triple: " + ret);
            return ret == "FINISHED";
        } else {
            return false;
        }
    }

    public static boolean deleteTriple(Connection conn, String backend, String schemaName, String datasetName,
            String sub, String pred, String obj, int dph_size, int rph_size, int dft_dcol, int dft_rcol, String gid,
            int lock_mode) {
        if (backend.equalsIgnoreCase(Store.Backend.db2.name())) {
            String ret = executeCall(conn, db2_sp_deleteTriple, 12, schemaName, datasetName, sub, obj, pred,
                    dph_size, rph_size, dft_dcol, dft_rcol, gid, lock_mode);
            System.out.println("---Delete Triple: " + ret);
            return ret == "FINISHED";
        } else {
            return false;
        }
    }

    //copy & modify SQLExecutor.executeCall here to return the result codes
    private static String executeCall(Connection conn, String sql, int retPid, Object... params) {

        CallableStatement stmt = null;
        String ret = null;

        try {
            conn.setAutoCommit(false);
        } catch (SQLException ex) {
            log.error(ex);
            ex.printStackTrace();
            System.out.println(ex.getLocalizedMessage());
            return ret;
        }

        try {

            stmt = conn.prepareCall(sql);
            int i = 1;
            for (Object o : params) {
                stmt.setObject(i, o);
                i++;
            }

            stmt.registerOutParameter(retPid, Types.VARCHAR);

            stmt.execute();
            ret = stmt.getString(retPid);

            conn.commit();

        } catch (SQLException e) {
            //         log.error(e);
            //         e.printStackTrace();
            //         System.out.println(e.getLocalizedMessage());
            ret = null;

            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                log.error(e1);
                e1.printStackTrace();
                System.out.println(e1.getLocalizedMessage());
                ret = null;
            }

        } finally {
            closeSQLObjects(stmt, null);
        }

        try {
            conn.setAutoCommit(true);
        } catch (SQLException ex) {
            log.error(ex);
            ex.printStackTrace();
            System.out.println(ex.getLocalizedMessage());
            ret = null;
        }

        return ret;
    }

    private static void closeSQLObjects(PreparedStatement stmt, ResultSet rs) {
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
        }

        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
        }
    }

    public static void main(String[] args) {
        String JDBC_URL = "jdbc:db2://localhost:10997/lubm";
        String user = "db2inst1";
        String password = "db2admin";

        String backend = "db2";
        String schema = "db2inst1";
        String store = "lubm_100m_r";
        int lock = 1;
        int dph_size = 11;
        int rph_size = 4;

        String[][] triples = { { "http://usersub1/", "http://testpred1/", "http://userobj1/" },
                { "http://usersub1/", "http://testpred2/", "http://userobj2/" },
                { "http://usersub1/", "http://testpred1/", "userobj3" },
                { "http://usersub2/", "http://testpred2/", "http://userobj2/" },
                { "http://usersub1/", "http://testpred3/", "userobj4" },
                { "http://usersub1/", "http://testpred4/", "userobj4" },
                { "http://usersub2/", "http://testpred8/", "http://userobj2/" },
                { "http://usersub2/", "http://testpred6/", "http://userobj2/" },
                { "http://usersub2/", "http://testpred7/", "http://userobj2/" },
                { "http://usersub1/", "http://testpred5/", "userobj5" },
                { "http://usersub2/", "http://testpred9/", "http://userobj2/" },
                { "http://usersub3/", "http://testpred2/", "http://userobj2/" },
                { "http://usersub1/", "http://testpred1/", "userobj4" },
                { "http://usersub1/", "http://testpred1/", "userobj5" },
                { "http://usersub1/", "http://testpred3/", "userobj5" },
                { "http://usersub4/", "http://testpred2/", "http://userobj2/" },
                { "http://usersub3/", "http://testpred1/", "userobj3" } };

        try {

            String classNameString = new String("com.ibm.db2.jcc.DB2Driver");
            Class.forName(classNameString);

            Connection conn = DriverManager.getConnection(JDBC_URL, user, password);

            String gid = "DEF";

            //add triples to graph DEF
            for (int i = 0; i < triples.length; i++) {
                String sub = triples[i][0];
                String pred = triples[i][1];
                String obj = triples[i][2];
                int obj_dt = obj.startsWith("http://") ? 10002 : 5001;
                int dft_col = i % 2;

                addTriple(conn, backend, schema, store, sub, pred, obj, obj_dt, dft_col, dft_col, gid, lock);
            }

            gid = "G1";

            //add triples to graph G1
            //some operations will fail due to UNIQUE indexes (on DS, RS) without GID
            for (int i = 0; i < triples.length; i++) {
                String sub = triples[i][0];
                String pred = triples[i][1];
                String obj = triples[i][2];
                int obj_dt = obj.startsWith("http://") ? 10002 : 5001;
                int dft_col = i % 2;

                addTriple(conn, backend, schema, store, sub, pred, obj, obj_dt, dft_col, dft_col, gid, lock);
            }

            gid = "DEF";

            //delete triples to graph DEF
            for (int i = triples.length - 1; i >= 0; i--) {
                String sub = triples[i][0];
                String pred = triples[i][1];
                String obj = triples[i][2];
                int dft_col = i % 2;

                deleteTriple(conn, backend, schema, store, sub, pred, obj, dph_size, rph_size, dft_col, dft_col,
                        gid, lock);
            }

            gid = "G1";

            //delete triples to graph G1
            for (int i = triples.length - 1; i >= 0; i--) {
                String sub = triples[i][0];
                String pred = triples[i][1];
                String obj = triples[i][2];
                int dft_col = i % 2;

                deleteTriple(conn, backend, schema, store, sub, pred, obj, dph_size, rph_size, dft_col, dft_col,
                        gid, lock);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }
}