JDeveloper R12 Calling SQL Statement Inside Java Code

Calling SQL Statment Inside Java Code

One of the important way for developing is calling a sql statment inside your java code and I will explain how to do this:
1- call a select statment
asume we want to make a function take department Id and return its name so we will make this function

	public String getDeptName(int deptId)
	{
	PreparedStatement stat = null;
	ResultSet rs = null;
	try
	{
	String sql = "select dept_name from departments where dept_id=" + deptId;
	stat = getAm().getDBTransaction().createPreparedStatement(sql, 1);
	rs = stat.executeQuery();
	if (rs.next())
	{
	return rs.getString(1);
	}
	}
	catch (Exception e)
	{
	e.printStackTrace();
	}
	finally
	{
	if (rs != null)
	try
	{
	rs.close();
	}
	catch (Exception e)
	{
	}
	if (stat != null)
	try
	{
	stat.close();
	}
	catch (Exception e)
	{
	}
	}
	return null;
	}

2- Call an updatable statment as (Create, Insert, Update and Delete)asume we want to make a function take department id and delete it

	public void deleteDepartment(int deptId)
	{
	PreparedStatement stat = null;
	try
	{
	String sql = "DELETE FROM Dept WHERE dept_id=" + deptId;
	stat = getAm().getDBTransaction().createPreparedStatement(sql, 1);
	stat.executeUpdate();
	}
	catch (Exception e)
	{
	e.printStackTrace();
	}
	finally
	{
	if(stat != null)
	try
	{
	stat.close();
	}
	catch(Exception e) { }
	}
	return null;
	}

where getAm() is a method return your Application Module as :

	public AppModuleImpl getAm()
	{
	AppModuleImpl am = (AppModuleImpl) ADFUtils.getApplicationModuleForDataControl("AppModuleDataControl");
	return am;
	}

If you want to use java connection (don't want to use AppModule) you can use something like this:

	private static Connection getConnection()
	{
	Connection connection = null;
	try
	{
	Class.forName("oracle.jdbc.driver.OracleDriver");
	connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr");
	}
	catch (Exception e)
	{
	}
	return connection;
	}
	public static void InsertDep()
	{
	PreparedStatement stat = null;
	Connection conn = null;
	try
	{
	conn = getConnection();
	String sql ="Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (300,'Security',200,1700)";
	stat = conn.prepareStatement(sql);
	stat.executeQuery();
	conn.commit();
	}
	catch (Exception e)
	{
	e.printStackTrace();
	}
	finally
	{
	try
	{
	stat.close();
	conn.close();
	}
	catch (Exception e)
	{
	// TODO: Add catch code
	e.printStackTrace();
	}
	}
	}