Java enum to sql

Convert between Java enums and PostgreSQL enums

PostgreSQL allows you to create enum types using the following syntax:

CREATE TYPE animal_type AS ENUM('DOG', 'CAT', 'SQUIRREL');

You can now use ‘animal’ as a datatype in your tables, for example:

create table pet ( pet_id integer not null, pet_type animal_type not null, name varchar(20) not null );

In Java, you’d have a corresponding enum type:

Converting between Java and PostgreSQL enums is straightforward. For example, to insert or update an enum field you could use the CAST syntax in your SQL PreparedStatement:

INSERT INTO pet (pet_id, pet_type, name) VALUES (?, CAST(? AS animal_type), ?); --or INSERT INTO pet (pet_id, pet_type, name) VALUES (?, . animal_type, ?);

Postgres will also let you insert/update an enum just by passing its value as a string.

Whether casting or not, the Java side is the same. You would set the fields like this:

stmt.setInt(1, 1); stmt.setString(2, AnimalType.DOG.toString()); stmt.setString(3, 'Rex');

Retrieving the enum from a SELECT statement looks like this:

AnimalType.valueOf(stmt.getString("pet_type"));

Take into consideration that enums are case-sensitive, so any case mismatches between your Postgres enums and Java enums will have to be accounted for. Also note that the PostgreSQL enum type is non-standard SQL, and thus not portable.

Also, FYI, to view the set of values in a given Postgres enum type, you can use the following SQL query:

SELECT enumlabel FROM pg_enum WHERE enumtypid = 'your_enum'::regtype ORDER BY oid;

While this looks great for regular Java, I’m using JPA and was relying upon EntityManager simply persisting my entities but it throws exception: @Entity
@Table(name=”myentity”)
public class MyEntity < @Id
@SequenceGenerator(name = “entitySequence”, sequenceName = “myentity_seq”, allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = “entitySequence”)
private int id; private int age; @Enumerated(EnumType.STRING)
@Column(name=”gender_type”, columnDefinition=”GENDER_ENUM”)
private GenderType gender; … /* getters and setters */
> **********************************
@Test
public void test1_Create() MyEntity myentity = new MyEntity();
myentity.setAge(20);
myentity.setGender(GenderType.Male); try EntityManager em = JpaUtils.create();
em.getTransaction().begin();
em.persist(myentity);
em.getTransaction().commit(); …
> **********************************
Caused by: org.postgresql.util.PSQLException: ERROR: column “gender_type” is of type gender_enum but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 57
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101) ********************************** JPA v2.0
PG v9.1
PG JDBC 9.1-902
Java 6
Eclipse Juno Any ideas? Thx
-Rob Reply

Share your thoughts, post a comment.

Tag Cloud

Your Link Here

If you maintain a technology/programming blog, we may benefit from exchanging links. If you are game, my contact info is on the About page.

Popular Posts

• Convert between Java enums and PostgreSQL enums
• Resize images in Java, preserving image quality
• Highlight words in text with jQuery
• Dell Inspiron 17R / N7010 Review
• How to load an image from a URL in java
• How to extract titles from web pages in Java
• Really Cool Glossy Progress/Result Bars in Pure CSS
• Disable URL session IDs (JSESSIONID) in Tomcat 7, Glassfish v3
• Royalty-free state flag icons
• Selecting Rows With Chinese Characters in MySQL
• Log SQL Statements With Parameter Values Filled In (Spring JDBC)
• Create thumbnails and avatars in Java
• Simple pagination taglib for JSP
• Using word clouds for SEO keyword analysis
• Web scraping in Java with Jsoup, Part 1

About

Doing web development since 1998.

Pages

Search

Copyright © 2023 !. All rights reserved.

Источник

Как сохранить Enum в БД используя JPA?

В этом уроке я хочу вам показать как сохранять такие объекты как Enum в базу данных используя Java Persistecr API.

Данный урок является легкой сложности, но я решил что он будет полезен вам.

Как создать WEB-проект с использованием JPA я не буду показывать, так как уже показывал это тут ‘JPA работа с базой данных‘.

Шаг 1

Давайте создадим сущность User.java:

package com.devcolibri.entity; import org.hibernate.annotations.GenericGenerator; import javax.persistence.*; import java.io.Serializable; import java.util.Set; @Entity @Table(name = "User") public class User implements Serializable < @Id private long id; @Column(name="login") private String login; @Column(name="email", nullable = false) private String email; @Column(name="password") private String password; @Column(name="first_name") private String firstName; @Column(name="last_name") private String lastName; public User() < >public String getLogin() < return login; >public void setLogin(String login) < this.login = login; >public long getId() < return id; >public void setId(long id) < this.id = id; >public String getEmail() < return email; >public void setEmail(String email) < this.email = email; >public String getPassword() < return password; >public void setPassword(String password) < this.password = password; >public String getFirstName() < return firstName; >public void setFirstName(String firstName) < this.firstName = firstName; >public String getLastName() < return lastName; >public void setLastName(String lastName) < this.lastName = lastName; >>

Как видите пока в этой сущности не используется Enum. В каком случае нам нужно будет использовать Enum?

Например у пользователя (User) должна быть роль, вот для этого мы и будем использовать Enum.

Шаг 2

Давайте создадим Enum -> RoleEnum.java:

package com.devcolibri.entity.enums; public enum RoleEnum < ANONYMOUS, USER, ADMIN; RoleEnum() < >>

Теперь у нас есть enum с перечислением ролей пользователя.

Шаг 3

Теперь давайте добавим в сущность User роль:

package com.devcolibri.entity; import org.hibernate.annotations.GenericGenerator; import javax.persistence.*; import java.io.Serializable; import java.util.Set; @Entity @Table(name = "User") public class User implements Serializable < @Id private long id; @Column(name="login") private String login; @Column(name="email", nullable = false) private String email; @Column(name="password") private String password; @Column(name="first_name") private String firstName; @Column(name="last_name") private String lastName; @Column(name = "role") @Enumerated(EnumType.STRING) private RoleEnum role; public User() < >public String getLogin() < return login; >public void setLogin(String login) < this.login = login; >public long getId() < return id; >public void setId(long id) < this.id = id; >public String getEmail() < return email; >public void setEmail(String email) < this.email = email; >public String getPassword() < return password; >public void setPassword(String password) < this.password = password; >public String getFirstName() < return firstName; >public void setFirstName(String firstName) < this.firstName = firstName; >public String getLastName() < return lastName; >public void setLastName(String lastName) < this.lastName = lastName; >public RoleEnum getRole() < return role; >public void setRole(RoleEnum role) < this.role = role; >>

Как видите для того чтобы добавить Enum в Entity нам потребовалось просто проаннотировать атрибут enum аннотацией @Enumerated.

Теоретический материал

Как вы уже видели аннотация @Enumerated – принимает параметр типа EnumType:

1. EnumType.STRING – это значит, что в базе будет хранится имя этого enum.

То есть если мы зададим role = RoleEnum.ADMIN, то в БД в поле role будет хранится значение ADMIN.

2. EnumType.ORDINAL – это значит, что в базе будет хранится ID этого enum. ID – это место расположение в списке перечисления начиная с 0.

Например если значение enum равно ADMIN, то в базе будет хранится число 2, а если будет ANONYMOUS, то в базе будет хранится 0.

Источник

Java ENUM Mapping in postgres using Spring Data JPA and Hibernate.

In this article, you are going to learn how to map Java enum in the Postgres database using ORM implementation such as Hibernate with Spring Data JPA.

If you don’t know how to set up Postgres using docker in local, please see the below video.

Postgres setup in local using docker.

Introduction:

Let us set the plot, you have a requirement to create a custom type such as product type, in PostgreSQL, Let’s call it product_type. It’s an enum defined as follows: electronics, cosmetic, edible, etc.

Once you have created this product_type, then while inserting data into the products table with some fields like id , product_name , price , tag , and product_type .

Let us create the project go to start.spring.io OR use any editor or IDE.

Create simple application with Controller, Service and Data access layer.

DB Script:

Database script to create the table, enum type and sample insert script for record insertion.

 CREATE TABLE IF NOT EXISTS public.products ( id bigint NOT NULL, product_name text COLLATE pg_catalog."default", price bigint, tag text COLLATE pg_catalog."default", product_type product_type, CONSTRAINT products_pkey PRIMARY KEY (id) ); -- insert some records INSERT INTO public.products( id, product_name, price, tag, product_type) VALUES (?, ?, ?, ?, ?); -- enum type CREATE TYPE product_type AS ENUM ('electronics', 'cosmetic', 'edible'); 

Properties:

Properties file contains properties to make database connection.

## default connection pool spring.datasource.hikari.connectionTimeout=20000 spring.datasource.hikari.maximumPoolSize=5 ## PostgreSQL spring.datasource.url=jdbc:postgresql://localhost:5432/postgres spring.datasource.username=postgres spring.datasource.password=pass #drop n create table again, good for testing, comment this in production spring.jpa.hibernate.ddl-auto=update spring.jpa.show-sql=true 

Implementation:

In this example, created sample Controller, service and data access layer coding. To handle the enum in java, you have to first creat the custom enumtype by extending the hibernate EnumType .

public class PgSQLEnumType extends EnumType< public void nullSafeSet( PreparedStatement ps, Object value, int index, SharedSessionContractImplementor session ) throws HibernateException, SQLException< ps.setObject(index,value!=null? ((Enum)value).name():null,Types.OTHER); > > 

Then you have to create the Java enum which will represent, your Postgres enum.

Once this is done then, you can go ahead and write down the entity class with annotation @TypeDef .

@Entity @Table(name = "products") @TypeDef( name = "product_type", typeClass = PgSQLEnumType.class ) public class Product < @Id @GeneratedValue(strategy = GenerationType.AUTO) private Long id; private String product_name; private float price; private String tag; @Enumerated(EnumType.STRING) @Column(name = "product_type") private ProductType type; //setter and getter > 

On the entity layer, you have to add @TypeDef annotation with two attribute fields, the first one is the name of the column and the second one is the type of EnumType class name.

These three parts are going to help you to handle the enum efficiently in Postgres with spring boot and Spring Data JPA.

Conclusion:

In this article, you learned how to handle enums efficiently in Spring Data JPA and hibernate. This is the one way, which I know, if you know or use any other best way, Let me know in the comment section below.

Complete Source Code:

Download the complete source code.

git clone https://github.com/maheshwarLigade/spring-boot-examples cd spring-boot-examples\postgresdemo -- run the spring boot Gradle project 

More such articles:

If this article adds any value for you then please clap and comment.

Did you find this article valuable?

Support techwasti by becoming a sponsor. Any amount is appreciated!

Источник