Skip to content

jSQLquery interface

Jump to bottom
Rob Parham edited this page Sep 25, 2017 · 7 revisions

This interface is the heart of jSQL and handles all database operations. There are 6 classes that implement this interface, depending on the query's type property. Query types are "CREATE", "UPDATE", "SELECT", "INSERT", "DROP", and "DELETE".

While all the methods of this interface are available in all of the classes that implement it, some of these methods only apply to certain types and if building a query, these methods should be called in logical order.

For example, in SQL, you would NOT say UPDATE USERS WHERE B = 3 SET C = 4. Likewise, in jSQL you wouldn't say jSQL.update(users).where('B').equals(3).set({C:4}) The correct SQL syntax is UPDATE USERS SET C = 4 WHERE B = 3 and likewise, the correct jSQL syntax is jSQL.update(users).set({C:4}).where('B').equals(3).

jSQLQuery.ifNotExists()

Used for "CREATE" queries to set a flag that will prevent overwriting this table if it already exists.

Returns
Example
jSQL.createTable({myTable: [
    { name: "ID", type: "INT", args: [] }, 
    { name: "Name", type: "VARCHAR", args: [30] }
]}).ifNotExists().execute();

jSQLQuery.ignore()

Used for "INSERT" queries to set a flag that will prevent an error from being thrown on a key violation.

Returns
Example
jSQL.insertInto('myTable').values({ID:0, Name:'Bob'}).ignore().execute();

jSQLQuery.execute(preparedVals)

Used for ALL query types. This function executes the query, setting the result set, if any, and performing the operation on the tables.

Parameters
  • preparedVals: Queries may substitute a question mark for a given value to create a prepared statement, when used, the question marks are replaced with the values provided in this array, in order.
Returns
Example
jSQL.query('UPDATE Users SET Name = ?').execute(['Frank']);

jSQLQuery.fetch(mode)

Used for "SELECT" queries to return the first row in the result set.

Parameters
  • mode: This can be either "ASSOC" or "ARRAY" and defaults to "ASSOC". When "ARRAY" is provided, this function will return a flat array, else it will return the result as an object with column name keys.
Returns
  • An array or object of the first result in the query's result set.
Example
var query = 
    jSQL.select('*')
    .from('Users')
    .where('Name')
    .equals('Frank')
    .execute();
var Frank = query.fetch('ASSOC');

jSQLQuery.fetchAll(mode)

Used for select queries to return the all rows in the result set.

Parameters
  • mode: This can be either "ASSOC" or "ARRAY" and defaults to "ASSOC". When "ARRAY" is provided, this function will return an array of flat array, else it will return the result as an array of objects with column name keys.
Returns
  • An array containing all records in the result set.
Example
var sql = 'SELECT `Name`, `Age` FROM `Users` WHERE `Age` > 32';
var query = jSQL.query(sql);
query.execute();
var oldPeople = query.fetchAll('ASSOC');

jSQLQuery.values(data)

Used for "INSERT" queries to set the values to be inserted into the newly created row.

Parameters
  • data: May be either a flat array of values or an object with column names as keys.
Returns
Example
var q = jSQL.insertInto('Users').values({Name: "?", Age: "?"});
q.execute(['Susan', 37]);

jSQLQuery.set(data)

Used for "UPDATE" queries to set the columns and values to be altered.

Parameters
  • data: An object with column names as keys.
Returns
Example
var q = jSQL.insertInto('Users').values({Name: "?", Age: "?"});
q.execute(['Susan', 37]);

jSQLQuery.where(column)

Used for "DELETE", "SELECT", and "UPDATE" queries to refine the result set, or the set of results to be altered or deleted.

Parameters
  • column: The first column name to add a condition to.
Returns
Example
jSQL.deleteFrom('Users').where('Age').greaterThan(37).execute();

jSQLQuery.limit(limit, offset)

Used for "DELETE", "SELECT", and "UPDATE" queries to limit the result set, or the set of results to be altered or deleted.

Parameters
  • limit: The maximum number of results allowed in the set.
  • offset: The index at which to start the result set.
Returns
Example
jSQL.deleteFrom('Users').where('Age').greaterThan(37).limit(2, 7).execute();

jSQLQuery.orderBy(column)

Used for "DELETE", "SELECT", and "UPDATE" queries to sort the result set, or the set of results to be altered or deleted.

Parameters
  • column: The column or columns to order results by.
Returns
Example
jSQL.deleteFrom('Users').where('Age').greaterThan(37).orderBy('Age').asc().limit(2, 7).execute();

jSQLQuery.asc()

Used for "DELETE", "SELECT", and "UPDATE" queries to sort the result set, or the set of results to be altered or deleted, in an ASCENDING order.

Returns
Example
jSQL.deleteFrom('Users').where('Age').greaterThan(37).orderBy('Age').asc().limit(2, 7).execute();

jSQLQuery.desc()

Used for "DELETE", "SELECT", and "UPDATE" queries to sort the result set, or the set of results to be altered or deleted, in a DESCENDING order.

Returns
Example
jSQL.deleteFrom('Users').where('Age').greaterThan(37).orderBy('Age').desc().limit(2, 7).execute();

jSQLQuery.distinct()

Used for "DELETE", "SELECT", and "UPDATE" queries to ensure all rows in the result set are unique.

Returns
Example
jSQL.select("*").distinct().from("myTable").execute();

jSQLQuery.from(table)

Used for ""SELECT" queries to define the table from which to pull the results.

Parameters
  • table: The table from which to pull the results.
Returns
Example
var allUsers = jSQL.select('*').from('Users').execute().fetchAll();

jSQL Docs Home | jSQL on GitHub

jSQL Docs Home

Persistence Management

Storage APIs

Querying the Database

jSQLTable class

jSQLQuery interface

jSQLWhereClause class

jSQL Helper Methods

jSQL Syntax

Fun with DataTypes

Error Handling

Examples

License

Clone this wiki locally