没有合适的资源?快使用搜索试试~ 我知道了~
首页h2数据库引擎开发文档h2.pdf
h2数据库引擎开发文档h2.pdf
需积分: 10 6 下载量 103 浏览量
更新于2023-06-04
评论
收藏 1.26MB PDF 举报
h2数据库引擎开发文档h2.pdf Welcome to H2, the Java SQL database. The main features of H2 are: Very fast, open source, JDBC API Embedded and server modes; in-memory databases Browser based Console application Small footprint: around 1.5 MB jar file size
资源详情
资源评论
资源推荐
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
剩余180页未读,继续阅读
gsls200808
- 粉丝: 777
- 资源: 237
上传资源 快速赚钱
- 我的内容管理 收起
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
会员权益专享
最新资源
- zigbee-cluster-library-specification
- JSBSim Reference Manual
- c++校园超市商品信息管理系统课程设计说明书(含源代码) (2).pdf
- 建筑供配电系统相关课件.pptx
- 企业管理规章制度及管理模式.doc
- vb打开摄像头.doc
- 云计算-可信计算中认证协议改进方案.pdf
- [详细完整版]单片机编程4.ppt
- c语言常用算法.pdf
- c++经典程序代码大全.pdf
- 单片机数字时钟资料.doc
- 11项目管理前沿1.0.pptx
- 基于ssm的“魅力”繁峙宣传网站的设计与实现论文.doc
- 智慧交通综合解决方案.pptx
- 建筑防潮设计-PowerPointPresentati.pptx
- SPC统计过程控制程序.pptx
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
评论0