Hazelcast SQL SqlService Arayüzü - Hazelcast SQL Dialect

Giriş

Şu satırı dahil ederiz

import com.hazelcast.sql.SqlService;

Maven

Şu satırı dahil ederiz. Scope neden runtime bilmiyorum

<dependency>
  <groupId>com.hazelcast</groupId>
  <artifactId>hazelcast-sql</artifactId>
  <version>${hazelcast.version}</version>
  <scope>runtime</scope>
</dependency>

execute metodu

İmzası şöyle. SqlResult nesnesi döner

SqlResult execute(String sql, Object... arguments)
long executeUpdate(String sql, Object... arguments)
SqlResult execute(SqlStatement statement)

execute metodu - SqlStatement

İmzası şöyle. SqlResult nesnesi döner

String sql = ...;
List<Object> parameters = ...;
SqlExpectedResultType expectedResult = SqlExpectedResultType.ROWS;

SqlStatement query = new SqlStatement(sql)
  .setParameters(parameters)
  .setExpectedResultType(expectedResult)
  .setSchema(schema);

query.setTimeoutMillis(SECONDS.toMillis(queryTimeout));
query.setCursorBufferSize(fetchSize);

try {
  SqlResult sqlResult = client.execute(query);
  if (sqlResult.isRowSet()) {
    ...
  } else {
    int updateCount = sqlResult.updateCount();
    ...
  }
} catch (HazelcastSqlException e) {
  ...
}

Örnek

Şöyle yaparız

SQL Service Ne İşe Yarar?

Açıklaması şöyle

You can use SQL to query data in maps, Kafka topics, or a variety of file systems.

SQL Service Nasıl Bağlanırız?

SQL Service'e bağlanmak için şunlar kullanılabilir. Java client denilen şey SqlService sınıfı

You can connect to the SQL service of a Hazelcast member using one of the following options:
- SQL shell or Management Center: For fast prototyping.
- JDBC driver or the Java client: For Java applications.
- Node.js client.
 -Python client.
- Go client.
- C# client.

Açıklaması şöyle

The SQL service exposes IMap objects as tables in the predefined partitioned schema using exact names. This schema is in the SQL service search path so that you can access the IMap objects with or without the schema name.

Schema and table names are case-sensitive; you can access the employee map, for example, as employee or partitioned.employee, but not as Employee:

SELECT * FROM employee

SELECT * FROM partitioned.employee

Genel Kullanım

Önce bir tablo yaratmak gerekir. Şöyle yaparız

CREATE MAPPING cities (
__key INT,
countries VARCHAR,
cities VARCHAR)
TYPE IMap
OPTIONS('keyFormat'='int', 'valueFormat'='json-flat');

Daha sonra şöyle yaparız

INSERT INTO cities VALUES
(1, 'United Kingdom','London'),
(2, 'United Kingdom','Manchester'),
(3, 'United States', 'New York'),
(4, 'United States', 'Los Angeles'),
(5, 'Turkey', 'Ankara'),
(6, 'Turkey', 'Istanbul'),
(7, 'Brazil', 'Sao Paulo'),
(8, 'Brazil', 'Rio de Janeiro');

Örnek - CREATE MAPPING

Şöyle yaparız. Burada kodla isimleri client ve client2system olan iki tane map yaratılıyor

@Bean
public SqlService sqlService(HazelcastInstance hazelcastInstance) {
 SqlService sqlService = hazelcastInstance.getSql();
 Map<String, Class<?>> configMap = Map.of(
   "client", Client.class,
   "client2system", Client2System.class,
 );
 configMap.forEach((name, type) -> {
   sqlService.execute(String.format("CREATE MAPPING \"%s\" TYPE IMap 
     OPTIONS ('keyFormat' = 'java', 
              'keyJavaClass' = 'java.lang.Long', 
              'valueFormat' =   'java', 
              'valueJavaClass' = '%s' )", 
     name, type.getName()));
  });
  return sqlService;
}

Örnek - SELECT

Şöyle yaparız

String SQL = "SELECT clientId FROM client2system " +
            "WHERE systemId = ? AND clientCode = ?";

public Client findByClientCode(long systemId, String clientCode) {
  try (SqlResult result = sqlService.execute(SQL, systemId, clientCode)) {
    Iterator<SqlRow> iterator = result.iterator();
    if (!iterator.hasNext()) {
      return null;
    }
    Long clientId = iterator.next().getObject(0);
    ...
  }
}

Index

Index tipleri şöyle

SORTED (default): For ranged queries such as listing all employees that are between 40 and 60.

HASH: For unordered queries such as getting the IDs of employees that are named John.

BITMAP: For querying fields that contain few distinct values such as boolean fields. See Bitmap Indexes.

Örnek - XML

Şöyle yaparız

<hazelcast>
  ...
  <map name="employees">
    <indexes>
      <index type="HASH">
        <attributes>
          <attribute>name</attribute>
        </attributes>
      </index>
      <index>
        <attributes>
          <attribute>age</attribute>
        </attributes>
      </index>
    </indexes>
  </map>
  ...
</hazelcast>

Aynı şeyi SQL veya Java ile şöyle yaparız

# SQL
CREATE INDEX IF NOT EXISTS name
ON employees (this.name)
TYPE HASH;

CREATE INDEX IF NOT EXISTS name
ON employees (name)
TYPE HASH;

# Java
map.addIndex(new IndexConfig(IndexType.HASH, "this.name"));
map.addIndex(new IndexConfig(IndexType.HASH, "name"));

JSON Functions

JSON Functions yazısına taşıdım

Table-Valued Functions

Table-Valued Functions yazısına taşıdım