Introduction
My previous post explained SQL DDL commands supported by the Aerospike JDBC driver. This post will discuss the DML commands.
DML - data manipulation language
Data manipulation language consists of the following commands:
INSERT
SELECT
UPDATE
DELETE
Aerospike JDBC driver treats Aerospoike sets as tables, and bins as columns. However Aerospike is not relational database. It treats key and value differently. Key is not a regular column. Aerospike JDBC driver uses special identifier PK
to refer the primary key.
INSERT
Insert for Aerospike is more than insert. Since Aerospike is schemaless, schema is actually defined by the data. Table (set) is created when data is written there. The actual data "defines" the table schema, i.e. types and name of table columns (or set bins using Aerospike terms.)
Insert syntax is not differ from syntax of regular SQL. You can either insert one row:
insert into people (PK, first_name, last_name, year_of_birth) values (1, 'John', 'Lennon', 1940)
or several rows:
insert into people (PK, first_name, last_name, year_of_birth)
values
(1, 1, 'John', 'Lennon', 1940),
(2, 'Paul', 'McCartney', 1942)
Important: primary key (PK) is required in INSERT
statement.
SELECT
Select statement is used in order to retrieve data from the table. Either explicit definition of columns to be retrieved or * are supported:
select first_name, last_name from people
select * from people
Data can be filtered using WHERE
statement:
select * from people where name = 'John'
Unlike AQL where clause here can use expressions, calculations, functions, parentheses and refer PK
or any bin name (column) either indexed or not. Logical operations <, <. >=, <=, =, !=, IN operator and LIKE statement for textual fields are supported as well. BETWEEN
statement is supported for numeric values only.
select * from people where name in ('John', 'Bill')
select * from people where PK in (1, 2, 3)
select * from people where PK BETWEEN (1, 42)
select * from people where id + 5 < 10
Rows returned by select
can be sorted using ORDER BY
and sliced using LIMIT
and OFFSET
. Inner SELECT
statements are supported.
JOIN
Select
can be used with join
. Inner and left joins are supported. The join expression can use equality expression on any column. PK
can be used for the right table. Using PK
on the left table is possible if PK
is stored in the DB (that does not happen by default). One of the next posts will be dedicated to details of using PK
in queries.
UPDATE
Update
and insert
in AQL are similar. Both commands add new record if specified PK
does not exist and update record if PK
exists. Update
implemented by Aersoplike SQL driver behave like the standard SQL update. It updates records found by criteria specified in WHERE
clause. WHERE
expression can be complex (see SELECT
).
DELETE
Unlike DELETE
implemented by AQL that can delete entries only identified by PK
this delete
works with regular where
clause (see update
).
Project home
The project is available in GitHub.
What's next
Next article of this series will explain how to use built-in functions.
Previous
Aerospike JDBC driver: SQL DDL compliance
Top comments (0)