Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "Scout/HowTo/3.7/Write a jdbc connection bundle"
(from: http://www.eclipse.org/forums/index.php/mv/msg/246503/738769/#msg_738769) |
|||
Line 1: | Line 1: | ||
{{ScoutPage|cat=HowTo}} | {{ScoutPage|cat=HowTo}} | ||
− | + | Eclipse Scout is shipped with support for the [http://db.apache.org/derby/ Apache Derby] database. When creating a [http://wiki.eclipse.org/Scout/Tutorial/Minicrm/Set_up_SQL_Service 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 <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>. | ||
+ | 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 libray 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 | |
− | Switch to the ' | + | # 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: | |
− | Switch to the ' | + | 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. | |
+ | # Download the current MS SQL Server JDBC driver (say sqljdbc.jar) | ||
+ | # Create a plugin project: File -> New -> Other... (or hit [Ctrl+N]) lets say 'org.eclipse.scout.rt.jdbc.microsoft'. | ||
+ | # Create the folder 'lib' and put the JDBC driver jar into the folder. | ||
+ | # Open the file 'META-INF/MANIFEST.MF', the manifest editor opens. | ||
+ | # Switch to the 'Runtime' tab and add the sqljdbc.jar to the 'Classpath' box. | ||
+ | # Switch to the 'Dependencies' tab and add 'org.eclipse.scout.rt.server' to the 'Required Plug-ins' box. | ||
+ | # Switch to the 'MANIFEST.MF' tab and add the line: | ||
+ | <source lang="text"> | ||
+ | Eclipse-RegisterBuddy: org.eclipse.scout.rt.server | ||
+ | </source> | ||
+ | # Create a new class <code>org.eclipse.scout.rt.jdbc.microsoft.AbstractMicrosoftSqlService</code>: | ||
<source lang="java"> | <source lang="java"> | ||
public abstract class AbstractMicrosoftSqlService extends AbstractSqlService { | public abstract class AbstractMicrosoftSqlService extends AbstractSqlService { | ||
− | + | ||
+ | @Override | ||
+ | protected Class<? extends ISqlStyle> getConfiguredSqlStyle() { | ||
+ | return org.eclipse.scout.rt.jdbc.microsoft.MicrosoftSqlStyle.class; | ||
+ | } | ||
+ | |||
@Override | @Override | ||
protected String getConfiguredJdbcDriverName() { | protected String getConfiguredJdbcDriverName() { | ||
− | return "com.microsoft | + | return "com.microsoft.sqlserver.jdbc.SQLServerDriver"; |
} | } | ||
@Override | @Override | ||
protected String getConfiguredJdbcMappingName() { | protected String getConfiguredJdbcMappingName() { | ||
− | return "jdbc | + | return "jdbc:sqlserver://[host][:port];DatabaseName=[database]"; |
} | } | ||
} | } | ||
</source> | </source> | ||
+ | # Create a new class <code>org.eclipse.scout.rt.jdbc.microsoft.MicrosoftSqlStyle</code>: | ||
+ | <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> | ||
+ | # Open the manifest editor again. Switch to the 'Runtime' tab. Add the package which contains the two created classes to the exported packages. | ||
+ | # 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). | ||
+ | # 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. | ||
+ | # 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>. |
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.
- 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:
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.
- Download the current MS SQL Server JDBC driver (say sqljdbc.jar)
- Create a plugin project: File -> New -> Other... (or hit [Ctrl+N]) lets say 'org.eclipse.scout.rt.jdbc.microsoft'.
- Create the folder 'lib' and put the JDBC driver jar into the folder.
- Open the file 'META-INF/MANIFEST.MF', the manifest editor opens.
- Switch to the 'Runtime' tab and add the sqljdbc.jar to the 'Classpath' box.
- Switch to the 'Dependencies' tab and add 'org.eclipse.scout.rt.server' to the 'Required Plug-ins' box.
- Switch to the 'MANIFEST.MF' tab and add the line:
Eclipse-RegisterBuddy: org.eclipse.scout.rt.server
- 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]"; } }
- 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) { } } } }
- Open the manifest editor again. Switch to the 'Runtime' tab. Add the package which contains the two created classes to the exported packages.
- 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 bundleorg.eclipse.scout.rt.jdbc.derby
from the dependencies). - Switch to the Scout perspective and create a new
SqlService
: server/Common Services/Sql Services. UseAbstractMicrosoftSqlService
as a super type. - Override the
getConfiguredJdbcMappingName
method and replace <servername> and <port>. If username and password are required, override the methodsgetConfiguredUsername
andgetConfiguredPassword
.