Database Access is a very important aspect of programming. Most applications are based on data storage and maintenance and to build such features, one has to be familiar with database programming.
Database access has two aspects: Front End and Back End. Front End is designed by the programmer. It is basically the GUI (Graphical User Interface) such as a screen with embedded textboxes to enable the user to enter data. Whereas, Back End is the heart of this operation as it plays the role of actually entering the data into the data sources. Designing a Back End is not in the approach of an individual and thus, we adopt third-party Back End platform libraries amongst various such libraries available like Oracle, Microsoft Access, FoxPro etc. Throughout this article, we are going to use ‘Microsoft Access’ as the Back End for both VB and JAVA.
Database Access in VB
Before you start database accessing code, you must add a reference to ‘Microsoft ActiveX Data objects 2.xx (version may be different on your PC, mine is 2.8) Library’ object before writing the actual code. The object reference is added as follows:
- Select Project >> References
- Select ‘Microsoft ActiveX Data objects 2.xx Library’ and put a checkbox in front of it.
- Press OK.
For your convenience, below is a picture of VB IDE on how to add this reference.
Now as we have added a reference to this object, we can start writing the code now. First of all, we need to create a connection to the database. There are various techniques of establishing a connection and I am using one of them. Below is the code for establishing a connection to a ‘Microsoft Access’ database named ‘data.mdb’ which resides in root drive D.
Dim adoC as ADODB.Connection
Set adoC=New ADODB.Connection
adoC.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/data.mdb"
Firstly we need to initialize a connection object. as in 1st and 2nd lines. Then we need to open a database by providing a ConnectionString paramater. You will notice that in the first half of the ConnectionString, I have specified a value for ‘Provider’. You must be wondering what is that. It is actually the name of the provider which gives us the platform to communicate with the database. It is a bridge between our program and the database and is provided by Microsoft which usually gets installed with the windows. The second half of this ConnectionString provides the name of the database to use.
Database Access in JAVA
Database Access in JAVA is a little bit different than VB. Before you start, you have to import the following packages:
- java.sql (To manage database objects)
- java.io (To manage Exceptions) (Please note that we are not discussing the exceptions here as this chapter focuses mainly on DB concepts. You will have to write the exception handler code yourself. If you want to know how to write exception handlers, Click Here to find out.)
- sun.jdbc.odbc (To manager database drivers)
Before you start writing a function in a java class, you will have to place this piece of code:
static {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
This code loads the jdbc-to-odbc driver which provides us the interface to communicate with the database.
Now, to connect the java application to a ‘Microsoft Access’ database, you will write the code as follows:
private Connection con;
con=DriverManager.getConnection("Jdbc:Odbc:;Driver={Microsoft Access Driver (*.mdb)};DBQ=D:/data.mdb");
First of all, you have to initialize a ‘Connection’ object to handle the connection to the database. ‘Connection’ Class is an abstract class and so you can’t initialize it with the ‘new’ keyword. So, you have to use the ‘DriverManager’ class to initialize a new ‘Connection’ class. Use it’s ‘getConnection’ function to establish a new connection. This function takes a ConnectionString as a parameter. This ConnectionString can be classified into three parts.
- Name of the Java Database driver (Jdbc in this case, applied mostly in all the cases)
- Name of the driver that you are using (also know as subprotocol) (Odbc in this case as we are using ‘Microsoft ODBC drivers here but it may vary and depend upon the driver you are using)
- The data source.
This data source part of the ConnectionString is a little different from the ConnectionString used in VB above. Mainly the first-half is different. In VB, we used the ‘Provider’ whereas here, we have used the ‘Driver’ interface. Java may not support the Microsoft Jet Providers, therefore we are using the ‘Drivers’ interface here. Though, the data source part of this ConnectionString will definitely work in VB as well.
Manipulating data
There are many and many ways of manipulating data contained in the database. We are using one of them which is widely used. Please note that we are using a database here which has one table named ‘Table1’ and has two columns : ‘Name’ and ‘Age’.
Entering data to data source in VB
Now, we are going to see how can we enter data into the data source.
Firstly, let’s have a look at the code:
'Initialize the connection first
Dim adoC As ADODB.Connection
Set adoC = New ADODB.Connection
adoC.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/data.mdb"
'Now enter the data into the database
Dim com As ADODB.Command
Set com = New ADODB.Command
com.ActiveConnection = adoC
com.CommandType = adCmdText
com.CommandText = "INSERT INTO Table1 (Name,Age) Values ('Rahul','35')"
com.Execute
The first part of this code has already been explained above. The first part connects the application to the database. Now, we come to the second part. The second part starts by declaring a variable of type ‘Command’ of the Data objects library. This type of object helps us in executing the SQL commands to the database. SQL (Structured Query Language) is a complete language used to perform various data operations on the databases. SQL has a set of commands like INSERT (The one we have used above), UPDATE, DELETE, SELECT etc. We are not discussing SQL in details here and you will have to go through a detailed study of SQL to know more about it. The ‘Command’ Object is first initialised and then associated with an active connection. Then its type is specified by using its ‘CommandType’ function.
Sometimes, the data is accessed directly by using the name of its table instead of an SQL Query and thus, in such cases, ‘CommandType’ plays a very important role. If the data is accessed directly by its table name, the CommandType will be ‘adCmdTable’ and if the data is accessed through a SQL Query like in the case above, then the ‘CommandType’ would be ‘adCmdText’. Similarly, there are a few more valid options for the ‘CommandType’ that exist. Finally, ‘Command’ object’s ‘Execute’ function is called to execute the SQL Query.
Entering data to data source in JAVA
Entering data to a data source in JAVA is quite similar to VB with just a few differences. Here is the code below:
'Initialize the connection first
private Connection con;
con=DriverManager.getConnection("Jdbc:Odbc:;Driver=Microsoft Access Driver (*.mdb);DBQ=D:/data.mdb");
'Now enter the data into the database
statement=con.createStatement();
statement.executeUpdate("INSERT INTO Table1 (Name,Age) Values ('Raksh','44')");
con.close ();
You can notice here that the ‘Statement’ object does exactly the same job of ‘Command’ object in VB. Yes, you are absolutely right. The ‘Statement’ object here can’t be initialized with the ‘new’ keyword, therefore we use the ‘Connection’ object’s ‘createStatement()’ method to associate the ‘Statement’ object to the active connection. Then we call the ‘executeUpdate’ method of the ‘Statement’ object to execute the updatable SQL query as it’s parameter. Please note that the last line is very important. The last line is used to close the active connection. If you skip this line. the connection will remain open and thus, the updates will not be applied to the database. Therefore, you must place this line when you want to exit the program and apply the updates to the database.
Retrieving data
The section above discussed how to apply updates to the database. This section will explain how to retrieve data from the database.
Firstly, you must know that both JAVA and VB have an object which holds the data of the database in the form of columns and rows. This object in VB is called ‘Recordset’ and in JAVA, it is called ‘ResultSet’. You can then use these objects to retrieve the values of any column of any row.
Retrieving data from the data source in VB
Let’s see how it works in VB.
'Initialize the connection first
Dim adoC As ADODB.Connection
Set adoC = New ADODB.Connection
adoC.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/data.mdb"
'Now retrieve the data from the database
Dim adoR as ADODB.Recordset
Set adoR=New ADODB.Recordset
adoR.Open "Table1", adoC, adOpenKeyset, adLockPessimistic
adoR.AbsolutePosition = 2
MsgBox adoR.Fields("Name").Value
As we discussed above, ‘Recordset’ is the object in VB which populates the data in the form of columns and rows. We must first initialize this object. Then we call it’s ‘Open’ function which has four parameters as follows:
- The first parameter is the name of the Table in the database whose data we want to access. In this case, we are using ‘Table1’ as it is the only table in our sample database.
- The second parameter is the name of the active database connection which we want to use for this recordset.
- The third parameter specifies the CursorType that we want to use and can have four constant values: adOpenDynamic, adOpenForwardOnly, adOpenKeyset, adOpenStatic
- The fourth parameter specifies the LockType that we want to use and can have one of these four values: :adLockBatchOptimistic, adLockOptimistic, adLockPessimistic, adLockReadOnly
Then we move to the 2nd record (row) of the table by calling ‘Recordset’ object’s ‘AbsolutePosition’ function. Finally, the application displays a message box containing the value of the column named ‘Name’ of the 2nd record.
Retrieving data from the data source in JAVA
Retrieving data in JAVA is also very similar to that of VB. Have a look at the code below:
'Initialize the connection first
private Connection con;
con=DriverManager.getConnection("Jdbc:Odbc:;Driver=Microsoft Access Driver (*.mdb);DBQ=D:/data.mdb");
'Now retrieve the data from the database
private Statement statement;
private ResultSet res;
statement=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
res=statement.executeQuery("Select * From Table1");
res.absolute(2);
System.out.print(res.getString("Name"));
The first difference that you will notice here is that the object that is used to hold the data is called ‘ResultSet’. It does the same job that a ‘Recordset’ does in VB.
You will also notice that the ‘Statement’ object here is initialized with a few parameters. These parameters are as follows:
- The first parameter specifies the CursorType that we want to use and it may have one of these values: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE
- The second parameter specifies the ConcurrencyType and is equivalent to the ‘LockType’ constant that we used in VB. It may have one of these values: ResultSet.CONCUR_READ_ONLY, ResultSet.CONCUR_UPDATABLE
Now, we initialize and populate the ‘ResultSet’ by calling the ‘Statement’ object’s ‘executeQuery’ method to select all the records (rows) of the table ‘Table1’ through a ‘Select’ SQL query.
Then we move on to the 2nd record by calling the ‘absolute’ method of the ‘ResultSet’ object.
Finally, we display the value of the column named ‘Name’ of the second record.
Please note that we have used the ‘getString’ method here to retrieve the value of the ‘Name’ column. But suppose if the column type is of ‘Integer’ in the database, then we won’t use the ‘getString’ method. Then, we would retrieve the value as follows:
System.out.print(res.getInt("Name"));
‘RecordSet’ provides many such functions to retrieve the data of all types. Some of them are: getString, getInt, getLong, getFloat, getDouble etc.
