How to connect ScyllaDB with Java

How to connect ScyllaDB with Java

This post gives you Introducing ScyllaDB With Jav, and an overview of ScyllaDB, how to connect with Java using Jakarta EE.

From Wikipedia: "Scylla is an open-source distributed NoSQL data store. It was designed to be compatible with Apache Cassandra while achieving significantly higher throughputs and lower latencies. It supports the same protocols as Cassandra (CQL and Thrift) and the same file formats (SSTable), but is a completely rewritten implementation, using the C++17 language replacing Cassandra's Java, and the Seastar asynchronous programming library replacing threads, shared memory, mapped files, and other classic Linux programming techniques."

This post will test the ScyllaDB with Java and give you the first impression of this database, which is compatible with Apache Cassandra.

Installation

The first step in this tutorial is to install the Scylla database. To make this process easier, we will use Docker, which simplifies a lot of the installation process with just this command:

docker run -d --name scylladb-instance -p 9042:9042 scylladb/scylla
Infrastructure Code

In a Maven project, we need to set the dependency project. Eclipse JNoSQL has two layers: one for mapping and the other one for communication. Once ScyllaDB is compatible with Cassandra, this post will use the Cassandra driver as the dependency for communication. Furthermore, it important to set a CDI implementation.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <artifactId>scylla</artifactId>
  <name>Artemis Demo using Java SE scylla</name>

  <parent>
    <groupId>org.jnosql.artemis</groupId>
    <artifactId>artemis-demo-java-se</artifactId>
    <version>0.0.9</version>
  </parent>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>

    <dependency>
      <groupId>org.jnosql.artemis</groupId>
      <artifactId>cassandra-extension</artifactId>
      <version>${project.version}</version>
    </dependency>
  </dependencies>

</project>

Show Me the Code

For this sample, we have the Person entity with idname, and phones as fields:

import org.jnosql.artemis.Column;
import org.jnosql.artemis.Entity;
import org.jnosql.artemis.Id;

import java.util.List;

@Entity("Person")
public class Person {

  @Id("id")
  private long id;

  @Column
  private String name;

  @Column
  private List<String> phones;

}

There is a Repository interface that implements the basic operations in the database. Also, it has the method query, which gives the method that Eclipse JNoSQL will implement to the Java developer:

import org.jnosql.artemis.Repository;

public interface PersonRepository extends Repository<Person, Long> {

}

The next step is to make a connection, an entity manager, available to CDI.

import org.jnosql.diana.cassandra.column.CassandraColumnFamilyManager;
import org.jnosql.diana.cassandra.column.CassandraColumnFamilyManagerFactory;
import org.jnosql.diana.cassandra.column.CassandraConfiguration;

import javax.annotation.PostConstruct;
import javax.enterprise.context.ApplicationScoped;
import javax.enterprise.inject.Produces;

@ApplicationScoped
public class ScyllaProducer {

private static final String KEY_SPACE = "developers";

private CassandraConfiguration cassandraConfiguration;

private CassandraColumnFamilyManagerFactory managerFactory;

@PostConstruct

public void init() {
    cassandraConfiguration = new CassandraConfiguration();
    managerFactory = cassandraConfiguration.get();
}

@Produces
public CassandraColumnFamilyManager getManagerCassandra() {
    return managerFactory.get(KEY_SPACE);
}

}

The ScyllaProducer class makes a manager entity class available and ready for the application use. Once the code does not define the Settings, it will use the default behavior, so it will read from the diana-cassandra.properties file. This file has the configuration startup, thus the port and host to connect. Scylla as Cassandra is not schemaless, which means we need to create the structure before we use it. The properties file has Cassandra Query Language to create the keyspace and the column family structure.

cassandra.host.1=localhost
cassandra.query.1=CREATE KEYSPACE IF NOT EXISTS developers WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 3};
cassandra.query.2=CREATE COLUMNFAMILY IF NOT EXISTS developers.Person (id bigint PRIMARY KEY, name text, phones list<text>);

The whole configuration process and the code are ready, so let's run the code!


import org.jnosql.artemis.cassandra.column.CassandraTemplate;
import org.jnosql.artemis.column.ColumnTemplate;
import org.jnosql.diana.api.column.ColumnQuery;

import javax.enterprise.inject.se.SeContainer;
import javax.enterprise.inject.se.SeContainerInitializer;
import java.util.Arrays;
import java.util.Optional;

import static org.jnosql.diana.api.column.query.ColumnQueryBuilder.select;

public class App {

public static void main(String[] args) {

    try (SeContainer container = SeContainerInitializer.newInstance().initialize()) {
        Person person = Person.builder().withPhones(Arrays.asList("234", "432"))
                .withName("Ada Lovelace").withId(1).build();
        ColumnTemplate template = container.select(CassandraTemplate.class).get();
        Person saved = template.insert(person);
        System.out.println("Person saved" + saved);

        ColumnQuery query = select().from("Person").where("id").eq(1L).build();

        Optional&lt;Person&gt; result = template.singleResult(query);
        System.out.println("Entity found: " + result);

    }
}

private App() {
}

}

import javax.enterprise.inject.se.SeContainer;
import javax.enterprise.inject.se.SeContainerInitializer;
import java.util.Arrays;
import java.util.Optional;

import static org.jnosql.artemis.DatabaseQualifier.ofColumn;

public class App2 {

public static void main(String[] args) {

    try(SeContainer container = SeContainerInitializer.newInstance().initialize()) {
        Person person = Person.builder().withPhones(Arrays.asList("234", "432"))
                .withName("Ada Lovelace").withId(1).build();
        PersonRepository repository = container.select(PersonRepository.class).select(ofColumn()).get();
        Person saved = repository.save(person);
        System.out.println("Person saved" + saved);

        Optional&lt;Person&gt; result = repository.findById(1L);
        System.out.println("Entity found: " + person);

    }
}

private App2() {}

}

We now have the ability to run a particular behavior in a NoSQL database matter! That's why the Eclipse JNoSQL worries about the extensibility of both Cassandra and ScyllaDB and worries about the support to native query the CQL and operation with consistency level. To support it and move resources, there is the CassandraTemplate, which is an extension of ColumnTemplate and allows features from Cassandra as a consequence on ScyllaDB.

import com.datastax.driver.core.ConsistencyLevel;
import org.jnosql.artemis.cassandra.column.CassandraTemplate;
import org.jnosql.diana.api.column.ColumnQuery;

import javax.enterprise.inject.se.SeContainer;
import javax.enterprise.inject.se.SeContainerInitializer;
import java.util.Arrays;
import java.util.List;

import static org.jnosql.diana.api.column.query.ColumnQueryBuilder.select;

public class App3 {

public static void main(String[] args) {

    try (SeContainer container = SeContainerInitializer.newInstance().initialize()) {
        Person person = Person.builder().withPhones(Arrays.asList("234", "432"))
                .withName("Ada Lovelace").withId(1).build();
        CassandraTemplate cassandraTemplate = container.select(CassandraTemplate.class).get();
        Person saved = cassandraTemplate.save(person, ConsistencyLevel.ONE);
        System.out.println("Person saved" + saved);
        List&lt;Person&gt; people = cassandraTemplate.cql("select * from developers.Person where id = 1");
        System.out.println("Entity found: " + people);

    }
}

private App3() {
}

}

This post gives you an overview of ScyllaDB and how to connect with Java using Jakarta EE. ScyllaDB has compatibility with Cassandra that allows us to use the same Cassandra API without issues. 

Thank you for reading!


Originally published on https://dzone.com

How to Build a CRUD API with Java, MongoDB, and Spring Boot

How to Build a CRUD API with Java, MongoDB, and Spring Boot

This tutorial shows how to build a CRUD API with Java, MongoDB, and Spring Boot. How to build a CRUD API with Java and MongoDB. In this tutorial, you create a Java data model class and mapped it to a MongoDB domain document using Spring Data annotations. You use a simple embedded MongoDB database as the datastore. You use Spring Boot to quickly and easily expose your data model via a REST API. Finally, you secured the REST API using Okta and Okta’s Spring Boot Starter.

This tutorial leverages two technologies that are commonly used to build web services: MongoDB and Java (we’ll actually use Spring Boot). MongoDB is a NoSQL database, which is a generic term for any non-relational databases and differentiates them from relational databases. Relational databases, such as SQL, MySQL, Postgres, etc…, store data in large tables with well-defined structures. These structures are strong and tight and not easily changed or customized on a per-record basis (this structure can also be a strength, depending on the use case, but we won’t get too deep into that here). Further, because relational databases grew up pre-internet, they were designed to run on monolithic servers. This makes them hard to scale and sync across multiple machines.

NoSQL databases like MongoDB were developed, to a large degree, to fit the needs of internet scaling where server loads can balloon dramatically and the preferred growth pattern is the replication of servers, not scaling a single monolithic server. MongoDB is a document-based database that natively stores JSON and was built for distributed scaling. Mongo documents are JSON objects and have no predetermined structure on the side of the database. The structure of the documents is determined by the application and can be changed dynamically, adding or removing fields as needed. This means that Mongo documents are very flexible (possibly a blessing and a curse, FYI). Also, because MongoDB produces JSON documents, it has become very popular with many of the JS-based front-ends where Javascript is king and JSON is easily handled.

Spring Boot is an easy to use web application framework from Spring that can be used to create enterprise web services and web applications. They’ve done an admirable job simplifying the underlying complexity of the Spring framework, while still exposing all of its power. And no XML required! Spring Boot can be deployed in a traditional WAR format or can be run stand-alone using embedded Tomcat (the default), Jetty, or Undertow. With Spring you get the benefit of literally decades of proven enterprise Java expertise - Spring has run thousands of productions applications - combined with the simplicity of a modern, “just work” web framework, incredible depth of features, and great community support.

In this tutorial, you will create a simple Java class file to model your data, you will store this data in a MongoDB database, and you will expose the data with a REST API. To do this, you will use Spring Boot and Spring Data.

Once you have created an unsecured REST API, you are going to use Okta and Spring Security (along with Okta’s Spring Boot Starter) to quickly and easily at JSON Web Token (JWT) authentication to your web service.

Install Java, Spring Boot, MongoDB, and Other Project Dependencies

You’ll need to install a few things before you get started.

Java 11: This project uses Java 11. If you don’t have Java 11, you can install OpenJDK. Instructions are found on the OpenJDK website. OpenJDK can also be installed using Homebrew. SDKMAN is another great option for installing and managing Java versions.

HTTPie: This is a simple command-line utility for making HTTP requests. You’ll use this to test the REST application. Check out the installation instructions on their website.

Okta Developer Account: You’ll be using Okta as an OAuth/OIDC provider to add JWT authentication and authorization to the application. Go to developer.okta.com/signup and sign up for a free developer account, if you haven’t already.

Download a Skeleton Project From Spring Initializr

To create a skeleton project, you can use Spring Initializr. It’s a great way to quickly configure a starter for a Spring Boot project.

Open this link to view and download your pre-configured starter project on Spring Initializr.

Take a look at the settings if you like. You can even preview the project by clicking the Explore button at the bottom of the page.

Once you’re ready, click the green Generate button at the bottom of the page to download the starter project to your computer.

The starter for this project is a Spring Boot 2.2.2 project that uses Java as the application language and Gradle as the build system (there are other options for both). We’ve covered Gradle in-depth in a few other posts (see below), so we won’t go into too much detail here, except to say that you won’t need to install anything for Gradle to work because of the Gradle wrapper, which includes a version of Gradle with the project.

The included dependencies in this project are:

  • Spring Web (spring-boot-starter-web): web application functionality
  • Spring Data MongoDB (spring-boot-starter-data-mongodb): MongoDB functionality
  • Embedded MongoDB Database (de.flapdoodle.embed.mongo): embed an in-memory MongoDB database, great for testing and tutorials like this
  • Rest Repositories (spring-boot-starter-data-rest): needed for the @RepositoryRestResource annotation, which allows us to quickly generate a REST api from our domain classes
  • Okta (okta-spring-boot-starter): starter that simplifies integrating OAuth 2.0 and OIDC authentication and authorization
  • Lombok (lombok): a getter, constructor, and setter helper generator via annotations

Before you do anything else, you need to make two changes to the build.gradle file.

  1. Temporarily comment out the dependency okta-spring-boot-starter
  2. Change de.flapdoodle.embed.mongo from testImplementation to implementation

You’re doing number one because you won’t be configuring the JWT OAuth until later in the tutorial, and the application won’t run with this dependency in it unless it is configured. You’re changing the de.flapdoodle.embed.mongo dependency because typically this embedded database is only used in testing, but for the purposes of this tutorial, you’re using it in the actual implementation. In a production situation, you’d use a real MongoDB instance.

The dependencies {} block should look like this:

dependencies {  
    implementation 'org.springframework.boot:spring-boot-starter-data-mongodb'  
    implementation 'org.springframework.boot:spring-boot-starter-data-rest'  
    implementation 'org.springframework.boot:spring-boot-starter-web'  
    //implementation 'com.okta.spring:okta-spring-boot-starter:1.3.0'  
    compileOnly 'org.projectlombok:lombok'  
    annotationProcessor 'org.projectlombok:lombok'  
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
    implementation 'de.flapdoodle.embed:de.flapdoodle.embed.mongo'  
}

With that done, you can run the application using:

./gradlew bootRun

If all goes well, you’ll see a bunch of output that ends with something like:

...
2019-12-16 20:19:16.430  INFO 69710 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2019-12-16 20:19:16.447  INFO 69710 --- [           main] c.o.m.m.MongodboauthApplication          : Started MongodboauthApplication in 16.557 seconds (JVM running for 18.032)

Open a second shell and use HTTPie to make a request:

$ http :8080

HTTP/1.1 200
Content-Type: application/hal+json;charset=UTF-8
Date: Mon, 16 Dec 2019 03:21:21 GMT
Transfer-Encoding: chunked

{
    "_links": {
        "self": {
            "href": "http://localhost:8080/profile"
        }
    }
}

The astute out there might be wondering why this request returned a 200 instead of a 404 (since you haven’t actually defined a controller endpoint).

When you included the spring-boot-starter-data-rest dependency, it included the functionality to automatically generate a “hypermedia-based RESTful front end” (as Spring describes it in their docs).

Create a Hypermedia-based RESTful Front End

What is a “hypermedia-based RESTful front end”? It is a REST API that uses Hypertext Application Language (HAL) format to output descriptive JSON. From the HAL Specification GitHub page:

HAL is a simple format that gives a consistent and easy way to hyperlink between resources in your API. Adopting HAL will make your API explorable, and its documentation easily discoverable from within the API itself. In short, it will make your API easier to work with and therefore more attractive to client developers.

Thus it’s a systematic way for a REST API to describe itself to client applications and for the client applications to easily navigate between the various endpoints.

Currently, there’s not much going on with the application, so there isn’t much to see in the response. It’ll make more sense a little later as we add endpoints and data.

Create a Domain Class with Java

To get the ball rolling, you need to create a domain class. Your application is going to be a simple refrigerator inventory application. You’ll be able to add, update, delete, and list all the items in a refrigerator. Each item will have 4 properties: 1) a unique ID assigned by the database, 2) a name, 3) an owner, and 4) and expiration date.

Create a RefrigeratorItem Java class and copy and paste the code below into it.

src/main/java/com/okta/mongodb/mongodboauth/RefrigeratorItem.java

package com.okta.mongodb.mongodboauth;  

import lombok.AllArgsConstructor;  
import lombok.Data;  
import lombok.NoArgsConstructor;  
import org.springframework.data.annotation.Id;  
import org.springframework.data.mongodb.core.mapping.Document;  

import java.util.Date;  

@Document  
@Data  
@AllArgsConstructor  
@NoArgsConstructor  
public class RefrigeratorItem {  

    @Id  
    private String id;  
    private String name;
    private String owner;
    private Date expiration;
}

The @Document annotation is the Spring Data annotation that marks this class as defining a MongoDB document data model. The other annotations are Lombok helpers that save us from the drudgery of creating various getters, setters, and constructors. See more about Lombok at the project’s website.

NOTE: If you’re using an IDE for this tutorial, you may need to install and enable the Lombok plugin.

Create a Spring Data Repository

The next step is to define a Spring Data repository. This is where some pretty incredible auto-magicking happens. You’re going to create a base class that extends the Spring Data class MongoRepository. This superclass includes all the necessary code for reading and writing our domain class to and from the database. Further, you will use the @RepositoryRestResource annotation to tell Spring Boot to automatically generate a REST endpoint for the data using the HAL JSON spec. mentioned above.

Create the repository class shown below.

src/main/java/com/okta/mongodb/mongodboauth/RefrigeratorRepository.java

package com.okta.mongodb.mongodboauth;  

import org.springframework.data.mongodb.repository.MongoRepository;  
import org.springframework.data.rest.core.annotation.RepositoryRestResource;  

@RepositoryRestResource(collectionResourceRel = "fridge", path = "fridge")
public interface RefrigeratorRepository extends MongoRepository<RefrigeratorItem, String> {  
}

You might notice that in the @RepositoryRestResource annotation you are specifying the /fridge URL context for the generated endpoints.

Test the Mongo Repository and Add Some Data

Stop the app (Control-C, if it’s still running) and re-run it.

./gradlew bootRun

Test the home endpoint again.

$ http :8080

HTTP/1.1 200
Content-Type: application/hal+json;charset=UTF-8
Date: Mon, 16 Dec 2019 03:41:05 GMT
Transfer-Encoding: chunked

{
    "_links": {
        "fridge": {
            "href": "http://localhost:8080/fridge{?page,size,sort}",
            "templated": true
        },
        "profile": {
            "href": "http://localhost:8080/profile"
        }
    }
}

This time you’ll see the /fridge endpoint is listed.

Test it out with http :8080/fridge. You should see a response like the one below:

{
    "_embedded": {
        "fridge": []
    },
    "_links": {
        "profile": {
            "href": "http://localhost:8080/profile/fridge"
        },
        "self": {
            "href": "http://localhost:8080/fridge{?page,size,sort}",
            "templated": true
        }
    },
    "page": {
        "number": 0,
        "size": 20,
        "totalElements": 0,
        "totalPages": 0
    }
}

Not a whole lot going on yet, but that’s easily changed. You’re going to use POST requests to add some data to the embedded MongoDB database. But first, you need to configure an application property.

Add the following line to your src/main/resources/application.properties file.

spring.jackson.date-format=MM-dd-yyyy

This tells Spring the expected date format for the expiration property, which will allow it to properly parse the JSON string into a Java date.

Stop (Control-C) and restart the application.

./gradlew bootRun

Now add some data using the following requests.

http POST :8080/fridge name=milk owner=Andrew expiration=01-01-2020 
http POST :8080/fridge name=cheese owner=Andrew expiration=02-10-2020
http POST :8080/fridge name=pizza owner=Andrew expiration=03-30-2020

Check out the inventory now and you should see these new items.

$ http :8080/fridge

HTTP/1.1 200
Content-Type: application/hal+json;charset=UTF-8
Date: Mon, 16 Dec 2019 03:45:23 GMT
Transfer-Encoding: chunked

{
    "_embedded": {
        "fridge": [
            {
                "_links": {
                    "refrigeratorItem": {
                        "href": "http://localhost:8080/fridge/5dae7b4c6a99f01364de916c"
                    },
                    "self": {
                        "href": "http://localhost:8080/fridge/5dae7b4c6a99f01364de916c"
                    }
                },
                "expiration": "01-01-2020",
                "name": "milk",
                "owner": "Andrew"
            },
            {
                "_links": {
                    "refrigeratorItem": {
                        "href": "http://localhost:8080/fridge/5dae7b4d6a99f01364de916d"
                    },
                    "self": {
                        "href": "http://localhost:8080/fridge/5dae7b4d6a99f01364de916d"
                    }
                },
                "expiration": "02-10-2020",
                "name": "cheese",
                "owner": "Andrew"
            },
            {
                "_links": {
                    "refrigeratorItem": {
                        "href": "http://localhost:8080/fridge/5dae7b4f6a99f01364de916e"
                    },
                    "self": {
                        "href": "http://localhost:8080/fridge/5dae7b4f6a99f01364de916e"
                    }
                },
                "expiration": "03-30-2020",
                "name": "pizza",
                "owner": "Andrew"
            }
        ]
    },
    "_links": {
        "profile": {
            "href": "http://localhost:8080/profile/fridge"
        },
        "self": {
            "href": "http://localhost:8080/fridge{?page,size,sort}",
            "templated": true
        }
    },
    "page": {
        "number": 0,
        "size": 20,
        "totalElements": 3,
        "totalPages": 1
    }
}

Notice that the returns JSON gives you the URL for each individual item. If you wanted to delete the first item in the list above, you could run the following request.

http DELETE :8080/fridge/5dae7b4c6a99f01364de916c

The long string,5dae7b4c6a99f01364de916c, is the unique ID for that item. MongoDB doesn’t use sequential integer ID numbers like SQL databases often do. It uses randomly generated UUIDs instead.

If you wanted to update an item, you could use a PUT, as shown below.

http PUT :8080/fridge/5dae7b4f6a99f01364de916e name="old pizza" expiration="03-30-2020" owner="Erin"

Note that with a PUT you have to send data for all the fields, not just the field you want to update, otherwise the omitted fields are set to null. If you just want to update select fields, use a PATCH.

http PATCH :8080/fridge/5dae7b4f6a99f01364de916e owner="Andrew"

With that rather paltry amount of work, you’ve created a MongoDB database model and exposed it to the world using a REST API. Pretty sweet!

The next step is to secure it. The last thing you need is hackers breaking into your house and stealing your pizza and cheese.

Create an OIDC Application for Your Java + MongoDB App

Okta is a software-as-service identity management provider. We provide solutions that make adding authentication and authorization to web applications easy. In this tutorial, you are going to use Okta to add JSON Web Token authentication and authorization to your application using OAuth 2.0 and OpenID Connect (OIDC).

OAuth 2.0 is an authorization protocol (verifying what the client or user is allowed to do) and OIDC is an authentication protocol (verifying the identity of the user) built on top of OAuth 2.0. They are a set of open standards that help ensure your web application’s security is handled safely and effectively. Together they provide a complete authentication and authorization protocol.

They are not, however, implementations. That’s where Okta comes in. Okta will be the identity provider and your Spring Boot app will be the client.

You should have already signed up for a free developer account at Okta. Navigate to the developer dashboard at https://developer.okta.com. If this is your first time logging in, you may need to click the Admin button.

To configure JSON Web Token (JWT) authentication and authorization, you need to create an OIDC application.

From the top menu, click on the Applications button. Click the Add Application button.

Select application type Web and click Next.

Give the app a name. I named mine “Spring Boot Mongo”.

Under Login redirect URIs, add a new URI: https://oidcdebugger.com/debug.

Under Grant types allowed, check Implicit (Hybrid).

The rest of the default values will work.

Click Done.

Leave the page open or take note of the Client ID. You’ll need it in a bit when you generate a token.

To test the REST API, you’re going to use the OpenID Connect Debugger to generate a token. This is why you need to add the login redirect URI and allow the implicit grant type.

Configure Spring Boot for OAuth 2.0

Now you need to update the Spring Boot application to use JWT authentication. First, open your src/main/resources/build.gradle file and uncomment the okta-spring-boot-starter dependency.

dependencies {  
    ...
    implementation 'com.okta.spring:okta-spring-boot-starter:1.3.0'  <-- UNCOMMENT ME
    ... 
}

Next, open your src/main/resources/application.properties file and add your Okta Issuer URI to it. The Issuer URI can be found by opening your Okta developer dashboard. From the top menu, select API and Authorization Servers. Your Issuer URI can be found in the panel in the row for the default authorization server.

spring.jackson.date-format=MM-dd-yyyy  
okta.oauth2.issuer=https://{yourOktaUrl}/oauth2/default

The last update is to add a new class called SecurityConfiguration.

src/main/java/com/okta/mongodb/mongodboauth/SecurityConfiguration.java

package com.okta.mongodb.mongodboauth;  

import org.springframework.context.annotation.Configuration;  
import org.springframework.security.config.annotation.web.builders.HttpSecurity;  
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;  

@Configuration  
public class SecurityConfiguration extends WebSecurityConfigurerAdapter {  

    @Override  
    public void configure (HttpSecurity http) throws Exception {  
        http.authorizeRequests()
            .anyRequest().authenticated()
            .and()
            .oauth2ResourceServer()
            .jwt();
    }
}

This simple class configures Spring Boot to authenticate all requests and to use an OAuth 2.0 resource server with JWT authentication and authorization.

Now if you restart the application and try a request, you’ll get a 401.

$ http :8080

HTTP/1.1 401
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
Content-Length: 0
...

This is the expected response. You’re REST API is now protected and requires a valid token.

Generate a Token Using the OpenID Connect Debugger

To access your now-protected server, you need a valid JSON Web Token. The OIDC Debugger is a handy page that will allow you to generate a JWT.

Open the OIDC Debugger.

You will need to fill in the following values.

  • Authorization URI: https://{yourOktaUrl}/oauth2/default/v1/authorize
  • Client ID: the Client ID from your Okta OIDC application
  • State: just fill in any non-blank value (this is used in production to help protect against cross-site forgery attacks)
  • Response type: check box for token

The rest of the default values should work. Scroll down to the bottom and click Send Request.

If all went well, you will see your brand new access token.

Copy the token to your clipboard and store it in a shell variable like so:

TOKEN=eyJraWQiOiJrQkNxZ3o1MmQtOUhVSl94c0x4aGtzYlJxUDVD...

Now you can make authenticated and authorized requests.

$ http :8080 "Authorization: Bearer $TOKEN"

HTTP/1.1 200
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
...

{
    "_links": {
        "fridge": {
            "href": "http://localhost:8080/fridge{?page,size,sort}",
            "templated": true
        },
        "profile": {
            "href": "http://localhost:8080/profile"
        }
    }
}

In this tutorial, you created a Java data model class and mapped it to a MongoDB domain document using Spring Data annotations. You used a simple embedded MongoDB database as the datastore. You used Spring Boot to quickly and easily expose your data model via a REST API. Finally, you secured the REST API using Okta and Okta’s Spring Boot Starter.

The source code for this tutorial is available on GitHub at oktadeveloper/okta-java-mongodb-example.

Explore MongoDB in a Java application for basic usage.

Explore MongoDB in a Java application for basic usage.

Before you start using MongoDB in your Java programs, you need to make sure that you have MongoDB JDBC driver and Java set up on the machine. You can check Java tutorial for Java installation on your machine.

In day-to-day life in the industry, we have data to be saved and fetched from any persistence source. The persistence source can be SQL, NoSQL, or other types. MongoDB is a NoSQL database. Sometimes we need a small tool to look at data or delete data in MongoDB. For quick development, we need a solution/layer/UI that simplifies our task.

MongoDB holds data in the form of a document, so any data that needs to be stored has to be converted into a MongoDB Document, and it's a cumbersome task, in general. To make things easier, we need a helper/util java class. MongoDB Java framework provides a mechanism to implement the same. The following enum class helps to do so. Through this enum class, the data that exists in any POJO can be saved/queried/updated with ease. The enum class can fetch the MongoDB collection data with the default security mechanism.

public enum MongoUtility {
    INSTANCE;
    private final Map<String, MongoDatabase> databaseMap = new HashMap<>();
    private final CodecRegistry pojoCodecRegistry;
    private final MongoClient mongo;
    private MongoUtility() {
        pojoCodecRegistry = fromRegistries(MongoClient.getDefaultCodecRegistry(),
                fromProviders(PojoCodecProvider.builder().automatic(true).build()));
        mongo = new MongoClient();
    }
    public void closeMongoDB() {
        Optional.of(mongo).ifPresent(mongoInstance -> mongoInstance.close());
    }
    public MongoDatabase getDatabase(String database) {
        Optional<MongoDatabase> mongoDBOptional = Optional.ofNullable(databaseMap.get(database));
        return mongoDBOptional.orElseGet(createMongoDatabaseInstance(database));
    }
    private Supplier<MongoDatabase> createMongoDatabaseInstance(String database) {
        return () -> {
            MongoDatabase mongoDB = mongo.getDatabase(database);
            mongoDB = mongoDB.withCodecRegistry(pojoCodecRegistry);
            databaseMap.put(database, mongoDB);
            return mongoDB;
        };
    }
    public <T> MongoCollection<T> getBucket(String database, String bucket, Class<T> cls) {
        MongoDatabase mongoDB = getDatabase(database);
        return mongoDB.getCollection(bucket, cls);
    }
}

The above implementation can be achieved in Maven by putting the dependency in the pom.xml file.

<dependency>
  <groupId>org.mongodb</groupId>
  <artifactId>mongo-java-driver</artifactId>
  <version>3.10.2</version>
</dependency>

So, the framework can get things done with the least amount of effort.

Thanks for reading. If you liked this post, share it with all of your programming buddies!

Originally published on https://dzone.com

What are the differences between Standard SQL and Transact-SQL?

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

#1 Names of Database Objects

In relational database systems, we name tables, views, and columns, but sometimes we need to use the same name as a keyword or use special characters. In standard SQL, you can place this kind of name in quotation marks (""), but in T-SQL, you can also place it in brackets ([]). Look at these examples for the name of a table in T-SQL:

CREATE TABLE dbo.test.“first name” ( Id INT, Name VARCHAR(100));
CREATE TABLE dbo.test.[first name]  ( Id INT, Name VARCHAR(100));

Only the first delimiter (the quotation marks) for the special name is also part of the SQL standard.

What Is Different in a SELECT Statement?#2 Returning Values

The SQL standard does not have a syntax for a query returning values or values coming from expressions without referring to any columns of a table, but MS SQL Server does allow for this type of expression. How? You can use a SELECT statement alone with an expression or with other values not coming from columns of the table. In T-SQL, it looks like the example below:

SELECT 12/6 ;

In this expression, we don’t need a table to evaluate 12 divided by 6, therefore, the FROM statement and the name of the table can be omitted.

#3 Limiting Records in a Result Set

In the SQL standard, you can limit the number of records in the results by using the syntax illustrated below:

SELECT * FROM tab FETCH FIRST 10 ROWS ONLY

T-SQL implements this syntax in a different way. The example below shows the MS SQL Server syntax:

SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

As you notice, this uses an ORDER BY clause. Another way to select rows, but without ORDER BY, is by using the TOP clause in T-SQL:

SELECT TOP 10 * FROM tab;
#4 Automatically Generating Values

The SQL standard enables you to create columns with automatically generated values. The syntax to do this is shown below:

CREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY);

In T-SQL we can also automatically generate values, but in this way:

CREATE TABLE tab (id INTEGER IDENTITY);
#5 Math Functions

Several common mathematical functions are part of the SQL standard. One of these math functions is CEIL(x), which we don’t find in T-SQL. Instead, T-SQL provides the following non-standard functions: SIGN(x), ROUND(x,[,d]) to round decimal value x to the number of decimal positions, TRUNC(x) for truncating to given number of decimal places, LOG(x) to return the natural logarithm for a value x, and RANDOM() to generate random numbers. The highest or lowest number in a list in the SQL standard is returned by MAX(list) and MIN(list) functions, but in Transact-SQL, you use the GREATEST(list) and LEAST(list) functions.

T-SQL function ROUND:

SELECT ROUND(col) FROM tab;

#6 Aggregate Functions

We find another syntax difference with the aggregate functions. The functions COUNT, SUM, and AVG all take an argument related to a count. T-SQL allows the use of DISTINCT before these argument values so that rows are counted only if the values are different from other rows. The SQL standard doesn't allow for the use of DISTINCT in these functions.

Standard SQL:
SELECT COUNT(col) FROM tab;

T-SQL:
SELECT COUNT(col) FROM tab;

SELECT COUNT(DISTINCT col) FROM tab;

But in T-SQL we don’t find a population covariance function: COVAR_POP(x,y), which is defined in the SQL standard.

#7 Retrieving Parts of Dates and Times

Most relational database systems deliver many functions to operate on dates and times.

In standard SQL, the EXTRACT(YEAR FROM x) function and similar functions to select parts of dates are different from the T-SQL functions like YEAR(x) or DATEPART(year, x).

There is also a difference in getting the current date and time. Standard SQL allows you to get the current date with the CURRENT_DATE function, but in MS SQL Server, there is not a similar function, so we have to use the GETDATE function as an argument in the CAST function to convert to a DATE data type.

#8 Operating on Strings

Using functions to operate on strings is also different between the SQL standard and T-SQL. The main difference is found in removing trailing and leading spaces from a string. In standard SQL, there is the TRIM function, but in T-SQL, there are several related functions: TRIM (removing trailing and leading spaces), LTRIM (removing leading spaces), and RTRIM (removing trailing spaces).

Another very-often-used string function is SUBSTRING.

The standard SQL syntax for the SUBSTRING function looks like:

SUBSTRING(str FROM start [FOR len])

but in T-SQL, the syntax of this function looks like:

SUBSTRING(str, start, length)

There are reasons sometimes to add values coming from other columns and/or additional strings. Standard SQL enables the following syntax to do this:

As you can see, this syntax makes use of the || operator to add one string to another.

But the equivalent operator in T-SQL is the plus sign character. Look at this example:

SELECT col1 + col2  FROM tab;

In SQL Server, we also have the possibility to use the CONCAT function concatenates a list of strings:

SELECT CONCAT(col1, str1, col2, ...)  FROM tab;

We can also repeat one character several times. Standard SQL defines the function REPEAT(str, n) to do this. Transact-SQL provides the REPLICATE function. For example:

SELECT  REPLICATE(str, x);

where x indicates how many times to repeat the string or character.

#9 Inequality Operator

During filtering records in a SELECT statement, sometimes we have to use an inequality operator. Standard SQL defines <> as this operator, while T-SQL allows for both the standard operator and the != operator:

SELECT col3 FROM tab WHERE col1 != col2;
#10 ISNULL Function

In T-SQL, we have the ability to replace NULL values coming from a column using the ISNULL function. This is a function that is specific to T-SQL and is not in the SQL standard.

SELECT ISNULL(col1) FROM tab;
Which Parts of DML Syntax Are Different?

In T-SQL, the basic syntax of DELETE, UPDATE, and INSERT queries is the same as the SQL standard, but differences appear in more advanced queries. Let’s look at them.

#11 OUTPUT Keyword

The OUTPUT keyword occurs in DELETE, UPDATE, and INSERT statements. It is not defined in standard SQL.

Using T-SQL, we can see extra information returned by a query. It returns both old and new values in UPDATE or the values added using INSERT or deleted using DELETE. To see this information, we have to use prefixes in INSERT, UPDATE, and DELETE.

UPDATE tab SET col='new value'
OUTPUT Deleted.col, Inserted.col;

We see the result of changing records with the previous and new values in an updated column. The SQL standard does not support this feature.

#12 Syntax for INSERT INTO ... SELECT

Another structure of an INSERT query is INSERT INTO … SELECT. T-SQL allows you to insert data from another table into a destination table. Look at this query:

INSERT INTO tab SELECT col1,col2,... FROM tab_source;

It is not a standard feature but a feature characteristic of SQL Server.

#13 FROM Clause in DELETE and UPDATE

SQL Server provides extended syntax of the UPDATE and DELETE with FROM clauses. You can use DELETE with FROM to use the rows from one table to remove corresponding rows in another table by referring to a primary key and a foreign key. Similarly, you can use UPDATE with FROM update rows from one table by referring to the rows of another table using common values (primary key in one table and foreign key in second, e.g. the same city name). Here is an example:

DELETE FROM Book
FROM Author
WHERE Author.Id=Book.AuthorId AND Author.Name IS NULL;

UPDATE Book
SET Book.Price=Book.Price*0.2
FROM Author
WHERE Book.AuthorId=Author.Id AND Author.Id=12;

The SQL standard doesn’t provide this syntax.

#14 INSERT, UPDATE, and DELETE With JOIN

You can also use INSERT, UPDATE, and DELETE using JOIN to connect to another table. An example of this is:

DELETE ItemOrder FROM ItemOrder
JOIN Item ON ItemOrder.ItemId=Item.Id
WHERE YEAR(Item.DeliveredDate) <= 2017;

This feature is not in the SQL standard.

Summary

This article does not cover all the issues about syntax differences between the SQL standard and T-SQL using the MS SQL Server system. However, this guide helps point out some basic features characteristic only of Transact-SQL and what SQL standard syntax isn’t implemented by MS SQL Server.

Thanks for reading. If you liked this post, share it with all of your programming buddies!

Originally published on https://dzone.com