Difference between revisions of "Scout/HowTo/3.7/Write a jdbc connection bundle"

Revision as of 17:53, 19 October 2011

The Scout documentation has been moved to https://eclipsescout.github.io/.

Eclipse Scout is shipped with support for the Apache Derby database. When creating a SQL service in Eclipse Scout, 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 org.eclipse.scout.rt.jdbc.derby provides the JDBC driver for Derby (derby.jar) and derby-specific subclasses of AbstractSqlService and AbstractSqlStyle. JDBC drivers for other database engines are available at the 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 libray directly to the host bundle org.eclipse.scout.rt.server
• 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 AbstractSqlService and AbstractSqlStyle, choose the first option if you don't. Have a look at bundle org.eclipse.scout.jdbc.derby for an example.

Providing JDBC connector in a fragment bundle

In this example we will create a fragment bundle containing the MySQL JDBC connector.

1. Download the current MySql jdbc driver, e.g. mysql-connector-java-5.1.16-bin.jar
2. Create a new fragment project in eclipse: File -> New -> Other... (or hit [Ctrl+N])
3. Choose Plug-in Development -> Fragment Project in the wizard, click 'next'
4. Choose a project name, e.g. com.example.myproject.jdbc.mysql.fragment
5. Untick checkbox 'Create a java project', keep the other settings, click 'next'
6. Fill the 'Properties' section
   1. Set the Version to '5.1.16' (or whatever version of MySql you are using)
   2. Set a name for the fragment, e.g. 'MySql jdbc connector'
   3. Set a Provider (or keep empty)
7. Fill the 'Host Plug-in' section
   1. Set Plug-in ID to org.eclipse.scout.rt.server (this is important!)
   2. Minimum and maximum version can be left empty
8. 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)
9. Create a 'lib' folder in your project: Choose New -> Folder from the context menu of the fragment project
10. Drag and drop the MySql library to the lib folder
11. Open the MANIFEST.MF file (double-click). The manifest editor opens, it has multiple tabs (at the bottom). Switch to the Runtime tab.
12. 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.
13. Switch to the MANIFEST.MF tab in the manifest editor to see the manifest file in plain text. It should look similar to this:

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

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 AbstractMicrosoftSqlService and MicrosoftSqlStyle 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:

Eclipse-RegisterBuddy: org.eclipse.scout.rt.server

1. Create a new class org.eclipse.scout.rt.jdbc.microsoft.AbstractMicrosoftSqlService:

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]";
  }
}

1. Create a new class org.eclipse.scout.rt.jdbc.microsoft.MicrosoftSqlStyle:

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) {
        }
      }
    }
  }

1. Open the manifest editor again. Switch to the 'Runtime' tab. Add the package which contains the two created classes to the exported packages.
2. Open the '*.product' files of your server bundle. Switch to the 'Dependencies' tab and add the created plugin org.eclipse.scout.rt.jdbc.microsoft. (If you do not need the derby sql service you can remove the bundle org.eclipse.scout.rt.jdbc.derby from the dependencies).
3. Switch to the Scout perspective and create a new SqlService: server/Common Services/Sql Services. Use AbstractMicrosoftSqlService as a super type.
4. Override the getConfiguredJdbcMappingName method and replace <servername> and <port>. If username and password are required, override the methods getConfiguredUsername and getConfiguredPassword.