Friday, November 25, 2011

JavaDB (Embedded) and NetBeans

updated...
Good day ALL..!
           I am sharing this for all those who find it a bit difficult starting with the Java DB (embedded) or the derby.There are a lot of tutorials and stuff available but only a very few are productive.In the beginning I was also mislead by several sources in the internet and finally when I found out the real solution it was very easy...Hope this will be helpful for many
              I myself use netbeans and finds it very simple and user friendly especially for beginners.So I personally recommend using netbeans.Also that derby ships with netbeans and nothing has to be installed further.You just have to start creating your db.
         
  • Open netbeans and select the services tab (left pane).You can also find it in the windows menu.
  • Once you expand DATABASES you will find JavaDB->sample which is the sample db created by netbeans.You can also find a db connection url for it ,something like                   jdbc:derby://localhost:1527/sample [app on APP]
  • Now right click on JavaDB and choose properties there you can see the location of your javadb and the location to which the database you create is stored.If its not set specify the folders by browsing.Normally javadb location is C:\Program Files \glassfish-3.1.1\javadb that is inside the glassfish installation which comes along with netbeans.
  • Now expand the Drivers folder and there you will see JavaDB(Embedded).Right click on it and open customise,where you can see the Driver files and driver classes filled.If  not set then click on add button and add all jar files in the db->lib folder of javaDB
  • Select org.apache.derby.jdbc.EmbeddedDriver as the driver class name it Java DB (Embedded) and press ok.
  • Once all this is set we have to create a db .
  1. Right click on JavaDB(Embedded) under the Drivers folder and select connect using .Now the connection wizard opens ..
  2. Select JavaDB(Embedded) as driver name.
  3. In the database field give the name of the database which should look like YourDatabaseName;create=true
  4. Type a username and password  and press Next.(dont forget the password cz everytime you connect the database you will be asked for password)
  5. Select schema as App (which is usually the default schema)and finish.
  • Now you have your db url along with sample db.Right click on it and connect.
  • Expand it and right click on APP->tables .Select create table and fill in details.                                For eg: table name : fruits.Now click on add column then give  name : id , type : numeric, size:5 check primary key and ok.You have 1 column now.Add one more column name: fruitname, type: varchar,size:20, and press ok.
  • Add values to the table by right clicking and selecting execute command. For sample just enter a statement like insert into fruits values(1,'apple') and choose run query.Right click and view data allows you to see table data .
  • Now locate the database you just created on your drive.It usually is stored in                                           C: / ../ .netbeans/7.0/derby / YourDatabaseName   Once you find the location copy that path 
  • Create a new project under Java application and use your db as below ..


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Fruits
{
    public static void main(String args [])
    {
        try {
                Connection con=      DriverManager.getConnection("jdbc:derby:C:/Users/Aparna/.netbeans/7.0/derby/ YourDatabaseName "); /* Note use' / 'and not' \'  The url above will be different in your system*/
               PreparedStatement stmt=con.prepareStatement("select * from fruits ");
                ResultSet rs=stmt.executeQuery();
                if(rs.next())
                {
                   System.out.println("Id : "+rs.getInt(1) +" "+" Fruitname :"+rs.getString(2));
                }
                else
                {
                   System.out.println("No word matching in database");
                }
            } catch (SQLException err) {
               System.out.println(err.getMessage()+ "No matching word in database");
            }
        }
    }
  • After writing your program expand your project in the left side pane.There you will find a Libraries folder.Right click on that and select Add jar/Folder.Browse to the folder containing the derby.jar file which will be inside the lib folder in glassfish installation (derbyclient.jar is used for client or network java db)
  •     Don't forget to right click and disconnect the db connection you made in netbeans once you are starting to run the program.This is bcause a dblock appears when you select connect  in netbeans.                                                                                                                 (You can visually see the dblock when it is connected in netbeans, ie open your database file in  C: / ../ .netbeans/7.0/derby / YourDatabaseName you will see a file there and once you choose disconnect that file disappears. )                                                                                                     To release the lock on db you will have to disconnect database from netbeans or it won't work.This is only for embedded db
Hope you all have a great time making your own db....thanks all who visited...please post in your feedbacks.

68 comments:

sandy said...

i appreciate this....useful 1

Basanta said...

Aparna it's a real good article, but in addition I would like to know that, this is running on the development machine, how to do that in client's machine. I mean package it in JAR, that's fine, but what should be done to locate and put the DB in client's machine. Your help would be highly appreciated.

aparna said...

Definitely will include that as my upcoming post Thanks for your opinion .Also take a look at this if its urgent http://java.sun.com/developer/technicalArticles/J2SE/Desktop/javadb/

Basanta Thapa said...

Thank you Aparna, I am eagerly waiting for your upcoming post and also if possible please elaborate the upcoming post with an installer program as well, that would be of so much helpful for the beginners.

kaan said...

thanks a lot..

Mack Claffey said...

Thumbs up!

aparna said...

You are welcome kaan.. Hope it was helpful

aparna said...

Thank you Mark ...

Linda said...

GREAT BLOG... i spent 2 days for resulting the Java DB embedded mode,but always give me that database doesn't exist... and then, i found the answer here, the key is the path of the database directory..

thank u so much, Mr.Aparna, u saved my life and my day :)

aparna said...

Thank you Linda.

Ali said...

I'm really appreciate your help. I spent one week to implement an application that using the Java Embedded DB.

Thanks a lot :)

Anonymous said...

First thank you for the useful tutorial. Second it does not work until i imported derby.jar to the library.

Unknown said...

hey aparna can you upload the
"JavaDB (Embedded) and NetBeans" project plest i tried but it is giving me sl statement error

aparna said...

Apurva can you tell me where you are getting the error? so that i can check over

Am said...

Aparns thank for such helpful blog, but i would request you to please suggest me code if i am using Access database , and have used jdbc:odbc driver.

aparna said...

Am its good to hear from you . I haven't tried access database but once i do, will definitely include that in my upcoming posts

Anonymous said...

Aparna i am gettting an error "cannot establish a connection to jdbc:derby:testdb;create=true using org.apache.derby.jdbc.EmbeddedDriver(could not initialize classorg.apache.derby.jdbc.EmbeddedDriver) even though a gave database,driver name and also uploaded jar file in library correctly can u please help me i will be very thankfull

Unknown said...

Connection authentication failure occurred. Reason: Invalid authentication. this is a continious problem i am facing i am sure about the passwrd ad username
Netbeans ver 7.3
jdk 7.17

Olakunle said...

Solution to this error
"cannot establish a connection to jdbc:derby:testdb;create=true using org.apache.derby.jdbc.EmbeddedDriver(could not initialize classorg.apache.derby.jdbc.EmbeddedDriver)

Make sure that the name of your database ends with YourDatabaseName;create=true

e.g schoolDatabase;create=true
e.g studentDb;create=true

The ;create=true must be with the name of your database

Unknown said...

embeded database that was created is'nt present on .netbean/derby folder

Unknown said...
This comment has been removed by the author.
Anonymous said...

Even now this blog is very helpful, thanks a lot

Anonymous said...

Even now this blog is very helpful, thanks a lot

joaquin said...

I really love you. I can't say more than that.

Shadab said...

Thank you very very much :)

Dr. Rajendra said...
This comment has been removed by the author.
Dr. Rajendra said...
This comment has been removed by the author.
Dr. Rajendra said...

Hey Aparna,
Thank you for the precious blog series!
I followed the process explained by you, but stuck at the system path of DB.
my DB : jdbc:derby:nighantu;create=true [raju on APP]
The DB path where the javaDB's are stored :
C:\Documents and Settings\Administrator\.netbeans-derby
But still the database physically doesn't exist in the folder where those DBs are stored who were created by right click on JavaDB and Create DataBase.
Please suggest :-)

Unknown said...

is it possible to run .exe file on other machine with database????

Unknown said...

I agree with Dr. Rajendra I'm facing the same problem. Database physically doesn't exist in the .netbeans-derby folder

aaronnewton said...

1) I was able to locate the database. Consider performing a Windows search, grep or mounting the databse in NetBeans and using ProcMon to monitor the file activity.

2) I've managed to load the database into the ./dist subfolder and mount it in the manner described in this blog post, so I assume this should work on any machine. You can use the following to find the path of the currently executing .jar

File("").getAbsolutePath()

hasan said...

Aparna, you are the king... For 8 hours, I am struggling with this issue and I found your page and solve it in minutes.. Thank you man...

Ptn Pnh said...

If you're experiencing problems with Java DB Embedded driver when using connections created in NetBeans Services window then it's due to a fact that NetBeans uses its own path for Embedded databases (namely [netbeans_default_userdir]\derby). It seems that it ignores the path configured in the Java DB server configuration dialog ([Java DB main path], that is ordinarily equal to [userdir]\.netbeans-derby) when making connections with the Embedded driver (but uses this path for the Network driver connections).
For better compatibility, you need to manually set Netbeans' system property 'derby.system.home' to point to this path. The best way to do this is to edit the file netbeans.conf which is located inside etc directory of NetBeans instalation. Add
-J-Dderby.system.home=\"[Java DB main path]\"
as the last switch to the netbeans_default_options key (this results in 2 quotes at the end of corresponding line in netbeans.conf).

DhruvhC said...

hi,
This tutorial is really very helpful as after days of searching I got very close to my need. But, it generated an error which is

Failed to start database 'C:/Users/Dhruvh/Documents/NetBeansProjects/DerbyFinal/sample' with class loader sun.misc.Launcher$AppClassLoader@45ee12a7, see the next exception for details.No matching word in database


The database was not connected though. I manually disconnected the database for running the program. Could anybody please help me out?

Unknown said...

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConn {
private Connection DBConn;
public Connection connect()
{

/*//localhost:3306/admin*/


try
{
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
System.out.println("MySQL JDBC found !!");
}
catch (ClassNotFoundException e) {
System.out.println("MySQL JDBC Driver not found !!");

}

String url="jdbc:derby:C:/Users/rohtash/.netbeans-derby/Testdb ";
try {
DBConn = DriverManager.getConnection("jdbc:derby:C:/Users/rohtash/.netbeans-derby/Test");
System.out.println("SQL Connection to database established!");

}
catch (SQLException e)
{
System.out.println("Connection Failed! Check output console");

}
return DBConn;


}

}

but i am getting error like this
not able to make a connection
i did exactly the same as you explained and it is really very helppful

Anonymous said...

hello
i can't find my database location in my computer.How can i find it

Unknown said...

Hi,

I'm getting exception as
'java.sql.SQLSyntaxErrorException: Table/View 'OFFICE' does not exist.'

can you please help me with this?

Thanks

Unknown said...

thank you soooooooooo muchhhhhhh..
i am so happy.. :-)

Unknown said...

Thank you very much, It helped me too, I faced a difficulty in in locating the db url that's why my database table was not found, but by using that format you presented, Now I have solved it, May God Bless youuuuuuu!

Admin said...

thank you!!!!!!!

Anonymous said...

Hi Aparna,

Do you provide consulting services for Java Projects?

Unknown said...

wov is wov but
For information related to human life, please visit this blog.
<a href="https://internationalyouthacuity.blogspot.com/

besant technology said...

Your very own commitment to getting the message throughout came to be rather powerful and have consistently enabled employees just like me to arrive at their desired goals.
Hadoop Training in Chennai
Hadoop Training in Bangalore

pooja said...

I know you feel more happy when you get things done and best of all those things are your most precious treasure.
Best Devops Training in pune
Microsoft azure training in Bangalore
Power bi training in Chennai

Unknown said...

I have searched and searched for some help with embedded db. I am working on a personal project that has now taken over two years. To far to give up now. Every problem I have faced I have overcome. And you my friend have helped me overcome a huge problem thank you so much. God Bless.

Unknown said...

Really great post, Thank you for sharing This knowledge.Excellently written article, if only all bloggers offered the same level of content as you, the internet would be a much better place. Please keep it up!
python training in chennai
Python Online training in usa
python course institute in chennai

jefrin said...

Amazing post thanks for sharing
salesforce training in chennai

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
service care said...

Thank you for allowing me to read it, welcome to the next in a recent article. And thanks for sharing the nice article, keep posting or updating news article.
apple service center in chennai
apple mobile service centre in chennai
apple service center near me

jvimala said...

Your very own commitment to getting the message throughout came to be rather powerful and have consistently enabled employees just like me to arrive at their desired goals.
MATLAB TRAINING IN CHENNAI | Best MATLAB TRAINING Institute IN CHENNAI
EMBEDDED SYSTEMS TRAINING IN CHENNAI |Best EMBEDDED TRAINING Institute IN CHENNAI
MCSA / MCSE TRAINING IN CHENNAI |Best MCSE TRAINING Institute IN CHENNAI
CCNA TRAINING IN CHENNAI | Best CCNA TRAINING Institute IN CHENNAI
ANDROID TRAINING IN CHENNAI |Best ANDROID TRAINING Institute IN CHENNAI
Selenium Training in Chennai | Best Selenium Training in chennai
Devops Course Training in Chennai | Best Devops Training in Chennai

manisha said...

nice course. thanks for sharing this post this post harried me a lot.
MCSE Training in Noida

Lite Mentors said...

I have read your blog and I got a piece of very useful and knowledgeable information from your blog.its really a very nice article. You have done a great job. If anyone wants to get Best Digital Marketing training institutes in Chennai, Please visit Litementors located at Chennai Guindy which offer Best Training in Chennai for multiple programming and marketing courses.

Training for IT and Software Courses said...

This is the exact information I am been searching for, Thanks for sharing the required infos with the clear update and required points. To appreciate this I like to share some useful information.Hadoop Training in Bangalore

Training for IT and Software Courses said...

Wow it is really wonderful and awesome thus it is veWow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your site.ry much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your site.Informatica Training in Bangalore

Training for IT and Software Courses said...

Excellent information with unique content and it is very useful to know about the SAP HANA.SAP HANA training in bangalore

Hi Tech Multi Education said...

Nice information.. Hi dear, This is an nice and valuable post thanks for this information! Visit .. VIEW MORE:-

Computer Repairing institute in Delhi

Realtime Experts said...

This is amazing and really inspiring goal. digital marketing training in bangalore

Training for IT and Software Courses said...

Your articles really impressed for me,because of all information so nice.servicenow training in bangalore

Softgen Infotech said...

such a great word which you use in your article and article is amazing knowledge. thank you for sharing it.

Upgrade your career Learn Oracle Training from industry experts gets complete hands on Training, Interview preparation, and Job Assistance at My Training Bangalore.

Anand Shankar said...
This comment has been removed by the author.
Tecmax said...

Thanks for the information...
Best SAP HANA Training in Bangalore - BTM Layout | SAP HANA Training Institutes | SAP HANA Course Content - Tecmax
- Tecmax offers the Best SAP HANA Training in Bangalore - BTM Layout, We offer Real-Time Training with Live Projects, Our SAP HANA Trainers are Working Professionals with 8+ years of Expertise in SAP HANA, we also provide placement assistance.

Numen Technology said...

Thank for great info....
Numen technology is the IT Solutions and Services to the organization division. We train niche technologies like Digital Marketing, Block Chain,python, machine leaning, Data Science, Artificial Intelligence(AI), Cloud Computing, web development and many more with 100% placement support.
Best Java Training In Bangalore
Web development training in Bangalore

Digital Marketing course

keerthi said...

This post is really nice and informative. The explanation given is really comprehensive and informative. Web Designing Course Training in Chennai | Web Designing Course Training in annanagar | Web Designing Course Training in omr | Web Designing Course Training in porur | Web Designing Course Training in tambaram | Web Designing Course Training in velachery

Lopa said...

Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.

Python Certification Course
Digital Marketing Certification Course
AWS Certification Course
Selenium Online Course
Data Science Certification Course
DevOps Certification Course

Anonymous said...

Keep share such valuable Content. Very helpful and knowledgeable, After Seeing this article it was awesome and very valuable.

Tableau Online Training

Best Training Institute said...

I like your post. Everyone should do read this blog.
Perl training in bangalore

Kabir Kahan said...

Well-written and informative article. Thanks! for sharing this article.

if anyone looking for a classroom or online training on front-end development course, explore here --> Nexdemy