GraphQL Spring MySQL CRUD Example

Introduction

The tutorial, GraphQL Spring MySQL CRUD example will show you how to build CRUD (Create, Read, Update, Delete) operations using GraphQL with Spring Boot framework. We will use MySQL database server for storing data and Spring Data JPA to perform database activities.

My previous tutorials on GraphQL explained about how to wrap REST APIs instead of converting them into GraphQL APIs. I explained on this using Spring Boot and NodeJS technologies.

In this example we are using Spring Boot 2.3.0 and you must use Gradle version 6.3 or more.

We will create two tables Category and Product. So each product must belong to a category and a category may have multiple products under it. So Product will have many-to-one relationship with Category. We are going to use Spring Data JPA to perform database activities using Spring’s built-in API JpaRepository that provides functions for performing basic CRUD operations out of the box.

For category and product, we will perform Create, Read, Update and Delete operations using GraphQL APIs.

For read operations we are going to create Query and for write operations we are going to create Mutation.

Prerequisites

Eclipse 2019-12, Java at least 1.8, Gradle 6.4.1, Spring 2.3.0

Setup Project

We create a gradle based project in Eclipse and the name of the project is spring-graphql-crud.

We need to add the required dependencies into build.gradle script.

buildscript {
	ext {
		springBootVersion = '2.3.0.RELEASE'
	}
	
    repositories {
    	mavenLocal()
    	mavenCentral()
    }
    
    dependencies {
    	classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

plugins {
    id 'java-library'
    id 'org.springframework.boot' version "${springBootVersion}"
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
	mavenLocal()
    mavenCentral()
}

dependencies {
	implementation("org.springframework.boot:spring-boot-starter-web:${springBootVersion}")
	implementation("org.springframework.boot:spring-boot-starter-data-jpa:${springBootVersion}")
	implementation('com.graphql-java:graphql-spring-boot-starter:5.0.2')
	implementation('com.graphql-java:graphql-java-tools:5.2.4')
	implementation('mysql:mysql-connector-java:8.0.17')
	
	//required for jdk 9 or above
	runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}

MySQL Table

We will create two tables into MySQL database server – category and product – under roytuts database.

CREATE TABLE `category` (
	`id` int unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(50) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `product` (
	`id` int unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(255) NOT NULL,
	`code` varchar(255) NOT NULL,
	`price` double NOT NULL,
	`category_id` int unsigned NOT NULL,
	PRIMARY KEY (`id`),
	FOREIGN KEY (category_id)
      REFERENCES category(id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Entity Class

Now we need to map database table with Java POJO. Therefore we will create two entity classes – Category & Product and their relationship. You will find full source code later in Github.

Product

package com.roytuts.spring.graphql.crud.entity;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "product")
public class Product implements Serializable {

	private static final long serialVersionUID = 1L;

	@Id
	@Column(name = "id")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column(name = "name")
	private String name;

	@Column(name = "code")
	private String code;

	@Column(name = "price")
	private Double price;

	@ManyToOne
	@JoinColumn(name = "category_id", nullable = false, updatable = false)
	private Category category;

	//getters and setters

}

Repository Interfaces

Spring provides JpaRepository or CrudRepository which we can extend to get the built-in functions for performing basic CRUD operations.

CategoryRepository

package com.roytuts.spring.graphql.crud.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.roytuts.spring.graphql.crud.entity.Category;

public interface CategoryRepository extends JpaRepository<Category, Integer> {

}

ProductRepository

package com.roytuts.spring.graphql.crud.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.roytuts.spring.graphql.crud.entity.Product;

public interface ProductRepository extends JpaRepository<Product, Integer> {

}

Query Resolver

As I said earlier we need Query for fetching or reading data from GraphQL APIs. So we need to implement GraphQLQueryResolver to read data.

We will use Spring Data JPA repository interface to interact with database.

Make sure the method names, you write in your query resolver class, you also write the same names into your .graphqls file, where you define Query and Mutation for GraphQL APIs.

The following class reads all categories, products, category by id, product by id.

package com.roytuts.spring.graphql.crud.resolver;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.coxautodev.graphql.tools.GraphQLQueryResolver;
import com.roytuts.spring.graphql.crud.entity.Category;
import com.roytuts.spring.graphql.crud.entity.Product;
import com.roytuts.spring.graphql.crud.repository.CategoryRepository;
import com.roytuts.spring.graphql.crud.repository.ProductRepository;

@Component
public class QueryResolver implements GraphQLQueryResolver {

	@Autowired
	private CategoryRepository categoryRepository;

	@Autowired
	private ProductRepository productRepository;

	public List<Category> allCategories() {
		return categoryRepository.findAll();
	}

	public List<Product> allProducts() {
		return productRepository.findAll();
	}

	public Category category(Integer id) {
		return categoryRepository.findById(id).orElseGet(null);
	}

	public Product product(Integer id) {
		return productRepository.findById(id).orElseGet(null);
	}

}

Mutation Resolver

I also mentioned that we need Mutation for any write operations, such as, create, update, delete.

We need to implement interface GraphQLMutationResolver to perform write operations.

Make sure the method names, you write in your class, you need to also maintain the same names into .graphqls file.

package com.roytuts.spring.graphql.crud.resolver;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.coxautodev.graphql.tools.GraphQLMutationResolver;
import com.roytuts.spring.graphql.crud.entity.Category;
import com.roytuts.spring.graphql.crud.entity.Product;
import com.roytuts.spring.graphql.crud.repository.CategoryRepository;
import com.roytuts.spring.graphql.crud.repository.ProductRepository;

@Component
public class MutationResolver implements GraphQLMutationResolver {

	@Autowired
	private CategoryRepository categoryRepository;

	@Autowired
	private ProductRepository productRepository;

	public Category addCategory(String name) {
		Category category = new Category();
		category.setName(name);

		return categoryRepository.saveAndFlush(category);
	}

	public Product addProduct(String name, String code, Double price, Integer category_id) {
		Category category = categoryRepository.findById(category_id).orElseGet(null);

		Product product = new Product();
		product.setName(name);
		product.setCode(code);
		product.setPrice(price);
		product.setCategory(category);

		return productRepository.saveAndFlush(product);
	}

	public Category updateCategory(Integer id, String name) {
		Category category = new Category();
		category.setId(id);
		category.setName(name);

		return categoryRepository.saveAndFlush(category);
	}

	public Product updateProduct(Integer id, String name, String code, Double price) {
		Product product = new Product();
		product.setId(id);
		product.setName(name);
		product.setCode(code);
		product.setPrice(price);

		return productRepository.saveAndFlush(product);
	}

	public Boolean deleteCategory(Integer id) {
		categoryRepository.deleteById(id);
		return true;
	}

	public Boolean deleteProduct(Integer id) {
		productRepository.deleteById(id);
		return true;
	}

}

GraphQl Schema

Now we need GraphQl schema which will provide APIs to query or mutate the data.

Put the below schema.graphqls file under src/main/resources folder.

type Query {
  allCategories: [Category]
  allProducts: [Product]
  category(id: ID!): Category
  product(id: ID!): Product
}

type Mutation {
  addCategory(name: String!): Category!
  updateCategory(id: ID!, name: String!): Category!
  deleteCategory(id: ID!): Boolean
  addProduct(name: String!, code: String!, price: Float!, category_id: ID!): Product!
  updateProduct(id: ID!, name: String!, code: String!, price: Float!): Product!
  deleteProduct(id: ID!): Boolean
}

type Category {
  id: ID
  name: String!
}

type Product {
  id: ID
  name: String!
  code: String!
  price: Float!
  category: Category!
}

Database Settings

We need database configurations to connect with database. We put below settings into src/main/resources/application.properties file.

#datasource
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/roytuts
spring.datasource.username=root
spring.datasource.password=root

#disable schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=none

Main Class

The final part is a class with main method for deploying Spring Boot application into embedded Tomcat server.

package com.roytuts.spring.graphql.crud;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringGraphQLCrudApp {

	public static void main(String[] args) {
		SpringApplication.run(SpringGraphQLCrudApp.class, args);
	}

}

So we finally finished coding for GraphQL server but what about client or how to test these GraphQl APIs. We can use latest version of Postman to test GraphQL APIs but here I am not going to use Postman tool, I am going to put an HTML file index.html under src/main/resources/static folder with the below source code. So when your application is deployed, you can access the context path http://localhost:8080 in the browser which will provide GraphQL interface for testing APIs.

The below code is not written by me.

<!--
 *  Copyright (c) 2019 GraphQL Contributors
 *  All rights reserved.
 *
 *  This source code is licensed under the license found in the
 *  LICENSE file in the root directory of this source tree.
-->
<!DOCTYPE html>
<html>
  <head>
    <style>
      body {
        height: 100%;
        margin: 0;
        width: 100%;
        overflow: hidden;
      }
      #graphiql {
        height: 100vh;
      }
    </style>
    <!--
      This GraphiQL example depends on Promise and fetch, which are available in
      modern browsers, but can be "polyfilled" for older browsers.
      GraphiQL itself depends on React DOM.
      If you do not want to rely on a CDN, you can host these files locally or
      include them directly in your favored resource bunder.
    -->
    <script src="//cdn.jsdelivr.net/es6-promise/4.0.5/es6-promise.auto.min.js"></script>
    <script src="//cdn.jsdelivr.net/fetch/0.9.0/fetch.min.js"></script>
    <script src="//cdn.jsdelivr.net/react/15.4.2/react.min.js"></script>
    <script src="//cdn.jsdelivr.net/react/15.4.2/react-dom.min.js"></script>
    <!--
      These two files can be found in the npm module, however you may wish to
      copy them directly into your environment, or perhaps include them in your
      favored resource bundler.
     -->
    <link rel="stylesheet" href="//cdn.jsdelivr.net/npm/graphiql@0.11.2/graphiql.css" />
	<script src="//cdn.jsdelivr.net/npm/graphiql@0.11.2/graphiql.js"></script>
  </head>
  <body>
    <div id="graphiql">Loading...</div>
    <script>
      /**
       * This GraphiQL example illustrates how to use some of GraphiQL's props
       * in order to enable reading and updating the URL parameters, making
       * link sharing of queries a little bit easier.
       *
       * This is only one example of this kind of feature, GraphiQL exposes
       * various React params to enable interesting integrations.
       */
      // Parse the search string to get url parameters.
      var search = window.location.search;
      var parameters = {};
      search.substr(1).split('&').forEach(function (entry) {
        var eq = entry.indexOf('=');
        if (eq >= 0) {
          parameters[decodeURIComponent(entry.slice(0, eq))] =
            decodeURIComponent(entry.slice(eq + 1));
        }
      });
      // if variables was provided, try to format it.
      if (parameters.variables) {
        try {
          parameters.variables =
            JSON.stringify(JSON.parse(parameters.variables), null, 2);
        } catch (e) {
          // Do nothing, we want to display the invalid JSON as a string, rather
          // than present an error.
        }
      }
      // When the query and variables string is edited, update the URL bar so
      // that it can be easily shared
      function onEditQuery(newQuery) {
        parameters.query = newQuery;
        updateURL();
      }
      function onEditVariables(newVariables) {
        parameters.variables = newVariables;
        updateURL();
      }
      function onEditOperationName(newOperationName) {
        parameters.operationName = newOperationName;
        updateURL();
      }
      function updateURL() {
        var newSearch = '?' + Object.keys(parameters).filter(function (key) {
          return Boolean(parameters[key]);
        }).map(function (key) {
          return encodeURIComponent(key) + '=' +
            encodeURIComponent(parameters[key]);
        }).join('&');
        history.replaceState(null, null, newSearch);
      }
      // Defines a GraphQL fetcher using the fetch API. You're not required to
      // use fetch, and could instead implement graphQLFetcher however you like,
      // as long as it returns a Promise or Observable.
      function graphQLFetcher(graphQLParams) {
        // When working locally, the example expects a GraphQL server at the path /graphql.
        // In a PR preview, it connects to the Star Wars API externally.
        // Change this to point wherever you host your GraphQL server.
        const isDev = !window.location.hostname.match(/(^|\.)netlify\.com$|(^|\.)graphql\.org$/)
        const api = isDev ? '/graphql' : 'https://swapi.graph.cool/'
        return fetch(api, {
          method: 'post',
          headers: {
            'Accept': 'application/json',
            'Content-Type': 'application/json',
          },
          body: JSON.stringify(graphQLParams),
          credentials: 'include',
        }).then(function (response) {
          return response.text();
        }).then(function (responseBody) {
          try {
            return JSON.parse(responseBody);
          } catch (error) {
            return responseBody;
          }
        });
      }
      // Render <GraphiQL /> into the body.
      // See the README in the top level of this module to learn more about
      // how you can customize GraphiQL by providing different values or
      // additional child elements.
      ReactDOM.render(
        React.createElement(GraphiQL, {
          fetcher: graphQLFetcher,
          query: parameters.query,
          variables: parameters.variables,
          operationName: parameters.operationName,
          onEditQuery: onEditQuery,
          onEditVariables: onEditVariables,
          onEditOperationName: onEditOperationName
        }),
        document.getElementById('graphiql')
      );
    </script>
  </body>
</html>

You will see GraphQL interface similar to below at http://localhost:8080.

Testing the Application

Currently we do not have any data into category and product tables.

So executing GraphQL query will give us null or empty result.

Even you can query a particular category or product by passing an id. You may also want to return only those attributes which you want to see, for example, you may return only id or id and name or name and code, etc.

query{
  category(id:10) {
    id
    name
  }
}

query{
  product(id:11) {
    id
    name
    code
    price
  }
}

To create category or product use similar kind of mutation as shown below.

We do not want to set value for id field as it will be generated automatically by the database. We want to see id and name attributes after creating new category, so we have passed id and name attributes inside {}. By default if you do not put any attribute inside {} then it will return id attribute because it is primary key. For example, if you fire mutation addCategory(name: "Desktop"), then you will see following output:

The id attribute inside {} for mutation added automatically when you fire the mutation.

To create new product use below example,

Update category by using below mutation example,

Update product using below mutation example,

Delete product,

Delete category,

Now you can play with various attributes of the query or mutation.

Source Code

Download

Thanks for reading.

Roy Tutorials