View source for Scout/HowTo/3.7/Write a jdbc connection bundle

{{ScoutPage|cat=HowTo 3.7}}

Eclipse Scout is shipped with support for the [http://db.apache.org/derby/ Apache Derby] database. When creating a {{ScoutLink|Concepts|Sql Service|Sql Service}} (''See the {{ScoutLink|Tutorial|Minicrm/Set_up_SQL_Service|Set up a SQL service step of the minicrm tutorial}}''), you need to define the JDBC driver name as well as the JDBC mapping name. To be able to actually connect to the database you need to include a bundle providing the JDBC driver in your project definition.
Bundle <code>org.eclipse.scout.rt.jdbc.derby</code> provides the JDBC driver for Derby (derby.jar) and derby-specific subclasses of <code>AbstractSqlService</code> and <code>AbstractSqlStyle</code> (the derby specific {{ScoutLink|Concepts|SqlStyle|Sql Style}}).

JDBC drivers for other database engines are available at the [http://marketplace.eclipse.org/content/jdbc-drivers-eclipse-scout Eclipse Marketplace], ready to be included in your Eclipse Scout projects.

It is also possible to create your own JDBC connection bundles (in order to support yet other database engines, or if the specific version of the required driver is not provided by the above sources).

You can
* create a ''fragment bundle'' providing the JDBC connector library directly to the host bundle <code>org.eclipse.scout.rt.server</code>
* create a normal ''bundle'' containing the JDBC connector library as well as additional sources which you like to export to other bundles.

Choose the second option if you need specific implementations of <code>AbstractSqlService</code> and <code>AbstractSqlStyle</code>, choose the first option if you don't. Have a look at bundle <code>org.eclipse.scout.jdbc.derby</code> for an example.

== Providing JDBC Connector in a Fragment Bundle ==
In this example we will create a fragment bundle containing the MySQL JDBC connector.

# Download the current MySql jdbc driver, e.g. mysql-connector-java-5.1.16-bin.jar
# Create a new fragment project in eclipse: File -> New -> Other... (or hit [Ctrl+N])
# Choose Plug-in Development -> Fragment Project in the wizard, click 'next'
# Choose a project name, e.g. com.example.myproject.jdbc.mysql.fragment
# Untick checkbox 'Create a java project', keep the other settings, click 'next'
# Fill the 'Properties' section
## Set the Version to '5.1.16' (or whatever version of MySql you are using)
## Set a name for the fragment, e.g. 'MySql jdbc connector'
## Set a Provider (or keep empty)
# Fill the 'Host Plug-in' section
## Set Plug-in ID to org.eclipse.scout.rt.server (this is important!)
## Minimum and maximum version can be left empty
# Finish the wizard. A new fragment project is created in your workspace (it should contain a folder META-INF with the MANIFEST.MF file and also a build.properties file)
# Create a 'lib' folder in your project: Choose New -> Folder from the context menu of the fragment project
# Drag and drop the MySql library to the lib folder
# Open the MANIFEST.MF file (double-click). The manifest editor opens, it has multiple tabs (at the bottom). Switch to the Runtime tab.
# Add the MySql library to the bundle class path (section 'Classpath' on the right). Use the Add.. button and select the jar file from the lib folder. Save the changes.
# Switch to the MANIFEST.MF tab in the manifest editor to see the manifest file in plain text. It should look similar to this:
<source lang="text">
Manifest-Version: 1.0
Bundle-ManifestVersion: 2
Bundle-Name: Mysql jdbc connector
Bundle-SymbolicName: com.example.myproject.server.jdbc.mysql.fragment
Bundle-Version: 5.1.16
Fragment-Host: org.eclipse.scout.rt.server
Bundle-RequiredExecutionEnvironment: JavaSE-1.6
Bundle-ClassPath: lib/mysql-connector-java-5.1.16-bin.jar
</source>

You can now add the new fragment to the product dependencies:
Open the product configuration file of the server bundle (e.g. com.example.myproject.server -> products -> development, open the .product file)
Add the new fragment in the Dependencies tab. Switch to the Overview tab, click the 'Synchronize' link in the 'Testing' section, then click the link to 'Launch an eclipse application in debug mode'.

== Providing JDBC Connector in a Bundle (With Additional Sources) ==
In this example we will create a bundle containing the SQL driver for Microsoft SQL Server. This bundle will also provide the classes <code>AbstractMicrosoftSqlService</code> and <code>MicrosoftSqlStyle</code> which will be exported and can be used in your Eclipse Scout projects.

* 1. Download the current MS SQL Server JDBC driver (say sqljdbc.jar)
* 2. Create a plugin project: File -> New -> Other... (or hit [Ctrl+N]) lets say 'org.eclipse.scout.rt.jdbc.microsoft'.
* 3. Create the folder 'lib' and put the JDBC driver jar into the folder.
* 4. Open the file 'META-INF/MANIFEST.MF', the manifest editor opens. 
* 5. Switch to the 'Runtime' tab and add the sqljdbc.jar to the 'Classpath' box. 
* 6. Switch to the 'Dependencies' tab and add 'org.eclipse.scout.rt.server' to the 'Required Plug-ins' box.
* 7. Switch to the 'MANIFEST.MF' tab and add the line:
<source lang="text">
Eclipse-RegisterBuddy: org.eclipse.scout.rt.server
</source>
* 8. Create a new class <code>org.eclipse.scout.rt.jdbc.microsoft.AbstractMicrosoftSqlService</code> (the {{ScoutLink|Concepts|Sql Service|sql service}}):

<source lang="java">
public abstract class AbstractMicrosoftSqlService extends AbstractSqlService {

  @Override
  protected Class<? extends ISqlStyle> getConfiguredSqlStyle() {
    return org.eclipse.scout.rt.jdbc.microsoft.MicrosoftSqlStyle.class;
  }

  @Override
  protected String getConfiguredJdbcDriverName() {
    return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  }

  @Override
  protected String getConfiguredJdbcMappingName() {
    return "jdbc:sqlserver://[host][:port];DatabaseName=[database]";
  }
}
</source>
* 9. Create a new class <code>org.eclipse.scout.rt.jdbc.microsoft.MicrosoftSqlStyle</code> (the {{ScoutLink|Concepts|SqlStyle|sql style}}):

<source lang="java">
public class MicrosoftSqlStyle extends AbstractSqlStyle {

  @Override
  public String getConcatOp() {
    return "+";
  }

  @Override
  public String getLikeWildcard() {
    return "%";
  }

  @Override
  protected int getMaxListSize() {
    return 1000;
  }

  @Override
  public boolean isLargeString(String s) {
    return (s.length() > 4000);
  }

  @Override
  public boolean isBlobEnabled() {
    return true;
  }

  @Override
  public boolean isClobEnabled() {
    return true;
  }

  @Override
  public String createDateTimeIsNow(String attribute) {
    return "TRUNC(" + attribute + ", 'MI')=TRUNC(SYSDATE, 'MI')";
  }

  @Override
  public String createDateTimeIsNotNow(String attribute) {
    return "TRUNC(" + attribute + ", 'MI')!=TRUNC(SYSDATE, 'MI')";
  }

  @Override
  public void testConnection(Connection conn) throws SQLException {
    Statement testStatement = null;
      try {
        testStatement = conn.createStatement();
        testStatement.execute("SELECT count(1) FROM dbo.sysobjects");
       }
      finally {
        if (testStatement != null) try {
          testStatement.close();
        }
        catch (Throwable t) {
        }
      }
    }
  }
}
</source>
* 10. Open the manifest editor again. Switch to the 'Runtime' tab. Add the package which contains the two created classes to the exported packages.
* 11. Open the '*.product' files of your server bundle. Switch to the 'Dependencies' tab and add the created plugin <code>org.eclipse.scout.rt.jdbc.microsoft</code>. (If you do not need the derby sql service you can remove the bundle <code>org.eclipse.scout.rt.jdbc.derby</code> from the dependencies).
* 12. Switch to the Scout perspective and create a new <code>SqlService</code>: server/Common Services/Sql Services. Use <code>AbstractMicrosoftSqlService</code> as a super type.
* 13. Override the <code>getConfiguredJdbcMappingName</code> method and replace <servername> and <port>. If username and password are required, override the methods <code>getConfiguredUsername</code> and <code>getConfiguredPassword</code>.

== Bundles, Fragments and Class Loading ==
The above two examples take care to allow the bundle <code>org.eclipse.scout.rt.server</code> to load the JDBC driver class provided by the external jar library. The Eclipse Scout server bundle explicitly loads the JDBC driver in class <code>SqlConnectionBuilder</code> through a method call to <code>Class.forName(String)</code>. This call will fail if the JDBC driver class is not available in the bundle classpath.
The first example works because a fragment's bundle classpath is directly added to the host bundle. The second example works because of the equinox-specific ''buddy classloading'' mechanism. for more information about buddy classloading, see: [[Context Class Loader Enhancements]].