How to connect to HSQL which Spring creates when jdbc:embedded-database is used?

Question

I have a HSQL database which Spring automatically creates for me:

<jdbc:embedded-database id="dataSource" type="HSQL">
    <jdbc:script location="classpath:scheme.sql" /
</jdbc:embedded-database>

And now I want to connect to this database. My question is how to do this, because I don't known which address I should use.

What do you mean by "connect to"? Do you want to use it in your spring bean or you want to connect with external database browser? — Piotr Gwiazda, Jan 16 '12 at 14:12
@peter-gwiazda I mean how to connect to it with external database browser. — Slava Semushin, Jan 17 '12 at 02:04

score 20 · Accepted Answer · answered Jan 16 '12 at 13:01

20

This embedded HSQL database is all-in-memory and in-process, therefore accessible only from the Spring Java process. If you want to access the database from another tool as well, for example to check the contents with a database manager, you can start an HSQLDB server with an all-in-memory instance, then connect to the server from Spring and other tools.

This is covered in the HSQLDB Guide http://hsqldb.org/doc/2.0/guide/listeners-chapt.html

The server is started with this command:

java -cp ../lib/hsqldb.jar org.hsqldb.Server --database.0 mem:test --dbname.0 test

You need to create a Spring data source with username "SA" and password "". The database driver and URL (from the same machine) to configure the Spring data source are:

org.hsqldb.jdbcDriver
jdbc:hsqldb:hsql://localhost/test

answered Jan 16 '12 at 13:01

fredt

24,044
3
40
61

When I run `java -cp ~/.m2/repository/org/hsqldb/hsqldb/2.0.0/hsqldb-2.0.0.jar org.hsqldb.util.DatabaseManagerSwing` and use `jdbc:hsqldb:hsql://localhost/test` I've got error: `java.sql.SQLTransientConnectionException: java.net.ConnextException: Connection refused` – Slava Semushin Jan 17 '12 at 03:00
java -cp ../lib/hsqldb.jar org.hsqldb.Server... is running without an error? – Piotr Gwiazda Jan 17 '12 at 10:46
@peter-gwiazda When I run server manually, I can connect to it with provided command. But in my case, Spring starts server, so I ask about how to connect to HSQL which runned by Spring.. – Slava Semushin Jan 18 '12 at 02:15
2

It's rather impossible. You have to run server manually and connect with Spring app and other applications. In embeded mode you can only access in the same JVM. What's wrong with server mode? What do you keep in the DB? Why restart DB every time with application? – Piotr Gwiazda Jan 18 '12 at 07:56
2

You were right that I can't connect to HSQL from another JVM process. I have used H2 console to accomplish that. Today I've found another way to achieve that with Spring and HSQL: http://www.mkyong.com/spring/spring-view-content-of-hsqldb-embedded-database/ – Slava Semushin Jun 22 '15 at 12:26
Note that to run HSQLDB in server mode (which allows connections from external clients), you don't need to start it in a separate process. You can embed it in your application by instantiating an `org.hsqldb.server.Server` object. – pacoverflow Oct 21 '16 at 17:45

score 7 · Answer 2 · answered Jan 16 '12 at 12:24

7

I recomend you to use external Database, but just in case if you want to use HSQL, then this may will help you http://java.dzone.com/articles/spring-3-makes-use-embedded-easy

answered Jan 16 '12 at 12:24

emilan

12,825
11
32
37

+1, in addition Transactional annotation for ContextConfiguration integration tests will rollback by default making the development db safe and consistent after testing. – Aubergine May 16 '15 at 14:14

Aravind A · Answer 3 · 2012-01-16T13:22:48.200

2

Embedded-database is an in memory DB and Spring supports HSQL, H2, and Derby . You could go to their respective site for the connection details .

For H2 see here . For HSQL see here and here.

As far as I understand , the

<jdbc:embedded-database id="dataSource" type="HSQL">
  <jdbc:script location="classpath:scheme.sql" /
</jdbc:embedded-database>

uses an in-memory DB and so is not accessible externally . You'll be able to access this within the same VM and same class loader .

edited Jan 16 '12 at 13:22

answered Jan 16 '12 at 12:39

Aravind A

9,507
4
36
45

Can we use XML file to load into DB – Vinay Veluri Feb 05 '13 at 10:22

score 0 · Answer 4 · edited Sep 26 '12 at 08:25

0

You can connect to the embedded database in the normal fashion, (SQL Developer, SQL Explorer etc); I used my debugger to look at the URL property in the embedded database bean I created with Spring, in your case dataSource. I would think your url would be something along the lines of jdbc:hsqldb:mem:dataSource.

edited Sep 26 '12 at 08:25

Marijn

10,367
5
59
80

answered Sep 07 '12 at 18:28

Aaron

125
5

It definitely won't work because in-memory database available only inside one JVM process. – Slava Semushin Jun 22 '15 at 12:20

score 0 · Answer 5 · edited May 23 '17 at 12:14

0

For some people a sufficient solution would be to use the h2 console - as described here:

spring boot default H2 jdbc connection (and H2 console)

You must only remember to set hsqldb drivers where needed. This way the database doesn't have to be started separately. You also don't have to install any additional software to browse it.

edited May 23 '17 at 12:14

Community

1
1

answered Apr 19 '16 at 08:33

gajos

897
2
10
21

score -1 · Answer 6 · edited May 30 '12 at 21:13

-1

you can do like this

final ApplicationContext ctx = new ClassPathXmlApplicationContext("dao-context.xml");
final DataSource dataSource = (DataSource)ctx.getBean("dataSource");
final Connection conn = dataSource.getConnection();

edited May 30 '12 at 21:13

Martijn Pieters

1,048,767
296
4,058
3,343

answered May 30 '12 at 20:25

Tanmay Saha

1

How to connect to HSQL which Spring creates when jdbc:embedded-database is used?

6 Answers6

Linked