Getting started with JSQL

Find out how to setup and develop your first app using JSQL

Create account in JSQL Cloud

The JSQL stack will not work without an account in the JSQL cloud.

To learn more about architecture and integration steps see overview.

Create an application

Sign in to the JSQL admin panel and register a new application to be able to integrate JSQL with your frontend and backend applications both.

Click the “Add api key” button in the Main Navigation menu. Enter the name of application and confirm with the “Save” button.

Getting API KEY

Sign in to the JSQL admin panel and select created application to get your API KEY from green field.

API KEY will be used in every part of integration.

Getting DEV KEY

Sign in to the JSQL admin panel and select Download Dev Key to get your DEV KEY.

DEV KEY will be used in every part of integration.

File content is your DEV KEY.

Database setup

In this tutorial we will not explain how to run the database.

For any backend application you should have relational database created.

Currently we support Postgres and MySQL.

You can see how to setup PostgreSQL here.

You can see how to setup MySQL here.

For examples we use database named “test” with single table:

CREATE TABLE person (
    person_id bigserial primary key,
    person_name varchar(20) NOT NULL,
    person_surname text NOT NULL,
    person_age integer default NULL
);

Spring Boot + Angular 7 + JSQL CLI

In this stack, we will use the Spring Boot framework as a backend application, Angular version 6 as a frontend application, and JSQL CLI as the SQL queries hasher task.

We will use local Postgres database.

Setup Spring Boot application using Spring Initializr

To setup Spring Boot app you can use Spring Initializr.

We need basic REST stack with Postgres JDBC driver as well.

Needed dependencies: JPA, PostgreSQL, RestRepositories.

We setup project using Gradle and Java. Feel free to use as well Groovy and Maven.

Import project into your IDE, we use Intelij IDEA.

First of all, you need to configure your application to work with your database.

We have such configuration in application.properties file:

server.port=8088
spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL95Dialect
spring.datasource.driver-class-name=org.postgresql.Driver
server.tomcat.uri-encoding=UTF-8
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults = false
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

After all, you can run your application from IDE or use bootJar task (for Gradle) to build and runn via command line.

Of course you can use JPA to generate database from Entities, but it’s not a point of this tutorial.

Integrate JSQL Spring Boot plugin

Our project using Gradle build tool, due to integration guide we need to add JSQL artifactory into build.gradle file:

repositories {

    mavenCentral()
    maven{
        url "http://46.41.135.99:8081/artifactory/libs-release-local"
    }

}

And then add dependency into dependencies block:

compile(group: 'it.jsql.connector.spring', name: 'jsql-spring-plugin', version: '1.0.0')

Last thing which I should do is create some controller which one will be extending JSQLController and providing EntityManager, API KEY and DEV KEY.

Simplest controller implementation:

package com.mysite.myapp;

import it.jsql.connector.controller.JSQLController;
import it.jsql.connector.service.IJSQLService;
import it.jsql.connector.service.JSQLService;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RestController;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

@CrossOrigin
@RestController
public class MyAppController extends JSQLController {

    protected final String API_KEY = "==8FVjKMSJ877wLvxzcYJSkHXkFqfWfC9sfZOG3/LlkQ==Z5fOB0Kre6rMML2TUKIc";
    protected final String DEV_KEY = "ZLsL1SOPxBuhtcafKw/vQw==";

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public IJSQLService getJsqlService() {
        return new JSQLService(entityManager, API_KEY, DEV_KEY);
    }

}

You can test if JSQL works sending example request:

curl 'http://localhost:8080/select' -H 'Accept: application/json' -H 'Content-Type: application/json' 
--data-binary '{"token":"select * from person","params":{}}' --compressed

Or use Postman-like stuff.

Set "allow plain queries" for Angular 2+ projects

Before we start developing Angular 7 application using JSQL, you should set “Plain queries allowed” option to “true” in JSQL Admin Panel.

You can read more on Angular 7 Guide.

Setup Angular 7 application using Angular CLI

To setup Angular 7 app you can use Angular CLI or see some tutorial here.

We only need basic application with simple routing and view with list of people.

Command to init Angular 7 application:

ng new myapp-angular

Then select routing “Yes” and select your favorite CSS preprocessor or plain CSS.

After creation you can run app using ng serve command:

ng serve

Then you can enter on localhost:4200 (by default).

Integrate Angular 7 app with JSQL CLI

Before we start integrating Angular 7 application with JSQL libarary for Angular, we need to setup JSQL CLI to work along your development using ng-serve. For our project we can use:
npm-jsql ==8FVjKMSJ877wLvxzcYJSkHXkFqfWfC9sfZOG3/LlkQ==Z5fOB0Kre6rMML2TUKIc src/app/*.ts tmp-dist true

Developer key

The plugin requires the file with the developer key which must be found in the directory in which Gruntfile is.

This file can be downloaded from the JSQL Admin Panel by each team member (see how to download it in FAQ ) and saved locally in the project.

We recommend that the file with the development key be added to the ignored files with the repository used (GIT, SVN etc).

Integrate Angular 7 app with JSQL

Once you know how to run JSQL CLI, you need to install JSQL plugin for Angular 7 into your project:

npm install jsql-angular6 --save

Then you can import JsqlService and JsqlConfig into your app.module.ts:

import {JsqlModule} from 'jsql-angular7';
import {JsqlConfig} from 'jsql-angular7/src/modules/jsql.module';

And set configuartion as well:

const jsqlConfig: JsqlConfig = {
  host: 'localhost:8088'
} as JsqlConfig;

To finally add JsqlService to imports section:

@NgModule({
  declarations: [
    AppComponent
  ],
  imports: [
    BrowserModule,
    AppRoutingModule,
    JsqlModule.forRoot(jsqlConfig)
  ],
  providers: [],
  bootstrap: [AppComponent]
})

To render some data from your database using JSQL, you should receive it using JSQL in some component/service for example.

Import JsqlService into your app.component.ts:

import {JsqlService} from 'jsql-angular7';

Then we receive data from our database with JSQL and RxJS:

  people = [];

  constructor(private jsqlService: JsqlService) {

    jsqlService.select('select * from person')
      .rx.subscribe((result: any) => {
        this.people = result;
        console.log(this.people);
      }, (error: any) => {
        console.log(error);
      });

  }

Let’s render given data in app.component.html template:

Close Menu