FAQ Database Discussion Community


Embedding an H2 Database within the WEB-INF Directory

jdbc,h2,web-inf
I have an embedded H2 Database I'd like to put in the WEB-INF directory of a web application. What is the proper way to refer to this in a JDBC url? Ideally I'd like a solution that would work both for a WAR, and an expanded WAR (If possible). Thank-you...

Find all tables in a database with Java

java,mysql,database,resultset,h2
I'm trying to get all the table names in my database back through Java but I am having problems using the Result Set. The below works and I can see the info listed when I inspect the result set. How do i get the table names from the result set...

H2 - How to create a database trigger that log a row change to another table?

java,sql,triggers,h2,jooq
How to create a database trigger that log a row change to another table in H2? In MySQL, this can be done easily: CREATE TRIGGER `trigger` BEFORE UPDATE ON `table` FOR EACH ROW BEGIN INSERT INTO `log` ( `field1` `field2`, ... ) VALUES ( NEW.`field1`, NEW.`field2`, ... ) ; END;...

H2 database isClosed/isValid report “not closed”, even with bogus credentials

java,h2
I've built an H2 in memory database for short term storage. I connect to the database from a short Java stub and test to make sure I have connected successfully. Since it's quite short, I've included the code in full. I've used both .isClosed and isValid(0). Is closed always always...

How to start H2 server from java

java,hibernate,h2
I have a little app that connects to H2 and does something using hibernate. I have successfully managed to make H2 and Hibernate work together but my problem is that I have to start H2 manually and this needs to be done programatically. I have tried several things but none...

SQL Keep (dense_rank last order by) to H2 database

sql,oracle,h2
I have following select statement SELECT c.car_id MAX(m.mod_number) KEEP (DENSE_RANK LAST ORDER BY DECODE(m.mod_number 'SP1', 10, 'SP2', 20, 'SP3', 30, 40)) FROM CAR c, MANUFACTURE m WHERE c.car_type = m.car_type AND m.make LIKE 'FOR%' GROUP BY c.car_id; I wanted to change it into H2 grammar for unit testing but I...

Introspect in-memory H2 Database

h2,playframework-2.3
My Play app uses H2 in-memory database when it runs in dev or test mode. I want to access this database when the app runs in dev mode. So I have the following options 1) Run the database in Embedded/Mixed mode. And make the play framework expose the database to...

Not able to load HIbernate + H2 in memory DB to automate testing with Spring and Maven

spring,hibernate,maven,testing,h2
Hello I have a Spring app with Hibernate + H2 in memory DB and JAX-RS. I am trying to create unit testing to to test the DB operations. My Config: OrderDaoTest: (Simple class to prove configuration) package com.ac.test.dataaccess; import com.ac.dataaccess.OrderDao; import com.ac.entities.Order; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.ContextConfiguration;...

Meta tables for H2 db in Spring Batch

java,spring,batch-processing,spring-batch,h2
I am facing some trouble in order to configuring spring batch with h2 db My configuration looks like this @Configuration @EnableBatchProcessing public class BatchConfiguration implements BatchConfigurer { @Bean public DataSource dataSource() { EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2).build(); DriverManagerDataSource dataSource = new DriverManagerDataSource(); dataSource.setDriverClassName(Driver.class.getName()); String h2Url = MessageFormat.format("jdbc:h2:file:{0}note;MODE=Oracle",...

Conditional Unique index on h2 database

oracle,h2,unique-index
I have a SAMPLE_TABLE with a column BIZ_ID which should be unique when the column active is not equal to 0. On an oracle database the index looks like this: CREATE UNIQUE INDEX ACTIVE_ONLY_IDX ON SAMPLE_TABLE (CASE "ACTIVE" WHEN 0 THEN NULL ELSE "BIZ_ID" END ); How would this unique...

Is it possible to use SELECT * FROM table_name WHERE column_name = 'value' where 'value' might appear in more than one row?

sql,jtable,h2
I am trying to create a screen that will display the history of purchases from a particular customer. My 'Sales' table contains columns "TicketNumber", "CustomerID", "InventoryNo", "Description", "Cost", "Retail", "IndividualSubTotal", "IndividualTax", "IndividualTotal", "SaleSubTotal", "SaleTax", and "SaleTotal". For the purposes of this screen, I will only need (for now) "TicketNumber", "CustomerID",...

Connecting to H2 server from DbVisualizer

h2,dbvisualizer
I have configured my H2 database as follows: @Configuration @Profile({ Profiles.DEV }) public class DevDataSourceConfiguration { @Bean(initMethod = "start", destroyMethod = "stop") public Server h2WebServer() throws SQLException { return Server.createWebServer("-web", "-webAllowOthers", "-webPort", "8082"); } @Bean(initMethod = "start", destroyMethod = "stop") @DependsOn("h2WebServer") public Server h2Server() throws SQLException { return Server.createTcpServer("-tcp", "-tcpAllowOthers",...

Upgrade H2 from sonarqube 4.5.1 to 5.1.1

sonarqube,upgrade,h2
Today I have installed Sonarqube 5.1.1. Not having a lot of data/projects I'm using the H2 db embedded in Sonarqube. Following the steps in the Upgrading page of the Sonarqube documentation I have the process up and running but without any data. Copying the \data\es folder from the old installation...

spring-boot integration testing using rest-assured

maven,spring-boot,h2
I have been struggling with the issue of starting the H2 db in my tests. This is how the tests are annotated - @SpringApplicationConfiguration(classes = ServiceApplication.class) @WebAppConfiguration @IntegrationTest("server.port:8084") @PropertySource("test:application.properties") public class testSerivce{ //test scenario } The in memory db H2 is used and this is how its setup in application...

Why is my JPA query getting slower on each loop iteration?

java,jpa,h2
I have the requirement of loading all the data from a db table and then get it into an index for search (elasticsearch to be specific). (Using an ES river is not an option in my case) What I experience is the following: I have the query with a specific...

can't login to h2 database from h2 console after using JDBC

java,jdbc,h2
i found answer as user name must be left blank from url What is the default username and password for h2 when there's nothing explicit in JDBC? and i was also logged in . But My Question is Why should i use blank username instead of "sa" ??

How to use a path relative to project root to H2 db-file configuration with Play Framework 2.4?

jdbc,h2,relative-path,persistent,playframework-2.4
We're developing a Play 2.4 application (Java API). For dev purposes, we'd like to use a persistent H2 database with DB file path relative to the project root directory. In How to use a persistent H2 database in the Play Framework instead of in-memory there was solution for Play 2.0:...

H2DB: Using the AND condition causing error in local memory

h2,h2db
I have a table called classification that has a column named product_id and a column named node_id. I'm trying to make a simple query in the h2 console as so: select * from classification where product_id =1 and node_id = 01010101 It gives me the following error General error: "java.lang.RuntimeException:...

Spring Boot: H2 database not saved to file

spring,spring-boot,spring-data,h2
I'm trying to persist an H2 in-memory DB to a file with Spring Boot to reuse the data in there. Unfortunately, the way to specify the datasource url like spring.datasource.url = jdbc:h2:file:~/WeatherDB;FILE_LOCK=FS (complete application.properties) doesn't work for me. I can't find a file generated by H2 anywhere on my hard...

Update statement to set a column based the maximum row of another table

sql,sql-update,h2
I have a Family table: SELECT * FROM Family; id | Surname | Oldest | Oldest_Age ---+----------+--------+------- 1 | Byre | NULL | NULL 2 | Summers | NULL | NULL 3 | White | NULL | NULL 4 | Anders | NULL | NULL The Family.Oldest column is not...

Desktop Database with Server without installation

java,database,server,desktop,h2
i have Java-Application that growth a lot over the past month. All configurations from the programm and all the logs are stored in .txt or .log files. The programm should change now. The user could install the programm on his own pc but the programm itself should get all the...

H2: executeBatch doesn't work

java,h2
I have the following code String query = "INSERT INTO student (age,name) VALUES (?,?)"; conn.setAutoCommit(true); ps = conn.prepareStatement(query); for (Student student:list) { ps.setInt(1, student.getAge()); ps.setString(2, student.getName()); } int[]temp=ps.executeBatch(); System.out.println("TEMP:"+temp.length);//returns 0 The code executed. No errors no exceptions. However table student is empty. I use h2 1.3.176 in embedded mode. What...

H2 and FOREIGN KEY

sql,h2
I ran into a problem: I was going to create 2 tables in H2. When I tried to create the second table, an error occured: column C_TASK not found Here is the code: CREATE TABLE S_TASK (C_TASK INT PRIMARY KEY, N_TASK VARCHAR(255), POINT_COUNT INT); CREATE TABLE S_ACHIEVEMENT(C_ACHIEVEMENT INT PRIMARY KEY,...

Play framework Slick 3 how to disable H2 foreign key constraints by scala?

scala,h2,slick
I'm using Play framework 2.3 by Scala. For database management I'm using slick 3. I would like to temporally disable foreign key constraints for H2 database. I found it is possible in H2 by SET REFERENTIAL_INTEGRITY FALSE. But I don't know how to set it by Scala in slick. Could...

org.h2.jdbc.JdbcSQLException: Connection is broken: “java.net.ConnectException: Connection refused: connect: localhost”

java,hibernate,h2
I am trying to connect to h2 database from a hibernate project. It worked fine with Oracle but I need to migrate to H2. The only things that I have changed are the driver and the hibernate.cfg.xml pom.xml <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> </dependency> hibernate.cfg.xml file <?xml version='1.0' encoding='utf-8'?> <!DOCTYPE hibernate-configuration PUBLIC...

JPA: Automatically delete data after timeinterval

jpa,triggers,cron,h2,resteasy
I've the following scenario: Data will be created by the sender on the server. If the receiver won't request the data within (for example) three days, the data should be deleted otherwise the receiver gets the data and after getting the data, the data will be deleted. In other words:...

Converting h2 database from h2.db to mv.db

java,database,jdbc,h2
I am converting old h2 databases (with the h2.db extension) to the newer mv database engine. In some tables of the database I have a problem with the stricter engine. just a SELECT * FROM does get an error, so with the latest h2-1.4.187.jar... i cannot even open the data...

How can I get the choice “H2” back in the H2 consol?

h2
I accidentally deleted the entries for H2 (Embedded) and H2 (Server) from the H2 console. The dropbox shown below allows you to select the kind of database you want to connect to/create. There is a button that allows you to remove entries, and with it I deleted the ones for...

Cannot connect to an H2 file database on Red Hat

java,database-connection,h2,redhat
I have written a Java application which creates an H2 file database (1.4.184). It works fine on Windows and Ubuntu. When I deployed it to the Red Hat server, it creates the database but is unable to connect. It just hangs and doesn't exit with any exception. This is the...

Select data between 2 defined rows

sql,h2
I have a H2 database. I want to count an average fuel usage regarding the data what i have been provided. The problem is that what i got is really messy. This is 1 car's fuel usage data. This is some sample data: | Amount | Date | Start (km)...

Why do I get 'Database is already closed' when invoking StaticQuery updateNA “shutdown;”

scala,h2,slick
import scala.slick.driver.H2Driver import scala.slick.jdbc.StaticQuery object Main extends App { val db = H2Driver.simple.Database forURL (url = s"jdbc:h2:mem:test", user = "sa", driver = "org.h2.Driver") StaticQuery updateNA "shutdown;" execute db.createSession() } Executing this with scala 2.11.5, h2 1.4.186 and slick 2.1.0 yields a "org.h2.jdbc.JdbcSQLException: Database is already closed". What is happening here?...

How to start H2 WebServer using Spring without XML?

java,spring,h2
I am currently using all Spring configurations as annotation and I would like to translate the following to it: <bean id="h2Server" class="org.h2.tools.Server" factory-method="createTcpServer" init-method="start" destroy-method="stop" depends-on="h2WebServer"> <constructor-arg value="-tcp,-tcpAllowOthers,-tcpPort,9092"/> </bean> <bean id="h2WebServer" class="org.h2.tools.Server" factory-method="createWebServer" init-method="start" destroy-method="stop"> <constructor-arg...

Using Joda DateTime with Hibernate in H2 Database - SQLError

java,hibernate,jpa,jodatime,h2
I'm using JPA - Hibernate 2.1 with Joda DateTime. For DateTime conversion I'm using org.jadira.usertype to work with Hibernate. This works all fine for development and local testing with H2 in-memory DB. But on our Jenkins server the tests always fail with some strange SQLExceptions. WARN o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error:...

How to properly use autoincrement

sql,syntax-error,h2
I am trying to create a database of accounts that automatically increase by 1 after every new insertion, but when I try to insert something into the table it throws this error. org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO ACCOUNT (USERACCNAME=[*]'admin',USERIP='lol', USEREMAIL='somehing', USERPASSWD='74123', USERPREMIUM='-1', CHAR1='1', CHAR2='-1', CHAR3='-1', CHAR4='-1') ";...

get the last data inserted in table query

java,sql,h2
I want to get the last data inserted 5 minutes ago and with a certain status but I get SQLException expected "., (, [, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )";, here is the query: SELECT * FROM...

Cannot insert minus sign into varchar column with check constraint (column <> '')

java,sql,jdbc,h2
I have an H2 table with a varchar column with a check constraint defined like this: CONSTRAINT my_constraint CHECK (varchar_field <> '') The following insert statement fails, but succeeds when I remove the constraint or insert any other value that is not just the minus sign ("-"). PreparedStatement ps= con.prepareStatement("INSERT...

Tomcat WebApp with embedded H2 : Database may be already in use: “Locked by another process”

java,spring,hibernate,tomcat,h2
I'm working on a Maven Spring Boot web application using Hibernate on embedded H2 database. The application is deployed on Tomcat 8 application container using Maven goal tomcat7:redeploy from Maven Tomcat plugin (tomcat7-maven-plugin). When I try to deploy this web application on Tomcat for the first time, I have no...

Try to save new Entitiy in Database but generated ID is null

java,spring,hibernate,jpa,h2
I have an object "Event" and an object eventDetails. Both are jpa Entities. The Event object contains the eventDetails by a @OneToOne annotation. My Database has lots of events. But the eventdetails of every event are null. In my RestController I try to fill the eventdetails like this: @RequestMapping(value =...

Where is the H2 database stored?

java,database,jdbc,h2
I am trying to use an H2 database with Java. I cannot seem to find out where the data is being written. The database URL I am giving is: jdbc:h2:/db/bh. I am connecting with the database using Java like so: dbObj.setDBConnection(DriverManager.getConnection(hObj.getDBUrl(), hObj.getDBUsername(), hObj.getDBPassword())); where DB URL is given above. Username:...

SQL switch from decode to case

sql,oracle,oracle11g,case,h2
I have a query that does something like this... SELECT * FROM ... ... ORDER BY DECODE(APR(ACC.RATE,'X'), 'FIRST RATE', 1, 'SECOND RATE', 2, 3); Because I am dealing with h2 database and h2 doesnt have decode and decode alias is very difficult to implement so I was wondering if I...

What is the maxium number of tables in a H2 database?

h2
What is the maxium number of tables in a H2 database? I could not find any info in the documentation. Of course I could test it but I was wondering whether there is an official/documented upper limit.

H2 performance recommendations

java,database,h2
I'm currently working with a somewhat larger database, and though I have no specific issues, I would like some recommendations, if anyone has any. The database is 2.2 gigabyte (after recreation/compacting). It contains about 50 tables. One of those tables contains a blob plus some metadata. It currently has about...

Numeric value out of range subtracting dates in jOOQ

java,h2,jooq
I'm trying to compare dates in jOOQ (v3.5.0) using the H2 database (v1.4.185), but I get "Numeric value out of range" if the comparison is too large. For example, with the following table CREATE TABLE example ( id INT AUTO_INCREMENT, deadline TIMESTAMP, PRIMARY KEY (id) ); and using the following...

H2 performance degrade using remote tcp server

database,performance,h2
I have a simple application that uses a single connection, the flow is something like this: SELECT QUERY CONNECTION CLOSE for(..) //thousands of iterations { SIMPLE SELECT QUERY } BUSINESS LOGIC CONNECTION CLOSE for(..) //thousands of iterations { SIMPLE SELECT QUERY } BUSINESS LOGIC CONNECTION CLOSE When i use the...