Aerospike JDBC driver: SQL DML compliance
Tags: aerospike, java, sql, jdbc
Published: 2020-05-27 16:35:03

#Introduction
My previous post explained [SQL DDL commands](https://dev.to/alexradzin/aerospike-jdbc-driver-sql-ddl-compliance-23bc) 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:

```sql
insert into people (PK, first_name, last_name, year_of_birth) values (1, 'John', 'Lennon', 1940)
```

or several rows:

```sql
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:

```sql
select first_name, last_name from people
select * from people
```

Data can be filtered using `WHERE` statement:

```sql
select * from people where name = 'John'
```

Unlike [AQL](https://www.aerospike.com/docs/tools/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.

```sql
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](https://github.com/alexradzin/aerospike-jdbc-driver/).

#What's next
Next article of this series will explain how to use built-in functions.

#Previous
Aerospike JDBC driver: [SQL DDL compliance](https://dev.to/alexradzin/aerospike-jdbc-driver-sql-ddl-compliance-23bc)