Shulou(Shulou.com)05/31 Report--

This article shows you what embedded databases are in Java, which are concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

I. introduction

First contact with embedded database (Embedded Database) may be a little vague, what is embedded database? Why use an embedded database? How to use embedded database? How to choose? This article will take you to reveal the answer.

From a software perspective, databases are classified into two categories:

First: database server (Database Server)

The second kind: embedded database (Embedded Database)

We generally call these well-known databases such as Oracle, PostgreSQL, MySQL and SQL Server as database servers. Of course, we do not rule out the possibility that some databases also provide embedded versions, such as MySQL Embedded is an embedded database.

While databases such as SQLite, Berkeley DB, Derby, H2, HSQL DB, etc., are embedded in the application and run with the application, we call it an embedded database.

The biggest difference between embedded databases and database servers is that they run in different address spaces. Typically, the database server runs a daemon process (daemon) independently, while the embedded database runs in the same process as the application.

In the actual development, the most contact should be the database server, the use of embedded database is relatively less.

But why is the embedded database?

In small applications, such as small handheld game consoles, it is not suitable to deploy database servers up to hundreds of megabytes, and there is no need for networking, resulting in a lightweight database requirement!

Compared with the common database, the embedded database has the characteristics of small size, complete functions, portability, robustness and so on. For example, the SVN version control software we are familiar with uses SQLite as the built-in database. The installation package of SQLite is only less than 350 KB. It is also widely used in microcomputers, such as Android, IOS and other mobile device operating systems have built-in SQLite database!

In view of the fact that there are many kinds of embedded databases, there are commercial charges and open source free ones! This article mainly introduces the open source free version, such as Derby, SQLite, H2 and so on. Let's practice the configuration and usage of each database.

II. Derby

Derby can be said to be a 100% database written by Java, and is open source free, very small, the core part of derby.jar is only 2m!

Many people may think that Derby is not very popular, but Derby has been developed for nearly two decades!

In 1996, a new company called Cloudscape Inc was founded with the goal of building a database server written in the Java language.

The company's first distribution was launched a year later, and the name of the product was changed to Cloudscape.

1999, Cloudscape, Inc. By the large database manufacturer Informix Software, Inc. Acquisition.

Informix Software was acquired by IBM in 2001, and the IBM Cloudscape ™database system was used as an embedded database engine in many IBM products.

In April 2004, IBM presented the Cloudscape database software to the Apache Software Foundation, and the Apache Derby project was born.

Then SUN donated a team to Derby. In JavaSE6.0, SUN named it JavaDB.

2.1. Project introduction

Because Derby is written in java, it is easier to integrate, and you can develop it by relying on library files in pom.xml directly through maven!

Org.apache.derby derby 10.14.1.0 runtime

2.2. Environment configuration

Like all database connections, it is basically the configuration of driver classes, connection addresses, accounts, passwords and other information.

String DRIVER_CLASS = "org.apache.derby.jdbc.EmbeddedDriver"; String JDBC_URL = "jdbc:derby:derbyDB;create=true"; String USER = "root"; String PASSWORD = "root"

Description:

Org.apache.derby.jdbc.EmbeddedDriver means to use the derby embedded database schema.

DerbyDB in JDBC_URL means to create a tempdb called derbyDB, which will be created automatically if not present.

USER and PASSWORD are mainly used for client login.

2.3. Unit test application

Next, let's write a test class using JDBC to test whether derby works properly.

Public class DerbyTest {/ * connect to the database in an embedded (local) connection * / private static final String JDBC_URL = "jdbc:derby:derbyDB;create=true"; private static final String DRIVER_CLASS = "org.apache.derby.jdbc.EmbeddedDriver"; private static final String USER = "root"; private static final String PASSWORD = "root" Public static void main (String [] args) throws Exception {/ / establish a connection to the database Class.forName (DRIVER_CLASS); Connection conn = DriverManager.getConnection (JDBC_URL, USER, PASSWORD); Statement statement = conn.createStatement (); / / delete table statement.execute ("DROP TABLE USER_INF") / / create table statement.execute ("CREATE TABLE USER_INF (id VARCHAR (50) PRIMARY KEY, name VARCHAR (50) NOT NULL, sex VARCHAR (50) NOT NULL"); / / insert data statement.executeUpdate ("INSERT INTO USER_INF VALUES ('1Qing,' Cheng Yaojin', 'male')") Statement.executeUpdate ("INSERT INTO USER_INF VALUES ('2mom,' Sun Shangxiang', 'female'); statement.executeUpdate (" INSERT INTO USER_INF VALUES ('3Qing,' monkey', 'male') "); / / query data ResultSet resultSet = statement.executeQuery (" select * from USER_INF ") While (resultSet.next ()) {System.out.println (resultSet.getInt ("id") + "," + resultSet.getString ("name") + "," + resultSet.getString ("sex"));} / / close connection statement.close (); conn.close ();}}

Output result:

1, Cheng Yaojin, male 2, Sun Shangxiang, female 3, Monkey, male

When the program is finished, it will generate a derbyDB folder in the root directory of the current project, which will store some persistent data. The next time you connect to the derbyDB database name, you can query the historical data that was inserted before. This feature can prevent data loss!

It is worth noting that derby does not support many mysql keywords, and derby does not support the insertion of null values.

In later versions, derby can also act as a database server, deployed separately on a single server via jar startup, adding IP and port numbers to the connection address, such as jdbc://derby://localhost:1527/derbyDB.

If you want to use the visual client tool to access and manage derby, you can use the SQuirreL SQL Client client to download the address http://www.squirrelsql.org/#installation.

At present, in the vast majority of embedded database applications, the number of appearances of derby is still small.

III. SQLite

SQLite is a powerful embedded relational database developed by D.RichardHipp with a small C library. Although its function is slightly lower than that of Berkeley DB (commercial database), it is easy to learn and faster. At the same time, it provides a rich database interface and provides most of the support for SQL92: support for multiple tables and indexes, transactions, views, triggers and a series of user interfaces and drivers.

SQLite not only supports Windows/Linux/Unix and other mainstream operating systems, but also can be combined with many programming languages, such as Tcl, C#, PHP, Java, etc., as well as ODBC interface. Compared with the two open source world-famous database management systems such as Mysql and PostgreSQL, its processing speed is faster than them. All source code is about 30,000 lines of C code, files are about 350KB, and support database size to 2TB!

Occupy the first place of embedded database almost all the year round!

3. 1. SQLite installation

3.1.1, windows installation

1. Visit the SQLite download page https://www.sqlite.org/download.html.

two。 Download the sqlite-tools-win32-*.zip and sqlite-dll-win32-*.zip zip files.

3. Create the folder C:\ sqlite, and extract the above two compressed files under this folder, and you will get sqlite3.def, sqlite3.dll, and sqlite3.exe files.

4. Add C:\ sqlite to the PATH environment variable.

Finally, at the command prompt, enter the sqlite3 command and display the following results to indicate that the installation is successful!

C:\ > sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";"

3.1.2, linux installation

Currently, almost all versions of the Linux operating system come with SQLite. So, just type sqlite3 on the command line to check whether SQLite is installed on the machine!

$sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";"

If you do not see the above results, the installation is also very simple!

1. Visit the SQLite download page https://www.sqlite.org/download.html.

two。 Download sqlite-autoconf-*.tar.gz and upload it to the linux server.

3. Perform installation operation

For example, an example of installation steps:

$tar xvzf sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $. / configure-- prefix=/usr/local $make $make install

3.1.3, mac installation

Mac installation is similar to linux.

3.2. Project introduction

Sqlite can also be developed by relying on library files in pom.xml directly through maven!

Org.xerial sqlite-jdbc 3.7.2

3.3. Environment configuration

String DRIVER_CLASS = "org.sqlite.JDBC"; String JDBC_URL = "jdbc:sqlite:sqliteDB.db"; String USER = "root"; String PASSWORD = "root"

Description:

Org.sqlite.JDBC means to use the sqlite embedded database schema.

SqliteDB in JDBC_URL means to create a tempdb called sqliteDB, which will be created automatically if not present.

USER and PASSWORD are mainly used for client login.

3.4. Unit test application

Next, let's test whether sqlite is working properly.

Public class SQLiteTest {/ * connect to the database in an embedded (local) connection * / private static final String JDBC_URL = "jdbc:sqlite:sqliteDB.db"; private static final String DRIVER_CLASS = "org.sqlite.JDBC"; private static final String USER = "root"; private static final String PASSWORD = "root" Public static void main (String [] args) throws Exception {/ / establish a connection to the database Class.forName (DRIVER_CLASS); Connection conn = DriverManager.getConnection (JDBC_URL, USER, PASSWORD); Statement statement = conn.createStatement (); / / delete table statement.execute ("DROP TABLE IF EXISTS USER_INF") / / create table statement.execute ("CREATE TABLE USER_INF (id VARCHAR (50) PRIMARY KEY, name VARCHAR (50) NOT NULL, sex VARCHAR (50) NOT NULL"); / / insert data statement.executeUpdate ("INSERT INTO USER_INF VALUES ('1Qing,' Cheng Yaojin', 'male')") Statement.executeUpdate ("INSERT INTO USER_INF VALUES ('2mom,' Sun Shangxiang', 'female'); statement.executeUpdate (" INSERT INTO USER_INF VALUES ('3Qing,' monkey', 'male') "); / / query data ResultSet resultSet = statement.executeQuery (" select * from USER_INF ") While (resultSet.next ()) {System.out.println (resultSet.getInt ("id") + "," + resultSet.getString ("name") + "," + resultSet.getString ("sex"));} / / close connection statement.close (); conn.close ();}}

Output result:

1, Cheng Yaojin, male 2, Sun Shangxiang, female 3, Monkey, male

When the program is finished, a file named sqliteDB customized above will also be generated in the root directory of the current project.

Each time you operate on a database named sqliteDB, the data is persisted to the sqliteDB file to prevent data loss.

If you want to use visual client tools to access and manage sqliteDB, you can use navicat to connect, select the generated sqliteDB file, enter the appropriate account, password, you can manage and maintain!

IV. H2

H3 is another embedded database written by pure java. It is only a class library, that is, only one jar file, which can be directly embedded in the application project. At the same time, it also provides a very friendly database management interface based on web.

Many developers on the Internet compare it with derby, saying that it is compatible with mysql database and has a good reputation.

Whether this is really the case or not, we will conduct corresponding performance tests later. Let's take a look at how to use it in development.

4.1. Project introduction

Since h3 is written in pure java, you can rely on library files in pom.xml directly through maven for development!

Com.h3database h3 1.4.200 runtime

4.2. Environment configuration

String DRIVER_CLASS = "org.h3.Driver"; String JDBC_URL = "jdbc:h3:mem:h3DB"; String USER = "root"; String PASSWORD = "root"

Description:

Org.h3.Driver means to use the H3 embedded database schema.

H3DB in JDBC_URL means to create a tempdb named h3DB.

USER and PASSWORD are mainly used for client login.

4.3, unit test application

Next, let's test whether H3 can be used properly.

Public class H2Test {/ * connect H2 database with embedded (local) connection * / private static final String JDBC_URL = "jdbc:h3:mem:h3DB"; private static final String DRIVER_CLASS = "org.h3.Driver"; private static final String USER = "root"; private static final String PASSWORD = "root" Public static void main (String [] args) throws Exception {/ / establish a connection to the database Class.forName (DRIVER_CLASS); Connection conn = DriverManager.getConnection (JDBC_URL, USER, PASSWORD); Statement statement = conn.createStatement (); / / delete table statement.execute ("DROP TABLE IF EXISTS USER_INF") / / create table statement.execute ("CREATE TABLE USER_INF (id VARCHAR (50) PRIMARY KEY, name VARCHAR (50) NOT NULL, sex VARCHAR (50) NOT NULL"); / / insert data statement.executeUpdate ("INSERT INTO USER_INF VALUES ('1Qing,' Cheng Yaojin', 'male')") Statement.executeUpdate ("INSERT INTO USER_INF VALUES ('2mom,' Sun Shangxiang', 'female'); statement.executeUpdate (" INSERT INTO USER_INF VALUES ('3Qing,' monkey', 'male') "); / / query data ResultSet resultSet = statement.executeQuery (" select * from USER_INF ") While (resultSet.next ()) {System.out.println (resultSet.getInt ("id") + "," + resultSet.getString ("name") + "," + resultSet.getString ("sex"));} / / close connection statement.close (); conn.close ();}}

Output result:

1, Cheng Yaojin, male 2, Sun Shangxiang, female 3, Monkey, male

H3 as an embedded database application, you can choose two types of url, the first is to run in memory mode, in which the data is temporarily placed in memory, and the data table and data are destroyed immediately after the end of the program, and they no longer exist; the second is to use local file mode to persist the data into files, and when you connect to the database again, you can get historical data.

# first, run jdbc:h3:mem:testDB in memory mode # second, use local file mode jdbc:h3:file:./target/testDB

H3 can also be used as a database server, deployed separately on the server, and the application operates through a remote connection as follows:

# connection syntax jdbc:h3:tcp:// [:] / [] # example: jdbc:h3:tcp://localhost:8080/~/test

If you want to use a visual client tool to access and manage h3, you can manage it through its own web page.

Let's take the springboot project as an example, which is maintained by configuring to use the management page that comes with H3.

Create a new WebConfig class and configure the web console of h3 as follows:

@ Configuration public class WebConfig {/ * add the mapped address of the h3 console * @ return * / @ Bean ServletRegistrationBean h3servletRegistration () {ServletRegistrationBean registrationBean = new ServletRegistrationBean (new WebServlet ()); registrationBean.addUrlMappings ("/ h3merconsoleplacca *"); return registrationBean;}}

Start the springboot project, open a browser, and enter http://127.0.0.1:8080/h3-console to access the management page!

After filling in the connection address, account number and password, connect!

You can write sql statements to query in the workbench, and edit the query results at the same time.

Fifth, performance testing

In the above, we introduced the use of the three databases. Next, let's test the performance of the three databases by inserting 1000, 10000 and 100000 operations in a loop to see how they perform.

What are the embedded databases in Java? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.