Features Feature List Comparison to Other Database Engines H2 in Use Connection Modes Database URL Overview Connecting to an Embedded (Local) Database In- Memory Databases Database Files Encryption Database File Locking Opening a Database Only if it Already Exists Closing a Database Ignore Unknown Settings Changing Other Settings when Opening a Connection Custom File Access Mode Multiple Connections Database File Layout Logging and Recovery Compatibility Auto- Reconnect Automatic Mixed Mode Page Size Using the Trace Options Using Other Logging APIs Read Only Databases Read Only Databases in Zip or Jar File Computed Columns / Function Based Index Multi- Dimensional Indexes User- Defined Functions and Stored Procedures Pluggable or User- Defined Tables Triggers Compacting a Database Cache Settings. Feature List. Main Features. Very fast database engine Open source Written in Java Supports standard SQL, JDBC API Embedded and Server mode, Clustering support Strong security features The Postgre.
SQL ODBC driver can be used Multi version concurrency Additional Features. Disk based or in- memory databases and tables, read- only database support, temporary tables Transaction support (read committed), 2- phase- commit Multiple connections, table level locking Cost based optimizer, using a genetic algorithm for complex queries, zero- administration Scrollable and updatable result set support, large result set, external result sorting, functions can return a result set Encrypted database (AES), SHA- 2. SSL SQL Support. Support for multiple schemas, information schema Referential integrity / foreign key constraints with cascade, check constraints Inner and outer joins, subqueries, read only views and inline views Triggers and Java functions / stored procedures Many built- in functions, including XML and lossless data compression Wide range of data types including large objects (BLOB/CLOB) and arrays Sequence and autoincrement columns, computed columns (can be used for function based indexes) ORDER BY, GROUP BY, HAVING, UNION, LIMIT, TOPCollation support, including support for the ICU4. J library Support for users and roles Compatibility modes for IBM DB2, Apache Derby, HSQLDB, MS SQL Server, My.
SQL, Oracle, and Postgre. SQL. Security Features. Includes a solution for the SQL injection problem User password authentication uses SHA- 2. For server mode connections, user passwords are never transmitted in plain text over the network (even when using insecure connections; this only applies to the TCP server and not to the H2 Console however; it also doesn't apply if you set the password in the database URL) All database files (including script files that can be used to backup data) can be encrypted using the AES- 1. The remote JDBC driver supports TCP/IP connections over TLS The built- in web server supports connections over TLS Passwords can be sent to the database using char arrays instead of Strings Other Features and Tools. Small footprint (smaller than 1.
These free video tutorials will show you how to use microsoft access to organize your business or personal data. Knowing how to use a database can also get you a. How to create a Microsoft Access macro that opens and runs a query: When creating databases for end users, the goal is to make the database user-friendly and intuitive. How to Create Action Queries in Microsoft Access. Queries within a DBMS system can do more than display answers to the questions you ask. They can actually perform.
MB), low memory requirements Multiple index types (b- tree, tree, hash) Support for multi- dimensional indexes CSV (comma separated values) file support Support for linked tables, and a built- in virtual 'range' table Supports the EXPLAIN PLAN statement; sophisticated trace options Database closing can be delayed or disabled to improve the performance Web- based Console application (translated to many languages) with autocomplete The database can generate SQL script files Contains a recovery tool that can dump the contents of the database Support for variables (for example to calculate running totals) Automatic re- compilation of prepared statements Uses a small number of database files Uses a checksum for each record and log entry for data integrity Well tested (high code coverage, randomized stress tests) Comparison to Other Database Engines This comparison is based on H2 1. Apache Derby version 1. HSQLDB 2. 2, My. SQL 5. Postgre. SQL 9. 0. Feature. H2. Derby. HSQLDBMy. SQLPostgre.
SQLPure Java. Yes. Yes. Yes. No. No. Embedded Mode (Java)Yes. Yes. Yes. No. No.
In- Memory Mode. Yes. Yes. Yes. No. No. Explain Plan. Yes. Yes *1. 2Yes. Yes. Yes. Built- in Clustering / Replication. Yes. Yes. No. Yes. Yes. Encrypted Database.
Yes. Yes *1. 0Yes *1. No. No. Linked Tables.
Yes. No. Partially *1. Partially *2. Yes. ODBC Driver. Yes. No. No. Yes. Yes. Fulltext Search. Yes.
Yes. No. Yes. Yes. Domains (User- Defined Types)Yes. No. Yes. Yes. Yes.
Files per Database. Few. Many. Few. Many. Many. Row Level Locking. Yes *9. Yes. Yes *9.
Yes. Yes. Multi Version Concurrency. Yes. No. Yes. Yes. Yes. Multi- Threaded Processing.
No *1. 1Yes. Yes. Yes. Yes. Role Based Security. Yes. Yes *3. Yes.
Yes. Yes. Updatable Result Sets. Yes. Yes *7. Yes. Yes. Yes. Sequences. Yes. Yes. Yes. No. Yes. Limit and Offset. Yes. Yes *1. 3Yes.
Yes. Yes. Window Functions. No *1. 5No *1. 5No. No. Yes. Temporary Tables. Yes. Yes *4. Yes. Yes. Yes. Information Schema.
Yes. No *8. Yes. Yes. Yes. Computed Columns. Yes. Yes. Yes. Yes. Yes *6. Case Insensitive Columns. Yes. Yes *1. 4Yes.
Yes. Yes *6. Custom Aggregate Functions. Yes. No. Yes. No. Yes. CLOB/BLOB Compression. Yes. No. No. No. Yes. Footprint (jar/dll size)~1. MB *5~3 MB~1. 5 MB~4 MB~6 MB *1 HSQLDB supports text tables.*2 My. SQL supports linked My.
SQL tables under the name 'federated tables'.*3 Derby support for roles based security and password checking as an option.*4 Derby only supports global temporary tables.*5 The default H2 jar file contains debug information, jar files for other databases do not.*6 Postgre. SQL supports functional indexes.*7 Derby only supports updatable result sets if the query is not sorted.*8 Derby doesn't support standard compliant information schema tables.*9 When using MVCC (multi version concurrency).*1. Derby and HSQLDB don't hide data patterns well.*1. The MULTI. Daffodil.
Db and One$Db It looks like the development of this database has stopped. The last release was February 2. Mc. Koi It looks like the development of this database has stopped. The last release was August 2.
H2 in Use For a list of applications that work with or use H2, see: Links. Connection Modes The following connection modes are supported: Embedded mode (local connections using JDBC) Server mode (remote connections using JDBC or ODBC over TCP/IP) Mixed mode (local and remote connections at the same time) Embedded Mode In embedded mode, an application opens a database from within the same JVM using JDBC. This is the fastest and easiest connection mode.
The disadvantage is that a database may only be open in one virtual machine (and class loader) at any time. As in all modes, both persistent and in- memory databases are supported.
There is no limit on the number of database open concurrently, or on the number of open connections. Server Mode When using the server mode (sometimes called remote mode or client/server mode), an application opens a database remotely using the JDBC or ODBC API. A server needs to be started within the same or another virtual machine, or on another computer. Many applications can connect to the same database at the same time, by connecting to this server. Internally, the server process opens the database(s) in embedded mode.
As in all modes, both persistent and in- memory databases are supported. There is no limit on the number of database open concurrently per server, or on the number of open connections. Mixed Mode The mixed mode is a combination of the embedded and the server mode. The first application that connects to a database does that in embedded mode, but also starts a server so that other applications (running in different processes or virtual machines) can concurrently access the same data. The local connections are as fast as if the database is used in just the embedded mode, while the remote connections are a bit slower. When using the automatic mixed mode, all clients that want to connect to the database (no matter if it's an local or remote connection) can do so using the exact same database URL. Database URL Overview This database supports multiple connection modes and connection settings.
This is achieved using different database URLs. Settings in the URLs are not case sensitive.
Topic. URL Format and Examples. Embedded (local) connection jdbc: h.
The prefix file: is optional. If no or only a relative path is used, then the current working directory is used as a starting point. The case sensitivity of the path and database name depend on the operating system, however it is recommended to use lowercase letters only. The database name must be at least three characters long (a limitation of File. Temp. File). The database name must not contain a semicolon. To point to the user home directory, use ~/, as in: jdbc: h. In- Memory Databases For certain use cases (for example: rapid prototyping, testing, high performance operations, read- only databases), it may not be required to persist data, or persist changes to the data.
This database supports the in- memory mode, where the data is not persisted. This means the database to be opened is private.
In this case, the database URL is jdbc: h. Opening two connections within the same virtual machine means opening two different (private) databases. In this case, the database URL must include a name. Example: jdbc: h. Accessing the same database using this URL only works within the same virtual machine and class loader environment.
The other processes then need to access the database over TCP/IP or TLS, using a database URL such as: jdbc: h. For an in- memory database, this means the content is lost. To keep the database open, add ; DB. To keep the content of an in- memory database as long as the virtual machine is alive, use jdbc: h.
DB. Database Files Encryption The database files can be encrypted. Three encryption algorithms are supported: . To use file encryption, you need to specify the encryption algorithm (the 'cipher') and the file password (in addition to the user password) when connecting to the database.