DEV Community: georgechou

Try To Use `savepoint` In Spring JPA/Hibernate

georgechou — Wed, 19 Jun 2024 17:53:26 +0000

This article describes my failed attempt to use the PostgreSQL SAVEPOINT in Spring JPA.
About PostgreSQL SAVEPOINT.

EntityManager

@PersistenceContext  
private EntityManager entityManager;

@Transactional(propagation=NESTED, isolation= READ_UNCOMMITTED)  
public void transferWithSavePoint(String fromAccount, String toAccount, String anotherAccount,  
                                  double amount) throws SQLException {  
    // step 1  
    Account from = accountRepository.findByName(fromAccount);  
    Branch fromBranch = branchRepository.findByName(from.getBranchName());  

    from.setBalance(from.getBalance().subtract(BigDecimal.valueOf(amount)));  
    fromBranch.setBalance(fromBranch.getBalance().subtract(BigDecimal.valueOf(amount)));  

    accountRepository.save(from);  
    branchRepository.save(fromBranch);  

    Connection connection = entityManager.unwrap(SessionImpl.class).connection();  
    Savepoint savepoint = connection.setSavepoint();  

    // step 2  
    Account to = accountRepository.findByName(toAccount);  
    Branch toBranch = branchRepository.findByName(to.getBranchName());  

    to.setBalance(to.getBalance().add(BigDecimal.valueOf(amount)));  
    toBranch.setBalance(toBranch.getBalance().add(BigDecimal.valueOf(amount)));  

    accountRepository.save(to);  
    branchRepository.save(toBranch);  

    connection.rollback(savepoint);  

    // final step  
    Account finalAccount = accountRepository.findByName(anotherAccount);  
    Branch finalBranch = branchRepository.findByName(to.getBranchName());  

    finalAccount.setBalance(finalAccount.getBalance().add(BigDecimal.valueOf(amount)));  
    finalBranch.setBalance(finalBranch.getBalance().add(BigDecimal.valueOf(amount)));  

    accountRepository.save(finalAccount);  
    branchRepository.save(finalBranch);  
}

I use Connection Pool in this project, so the connection created by EntityManager is different @Transactional method’s connection

savepoint: is before transferWithSavePoint
rollback: rollback to all of the operations before, so STEP1 is rollback, but STEP2 and Final STEP is done.

Next Try: AbstractTransactionStatus

@Autowired  
private JdbcTemplate jdbcTemplate;  

@Autowired  
private TransactionTemplate transactionTemplate;

public void transferWithSavePoint(String fromAccount, String toAccount, String anotherAccount,  
                                  double amount) {  
    transactionTemplate.execute(status -> {  
        Account from = accountRepository.findByName(fromAccount);  
        Branch fromBranch = branchRepository.findByName(from.getBranchName());  

        from.setBalance(from.getBalance().subtract(BigDecimal.valueOf(amount)));  
        fromBranch.setBalance(fromBranch.getBalance().subtract(BigDecimal.valueOf(amount)));  

        accountRepository.save(from);  
        branchRepository.save(fromBranch);  

        Object savepoint = status.createSavepoint();  

        // step 2  
        Account to = accountRepository.findByName(toAccount);  
        Branch toBranch = branchRepository.findByName(to.getBranchName());  

        to.setBalance(to.getBalance().add(BigDecimal.valueOf(amount)));  
        toBranch.setBalance(toBranch.getBalance().add(BigDecimal.valueOf(amount)));  

        accountRepository.save(to);  
        branchRepository.save(toBranch);  

        status.rollbackToSavepoint(savepoint);  

        // final step  
        Account finalAccount = accountRepository.findByName(anotherAccount);  
        Branch finalBranch = branchRepository.findByName(to.getBranchName());  

        finalAccount.setBalance(finalAccount.getBalance().add(BigDecimal.valueOf(amount)));  
        finalBranch.setBalance(finalBranch.getBalance().add(BigDecimal.valueOf(amount)));  

        accountRepository.save(finalAccount);  
        branchRepository.save(finalBranch);  

        status.releaseSavepoint(savepoint);  

        return null;    });  
}

Also failed:

JpaDialect does not support savepoints — check your JPA provider’s capabilities

Then I start to search ‘How to use savepoint in Hibernate’ online:

Please note that this is not a recommended way to use Spring JPA. It’s better to structure your transactions so that you don’t need to use savepoints or nested transactions. If you find yourself needing them, it might be a sign that your transactions are too complex and should be broken down into smaller parts.

Maybe I’m wrong from the beginning, indeed, I won’t encounter it in real application scenarios, but I’m just trying to figure out how to implement it, so let me know if you have any good ideas.

How To Map PostgreSQL `point` Data Type To Java PGpoint Data Type?

georgechou — Tue, 18 Jun 2024 19:32:55 +0000

This article describes using PostgreSQL point data in Spring Boot & Spring JPA/Hibernate projects.

Which Java Data Type should be used for the point Data Type mapped?
Why can’t you use PGpoint directly?
How do you use data types that are not supported in JPA?

Technology

Java 11
Spring Boot 2.x
Spring JPA 2.x
PostgreSQL
Maven

'point' Mapped TO 'PGpoint'

The official PostgreSQL library provides definitions of some special data that we can use directly in our projects.

For examplePGpoint.class:

public class PGpoint extends PGobject implements PGBinaryObject, Serializable, Cloneable {  
    public double x;  
    public double y;  
    public boolean isNull;  
}

Then for columns of type point in Table, you can use PGpoint in Java Model Class:

@Data  
@Entity  
@Table(name = "cities")  
public class City implements Serializable {  

    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    private Long id;  

    private String name;  

    private PGpoint location;  
}

However, when querying the city data, an exception occurs:

Caused by: org.hibernate.type.SerializationException: could not deserialize
 at org.hibernate.internal.util.SerializationHelper.doDeserialize(SerializationHelper.java:243) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
 at org.hibernate.internal.util.SerializationHelper.deserialize(SerializationHelper.java:287) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
 at org.hibernate.type.descriptor.java.SerializableTypeDescriptor.fromBytes(SerializableTypeDescriptor.java:138) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
 at org.hibernate.type.descriptor.java.SerializableTypeDescriptor.wrap(SerializableTypeDescriptor.java:113) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
 at org.hibernate.type.descriptor.java.SerializableTypeDescriptor.wrap(SerializableTypeDescriptor.java:29) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
 at org.hibernate.type.descriptor.sql.VarbinaryTypeDescriptor$2.doExtract(VarbinaryTypeDescriptor.java:60) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
 at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
 at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
 at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
 at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
 at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
 at ...

Reason for Exception

According to the StackCause of the above exception, it fails to deserialize the query result to a Java object. It's using the default AbstractStandardBasicType in Hibernate because there is no type implementation of PGpoint in BasicType, so it can’t do serialization and deserialization.

How to fix this Exception

Hibernate provides an interface for UserType user-defined types.
This interface should be implemented by user-defined “types”. A “type” class is not the actual property type — it is a class that knows how to serialize instances of another class to and from JDBC.

PGpointType

package com.example.demo;  

import org.hibernate.HibernateException;  
import org.hibernate.engine.spi.SharedSessionContractImplementor;  
import org.hibernate.usertype.UserType;  
import org.postgresql.geometric.PGpoint;  
import org.springframework.util.ObjectUtils;  

import java.io.Serializable;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Types;  

public class PGpointType implements UserType {  

    /**  
     * Return the SQL type codes for the columns mapped by this type.     
     * @return int[]  
     */
    @Override  
    public int[] sqlTypes() {  
        return new int[] {  
                Types.VARCHAR  
        };  
    }  

    /**  
     * The class returned by nullSafeGet().
     * @return Class  
     */
    @Override  
    public Class returnedClass() {  
        return PGpoint.class;  
    }  

    @Override  
    public boolean equals(Object o, Object o1) throws HibernateException {  
        return ObjectUtils.nullSafeEquals(o, o1);  
    }  

    @Override  
    public int hashCode(Object o) throws HibernateException {  
        return ObjectUtils.nullSafeHashCode(o);  
    }  

    /**  
     * Retrieve an instance of the mapped class from a JDBC resultset.
     */
    @Override  
    public Object nullSafeGet(ResultSet resultSet, String[] names, SharedSessionContractImplementor sharedSessionContractImplementor, Object o) throws HibernateException, SQLException {  
        if (names.length == 1) {  
            if (resultSet.wasNull() || resultSet.getObject(names[0]) == null) {  
                return null;  
            } else {  
                return new PGpoint(resultSet.getObject(names[0]).toString());  
            }  
        }  
        return null;  
    }  

    /**  
     * Write an instance of the mapped class to a prepared statement.     
     */
    @Override  
    public void nullSafeSet(PreparedStatement preparedStatement, Object o, int i, SharedSessionContractImplementor sharedSessionContractImplementor) throws HibernateException, SQLException {  
        if (o == null) {  
            preparedStatement.setNull(i, Types.OTHER);  
        } else {  
            preparedStatement.setObject(i, o.toString(), Types.OTHER);  
        }  
    }  

    @Override  
    public Object deepCopy(Object o) throws HibernateException {  
        return o;  
    }  

    @Override  
    public boolean isMutable() {  
        return false;  
    }  

    @Override  
    public Serializable disassemble(Object o) throws HibernateException {  
        return (Serializable) o;  
    }  

    @Override  
    public Object assemble(Serializable serializable, Object o) throws HibernateException {  
        return serializable;  
    }  

    @Override  
    public Object replace(Object o, Object o1, Object o2) throws HibernateException {  
        return o;  
    }  
}

Conclusion

The above is how to use the PostgreSQL point Data Type in a Spring Boot project, if there are other custom data types, you can also follow this way to implement.

Implementing an Email Delivery Service with Cloudflare Workers

georgechou — Mon, 17 Jun 2024 18:28:59 +0000

Without using the STMP server or three-party mail forwarding services, only through Cloudflare Workers and Email Routing, it is free to implement a mail delivery service.

Cloudflare Workers and Email Routing are both on a free plan, which is more than enough for individual users.

Prepare

Enable Cloudflare Email Routing

Refer to the official document to enable it.

Deploy Workers

clone/download https://github.com/alivefree/cf-email-workers

 import { Hono } from "hono"
 import { cors } from "hono/cors"
 import { EmailMessage } from "cloudflare:email"
 import { createMimeMessage } from "mimetext"

 const worker = new Hono();

 // cors 
 worker.use('*', (c, next) => {
   const origins = c.env.ALLOWED_ORIGINS == '*' ? '*' : c.env.ALLOWED_ORIGINS.split(',');
   const corsMiddleware = cors(origins);
   return corsMiddleware(c, next);
 });

 // Mail sending interface, can be modified to any api you want
 worker.post('/send', async (c) => {
   const text = await c.req.text();
   const body = JSON.parse(text);
   if (!body['subject'] || !body['body']) {
     c.status(400)
     return c.json({
         "status": "error",
         "message": "Missing subject or body"
     })
   }

   const msg = createMimeMessage()
   msg.setSender({ name: c.env.SENDER_NAME, addr: c.env.SENDER_ADDRESS })
   msg.setRecipient(c.env.RECIPIENT_ADDRESS)
   msg.setSubject(body['subject'])
   msg.addMessage({
       contentType: 'text/html',
       data: body['body']
   })

   var message = new EmailMessage(
       c.env.SENDER_ADDRESS,
       c.env.RECIPIENT_ADDRESS,
       msg.asRaw()
   );

   try {
       // The SEB here comes from the send_email configuration in wrangler.toml
       await c.env.SEB.send(message)
   } catch (e) {
       c.status(500)
       return c.json({
           "status": "error",
           "message": "Email failed to send",
           "error_details": e.message
       });
   }

   return c.json({
       "status": "success",
       "message": "Email sent successfully"
   });

 });

 export default worker;

Modifying Custom Configurations(wrangler.toml)

 # The name configuration here corresponds to c.env.SEB.send in the code, which needs to be synchronized.
 send_email = [
     {type = "send_email", name = "SEB", destination_address = "xxx"},
 ]

 [vars]
 ALLOWED_ORIGINS = "*"
 RECIPIENT_ADDRESS = "*"
 SENDER_ADDRESS = "*"
 SENDER_NAME = "*"

deploy to Cloudflare Workers

$ npm install -g wrangler
$ wrangler login
$ wrangler deploy

Call

After successful deployment, you can call the API to send emails to other projects
API: worker domain/API

fetch('https://example.workers.dev/send', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    subject: 'subject',
    body: 'email content'
  })
})

You can modify the content of the body to customize the email content and make it more aesthetically pleasing.

The Email Worker code deployed in this article has been opened on GitHub.

Spring Boot + Hibernate + PostgreSQL Example

georgechou — Sun, 16 Jun 2024 14:26:06 +0000

This tutorial will build a Spring Boot CRUD Rest API example with Maven that uses Spring Data JPA/Hibernate to interact with the PostgreSQL database.

You’ll know:

How to configure Spring Data, JPA, and Hibernate to work with PostgreSQL Database
Way to use Spring Data JPA to interact with PostgreSQL Database

Technology

Java 11
Spring Boot 2.x
PostgreSQL
Maven

PostgreSQL Set up

Install PostgreSQL in Debian

$ sudo apt-get -y install PostgreSQL

The latter step requires that you first run the psql command.

Create a new DB

$ createdb mydb

Create a new user

$ CREATE USER newuser WITH PASSWORD 'xxxxxx';

Create Table

CREATE TABLE weather (
 id              serial primary key,
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date,
    is_del          int default 0
);
ALTER TABLE weather OWNER TO newuser;

CREATE TABLE cities (
    id              serial primary key,
    name            varchar(80),
    location        point
);
ALTER TABLE cities OWNER TO newuser;

Or, use my test.sql initial Table:

$ mydb=> \i test.sql

Create Spring Boot Project

Use Spring Initializr to create a Maven Spring Boot Project
Add some dependencies to pom.xml:

<dependency>  
    <groupId>org.projectlombok</groupId>  
    <artifactId>lombok</artifactId>  
    <scope>provided</scope>  
</dependency>  

<dependency>  
    <groupId>org.springframework.boot</groupId>  
    <artifactId>spring-boot-starter-web</artifactId>  
</dependency>  

<dependency>  
    <groupId>org.springframework.boot</groupId>  
    <artifactId>spring-boot-starter-data-jpa</artifactId>  
</dependency>  

<dependency>  
    <groupId>org.postgresql</groupId>  
    <artifactId>postgresql</artifactId>  
</dependency>  

<dependency>  
    <groupId>javax.persistence</groupId>  
    <artifactId>javax.persistence-api</artifactId>  
</dependency>

PostgreSQL Configuration

spring.datasource.url=jdbc:postgresql://localhost:5432/mydb  
spring.datasource.username=newuser  
spring.datasource.password=xxxxxx 

# connection timeout  
spring.datasource.hikari.connection-timeout=20000  
# min idle connections  
spring.datasource.hikari.minimum-idle=5  
# max pool size  
spring.datasource.hikari.maximum-pool-size=12  
spring.datasource.hikari.idle-timeout=300000  
spring.datasource.hikari.max-lifetime=1200000  
spring.datasource.hikari.auto-commit=true

For production environments, a single database connection is not enough to solve the real demand, so we need to configure the connection pool here.
By default, jpa-data uses hikari connection pooling, so it only needs to be configured in the application.properties file, no other dependencies are needed.

Define Model Class

Weather.java

package com.example.demo.model;  


import lombok.Data;  
import javax.persistence.*;  
import java.io.Serializable;  
import java.time.LocalDate;  

@Data  
@Entity  
@Table(name = "weather")  
public class Weather implements Serializable {  

    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    private Long id;  

    private String city;  

    private Integer temp_hi;  

    private Integer temp_lo;  

    private Float prcp;  

    private Integer is_del;  

    private LocalDate date;  
}

City.java

package com.example.demo.model;  

import com.example.demo.PGpointType;  
import org.hibernate.annotations.Type;  
import org.hibernate.annotations.TypeDef;  
import org.postgresql.geometric.PGpoint;  
import lombok.Data;  

import javax.persistence.*;  
import java.io.Serializable;  

@Data  
@Entity  
@TypeDef(name = "point", typeClass = PGpointType.class)  
@Table(name = "cities")  
public class City implements Serializable {  

    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    private Long id;  

    private String name;  

    @Type(type = "point")  
    private PGpoint location;  
}

For auto increment id, you need to use GeneratedValue annotate, and the user IDENTITY strategy
Because in hibernate, its do not support PGpoint data type, need to create a customer PGpointType Class

Create Repository Interface

package com.example.demo.repository;  

import com.example.demo.model.Weather;  
import org.springframework.data.domain.Pageable;  
import org.springframework.data.jpa.repository.JpaRepository;  
import org.springframework.data.jpa.repository.Query;  
import org.springframework.data.repository.query.Param;  

import java.util.List;  

public interface WeatherRepository extends JpaRepository<Weather, Long> {  

    @Query("SELECT w FROM Weather w " +  
            " WHERE (:city is NULL OR :city = '' OR w.city = :city)" +  
            " AND w.is_del = 0")  
    List<Weather> listWeather(@Param("city") String city, Pageable pageable);  
}

If use SELECT id, city, temp_hi, temp_lo, prcp, is_del, date FROM weather, the result will be Object[], can’t convert to Weather.class, So I use SELECT w FROM Weather w

Create Controller & Service

Controller

@RestController  
@RequestMapping("/api")  
public class DemoController {
 @Autowired  
 private DemoService demoService;
 //...
}
@Service  
public class DemoService {  

    @Autowired  
    private WeatherRepository weatherRepository;  

    @Autowired  
    private CityRepository cityRepository;

 //...
}

Api Test

Import api-test.json to Postman for API test.

Conclusion

Above are the steps for building a Spring Boot + Hibernate + PostgreSQL example with REST API.

The Source is open on GitHub!