h2 – Page 3 – Row Coding

View content of embedded H2 database started by Spring

June 7, 2023 by Tarik

Pretty much the same question as View content of H2 or HSQLDB in-memory database. Simply add the following to your configuration. <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 value=”-web,-webAllowOthers,-webPort,8082″/> </bean> This will start both H2 web console and TCP server in the same JVM as … Read more

Reset Embedded H2 database periodically

June 7, 2023 by Tarik

H2 supports a special SQL statement to drop all objects: DROP ALL OBJECTS [DELETE FILES] If you don’t want to drop all tables, you might want to use truncate table: TRUNCATE TABLE

problem with INIT=RUNSCRIPT and relative paths

May 29, 2023 by Tarik

You can use the following url: “jdbc:h2:mem:sample;INIT=RUNSCRIPT FROM ‘classpath:scripts/create.sql'” With that one it is possible to run script from classpath. So you can just put it src/main/resources/scripts or src/test/resources/scripts in your maven (or something else) project.

H2 database error: Database may be already in use: “Locked by another process”

May 29, 2023 by Tarik

H2 is still running (I can guarantee it). You need to use a TCP connection for multiple users such as -> <property name=”javax.persistence.jdbc.url” value=”jdbc:h2:tcp://localhost/C:\Database\Data\production;”/> OR DriverManager.getConnection(“jdbc:h2:tcp://localhost/server~/dbname”,”username”,”password”); It also means you need to start the server in TCP mode. Honesetly, it is pretty straight forward in the documentation. Force kill the process (javaw.exe for Windows), and … Read more

H2-Console is not showing in browser

May 22, 2023 by Tarik

add this two lines in your spring security file and you are good to go. http.csrf().disable(); http.headers().frameOptions().disable();

Executing script file in h2 database

March 4, 2023 by Tarik

You can use the RUNSCRIPT SQL statement: RUNSCRIPT FROM ‘test.sql’ or you can use the RunScript standalone / command line tool: java -cp h2*.jar org.h2.tools.RunScript -url jdbc:h2:~/test -script test.sql You can also use the RunScript tool within an application: RunScript.execute(conn, new FileReader(“test.sql”));

How in H2DB get sql dump like in MySql?

February 28, 2023 by Tarik

Yes, there are multiple solutions. One is to run the SCRIPT SQL statement: SCRIPT TO ‘fileName’ Another is to use the Script tool: java org.h2.tools.Script -url <url> -user <user> -password <password> Then, there are also the RUNSCRIPT statement and RunScript tool. By the way, you should consider upgrading to a more recent version of H2. … Read more

auto increment ID in H2 database

February 25, 2023 by Tarik

It works for me. JDBC URL: jdbc:h2:~/temp/test2 drop table test; create table test(id bigint auto_increment, name varchar(255)); insert into test(name) values(‘hello’); insert into test(name) values(‘world’); select * from test; result: ID NAME 1 hello 2 world

How to find rows in one table that have no corresponding row in another table

February 24, 2023 by Tarik

select tableA.id from tableA left outer join tableB on (tableA.id = tableB.id) where tableB.id is null order by tableA.id desc If your db knows how to do index intersections, this will only touch the primary key index

How to configure spring-boot to use file based H2 database

February 17, 2023 by Tarik

I am adding this answer to avoid confusion and further research. Actually I have the same problem and none of the answer worked for me completely rather than the mix for some answers worked. Here is the minimal configuration which is required to persist H2 db in spring boot. application.properties # H2 spring.h2.console.enabled=true spring.h2.console.path=/h2 # … Read more