run a sql query through JDBC .....

import java.sql.*;

class QueryTest
{
  public static void main(String args[]) throws Exception
  {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection l_con = DriverManager.getConnection("jdbc:oracle:thin:@172.20.0.207:1522:BCCLPROD","coalnetapps","coalnetapps");
      Statement l_stmt = l_con.createStatement();
      ResultSet l_rs = l_stmt.executeQuery("(select sysdate from dual)");
      while(l_rs.next()) System.out.println("Debasis: " + l_rs.getString(1));
      System.out.println("Debasis Yahoooooo .....");
  }
}

public class DBConnector 
{

	private String className;
	private Properties jdbcConnectProps = new Properties();
	private String connectionString;

	public Connection getJDBCConnection(String dbUserId, String dbPassword)
	{
		Connection conn = null;
		try
		{
			Class.forName(this.className);
			conn = DriverManager.getConnection(this.connectionString, dbUserId, dbPassword);
		}
		catch(Exception e)
		{
			// TODO: ND - use of ESF Exceptions relating to databases - may need to generate a new Exception class
		}

		return conn;
	}
	
	public void loadProperties(String propsLocation)
	{
		try
		{
			this.jdbcConnectProps.load(new FileInputStream(propsLocation));
		}
		catch(Exception e)
		{
			// TODO: ND - use ESFIOException as this means no props file found
		}
	}
	
	public void setClassName(String key)
	{
		try
		{
			this.className = jdbcConnectProps.getProperty(key);
		}
		catch(Exception e)
		{
			// TODO: ND - use ESFRetrievalException as this means we could not get the properties
		}

	}
	
	public void setConnectionString(String connectString)
	{
		this.connectionString = connectString;
	}
	
	public String getConnectionString()
	{
		return this.connectionString;
	}
	
	public String getClassName()
	{
		return className;
	}

	// @Override
	// public Connection getBlackboardJDBCConnection() {
	// // TODO Auto-generated method stub
	// return null;
	// }
	//
	//
	// @Override
	// public Connection getCMISJDBCConnection() {
	// // TODO Auto-generated method stub
	// return null;
	// }
}

#Class names for different databases
jdbc.sqlserver.classname=com.microsoft.sqlserver.jdbc.SQLServerDriver
#jbdc.oracle.classname=
#jdbc.mysql.classname=

package org.esf.test;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.esf.utils.DBConnector;

public class GetUserQueries
{
	private final DBConnector dbc = new DBConnector();
	private ResultSet usersByDSK = null;

	
	public static void main(String[] args)
	{
		GetUserQueries guq = new GetUserQueries();
		guq.run();
	}

	public GetUserQueries()
	{
		dbc.loadProperties("E:/full/path/to/properties/jdbcConnection.properties");
		dbc.setClassName("jdbc.sqlserver.classname");
		dbc.setConnectionString("jdbc:sqlserver://machine:port;databaseName=mydatabase");
	}

	public void run()
	{
		// get a rs
		usersByDSK = getUserNamesByDSK(1);
		// print rs
		System.out.println(toString());
	}

	public ResultSet getUserNamesByDSK(int dsk)
	{
		Statement queryStmt;
		ResultSet queryResult = null;
		try
		{
			queryStmt = dbc.getJDBCConnection("username", "password").createStatement();
			queryResult = queryStmt.executeQuery("Select user_id from users where data_src_pk1 = " + dsk);
		}
		catch(SQLException sqlEx)
		{
			System.out.println("Oh Poo - can't query");
		}
		return queryResult;
	}

	public String toString()
	{
		StringBuilder sb = new StringBuilder();

		try
		{
			while(usersByDSK.next())
			{
				// retrieve and print the values for the current row

				sb.append("User : " + usersByDSK.getString("user_id") + "\n");
			}
		}
		catch(SQLException e)
		{
			System.out.println("Oh Poo - results issues");
			//TODO: ND Handle this exception properly
		}

		return sb.toString();
	}
}