This is another post  of our Netweaver Mobile 7.1 series where we share best practices and tips and tricks for development of mobile applications. The available blogs in this series are

Stefan Henke reported in his last What you ever wanted to know about pda development in mobile 7.1 (part 2) howto connect to the database of Netweaver Mobile (minDB or db2e) using DBVisualizer. Sometimes it is also helpful to connect to the database from a standalone Java application using plain JDBC, e.g. for advanced or repeated tasks. So I will give you the sample code to do this. By reading this blog you can execute your first JDBC query within five minutes and avoid common pitfalls.

Installation

The code is packaged up as simple Eclipse project. Extract the attached zip file, open Eclipse or the Netweaver Developer Studio and import it using File -> Import -> Existing Project into workspace. You have to copy the JDBC driver to the lib directory and add it to the classpath (right click on the jar file select "Build Path" and "Add To Build Path"). You can get the driver from you Netweaver Mobile MI/lib folder. Afterwards you have to edit the jdbc.properties to define the connection settings of your database. Here you have to select whether you are using minDB or db2e (just uncomment the appropriate lines for your db) and maintain the path to your database in the jdbc.url in line. E.g. if you are using minDB and the MI folder of your Netweaver Mobile installation is located at C:NetweaverMobileMI, the file should look something like this:

jdbc.user=dba

jdbc.password=dba

jdbc.driver=com.sap.sdb.minDB.DriverEmbeddedMinDB

jdbc.url=jdbc:embeddedMinDB://?minDB=C:/NetweaverMobile/MI/data

Afterwards you can run the demo query from the DemoQuery class, e.g. launch it by executing "Netweaver Mobile JDBC demo" from the launch configuration menu. The query will print the names of the installed Development Components, so make sure you have already deployed some applications to your client. The output will look something like this:

Currently installed DCs:

demo.sap.com~some_sample_app~implementation

If the query is working you can start to write your own code.

Caution

Before you access the client via JDBC please make a backup of your data, you could destroy it. Do not modify any system tables of Netweaver Mobile, the client may stop working properly after doing this. Make sure the client is not running before you try to access its database. Never ever try to use JDBC code from within your current mobile application to access the running client.

To access a minDB the client has to be started at least one time, since the database is created on the first startup.

Sample Code

Below you can see the classes and the jdbc.properties used for this sample. Just create an Eclipse project and paste the content into the appropriate classes. I had to post the code this way, since I couldn't upload the code as zip file. If you want to have the code as zip, please request it by mail. Sorry for this inconvenience.

package com.sap.mobile.jdbcsample;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Sample code which shows howto query the database of Netweaver Mobile with plain JDBC.
 * The connection settings are maintained in /src/main/resources/jdbc.properties
 */
public class DemoQuery {

 private final Connection connection;

 public DemoQuery(Connection connection) {
  this.connection = connection;
 }

 public static void main(String[] args) throws Exception {
  //init and create the database connection, maintain the connection settings in jdbc.properties
  JdbcConnectionManager jdbcConnectionManager = new JdbcConnectionManager(
    new DatabaseProperties());
  Connection connection = jdbcConnectionManager.createConnection();
  
  
  new DemoQuery(connection).run();
 }

 private void run() throws SQLException, IOException {
  
  Statement st1 = connection.createStatement();
  //hint: the easiest way to lookup the table names is using a graphical tool like db visualizer
  st1.execute("SELECT DO_NAME FROM CFS_APPLICATION");
  ResultSet rs = st1.getResultSet();
  System.out.println("Currently installed DCs:");
  while (rs.next()){
   System.out.println(rs.getString("DO_NAME"));
  }
 }

}
 

package com.sap.mobile.jdbcsample;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

/**
 * Stores database connections settings. The settings are loaded by default from
 * the jdbc.properties file which is read from the classpath and imports the
 * connection settings
 * 
 * @see JdbcConnectionManager
 */
public class DatabaseProperties {

 public static final String JDBC_PROPERTY_USER = "user";
 public static final String JDBC_PROPERTY_PASSWORD = "password";

 private String username;
 private String password;
 private String jdbcUrl;
 private String jdbcDriver;
 private Properties connectionProperties;

 /**
  * Loads the connection settings from /jdbc.properties classpath resource.
  * 
  * @throws IOException
  */
 public DatabaseProperties() throws IOException {
  Properties jdbcProperties = loadPropertiesFromClassPath("/jdbc.properties");
  importSettings(jdbcProperties);
 }

 /**
  * Loads the connection setting from a given {@link Properties} object.
  * 
  * @param connectionProperties
  * @throws IOException
  */
 public DatabaseProperties(Properties connectionProperties)
   throws IOException {
  importSettings(connectionProperties);
 }

 private Properties loadPropertiesFromClassPath(String jdbcPropResource) throws IOException {
  Properties p = new Properties();
  InputStream jdbcPropsIS = getClass().getResourceAsStream(
    jdbcPropResource);
  if (jdbcPropsIS == null) {
   throw new IllegalStateException(
     "Could not load " + jdbcPropResource + " from classpath because it could bot be found. " +
     "Verify " + jdbcPropResource + " is present on the root classpath.");
  }
  p.load(jdbcPropsIS);
  jdbcPropsIS.close();
  return p;
 }

 private void importSettings(Properties p) throws IOException {
  username = p.getProperty("jdbc.user", "");
  password = p.getProperty("jdbc.password", "");
  jdbcUrl = p.getProperty("jdbc.url");
  jdbcDriver = p.getProperty("jdbc.driver");
  connectionProperties = new Properties();
  connectionProperties.setProperty(JDBC_PROPERTY_USER, getUsername());
  connectionProperties.setProperty(JDBC_PROPERTY_PASSWORD, getPassword());
 }

 public String getJdbcUsername() {
  return username;
 }

 public String getJdbcUrl() {
  return jdbcUrl;
 }

 public String getJdbcDriver() {
  return jdbcDriver;
 }

 public String getUsername() {
  return username;
 }

 public String getPassword() {
  return password;
 }

 public Properties getConnectionProperties() {
  return connectionProperties;
 }

 public String toString() {
  return username + "@" + jdbcUrl + ", driver: " + jdbcDriver;
 }
}

package com.sap.mobile.jdbcsample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Logger;

/**
 * Loads the JDBC driver and allows to create JDBC {@link Connection}s.
 * The connection and JDBC settings are loaded using {@link DatabaseProperties}.
 */
public class JdbcConnectionManager {

 private final static Logger logger = Logger.getLogger(JdbcConnectionManager.class .getName());
 
 private final DatabaseProperties dbProps;

 public JdbcConnectionManager(DatabaseProperties dbProps) throws Exception {
  this.dbProps = dbProps;
  try{
   Class jdbcDriverClass = Class.forName(dbProps.getJdbcDriver());
   jdbcDriverClass.newInstance();
  }catch (ClassNotFoundException e) {
   throw new RuntimeException("Couldnt find the JDBC driver class "" + dbProps.getJdbcDriver() 
    + "". Please ensure the jdbc driver is on the classpath and configured properly in jdbc.properties.", e);
  }
  logger.info("Loaded the " + dbProps.getJdbcDriver() + " JDBC driver.");
    }
 
 public Connection createConnection() throws SQLException{
        Connection connection = DriverManager.getConnection(dbProps.getJdbcUrl(), dbProps.getConnectionProperties());
        connection.setAutoCommit(false);
        return connection;
 }
}

jdbc.properties (just put it into the root classpath)

jdbc.user=dba
jdbc.password=dba

######
#db2e#
######
#  

#ensure the project references the db2e jdbc driver db2ejdbc.jar to use minDB
#the native db2e DLLs (DB2e.dll, DB2eJDBC.dll and CryptoPlugin.dll are required to be in the 
#java library path, e.g. copy them to your windows folder else a UnsatisfiedLinkError or 
#ClassNotFoundException may be thrown
#It is also possible just to go to the context menu of db2ejdbc.jar and enter the full path to your 
#Netweaver Clients MI/bin folder in the Native Library tab

jdbc.driver = com.ibm.db2e.jdbc.DB2eDriver
#caution: the trailing "/" is important, dont forget it
jdbc.url=jdbc:db2e:C:/NetweaverMobile/MI/data/

#######
#mindb#
#######
#ensure the project references the minDB jdbc driver minDBembedded.jar to use minDB

#jdbc.driver=com.sap.sdb.minDB.DriverEmbeddedMinDB
#jdbc.url=jdbc:embeddedMinDB://?minDB=C:/NetweaverMobile/MI/data