fbpx

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 the 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 the 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 Developer key to get your developer key.

Developer key will be used in every part of integration.

(1) To copy your developer key

(2) To download it as a file

File content is your developer key.

Database setup

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

For any backend application you should have a 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 a 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 a Spring Boot framework as a backend application, Angular version 6 as a frontend application, and JSQL CLI as an SQL queries hasher task.

We will use a local Postgres database.

Setup Spring Boot application using Spring Initializr

To setup Spring Boot app you can use Spring Initializr.

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

Needed dependencies: JPA, PostgreSQL, RestRepositories.

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

Import the project into your IDE, we will be using Intelij IDEA.

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

This is our configuration in the 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

Finally, you can run your application from the IDE or use a bootJar task (for Gradle) to build and run via the command line.

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

Integrate the JSQL Spring Boot plugin

In our project we are using the Gradle build tool and according to the integration guide, we need to add the JSQL artifactory into the build.gradle file:

repositories {

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

}

And then add the dependency into dependencies block:

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

The last thing to do is to create a controller which will be extending the JSQLController and providing the EntityManager, API KEY and DEV KEY.

The 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 by sending an example request:

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

Or use Postman-like stuff.

Set "Remove developer queries after build" for Angular 7 projects

Before we start developing an Angular 7 application using JSQL, you should set “Remove developer queries after build” option to enable in JSQL Admin Panel.

You can read more on Angular 7 Guide.

Setup Angular 7 application using Angular CLI

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

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

The command to init Angular 7 application:

ng new myapp-angular

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

After installation you can run the app using the ng serve command:

ng serve

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

Integrate Angular 7 app with JSQL CLI

Before we start integrating an Angular 7 application with the JSQL libarary for Angular, we need to setup JSQL CLI to use during 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 a file with the developer key which can be found in the same directory as the Gruntfile.

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 the 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;

Finally add the JsqlService to the 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 the JsqlService into your app.component.ts:

import {JsqlService} from 'jsql-angular7';

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

  people = [];

  constructor(private jsqlService: JsqlService) {

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

  }

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

Close Menu