JDBC Connectivity with Webdynpro and Max DB

Hello Friends,

My ultimate aim in this blog is to explain how to use MaxDB in Webdynpro applications, so that your webdynpro application becomes persistent with the data entered. Before starting up I would like to confirm with the pre requisites.

1.) MaxDB installed to the server where you deploy your Webdynpro application.

2.) SAP NWDS installed.

3.) Check for the availability of JDBC Driver in your Visual Administrator.

Step1:

Create a Dictionary Project.

Go to Dictionary Perspective-> New Project.

Enter the Project Name and click finish.

Now expand the sub tree and create a new table by right clicking on the Tables option in the Dictionary Project created. Create table rows with the data type.

Now rebuild the project, create archive. You would find the .sda archive created in the Navigator.

Now deploy your application to the J2EE Engine. Therefore the Table instance is created in the J2EE Engine.

So the Database part is over and we shall create the Webdynpro application to access the table rows.

Step2: Create a Webdynpro Application to Access the Database using Jdbc Connectivity.

Create a Webdynpro application with views and component.

Create a context in the component controller. A Value Node with cardinality 1...n and value attributes as that of the columns which you created in the dictionary table. Bind this node to a form in the view.

Create another context in the component controller with a value node having Cardinality 0..n, bound to a table displaying data from the database.

Use buttons which performs the action to add the data to the database remove data and retrieve data.

In the Event Handler of the button write the following code.

/* Event handler to Insert a new set of data to the Database*/

try

    {

      InitialContext ctx = new InitialContext();

      java.sql.DataSource ds = (java.sql.DataSource)ctx.lookup("jdbc/DataSourceName");

                  Connection con = ds.getConnection();

                  PreparedStatement pstm = con.prepareStatement("insert into TMP_PHONEBOOK(FIRSTNAME,LASTNAME,LOCATION,MOBILENUMBER,LANDLINE,EMAIL) values(?,?,?,?,?,?)");

                  pstm.setString(1,wdContext.currentInputNodeElement().getF_name());

                  pstm.setString(2,wdContext.currentInputNodeElement().getL_name());

                  pstm.setString(3,wdContext.currentInputNodeElement().getLocation());

                  pstm.setLong(4,wdContext.currentInputNodeElement().getMob_number());

                  pstm.setInt(5,wdContext.currentInputNodeElement().getLandline());

                  pstm.setString(6,wdContext.currentInputNodeElement().getEmail());

pstm.executeUpdate();

                  con.close();

            wdContext.nodeInputNode().invalidate();

    }

    catch(Exception e)

    {

      wdComponentAPI.getMessageManager().reportWarning("Warning::"+e.getLocalizedMessage());

    }

/*Event handler to Retrieve Data From The Database*/

wdContext.nodeOutputNode().invalidate();

    try

    {

            InitialContext ctx = new InitialContext();

            java.sql.DataSource ds = (java.sql.DataSource)ctx.lookup("jdbc/DataSourceName");

            Connection con = ds.getConnection();

            Statement stmt = con.createStatement();

            ResultSet rs = stmt.executeQuery("select * from TMP_PHONEBOOK");

            while(rs.next())

            {

                  IPrivatePhoneBookView.IOutputNodeElement ele = wdContext.createOutputNodeElement();

                  ele.setF_name(rs.getString("FIRSTNAME"));

                  ele.setL_name(rs.getString("LASTNAME"));

                  ele.setLocation(rs.getString("LOCATION"));

                  ele.setMob_number(rs.getLong("MOBILENUMBER"));

                  ele.setLandline(rs.getInt("LANDLINE"));

                  ele.setEmail(rs.getString("EMAIL"));

                  wdContext.nodeOutputNode().addElement(ele);

            }

            con.close();

    }

    catch(Exception e)

    {

      wdComponentAPI.getMessageManager().reportWarning("Warning::"+e.getLocalizedMessage());

    }

/*Event handler to Delete a particular data from the Database*/

try    {     

InitialContext ctx = new InitialContext(); 

DataSource ds = (DataSource)ctx.lookup("jdbc/DataSourceName"); 

Connection con = ds.getConnection();  

PreparedStatement stmt = con.prepareStatement("delete from TMP_PHONEBOOK where FIRSTNAME = ?"); 

stmt.setString(1,wdContext.currentContextElement().getDelName());

stmt.executeUpdate();  

 con.close();  

} catch(Exception e)    

{ 

   wdComponentAPI.getMessageManager().reportWarning("Warning::"+e.getLocalizedMessage());

} 

  Now you can rebuild your Project and deploy the application. Similarly you can use other SQL statements for required Operations. 

Please note:

1.)The DataSourceName should be obtained from the Visual Administrator.Login to the Visual Administrator. 

Go to Cluster- Server - Services - JDBC Connectors - DataSources. 

You could see the DataSource Aliases in the table..

2.) The Highlighted text in this blog denotes name of the node,attributes, table etc., which are not generic.