Springboot - Connecting into a database (MySQL 8.x) - Repository and EntityManager

 

By default springboot is ready to use H2 database. But we can add support to another databases adding their connectors in pom.xml.

 

The code below show how to include MySQL connector.

 

     <!-- Use MySQL Connector-J -->

        <dependency>

                    <groupId>mysql</groupId>

                    <artifactId>mysql-connector-java</artifactId>

       </dependency

 

  

Now we have added  database dependency, we have to update the file application.properties with parameters for database connections. This application.properties if not created yet, can be manually created. The directory for this file location is src/main/resources/application.properties. In this file we can add thhe database URL, username, password, and also DDL strategy.

  

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.url=jdbc:mysql://localhost:3306/sakila?useSSL=false&serverTimezone=UTC

spring.datasource.username=root

spring.datasource.password=root

spring.jpa.hibernate.ddl-auto=none


 

The ddl-auto is recommended to set as none to do not overwritten your existing database.

Now we have a reference to the database, we have two options to interact with that using JPA. For this purpose we have to define Entities and repositories.

 

Working with JPA, we first have to define an @Entity, as the example below:

 

 

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.Table;

 

@Entity

@Table (name="actor")

public class Actor {

       @Id @GeneratedValue(strategy = GenerationType.IDENTITY)

       @Column(name="actor_id")

       private Long id;

      

       @Column(name="first_name")

       private String firstName;

      

       @Column(name="last_name")

       private String lastName;

 

       //getters and setters

       } 

}

 

Once the Entity is created, now we can create a repository interface. It will allow us to handle almost all crud operations  without have to code that directly. 

 

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

import org.springframework.stereotype.Repository;

import springboot.demo.model.Actor;

@Repository

public interface ActorRepository extends JpaRepository< Actor, Long> {   

}

 

 

Now we can use this repository in any controller, only creating an attribute from Repository type and autowiring that:

 

 import java.util.List;

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

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

 

@RestController

public class ActorService {

       @Autowired

       ActorRepository actorRepository;    

       @RequestMapping("/actors")

       String list() {

             List<Actor> list=actorRepository.findAll();

             String output="";

             for(Actor actor: list) {

                    output+=actor.getFirstName()+" "+actor.getLastName()+"<br />";            

             }

             return output;

       }

            

    }

}

 

 

Alternatively we may also interact  with database data using native queries, through Entity manager object:

 @PersistenceContext

private EntityManager entityManager;

 

Once you have the entity manager declared, it is possible to use that inside methods to use native SQL queries through the method createNativeQuery. Below you can see an example which after executing a custom query, the retrieve ResultSet is looped though each record and column.

        @RequestMapping("/native_query")

       String nativeQuery() {

             String queryStr="SELECT first_name, last_name FROM actor where actor_id<?;";

      

             try{

            Query query=entityManager.createNativeQuery(queryStr);

            query.setParameter(1, 10);

            List<Object[]> list=query.getResultList();

            list.stream().forEach((record) -> {

                String firstName = (String) record[0];

                String lastName = (String) record[1];

                output+= firstName+" "+lastName+"<br />";

            });

            return output+="List: "+String.valueOf(list.size());

        } catch(Exception e) {

            e.printStackTrace();

        }

        return "";

       }


You may download the source code here:

https://github.com/rafaelqg/springboot_repository_entity_manager

Watch a video class:




Comments

Post a Comment

Popular posts from this blog

HASHLIB: Using HASH functions MD5 and SHA256

Image
  The usage of hash functions in python is quite simple. Basically we can use the native library hashlib, which give us the possibly to create message digest based on several algorithms, such as MD5 or SHA256. Using this library we may create a hash of some content and then retrieve its binary output or transform that in hexadecimal strings to easy the data transfer in different medias. The library can be imported with the following instruction: import hashlib   If you want to encode strings, it is necessary to encode that with UTF-8, and it can be done using the encode method of a string object: myPassword= "python3.x" myPasswordEncoded=myPassword.encode( 'utf-8' )       The n, the hashlib module has a md5 function which receives as parameter the the we would like to generate the hash. And then return the hash object. hashObject=hashlib.md5(myPasswordEncoded)   The hashObject already contains the ...
Read more

Spread Operator

Image
  The spread operator can be utilized both on arrays and objects. For arrays it will transform the array in a plain list of objects/variables, which can be utilized as input to functions that accept a list of arguments (example: array.push(el1, el2, el3). In This example if we have one array named array01 with options [1,2,3] and we want to add these values to another array, named array02, then we can do array02.push(…array01). Performing that, using the spread operator (…) actually the array02 will have a copy of all elements that are inside array01. Without using the spread operator , for example, array02.push(array01), the array02 will contains one single object that is the array01 itself, and only inside array01 that the values will be found. Another applicability for spread operator is for objects itself. When we want to have a new object with contains a copy of all attributes an existing object already have, in addition to new attributes, we can use the spread operator. L...
Read more

Dart: implementing Object Oriented Programming (OOP)

Image
The object oriented programming provides several benefits for system design. It makes the code easier to maintain, easier to extends, and also implements several well-know design patterns. Using the Dart programing language classes can be simple defined utilizing the class keyword, followed by the class name, and then delimitated the class body with the key brackets {}, like in the example below, presenting one class with two methods. class ProgrammingLanguage {   void compile (){     print( "Compiling..." );   }   void execute (){     print ( "Executing..." );   } } When we need to add new features to this class, it is possible to use the inherence concept, defining a new class that will receive all implementation this ProgrammingLanguage class already has done. And this inherence is done using the extends keywork, like in the example below, which the Dart class has the compile() and the execute() methos from its superclass...
Read more