Ignite内存数据库与sql支持

Ignite采用h2作为内存数据库，支持h2的一切sql语法。如果是本地缓存或者复制缓存，sql执行直接在本地h2数据库中执行，如果是分区缓存，ignite则会分解sql到多个h2数据库执行后再汇总。

通过@QuerySqlField注解可以轻松的把对象属性映射到表字段，下述代码实现了缓存对象与h2表的映射关系：

1、 class

package com.coshaho.learn.ignite.sql;

import org.apache.ignite.cache.query.annotations.QuerySqlField;

public class Class 
{
    @QuerySqlField(index = true)
    private int id;
    
    @QuerySqlField
    private String name;
    
    private int level;

    public Class(int id, String name)
    {
        this.id = id;
        this.name = name;
    }
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
    public int getLevel() {
        return level;
    }
    public void setLevel(int level) {
        this.level = level;
    }
    @Override
    public String toString() {
        return "Class [id=" + id + ", name=" + name + ", level=" + level + "]";
    }
}

2、 Student

package com.coshaho.learn.ignite.sql;

import org.apache.ignite.cache.query.annotations.QuerySqlField;

public class Student 
{
    @QuerySqlField
    private int classId;
    
    @QuerySqlField
    private String name;
    
    @QuerySqlField
    private int age;
    
    public Student(int classId, String name, int age)
    {
        this.classId = classId;
        this.name = name;
        this.age = age;
    }

    public int getClassId() {
        return classId;
    }

    public void setClassId(int classId) {
        this.classId = classId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student [classId=" + classId + ", name=" + name + ", age="
                + age + "]";
    }
}

要把缓存对象存储到h2表中，还必须设置缓存的IndexedTypes，IndexedTypes必须成对出现，并且和缓存键值对类型相同。

Ignite还支持不同缓存之间的关联查询，查询时把缓存名当做h2 schema就行，缓存关联查询时需要把缓存名设置为大写（应该是ignite bug）。

测试代码如下：

package com.coshaho.learn.ignite.sql;

import java.util.List;

import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.Ignition;
import org.apache.ignite.cache.CacheMode;
import org.apache.ignite.cache.query.QueryCursor;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.configuration.CacheConfiguration;

/**
 * 
 * IgniteDB.java Create on 2017年5月25日 下午11:00:35    
 *    
 * 类功能说明:   ignite内存数据库
 *
 * Copyright: Copyright(c) 2013 
 * Company: COSHAHO
 * @Version 1.0
 * @Author coshaho
 */
public class IgniteDB 
{
    public static void main(String[] args)
    {
        Ignite ignite = Ignition.start();
        CacheConfiguration<Integer, Class> classCfg = new CacheConfiguration<Integer, Class>();
        classCfg.setName("CLA");
        classCfg.setCacheMode(CacheMode.PARTITIONED);
        classCfg.setIndexedTypes(Integer.class, Class.class);
        IgniteCache<Integer, Class> classCache = ignite.getOrCreateCache(classCfg);
        classCache.put(1, new Class(1, "五年级一班"));
        classCache.put(2, new Class(2, "五年级二班"));
        
        CacheConfiguration<Integer, Student> stuCfg = new CacheConfiguration<Integer, Student>();
        stuCfg.setName("STU");
        stuCfg.setCacheMode(CacheMode.PARTITIONED);
        stuCfg.setIndexedTypes(Integer.class, Student.class);
        IgniteCache<Integer, Student> stuCache = ignite.getOrCreateCache(stuCfg);
        stuCache.put(1, new Student(1, "张三", 10));
        stuCache.put(2, new Student(1, "李四", 11));
        stuCache.put(3, new Student(2, "王五", 11));
        stuCache.put(4, new Student(2, "胜七", 10));
        
        SqlFieldsQuery sql = new SqlFieldsQuery(
                  "select concat(stu.classId, '----', stu.name) as stuinfo"
                  + " "
                  + "from Student as stu "
                  + "");
        
        QueryCursor<List<?>> cursor = stuCache.query(sql);
        for (List<?> row : cursor)
        {
            System.out.println("学生信息：" + row.get(0));
        }
        
        SqlFieldsQuery sql1 = new SqlFieldsQuery(
                  "select concat(cla.id, '----', cla.name) as clainfo"
                  + ", concat(stu.name, '----', stu.age) as stuinfo "
                  + "from Class as cla, STU.Student as stu "
                  + "where cla.id = stu.classId");
        
        QueryCursor<List<?>> cursor1 = classCache.query(sql1);
        for (List<?> row : cursor1)
        {
            System.out.println("班级信息：" + row.get(0) + ", 学生信息：" + row.get(1));
        }
    }
}

测试结果：

[23:02:08] Ignite node started OK (id=6073a91e)
[23:02:08] Topology snapshot [ver=1, servers=1, clients=0, CPUs=4, heap=0.88GB]
学生信息：1----张三
学生信息：1----李四
学生信息：2----王五
学生信息：2----胜七
班级信息：1----五年级一班, 学生信息：张三----10
班级信息：1----五年级一班, 学生信息：李四----11
班级信息：2----五年级二班, 学生信息：王五----11
班级信息：2----五年级二班, 学生信息：胜七----10

可以看出来，ignite对sql的支持非常强大，对于需要频繁进行数据库操作的场景，ignite缓存相当有效。