How to manipulate a MaxDB Database using JDBC – Part 1

The JDBC API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases. By using the JDBC programming interface, Java programmers can request a connection with a database, then send query statements using SQL and receive the results for processing. As Java can run on many different hardware platforms and operating systems, developers can use JDBC to write applications that access data across incompatible database management systems running on varied platforms. MaxDB has JDBC as a part of its software and can be easily downloaded from the MaxDB website.
This weblog discusses important points regarding the manipulation of MaxDB database using the JDBC Interface. This weblog assumes that you are working on a windows platform and you understand Java syntax to a degree, and that you are comfortable compiling and executing Java code.

The below links will be useful to learn more about the JDBC and MaxDB
MaxDB Documentation
JDBC Technology

The main steps that are to be followed when we access the MaxDB database is given below
1. Registering the JDBC Driver.
2. Establishing the connection with MaxDB database.
3. Execute a SQL query.
4. Processing the resultset obtained.

1. Registering the JDBC Driver.

The first important task is to register the MaxDB JDBC driver in the Java program. This can be done by loading the class com.sap.dbtech.jdbc.DriverSapDB. If an exception of the class java.lang.ClassNotFoundException is raised, this indicates that the JDBC driver has not been installed correctly. Install the driver again.

2. Establishing the connection with MaxDB database.

The next thing we must do in order to manipulate data in the database is to establish a connection to the database. This connection, referenced in the Java language as an Object of type java.sql.Connection, is handed out by the DriverManager. Here we specify all the information such as the server name, database name, user name etc to a connection string using which we can establish a connection. Once a connection is established, then we can run query on the same.

import java.io.*;import java.sql.*;public class MaxDBJDBC1 { public static void main(java.lang.String[] args)  { try  {  // This is where we load the driver  Class.forName(“com.sap.dbtech.jdbc.DriverSapDB”);  System.out.println(“The Driver has been loaded”); }  catch (ClassNotFoundException e)  {  System.out.println(“Unable to load Driver Class”);  return; } try  {     // All database access is within a try/catch block. Connect to database,  // specifying particular database, username, and password  // required connection parameters are being added to a connection string  String  Server = “localhost”,Database=”KATHIR”,User=”DBA”,Password=”maxdb”;      // Establishing a connection through DriverManager  Connection con = DriverManager.getConnection (“jdbc:sapdb://” + Server + “/” +  Database, User, Password);     System.out.println(“The Connection was successfully established”);     }      catch (SQLException se)  {       // Inform user of any SQL errors and if connection fails       System.out.println(“Error in establishing the connection”);       System.out.println(se);      }     } }

3. Execute a SQL query.

Once a connection is established, you can manipulate data within the database using SQL query. For this you need to do 2 things:
1. Create a Statement from the connection you have made
2. Executing the statement to get ResultSet
Now lets see how to make a statement and execute the same with the help of the program below.

import java.io.*;import java.sql.*;public class MaxDBJDBC1 { public static void main(java.lang.String[] args)  { try  {  // This is where we load the driver  Class.forName(“com.sap.dbtech.jdbc.DriverSapDB”);  System.out.println(“The Driver has been loaded”); }  catch (ClassNotFoundException e)  {  System.out.println(“Unable to load Driver Class”);  return; } try  {     // All database access is within a try/catch block. Connect to database,  // specifying particular database, username, and password  // required connection parameters  String  Server = “localhost”,Database=”KATHIR”,User=”DBA”,Password=”maxdb”;   // get connection     Connection con = DriverManager.getConnection (“jdbc:sapdb://” + Server + “/” +  Database, User, Password);    // Create and execute an SQL Statement  Statement stmt = con.createStatement(  );  System.out.println(“The Connection was successfully established”);  System.out.println(“The con value is:” + con);  ResultSet rs = null;  rs = stmt.executeQuery(“SELECT * FROM certi”);  System.out.println(“Statement Executed Successfully”);  // Closing the connections and resultset etc.   rs.close(  );  stmt.close(  );  con.close(  );     }      catch (SQLException se)  {       // Inform user of any SQL errors       System.out.println(“Error in establishing the connection”);       System.out.println(se);      }     } }

4. Processing the resultset obtained

The result obtained is verified using the SQL Studio.

Thus we have accessed MaxDB Database and executed a query using the JDBC interfacing technique.

Once a query is executed we can capture the result in a ResultSet. This can be processed to print the result in a pleasant form. The logic is that we are looping through the result set, and for every row obtained the columns of data are printed to the screen. The method getString(int columnNumber) provided in the result set is used to get the data from the result set as as String object, and then we’re just printing it using command System.out.println().Here we have used getString() method to get data but there are many other methods that can be used based on the data type. To know more about them please refer the Interface ResultSet Documenation.

import java.io.*;import java.sql.*;public class MaxDBJDBC1{ public static void main(java.lang.String[] args)  { try  {  // This is where we load the driver  Class.forName(“com.sap.dbtech.jdbc.DriverSapDB”);  System.out.println(“The Driver has been loaded”); }  catch (ClassNotFoundException e)  {  System.out.println(“Unable to load Driver Class”);  return; } try  {     // All database access is within a try/catch block. Connect to database,  // specifying particular database, username, and password  // required connection parameters  String  Server = “localhost”,Database=”KATHIR”,User=”DBA”,Password=”maxdb”;      // get connection  Connection con = DriverManager.getConnection (“jdbc:sapdb://” + Server + “/” +  Database, User, Password);    // Create and execute an SQL Statement  Statement stmt = con.createStatement(  );  System.out.println(“The con value is:” + con);  ResultSet rs = null;  rs =  stmt.executeQuery(“SELECT * FROM certi where empid >= ‘3’”);  // Display the SQL Results  while(rs.next(  ))   {   System.out.println(rs.getString (1) +”\t”+ rs.getString(2)+”\t”+ rs.getString(3));  }   // Make sure our database resources are released  rs.close(  );  stmt.close(  );  con.close(  );      }      catch (SQLException se)  {       // Inform user of any SQL errors       System.out.println(“Error in establishing the connection”);       System.out.println(se);      }     } }

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

How to manipulate a MaxDB Database using JDBC – Part 1

How to manipulate a MaxDB Database using JDBC – Part 1

Assigned Tags