java/demo/nserverdemo/SimpleNetworkClientSample.java - derby - Git at Google

 /*

    Derby - Class SimpleNetworkClientSample

    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.

  */

 import java.lang.reflect.InvocationTargetException;
 import java.lang.reflect.Method;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.Properties;

 import javax.sql.DataSource;

 /**
  * The primary purpose of this program is to demonstrate how to obtain
  * client connections using DriverManager or a DataSource
  * and interact with Derby Network Server.
  *
  * In particular,this sample program
  * 1)	obtains a client connection using the DriverManager
  * 2)	obtains a client connection using a DataSource
  * 3)	tests the database connections by executing a sample query
  * and then exits the program
  *
  * Before running this program, please make sure that Derby Network Server is up
  * and running.
  *  <P>
  *  Usage: java SimpleNetworkClientSample
  *
  */
 public class SimpleNetworkClientSample
 {

 	/*
 	 * The database is located in the same directory where this program is being
 	 * run. Alternately one can specify the absolute path of the database location
 	 */
 	private static String DBNAME="NSSimpleDB";

 	/**
 	 * Derby network server port ; default is 1527
 	 */
 	private static int NETWORKSERVER_PORT=1527;


 	/**
 	 * Derby Network Client Driver class names
 	 */

 	private static final String DERBY_CLIENT_DS = "org.apache.derby.jdbc.ClientDataSource";

 	/**
 	 * This URL is used to connect to Derby Network Server using the DriverManager.
 	 * Notice that the properties may be established via the URL syntax
 	 */
 	private static final String CS_NS_DBURL= "jdbc:derby:net://localhost:"+NETWORKSERVER_PORT+"/"+DBNAME+";retrieveMessagesFromServerOnGetMessage=true;deferPrepares=true;";

         // URL for the Derby client JDBC driver.
         private static final String DERBY_CLIENT_URL= "jdbc:derby://localhost:"+ NETWORKSERVER_PORT+"/"+DBNAME+";create=true";

         // Default to using the Derby Client JDBC Driver for database connections
         String url = DERBY_CLIENT_URL;
         String jdbcDataSource = DERBY_CLIENT_DS;

 	public static void main (String[] args) throws Exception
         {

                    new SimpleNetworkClientSample().startSample(args);

         }
 	public void startSample (String[] args) throws Exception
 	{
 		DataSource clientDataSource = null;
 		Connection clientConn1 = null;
 		Connection clientConn2 = null;


 		try
 		{
 			System.out.println("Starting Sample client program ");

 			// get a client connection using DriverManager
 			clientConn1 = getClientDriverManagerConnection();
 			System.out.println("Got a client connection via the DriverManager.");

 			// create a datasource with the necessary information
 			javax.sql.DataSource myDataSource = getClientDataSource(DBNAME, null, null);

 			// get a client connection using DataSource
 			clientConn2 = getClientDataSourceConn(myDataSource);
 			System.out.println("Got a client connection via a DataSource.");

 			// test connections by doing some work
 			System.out.println("Testing the connection obtained via DriverManager by executing a sample query ");
 			test(clientConn1);
 			System.out.println("Testing the connection obtained via a DataSource by executing a sample query ");
 			test(clientConn2);

 			System.out.println("Goodbye!");
 		}
 		catch (SQLException sqle)
 		{
 			System.out.println("Failure making connection: " + sqle);
 			sqle.printStackTrace();
 		}
 		finally
 		{

 			if(clientConn1 != null)
 				clientConn1.close();
 			if(clientConn2 != null)
 				clientConn2.close();
 		}
 	}

 	/**
 	 * Get a database connection from DataSource
 	 * @pre Derby Network Server is started
 	 * @param	ds	data source
 	 * @return	returns database connection
 	 * @throws Exception if there is any error
 	 */
 	public Connection getClientDataSourceConn(javax.sql.DataSource ds)
 		throws Exception
 	{
 		Connection conn = ds.getConnection("usr2", "pass2");
 		System.out.print("connection from datasource; getDriverName = ");
 		System.out.println(conn.getMetaData().getDriverName());
 		return conn;
 	}

 	/**
 	 * Creates a client data source and sets all the necessary properties in order to
 	 * connect to Derby Network Server
 	 * The server is assumed to be running on 1527 and on the localhost
 	 * @param	database	database name; can include Derby URL attributes
 	 * @param	user		database user
 	 * @param	password
 	 * @return	returns DataSource
 	 * @throws Exception if there is any error
 	 */
 	public javax.sql.DataSource getClientDataSource(String database, String user, String
 									  password) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException
 	{
 		Class<?> nsDataSource = Class.forName(jdbcDataSource);
 		DataSource ds = (DataSource) nsDataSource.getConstructor().newInstance();

 		// can also include Derby URL attributes along with the database name
 		Class[] methodParams = new Class[] {String.class};
 		Method dbname = nsDataSource.getMethod("setDatabaseName", methodParams);
 		Object[] args = new Object[] {database};
 		dbname.invoke(ds, args);

 		if (user != null) {
 			Method setuser = nsDataSource.getMethod("setUser", methodParams);
 			args = new Object[] {user};
 			setuser.invoke(ds, args);
 		}
 		if (password != null) {
 			Method setpw = nsDataSource.getMethod("setPassword", methodParams);
 			args = new Object[] {password};
 			setpw.invoke(ds, args);
 		}
 		// host on which network server is running
 		Method servername = nsDataSource.getMethod("setServerName", methodParams);
 		args = new Object[] {"localhost"};
 		servername.invoke(ds, args);

 		// port on which Network Server is listening
 		methodParams = new Class[] {int.class};
 		Method portnumber = nsDataSource.getMethod("setPortNumber", methodParams);
 		args = new Object[] {1527};
 		portnumber.invoke(ds, args);

 		return ds;

 	}

 	/**
 	 * Get a client connection using the DriverManager
 	 * @pre The JDBC driver must have been loaded before calling this method
 	 * @return Connection	client database connection
 	 */
 	public Connection getClientDriverManagerConnection()
 		throws Exception
 	{

 		// See Derby documentation for description of properties that may be set
 		//  in the context of the network server.
 		Properties properties = new java.util.Properties();

 		// The user and password properties are a must, required by JCC
 		properties.setProperty("user","derbyuser");
 		properties.setProperty("password","pass");

 		// Get database connection  via DriverManager api
 		Connection conn = DriverManager.getConnection(url,properties);

 		return conn;
 	}


 	/**
 	 * Test a connection by executing a sample query
 	 * @param	conn 	database connection
 	 * @throws Exception if there is any error
 	 */
 	public void test(Connection conn)
 		throws Exception
 	{

 	  Statement stmt = null;
 	  ResultSet rs = null;
 	  try
 	  {
 		// To test our connection, we will try to do a select from the system catalog tables
 		stmt = conn.createStatement();
 		rs = stmt.executeQuery("select count(*) from sys.systables");
 		while(rs.next())
 			System.out.println("number of rows in sys.systables = "+ rs.getInt(1));

 	  }
 	  catch(SQLException sqle)
 	  {
 		  System.out.println("SQLException when querying on the database connection; "+ sqle);
 		  throw sqle;
   	  }
   	  finally
   	  {
 		  if(rs != null)
 		  	rs.close();
 		  if(stmt != null)
 		  	stmt.close();
  	  }
 	}

 }
	/*

	Derby - Class SimpleNetworkClientSample

	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.

	*/

	import java.lang.reflect.InvocationTargetException;
	import java.lang.reflect.Method;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.Properties;

	import javax.sql.DataSource;

	/**
	* The primary purpose of this program is to demonstrate how to obtain
	* client connections using DriverManager or a DataSource
	* and interact with Derby Network Server.
	*
	* In particular,this sample program
	* 1) obtains a client connection using the DriverManager
	* 2) obtains a client connection using a DataSource
	* 3) tests the database connections by executing a sample query
	* and then exits the program
	*
	* Before running this program, please make sure that Derby Network Server is up
	* and running.
	* <P>
	* Usage: java SimpleNetworkClientSample
	*
	*/
	public class SimpleNetworkClientSample
	{

	/*
	* The database is located in the same directory where this program is being
	* run. Alternately one can specify the absolute path of the database location
	*/
	private static String DBNAME="NSSimpleDB";

	/**
	* Derby network server port ; default is 1527
	*/
	private static int NETWORKSERVER_PORT=1527;


	/**
	* Derby Network Client Driver class names
	*/

	private static final String DERBY_CLIENT_DS = "org.apache.derby.jdbc.ClientDataSource";

	/**
	* This URL is used to connect to Derby Network Server using the DriverManager.
	* Notice that the properties may be established via the URL syntax
	*/
	private static final String CS_NS_DBURL= "jdbc:derby:net://localhost:"+NETWORKSERVER_PORT+"/"+DBNAME+";retrieveMessagesFromServerOnGetMessage=true;deferPrepares=true;";

	// URL for the Derby client JDBC driver.
	private static final String DERBY_CLIENT_URL= "jdbc:derby://localhost:"+ NETWORKSERVER_PORT+"/"+DBNAME+";create=true";

	// Default to using the Derby Client JDBC Driver for database connections
	String url = DERBY_CLIENT_URL;
	String jdbcDataSource = DERBY_CLIENT_DS;

	public static void main (String[] args) throws Exception
	{

	new SimpleNetworkClientSample().startSample(args);

	}
	public void startSample (String[] args) throws Exception
	{
	DataSource clientDataSource = null;
	Connection clientConn1 = null;
	Connection clientConn2 = null;


	try
	{
	System.out.println("Starting Sample client program ");

	// get a client connection using DriverManager
	clientConn1 = getClientDriverManagerConnection();
	System.out.println("Got a client connection via the DriverManager.");

	// create a datasource with the necessary information
	javax.sql.DataSource myDataSource = getClientDataSource(DBNAME, null, null);

	// get a client connection using DataSource
	clientConn2 = getClientDataSourceConn(myDataSource);
	System.out.println("Got a client connection via a DataSource.");

	// test connections by doing some work
	System.out.println("Testing the connection obtained via DriverManager by executing a sample query ");
	test(clientConn1);
	System.out.println("Testing the connection obtained via a DataSource by executing a sample query ");
	test(clientConn2);

	System.out.println("Goodbye!");
	}
	catch (SQLException sqle)
	{
	System.out.println("Failure making connection: " + sqle);
	sqle.printStackTrace();
	}
	finally
	{

	if(clientConn1 != null)
	clientConn1.close();
	if(clientConn2 != null)
	clientConn2.close();
	}
	}

	/**
	* Get a database connection from DataSource
	* @pre Derby Network Server is started
	* @param ds data source
	* @return returns database connection
	* @throws Exception if there is any error
	*/
	public Connection getClientDataSourceConn(javax.sql.DataSource ds)
	throws Exception
	{
	Connection conn = ds.getConnection("usr2", "pass2");
	System.out.print("connection from datasource; getDriverName = ");
	System.out.println(conn.getMetaData().getDriverName());
	return conn;
	}

	/**
	* Creates a client data source and sets all the necessary properties in order to
	* connect to Derby Network Server
	* The server is assumed to be running on 1527 and on the localhost
	* @param database database name; can include Derby URL attributes
	* @param user database user
	* @param password
	* @return returns DataSource
	* @throws Exception if there is any error
	*/
	public javax.sql.DataSource getClientDataSource(String database, String user, String
	password) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException
	{
	Class<?> nsDataSource = Class.forName(jdbcDataSource);
	DataSource ds = (DataSource) nsDataSource.getConstructor().newInstance();

	// can also include Derby URL attributes along with the database name
	Class[] methodParams = new Class[] {String.class};
	Method dbname = nsDataSource.getMethod("setDatabaseName", methodParams);
	Object[] args = new Object[] {database};
	dbname.invoke(ds, args);

	if (user != null) {
	Method setuser = nsDataSource.getMethod("setUser", methodParams);
	args = new Object[] {user};
	setuser.invoke(ds, args);
	}
	if (password != null) {
	Method setpw = nsDataSource.getMethod("setPassword", methodParams);
	args = new Object[] {password};
	setpw.invoke(ds, args);
	}
	// host on which network server is running
	Method servername = nsDataSource.getMethod("setServerName", methodParams);
	args = new Object[] {"localhost"};
	servername.invoke(ds, args);

	// port on which Network Server is listening
	methodParams = new Class[] {int.class};
	Method portnumber = nsDataSource.getMethod("setPortNumber", methodParams);
	args = new Object[] {1527};
	portnumber.invoke(ds, args);

	return ds;

	}

	/**
	* Get a client connection using the DriverManager
	* @pre The JDBC driver must have been loaded before calling this method
	* @return Connection client database connection
	*/
	public Connection getClientDriverManagerConnection()
	throws Exception
	{

	// See Derby documentation for description of properties that may be set
	// in the context of the network server.
	Properties properties = new java.util.Properties();

	// The user and password properties are a must, required by JCC
	properties.setProperty("user","derbyuser");
	properties.setProperty("password","pass");

	// Get database connection via DriverManager api
	Connection conn = DriverManager.getConnection(url,properties);

	return conn;
	}


	/**
	* Test a connection by executing a sample query
	* @param conn database connection
	* @throws Exception if there is any error
	*/
	public void test(Connection conn)
	throws Exception
	{

	Statement stmt = null;
	ResultSet rs = null;
	try
	{
	// To test our connection, we will try to do a select from the system catalog tables
	stmt = conn.createStatement();
	rs = stmt.executeQuery("select count(*) from sys.systables");
	while(rs.next())
	System.out.println("number of rows in sys.systables = "+ rs.getInt(1));

	}
	catch(SQLException sqle)
	{
	System.out.println("SQLException when querying on the database connection; "+ sqle);
	throw sqle;
	}
	finally
	{
	if(rs != null)
	rs.close();
	if(stmt != null)
	stmt.close();
	}
	}

	}