Starting Apache Derby Embedded in JAR Programmaticaly

In my last post Packaging Java application with Apache Derby as JAR Executable using Eclipse, the code that I used does not start the server at port 1527, by itself, when the JAR is launched. So I have added a few lines to accomplish this.

server = new NetworkServerControl(InetAddress.getByName("localhost"), 1527);
server.start(null);

(Entire code below)

One more thing that I missed out was, that on every users computer the Database is supposed to be created initially. Also the table, has to be created, if it does not exists. So the first time, a user launches JAR (the simple app we made) the database has to created as well as all the required table should be created and populated with content. (Do, read important points below)

So here is the entire code, which

  1. Starts the server
  2. Creates database
  3. Creates table
  4. Inserts values
  5. Queries the table
  6. Closes connection, and shuts down server
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JOptionPane;
import org.apache.derby.drda.NetworkServerControl;


public class Test {
	
	public static void main(String[] args) {

	    NetworkServerControl server = null;
            Connection conn = null;
	    PreparedStatement prestat = null;
	    Statement stmt = null;
	    ResultSet result = null;

	    try {
			server = new NetworkServerControl(InetAddress.getByName("localhost"), 1527);
			server.start(null);
		} catch (UnknownHostException e1) {
			e1.printStackTrace();
		} catch (Exception e1) {
			e1.printStackTrace();
		}
	    
	    try {
	        Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
	    } catch (InstantiationException e) {
	        e.printStackTrace();
	    } catch (IllegalAccessException e) {
	        e.printStackTrace();
	    } catch (ClassNotFoundException e) {
	        e.printStackTrace();
	    }
	    try {
	        conn = DriverManager.getConnection("jdbc:derby://localhost:1527/MyDbTest;create=true");
	        
	        stmt = conn.createStatement();
	        try {
	        	stmt.execute("CREATE TABLE newTestTable(id int primary key, name varchar(20))");
	        } catch (Exception e) {
	        	//e.printStackTrace();
	        }
	        try {
	        	stmt.execute("INSERT INTO newTestTable VALUES(10, 'Hey,'), (20, 'Look I changed'), (30, 'The code!')");
	        } catch (Exception e) {
	        	
	        }
	        	        
	        prestat = conn.prepareStatement("SELECT * FROM newTestTable");
	        result = prestat.executeQuery();
	        
	        StringBuilder builder = new StringBuilder();
	        while (result.next())
	        {
	        	builder.append(result.getInt(1) + ", " + result.getString(2));
	        	builder.append('\n');
	        }
	        
	        JOptionPane.showMessageDialog(null, builder.toString());
	        
	        result.close();
	        result = null;
	        prestat.close();
	        prestat = null;
	        conn.close();
	        conn = null;
	        
	        server.shutdown();
	        
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } catch (Exception e) {
			e.printStackTrace();
		}
	    finally{
	        if (result != null){
	            try { result.close();} catch (SQLException e){;}
	            result = null;
	        }
	        if (prestat != null){
	            try { prestat.close();} catch (SQLException e){;}
	            prestat = null;
	        }
	        if (conn != null){
	            try {conn.close();} catch(SQLException e) {;}
	        conn = null;
	        }
	    }
	
	}
	
}

IMPORTANT POINTS to note about above program

  1. The code is made small deliberately, hence not very neat. You should break down your logic into methods. Like for inserting values, there should be a separate method.
  2. SQL queries should be handled for exceptions. For eg, if you do not handle exception for query to “CREATE TABLE”, your program would work for first launch. But subsequent launches will terminate the application. Because the table is already present. Also “INSERT INTO” needs to be handled for exception, where duplicate rows might be getting inserted.
  3. Lastly your program, is not going to directly insert data, or search for it. You would be processing user data, and then doing query. I presented a ultra simple case.

And if you make your own great JAR app, don’t forget to share with all of us, here in comments 🙂

Advertisements
This entry was posted in Programming and tagged , , , , . Bookmark the permalink.

19 Responses to Starting Apache Derby Embedded in JAR Programmaticaly

  1. Yatin says:

    In This Example you create a table every time u run the application.
    what if i want too add MyDB.jar into the runnable jar file or in side with the runnable jar file so that with those two files my entire application may be transported.

    • vinayakgarg says:

      Note that, the code I have posted is only for teaching the concept. One of course wouldn’t want to create table every time application is run. So for that, you need to break up the program into several classes and functions. Check if the table exists, if not, call the method to create and populate the table. Same goes for code for initializing other items.
      Why do you need to have 2 JARS? In most cases your program logic must be entirely inside your project, which you can export as runnable JAR. Exception is when, the other JAR is a external dependence. And Apache derby is the example for that.

  2. Yatin says:

    I tried that , and the table doesn’t exists , that means i have to populate the database table every time the application runs? Or is there a way so that i can permanently record the changes being made in the java application ,When i run the application in Eclipse the folder myDB records the permanent changes every time it is run , but if i export the project into the runnable jar myDB is not exported along side with it.and everytime the new databse is created when i run my java app. SOS.
    I want my application to permanently record changes and update itself everytime the user uses it,So that it truly acts as an stand alone application with embedded database? .Is it possible?

    • vinayakgarg says:

      One feature of database is persistence of data. So the purpose of using derby, is as a embedded database, for your application. Carefully note the steps, for initialisation part-
      1. Try to connect to database. If an exception is thrown, create the database. [First time the application is run, the exception should be raised, consequently, you should be able to connect]
      2. Try to select content from table, if an exception is thrown, create table and populate it. [Same explanation]
      3. Perform your own program steps.

      This should work. In fact, it is not possible to create the database again with same name, so please recheck your steps. And what you want is definitely possible. Cheers!

  3. Yatin says:

    And thanks for the prompt reply !! really appreciate it .thnks a ton

  4. J82 says:

    server = new NetworkServerControl(InetAddress.getByName(“localhost”), 1527);
    server.start(null);
    Can you please elaborate what these lines really do? Because I think you’re using an embedded database, and I think there is no need to start the server.

    I’m having problems with deploying a Server-Client Java+Derby database and I badly need help. Add me on facebook, I’ll send you my e-mail

    • vinayakgarg says:

      Right, these statements are for starting the server.
      Whether it is a embedded or any other database, we always need to start the server for communication.
      To see it, comment these lines, and run the JAR (as a standalone) in terminal/prompt. Inspect the error message.
      For help, it would be better to post your exact problem with relevant code on stackoverflow (or any other forum/QA site). I can provide limited help here.

  5. J82 says:

    @vinaya
    I’ve already posted many times on DIC (dreamincode.net) but the answers didn’t really help. You know guys like us (that uses Java+Derby) are very rare so there are only few people to ask from. Actually all those who helped me really have no experiences with Derby.

    About your server start thingy, I’m pretty sure an embedded Derby database doesn’t really need to start the server. I just tested mine.

    I really need help with a client-server model. Would you mind test your codes and change it to a client-server one? Because I’ve literally tried all methods that were suggested but I just can’t connect from one PC to another. Even in a single machine, using its own IP address. I would really appreciate your help. Thanks~

    • vinayakgarg says:

      For client server example, please download these 2 files https://www.box.com/s/1d02fc931bf11ffb0a80, and https://www.box.com/s/25849352303a9244bab6. First one is server, second is client. After compiling both of them, in one terminal run the server, then in other terminal, run the client. When you type anything in client terminal, the typed message should appear on server terminal.

      The code posted here is tested, and runs fine. Try asking your question on stackoverflow. I am sure 1 million+ user community is big enough to solve your problem. You must have installed derby differently. Please take a look at my related posts, to see how I installed derby. And you should run your JAR, on a different computer, without apache derby.

  6. J82 says:

    Hey Vinayak, I guess those links are not for a client-server database. And I’m using Netbeans IDE which fully supports derby. You don’t even need to use ij tools, like what you did in your video. Can you please create a client-server version of your embedded database?

    • vinayakgarg says:

      Indeed they were only for client-server communication. Client server concept is very broad, you need to answer these questions for yourself –
      First you need to identify what your server is, and what your client is. Are you making a server which will respond over HTTP? or through sockets? etc. Are the server and client on same machine? Is your server on a remote machine (hosted)? Is it a web application or a desktop application. What type of information will client request from server? etc.
      My guess is, you are trying to distribute a desktop application, from which the client (user) can access information from your remote database over network. Please note that I have limited experience in this domain with Java (probably servlets). As an alternative, for web application, you can try PHP and MySQL, it is very popular and easy to use.

      • J82 says:

        It is a desktop application (Java) where the database (Derby) will be located on 1 computer (server), and an application will be distributed among the PCs in the LAN (the clients). So the problem is, I have no idea how the other PCs would access the database from the server PC and I’m really stuck. I hope you can help because you seem to be very familiar with derby

      • vinayakgarg says:

        Ok much clearer now. To be true, I wouldn’t use derby in this scenario. I would go for MySQL, Oracle etc, since you don’t need embedded database. Derby is best for embedding. And your database is on a central computer(server), not distributed to clients. MySQL would do a decent job, install it on your server.

        Now I don’t know any straightforward method (but it has to be there, because what you are doing is not uncommon). What you can do is, implement communication between client and server using sockets. In this communication, client may request for data or modification of data. On your server translate this request into query, and then respond with data or a message.

        You must research on JDBC. And also do take a look at http://docs.oracle.com/javase/tutorial/networking/sockets/clientServer.html
        Hope this helps.

  7. J82 says:

    Hey, Thanks a lot dude for all the help. I hope you continue your tutorials, they are very much big help for other people.

  8. Travis says:

    Hi, just found your blog whilst looking for a solution to my problem. I am new to programming java so forgive me if this is a stupid question.
    My problem is I cannot import org.apache.derby.drda.NetworkServerControl
    I can see up to “org.apache.derby” but “drda” does not exist for some reason.
    I can get my program to work if I start the server manually but that is not really good enough, I need to start it programmaticaly.

    • vinayakgarg says:

      This blog post is a continuation of 2 previous blog posts. Please check out “Packaging Java application with Apache Derby as JAR Executable using Eclipse” and “Installing and using Apache Derby with Eclipse”. You should be able to sort the problem then. Insure that you have installed Derby with Eclipse properly. Also note I have added a video for these 2 posts.

  9. Francis says:

    hi,
    i have read ur tutorial and many other countless tutorial but I seemed not to understand how to implement derby server-client database using java code.

    Could please explain using sample code how a server java app will connnect to a database and how another java app in a different computer(client) but same network will be able to connect to the same database on the server machine.

    Which connections drivers(i.e EmbeddedDriver or ClientDriver ) are used in the server side java app and client side app

    Please show how i can do this in basic java code ( I use netbeans so please avoid using the cmd.exe window).

    I look forward to your quick response

    • vinayakgarg says:

      Hi Francis,
      I did Java networking for small time, long back. Now I am not really the person to ask for detailed explanations on the subjects.
      But I can help you get started, by this->
      1. First read the 2 posts before this post. Then reread this post. See the video for greater understanding, and use the code in this blog.
      2. I am using Eclipse IDE, so if you can use this instead of netbeans, you can follow the video better. And whatever I do in video, you should do it on your end as well.
      3. Now if, you have some specific query, please go over to stackoverflow.com to ask. It is a great site, and your Java question will definitely get answered.
      4. Networking is not a easy topic. It is best to get some book on Java networking, since no blog can teach you such a in-depth topic.
      Good Luck 🙂

  10. Francis says:

    Thanks for your response but i will keep on trying to find a way
    thanks again

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s