import ballerina/io;
type Person {
int id;
int age = -1;
float salary;
string name;
boolean married;
};
type Order {
int personId;
int orderId;
string items;
float amount;
};
type SummedOrder {
int personId;
float amount;
};
type OrderDetails {
int orderId;
string personName;
string items;
float amount;
};type PersonPublicProfile {
string knownName;
int age = -1;
};
public function main (string[] args) {
table<Person> personTable = table{};
table<Order> orderTable = table{};
Person p1 = {id:1, age:25, salary:1000.50, name:"jane", married:true};
Person p2 = {id:2, age:26, salary:1050.50, name:"kane", married:false};
Person p3 = {id:3, age:27, salary:1200.50, name:"jack", married:true};
Person p4 = {id:4, age:28, salary:1100.50, name:"alex", married:false};
Order o1 = {personId:1, orderId:1234, items:"pen, book, eraser", amount:34.75};
Order o2 = {personId:1, orderId:2314, items:"dhal, rice, carrot", amount:14.75};
Order o3 = {personId:2, orderId:5643, items:"Macbook Pro", amount:2334.75};
Order o4 = {personId:3, orderId:8765, items:"Tshirt", amount:20.75};
personTable.add(p1);
personTable.add(p2);
personTable.add(p3);
personTable.add(p4); io:print("The personTable: ");
io:println(personTable);
orderTable.add(o1);
orderTable.add(o2);
orderTable.add(o3);
orderTable.add(o4); io:print("The orderTable: ");
io:println(orderTable);
table<Person> personTableCopy = from personTable
select *;
io:println("table<Person> personTableCopy = from personTable select *;");
io:print("personTableCopy: ");
io:println(personTableCopy);
table<Person> orderedPersonTableCopy = from personTable
select * order by salary;
io:println("table<Person> orderedPersonTableCopy = from personTable select * order by salary;");
io:print("orderedPersonTableCopy: ");
io:println(orderedPersonTableCopy);
table<Person> personTableCopyWithFilter = from personTable where name == "jane"
select *;
io:println("table<Person> personTableCopyWithFilter = from personTable where name == 'jane' select *;");
io:print("personTableCopyWithFilter: ");
io:println(personTableCopyWithFilter);
table<PersonPublicProfile > childTable = from personTable
select name as knownName, age;
io:println("table<PersonPublicProfile > childTable = from personTable select name as knownName, age;");
io:print("childTable: ");
io:println(childTable);
table<SummedOrder> summedOrderTable = from orderTable
select personId, sum(amount) group by personId;
io:println("table<SummedOrder> summedOrderTable = from orderTable select personId, sum(amount) group by personId;");
io:print("summedOrderTable: ");
io:println(summedOrderTable);
table<OrderDetails> orderDetailsTable = from personTable as tempPersonTable
join orderTable as tempOrderTable on tempPersonTable.id == tempOrderTable.personId
select tempOrderTable.orderId as orderId, tempPersonTable.name as personName, tempOrderTable.items as
items, tempOrderTable.amount as amount;
io:println("table<OrderDetails> orderDetailsTable = from personTable as tempPersonTable
join orderTable as tempOrderTable on tempPersonTable.id == tempOrderTable.personId
select tempOrderTable.orderId as orderId, tempPersonTable.name as personName, tempOrderTable.items as
items, tempOrderTable.amount as amount;");
io:print("orderDetailsTable: ");
io:println(orderDetailsTable);
table<OrderDetails> orderDetailsWithFilter = from personTable where name != "jane" as tempPersonTable
join orderTable where personId != 3 as tempOrderTable on tempPersonTable.id == tempOrderTable.personId
select tempOrderTable.orderId as orderId, tempPersonTable.name as personName, tempOrderTable.items as items,
tempOrderTable.amount as amount;
io:println("table<OrderDetails> orderDetailsWithFilter = from personTable where name != 'jane' as tempPersonTable
join orderTable where personId != 3 as tempOrderTable on tempPersonTable.id == tempOrderTable.personId
select tempOrderTable.orderId as orderId, tempPersonTable.name as personName, tempOrderTable.items as items,
tempOrderTable.amount as amount;");
io:print("orderDetailsWithFilter: ");
io:println(orderDetailsWithFilter);
}
SQL queries on tablesBallerina supports querying tables using syntax similar to SQL. These query syntax can only be used to query a table. Modification of an existing table is not supported. When an existing table is queried using Ballerina SQL, a new in-memory table is created with the queried data and that table is returned. |
|
import ballerina/io;
|
|
type Person {
int id;
int age = -1;
float salary;
string name;
boolean married;
};
|
struct to represent a person in the example. |
type Order {
int personId;
int orderId;
string items;
float amount;
};
|
struct to represent an order in this example. |
type SummedOrder {
int personId;
float amount;
};
|
struct to represent summed order details. |
type OrderDetails {
int orderId;
string personName;
string items;
float amount;
};
|
struct to represent order details (this is derived by joining person details and order details |
type PersonPublicProfile {
string knownName;
int age = -1;
};
|
|
public function main (string[] args) {
|
main function |
table<Person> personTable = table{};
|
The in memory table which is constrained by struct “Person” |
table<Order> orderTable = table{};
|
The in memory table which is constrained by struct “Order” |
Person p1 = {id:1, age:25, salary:1000.50, name:"jane", married:true};
Person p2 = {id:2, age:26, salary:1050.50, name:"kane", married:false};
Person p3 = {id:3, age:27, salary:1200.50, name:"jack", married:true};
Person p4 = {id:4, age:28, salary:1100.50, name:"alex", married:false};
|
few sample values which represents different persons |
Order o1 = {personId:1, orderId:1234, items:"pen, book, eraser", amount:34.75};
Order o2 = {personId:1, orderId:2314, items:"dhal, rice, carrot", amount:14.75};
Order o3 = {personId:2, orderId:5643, items:"Macbook Pro", amount:2334.75};
Order o4 = {personId:3, orderId:8765, items:"Tshirt", amount:20.75};
|
few sample values which represents orders made by above persons |
personTable.add(p1);
personTable.add(p2);
personTable.add(p3);
personTable.add(p4);
|
Insert the person struct objects and populate the table. |
io:print("The personTable: ");
io:println(personTable);
|
|
orderTable.add(o1);
orderTable.add(o2);
orderTable.add(o3);
orderTable.add(o4);
|
Insert the order struct objects and populate the table |
io:print("The orderTable: ");
io:println(orderTable);
|
|
Querying a table will always return a new in memory table. |
|
table<Person> personTableCopy = from personTable
select *;
io:println("table<Person> personTableCopy = from personTable select *;");
io:print("personTableCopy: ");
io:println(personTableCopy);
|
|
table<Person> orderedPersonTableCopy = from personTable
select * order by salary;
io:println("table<Person> orderedPersonTableCopy = from personTable select * order by salary;");
io:print("orderedPersonTableCopy: ");
io:println(orderedPersonTableCopy);
|
|
table<Person> personTableCopyWithFilter = from personTable where name == "jane"
select *;
io:println("table<Person> personTableCopyWithFilter = from personTable where name == 'jane' select *;");
io:print("personTableCopyWithFilter: ");
io:println(personTableCopyWithFilter);
|
|
table<PersonPublicProfile > childTable = from personTable
select name as knownName, age;
io:println("table<PersonPublicProfile > childTable = from personTable select name as knownName, age;");
io:print("childTable: ");
io:println(childTable);
|
|
table<SummedOrder> summedOrderTable = from orderTable
select personId, sum(amount) group by personId;
io:println("table<SummedOrder> summedOrderTable = from orderTable select personId, sum(amount) group by personId;");
io:print("summedOrderTable: ");
io:println(summedOrderTable);
|
|
table<OrderDetails> orderDetailsTable = from personTable as tempPersonTable
join orderTable as tempOrderTable on tempPersonTable.id == tempOrderTable.personId
select tempOrderTable.orderId as orderId, tempPersonTable.name as personName, tempOrderTable.items as
items, tempOrderTable.amount as amount;
io:println("table<OrderDetails> orderDetailsTable = from personTable as tempPersonTable
join orderTable as tempOrderTable on tempPersonTable.id == tempOrderTable.personId
select tempOrderTable.orderId as orderId, tempPersonTable.name as personName, tempOrderTable.items as
items, tempOrderTable.amount as amount;");
io:print("orderDetailsTable: ");
io:println(orderDetailsTable);
|
|
table<OrderDetails> orderDetailsWithFilter = from personTable where name != "jane" as tempPersonTable
join orderTable where personId != 3 as tempOrderTable on tempPersonTable.id == tempOrderTable.personId
select tempOrderTable.orderId as orderId, tempPersonTable.name as personName, tempOrderTable.items as items,
tempOrderTable.amount as amount;
io:println("table<OrderDetails> orderDetailsWithFilter = from personTable where name != 'jane' as tempPersonTable
join orderTable where personId != 3 as tempOrderTable on tempPersonTable.id == tempOrderTable.personId
select tempOrderTable.orderId as orderId, tempPersonTable.name as personName, tempOrderTable.items as items,
tempOrderTable.amount as amount;");
io:print("orderDetailsWithFilter: ");
io:println(orderDetailsWithFilter);
}
|
|
$ ballerina run sql-queries-on-tables.bal
The personTable: {data: [{id:1, age:25, salary:1000.5, name:"jane", married:true}, {id:2, age:26, salary:1050.5,
name:"kane", married:false}, {id:3, age:27, salary:1200.5, name:"jack", married:true}, {id:4, age:28, salary:1100.5,
name:"alex", married:false}]}
|
|
The orderTable: {data: [{personId:1, orderId:1234, items:"pen, book, eraser", amount:34.75}, {personId:1, orderId:2314,
items:"dhal, rice, carrot", amount:14.75}, {personId:2, orderId:5643, items:"Macbook Pro", amount:2334.75}, {personId:3,
orderId:8765, items:"Tshirt", amount:20.75}]}
|
|
table<Person> personTableCopy = from personTable select *;
personTableCopy: {data: [{id:1, age:25, salary:1000.5, name:"jane", married:true}, {id:2, age:26, salary:1050.5,
name:"kane", married:false}, {id:3, age:27, salary:1200.5, name:"jack", married:true}, {id:4, age:28, salary:1100.5,
name:"alex", married:false}]}
|
|
table<Person> orderedPersonTableCopy = from personTable select * order by salary;
orderedPersonTableCopy: {data: [{id:1, age:25, salary:1000.5, name:"jane", married:true}, {id:2, age:26, salary:1050.5,
name:"kane", married:false}, {id:4, age:28, salary:1100.5, name:"alex", married:false}, {id:3, age:27, salary:1200.5,
name:"jack", married:true}]}
|
|
table<Person> personTableCopyWithFilter = from personTable where name == 'jane' select *;
personTableCopyWithFilter: {data: [{id:1, age:25, salary:1000.5, name:"jane", married:true}]}
|
|
table<PersonPublicProfile > childTable = from personTable select name as knownName, age;
childTable: {data: [{knownName:"jane", age:25}, {knownName:"kane", age:26}, {knownName:"jack", age:27},
{knownName:"alex", age:28}]}
|
|
table<SummedOrder> summedOrderTable = from orderTable select personId, sum(amount) group by personId;
summedOrderTable: {data: [{personId:1, amount:49.5}, {personId:2, amount:2334.75}, {personId:3, amount:20.75}]}
|
|
table<OrderDetails> orderDetailsTable = from personTable as tempPersonTable
join orderTable as tempOrderTable on tempPersonTable.id == tempOrderTable.personId
select tempOrderTable.orderId as orderId, tempPersonTable.name as personName, tempOrderTable.items as
items, tempOrderTable.amount as amount;
orderDetailsTable: {data: [{orderId:1234, personName:"jane", items:"pen, book, eraser", amount:34.75}, {orderId:2314,
personName:"jane", items:"dhal, rice, carrot", amount:14.75}, {orderId:5643, personName:"kane", items:"Macbook Pro",
amount:2334.75}, {orderId:8765, personName:"jack", items:"Tshirt", amount:20.75}]}
|
|
table<OrderDetails> orderDetailsWithFilter = from personTable where name != 'jane' as tempPersonTable
join orderTable where personId != 3 as tempOrderTable on tempPersonTable.id == tempOrderTable.personId
select tempOrderTable.orderId as orderId, tempPersonTable.name as personName, tempOrderTable.items as items,
tempOrderTable.amount as amount;
orderDetailsWithFilter: {data: [{orderId:5643, personName:"kane", items:"Macbook Pro", amount:2334.75}]}
|
|