To retrieve data from your database start writing an SQL query and pass it to the query() method of your connection:

let params = {
    'driver': 'mysql',
    'dbname': 'test',
    'host': 'localhost',
    'port': '3306',
    'username': 'root',
    'password': 'password'
};

let connection = DriverManager.getConnection(params);
let sql = "SELECT * FROM articles";
connection.query(sql)
    .then(function(response){
      let rows = response.rows;
      // do something with the data
    })
    .catch(function(error){
      // do something with the error
    }); // Simple, but has several drawbacks

The query method executes the SQL and returns a Promise object.

The query method is the most simple one for fetching data, but it also has at least one drawbacks:

There is no way to add dynamic parameters to the SQL query without modifying sql itself. This can easily lead to a category of security holes called SQL Injection, where a third party can modify the SQL executed and even execute their own queries through clever exploiting of the security hole.

Using Connection

There are two low-level methods on Connection that allow you to execute queries:

  • executeQuery(sql, params) - This method is recommended for SELECT statements

  • executeUpdate(sql, params) - This method is recommended for INSERT, UPDATE, DELETE statements

Both of them execute the given query with the underlying driver and return a Promise. The promise provide a Response Statement object that can be one of this tree depending which query type was executed: * SELECT returns a Statement\SelectResponseStatement that have a rows property with the rows that match the query * UPDATE or DELETE returns a Statement\UpdateResponseStatement that have a affectedRows property, a number representing how many rows where affected * INSERT returns a Statement\InsertResponseStatement that have a insertId property with the id of the currently inserted row.

The Connection provide another methods for easy access and execution of statements

  • fetchAssoc(query, params) - Execute the given query and return a Promise with the response object holding just the first row of the result
  • fetchColumn(query, params, column) - Prepares and executes an SQL query and returns the value of a single column of the first row of the result.

NOTE: it should be use with constraints since diff databases return diff set of order in values

Additionally there are lots of convenience methods for data-retrieval and manipulation on the Connection, which are all described in the API section below.