Questions tagged [h2]
1350 questions
1
votes
0
answer
232
Views
How to close H2 SQL with all it's background threads (MVStore)
During memory allocation optimization process for Java application that use embedded H2SQL v1.4.196 i noticed that even after closing all H2 database connections (via 'Connection.close()') and even after trying to execute 'SHUTDOWN' statement, there is still one background H2 thread running - 'MVSto...
1
votes
1
answer
623
Views
Why doesn't Hibernate use the H2 DB's sequence?
I am using Hibernate 5.2.15 and a H2 database (version 1..4.197). I have created a sequence with
CREATE SEQUENCE SEQ_PRIMARYKEY START WITH 1 INCREMENT BY 1;
and my entity looks like
@Entity
@Table(name='Payment',
uniqueConstraints={@UniqueConstraint(columnNames={'ID'})})
public class Payment {
@Id...
1
votes
3
answer
73
Views
SQL to get top 3 from a table with at least one from opposite gender
I have a database table like this
Users
id Capacity Gender
----------------------
1 10 M
---------------------
2 9 M
---------------------
3 4 F
---------------------
4 8 M
---------------------
5 7 F
---------------------
And I want to retrieve the top 3...
1
votes
0
answer
114
Views
H2 database with corrupted table
We have an H2 database with what looks to be a corrupted table. Using the H2 web console, we are able to run SELECT * queries on all the tables except one. For this one table, TBL_TRANSACTION, we get the error result:
SELECT * FROM TBL_TRANSACTION;
General error: 'java.nio.BufferUnderflowException...
1
votes
1
answer
617
Views
Spring Boot Application Tests with H2 Database and Flyway Fails to Seed
I have an application I am trying to test using H2.
Here's my application.yml:
spring:
datasource:
url: jdbc:h2:mem:appdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false
jpa:
database-platform: org.hibernate.dialect.H2Dialect
flyway:
url: jdbc:h2:mem:appdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false
user:...
1
votes
0
answer
117
Views
Migrate h2 database to a postgresql docker in java spring
As titled, I am trying to migrate my h2 database to a postgres docker.
I made a dump of my h2 database using the command
SCRIPT TO 'fileName'
I then copied the file to my docker through the docker cp command. After that, I created a new database, and I tried launching
psql realdb < myfile.sql
which...
1
votes
0
answer
92
Views
H2 password issue after Upgrading to Hibernate 5
I've just upgraded my project from Hibernate 4.2.8 to Hibernate 5.2.12.Final
The only change in terms of configuration is the persistence provider, instead of org.hibernate.ejb.HibernatePersistence it's now org.hibernate.jpa.HibernatePersistenceProvider.
All of a sudden my Unit Tests which use H2 fa...
1
votes
0
answer
166
Views
Spring Boot: 2 in-memory databases, first one initialized by flyway, second manually
I have two H2 databases in my Spring boot application. Each of these databases is responsible for persistence of different entities.
The first one is initialized by Flyway which works fine, but now I need to initialize the other H2 database. I think I have found out that it is impossible to initiali...
1
votes
0
answer
52
Views
jMock test case for Spring JDBC template operations
I am using Spring and JDBC to insert into an Oracle Database using NamedParameterJdbcOperations. I got an error when I tried to write a test case for this operation using jMock and in-memory h2 database
CREATE TABLE DIM_ATTRIBUTE
(
ATTRIBUTE_ID VARCHAR2(45) NOT NUL...
1
votes
1
answer
476
Views
Getting SQL Syntax Error when loading schema.sql file in Spring Boot
I have a pretty basic schema.sql file:
Ideally, I just want the tables to auto-generate. I've disabled the auto ddl setting in my application.properties. However, I am getting errors:
Error:
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource...
1
votes
0
answer
170
Views
how to restore h2 database in java
How to restore a backup of h2 database in java. I store the backup on
statement.execute(String.format('SCRIPT TO '%s'','path.sql'));
but I don't know how to restore this backup I tried
statement.execute(String.format('RUNSCRIPT FROM '%s'', 'path.sql'));
but it gives me an error:
org.h2.jdbc.JdbcSQL...
1
votes
0
answer
256
Views
Error with type in base H2 for Instant type with Java 8
I have an entity
public class UserEntity {
...
@Basic(optional = false)
@Column(name = 'created', nullable = false, updatable = false)
@CreatedDate
private Instant created;
@Basic
@Column(name = 'modified_date', nullable = false)
@LastModifiedDate
private Instant modifiedDate;
...
}
I create the tab...
1
votes
1
answer
157
Views
hibernate.hbm2ddl.auto does not link sequence to id column
Question
Why I get NULL not allowed for column 'ID' exception when I execute INSERT INTO PUBLIC.MY_ENTITY (name) VALUES ('test name');?
Setup
I'm using Spring Boot and Hibernate. Spring Boot is launched with properties:
hibernate.hbm2ddl.auto=update
spring.jpa.hibernate.ddl-auto=update
I have entit...
1
votes
0
answer
118
Views
Spring boot test how to import data.sql just once during of h2 testing
I want to test my spring boot application via h2 in memory database where I would like to use:
spring.jpa.hibernate.ddl-auto: update
However, once I want to run tests with this command:
mvn clean test
it will show some errors which are related to constraint violations (ID must be unique).
These one...
1
votes
1
answer
414
Views
How to map an id column from a H2 database in java
I am trying to create a data table and map this table to an entity using a H2 in-memory database. I am using spring-boot (release 2.0.2) and flyway to create the database before running.
For some reason I am not successful to map the identity column when hibernate is to validate against the schema....
1
votes
1
answer
49
Views
Id not saved automatically in spring boot
I am creating a simple program in spring boot using JPA to show student and phone number relationship.
CODE
Student Entity
package student.entity;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import ja...
1
votes
0
answer
246
Views
java spring H2 merge sql syntax error
I cant figure out what is wrong with this merge statement. It was working fine then I added COL2 in the statement, following the same pattern I believed but I get this error which seems to indicate it comes from aliasing the table?
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statem...
1
votes
0
answer
70
Views
jump-ahead clocks with Oracle timestamps on Java 10
We have an application that has unit tests using H2 and integration tests using Oracle 11. All tests were passing using Java 8. We have a Foo table that has a created_at timestamp using SQL DDL created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL, which we use both on Oracle and on H2 in Oracle mode....
1
votes
2
answer
500
Views
“SQLServerException: String or binary data would be truncated” issue - Configured H2 DB to diagnose but column size change not taking effect?
What?
When investigating an SQLServerException: String or binary data would be truncated issue in a Java Spring Boot app, I configured H2 as an alternative to give me some better diagnostic error messages and more flexibility in recreating the scenario.
TLDR?
Skip to And the problem is...
How did...
1
votes
2
answer
398
Views
spring.h2.console.settings.web-allow-others not working for me to disable access
I am trying to set spring.h2.console.settings.web-allow-others=false so that the console is only available on the local machine. It works great if I run my application in IntelliJ, but as soon as I build the jar and run it, this setting seems to have no effect and defaults to enabled.
I don't belie...
1
votes
1
answer
57
Views
Integration test for DB H2 and for controller
I have two integration test in my program and unfortunately both doesn't work. I don't know this is good idea to write both problem in one case but i try.
Firstly i show my db integration test :
@RunWith(SpringRunner.class)
@DataJpaTest
public class TeamDatabaseIntegrationTest {
@MockBean
private Te...
1
votes
1
answer
180
Views
Liquibase detects h2 as database even when h2 runs in MySQL mode [Spring boot]
I am trying to run the tests of my Spring Boot application using liquibase and h2 (in mysql mode). The Liquibase changelogs are MySQL specific so I was thinking that testing with liquibase enabled and h2 in MySQL mode would do the trick.
Problem is that Liquibase is not detecting the database as MyS...
1
votes
1
answer
96
Views
Get unique date from timestamps using h2 or hsqldb
I can get the result I want using
SELECT DISTINCT(DATE(loginTimeStamp)) FROM login; in mySQL, how to do the same using h2 database or hsqldb
1
votes
1
answer
222
Views
How to convert Array datatype(H2 database) to java List<Long>?
I tried this
Array a = resultSet.getArray('IDS');
Object[] idsArray = (Object[]) a.getArray();
List idsList = new ArrayList();
for(Object obj : idsArray) {
idsList.add((Long) obj); -----> (java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer)
}
It's throwing exception....
1
votes
0
answer
46
Views
What kind of auto incremant the “In-Memory” H2 database use?
My Spring Boot 2 / Hibernate 5 application is running some integration tests against the H2 server version 1.4.197.
What kind of auto increment is the database server using ?
a table keeping a number, global to all tables
an 'hibernate_sequence' sequence, global to all tables ?
some auto increment c...
1
votes
1
answer
34
Views
Can not open H2Spatial Database anywhere
I have a H2 database that use h2spatial functions.
but when i try open it , it cause an error like this :
Database driver not found
See in the Help for how to add drivers
Class 'org.h2gis.h2spatial.internal.function.spatial.properties.ST_GeometryTypeCode' not found; SQL statement:
CREATE DOMAIN MUL...
1
votes
1
answer
175
Views
How can I connect H2 database as embedded mode on Android platform by using Gluon?
I want to use H2 database to store database on my Gluon Mobile application. I have try JDBC it work well on Desktop version. but I got failed on Android (org.h2.jdbc.JdbcSQLException: Error while creating file).
I have change connection like the following url, but not luck
http://www.h2database.com/...
1
votes
0
answer
79
Views
H2 Database - how to protect a column?
From a previous H2 question here: How to create a secured field in H2 database?
I gather that I need to put cipher aes to make a field encrypted. But that does not work.
The statement:
CREATE TABLE IF NOT EXISTS MyTable(
myPK varchar(256),
protectedField blob cipher aes
...
);
returns:
CREATE TABLE...
1
votes
0
answer
292
Views
Error in flyway migrations on h2Database since upgrading to spring boot v2
I have searched for solutions to this and haven't found anything. I have a service performing migrations via flyway. As part of integration tests for this service we use h2 database. Previously all tests and migrations worked on the h2 database when based off of Spring Boot version 1.5.9.RELEASE
Sin...
1
votes
0
answer
32
Views
H2: Load database from multiple sources
Is there any way to pre-populate an H2 Database using multiple sources? I've only found examples of RUNSCRIPT with a single source. I am looking for something along the lines of the code below, but in this case, H2 seems to be looking for a file called '*.sql'.
1
votes
1
answer
410
Views
H2-database exception “Caused by: org.h2.jdbc.JdbcSQLException: Sequence ”SYSTEM_SEQUENCE_*“ not found;” while running Junit test case
I am getting an error while executing junit test case, saying Caused by: org.h2.jdbc.JdbcSQLException: Sequence 'SYSTEM_SEQUENCE_BBAED672_9E78_4AC7_9306_10A675C3C8D2' not found; SQL statement
This is anywhere not related to the project I am working on currently but was there in my workspace. I have...
1
votes
1
answer
365
Views
Create a user that can connect to the database in H2
To create a user in H2, one does:
CREATE USER NEWUSER PASSWORD 'P';
How is it possible to create a new user that can connect to an existing database in H2?
I have tried:
GRANT ALL TO NEWUSER;
but this does not work.
Any ideas?
1
votes
0
answer
42
Views
H2 slow query with in clause does not use indexes
I have embedded db with in memory config:
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;LOCK_TIMEOUT=4000
I have following query what is constructed with hibernate criteria api, when I execute it executes forever, and also it locks all h2 operation i can't read and write.
Largest table...
1
votes
2
answer
251
Views
H2 - duplicate key not throwing exception in Spring boot test when Persistable<ID> isNewObject set to true
I am using H2 as test DB in a Java Spring Boot application, but when I want to catch a 'duplicate key' exception when trying to insert an duplicate ID/PK, H2 does not throw anything.
With Postman all is fine and nice, I just cannot pass the test.
The real DB is PostgreSQL, and it does throw exceptio...
1
votes
1
answer
692
Views
Syntax error in SQL statement org.h2.jdbc.JdbcSQLException:
Hi this is my repository
@Repository
interface GuestRepository : JpaRepository {
@Query('from Guests g where g.date>=:fromDate and g.date=? AND G.DATE
1
votes
0
answer
41
Views
Apply CTE function consolidating strings to every row in a H2 table
I have a table with long strings in one column which I want to consolidate into an easier to read format ('abc;abc;abc;efg;hij;klm;klm;klm' -> 'abc: 3, efg: 1, hij: 1, klm: 3').
I have written a function that consolidates the string, but now I want to apply it to every entry in a table. Any suggesti...
1
votes
2
answer
1.5k
Views
SpringBoot - Testing with Flyway and H2 database
I'm coding acceptance tests with Cucumber, and I want to use a H2 database for the tests.
The application-test.properties looks like:
server.port:8090
spring.jpa.database=H2
spring.database.driverClassName=org.h2.Driver
spring.datasource.url:jdbc:h2:mem:database_user;DB_CLOSE_ON_EXIT=FALSE
flyway.lo...
1
votes
1
answer
148
Views
How to enable XA for Springboot H2 datasource?
What should I change in the below spring-boot H2 datasource config, to make sure XA is enabled?
@Bean
//@ConfigurationProperties(prefix = 'spring.datasource')
public DataSource h2() {
String url = 'jdbc:h2:mem:mydatabase;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE';
return DataSourceBuilder.create()
.u...
1
votes
0
answer
285
Views
Two databases configured in spring boot application
I am trying to use 2 DB in my spring app. From the first I read data and in the second I save the data. The problem is that my second DB it is not constructed.
application.properties
fromDB.datasource.url=jdbc:h2:file:D:/test1/db1
fromDB.datasource.username=sa
fromDB.datasource.password=
fromDB.data...
1
votes
0
answer
181
Views
problem with the relationship many to many in jhipster
I have a very painful error and i was searching for a solution since four days . So i want to do a many to many relationship with the jhipster commands between two entities folder and client , and the folder entity is the holder of the relationship . so after creating the relationship i tried to pos...