Thursday, August 4, 2016

H2 DB with Java Example

INTRODUCTION

  • H2 Database Engine is very fast, open source and lightweight.
  • It is another Java Database and it's completely written in java.
  • It is an in-memory database.
  • Easy installation.
  • Tables can be created in an in-memory or local disk.

SOFTWARES & TOOLS

  1. H2 DB
  2. Eclipse IDE (Mars 2)
  3. SQL-Workbench

INSTALLATION

  1. Download latest H2 DB Installer from http://www.h2database.com/html/main.html
  2. Install the DB to your local drive: C:\ranjiths\jbr\InstalledSofties\H2

CREATE DATABASE & TABLES

Unlike other Open Source In-Memory Database, there is no default DB Editor Tool within H2. So download SQL-WorkBench and install for running the SQL Queries.

Now, Open the SQL-WorkBench and enter below details to create a new connection.
  1. Default Group Name : JBR
  2. Driver : Select H2 Database(org.h2.Driver)
  3. URL: jdbc:h2:C:/ranjiths/jbr/InstalledSofties/h2db/db
  4. Username : ranjith
  5. Password : sekar
Click OK

Create a table and insert some data.
CREATE TABLE users (
 user_id INTEGER NOT NULL CONSTRAINT EMP_NO_PK PRIMARY KEY,
 first_name VARCHAR(30) NOT NULL,
 last_name VARCHAR(30) NOT NULL,
 email VARCHAR(40),
 phone INTEGER
   );

INSERT INTO users VALUES(1,'Ranjith','Sekar','ranjith@gmail.com',96000);
INSERT INTO users VALUES(2,'Manoj','Kumar','manoj@gmail.com',88000);
INSERT INTO users VALUES(3,'Sachin','Tendulkar','sachin@gmail.com',77000);

EXAMPLE JAVA PROGRAM

  1. Create a Java Project in Eclipse
  2. Add C:\ranjiths\jbr\InstalledSofties\H2\bin\h2-1.4.192.jar into the project classpath.

package jbr.h2db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class H2DBExample {
 public static void main(String[] args) {
   Connection connection = null;
   ResultSet resultSet = null;
   Statement statement = null;
   String URL = "jdbc:h2:c:/ranjiths/jbr/InstalledSofties/h2db/db";
   String username = "ranjith";
   String password = "sekar";
   try {
     Class.forName("org.h2.Driver");
     connection = DriverManager.getConnection(URL, username, password);
     statement = connection.createStatement();
     resultSet = statement.executeQuery("SELECT * FROM users");
     while (resultSet.next()) {
       System.out.println("User Name: " + resultSet.getString("FIRST_NAME")
+ " " + resultSet.getString("LAST_NAME"));
     }
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     try {
       resultSet.close();
       statement.close();
       connection.close();
     } catch (Exception e) {
       e.printStackTrace();
     }
   }
 }
Posted by at No comments :
Labels:

HSQLDB With Java Example

INTRODUCTION

  • HSQLDB is called Hyper SQL Database and it is completely written in java.
  • Supports for embedding with java application as well as server mode.
  • Tables can be created in an in-memory or local disk.
  • It is 100% free and used in many of open source projects.

SOFTWARES & TOOLS

  1. HSQLDB
  2. Eclipse IDE (Mars 2)
  3. HSQLDB in-built DB Editor (Swing Based Application)

INSTALLATION

  1. Download latest HQLDB from https://sourceforge.net/projects/hsqldb/
  2. Unzip and copy to your local drive: C:\ranjiths\CommonSofties\hsqldb-2.3.4

CREATE DATABASE & TABLES

Go to C:\ranjiths\CommonSofties\hsqldb-2.3.4\bin and Click runManagerSwing.bat
Swing based Application will be opened and enter below details.

  1. Setting Name: JBR
  2. Type : HSQL Database Engine In-Memory
  3. Driver: org.hsqldb.jdbcDriver
  4. URL: jdbc:hsqldb:file:C:\ranjiths\jbr\hsqldb\db
  5. User: ranjith
  6. Password: sekar

Click OK

Create a table and insert some data.
CREATE TABLE users (
 user_id INTEGER NOT NULL CONSTRAINT EMP_NO_PK PRIMARY KEY,
 first_name VARCHAR(30) NOT NULL,
 last_name VARCHAR(30) NOT NULL,
 email VARCHAR(40),
 phone INTEGER
 );

INSERT INTO users VALUES(1,'Ranjith','Sekar','ranjith@gmail.com',96000);
INSERT INTO users VALUES(2,'Manoj','Kumar','manoj@gmail.com',88000);
INSERT INTO users VALUES(3,'Sachin','Tendulkar','sachin@gmail.com',77000);

EXAMPLE JAVA PROGRAM

  1. Create a Java Project in Eclipse
  2. Add all jars from C:\ranjiths\CommonSofties\hsqldb-2.3.4\lib into the project’s classpath.

package jbr.hsqldb;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class HSQLDBExample {
 public static void main(String[] args) {
   Connection connection = null;
   ResultSet resultSet = null;
   Statement statement = null;
   try {
     Class.forName("org.hsqldb.jdbcDriver");
     connection = DriverManager.getConnection("jdbc:hsqldb:file:
                  c:/ranjiths/jbr/hsqldb/db", "ranjith", "sekar");
     statement = connection.createStatement();
     resultSet = statement.executeQuery("SELECT * FROM users");
     while (resultSet.next()) {
       System.out.println("User Name: " + resultSet.getString("FIRST_NAME") \
            + " " + resultSet.getString("LAST_NAME"));
     }
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     try {
       resultSet.close();
       statement.close();
       connection.close();
     } catch (Exception e) {
       e.printStackTrace();
     }
   }
 }
}
Posted by at No comments :
Labels:

Apache Derby With Java Example

INTRODUCTION

  • Apache Derby is an open source database and it is completely written in java.
  • Supports for embedding with your java application using it’s embedded JDBC driver as well as client-server mode.
  • Easy installation and use.
  • Oracle’s Java DB is a distribution of Apache Derby.

SOFTWARES & TOOLS

  1. Apache Derby DB
  2. Eclipse IDE (Mars 2)

INSTALLATION & SETUP

  1. Download latest Derby (**-bin.zip) from Apache website : https://db.apache.org/derby/derby_downloads.html
  2. Unzip and copy to your local drive: C:\ranjiths\CommonSofties\db-derby-10.12.1.1
  3. Set Environment Variables (for Windows)
    1. DERBY_HOME= C:\ranjiths\CommonSofties\db-derby-10.12.1.1
    2. PATH = C:\ranjiths\CommonSofties\db-derby-10.12.1.1\bin
    3. CLASSPATH = C:\ranjiths\CommonSofties\db-derby-10.12.1.1\lib
  4. Run & Check IJ Command Line Interface (CLI)
    1. Open cmd prompt and type : ij and you will able to access ij>

CREATE DATABASE & TABLES

  1. Open the cmd and type ij to enter into the CLI.
  2. Create Database
ji> connect 'jdbc:derby:/jbr/library;create=true';

Upon successful execution of the above command, under C drive new folder will be created in the name of: jbr/library

  1. Create Tables
CREATE TABLE users (
 user_id INTEGER NOT NULL CONSTRAINT EMP_NO_PK PRIMARY KEY,
 first_name VARCHAR(30) NOT NULL,
 last_name VARCHAR(30) NOT NULL,
 email VARCHAR(40),
 phone INTEGER
);

CREATE TABLE books(
 book_id VARCHAR(30),
 book_name VARCHAR(30),
 authors VARCHAR(30),
 publisher VARCHAR(30),
 price INTEGER
);

  1. Insert data and execute a SELECT command to check the data.
INSERT INTO users VALUES(1,'Ranjith','Sekar','ranjith@gmail.com',96000);
INSERT INTO users VALUES(2,'Manoj','Kumar','manoj@gmail.com',88000);
INSERT INTO users VALUES(3,'Sachin','Tendulkar','@gmail.com',77000);

INSERT INTO books VALUES('b100','Computer Science','John','TATA',400);
INSERT INTO books VALUES('b200','Mathematics','David','Oreilly',300);
INSERT INTO books VALUES('b300','Java Complete Reference','Jonathan','PSB',600);

EXAMPLE JAVA PROGRAM

  1. Create a Java Project in Eclipse
  2. Add all jars from C:\ranjiths\CommonSofties\db-derby-10.12.1.1\lib into the project’s classpath.

package jbr.derbyex;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DerbyExample {
public static void main(String[] args) throws SQLException {
  String driver = "org.apache.derby.jdbc.EmbeddedDriver";
  String dbName = "/jbr/library";
  String connectionURL = "jdbc:derby:" + dbName;
  Connection connection = null;
  try {
         Class.forName(driver);
         connection = DriverManager.getConnection(connectionURL);
         Statement statement = connection.createStatement();
         // INSERT
         int result = statement.executeUpdate("INSERT INTO users VALUES(4,'Bose','Subash','bose@gmail.com',96000)");
         System.out.println("Updated " + result + " rows");
         // SELECT
         ResultSet resultSet = statement.executeQuery("SELECT * FROM USERS");
         while (resultSet.next()) {
                String first = resultSet.getString("FIRST_NAME");
                String last = resultSet.getString("LAST_NAME");
                System.out.println("Name: " + first + " " + last);
         }
         resultSet.close();
  } catch (Throwable e) {
         e.printStackTrace();
  } finally {
         connection.close();
  }
}
}
Posted by at No comments :
Labels:
Subscribe to: Posts ( Atom )