bookug
7/17/2017 - 1:46 PM

use Virtuoso's RESTful API to answer SPARQL queries in Java

use Virtuoso's RESTful API to answer SPARQL queries in Java

//REFERENCE:
//http://www.2cto.com/database/201405/298274.html
//http://docs.openlinksw.com/virtuoso/jdbcurl4mat/

//Problem: Number of licensed connections exceeded
//https://sourceforge.net/p/virtuoso/mailman/message/28445588/

//JDBC:
//http://vos.openlinksw.com/owiki/wiki/VOS/VOSDownload

import java.io.File;
import java.net.URLDecoder;
//DriverManager在java.sql这个包里面,管理一组 JDBC 驱动程序的基本服务
import java.sql.*;
import java.util.*;

public class Virtuoso {
	
	public static Virtuoso instance = new Virtuoso();
	//NOTICE: we do not use the cache because it will crash if the result's size is large
	//Besides, FileUtil is not ok, we need to implement it.
	//NOTICE: virtuoso has implemented a good query-level cache by itself, and we are not expected to do better
	public boolean useCache = false;
	Connection conn;
	//String cacheFilePath = "resources/caches/virtuoso.cache";	
	//HashMap<String, List<String>> cache = new HashMap<String, List<String>>();
	//HashMap<String, List<String>> updatedCache = new HashMap<String, List<String>>();
	//public static int writeCacheThreshold = 10000;

	public void connect()
	{
		try {
			//加载想要连接的数据库的驱动到JVM(Java虚拟机)
			Class.forName("virtuoso.jdbc4.Driver");
			conn = DriverManager.getConnection("jdbc:virtuoso://0.0.0.0:1111/freebase","dba","dba");
			/*
			if( this.useCache )
				this.loadCache();
				*/
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void close()
	{
		try{
			if( conn != null ){
				 conn.close();
			}
			/*
			if( this.useCache ){
				List<String> outputs = writeCache();
				FileUtil.writeFile(outputs, cacheFilePath, true);
			}
			*/
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	/*
	public void flushCache()
	{
		List<String> outputs = writeCache();
		FileUtil.writeFile(outputs, cacheFilePath, true);
		this.updatedCache.clear();
	}
	public void loadCache(){
		List<String> lines = new ArrayList<String>();
		if( new File(cacheFilePath).exists() )
			lines = FileUtil.readFile(cacheFilePath);
		for(String line : lines){
			String[] info = line.split("\t");
			String key = info[0];
			List<String> values = new ArrayList<String>();
			for(int i = 1; i < info.length; i++)
				values.add(info[i]);
			this.cache.put(key, values);
		}
	}
	public List<String> writeCache(){
		List<String> lines = new ArrayList<String>();
		for(String key : this.updatedCache.keySet() ){
			StringBuffer buffer = new StringBuffer();
			List<String> values = this.updatedCache.get(key);
			buffer.append(key.replace("\t", " ").replace("\n", " ").trim() + "\t");
			for(String value : values)
				buffer.append(value+"\t");
			lines.add( buffer.toString().trim() );
		}
		return lines;
	}
	*/
	
	public Virtuoso(){
		try {
			this.connect();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/*
	 * An example of sparql: "sparql select * where { <fb:g.11vjx745d> ?y ?z}"
	 */
	public List<String> execute(String sparql)
	{
		/*
		if( this.cache.containsKey(sparql.replace("\t", " ").replace("\n", " ").trim()) ){
			return this.cache.get(sparql.replace("\t", " ").replace("\n", " ").trim());
		}
		*/
		List<String> result = new ArrayList<String>();
		try{
			Statement stmt = conn.createStatement();
			boolean more = stmt.execute(sparql);
		    ResultSetMetaData data = stmt.getResultSet().getMetaData();
		    while(more)
		    {
		    	ResultSet rs = stmt.getResultSet();
				while(rs.next())
				{
					StringBuffer s = new StringBuffer();
				    for(int i = 1;i <= data.getColumnCount();i++)
				    {
				    	s.append(rs.getString(i) + "\t");
				    }
				    String sUTF8 = new String(s.toString().getBytes("ISO8859-1"),"UTF-8");
				    result.add(sUTF8.trim());
				}
				more = stmt.getMoreResults();
		    }
		    stmt.close();
		    /*
		    this.updatedCache.put(sparql, result);
		    if(this.useCache & this.updatedCache.size() > writeCacheThreshold)
		    	flushCache();
		    	*/
		    
		    return result;
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	public List<String> executeFuzzyQuery(String sp){
		List<String> names = new ArrayList<String>();
		String subj = sp.split("\t")[0];
		String pred = sp.split("\t")[1].trim();
		try{
			Set<String> result = new HashSet<String>();
			for(String triple : this.execute("sparql select ?x where {<"+subj.replace("ns:", "http://rdf.freebase.com/ns/")+"> <"+pred.replace("ns:", "http://rdf.freebase.com/ns/")+"> ?x .}")){
				String mid = triple;
				result.add(mid);
			}
			if( result.size() == 0 ){
				List<String> triples = this.execute("sparql select ?x ?z where {<"+subj.replace("ns:", "http://rdf.freebase.com/ns/")+"> ?x ?y."
						+ "?y <"+pred.replace("ns:", "http://rdf.freebase.com/ns/")+"> ?z .}");
				if( triples.size() > 60000 )
					return names;
				String newPred = new String();
				for(String triple : triples){
					String mid = triple.split("\t")[1];
					newPred = triple.split("\t")[0].replace("http://rdf.freebase.com/ns/", "ns:")+".."+pred.replace("ns:", "");
					result.add(mid);
				}
				names.add(newPred);
			}
			else
				names.add(pred);
			
			
			result.remove(subj.replace("ns:", "http://rdf.freebase.com/ns/"));
			
			if( result.size() > 10000 )
				return names;
			for(String mid : result){
				if( !mid.startsWith("http://rdf.freebase.com/ns/") ){
					names.add("\""+URLDecoder.decode(new String(mid.replace("%", "").getBytes("ISO8859-1")), "utf-8").replace("\"", "\\\"")+"\"");
				}
				else{
					for(String triple : this.execute("sparql select * where {<"+mid+"> <http://rdf.freebase.com/ns/type.object.name> ?z .}")){
						names.add("\""+URLDecoder.decode(new String(triple.replace("%", "").getBytes("ISO8859-1")), "utf-8").replace("\"", "\\\"")+"\"");
					}
				}
			}
			return names;
			
		}catch(Exception e){
			
		}
		return names;
	}
	
	@SuppressWarnings("finally")
	public Set<String> executeSPQuery(String sp) {
		Set<String> names = new HashSet<String>();
		try {
			Set<String> result = new HashSet<String>();
			String subj = sp.split("\t")[0];
			String pred = sp.split("\t")[1].trim();
			if (pred.indexOf("..") != -1) 
			{
				String firstPredicate = pred.split("\\.\\.")[0];
				String secondPredicate = "ns:" + pred.split("\\.\\.")[1];
				for (String triple : this
						.execute("sparql select ?y where {<" + subj.replace("ns:", "http://rdf.freebase.com/ns/")
								+ "> <" + firstPredicate.replace("ns:", "http://rdf.freebase.com/ns/") + "> ?x. "
								+ "?x <" + secondPredicate.replace("ns:", "http://rdf.freebase.com/ns/") + "> ?y .}")) {
					String mid = triple;
					result.add(mid);
				}
			} 
			else 
			{
				for (String triple : this
						.execute("sparql select * where {<" + subj.replace("ns:", "http://rdf.freebase.com/ns/") + "> <"
								+ pred.replace("ns:", "http://rdf.freebase.com/ns/") + "> ?z .}")) {
					String mid = triple;
					result.add(mid);
				}
			}
			//Subject entity may occurred in answer set? Yes, <spouse>
			result.remove(subj.replace("ns:", "http://rdf.freebase.com/ns/"));
			
			if( result.size() > 500 )
				return names;

			//IDs should be translated to Names.
			for (String mid : result) 
			{
				if (!mid.startsWith("http://rdf.freebase.com/ns/")) 
				{
					names.add(mid);	//If json, maybe need : replace("\"", "\\\"")
					//names.add("\"" + URLDecoder.decode(new String(mid.replace("%", "").getBytes("ISO8859-1")), "utf-8").replace("\"", "\\\"") + "\"");
				} 
				else 
				{
					for (String triple : this.execute("sparql select * where {<" + mid
							+ "> <http://rdf.freebase.com/ns/type.object.name> ?z .}")) 
					{
						names.add(triple); //If json, maybe need : replace("\"", "\\\"")
						//names.add("\"" + URLDecoder.decode(new String(triple.replace("%", "").getBytes("ISO8859-1")), "utf-8").replace("\"", "\\\"") + "\"");
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			return names;
		}
	}

	public Set<String> retrieveNeighbourNodes(String sp){
		Set<String> results = new HashSet<String>();
		String[] info = sp.split("\t");
		String subj = "ns:"+info[0];
		String pred = "ns:"+info[1];
		if( pred.indexOf("..") == -1 )
			return results;
		String firstPred = pred.split("\\.\\.")[0];
		List<String> triples = this.execute("sparql select ?z where {<"+subj.replace("ns:", "http://rdf.freebase.com/ns/")+"> <" + firstPred.replace("ns:", "http://rdf.freebase.com/ns/") + ">?x."
				+ "?x ?y ?z .}");
		
		for(String triple : triples){
			if( triple.equals(subj.replace("ns:", "http://rdf.freebase.com/ns/")))
				continue;
			if( !triple.startsWith("http://rdf.freebase.com/ns/") )
				results.add(triple);
			else
				results.addAll(this.retrieveNames(triple.replace("http://rdf.freebase.com/ns/", "")));
		}
		return results;
	}
	
	@SuppressWarnings("finally")
	public Set<String> executePOQuery(String po){
		Set<String> names = new HashSet<String>();
		try{
			Set<String> result = new HashSet<String>();
			String pred = po.split("\t")[0].trim();
			String obj = po.split("\t")[1].trim();
			for (String triple : this.execute("sparql select * where {?x <" + pred.replace("ns:", "http://rdf.freebase.com/ns/") + "> <"
							+ obj.replace("ns:", "http://rdf.freebase.com/ns/") + "> .}")) {
				String mid = triple;
				result.add(mid);
			}
			
			result.remove(obj.replace("ns:", "http://rdf.freebase.com/ns/"));
			
			
			if( result.size() > 100 )
				return names;
			for(String mid : result){
				if( !mid.startsWith("http://rdf.freebase.com/ns/") ){
					names.add("\""+URLDecoder.decode(new String(mid.replace("%", "").getBytes("ISO8859-1")), "utf-8").replace("\"", "\\\"")+"\"");
				}
				else{
					for(String triple : this.execute("sparql select * where {<"+mid+"> <http://rdf.freebase.com/ns/type.object.name> ?z .}")){
						names.add("\""+URLDecoder.decode(new String(triple.replace("%", "").getBytes("ISO8859-1")), "utf-8").replace("\"", "\\\"")+"\"");
					}
				}
			}
		}catch(Exception e){
		}finally{
			return names;
		}
		
	}
	
	@SuppressWarnings("finally")
	public Set<String> executeTriangle(String sp){
		Set<String> names = new HashSet<String>();
		try{
			Set<String> result = new HashSet<String>();
			String[] info = sp.split("\t");
			String subj = info[0];
			String pred_1 = info[1].trim();
			String pred_2 = info[2].trim();
			for (String triple : this
					.execute("sparql select ?y where {?x <" + pred_1.replace("ns:", "http://rdf.freebase.com/ns/") + "> <"
							+ subj.replace("ns:", "http://rdf.freebase.com/ns/") + "> . " + "?x <"
							+ pred_2.replace("ns:", "http://rdf.freebase.com/ns/") + "> ?y .}")) {
				String mid = triple;
				result.add(mid);
			}

			if (result.size() > 100)
				return names;
			for (String mid : result) {
				if (!mid.startsWith("http://rdf.freebase.com/ns/")) {
					names.add("\"" + URLDecoder.decode(new String(mid.replace("%", "").getBytes("ISO8859-1")), "utf-8").replace("\"", "\\\"") + "\"");
				} else {
					for (String triple : this.execute(
							"sparql select * where {<" + mid + "> <http://rdf.freebase.com/ns/type.object.name> ?z .}")) {
						names.add("\"" + URLDecoder.decode(new String(triple.replace("%", "").getBytes("ISO8859-1")), "utf-8").replace("\"", "\\\"") + "\"");
					}
				}
			}
		}catch(Exception e){
			
		}finally{
			return names;
		}
	}
	
	public Set<String> retrieveAnswerType(String sp) throws Exception{
		Set<String> types = new HashSet<String>();
			String subj = sp.split("\t")[0];
			String pred = sp.split("\t")[1].trim();
			if( pred.indexOf("..") != -1 ){
				String firstPredicate = pred.split("\\.\\.")[0];
				String secondPredicate = "ns:"+pred.split("\\.\\.")[1];
				for(String triple : this.execute("sparql select ?x where {<"+subj.replace("ns:", "http://rdf.freebase.com/ns/")+"> <"+firstPredicate.replace("ns:", "http://rdf.freebase.com/ns/")+"> ?x. "
						+ "?x <"+secondPredicate.replace("ns:", "http://rdf.freebase.com/ns/")+"> ?y .}" 
						+ "?y <http://rdf.freebase.com/ns/type.object.type> ?z ." )){
					types.add(triple);
				}
			}
			else{
				for(String triple : this.execute("sparql select ?y where {<"+subj.replace("ns:", "http://rdf.freebase.com/ns/")+"> <"+pred.replace("ns:", "http://rdf.freebase.com/ns/")+"> ?x ."
						+ "?x <http://rdf.freebase.com/ns/type.object.type> ?y ."
						+ "}")){
					types.add(triple);
				}
			}
		
		return types;
	}
	
	public Set<String> retrieveRelations(String subj) throws Exception{
		Set<String> relations = new HashSet<String>();
			for(String triple : this.execute("sparql select ?x ?z where {<"+subj.replace("ns:", "http://rdf.freebase.com/ns/")+"> ?x ?y. "
						+ "?y ?z ?m .}") ){
				triple = triple.replace("http://rdf.freebase.com/ns/", "");
				String[] info = triple.split("\t");
				relations.add("ns:"+info[0]);
				if( !info[1].equals("type.object.name") && !info[1].startsWith("user.") && !info[1].startsWith("base."))
					relations.add("ns:"+info[0]+".."+info[1]);
			}
		
		return relations;
	}
	
	public List<String> retrieveWikiIds(String mid) throws Exception{
		List<String> triples = this.execute("sparql select ?x where { <http://rdf.freebase.com/en/"+mid+"> <http://rdf.freebase.com/ns/type.namespace.keys> ?x}");
		for(int i = triples.size()-1; i >= 0; i--){
				System.err.println(triples.get(i));
		}
		return triples;
	}
	
	public List<String> retrieveNames(String mid){
		List<String> triples = this.execute("sparql select ?x where { <http://rdf.freebase.com/ns/"+mid+"> <http://rdf.freebase.com/ns/type.object.name> ?x}");
		return triples;
	}
	
	public String retrieveDescription(String mid){
		List<String> triples = this.execute("sparql select ?x where { <http://rdf.freebase.com/ns/"+mid+"> <http://rdf.freebase.com/ns/common.topic.description> ?x}");
		if( triples.size() == 0 )
			return "";
		return triples.get(0);
	}
	
	public Set<String> execute2HopQuery(String subj, String firstPred, String secondPred, String sibling){
		
		Set<String> results = new HashSet<String>();
		subj = "ns:"+subj;
		firstPred = "ns:"+firstPred;
		secondPred = "ns:"+secondPred;
		List<String> triples = this.execute("sparql select ?z ?target where {<"+subj.replace("ns:", "http://rdf.freebase.com/ns/")+"> <" + firstPred.replace("ns:", "http://rdf.freebase.com/ns/") + "> ?x. "
				+ "?x <"+secondPred.replace("ns:", "http://rdf.freebase.com/ns/")+"> ?z."
				+ "?x ?n ?target .}");
		
		for(int i = 0; i < triples.size(); ){
			String triple = triples.get(i);
			String[] info = triple.split("\t");
			String ans = new String();
			String target = new String();
			if( info.length != 2  ){
				ans = triples.get(i);
				target = triples.get(i+1);
				i+=2;
			}
			else{
				ans = info[0];
				target = info[1];
				i++;
			}
			
			if( ans.equals(subj.replace("ns:", "http://rdf.freebase.com/ns/")) )
				continue;
			if( target.equals(sibling) )
				results.add(ans);
		}
		
		if( results.size() == 0 ){
			triples = this.execute("sparql select ?z ?target where {<"+subj.replace("ns:", "http://rdf.freebase.com/ns/")+"> <" + firstPred.replace("ns:", "http://rdf.freebase.com/ns/") + "> ?x."
					+ "?x <"+secondPred.replace("ns:", "http://rdf.freebase.com/ns/")+"> ?z. "
					+ "?x ?n ?y. "
					+ "?y <http://rdf.freebase.com/ns/type.object.name> ?target.}");
		}
		
		for(int i = 0; i < triples.size(); ){
			String triple = triples.get(i);
			String[] info = triple.split("\t");

			String ans = new String();
			String target = new String();
			if( info.length != 2  ){
				ans = triples.get(i);
				target = triples.get(i+1);
				i+=2;
			}
			else{
				ans = info[0];
				target = info[1];
				i++;
			}
			
			if( ans.equals(subj.replace("ns:", "http://rdf.freebase.com/ns/")) )
				continue;
			if( target.equals(sibling) )
				results.add(ans);
		}
		HashSet<String> names = new HashSet<String>();
		for(String result : results){
			names.addAll(this.retrieveNames(result.replace("http://rdf.freebase.com/ns/", "")));
		}
		
		return names;
	}
	
	
	public static void main(String[] args) {
		Virtuoso instance = new Virtuoso();
//		String sparql = "sparql PREFIX ns: <http://rdf.freebase.com/ns/> SELECT DISTINCT ?x, ?name WHERE { ns:m.0rmlp ns:location.location.containedby ?x . ?x ns:common.topic.notable_types ns:location.us_county . ?x ns:type.object.name ?name .}";
		String sparql = "sparql PREFIX ns: <http://rdf.freebase.com/ns/> SELECT DISTINCT ?x, ?name WHERE { ns:m.04ly1 ns:location.location.time_zones ?x . ?x ns:type.object.name ?name .}";
		List<String> answers = instance.execute(sparql);
		System.out.println(answers);
	}
}