Java Database Connectivity- JDBC Tutorial, PreparedStatement, ResultSet

JDBC is java based API or technology to access data from database and use in Java Desktop or Enterprize application. In short we can say JDBC is an interface between Java and Database, using JDBC you can interact with any of database as Oracle, MySQL, Access.
JDBC is released with JDK(Java Development Kit)1.1 on 19 Feb 1997 and It is a part of Java Standard Edition. if you know about DBMS(Database Management Systems) then you should know about Database Drivers, drivers are of 4 types.

1. Type 1 Driver - JDBC-ODBC bridge
2. Type 2 Driver - Native-API Driver
3. Type 3 Driver - Network-Protocol Driver(MiddleWare Driver)
4. Type 4 Driver - Native-Protocol Driver(Pure Java Driver)-Thin Driver

If you don't have java installed , follow the link below to donload it

JDK1.7- Download jdk1.7
Now we will learn that how to implement Database connection in Java. To start with JDBC you must have a little knowledge of DBMS and SQL(Structured Query Language) to interact with Database
here i will show you that how to connect with Oracle Database
Follow these steps to connect with Oracle Database

1. Load Driver (Thin Driver)

The very first step towards database connection is Loading Driver, as discussed we use pure java driver in JDBC connectivity there are two steps to load driver for connection

A. Using DriverManager class
The syntax is - DriverManager.registerDriver(DriverName);

1.         try {
2.             DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
3.            
4.         } catch (SQLException e) {
5.         }
6.        

B. Using Class.Forname class
The syntax is - Class.forName(”DriverName”);

1.         try {
2.              Class.forName("oracle.jdbc.driver.OracleDriver");
3.            
4.         } catch (SQLException e) {
5.         }
6.        

2. Create Connection

Connection interface is inside java.sql package and extends Wrapper interface. now to create Connection we register driver with DriverManager class.
Syntax is- DriverManager.getConnection("url string", "username", "password");

1. Connection con;
2.  try {
3.      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
4.      con=DriverManager.getConnection(
5.      "jdbc:oracle:thin:@192.168.1.234:1521:XE", "hr", "hr");
6.  } catch (SQLException e) {
7.  }

You have to change url string with your ip address and SID according to your database setting

3. Execute PreparedStatement and return Resultset

Now we see that how to execute SQL query using PreparedStatement and store its result in ResultSet object
Here i am getting username and password from LOGIN_DET table (you can use your own query here)

1.         Connection con;
2.         try {
3.             DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
4.             con = DriverManager.getConnection("
5.            jdbc:oracle:thin:@192.168.1.241:1521:eadf", "hr", "hr");
6.             PreparedStatement psmt = con.prepareStatement("SELECT * FROM LOGIN_DET");
7.             ResultSet rs = psmt.executeQuery();
8.             if (rs.next()) {
9.                 while (rs.next()) {
10.                 System.out.println("Username is--" + rs.getString(1)
11.                + "and Password is-->" + rs.getString(2) +
12.                "Email Id is--->" + rs.getString("E_ID"));
13.                 }
14.             }
15.             psmt.close();
16.             con.close();
17.         } catch (SQLException e) {
18.         }

This is the brief overview of using JDBC with Oracle Database, For others you have to change only DriverName respectively
Happy Learning :)

ADF Basics: Disable user input in af:inputListOfValues

Sometimes we need to disable user input in input Lov as there is requirement of selecting value only from Lov popup

By default af:inputListOfValues provides feature to select value from popup or type a value as input

To disable user input there is a property -EditMode
From Oracle Docs-

editMode

String

Yes

Valid Values: input, select the mode that controls how the user specifies a value. This attribute is only applicable when the 'readOnly' attribute is set to false. input: this mode allows the user to type in a value as well as browse and select from a list of available values. select: this mode allows the user only to browse and select from a list of available values.

Set it's value to select

Happy Learning , Thanks :)

Searching pivotTable using dvt:pivotFilterBar in Oracle ADF

dvt:pivotTable-

From Oracle docs- The Pivot Table supports the display of multiple nested attributes on a row and column header. In addition, the Pivot Table supports the ability to dynamically change the layout of the attributes displayed in the row or column headers via drag and drop pivoting.

Framework provides  <dvt:pivotFilterBar>component to perform search operation on pivotTable
The PivotFilterBar component is used to filter data based on the selected criterion belonging to the PivotableQueryDescriptor as specified by the value property



In this example i am using Departments and Employees table of HR Schema, Created a query based viewObject using this query (to show Departments and it's Employees)

SELECT A.DEPARTMENT_ID,  
B.EMPLOYEE_ID, 
A.DEPARTMENT_NAME , 
B.FIRST_NAME||' '||B.LAST_NAME AS NAME,  
B.EMAIL,  
B.PHONE_NUMBER, 
B.HIRE_DATE, 
B.JOB_ID, 
B.SALARY, 
B.COMMISSION_PCT 
FROM DEPARTMENTS A, EMPLOYEES B 
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID

Add this viewObject to Application Module and drop on page as pivot table from Data Control

Configuration for pivotTable-

Finally it looks like this-

Now i have dropped <dvt:pivotTable> on page and it automatically sets it's properties that are required for filtering pivotTable

<dvt:pivotFilterBar id="pt1pivotFilterBar" value="#{bindings.DeptEmp1.pivotFilterBarModel}"
                                    modelName="pt1Model"/>

Once check that modelName property should be same for both pivotTable and filterbar
Now run this application and drop any column on filterbar that you want to search

Sample ADF Application-Download
Cheers :) Happy Learning