We are happy to announce that lucee can handle Queries in different way. This is simplest & easiest way to handle the Queries by lucee server.
QueryBuilder
Create object to Query Builder Component which is used to build the query in different way.
qb = new QueryBuilder();
DataSource Initialize
We need a datasource to execute the query. Here we can initialize the datasource by two methods
Method:1 datasource Initialize with constructor
qb = new QueryBuilder(“dsnName”);
Method:2 datasource Initialize with setDataSource()
qb.setDataSource (“dnsName”);
Get DataSource Info
You can get the datasource info from the getDataSourceInfo().
qb.getDatasourceInfo(“dataSourceName”);
It return’s details of datasource in structure
Select
Select the column from the table.
Method 1:
passing column name as string
qb.select(“column”)
Method 2:
passing column names as array
qb.select([“column1”, “column2”]);
If you want to overwrite the selected column need to use like below
qb.select([“column1”, “column2”], true);
Examples
Example
//1st method select the single column passing value as string
qb = new QueryBuilder();
qb.setDatasource("TestDSN");
qb.select("cat_Name");
qb.from("categories");
writeDump(var = qb.execute(), label="passing Column name as string");
qb = new QueryBuilder();
qb.setDatasource("TestDSN");
qb.select("cat_Name, cat_ID");
qb.from("categories");
writeDump(var = qb.execute(), label="passing Column name as list");
//2nd method select the multiple column with array of data
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
writeDump(var = qb.execute(), label="passing Column name as array");
//overwrites selected data
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.select("cat_Name", true);
qb.from("categories");
writeDump(var = qb.execute(), label="Select statement with overwrite eq true");
// To reterive all data
without select statement it calls all the data in table
qb = new QueryBuilder("TestDSN");
qb.from("categories");
writeDump(var = qb.execute(), label="Select statement with overwrite eq true");
qb = new QueryBuilder("TestDSN");
qb.select("*");
qb.from("categories");
writeDump(var = qb.execute(), label="Select statement with overwrite eq true");
From
Used to choose the table which you want to perform the operation. Without from statement it throw’s error.
Method 1:
passing table name as string
qb.from(“table”);
Method 2:
Passing table name as array
qb.from([“table1”,“table2”, “table3”]);
IF you want to overwrite the table name it replace already defined table name
qb.from(“tableName”, true);
Examples
// passing table name as string
qb = new QueryBuilder("TestDSN"); qb.select("cat_Name"); qb.from("categories,product"); writeDump(var = qb.execute(), label="using from");// passing table name as array format
qb = new QueryBuilder("TestDSN"); qb.from(["categories","product", "item"]); writeDump(var = qb.execute(), label="passing table name as array");// setting overwrite true in from statement
qb = new QueryBuilder(“TestDSN”);
qb.from([“categories”,“product”, “item”]);
qb.from(“categories”, true)
writeDump(var = qb.execute(), label=“overwrite true in from statement”);
Where Clause
Here we can use where condition opertions
qb.where(QB::eq(“columnName”, “value”));
QB is component contains static function which is used to perform operations like eq,neq,gt,lt,in,notin,and,or,like,between
Use like below
Syntax
qb.where(QB::eq(“columnName”, “value”));
qb.where(QB::neq(“columnName”, “value”));
qb.where(QB::gt(“columnName”, “value”));
qb.where(QB::lt(“columnName”, “value”));
qb.where(QB::in(“columnName”, “listValue”));
qb.where(QB::notin(“columnName”, “listValue”));
qb.where(QB:: like(“columnName”, “SearchingText”));
qb.where(QB:: between(“columnName”, “values as list”));
qb.where(QB::and(QB::neq(“columnName”, “value”), QB::gt(“columnName”, “value”), QB::lt(“columnName”, “value”)));
qb.where(QB::or(QB::eq(“columnName”, “value”), QB::eq(“columnName”, “value”)));
Examples
Eq opertaion
qb = new QueryBuilder();
qb.setDatasource("TestDSN");
qb.select("cat_Name");
qb.from("categories");
qb.where(QB::eq("Cat_id", "1"));
writeDump(var = qb.execute(), label="Eqaual operation in QueryBuilder like QB::eq()");
// not equal
qb = new QueryBuilder();
qb.setDatasource("TestDSN");
qb.select("cat_Name");
qb.from("categories");
qb.where(QB::neq("Cat_id", "1"));
writeDump(var = qb.execute(), label="Not Eqaual operation in QueryBuilder like QB::neq()");
//greater than
qb = new QueryBuilder();
qb.setDatasource("TestDSN");
qb.select("cat_Name");
qb.from("categories");
qb.where(QB::gt("Cat_id", "1"));
writeDump(var = qb.execute(), label="Greater than operation in QueryBuilder like QB::gt()");
// less than
qb = new QueryBuilder();
qb.setDatasource("TestDSN");
qb.select("cat_Name");
qb.from("categories");
qb.where(QB::lt("Cat_id", "4"));
writeDump(var = qb.execute(), label="Less than operation in QueryBuilder like QB::lt()");
// IN
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB::in("Cat_id", "1,2,3"));
writeDump(var = qb.execute(), label="Less than operation in QueryBuilder like QB::in()");
//not IN
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB:: notin("Cat_id", "2,4"));
writeDump(var = qb.execute(), label="Less than operation in QueryBuilder like QB::notin()");
//not
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB:: not("Cat_id", "1"));
writeDump(var = qb.execute(), label="Less than operation in QueryBuilder like QB::not()");
//Like
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB:: Like("cat_Name", "%ver"));
writeDump(var = qb.execute(), label="Like operation in QueryBuilder like QB:: Like ()");
//Between
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB:: Between("Cat_id", "1,3"));
writeDump(var = qb.execute(), label="Between operation in QueryBuilder like QB:: Between ()");
//And Opertion
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB::and(QB::neq("cat_Name", "freeware"), QB::gt("Cat_id", "2"), QB::lt("Cat_id", "4")));
writeDump(var = qb.execute(), label="And operation in QueryBuilder like QB::and(QB::neq(), QB::gt(),..)");
// OR Operation
qb = new QueryBuilder("TestDSN");
qb.select(["cat_Name", "Cat_id"]);
qb.from("categories");
qb.where(QB::or(QB::eq("cat_Name", "freeware"), QB::eq("Cat_id", "3")));
writeDump(var = qb.execute(), label="OR operation in QueryBuilder like QB::or(QB::neq(), QB::gt(),..)");
Joins
We can joins the table
Syntax
//inner join
From("table1")
qb.join("table2", QB:: on("table1.columnName", "table2.columnName"));
From("table1")
qb.leftJoin("table2", QB:: on("table1.columnName", "table2.columnName"));
From("table1")
qb.rightJoin("table2", QB:: on("table1.columnName", "table2.columnName"));
on method is called from QB component used to join the tables based on their column values
QB:: on(“table1.columnName”, “table2.columnName”);
Examples
// inner join
qb = new QueryBuilder("TestDSN");
qb.from("categories");
qb.join("product", QB:: on("categories.Cat_id", "product.pro_id"));
qb.join("item", QB:: on("product.pro_id", "item.itm_id"));
writeDump(var = qb.execute(), label="join operation in QueryBuilder");
//Left join
qb = new QueryBuilder("TestDSN");
qb.from("product");
qb.Leftjoin("categories", QB:: on("categories.Cat_id", "product.pro_id"));
writeDump(var = qb.execute(), label="Leftjoin operation in QueryBuilder");
//Right join
qb = new QueryBuilder("TestDSN");
qb.from("categories");
qb.rightJoin("product", QB:: on("categories.Cat_id", "product.pro_id"));
writeDump(var = qb.execute(), label="Rightjoin operation in QueryBuilder");
Is Just beginning later will support for create, insert, delete, drop operations.
Any suggestion should be greatly appreciated.
Thanks
cfmitrah
