Fan Peizhong 2017-06-19
H2 is a Java-developed embedded database that is itself a class library that can be embedded directly into an application project. The biggest use of H2 is that it can be packaged and distributed with applications, which makes it very easy to store a small amount of structured data. Another use of it is for unit testing. The startup speed is fast, and the persistence feature can be turned off, and every use case is restored to its original state when it is executed. The third use of H2 is as a supplement to the cache as a nosql. When the data model must be relational in some scenarios, it can be used as a buffer layer for back-end mysql/oracle, caching some data that does not change frequently but requires frequent access, such as dictionary tables, memcached, and Permissions tables. However, the system architecture will be more complex.
First, Product advantagesPure Java writing, not limited by the platform; only one jar file is suitable for use as an embedded database; H2 provides a very convenient web console for manipulating and managing database content, full functionality, and support for standard SQL and JDBC. Perfectly formed; supports inline mode, server mode, and cluster.
Second, download and install(a) Download H2 database download Address: http://www.h2database.com/html/download.html
Smart as you will definitely choose the second one. Windows, Linux can be extracted directly to use, consistent with the two middle-aged developers of the use of the habit. (ii) The directory structure after decompression: H2 |---bin | |---The jar package for the H2-1.1.116.jar//H2 database (driver is also inside) | |---h2.bat//windows console startup script | |---h2.sh//linux console startup script | |---h2w.bat//windows console startup script (without a black screen window) | Help documentation for---docs//H2 database (user's Manual for H2 database) |---servic E//through wrapper packaging into a service. |---src//H2 database Source code |---build.bat//windows build script |---build.sh//linux build script
At this point, even if "install" is complete.
three, operation mode and Operation Way(a) operating mode H2 has three modes of operation. 1. Inline mode (Embedded mode), the application and the database are in one JVM and are connected through JDBC. Can be persisted, but only one client connection at a time. Inline mode performance is better.
2. Server mode uses the same server mode as the inline mode, except that it can run in another process.
3, Mixed mode the first application in the embedded mode to start it, for the later application it is server mode running. Mixed mode is a combination of inline mode and server mode. The first application connects to the database in inline mode and starts as a server, so that other applications (running on different processes or virtual machines) can access the same data at the same time. The first application's local area connection is as fast as the embedded mode, while the other connections are theoretically slightly slower.
(b) The mode of operation of the development of the server is certainly convenient, because the need to use tools to manage data. So enter the bin directory of the H2.
If you can run H2.bat or h2w.bat in the win environment. The only difference is that the latter is silently running in the background.
If you are in a Linux environment, you can run the./h2.sh file merrily running up the database service. But it is not recommended Jiangzi, because there are many inconveniences. Can be modified as follows: Copy h2.sh to h2_server.sh file;
Edit the h2_server.sh as follows: #!/bin/shdir=$ (dirname "$") java-cp "$dir/h2-1.3.176.jar: $H 2DRIVERS: $CLASSPATH" Org.h2.tools.server-tcpallowothers-weballowothers-webport 8082 "$@" description: Org.h2.tools.Server: Start in server mode-tcpallowoth ERS: Allows remote machines to access-weballowothers via TCP: Allows remote machines to access-webport 8082 via a browser: The default access port (8082 is an unoccupied port, and if the port is already occupied by another port) the specific operation Line mode ①chmod modify file permissions; ② input nohup./h2_server.sh & Enter. This can be run in the background;
Iv. Remote ManagementTo access the H2 access address via a remote browser: http://server ip:8082/, the following page appears.
2. The path configuration jdbc:h2:tcp://localhost//usr/h2/data/rlib is the path of the H2. TCP represents access using TCP. localhost/is IP. Because the example is under Linux, the file path of the database is "/usr/h2/data/rlib", so there is a/. localhost/behind it. Detailed URL settings are visible in this appendix. (as smart as you might ask why, since H2 is deployed remotely under Linux but uses a browser to access IP, it also fills in localhost.) Do not tell you) user Name:sa is the H2 default, the password can be empty. But as a middle-aged, always-in-one developer, set a password, especially if your H2 is deployed on a public network. Otherwise it will be boring people to delete the data to sweat pants are not left.
3. Click the figure Test Connect button to test the connection. This is automatically created if the database file itself does not exist. The database file is automatically generated to the/usr/h2/data/directory, named Rlib.mv.db. Click Connect to access the management terminal.
H2 's management terminal is doing very well. Press Ctrl+enter to execute the input area of SQL, and press Shift+enter to execute the currently selected SQL.
v. Data backup and RecoveryDatabases that cannot back up and import data are lame. You can export to a CSV format file by using the Csvwrite method, or you can import data through Csvread.
(i) Export function Csvwrite
Example: Call Csvwrite (' test2.csv ', ' SELECT * from TEST ', ' charset=utf-8 fieldseparator=| ');
Note: Even if the export is encoded, the exported file with Vim, EditPlus view is normal, but the Excel view will still be garbled. You need to save the editplus as Unicode, just fine.
(ii) Import function CsvwriteSyntax: Csvread (filenamestring [, columnsstring [, Csvoptions]]) example ①:
Import Data:INSERT into TEST (the SELECT * from Csvread (' d:/test.csv ')); Example ②:
import structure and data,Create a H2 data table from a CSV file. CSV file data creation TEST table CREATE TABLE test as SELECT * from CS vread (' d:/test.csv '); Create the test table, insert the corresponding column of the CSV file into the test table corresponding fields create TABLE TEST (ID INT PRIMARY KEY, NAME VARCHAR (255)) as SELECT * from Csvread (' d:/t Est.csv ');
(c) csvoptions
VI. Application DevelopmentFinally to the development of the use of the link. The development of H2 database is very simple, and MySQL is not much different. (i) Join Maven dependency <
Dependency> <
groupId> Com.h2database </
groupId> <
Artifactid> H2 </
Artifactid> <
version> 1.4.195 </
version> </
Dependency> (ii) Creation of Db.properties
Driverclassname=
Org.h2.Driver#此处的写法是使用嵌入模式链接H2库, the line is followed by a TCP connection using the server mode. The difference is that the former performs better, while the latter can connect multiple clients simultaneously
URL=
jdbc:h2:d:/test#url =jdbc:h2:tcp://1localhost//usr/h2/data/rlib
username=
SA
Password=
PS: When using embedded mode to link the H2 library, using the Manage page connection will error the following:
(iii) Use of the db.properties can be developed using the H2 database. The specific database operation and so on does not belong to the H2 category, may refer to other information by oneself.
Appendix: H2 Database different mode connection string
|
URL Format and Examples |
| Embedded (local) connection |
Jdbc:h2:[file:][<path>]<databasename>
Jdbc:h2:~/test
Jdbc:h2:file:/data/sample
Jdbc:h2:file:c:/data/sample (Windows only) |
| In-memory (Private) |
Jdbc:h2:mem: |
| In-memory (named) |
Jdbc:h2:mem:<databasename>
Jdbc:h2:mem:test_mem |
| Server mode (remote connections) using TCP/IP |
Jdbc:h2:tcp://<server>[:<port>]/[<path>]<databasename>
Jdbc:h2:tcp://localhost/~/test
Jdbc:h2:tcp://dbserv:8084/~/sample
Jdbc:h2:tcp://localhost/mem:test |
| Server mode (remote connections) using TLS |
Jdbc:h2:ssl://<server>[:<port>]/<databasename>
Jdbc:h2:ssl://localhost:8085/~/sample; |
| Using Encrypted files |
jdbc:h2:<url>; Cipher=aes
Jdbc:h2:ssl://localhost/~/test; Cipher=aes
Jdbc:h2:file:~/secure; Cipher=aes |
| File Locking Methods |
jdbc:h2:<url>; file_lock={file| Socket|no}
Jdbc:h2:file:~/private; Cipher=aes; File_lock=socket |
| Only open if it already exists |
Jdbc:h2:<url>;ifexists=true
Jdbc:h2:file:~/sample;ifexists=true |
| Don ' t close the database when the VM exits |
Jdbc:h2:<url>;D B_close_on_exit=false |
| Execute SQL on connection |
Jdbc:h2:<url>;init=runscript from ' ~/create.sql '
Jdbc:h2:file:~/sample;init=runscript from ' ~/create.sql '; RunScript from ' ~/populate.sql ' |
| User name and/or password |
jdbc:h2:<url>[; user=<username>][; Password=<value>]
Jdbc:h2:file:~/sample; User=sa; Password=123 |
| Debug Trace Settings |
jdbc:h2:<url>; Trace_level_file=<level 0..3>
Jdbc:h2:file:~/sample; Trace_level_file=3 |
|
