Ajay Shilwant Blogs....: 01/01/2011

17 January 2011

Java Database Connectivity (JDBC Tutorial)

Java Database Connectivity:

JDBC (Java Database Connectivity) is designed to allow users to use SQL(Structured Query Language) to query databases. It makes the tasks of the developers easy as it handles all low-level concerns about particular database types.

JDBC is similar to Microsoft’s ODBC with the plus point “Platform Independence”. To use JDBC, you need to have database driver to communicate with the database. Normally drivers are installed while installing the database. Like if you install MS SQL Server, Oracle or DB2, database drivers will be installed. If you are working with MySQL, PostgreSQL or some third party database, you need to put its driver (Jar fileI into the class path.

JDBC Drivers
            JDBC drivers can be broadly divided into four categories depending upon the driver implementation. The four categories/types are:

            1: JDBC-ODBC Bridge
            2: Native-API/partly Java driver
            3: Net-protocol/all-Java driver
            4: Native-protocol/all-Java driver

I will briefly talk about each type:
            JDBC-OBC bridge driver is pure Java and is include in java.sql.*. The client needs ODBC driver manager and ODBC driver for data source. It is ideal in situations, when ODBC driver is available for the database and is already installed on the client machine.

            Type-2 is Native code driver. It implements native JDBC interfaces using language functions in the DBMS product’s API. Type 2 drivers need platform specific library, so client and server both may run on same host. Type 2 drivers offer better performance than Type 1 drivers.

Type 3 drivers are pure Java drivers and they use middleware network protocol. They need DBMS server to implement the standard protocol to be middleware specific. The advantage is that there is no nee for any vendor database library to be present on client machines. Interesting thing is, there is no JDBC standard network protocol yet.

Type 4 drivers are pure Java drivers and they use vendor specific network protocol. These use DBMS specific network protocol (Oracle SQL Net, etc).
For the beginners, Type 1 drivers are suitable. Users simply have to make a DSN and start interacting with the database.

Using JDBC-ODBC Bridge

The beginners should start with JDBC-ODBC Bridge since it is simple and easy to work with. Consider that you have a database with tables and data and you want to connect to it in order to carry out operations.
First step is to create an ODBC dsn. It is done from Control panel > Data Sources (ODBC).
Now you have to load the JDBC driver. This is done using static method forName(…) of class called Class. Static method forName(…) takes name of the driver as parameter.

Code:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

DriverManager.getConnection() is used to connect to the database. Its signature is as follows:

Code:

static Connection getConnection(String url, String user, String password)

Continue...
31/01/2011

Connection time:

Sometimes, it is interesting to know how much time it takes to connect to the database. The code sample below calculates the time it takes to connect to a database referred by dsn.

Code:

long connection_time;

Date start = new Date(); //get start time

String stUrl_= "jdbc:odbc:myDSN";

connection_ = DriverManager.getConnection(stUrl,"sa","sa");

Date end = new java.util.Date(); //get end time

connection_time = end.getTime()-start.getTime();

Getting the Warnings:

Sometimes it is a wise decision to retrieve the first warning reported by calls on this Connection object. This can be done using getWarnings() method. The code sample below shows how to print all the warnings with their sates and messages.

Code:

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;

Connection conn = DriverManager.getConnection( "jdbc:odbc:Database" ) ;

// Print all warnings

for( SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning() )

{

System.out.println( "SQL Warning:" ) ;

System.out.println( "State : " + warn.getSQLState() ) ;

System.out.println( "Message: " + warn.getMessage() ) ;

System.out.println( "Error : " + warn.getErrorCode() ) ;

}

Adding records in the database tables:

Statement object is used to add enteries into the tables. The method used is executeUpdate(…).

Code:

Statement st = conn.createStatement();

st.executeUpdate("INSERT INTO customers VALUES (100, 'Laiq', 'Mr.', 'Paris', 2008)");

Using Resultset:
ResultSet is an interface found in java.sql package. It actually represents a table in the memory containing all the records fetched from the database in result of a query.

Code:

String name, brand ;

float price;

ResultSet rs = stmt.executeQuery("SELECT * FROM customers");

while ( rs.next() ) {

name = rs.getString("name");

brand = rs.getString("brand");

price = rs.getFloat("price");

}

Getting number of rows updated
Statement’s executeUpdate(…) method return no of row modified. So you can easily know how many rows were modified by your update query.

Code:

int rows = stmt.executeUpdate( "UPDATE customer SET

cust_name = ‘Laiq’ WHERE cust_id = 100" ) ;

System.out.println( rows + " Rows modified" ) ;

Java Database Connectivity:

Code:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

DriverManager.getConnection() is used to connect to the database. Its signature is as follows:

Code:

static Connection getConnection(String url, String user, String password)

Continue...
31/01/2011

Code:

long connection_time;

Date start = new Date(); //get start time

String stUrl_= "jdbc:odbc:myDSN";

connection_ = DriverManager.getConnection(stUrl,"sa","sa");

Date end = new java.util.Date(); //get end time

connection_time = end.getTime()-start.getTime();

Code:

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;

Connection conn = DriverManager.getConnection( "jdbc:odbc:Database" ) ;

// Print all warnings

for( SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning() )

{

System.out.println( "SQL Warning:" ) ;

System.out.println( "State : " + warn.getSQLState() ) ;

System.out.println( "Message: " + warn.getMessage() ) ;

System.out.println( "Error : " + warn.getErrorCode() ) ;

}

Adding records in the database tables:

Statement object is used to add enteries into the tables. The method used is executeUpdate(…).

Code:

Statement st = conn.createStatement();

st.executeUpdate("INSERT INTO customers VALUES (100, 'Laiq', 'Mr.', 'Paris', 2008)");

Using Resultset:
ResultSet is an interface found in java.sql package. It actually represents a table in the memory containing all the records fetched from the database in result of a query.

Code:

String name, brand ;

float price;

ResultSet rs = stmt.executeQuery("SELECT * FROM customers");

while ( rs.next() ) {

name = rs.getString("name");

brand = rs.getString("brand");

price = rs.getFloat("price");

}

Getting number of rows updated
Statement’s executeUpdate(…) method return no of row modified. So you can easily know how many rows were modified by your update query.

Code:

int rows = stmt.executeUpdate( "UPDATE customer SET

cust_name = ‘Laiq’ WHERE cust_id = 100" ) ;

System.out.println( rows + " Rows modified" ) ;

14 January 2011

In Java format a message that contains date information

/* **********************************************************************************
This example demonstrate how you can use the java.text.MessageFormat class to format a message with a date information in it.
*************************************************************************************/
import java.util.Date;
import java.util.Calendar;
import java.util.Locale;
import java.text.MessageFormat;

public class MessageFormatDate {
public static void main(String[] args) {
Date today = new Date();
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.DATE, 7);

Date nextWeek = calendar.getTime();
Date[] dates = new Date[] {today, nextWeek};

// We want the message to be is Locale.US

Locale.setDefault(Locale.US);

// Format a date, the time value is included
String message = MessageFormat.format("Today is {0} and next week is {1}", dates);
System.out.println(message);

// Format a date and display only the date portion
message = MessageFormat.format("Today is {0,date} and next week is {1,date}", dates);
System.out.println(message);

// Format a date using a short format (eg. dd/MM/yyyy)
message = MessageFormat.format("Today is {0,date,short} and next week is {1,date,short}", dates);
System.out.println(message);

// Format a date using a medium format, it display the month long name,
// but using a two digit date and year.
message = MessageFormat.format("Today is {0,date,medium} and next week is {1,date,medium}", dates);
System.out.println(message);

// Format a date using a long format, two digit for date, a long month
// name and a four digit year.
message = MessageFormat.format("Today is {0,date,long} and next week is {1,date,long}", dates);
System.out.println(message);

// Format a date using a full format, the same as above plus a full day
// name.
message = MessageFormat.format("Today is {0,date,full} and next week is {1,date,full}", dates);
System.out.println(message);

// Format a date using a custom pattern.
message = MessageFormat.format("Today is {0,date,dd-MM-yyyy} and next week is {1,date,dd-MM-yyyy}", dates);
System.out.println(message);
}
}

// Out Put

Today is 5/5/09 7:54 AM and next week is 5/12/09 7:54 AM
Today is May 5, 2009 and next week is May 12, 2009
Today is 5/5/09 and next week is 5/12/09
Today is May 5, 2009 and next week is May 12, 2009
Today is May 5, 2009 and next week is May 12, 2009
Today is Tuesday, May 5, 2009 and next week is Tuesday, May 12, 2009
Today is 05-05-2009 and next week is 12-05-2009 /* **********************************************************************************
This example demonstrate how you can use the java.text.MessageFormat class to format a message with a date information in it.
*************************************************************************************/
import java.util.Date;
import java.util.Calendar;
import java.util.Locale;
import java.text.MessageFormat;

public class MessageFormatDate {
public static void main(String[] args) {
Date today = new Date();
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.DATE, 7);

Date nextWeek = calendar.getTime();
Date[] dates = new Date[] {today, nextWeek};

// We want the message to be is Locale.US

Locale.setDefault(Locale.US);

// Format a date, the time value is included
String message = MessageFormat.format("Today is {0} and next week is {1}", dates);
System.out.println(message);

// Format a date and display only the date portion
message = MessageFormat.format("Today is {0,date} and next week is {1,date}", dates);
System.out.println(message);

// Format a date using a short format (eg. dd/MM/yyyy)
message = MessageFormat.format("Today is {0,date,short} and next week is {1,date,short}", dates);
System.out.println(message);

// Format a date using a medium format, it display the month long name,
// but using a two digit date and year.
message = MessageFormat.format("Today is {0,date,medium} and next week is {1,date,medium}", dates);
System.out.println(message);

// Format a date using a long format, two digit for date, a long month
// name and a four digit year.
message = MessageFormat.format("Today is {0,date,long} and next week is {1,date,long}", dates);
System.out.println(message);

// Format a date using a full format, the same as above plus a full day
// name.
message = MessageFormat.format("Today is {0,date,full} and next week is {1,date,full}", dates);
System.out.println(message);

// Format a date using a custom pattern.
message = MessageFormat.format("Today is {0,date,dd-MM-yyyy} and next week is {1,date,dd-MM-yyyy}", dates);
System.out.println(message);
}
}

// Out Put

Today is 5/5/09 7:54 AM and next week is 5/12/09 7:54 AM
Today is May 5, 2009 and next week is May 12, 2009
Today is 5/5/09 and next week is 5/12/09
Today is May 5, 2009 and next week is May 12, 2009
Today is May 5, 2009 and next week is May 12, 2009
Today is Tuesday, May 5, 2009 and next week is Tuesday, May 12, 2009
Today is 05-05-2009 and next week is 12-05-2009

In Java format a date into dd/mm/yyyy and other date formats....

/* **********************************************************************************
Formatting a display is a common requirement when creating a program. Information in a good format can be seen as an added value to the user of the program. Using Java SimpleDateFormat we can easily format a date in our program.
********************************************************************************* */

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class DateFormatExample {
    public static void main(String[] args) {
        Date date = Calendar.getInstance().getTime();

        // Display a date in day, month, year format

        DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        String today = formatter.format(date);
        System.out.println("Today : " + today);


        // Display date with day name in a short format

        formatter = new SimpleDateFormat("EEE, dd/MM/yyyy");
        today = formatter.format(date);
        System.out.println("Today : " + today);


       // Display date with a short day and month name

       formatter = new SimpleDateFormat("EEE, dd MMM yyyy");
       today = formatter.format(date);
       System.out.println("Today : " + today);


        // Formatting date with full day and month name and show time up to
        // milliseconds with AM/PM

       formatter = new SimpleDateFormat("EEEE, dd MMMM yyyy, hh:mm:ss.SSS a");
       today = formatter.format(date);
       System.out.println("Today : " + today);
   }
}

//Out Put....
Today : 02/12/2007
Today : Sun, 02/12/2007
Today : Sun, 02 Dec 2007
Today : Sunday, 02 December 2007, 08:03:17.828 AM /* **********************************************************************************
Formatting a display is a common requirement when creating a program. Information in a good format can be seen as an added value to the user of the program. Using Java SimpleDateFormat we can easily format a date in our program.
********************************************************************************* */

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class DateFormatExample {
    public static void main(String[] args) {
        Date date = Calendar.getInstance().getTime();

        // Display a date in day, month, year format

        DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        String today = formatter.format(date);
        System.out.println("Today : " + today);


        // Display date with day name in a short format

        formatter = new SimpleDateFormat("EEE, dd/MM/yyyy");
        today = formatter.format(date);
        System.out.println("Today : " + today);


       // Display date with a short day and month name

       formatter = new SimpleDateFormat("EEE, dd MMM yyyy");
       today = formatter.format(date);
       System.out.println("Today : " + today);


        // Formatting date with full day and month name and show time up to
        // milliseconds with AM/PM

       formatter = new SimpleDateFormat("EEEE, dd MMMM yyyy, hh:mm:ss.SSS a");
       today = formatter.format(date);
       System.out.println("Today : " + today);
   }
}

//Out Put....
Today : 02/12/2007
Today : Sun, 02/12/2007
Today : Sun, 02 Dec 2007
Today : Sunday, 02 December 2007, 08:03:17.828 AM

13 January 2011

Change( Screen) Windows display resolution.

'Using this code to change Screen Resolution......... or your computer
' this code is use for VB 6.0

'********************** Module*****************
Declarations
Public Const EWX_LOGOFF = 0
Public Const EWX_SHUTDOWN = 1
Public Const EWX_REBOOT = 2
Public Const EWX_FORCE = 4
Public Const CCDEVICENAME = 32
Public Const CCFORMNAME = 32
Public Const DM_BITSPERPEL = &H40000
Public Const DM_PELSWIDTH = &H80000
Public Const DM_PELSHEIGHT = &H100000
Public Const CDS_UPDATEREGISTRY = &H1
Public Const CDS_TEST = &H4
Public Const DISP_CHANGE_SUCCESSFUL = 0
Public Const DISP_CHANGE_RESTART = 1

Type typDevMODE
    dmDeviceName       As String * CCDEVICENAME
    dmSpecVersion      As Integer
    dmDriverVersion    As Integer
    dmSize             As Integer
    dmDriverExtra      As Integer
    dmFields           As Long
    dmOrientation      As Integer
    dmPaperSize        As Integer
    dmPaperLength      As Integer
    dmPaperWidth       As Integer
    dmScale            As Integer
    dmCopies           As Integer
    dmDefaultSource    As Integer
    dmPrintQuality     As Integer
    dmColor            As Integer
    dmDuplex           As Integer
    dmYResolution      As Integer
    dmTTOption         As Integer
    dmCollate          As Integer
    dmFormName         As String * CCFORMNAME
    dmUnusedPadding    As Integer
    dmBitsPerPel       As Integer
    dmPelsWidth        As Long
    dmPelsHeight       As Long
    dmDisplayFlags     As Long
    dmDisplayFrequency As Long
End Type

Declare Function EnumDisplaySettings Lib "user32" Alias "EnumDisplaySettingsA" (ByVal lpszDeviceName As Long, ByVal iModeNum As Long, lptypDevMode As Any) As Boolean
Declare Function ChangeDisplaySettings Lib "user32" Alias "ChangeDisplaySettingsA" (lptypDevMode As Any, ByVal dwFlags As Long) As Long
Declare Function ExitWindowsEx Lib "user32" (ByVal uFlags As Long, ByVal dwReserved As Long) As Long

'********************************************************************************************************************************************************************
'**************************Form******************************

Dim ScreenWidth As Integer
Dim ScreenHeight As Integer

Private Sub Command1_Click()
'Code:
'Following Text Boxes is the parameters for the screen resulution
'eg. 800*600
Dim typDevM As typDevMODE
Dim lngResult As Long
Dim intAns    As Integer
ScreenWidth = Val(Text1.Text) '800
ScreenHeight = Val(Text2.Text) '600

' Retrieve info about the current graphics mode
' on the current display device.
lngResult = EnumDisplaySettings(0, 0, typDevM)

' Set the new resolution. Don't change the color
' depth so a restart is not necessary.
With typDevM
    .dmFields = DM_PELSWIDTH Or DM_PELSHEIGHT
    .dmPelsWidth = ScreenWidth 'ScreenWidth (640,800,1024, etc)
    .dmPelsHeight = ScreenHeight 'ScreenHeight (480,600,768, etc)
End With

' Change the display settings to the specified graphics mode.
lngResult = ChangeDisplaySettings(typDevM, CDS_TEST)
Select Case lngResult
    Case DISP_CHANGE_RESTART
        intAns = MsgBox("You must restart your computer to apply these changes." & _
            vbCrLf & vbCrLf & "Do you want to restart now?", _
            vbYesNo + vbSystemModal, "Screen Resolution")
        If intAns = vbYes Then Call ExitWindowsEx(EWX_REBOOT, 0)
    Case DISP_CHANGE_SUCCESSFUL
        Call ChangeDisplaySettings(typDevM, CDS_UPDATEREGISTRY)
        Message = MsgBox("Screen resolution changed", vbInformation, "Resolution Changed ")
    Case Else
        Message = MsgBox("Mode not supported", vbSystemModal, "Error")
End Select

End Sub 'Using this code to change Screen Resolution......... or your computer
' this code is use for VB 6.0

'********************** Module*****************
Declarations
Public Const EWX_LOGOFF = 0
Public Const EWX_SHUTDOWN = 1
Public Const EWX_REBOOT = 2
Public Const EWX_FORCE = 4
Public Const CCDEVICENAME = 32
Public Const CCFORMNAME = 32
Public Const DM_BITSPERPEL = &H40000
Public Const DM_PELSWIDTH = &H80000
Public Const DM_PELSHEIGHT = &H100000
Public Const CDS_UPDATEREGISTRY = &H1
Public Const CDS_TEST = &H4
Public Const DISP_CHANGE_SUCCESSFUL = 0
Public Const DISP_CHANGE_RESTART = 1

Type typDevMODE
    dmDeviceName       As String * CCDEVICENAME
    dmSpecVersion      As Integer
    dmDriverVersion    As Integer
    dmSize             As Integer
    dmDriverExtra      As Integer
    dmFields           As Long
    dmOrientation      As Integer
    dmPaperSize        As Integer
    dmPaperLength      As Integer
    dmPaperWidth       As Integer
    dmScale            As Integer
    dmCopies           As Integer
    dmDefaultSource    As Integer
    dmPrintQuality     As Integer
    dmColor            As Integer
    dmDuplex           As Integer
    dmYResolution      As Integer
    dmTTOption         As Integer
    dmCollate          As Integer
    dmFormName         As String * CCFORMNAME
    dmUnusedPadding    As Integer
    dmBitsPerPel       As Integer
    dmPelsWidth        As Long
    dmPelsHeight       As Long
    dmDisplayFlags     As Long
    dmDisplayFrequency As Long
End Type

Declare Function EnumDisplaySettings Lib "user32" Alias "EnumDisplaySettingsA" (ByVal lpszDeviceName As Long, ByVal iModeNum As Long, lptypDevMode As Any) As Boolean
Declare Function ChangeDisplaySettings Lib "user32" Alias "ChangeDisplaySettingsA" (lptypDevMode As Any, ByVal dwFlags As Long) As Long
Declare Function ExitWindowsEx Lib "user32" (ByVal uFlags As Long, ByVal dwReserved As Long) As Long

'********************************************************************************************************************************************************************
'**************************Form******************************

Dim ScreenWidth As Integer
Dim ScreenHeight As Integer

Private Sub Command1_Click()
'Code:
'Following Text Boxes is the parameters for the screen resulution
'eg. 800*600
Dim typDevM As typDevMODE
Dim lngResult As Long
Dim intAns    As Integer
ScreenWidth = Val(Text1.Text) '800
ScreenHeight = Val(Text2.Text) '600

' Retrieve info about the current graphics mode
' on the current display device.
lngResult = EnumDisplaySettings(0, 0, typDevM)

' Set the new resolution. Don't change the color
' depth so a restart is not necessary.
With typDevM
    .dmFields = DM_PELSWIDTH Or DM_PELSHEIGHT
    .dmPelsWidth = ScreenWidth 'ScreenWidth (640,800,1024, etc)
    .dmPelsHeight = ScreenHeight 'ScreenHeight (480,600,768, etc)
End With

' Change the display settings to the specified graphics mode.
lngResult = ChangeDisplaySettings(typDevM, CDS_TEST)
Select Case lngResult
    Case DISP_CHANGE_RESTART
        intAns = MsgBox("You must restart your computer to apply these changes." & _
            vbCrLf & vbCrLf & "Do you want to restart now?", _
            vbYesNo + vbSystemModal, "Screen Resolution")
        If intAns = vbYes Then Call ExitWindowsEx(EWX_REBOOT, 0)
    Case DISP_CHANGE_SUCCESSFUL
        Call ChangeDisplaySettings(typDevM, CDS_UPDATEREGISTRY)
        Message = MsgBox("Screen resolution changed", vbInformation, "Resolution Changed ")
    Case Else
        Message = MsgBox("Mode not supported", vbSystemModal, "Error")
End Select

End Sub