From SQL to Cypher

package cn.dh.neo;

import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.neo4j.cypher.javacompat.ExecutionEngine;
import org.neo4j.cypher.javacompat.ExecutionResult;
import org.neo4j.graphdb.GraphDatabaseService;
import org.neo4j.graphdb.Node;
import org.neo4j.graphdb.RelationshipType;
import org.neo4j.graphdb.Transaction;
import org.neo4j.graphdb.factory.GraphDatabaseFactory;
import org.neo4j.graphdb.index.Index;
import org.neo4j.kernel.impl.util.FileUtils;

/**
 * 
 * <p>User: 裴东辉
 * <p>Date: 2014-8-6  上午10:43:10
 * <p>Version: 1.0
 */
public class Neosql {
    
    public static enum RelationshipTypes implements RelationshipType { 
        FLLOW, //用户链          歌手出版专辑
        EMAIL//用户-邮箱         专辑包含很多歌曲
      } 
    
    public final String USERPK="name";
    public final String EMAILPK="email";
    
    /**
     * 数据初始化
     * @author 裴东辉
     * @since 2014-8-6  上午10:46:18
     */
    public void initdata(){
        try {FileUtils.deleteRecursively(new File("db/user.db"));} catch (IOException e) {e.printStackTrace();} //删除数据库
        GraphDatabaseService graphDb = new GraphDatabaseFactory().newEmbeddedDatabase( "db/user.db" );
        try(Transaction tx = graphDb.beginTx()){ 
            //节点索引
            Index<Node> usersIndex = graphDb.index().forNodes("usersIndex"); 
            //-----------------第1个用户，第1个用户的2个邮箱
            Node node1 = graphDb.createNode(); 
            node1.setProperty(USERPK, "admin");
            usersIndex.add(node1, USERPK, node1.getProperty(USERPK));
            
            Node node1_1=graphDb.createNode();
            node1_1.setProperty(EMAILPK, "softwareworker@126.com");
            node1_1.setProperty("comment", "work");
            usersIndex.add(node1_1, EMAILPK, node1_1.getProperty(EMAILPK));
            
            Node node1_2=graphDb.createNode();
            node1_2.setProperty(EMAILPK, "dhsoftware8091@126.com");
            node1_2.setProperty("comment", "home");
            usersIndex.add(node1_2, EMAILPK, node1_2.getProperty(EMAILPK));
            
            node1.createRelationshipTo(node1_1, RelationshipTypes.EMAIL);
            node1.createRelationshipTo(node1_2, RelationshipTypes.EMAIL);
            
            //-----------------第2个用户，第2个用户的2个邮箱
            Node node2 = graphDb.createNode(); 
            node2.setProperty(USERPK, "user");
            usersIndex.add(node2, USERPK, node2.getProperty(USERPK));
            
            Node node2_1=graphDb.createNode();
            node2_1.setProperty(EMAILPK, "usesoftware@126.com");
            node2_1.setProperty("comment", "work");
            usersIndex.add(node2_1, EMAILPK, node2_1.getProperty(EMAILPK));
            
            Node node2_2=graphDb.createNode();
            node2_2.setProperty(EMAILPK, "dhusehome@126.com");
            node2_2.setProperty("comment", "home");
            usersIndex.add(node2_2, EMAILPK, node2_2.getProperty(EMAILPK));
            
            node2.createRelationshipTo(node2_1, RelationshipTypes.EMAIL);
            node2.createRelationshipTo(node2_2, RelationshipTypes.EMAIL);
            
            //----两个用户之间的关联关系
            node1.createRelationshipTo(node2, RelationshipTypes.FLLOW);
            
            tx.success();
        }finally{
            graphDb.shutdown();
        }
    }
    
     
    /**
     * select * from t_user  where name = ‘admin‘
     * start user=node:usersIndex(name = {name} ) return user ,其中的{name}通过一个Map的参数传递过去params
     * 1、SQL starts with the result you want — we SELECT what we want and then declare how to source it. In
            Cypher, the START clause is quite a different concept which specifies starting points in the graph from
            which the query will execute.
        2、From a SQL point of view, the identifiers in START are like table names that point to a set of nodes or
            relationships. The set can be listed literally, come via parameters, or as I show in the following example,
            be defined by an index look-up.
        3、So in fact rather than being SELECT-like, 
            the START clause is somewhere between the FROM and the WHERE clause in SQL.
     * @author 裴东辉
     * @since 2014-8-6  上午11:08:44
     */
    public void findUserByName(String name){
        GraphDatabaseService graphDb = new GraphDatabaseFactory().newEmbeddedDatabase( "db/user.db" );
        try(Transaction tx = graphDb.beginTx()){ 
            ExecutionEngine engine = new ExecutionEngine(graphDb);  
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("name",name);
            ExecutionResult result = engine.execute( "start user=node:usersIndex(name = {name} ) return user",params);  
            Iterator<Node> nodeIta=result.columnAs("user");
            Node node=null;
            while(nodeIta.hasNext()){
                node=nodeIta.next();
                infoln(node+"-"+node.getProperty(USERPK));
            }
            tx.success();
        }finally{
            graphDb.shutdown();
        }
    }
    
    /**
     * 1、select t_email.*
            from t_user join t_email on t_user.id = t_email.user_id
            where t_user.name = ‘admin‘
        2、start user=node:usersIndex(name = {name})
             match user-[:EMAIL]->email
             return email
        3、Unlike SQL which operates on sets, Cypher predominantly works on sub-graphs. 
            The relational equivalent is the current set of tuples being evaluated during a SELECT query.
        4、The shape of the sub-graph is specified in the MATCH clause. 
            The MATCH clause is analogous to the JOIN in SQL. 
            A normal a→b relationship is an inner join between nodes a and b 
            both sides have to have at least one match, or nothing is returned.
        5、We’ll start with a simple example, 
            where we find all email addresses that are connected to the person “admin”. 
            This is an ordinary one-to-many relationship.
        6、There is no join table here, but if one is necessary the next example will show how to do that, 
            writing the pattern relationship like so: -[r:belongs_to]-> will introduce (the equivalent of) join table available asthe variable r. 
            In reality this is a named relationship in Cypher, so we’re saying “join Person to Group viabelongs_to.” 
            To illustrate this, consider this image, comparing the SQL model and Neo4j/Cypher.
     * @author 裴东辉
     * @since 2014-8-6  上午11:40:10
     */
    public void findEmailByUserName(String name){
        GraphDatabaseService graphDb = new GraphDatabaseFactory().newEmbeddedDatabase( "db/user.db" );
        try(Transaction tx = graphDb.beginTx()){ 
            ExecutionEngine engine = new ExecutionEngine(graphDb);  
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("name", name);
            
            ExecutionResult result = engine.execute( "start user=node:usersIndex(name = {name})  match user-[:EMAIL]->email  return email",params);  
            Iterator<Node> nodeIta=result.columnAs("email");
            Node node=null;
            while(nodeIta.hasNext()){
                node=nodeIta.next();
                infoln(node+"-"+node.getProperty(EMAILPK));
            }
            tx.success();
        }finally{
            graphDb.shutdown();
        }
    }
    
    
    
    public void info(Object j){System.out.print(j.toString());}
    public void infoln(Object j){System.out.println(j.toString());}
    /**
     * @author 裴东辉
     * @since 2014-8-6  上午10:42:54
     * @param args
     */
    public static void main(String[] args) {
        new Neosql().initdata();
        new Neosql().findUserByName("user");
        new Neosql().findEmailByUserName("admin");
    }
    
}