h2数据库引擎开发文档h2.pdf资源-CSDN文库

pdf

需积分: 10 119 浏览量 2015-06-21 21:11:02 上传评论收藏 1.26MB PDF 举报

资源推荐

资源详情

资源评论

H2 Database Engine

Version 1.4.187 (2015-04-10)

1 of 181

Table of Contents
Embedding H2 in an Application.....................................................................................................................................12
The H2 Console Application...........................................................................................................................................12
Step-by-Step...........................................................................................................................................................12
Installation.........................................................................................................................................................12
Start the Console................................................................................................................................................12
Login.................................................................................................................................................................13
Sample..............................................................................................................................................................14
Execute..............................................................................................................................................................15
Disconnect.........................................................................................................................................................16
End....................................................................................................................................................................16
Requirements...............................................................................................................................................................17
Database Engine......................................................................................................................................................17
H2 Console..............................................................................................................................................................17
Supported Platforms......................................................................................................................................................17
Installing the Software..................................................................................................................................................17
Directory Structure........................................................................................................................................................17
Starting and Using the H2 Console.................................................................................................................................18
Firewall...................................................................................................................................................................19
Testing Java............................................................................................................................................................19
Error Message 'Port may be in use'...........................................................................................................................19
Using another Port...................................................................................................................................................19
Connecting to the Server using a Browser.................................................................................................................19
Multiple Concurrent Sessions....................................................................................................................................19
Login......................................................................................................................................................................20
Error Messages........................................................................................................................................................20
Adding Database Drivers..........................................................................................................................................20
Using the H2 Console...............................................................................................................................................20
Inserting Table Names or Column Names..................................................................................................................20
Disconnecting and Stopping the Application...............................................................................................................20
Special H2 Console Syntax.............................................................................................................................................20
Settings of the H2 Console............................................................................................................................................21
Connecting to a Database using JDBC............................................................................................................................21
Creating New Databases................................................................................................................................................22
Using the Server...........................................................................................................................................................22
Starting the Server Tool from Command Line............................................................................................................22
Connecting to the TCP Server...................................................................................................................................22
Starting the TCP Server within an Application............................................................................................................22
Stopping a TCP Server from Another Process.............................................................................................................22
Using Hibernate............................................................................................................................................................23
Using TopLink and Glassfish..........................................................................................................................................23
Using EclipseLink..........................................................................................................................................................23
Using Apache ActiveMQ.................................................................................................................................................23
Using H2 within NetBeans..............................................................................................................................................24
Using H2 with jOOQ......................................................................................................................................................24
Using Databases in Web Applications.............................................................................................................................24
Embedded Mode......................................................................................................................................................25
Server Mode............................................................................................................................................................25
Using a Servlet Listener to Start and Stop a Database................................................................................................25
Using the H2 Console Servlet....................................................................................................................................25
Android........................................................................................................................................................................26
CSV (Comma Separated Values) Support........................................................................................................................27
Reading a CSV File from Within a Database...............................................................................................................27
Importing Data from a CSV File................................................................................................................................27
Writing a CSV File from Within a Database................................................................................................................27
Writing a CSV File from a Java Application.................................................................................................................27
Reading a CSV File from a Java Application...............................................................................................................28
Upgrade, Backup, and Restore.......................................................................................................................................28
Database Upgrade...................................................................................................................................................28
Backup using the Script Tool....................................................................................................................................28
Restore from a Script...............................................................................................................................................28
Online Backup.........................................................................................................................................................28
Command Line Tools.....................................................................................................................................................29
The Shell Tool...............................................................................................................................................................29
Using OpenOffice Base..................................................................................................................................................30
Java Web Start / JNLP...................................................................................................................................................30
Using a Connection Pool................................................................................................................................................30
2 of 181

Fulltext Search..............................................................................................................................................................31
Using the Native Fulltext Search...............................................................................................................................31
Using the Lucene Fulltext Search..............................................................................................................................31
User-Defined Variables..................................................................................................................................................32
Date and Time..............................................................................................................................................................33
Using Spring.................................................................................................................................................................33
Using the TCP Server...............................................................................................................................................33
Error Code Incompatibility........................................................................................................................................33
OSGi............................................................................................................................................................................34
Java Management Extension (JMX)................................................................................................................................34
Feature List..................................................................................................................................................................35
Main Features..........................................................................................................................................................35
Additional Features..................................................................................................................................................35
SQL Support............................................................................................................................................................35
Security Features.....................................................................................................................................................36
Other Features and Tools.........................................................................................................................................36
Comparison to Other Database Engines..........................................................................................................................36
DaffodilDb and One$Db............................................................................................................................................37
McKoi......................................................................................................................................................................37
H2 in Use.....................................................................................................................................................................37
Connection Modes.........................................................................................................................................................37
Embedded Mode......................................................................................................................................................37
Server Mode............................................................................................................................................................38
Mixed Mode.............................................................................................................................................................38
Database URL Overview................................................................................................................................................39
Connecting to an Embedded (Local) Database................................................................................................................40
In-Memory Databases...................................................................................................................................................40
Database Files Encryption..............................................................................................................................................40
Creating a New Database with File Encryption...........................................................................................................40
Connecting to an Encrypted Database.......................................................................................................................40
Encrypting or Decrypting a Database........................................................................................................................41
Database File Locking....................................................................................................................................................41
Opening a Database Only if it Already Exists...................................................................................................................41
Closing a Database........................................................................................................................................................41
Delayed Database Closing........................................................................................................................................41
Don't Close a Database when the VM Exits................................................................................................................42
Execute SQL on Connection...........................................................................................................................................42
Ignore Unknown Settings..............................................................................................................................................42
Changing Other Settings when Opening a Connection.....................................................................................................42
Custom File Access Mode...............................................................................................................................................42
Multiple Connections.....................................................................................................................................................43
Opening Multiple Databases at the Same Time..........................................................................................................43
Multiple Connections to the Same Database: Client/Server.........................................................................................43
Multithreading Support.............................................................................................................................................43
Locking, Lock-Timeout, Deadlocks............................................................................................................................43
Avoiding Deadlocks..................................................................................................................................................44
Database File Layout.....................................................................................................................................................44
Moving and Renaming Database Files.......................................................................................................................44
Backup....................................................................................................................................................................44
Logging and Recovery...................................................................................................................................................44
Compatibility.................................................................................................................................................................44
Compatibility Modes.................................................................................................................................................45
DB2 Compatibility Mode...........................................................................................................................................45
Derby Compatibility Mode.........................................................................................................................................45
HSQLDB Compatibility Mode.....................................................................................................................................45
MS SQL Server Compatibility Mode............................................................................................................................45
MySQL Compatibility Mode.......................................................................................................................................45
Oracle Compatibility Mode........................................................................................................................................46
PostgreSQL Compatibility Mode................................................................................................................................46
Auto-Reconnect............................................................................................................................................................46
Automatic Mixed Mode..................................................................................................................................................46
Page Size......................................................................................................................................................................47
Using the Trace Options................................................................................................................................................47
Trace Options..........................................................................................................................................................47
Setting the Maximum Size of the Trace File...............................................................................................................47
Java Code Generation..............................................................................................................................................48
Using Other Logging APIs..............................................................................................................................................48
Read Only Databases....................................................................................................................................................48
Read Only Databases in Zip or Jar File............................................................................................................................48
Opening a Corrupted Database.................................................................................................................................49
Computed Columns / Function Based Index....................................................................................................................49
3 of 181

Multi-Dimensional Indexes.............................................................................................................................................49
User-Defined Functions and Stored Procedures...............................................................................................................50
Referencing a Compiled Method................................................................................................................................50
Declaring Functions as Source Code..........................................................................................................................50
Method Overloading.................................................................................................................................................51
Function Data Type Mapping....................................................................................................................................51
Functions That Require a Connection........................................................................................................................51
Functions Throwing an Exception..............................................................................................................................51
Functions Returning a Result Set..............................................................................................................................51
Using SimpleResultSet..............................................................................................................................................51
Using a Function as a Table......................................................................................................................................52
Pluggable or User-Defined Tables...................................................................................................................................52
Triggers........................................................................................................................................................................53
Compacting a Database.................................................................................................................................................53
Cache Settings..............................................................................................................................................................54
Performance Comparison...............................................................................................................................................55
Embedded...............................................................................................................................................................55
Client-Server...........................................................................................................................................................55
Benchmark Results and Comments...........................................................................................................................56
H2.....................................................................................................................................................................56
HSQLDB.............................................................................................................................................................56
Derby................................................................................................................................................................56
PostgreSQL........................................................................................................................................................56
MySQL...............................................................................................................................................................56
Firebird..............................................................................................................................................................56
Why Oracle / MS SQL Server / DB2 are Not Listed................................................................................................57
About this Benchmark..............................................................................................................................................57
How to Run........................................................................................................................................................57
Separate Process per Database...........................................................................................................................57
Number of Connections.......................................................................................................................................57
Real-World Tests................................................................................................................................................57
Comparing Embedded with Server Databases.......................................................................................................57
Test Platform.....................................................................................................................................................57
Multiple Runs.....................................................................................................................................................57
Memory Usage...................................................................................................................................................57
Delayed Operations............................................................................................................................................58
Transaction Commit / Durability..........................................................................................................................58
Using Prepared Statements.................................................................................................................................58
Currently Not Tested: Startup Time.....................................................................................................................58
PolePosition Benchmark.................................................................................................................................................58
Database Performance Tuning.......................................................................................................................................59
Keep Connections Open or Use a Connection Pool.....................................................................................................59
Use a Modern JVM...................................................................................................................................................59
Virus Scanners.........................................................................................................................................................59
Using the Trace Options...........................................................................................................................................59
Index Usage............................................................................................................................................................59
How Data is Stored Internally...................................................................................................................................59
Optimizer................................................................................................................................................................60
Expression Optimization...........................................................................................................................................60
COUNT(*) Optimization............................................................................................................................................60
Updating Optimizer Statistics / Column Selectivity......................................................................................................60
In-Memory (Hash) Indexes.......................................................................................................................................60
Use Prepared Statements.........................................................................................................................................61
Prepared Statements and IN(...)...............................................................................................................................61
Optimization Examples.............................................................................................................................................61
Cache Size and Type................................................................................................................................................61
Data Types..............................................................................................................................................................61
Sorted Insert Optimization........................................................................................................................................61
Using the Built-In Profiler..............................................................................................................................................61
Application Profiling.......................................................................................................................................................62
Analyze First............................................................................................................................................................62
Database Profiling.........................................................................................................................................................62
Statement Execution Plans............................................................................................................................................63
Displaying the Scan Count........................................................................................................................................63
Special Optimizations...............................................................................................................................................64
How Data is Stored and How Indexes Work....................................................................................................................64
Indexes...................................................................................................................................................................64
Using Multiple Indexes.............................................................................................................................................65
Fast Database Import....................................................................................................................................................66
Result Sets...................................................................................................................................................................67
Statements that Return a Result Set.........................................................................................................................67
4 of 181

Limiting the Number of Rows....................................................................................................................................67
Large Result Sets and External Sorting......................................................................................................................67
Large Objects...............................................................................................................................................................68
Storing and Reading Large Objects...........................................................................................................................68
When to use CLOB/BLOB..........................................................................................................................................68
Large Object Compression........................................................................................................................................68
Linked Tables...............................................................................................................................................................68
Updatable Views...........................................................................................................................................................68
Transaction Isolation.....................................................................................................................................................69
Table Level Locking..................................................................................................................................................69
Lock Timeout...........................................................................................................................................................69
Multi-Version Concurrency Control (MVCC).....................................................................................................................70
The MVCC mode is enabled by default in version 1.4.x, with the default MVStore storage engine. MVCC is disabled by 
default when using the PageStore storage engine (which is the default in version 1.3.x). The following applies when using 
the PageStore storage engine: The MVCC feature is not fully tested yet. The limitations of the MVCC mode are: with the 
PageStore storage engine, it can not be used at the same time as MULTI_THREADED=TRUE; the complete undo log (the list 
of uncommitted changes) must fit in memory when using multi-version concurrency. The setting MAX_MEMORY_UNDO has 
no effect. Clustering / High Availability...........................................................................................................................70
Using the CreateCluster Tool....................................................................................................................................70
Detect Which Cluster Instances are Running..............................................................................................................71
Clustering Algorithm and Limitations.........................................................................................................................71
Two Phase Commit.......................................................................................................................................................72
Compatibility.................................................................................................................................................................72
Transaction Commit when Autocommit is On.............................................................................................................72
Keywords / Reserved Words.....................................................................................................................................72
Standards Compliance...................................................................................................................................................72
Supported Character Sets, Character Encoding, and Unicode......................................................................................72
Run as Windows Service................................................................................................................................................73
Install the Service....................................................................................................................................................73
Start the Service......................................................................................................................................................73
Connect to the H2 Console.......................................................................................................................................73
Stop the Service......................................................................................................................................................73
Uninstall the Service................................................................................................................................................73
Additional JDBC drivers............................................................................................................................................73
ODBC Driver.................................................................................................................................................................73
ODBC Installation....................................................................................................................................................74
Starting the Server...................................................................................................................................................74
ODBC Configuration.................................................................................................................................................74
PG Protocol Support Limitations................................................................................................................................75
Security Considerations............................................................................................................................................75
Using Microsoft Access.............................................................................................................................................75
Using H2 in Microsoft .NET............................................................................................................................................75
Using the ADO.NET API on .NET...............................................................................................................................75
Using the JDBC API on .NET.....................................................................................................................................75
ACID............................................................................................................................................................................76
Atomicity.................................................................................................................................................................76
Consistency.............................................................................................................................................................76
Isolation..................................................................................................................................................................76
Durability................................................................................................................................................................76
Durability Problems.......................................................................................................................................................76
Ways to (Not) Achieve Durability..............................................................................................................................76
Running the Durability Test......................................................................................................................................77
Using the Recover Tool..................................................................................................................................................77
File Locking Protocols....................................................................................................................................................78
File Locking Method 'File'..........................................................................................................................................78
File Locking Method 'Socket'.....................................................................................................................................78
File Locking Method 'FS'...........................................................................................................................................79
Using Passwords...........................................................................................................................................................79
Using Secure Passwords...........................................................................................................................................79
Passwords: Using Char Arrays instead of Strings........................................................................................................79
Passing the User Name and/or Password in the URL..................................................................................................80
Password Hash.............................................................................................................................................................80
Protection against SQL Injection....................................................................................................................................80
What is SQL Injection...............................................................................................................................................80
Disabling Literals......................................................................................................................................................80
Using Constants.......................................................................................................................................................81
Using the ZERO() Function.......................................................................................................................................81
Protection against Remote Access..................................................................................................................................81
Restricting Class Loading and Usage..............................................................................................................................81
Security Protocols.........................................................................................................................................................82
User Password Encryption........................................................................................................................................82
5 of 181