Kwert-soft.ru

IT Софт для ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Embedded database java

Java Embedded Databases Comparison [closed]

Want to improve this question? Update the question so it’s on-topic for Stack Overflow.

Closed 6 years ago .

I intend to develop a small (Java) application for managing my finances. I believe I need to use an embedded database, but I have no experience regarding this issue. I tried to look at some of the available products, but I can’t decide which one would be more suitable for me. H2, HSQLDB, Derby and Berkeley DB seem to be good candidates, but I still don’t see how they compare to each other. I appreciate your help comparing them and helping me decide which one to use.

I intend to use Hibernate for my application (unless you would recommend using DBMS-provided API), but I also want to have the ability to edit the database easily using a SQL browsing tool (modifying schema and changing data).

18 Answers 18

  • HSQLDB — Used by OpenOffice, tested and stable. It’s easy to use. If you want to edit your db-data, you can just open the file and edit the insert statements.
  • H2 — Said to be faster (by the developer, who originally designed hsqldb, too)

Which one you use is up to you, depending how much performance and how much stability you need.

The developer of H2 has put up a nice performance evaluation:
http://www.h2database.com/html/performance.html

I use Apache Derby for pretty much all of my embedded database needs. You can also use Sun’s Java DB that is based on Derby but the latest version of Derby is much newer. It supports a lot of options that commercial, native databases support but is much smaller and easier to embed. I’ve had some database tables with more than a million records with no issues.

I used to use HSQLDB and Hypersonic about 3 years ago. It has some major performance issues at the time and I switch to Derby from it because of those issues. Derby has been solid even when it was in incubator at Apache.

I needed to use Java embedded database in one of my projects and I did lot of research understanding pros and cons of each database. I wrote a blog listing pros and cons of popular embedded java databases (H2, HSQLDB, Derby, ObjectDB, Neo4j, OrientDB), you can have a look at it. I chose H2 as I thought it best suited my requirements. Link for the blog: http://sayrohan.blogspot.in/2012/12/choosing-light-weight-java-database.html Hope it helps!

I’d go with H2, the performance is meant to much better than Derby. Read http://www.h2database.com/html/performance.html for more info.

HSQLDB is a good candidate (the fact that it is used in OpenOffice may convinced some of you), but for such a small personnal application, why not using an object database (instead of a classic relationnal database) ?

I used DB4O in one of my projects, and I’m very satisfied with it. Being object-oriented, you don’t need the whole Hibernate layer, and can directly insert/update/delete/query objects ! Moreover, you don’t need to worry about the schema, you directly work with the objects and DB4O does the rest !

I agree that it may take some time to get used to this new type of database, but check the DB40 tutorial to see how easy it makes working with the DB !

EDIT: As said in the comments, DB4O handles automatically the newer versions of the classes. Moreover, a tool for browsing and updating the database outside of the application is available here : http://code.google.com/p/db4o-om/

Java DB (Sun’s distribution of Apache Derby) now ships in JDK 6!

I’ve been wanted to do something like Jason Cohen and have been thinking this looks like the easiest way being in the JDK distro (which of last week is now a requirement for my app). Or maybe I am just lazy that way.

We use HSQLDB in production as a «no-configuration» option for our application. It allows people to trial without the hassle of setting up a real database.

However we do not support it for normal use. The reasons are several:

  1. Slows down proportionally to the size of the data.
  2. Difficult to access outside of our app (e.g. for custom reports).
  3. Transactions / disk-sync is difficult to get right, so it’s easy to lose data.

For at least (2) and (3), there are ways around it but it’s difficult; it’s much easier to e.g. install MySQL.

an embedded, disk-based, fully transactional Java persistence engine that stores data structured in graphs rather than in tables

I haven’t had a chance to try it yet — but it looks very promising. Note this is not an SQL database — your object graph is persisted for you — so it might not be appropriate for your existing app.

Good comparison tool can be found here: http://www.jpab.org/All/All/All.html

Notice also the Head to Head DBMS/JPA Comparisons

Most things have been said already, but I can just add that I’ve used HSQL, Derby and Berkely DB in a few of my pet projects and they all worked just fine. So I don’t think it really matters much to be honest. One thing worth mentioning is that HSQL saves itself as a text file with SQL statements which is quite good. Makes it really easy for when you are developing to do tests and setup data quickly. Can also do quick edits if needed. Guess you could easily transfer all that to any database if you ever need to change as well 🙂

HSQLDB may cause problems for large applications, its not quite that stable.

Читать еще:  Java net bindexception

The best I’ve heard (not first hand experience however) is berkleyDB. But unless you opensource it, it will cost you an arm and a leg to use due to licensing. see this http://www.oracle.com/technology/software/products/berkeley-db/htdocs/licensing.html for details.

ps. berkleyDB is not a relational database in case you didnt know.

Performance has become much better since the early releases. The licensing model isnt too bad, either. I particularly like the options available for querying your objects. Query by example is very powerful and easy to get used to.

What criteria will you use to evaluate these ? If you don’t know yet, then you don’t need to decide right now. Try to make your application as database-implementation-agnostic as you can — providing the appropriate wrappers, data access objects etc., and make this decision when you have all the facts to hand and you have to decide.

If you’re using relational databases and SQL then the above shouldn’t be too hard (using JDBC etc). Make sure you have plenty of surrounding tests so that when you want to switch between databases, you can determine that your application’s functionality remains the same.

I ran into the same issue some time ago. I didn’t know which database to go for, so my first solution used Derby (or HSQLDB?), and I was later able to switch to HSQLDB (or Derby ? Can’t remember which solution worked) once I’d determined where I had issues (relating to performance) and which solution would really work for me.

Пример использования базы данных H2 в embended-режиме в Eclipse

Статья предназначено прежде всего для новичков, т.к. и сам я не являюсь программистом по своей специальности, и на Java начал программировать совсем недавно.

Мне захотелось написать одну простую программу, в которой использовалось бы встроенная БД. Немного погуглив я вышел на H2. Об этой БД были хорошие отзывы, и в сравнении производительности у нее были хорошие результаты, поэтому ее и выбрал.

Далее у меня появилась проблема как же ее все-таки использовать. Дальше я снова погуглил, и нашел вот это. Здесь правда для NetBeans, в Eclipse я делал по другому. В первую очередь это касается создания таблиц. Далее опишу что у меня получилось.

1. Скачиваем с сайта установочный файл или zip архив. Устанавливаем и распаковываем.

2. Для того чтоб было проще, я скопировал в отдельную папку h2-*.jar из папки bin и папку javadoc из папки docs.

3. Далее запускаем Eclipse, создаем новый проект, задаем ему имя и нажимаем Next. В следующем окне выбираем Add External JAR’s и там выбираем наш h2-*.jar. Разворачиваем список появившийся и выбираем Javadoc Location нажав кнопку Edit, выбираем папку javadoc. И жмем Finish.

4. Создаем package и class которые вам нужны. Далее последует код который поможет вам создать базу, таблицу, заполнить ее значениями и получить их.

import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;

public class ConnectH2 <

public static void main(String[] args) <
try <
Class.forName(«org.h2.Driver»).newInstance();
Connection conn = DriverManager.getConnection(«jdbc:h2:test»,
«sa», «»);
Statement st = null;
st = conn.createStatement();
st.execute(«INSERT INTO TEST VALUES(default,’HELLO’)»);
st.execute(«INSERT INTO TEST(NAME) VALUES(‘JOHN’)»);
String name1 = «Jack»;
String q = «insert into TEST(name) values(?)»;
PreparedStatement st1 = null;

st1 = conn.prepareStatement(q);
st1.setString(1, name1);
st1.execute();

ResultSet result;
result = st.executeQuery(«SELECT * FROM TEST»);
while (result.next()) <
String name = result.getString(«NAME»);
System.out.println(result.getString(«ID»)+» «+name);
>
> catch (Exception e) <
e.printStackTrace();
>
>
>

Вот небольшие комментарии по коду.

Обработка ошибки try..catch – я использовал простую обработку Exception, иначе выдавалось бы сообщение об ошибке что не обрабатываются исключения, и просило бы обработать исключения SQLException и еще какие-то, просто Exception упрощает в данном случае написание кода, но не думаю что это слишком правильно и следует все таки более аккуратно обрабатывать исключения.

В этой строчке я не совсем понял для чего используют newInstance(), в примере на сайте H2 без нее обходятся, но в примере для NetBeanse по ссылке выше пишется вот так. Вроде бы, как я понял помогает избежать каких то исключений.

Connection conn = DriverManager.getConnection(«jdbc:h2:test»,»sa», «»);

Первое здесь это путь к файлу с БД. Если написать так, то он создаться в корне с программой (jar файлом, а в случае запуска из Eclipse — в корне проекта), так же можно задать путь в какую-нибудь папку, относительный путь и т.п. Далее идет имя администратора и пароль. Так как я создаю встроенную базу данных, то не стал пока разбираться с пользователями и т.п. После этого должен создаться файл test.h2.db. Так же этот файл можно зашифровать сразу же. В файле H2.pdf есть раздел «Connecting to an Encrypted Database» и там есть пример кода. Так же еще хотелось бы сказать что путь к базе, логин и т.д. имеет смысл вынести в отдельные константы, в данном случае я опять же упростил написание просто.

Назначение переменных Statement и PreparedStatment для меня выглядит одинаковым, просто в случае с PreparedStatment позволяет нам создавать запросы в БД с использованием переменных.
Обратите внимание на:
st1.setString(1, name1);

Здесь идет преобразование переменной к необходимому типу, есть еще setInt и т.д. Здесь 1 — это номер знака вопроса в запросе.

insert into TEST(name) values(?)

Так же существует несколько способов выполнить запрос – execute, executeQuery и executeUpdate, так что стоит про них почитать в javadoc на H2 может вам это и пригодится, мне пока что не нужно было.

Ну и последнее — переменная ResultSet result – судя по описание в ней довольно много чего можно выполнить, мне она пригодилась только для одного вывода из базы данных. Знать надо вот что, result.next() надо сделать в первую очередь, до того как вытаскивать результаты, иначе будет ошибка, изначально переменная result ссылается на строку -1 (если так можно выразиться), а там ничего нет.

Читать еще:  Java android intent

В примере для NetBeanse немного по другому написан цикл вывода, с тем вариантом я не разобрался, возникали ошибки, у меня заработал мой вариант.

Так же в реальной программе я это все разделю на отдельные нужные мне функции, и так же не забываем о том что надо еще отсоединиться от базы и закрыть ее: st.close(); и conn.close(); Например это стоит делать в блоке finally после обработки ошибки. Я этого в данном случае не делал.

Как запустить java программу без Eclipse?

Выбираем File-Export, там уже выбираем Java-Runable JAR File, затем Next.
Launch Configuration – выбираем наш проект. Задаем имя выходного файла и его расположение.
Выбираем library handling.
Как я понял первый вариант копируем только необходимый ему код, второй вариант упаковывает все целиком (в нашем случае h2-*.jar), третий вариант – создает поддиректорию, в которой будет располагаться h2-*.jar.
Если вы пишете только для себя то тут думаю неважно какой вариант вы выберите, но если хотите распространять, то по-моему лучше всего третий вариант, или второй, про первый вариант сам Eclipse выдает предупреждение что могут быть проблемы с лицензией. Лицензия на H2 вот тут.

Создали jar файл, теперь нужно его запустить, пишем в консоли:
java –jar test.jar
где test.jar – имя вашего файла.

После установке в Windows, появляются еще ссылки на H2 Console в меню «Пуск». Запускаем одну из них, и должен открыться браузер с формой в которой если вы укажите путь до вашей базы, логин, пароль и т.д. то сможете выполнить какие-либо запросы к вашей базе.

В примерах по H2 есть примеры подключения через браузер к базе, там что то пишется в файл web.xml, но как понимаю это не для простого java приложения. Так что способ описанный мной, может пригодиться.

Данная статья не подлежит комментированию, поскольку её автор ещё не является полноправным участником сообщества. Вы сможете связаться с автором только после того, как он получит приглашение от кого-либо из участников сообщества. До этого момента его username будет скрыт псевдонимом.

Java DB Embedded Mode

Posted by: Prasad Saya in Core Java July 4th, 2013 10 Comments Views

Java DB is a relational database management system that is based on the Java programming language and SQL. This is the Oracle release of the Apache Software Foundation’s open source Derby project. Java DB is included in the Java SE 7 SDK.

Java DB has two deployment options: Embedded and Network Server.

This post is about the Embedded deployment or mode.

1. Embedded

In an Embedded mode:

  • The database is accessed from one application only.
  • There is no network connectivity between the application and the database.
  • The application and the database run in the same JVM (Java Virtual Machine).

The database is accessed from a Java application using JDBC (Java Database Connectivity). Typically, the database is started and stopped by the application that accesses it. And, there is no database administration required.

1.1. Starting Java DB

Start the Java DB from an application. First, load the database JDBC driver. Next, connect to the database with a connection URL.

  • The driver is included in the Java DB: org.apache.derby.jdbc.EmbeddedDriver
  • The connection URL format is: jdbc:derby:databaseName;URLAttributes;
  • An example connection URL is jdbc:derby:SampleDB;create=true;, where, the database name is SampleDB and the ‘create=true’ is an attribute name/value pair.

The following is an example Java method that starts the database using JDBC:

The Connection object created in the above method is used to access the database objects and data using SQL. sampleDB is the name of the database.

NOTE: The derby.jar library is required to be in the classpath of an application that uses Java DB Embedded. This library includes the driver program. This is included with the Java DB.

1.2. Accessing Java DB Database Interactively With ‘ij’

ij is a command line tool included with Java DB. ij is a JDBC tool used to run interactive queries on a Java DB database. This is in the bin directory of Java DB installation.

Start ij, create a database and run SQL commands to create and access data:

This creates a database named testDB in the current directory and connects to it. The Java DB database is stored in files within a directory with the same name as that of the database name.

2. Usage Example

An example usage is a Java Swing based desktop application that uses an Embedded database. The first time, the application starts, the database and its objects are created. Subsequently, the application creates or accesses data in the database. The application also has an online backup database function (i.e., make a backup copy while the database is open) and a restore from the backup database copy function. The database is closed with the application.

3. Connect to Java DB Embedded from Multiple Clients

Java DB in Embedded mode can be accessed from multiple clients. A Java Servlet application deployed on a web server and connects to the database, is an example. The Java DB database is configured as a resource of DataSource type, on the web server. The Servlet application starts the database and creates Connection objects. The Connection objects are created using the DataSource object, rather than the DriverManager. Multiple web clients access the database data through the application.

3.1. An Example

  • Configure DataSource Resource on a Web Server
  • Access the Database from a Servlet (using the configured DataSource)
Читать еще:  Java regexp substring

The configuration is for Apache Tomcat 6 web server. The following code samples use Java Servlet (Java EE), JNDI (Java Naming and Directory Interface) and JDBC API.

3.1.1. Configure DataSource Resource on Web Server

This is called as the context configuration. This defines the JNDI name of a JDBC data source as a resource for the Java DB database used in the web application (for JNDI lookup). Also, specifies the resource parameters.

The following entry is added to the file ‘/META-INF/context.xml’. The META-INF directory is to be in the root directory of the web application deployment WAR file.

  • name: is the JNDI name of the data source resource.
  • url: specifies the JDBC database connection URL of the format jdbc:derby:databaseName;URLAttributes;. And, testDB is the name of the Java DB database to connect to.

Note that, an application accesses the Embedded database from the Derby system directory. By default, the current directory (the JVM system property user.dir) is the system directory.

For Java DB, the Derby system directory can be specified (optional), by setting the JVM system property derby.system.home. This is set when using the ‘java’ command from operating system command prompt (for example, java -Dderby.system.home=D:mydatabases MyApplication) or from a Java program (using java.lang.System class’s setProperty()).

NOTE: The Embedded database driver (derby.jar) is to be placed in ’CATALINA_HOMElib’ directory. CATALINA_HOME is the Tomcat installation directory.

3.1.2. Access the Database from Servlet

The following Java code shows a servlet class accessing the Java DB database using the configured DataSource resource. The code assumes that the database is already created (using another application or interactively using ij).

4. Network Server (aka Server)

In this mode, Java DB is accessed from multiple clients over a network – a client-server configuration. The database and the applications run in different JVMs. The database on the server is accessed from a Java application using JDBC.

The following is an example of the Server usage.

4.1. Start the Server

Start and stop the server from the Windows command prompt using the provided batch programs (these are in the bin directory of Java DB installation): startNetworkServer.bat and stopNetworkServer.bat.

4.2. Access Java DB from Client Application

Load the driver and connect to the database with a connection URL.

  • The driver is: org.apache.derby.jdbc.ClientDriver
  • The connection URL format is: jdbc:derby://server:port/databaseName;URLAttributes;
  • server is the host name (or ip address), and port is the port number (1527 is the default) where the server is listening for client requests.

The following is an example Java method that starts the database using JDBC:

The Connection object created in the above method is used to access the database objects and data using SQL. sampleDB is the name of the database.

An Overview of 3 Java Embedded Databases

Embedded databases move management from standalone systems to your app. Let’s take a look at HSQLDB, H2, and Apache Derby to see how they can work with Java apps.

Join the DZone community and get the full member experience.

An embedded database is a database technology in which database management solutions are embedded into an application, rather than provided as standalone database systems.

Embedded databases can be very helpful during the development or testing phases because they are lightweight, fast, improve testability, and ease of configuration. We have plenty of choices for incorporating embedded databases. Be sure to apply pragmatism while picking one because each has their own pros and cons.

In this article, I will go over some of the embedded databases for working with Java.

HSQLDB (HyperSQL Database)

HyperSQL Database conforms to the SQL standard and JDBC specifications.

It supports all the core features that are expected from a modern relational database. It can be run either in embedded or server mode. HyperSQL database is built in pure Java and supports JDK 5, 6, 7 and 8 in HyperSQL 2.x.

It provides support for CallableStatement and PreparedStatement, including batch executions to speed up data processing. With version 2.3.x onwards, it supports two-phased locking and multiversion concurrency control.

The package contains a JAR file which contains the required components such as HyperSQL RDBMS Engine and the JDBC driver for embedding HyperSQL in a Java application.

Connection

A connection can be established as follows:

Check the official documentation for more details.

H2

H2 is the Java SQL database. It’s open source and supports the JDBC API. It supports both embedded and server modes and provides support for row-level locking and multiversion concurrency.

Connection

A connection can be established as follows:

/ indicates user home directory.

In their official documentation, the comparison of H2 with other database engines is quite comprehensive, which makes it easier to pick the one of your choice.

Apache Derby

Apache Derby is an open source relational database implemented entirely in Java and available under the Apache License, Version 2.0. It is based on Java, JDBC, and SQL standards.

It supports client/server and embedded modes. All the standard features of a relational database are supported by Derby.

Connection

Use derby.jar in your application. A connection can be established as follows:

Summary

The JAR files provided by these database providers contain all the necessary components for CRUD operations. Working with embedded databases is easy and fun. You just need to remember a few tips to make your application work with embedded databases successful!

Ссылка на основную публикацию
Adblock
detector