ORA-01882: timezone region not found error with Oracle Sql Developer

In order to solve this issue add your time zone to the sqldeveloper.conf file as below. You can find this file inside the <sqldeveloper-home>\sqldeveloper\bin folder.

AddVMOption -Duser.timezone=<Your Time Zone>

Example:-

    AddVMOption -Duser.timezone="+02:00"

or 

    AddVMOption -Duser.timezone=GMT

How to use SQLite with java

This tutorial is a quick guide to use SQLite in your java application. In this tutorial you will learn to create a database, to create a table,  insert data in to the table and to retrieve data from the table.

Before using SQLite with your java project, as with any database first you have to add the SQLite JDBC driver to the project. 

You can download SQLite JDBC driver from the below link
https://bitbucket.org/xerial/sqlite-jdbc/downloads

Create a new Java project and add this jar to your project as a normal library. Now you are ready to use SQLite with your application.

Look at following code samples and you will easily understand how to use SQLite with Java.

Create a new folder in your project and name it as 'dataFolder'. 
Below code creates a database names 'myDatabase.db' in that folder.

  public void createDatabase {
    try {
      Class.forName("org.sqlite.JDBC");
      //Following line creates a database named 'myDatabase.db' in 'dataFolder' folder.
      Connection con =
              DriverManager.getConnection("jdbc:sqlite:dataFolder/myDatabase.db");
      Statement stmt = con.createStatement();
     
     //Following line creates a table named 'employee' indatabase.
      String query = "CREATE TABLE employee(id INTEGER, name TEXT)";
      stmt.executeUpdate(query);
      stmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

Below method inserts two rows in to the employee table that we created in above method.

 private void insert() throws SQLException {
    Connection con =
             DriverManager.getConnection("jdbc:sqlite:dataFolder/myDatabase.db");
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("insert into employee (id, name) values(1, 'john')");
    stmt.executeUpdate("insert into employee (id, name) values(2, 'william')");
    stmt.close();
    con.close();
 }

Below method retrieves all the records from employee table and prints the id and the name.

  private static void read() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:sqlite:dataFolder/myDatabase.db");
    Statement s = conn.createStatement();
    ResultSet rs = s.executeQuery("SELECT id, name FROM employee");
    while (rs.next()) {
      Integer id = rs.getInt("id");
      String name = rs.getString("name");
      System.out.println(id + ":" + name);
    }
  }

Starting with SQLite...

SQLite is a small library which implements a lightweight DB engine. There is no DB server or configuration files used in SQLite. All tables views etc are written in to a single file which can be used in different platforms.  Above features together with less size, less memory consumption, portability and reliability have make SQLite a widely used component in various applications and devices.

SQLite's command line utility allows you to easily execute SQLite queries.
You can download SQLite3.exe from this page http://www.sqlite.org/download.html.
To use the command line utility open command window and point to the folder where SQLite3.exe exists. Now in this window you can run SQLite commands and queries.

Some of the basic queries and commands for a beginner are listed below.

☛ This command create a new Database with the name 'myDatabase'. SQLite will create a file with the same name. Note that here we haven't put the semicolon at the end. If you put a semicolon, it will append to the file name.

> sqlite3 myDatabase

☛ Following line creates a new table with the name 'employee'. It will contains two columns 'id' and 'name'.

> create table employee(id INTEGER, name TEXT);

☛ The above query can be changed as below so that 'id' column becomes a primary key.

> create table employee(id INTEGER PRIMARY KEY, name TEXT);

☛ Insert data

> insert into employee (id, name) values(10, 'john');

☛ Retrieve data

> select * from employee;

☛ Update a row

> update employee set id = 20, name = 'Steven' where id = 10;

☛ Drop table

> drop table employee;

☛ 'Create Trigger' syntax allows you to create triggers. Following trigger runs after inserting a record in to the employee table. It automatically adds 100 to the newly inserted row id. Note that the id of the newly inserted row has been referred as 'new.id'.

> create trigger trigger1 after insert on employee

> begin

> update employee set id = new.id + 100 where id = new.id;

> end;

Now insert a record to the employee table by using normal insert query and check the record by using a select query. You will see the id of the row has been changed.

☛ View all triggers
'sqlite_master' table contains the information about the database. To view all the triggers you can directly query from this table.

> SELECT name FROM sqlite_master WHERE type = 'trigger';

☛ Following command drops the trigger named 'myTrigger'.

> drop trigger myTrigger;

☛ In SQLite you use '||' to concatenate Strings. Following line query the name from employee table and appends the word 'Mr. '.

> select 'Mr. ' || name from employee where id = 10;

☛ '.read' reads queries from a file and executes them. Don't use semicolon at the end.

> .read D:/temp/my-queries.txt

☢ If it says that the file cannot be opened, check to make sure you haven't put a semicolon at the end of the command.
☢ If it gave a error message saying "incomplete SQL", make sure you have put a semicolon at the end of the query in the file.

☛ List all tables in current database.

> .table

☛ You can list all the tables which match a certain pattern. Following command lists the tables starts with 'emp'.

> .table emp%

☛ The ".dump" command shows information of the current database and data inserted.

> .dump

☛ To exit SQLite, you can use any of the following commands

> .quit
> .exit

Difference between Stored Procedures and Prepared StatementsAre they the same?

No. Stored Procedures and Prepared Statements are not the same. They are created for different purposes, created in different manner, and behave differently to each other.

Stored Procedures(SP)
A stored procedure can be defined as a bunch of SQL statements which are written to perform a certain task. After once compiled Stored Procedures are stored in the database.
When you are creating a Stored Procedure you have to give it a name and you can call it later using that name. A stored procedure can be a single query or a collection of different queries.

In some cases using Stored Procedures can have some advantages over multiple SQL queries.
♫ For an example, when using Stored Procedures you can keep your business logic in one place and let it be accessed by different programming languages such as Java, c# etc. So your Java or C# code also will become much simpler. However don't forget that, even though Stored Procedures make it easy to switch between programming languages, it can makes it harder to switch between databases in the case you wanted to switch to a different database.

♫ Assume an environment where the database server is accessed through a network. There you will surely find Stored Procedure are very useful. Instead of sending each and every SQL query through the network, calling a Stored Procedure stored in the database will be very faster and will causes to reduce the network traffic.

♫ You cannot forget the data security also. Once one of my friends was outsourced by a Telecommunications service provider company. He had to work with very sensitive data of the company. However once I met him, he said that his team was not allowed to access the data from tables. Instead he had to access previously created Stored Procedures. So data is secured. Since Stored Procedures can thus be used as an interface between data nad user, sensitive data and the table structure can be kept hidden from users

Prepared Statements
♫ Prepared Statements are some what different from Stored Procedures.
Once you created a Prepared Statement it is available within that session only. If you didn't de-allocate(drop) the Prepared Statement before the session is terminated, the database server de-allocates it.

♫ As soon as you created a Prepared Statement it is parsed and compiled and then compiled version is saved until the session lasts. Within that session cycle you can use it any number of times. The query is not parsed or compiled again because it is already compiled, thus the performance is higher.

♫ Prepared Statements are very useful when you need to use the same query multiple times, with the same or different parameters.

♫ When talking about Prepared Statements we can't forget the topic of SQL Injection. If you are using parameterised Prepared Statements, the user input is handled as the value of the parameter. So even when anybody passed a SQL portion as the user input, that SQL part also is not considered as a separate command and the injection fails.

How and When To Use Database Indexing

Indexing is crucial when it comes to Database Design. You should have a clear perception on plus and minus points of indexing.

What is Indexing 
Indexing is some strategy used to increase the performance of data retrieval operations of a database. Yes, indexing can increase only the data retrieval performance.

Lets think that we have a table named 'user' which contains thousands or millions of records. Assume that it contains a column named 'user_name'.

As you already know simple query like below will take lot of time.

SELECT * FROM user WHERE user_name = 'john';

Why this simple query takes such a long time. Answer is simple. This query search for all the records with user_name equal to 'john'.
Whether or not multiple records are available, a full table scan is required to check for all the matching records.
So each and every record is examined to check whether user_name is 'john'.

How indexing helps?
First of all note that we use indexing for columns (Ex:- user_name).
Second, for now(for learning purpose) assume index as a separate column. So think that when you create an index for user_name column, a duplicate column

of user_name column is generated with the name you give. The data in this column are sorted. You create indexes using a query like this.

CREATE INDEX user_name_index ON user (user_name);

In above query 'user_name_index' is the name of the index. 'user_name' is the name of the column.

You can create an index using multiple columns as below.

CREATE INDEX user_name_index ON user (first_name, last_name);

After you created the index for user_name column, if you execute the previous SELECT query again you will see that it doesn't take time as much as before.

How does this happen?
If you have indexed the column when you executing the query the database doesn't query from your 'user' table, instead it queries from the index.
Querying from the index is faster than querying from the entire table. Index is sorted(Not all. It depend of the data structure used. We will discuss it later), and it is easy to find all the records with a certain 'user_name' because all of the matching records are placed next to each other(because sorted).
Note that it is your task to create indexes, but deciding whether to use the index is a task of database itself. According to the query database decides whether to use the index or column name.

Is index a separate column in the table?
No, really it is a separate data structure which contains the column value(here 'user_name' column value) as the key and a pointer to the memory address of
the relevant table row. So after a certain index is queried the relevant data row can be quickly retrieved.

Disadvantages of Using Indexes
1. Index is a separate data structure and it contains the column data you selects. So it takes disk space to store them.

2. Every time you INSER, UPDATE or DELETE records Index also will be updated. This takes some time. So INSER, UPDATE and DELETE operations can be slower.

Data Structures
As you know now an index is a data structure. There are several data structures usually used for indexes such as B-tree, R-Tree, Hash, Bitmap etc.
However out of these, B-tree and Hash indexes are the widely used types.

  B-Tree
  ✔ This is the most commonly use data structure
  ✔ Stores data in an ordered manner. So retrieval of data is faster.
  ✔ can be used with LIKE and ORDER BY operations.

  Hash Index
  ✔ Hash table is used
  ✔ There is no way to determine whether one hash is greater than another. So if you want to
     retrieve data greater or less than a certain value, hashes can't help you.
     Similarly you can't select a range with hash indexes.
  ✔ equal and not-equal(= and <>) operations are very faster.
  ✔ can't use with LIKE and ORDER BY operations.

Database Access in Java (JDBC Example)

Accessing and retrieving data from a database is very simple.
First of all you need to add the DB driver(.jar) to your project. After that you can use below code.
In this example I am using mySql database.

//load DB driver

Class.forName("com.mysql.jdbc.Driver");

//this is the connection string

String connString 

   = "jdbc:mysql://localhost:3306/mydb?user=root&password=";  

//Create connection

Connection con = DriverManager.getConnection(connString);

//Create a Statement object using connection

Statement statement = con.createStatement();

//Execute the SQL statement and get the results as a Resultset(Note that the name of the table is 'employee')

ResultSet resultSet = statement.executeQuery("select * from 

           employee");

//Now we can iterate through the resultSet 

while(resultSet.next()){

//following method returns the value in Object type. 

//'name_full' is the column name

  Object o = resultSet.getObject("name_full");

  System.out.println(o);

//following method returns the value in String type.

 String s = resultSet.getString("name_full");

}
//Close the Statement object and Connection object.
statement.close();
con.close();

Note that you can remove above first two lines of the code and replace third line from below line to get the same result.

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "");