View content of H2 or HSQLDB in-memory database

Question

Is there a way to browse the content of an H2 or an HSQLDB in-memory database for viewing  For example  during a debugging session with Hibernate in order to check when the flush is executed  or to make sure the script that instantiates the DB gives the expected result   Does it exist an addon or a library that you can embed with your code in order to allow this   Please  mention which one you re talking about  H2 or HSQLDB  in case you have an answer specific to one of them

User · Answer

I ve a problem with H2 version 1 4 190 remote connection to inMemory  as well as in file  with  Connection is broken   unexpected status 16843008  until do not downgrade to 1 3 176  See Grails accessing H2 TCP server hangs

User · Answer

This is a Play 2 controller to initialize the H2 TCP and Web servers   package controllers   import org h2 tools Server  import play mvc Controller  import play mvc Result   import java sql SQLException          Play 2 controller to initialize H2 TCP Server and H2 Web Console Server        Once it s initialized  you can connect with a JDBC client with    the URL  jdbc h2 tcp   127 0 1 1 9092 mem DBNAME      or can be accessed with the web console at  http   localhost 8082      and the URL JDBC  jdbc h2 mem DBNAME          author Mariano Ruiz  lt mrsarm gmail com gt      public class H2ServerController extends Controller        private static Server h2Server   null      private static Server h2WebServer   null       public static synchronized Result debugH2   throws SQLException           if  h2Server    null                h2Server   Server createTcpServer  -tcp    -tcpAllowOthers    -tcpPort    9092                h2Server start                h2WebServer   Server createWebServer  -web   -webAllowOthers   -webPort   8082                h2WebServer start                return ok  H2 TCP Web servers initialized              else               return ok  H2 TCP Web servers already initialized

User · Answer

You can run H2 web server within your application that will access the same in-memory database  You can also access the H2 running in server mode using any generic JDBC client like SquirrelSQL   UPDATE   Server webServer   Server createWebServer  -web -webAllowOthers true -webPort 8082   start    Server server   Server createTcpServer  -tcp -tcpAllowOthers true -tcpPort 9092   start      Now you can connect to your database via jdbc h2 mem foo db URL within the same process or browse the foo db database using localhost 8082  Remember to close both servers  See also  H2 database in memory mode cannot be accessed by Console   You can also use Spring    lt bean id  h2Server  class  org h2 tools Server  factory-method  createTcpServer  init-method  start  destroy-method  stop  depends-on  h2WebServer  gt       lt constructor-arg value  -tcp -tcpAllowOthers true -tcpPort 9092   gt   lt  bean gt   lt bean id  h2WebServer  class  org h2 tools Server  factory-method  createWebServer  init-method  start  destroy-method  stop  gt       lt constructor-arg value  -web -webAllowOthers true -webPort 8082   gt   lt  bean gt    lt bean id  dataSource  class  com mchange v2 c3p0 ComboPooledDataSource  destroy-method  close  depends-on  h2Server  gt       lt property name  driverClass  value  org h2 Driver   gt       lt property name  jdbcUrl  value  jdbc h2 mem foo db   gt   lt  bean gt    BTW you should only depend on assertions and not on manual peeking the database contents  Use this only for troubleshooting   N B  if you use Spring test framework you won t see changes made by a running transaction and this transaction will be rolled back immediately after the test

User · Answer

In H2  what works for me is    I code  starting the server like   server   Server createTcpServer   start      That starts the server on localhost port 9092   Then  in code  establish a DB connection on the following JDBC URL   jdbc h2 tcp   localhost 9092 mem test DB CLOSE DELAY -1 MODE MySQL   While debugging  as a client to inspect the DB I use the one provided by H2  which is good enough  to launch it you just need to launch the following java main separately  org h2 tools Console   This will start a web server with an app on 8082  launch a browser on localhost 8082  And then you can enter the previous URL to see the DB

User · Answer

What about comfortably viewing  and also editing  the content over ODBC  amp   MS-Access  Excel  Softwareversions     H2 Version 1 4 196 Win 10 Postgres ODBC Driver Version  psqlodbc 09 03 0210 For Win7 ODBC Client  win7 psqlodbc 09 00 0101-x64 msi   H2 Server      For JDBC Clients to connect  jdbc h2 tcp   localhost 9092 trader CIPHER AES IFEXISTS TRUE MVCC true LOCK TIMEOUT 60000 CACHE SIZE 131072 CACHE TYPE TQ    public class DBStarter       public static final String BASEDIR     C  Trader db        public static final String DB URL   BASEDIR    trader CIPHER AES IFEXISTS TRUE MVCC true LOCK TIMEOUT 10000 CACHE SIZE 131072 CACHE TYPE TQ      static void startServer   throws SQLException           Server tcpServer   Server createTcpServer                   -tcpPort    9092                    -tcpAllowOthers                    -ifExists                      -trace                    -baseDir   BASEDIR                    tcpServer start            System out println  H2 JDBC Server started       tcpServer getStatus              Server pgServer   Server createPgServer                   -pgPort    10022                    -pgAllowOthers                    -key    traderdb   DB URL                    pgServer start            System out println  H2 ODBC PGServer started      pgServer getStatus                   Windows10 ODBC Datasource Configuration which can be used by any ODBC client  In Databse field the name given in  -key  parameter has to be used

User · Answer

For HSQLDB  The following worked for me  DatabaseManager threadedDBM     And this brought up the GUI with my tables and data once I pointed it to the right named in-mem database  It is basically the equivalent of newing up a DatabaseManager  the non Swing variety   which prompts for connection details  and is set to --noexit  I also tried the Swing version  but it only had a main  and I was unsure of the arguments to pass  If anyone knows  please post here  Just because I searched for hours for the right database name  The name of the database is the name of your datasource  So try with URL jdbc hsqldb mem dataSource if you have a data source bean with id dataSource  If this does not work  try testdb which is the default

User · Answer

I don t know why is it working fine at yours machines  but I had to spend a day in order to get it is working   The server works with Intellij Idea U via url  jdbc h2 tcp   localhost 9092   default     localhost 8082  in the browser alse works fine   I added this into the mvc-dispatcher-servlet xml   lt bean id  dataSource  class  org apache commons dbcp BasicDataSource  destroy-method  close  depends-on  h2Server  gt       lt property name  driverClassName  value  org h2 Driver   gt       lt property name  url  value  jdbc h2 tcp   localhost 9092   default   gt       lt property name  username  value  sa   gt       lt property name  password  value     gt   lt  bean gt    lt bean id  h2Server  class  org h2 tools Server  factory-method  createTcpServer  init-method  start  destroy-method  stop  depends-on  h2WebServer  gt       lt constructor-arg gt           lt array gt               lt value gt -tcp lt  value gt               lt value gt -tcpAllowOthers lt  value gt               lt value gt -tcpPort lt  value gt               lt value gt 9092 lt  value gt           lt  array gt       lt  constructor-arg gt   lt  bean gt    lt bean id  h2WebServer  class  org h2 tools Server  factory-method  createWebServer  init-method  start  destroy-method  stop  gt       lt constructor-arg gt           lt array gt               lt value gt -web lt  value gt               lt value gt -webAllowOthers lt  value gt               lt value gt -webPort lt  value gt               lt value gt 8082 lt  value gt           lt  array gt       lt  constructor-arg gt   lt  bean gt

User · Answer

You can expose it as a JMX feature  startable via JConsole    ManagedResource  Named public class DbManager         ManagedOperation description    Start HSQL DatabaseManagerSwing        public void dbManager             String   args     --url    jdbc hsqldb mem embeddedDataSource    --noexit            DatabaseManagerSwing main args             XML context    lt context component-scan base-package  your package root  scoped-proxy  targetClass   gt   lt context annotation-config   gt   lt context mbean-server   gt   lt context mbean-export   gt

User · Answer

This is more a comment to previous Thomas Mueller s post rather than an answer  but haven t got enough reputation for it  Another way of getting the connection if you are Spring JDBC Template is using the following   jdbcTemplate getDataSource   getConnection      So on debug mode if you add to the  Expressions  view in Eclipse it will open the browser showing you the H2 Console   org h2 tools Server startWebServer jdbcTemplate getDataSource   getConnection       Eclipse Expressions View  H2 Console

User · Answer

With HSQLDB  you have several built-in options   There are two GUI database managers and a command line interface to the database  The classes for these are   org hsqldb util DatabaseManager org hsqldb util DatabaseManagerSwing org hsqldb cmdline SqlTool   You can start one of the above from your application and access the in-memory databases   An example with JBoss is given here   http   docs jboss org jbpm v3 2 userguide html ch07s03 html  You can also start a server with your application  pointing it to an in-memory database   org hsqldb Server

User · Answer

For H2  you can start a web server within your code during a debugging session if you have a database connection object  You could add this line to your code  or as a  watch expression   dynamically    org h2 tools Server startWebServer conn     The server tool will start a web browser locally that allows you to access the database

[hsqldb] View content of H2 or HSQLDB in-memory database

Examples related to hsqldb

Examples related to h2

Examples related to in-memory-database