docs/_docs/sql/java.adoc

// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements.  See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License.  You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
= Java SQL API

In your Java projects, you can use the Java SQL API to execute SQL statements and getting results. All operations are executed as a part of sql session. You can create a session with default parameters by using a `sql.createSession()` method, or use an `sql.sessionBuilder` to configure it for your environment. Sessions are usually long-lived objects that can be used in multiple threads and may hold data server-side. Session object is light-weight, and Ignite manages resources automatically in failover scenarios (for example, for disconnects). You have to close the session manually by using the `close()` method when the session is no longer needed to release server resources. Here is how you usually set up a session:

[source, java]
----
Session ses = sql.createSession();
Session ses = sql.sessionBuilder().defaultSchema("PUBLIC").build();
----

The `execute` method accepts a query String, or a Statement object, that can be created by using `sql.createStatement()` and `sql.statementBuilder()` methods. Statements are light-weight objects and can be reused in multiple threads or sessions. Setting set by using `statementBuilder` override session settings for this statement. Here is how you usually set up a statement:

[source, java]
----
Statement stmt = sql.createStatement(sqlQueryString));
Statement stmt = sql.statementBuilder().query(sqlQueryString)).build();
----

== Creating Tables

Here is an example of how you can create a new table on a cluster:

[source, java]
----
try (ResultSet rs = ses.execute(null,
        "CREATE TABLE SYNCH(ID INT PRIMARY KEY, VAL0 INT)")
) {
    // no-op
}
----

NOTE: ResultSet is closable, but it is safe to skip `close()` method for DDL and DML queries, as they do not keep server cursor open.


== Filling Tables

With Apache Ignite 3, you can fill the table by adding rows one by one, or in a batch. In both cases, you create an `INSERT` statement, and then exeсute it:

[source, java]
----
long rowsAdded = Arrays.stream(ses.executeBatch(null,
    "INSERT INTO ACCOUNTS (ACCOUNT_ID, CITY_ID, FIRST_NAME, LAST_NAME, BALANCE) values (?, ?, ?, ?, ?)",
    BatchedArguments.of(1, 1, "John", "Doe", 1000.0d)
        .add(2, 1, "Jane", "Roe", 2000.0d)
        .add(3, 1, "Mary", "Major", 1500.0d)
        .add(4, 1, "Richard", "Miles", 1450.0d)))
        .asLongStream().sum();
----

== Getting Data From Tables

To get data from a table, execute the `SELECT` statement to get a set of results. SqlRow can provide access to column values by column name or column index. You can then iterate through results to get data:

[source, java]
----
try (ResultSet rs = ses.execute(null,
        "SELECT a.FIRST_NAME, a.LAST_NAME FROM ACCOUNTS a")) {
    while (rs.hasNext()) {
        SqlRow row = rs.next();

        result += row.stringValue(1) + ", " + row.stringValue("LAST_NAME") + "\n";
    }
}
----

NOTE: ResultSet may hold server-side cursor open due to lazy query execution. It must be closed manually, or by using the `try-with-resources` statement.