Oc-windows.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Java sql array

Java sql array

The Array interface provides methods for bringing an SQL ARRAY value’s data to the client as either an array or a ResultSet object. If the elements of the SQL ARRAY are a UDT, they may be custom mapped. To create a custom mapping, a programmer must do two things:

  • create a class that implements the SQLData interface for the UDT to be custom mapped.
  • make an entry in a type map that contains
    • the fully-qualified SQL type name of the UDT
    • the Class object for the class implementing SQLData

When a type map with an entry for the base type is supplied to the methods getArray and getResultSet , the mapping it contains will be used to map the elements of the ARRAY value. If no type map is supplied, which would typically be the case, the connection’s type map is used by default. If the connection’s type map or a type map supplied to a method has no entry for the base type, the elements are mapped according to the standard mapping.

All methods on the Array interface must be fully implemented if the JDBC driver supports the data type.

Method Summary

Methods

Modifier and TypeMethod and Description
voidfree()
ObjectgetArray()ObjectgetArray(long index, int count)ObjectgetArray(long index, int count, Map > map)ObjectgetArray(Map > map)intgetBaseType()StringgetBaseTypeName()ResultSetgetResultSet()ResultSetgetResultSet(long index, int count)ResultSetgetResultSet(long index, int count, Map > map)ResultSetgetResultSet(Map > map)

Method Detail

getBaseTypeName

getBaseType

getArray

Note: When getArray is used to materialize a base type that maps to a primitive data type, then it is implementation-defined whether the array returned is an array of that primitive data type or an array of Object .

getArray

Note: When getArray is used to materialize a base type that maps to a primitive data type, then it is implementation-defined whether the array returned is an array of that primitive data type or an array of Object .

getArray

Note: When getArray is used to materialize a base type that maps to a primitive data type, then it is implementation-defined whether the array returned is an array of that primitive data type or an array of Object .

getArray

This method uses the specified map for type map customizations unless the base type of the array does not match a user-defined type in map , in which case it uses the standard mapping. This version of the method getArray uses either the given type map or the standard mapping; it never uses the type map associated with the connection.

Note: When getArray is used to materialize a base type that maps to a primitive data type, then it is implementation-defined whether the array returned is an array of that primitive data type or an array of Object .

getResultSet

The result set contains one row for each array element, with two columns in each row. The second column stores the element value; the first column stores the index into the array for that element (with the first array element being at index 1). The rows are in ascending order corresponding to the order of the indices.

getResultSet

The result set contains one row for each array element, with two columns in each row. The second column stores the element value; the first column stores the index into the array for that element (with the first array element being at index 1). The rows are in ascending order corresponding to the order of the indices.

getResultSet

The result set has one row for each element of the SQL array designated by this object, with the first row containing the element at index index . The result set has up to count rows in ascending order based on the indices. Each row has two columns: The second column stores the element value; the first column stores the index into the array for that element.

getResultSet

The result set has one row for each element of the SQL array designated by this object, with the first row containing the element at index index . The result set has up to count rows in ascending order based on the indices. Each row has two columns: The second column stores the element value; the first column stroes the index into the array for that element.

After free has been called, any attempt to invoke a method other than free will result in a SQLException being thrown. If free is called multiple times, the subsequent calls to free are treated as a no-op.

  • Overview
  • Package
  • >Java™ Platform
    Standard Ed. 7
  • Summary:
  • Nested |
  • Field |
  • Constr |
  • Method
  • Detail:
  • Field |
  • Constr |
  • Method

Submit a bug or feature
For further API reference and developer documentation, see Java SE Documentation. That documentation contains more detailed, developer-targeted descriptions, with conceptual overviews, definitions of terms, workarounds, and working code examples.
Copyright © 1993, 2018, Oracle and/or its affiliates. All rights reserved. Use is subject to license terms. Also see the documentation redistribution policy.

Java Code Examples for java.sql.Array

The following code examples are extracted from open source projects. You can click to vote up the examples that are useful to you.

From project ANNIS , under directory /annis-service/src/main/java/annis/sqlgen/ .

From project ANNIS , under directory /annis-service/src/test/java/annis/sqlgen/ .

From project ANNIS , under directory /annis-service/src/test/java/annis/sqlgen/ .

From project ANNIS , under directory /annis-service/src/test/java/annis/sqlgen/ .

From project bonecp , under directory /bonecp/src/main/java/com/jolbox/bonecp/ .

From project components-ness-jdbi , under directory /src/main/java/com/nesscomputing/jdbi/argument/ .

From project components-ness-jdbi , under directory /src/main/java/com/nesscomputing/jdbi/ .

From project components-ness-jdbi , under directory /src/main/java/com/nesscomputing/jdbi/ .

From project components-ness-jdbi , under directory /src/main/java/com/nesscomputing/jdbi/ .

From project ned , under directory /NEDAdminConsole/src/org/ned/server/nedadminconsole/datasource/ .

From project bonecp , under directory /bonecp/src/test/java/com/jolbox/bonecp/ .

From project Amoeba-for-Aladdin , under directory /src/java/com/meidusa/amoeba/jdbc/ .

From project bonecp , under directory /bonecp/src/main/java/com/jolbox/bonecp/ .

From project bonecp , under directory /bonecp/src/main/java/com/jolbox/bonecp/ .

From project ha-jdbc , under directory /src/main/java/net/sf/hajdbc/sql/ .

From project ned , under directory /NEDCatalogTool2/src/org/ned/server/nedcatalogtool2/datasource/ .

Блог только про Java

Учимся программировать на Java с нуля

Расширенные типы данных в языке SQL

Тип ARRAY языка SQL представляет собой последовательность значений. Например, таблица Student может иметь столбец с оценками Scores типа ARRAY OF INTEGER, то есть с массивом целочисленных значений. Для возвращения данных типа java.sql.Array используется метод getArray(). В интерфейсе java.sql.Array также предусмотрены методы извлечения значений массива.

Тип данных SQL Тип данных Java
INTEGER или INTInt
SMALLINTShort
NUMERIC, DECIMAL или DECjava.math.BigDecimal
FLOATDouble
REALFloat
DOUBLEDouble
CHARACTER или CHARString
VARCHAR, LONGCHARString
BOOLEANBoolean
DATEjava.sql.Date
TIMEjava.sql.Time
TIMESTAMPjava.sql.TimeStamp
BLOBjava.sql.Blob
CLOBjava.sql.clob
ARRAYjava.sql.Array
ROWIDjava.sql.RowId
NCHAR, NVARCHAR, LOG NVARCHARString
NCLOBjava.sql.NClob
SQLXMLjava.sql.SQLXML

При получении LOB-обьекта или массива из базы данные фактически доставляются только после запроса отдельных значений. Это сделано для повышения эффективности работы. Дело в том, что размер объектов может оказаться очень большим. Некоторые базы данных поддерживают значения ROWID, которые описывают местонахождения строки, благодаря чему ее можно извлечь очень быстро. JDBC 4 содержит интерфейс java.sql.RowId и предлагает методы для передачи идентификатора(ID) строки в запросы и извлечения его из результатов.

Строка национальных символов(NCHAR — national character string, и его варианты) хранит строки в локальной кодировке символов и сортирует их согласно условиям локальной сортировки. JDBC 4 предлагает методы для преобразования между объектами Java String и строками национальных символов в запросах и результатах.

В некоторых базах данных допускается хранение типов, определенных пользователем. В JDBC 3 поддерживается механизм автоматического отображения структурных типов SQL в Java-объекты. Скоро планируется написать серию материалов на тему разработка интернет магазина на Java с использованием SQL, для укрепления сегодняшнего материала.

Некоторые базы данных предлагают естественное хранение данных XML. JDBC 4 предлагает интерфейс SQLXML, который может быть связующим звеном между внутренним представлением XML и интерфейсами DOM Source/Result, а также двоичными потоками. Подробнее о классе SQLXML можно найти в документации по API.

Vlad Mihalcea

Posted on June 21, 2017 by vladmihalcea

How to map Java and SQL arrays with JPA and Hibernate

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

In this article, we are going to see how you can map SQL arrays to JPA entity attributes when using Hibernate.

Hibernate custom Types allow you to map all sorts of database-specific column types, like IP address, JSON columns, bit sets, or SQL arrays.

However, while you can create your own custom Hibernate Types, for mapping PostgreSQL arrays, you don’t need to implement your own Hibernate Type. All you need to do is use the Hibernate Types open-source project.

Database table

Considering we have the following table in our PostgreSQL database schema:

We want to map this table using JPA and Hibernate. However, neither JPA nor Hibernate support SQL arrays by default, and we want to map these arrays to a String and int Java arrays, respectively. So, we are going to use the Hibernate Types project to achieve this goal.

Maven dependency

The first thing you need to do is to set up the following Maven dependency in your project pom.xml configuration file:

JPA entity mapping

The JPA mapping for the event database table will look as follows:

The string-array and int-array are custom types which can be defined in the BaseEntity superclass:

The StringArrayType and IntArrayType are classes offered by the Hibernate Types project.

Testing time

When persisting these two Event entities:

Hibernate is going to generate the following SQL statement:

Great! Now, we can also update the Event entities too:

And Hibernate is going to generate the following SQL UPDATE statement:

Since the Hibernate array types are mutable, we can even update them without overriding the array object:

Hibernate generating the appropriate UPDATE SQL statement:

I’m running an online workshopk on the 14th of May about The Best Way to Fetch Data with Java Persistence and Hibernate.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

While JPA defines the AttributeConverter interface, the standard converters are way too limited when it comes to mapping arrays or JSON types. That’s why the Hibernate Types project uses the Hibernate API to provide additional column type mappings.

Related

22 Comments on “ How to map Java and SQL arrays with JPA and Hibernate ”

Vlad thank you very much for doing everything possible to make programming a great universe of possibilities, all your tutorials have helped me.

You are very welcome.

Does this support other database as well? Aside from PostgresSql (oracle, mysql, etc.)?

MySQL and Oracle do not support ARRAY column types. Only PostgreSQL has support for ARRAY columns.

Many thanks for this.

I’ve been wrestling with plugging into JPA with pure XML persistence_2_1.xsd configuration.

What I found is that I can make it work if I declare:

inside persistence.xml , and then declare myjpa.MBcontrib as:

Putting out there in case it is helpful to somebody else.

I also explained how to set up the MetadataBuilderContributor in this article.

Perhaps we should provide a basic implementation for MetadataBuilderContributor in hibernate-types? Although some of the types may not be able to load due to missing classes on the classpath. We’d have to use some reflection for that. On the other hand, pointing people to the MetadataBuilderContributor may make hibernate-types easier to use. The typedefs grow quite old after a while…

We could perhaps even register the MetadataBuilderContributor using the service provider API, although this might have too many side effects.

Yes, I think this is a good idea. There could be a pre-defined MetadataBuilderContributor containing all supported types. I’ll add an issue to it.

Hi Vlad, thanks for your great Library. I am new to Jpa. I am using Java 11 with Postgres.
I am trying to use a JPQL where clause with a int[] field like “where a in :variable” where a is declared as a int[] table field, exactly as your example.

I am getting the following error message:

java.lang.ClassCastException: class beans.IntArrayTypeDescriptor cannot be cast to .beans.AbstractArrayTypeDescriptor .beans.IntArrayTypeDescriptor and .AbstractArrayTypeDescriptor are in unnamed module of loader org.springframework.boot.devtools.restart.classloader.RestartClassLoader @9566fe3)

If I remove this JPQK where clause argument, I have my resultset as it should. So, I am only struggling with the int[] in where clause. Any hint?

Looks like a class loading issue. I’m not very familiar with the Java module system, so I don’t know what the problem is.

But a Java list is resizeable and an array is not. A Postgres array is also resizeable; feels like a List would be a better fit than arrays for mapping Postgres arrays.

It’s not difficult to add a new type that maps a List to a PostgreSQL ARRAY. If you are interested in this feature, send me a Pull Request with a proposal and I’ll review it when it’s done.

Hi Vlad, StringArrayTypeDescriptor class is missing
public static final StringArrayTypeDescriptor INSTANCE = new StringArrayTypeDescriptor();
in latest version

You only need to use StringArrayType. The descriptor is used implicitly, so you don’t need it on your code, although you can find it there as well.

Hi,
When I add :
@Column(columnDefinition = “integer[]”)
private int[] days;

hmb2ddl creates:
alter table if exists “access_control_permissions”
add column “days” “integer[]”

And coulmn cannot be created because the “integer[]” should not be in double quotations.
I am using Hibernate 5.4.3, Postgrsql 9.6 and set dialect to: “org.hibernate.dialect.PostgreSQL95Dialect”
Could you please help me

If you run the tests in the hibernate-types GitHub repository, you will see that the table is generated without any quotes:

Try to compare why my test works fine and yours does not.

I am newbee to JPA. Could you please describe, how to persist int[][] (two-dimesional array of int-type)
Thanks in advice!

Check out the associated GitHub repository and inspect the tests. I haven’t tested this use case, but you can try it and see how it works. If it doesn’t, send me a Pull Request with a fix. Cheers.

I am going to pass to EntityManager already assembled/populated two-dimensional array.
Therefore, I believe, that mentioned code in the article above should be rewritten slightly for my particular case, since it’s like Object.

Please, don’t get me wrong, due to my lack of expirience and knowledge.

You will have to try it, yes.

When I pass to hibernate two-dimensional Long[][] and configured classes as in upper article, Logger prints:

19:05:21 PM [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder – binding parameter [1] as [ARRAY] – [[[Ljava.lang.Long;@3eabe84a]
19:05:21 PM [main] WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper – SQL Error: 0, SQLState: 42602
19:05:21 PM [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper – Unable to find server array type for provided name object.

Try to debug it and see what causes the problem. Once you figure it out, you need to send a Pull Request with a fix. Looking forward to it.

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Читать еще:  Math min java
Ссылка на основную публикацию
Adblock
detector