plugins/eclipse/org.apache.derby.plugin.doc/topics/derby_app.html - derby - Git at Google

 <!--
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to you under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 -->
 <html>
 <head>
 <title>Creating a Java application to access a Derby database</title>
 </head>
 <body>
 <h2>Creating a Java application to access a Derby database</h2>
 <p>
 Once you've become familiar with starting the network server and running
 SQL queries, you'll want to use Derby from within a Java application.
 This section will demonstrate using Derby from a stand-alone Java
 application.
 </p>
 <p>
 This example will access the data in our sample database, <b>myDB</b>,
 which contains the restaurants table.  By following the two <a href="ij_toc.html"><b>ij</b></a> sections in the help, this database and table will have already
 been created and ready to use.  If you have not created the database and the table return to the <a href="ij_toc.html"><b>ij</b></a> sections and create them now.
 </p>
 <h3>Steps to create a stand-alone Java application</h3>
 <ul>
 <li>
 From the <b>Java</b> perspective, select the project in the <b>Package Explorer</b> view. Right-click the project to bring up the context menu and select
 <b>New, Class</b>.
 </li>
 </ul>
 </p>
 <img src="images/new_class.GIF" alt="Creating a Java class in Eclipse" width="914" height="638"></img>
 <p>
 Give the Java class a package name, <b>myapp</b>, name the class
 <b>Restaurants</b>, make it a public class, and include a main method
 in the class since this will be a stand-alone application.  The image
 below shows an example of this.
 Click <b>Finish</b> to create the class.
 </p>
 <img src="images/create_class.GIF" alt="New Java class wizard" width="515" height="566"></img>

 <p>
 The Java class shown below, <b>Restaurants.java</b>, connects to the Derby Network Server, inserts a row into the restaurants table, and then displays a select
 from the restaurants table.  Copy the code below into the Java editor window
 for the <b>Restaurants.java</b> class you just created.
 </p>

 <pre>
 package myapp;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.sql.ResultSetMetaData;


 public class Restaurants
 {
     private static String dbURL = "jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine";
     private static String tableName = "restaurants";
     // jdbc Connection
     private static Connection conn = null;
     private static Statement stmt = null;

     public static void main(String[] args)
     {
         createConnection();
         insertRestaurants(5, "LaVals", "Berkeley");
         selectRestaurants();
         shutdown();
     }

     private static void createConnection()
     {
         try
         {
             Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
             //Get a connection
             conn = DriverManager.getConnection(dbURL);
         }
         catch (Exception except)
         {
             except.printStackTrace();
         }
     }

     private static void insertRestaurants(int id, String restName, String cityName)
     {
         try
         {
             stmt = conn.createStatement();
             stmt.execute("insert into " + tableName + " values (" +
                     id + ",'" + restName + "','" + cityName +"')");
             stmt.close();
         }
         catch (SQLException sqlExcept)
         {
             sqlExcept.printStackTrace();
         }
     }

     private static void selectRestaurants()
     {
         try
         {
             stmt = conn.createStatement();
             ResultSet results = stmt.executeQuery("select * from " + tableName);
             ResultSetMetaData rsmd = results.getMetaData();
             int numberCols = rsmd.getColumnCount();
             for (int i=1; i<=numberCols; i++)
             {
                 //print Column Names
                 System.out.print(rsmd.getColumnLabel(i)+"\t\t");
             }

             System.out.println("\n-------------------------------------------------");

             while(results.next())
             {
                 int id = results.getInt(1);
                 String restName = results.getString(2);
                 String cityName = results.getString(3);
                 System.out.println(id + "\t\t" + restName + "\t\t" + cityName);
             }
             results.close();
             stmt.close();
         }
         catch (SQLException sqlExcept)
         {
             sqlExcept.printStackTrace();
         }
     }

     private static void shutdown()
     {
         try
         {
             if (stmt != null)
             {
                 stmt.close();
             }
             if (conn != null)
             {
                 DriverManager.getConnection(dbURL + ";shutdown=true");
                 conn.close();
             }
         }
         catch (SQLException sqlExcept)
         {

         }

     }
 }
 </pre>
 <p>
 Once Restaurants.java is compiled without errors, run the Java application by
 right-clicking it from the Package Explorer view and selecting <b>Run As, Java Application</b>.
 </p>

 <img src="images/run_javaapp.GIF" alt="Running a java application" width="857" height="703"></img>

 <p>
 The output from running <b>Restaurants.java</b> is shown below.  It shows the
 successful insert of a row into our restaurants table and a select of all rows
 in the table.  If you encounter any errors when running the application make
 sure the Derby Network Server has been started on port 1527 and the myDB
 database exists in the current workspace and Java project.
 </p>
 <img src="images/completed_javaapp.GIF" alt="Output from a java application" width="857" height="703"></img>

 <h3>Changing the application to use the Derby Embedded Driver</h3>
 <p>
 <b>Restaurants.java</b> accessed the Derby database, <b>myDB</b> using
 the Derby Network Client Driver.  The values for loading the driver and the
 Database connection URL are shown below.
 </p>
 <ul>
 <li>
 <b>
 Driver name <br/>
 </b>
 org.apache.derby.jdbc.ClientDriver
 <br/><br/>
 </li>
 <li>
 <b>
 Database connection URL<br/>
 </b>
 jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine
 <br/><br/>
 </li>
 </ul>
 <p>
 To change the application to use the Derby Embedded Driver we need to change
 these values to:
 </p>
 <ul>
 <li>
 <b>
 Driver name <br/>
 </b>
 org.apache.derby.jdbc.EmbeddedDriver
 <br/><br/>
 </li>
 <li>
 <b>
 Database connection URL<br/>
 </b>
 jdbc:derby:myDB;create=true;user=me;password=mine
 <br/><br/>
 </li>
 </ul>

 <h3>About Schema Names</h3>
 <p>
 If a database is created in Derby using the embedded driver and no user
 name is specified, the default schema used becomes <b>APP</b>.  Therefore
 any tables created in the database have a schema name of <b>APP</b>.
 However, when creating a Derby database using the Network Server, the value
 for the schema becomes the value of the username used to connect with as
 part of the database URL.  In our example we first created the <b>myDB</b>
 database using the user <b>me</b>.
 </p>
 <p>
 When we change the application to connect using the embedded driver, the
 schema will default to <b>APP</b> unless we explicitly specify a schema,
 or pass the username as part of the Database connection URL. To access the
 table without passing the username as part of the embedded driver Database
 URL we would refer to the table as <b>ME.restaurants</b>.
 </p>
 <p>
 Only two lines of code need to be changed in Restaurants.java to use the
 Derby Embedded Driver to access the myDB database and insert and select into
 the me.restaurants table.  The old values are listed below.
 </p>
 <pre>
 // variables
 private static String dbURL = "jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine";

 // from the createConnection method
 Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
 </pre>
 <p>
 The new values are shown below to use the Embedded Driver.
 </p>
 <pre>
 // variables
 private static String dbURL = "jdbc:derby:myDB;create=true;user=me;password=mine";

 // from the createConnection method
 Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
 </pre>

 <p>
 Comment out the old values and replace them with the new ones shown above.
 Recompile the class and if your Derby Network Server is running stop it before
 running the Java application with the Embedded Driver.
 </p>
 <p>
 Applications which use the Derby Embedded Driver may only access the database
 from the same JVM.  Applications which use the Derby Network Server can
 access the database from other JVM's.
 </p>
 <p>
 </p>

 </body>
 </html>
	<!--
	Licensed to the Apache Software Foundation (ASF) under one or more
	contributor license agreements. See the NOTICE file distributed with
	this work for additional information regarding copyright ownership.
	The ASF licenses this file to you under the Apache License, Version 2.0
	(the "License"); you may not use this file except in compliance with
	the License. You may obtain a copy of the License at

	http://www.apache.org/licenses/LICENSE-2.0

	Unless required by applicable law or agreed to in writing, software
	distributed under the License is distributed on an "AS IS" BASIS,
	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	See the License for the specific language governing permissions and
	limitations under the License.
	-->
	<html>
	<head>
	<title>Creating a Java application to access a Derby database</title>
	</head>
	<body>
	<h2>Creating a Java application to access a Derby database</h2>
	<p>
	Once you've become familiar with starting the network server and running
	SQL queries, you'll want to use Derby from within a Java application.
	This section will demonstrate using Derby from a stand-alone Java
	application.
	</p>
	<p>
	This example will access the data in our sample database, <b>myDB</b>,
	which contains the restaurants table. By following the two <a href="ij_toc.html"><b>ij</b></a> sections in the help, this database and table will have already
	been created and ready to use. If you have not created the database and the table return to the <a href="ij_toc.html"><b>ij</b></a> sections and create them now.
	</p>
	<h3>Steps to create a stand-alone Java application</h3>
	<ul>
	<li>
	From the <b>Java</b> perspective, select the project in the <b>Package Explorer</b> view. Right-click the project to bring up the context menu and select
	<b>New, Class</b>.
	</li>
	</ul>
	</p>
	<img src="images/new_class.GIF" alt="Creating a Java class in Eclipse" width="914" height="638"></img>
	<p>
	Give the Java class a package name, <b>myapp</b>, name the class
	<b>Restaurants</b>, make it a public class, and include a main method
	in the class since this will be a stand-alone application. The image
	below shows an example of this.
	Click <b>Finish</b> to create the class.
	</p>
	<img src="images/create_class.GIF" alt="New Java class wizard" width="515" height="566"></img>

	<p>
	The Java class shown below, <b>Restaurants.java</b>, connects to the Derby Network Server, inserts a row into the restaurants table, and then displays a select
	from the restaurants table. Copy the code below into the Java editor window
	for the <b>Restaurants.java</b> class you just created.
	</p>

	<pre>
	package myapp;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.sql.ResultSetMetaData;


	public class Restaurants
	{
	private static String dbURL = "jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine";
	private static String tableName = "restaurants";
	// jdbc Connection
	private static Connection conn = null;
	private static Statement stmt = null;

	public static void main(String[] args)
	{
	createConnection();
	insertRestaurants(5, "LaVals", "Berkeley");
	selectRestaurants();
	shutdown();
	}

	private static void createConnection()
	{
	try
	{
	Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
	//Get a connection
	conn = DriverManager.getConnection(dbURL);
	}
	catch (Exception except)
	{
	except.printStackTrace();
	}
	}

	private static void insertRestaurants(int id, String restName, String cityName)
	{
	try
	{
	stmt = conn.createStatement();
	stmt.execute("insert into " + tableName + " values (" +
	id + ",'" + restName + "','" + cityName +"')");
	stmt.close();
	}
	catch (SQLException sqlExcept)
	{
	sqlExcept.printStackTrace();
	}
	}

	private static void selectRestaurants()
	{
	try
	{
	stmt = conn.createStatement();
	ResultSet results = stmt.executeQuery("select * from " + tableName);
	ResultSetMetaData rsmd = results.getMetaData();
	int numberCols = rsmd.getColumnCount();
	for (int i=1; i<=numberCols; i++)
	{
	//print Column Names
	System.out.print(rsmd.getColumnLabel(i)+"\t\t");
	}

	System.out.println("\n-------------------------------------------------");

	while(results.next())
	{
	int id = results.getInt(1);
	String restName = results.getString(2);
	String cityName = results.getString(3);
	System.out.println(id + "\t\t" + restName + "\t\t" + cityName);
	}
	results.close();
	stmt.close();
	}
	catch (SQLException sqlExcept)
	{
	sqlExcept.printStackTrace();
	}
	}

	private static void shutdown()
	{
	try
	{
	if (stmt != null)
	{
	stmt.close();
	}
	if (conn != null)
	{
	DriverManager.getConnection(dbURL + ";shutdown=true");
	conn.close();
	}
	}
	catch (SQLException sqlExcept)
	{

	}

	}
	}
	</pre>
	<p>
	Once Restaurants.java is compiled without errors, run the Java application by
	right-clicking it from the Package Explorer view and selecting <b>Run As, Java Application</b>.
	</p>

	<img src="images/run_javaapp.GIF" alt="Running a java application" width="857" height="703"></img>

	<p>
	The output from running <b>Restaurants.java</b> is shown below. It shows the
	successful insert of a row into our restaurants table and a select of all rows
	in the table. If you encounter any errors when running the application make
	sure the Derby Network Server has been started on port 1527 and the myDB
	database exists in the current workspace and Java project.
	</p>
	<img src="images/completed_javaapp.GIF" alt="Output from a java application" width="857" height="703"></img>

	<h3>Changing the application to use the Derby Embedded Driver</h3>
	<p>
	<b>Restaurants.java</b> accessed the Derby database, <b>myDB</b> using
	the Derby Network Client Driver. The values for loading the driver and the
	Database connection URL are shown below.
	</p>
	<ul>
	<li>
	<b>
	Driver name <br/>
	</b>
	org.apache.derby.jdbc.ClientDriver
	<br/><br/>
	</li>
	<li>
	<b>
	Database connection URL<br/>
	</b>
	jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine
	<br/><br/>
	</li>
	</ul>
	<p>
	To change the application to use the Derby Embedded Driver we need to change
	these values to:
	</p>
	<ul>
	<li>
	<b>
	Driver name <br/>
	</b>
	org.apache.derby.jdbc.EmbeddedDriver
	<br/><br/>
	</li>
	<li>
	<b>
	Database connection URL<br/>
	</b>
	jdbc:derby:myDB;create=true;user=me;password=mine
	<br/><br/>
	</li>
	</ul>

	<h3>About Schema Names</h3>
	<p>
	If a database is created in Derby using the embedded driver and no user
	name is specified, the default schema used becomes <b>APP</b>. Therefore
	any tables created in the database have a schema name of <b>APP</b>.
	However, when creating a Derby database using the Network Server, the value
	for the schema becomes the value of the username used to connect with as
	part of the database URL. In our example we first created the <b>myDB</b>
	database using the user <b>me</b>.
	</p>
	<p>
	When we change the application to connect using the embedded driver, the
	schema will default to <b>APP</b> unless we explicitly specify a schema,
	or pass the username as part of the Database connection URL. To access the
	table without passing the username as part of the embedded driver Database
	URL we would refer to the table as <b>ME.restaurants</b>.
	</p>
	<p>
	Only two lines of code need to be changed in Restaurants.java to use the
	Derby Embedded Driver to access the myDB database and insert and select into
	the me.restaurants table. The old values are listed below.
	</p>
	<pre>
	// variables
	private static String dbURL = "jdbc:derby://localhost:1527/myDB;create=true;user=me;password=mine";

	// from the createConnection method
	Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
	</pre>
	<p>
	The new values are shown below to use the Embedded Driver.
	</p>
	<pre>
	// variables
	private static String dbURL = "jdbc:derby:myDB;create=true;user=me;password=mine";

	// from the createConnection method
	Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
	</pre>

	<p>
	Comment out the old values and replace them with the new ones shown above.
	Recompile the class and if your Derby Network Server is running stop it before
	running the Java application with the Embedded Driver.
	</p>
	<p>
	Applications which use the Derby Embedded Driver may only access the database
	from the same JVM. Applications which use the Derby Network Server can
	access the database from other JVM's.
	</p>
	<p>
	</p>

	</body>
	</html>