Skip to main content
Jump to: navigation, search

Difference between revisions of "Scout/Concepts/Sql Service"

m (Category changed)
(Select into an array holder)
 
(2 intermediate revisions by 2 users not shown)
Line 17: Line 17:
 
* {{ScoutProp|SqlStyle}} provides the class of the {{ScoutLink|Concepts|SqlStyle|Sql Style}} that should be used.
 
* {{ScoutProp|SqlStyle}} provides the class of the {{ScoutLink|Concepts|SqlStyle|Sql Style}} that should be used.
  
 +
 +
 +
===Delegation of the configuration to config.ini===
 +
If the Property {{ScoutProp|JdbcMappingName}} is set (using the {{ScoutLink|SDK|Object Properties View}}), the Java code looks like this:
 +
 +
<source lang="java">
 +
package myapp.server.services.common.sql;
 +
 +
import org.eclipse.scout.rt.services.common.jdbc.AbstractSqlService;
 +
import org.eclipse.scout.service.IService;
 +
 +
public class MySqlService extends AbstractSqlService implements IService {
 +
  @Override
 +
  protected String getConfiguredJdbcMappingName() {
 +
    return "jdbc:derby:C:/MyDB";
 +
  }
 +
  // + other configurations...
 +
}
 +
</source>
 +
 +
The path to the database is hard coded in the code. You might want to delegate this configuration to the <code>config.ini</code> to have more flexibility (e.g one path for development and one path for production). Because the {{ScoutJavadoc|AbstractSqlService|C}} also provides a setter for this property <code>setJdbcMappingName(String)</code>, it is possible to set the property with the <code>config.ini</code> file:
 +
 +
<qualified name of the class>#<setter name without set prefix>=<value>
 +
 +
This pattern works in Eclipse Scout for all classes extending the {{ScoutJavadoc|AbstractService|C}}
 +
 +
For this example:
 +
 +
  myapp.server.services.common.sql.MySqlService#JdbcMappingName=jdbc:derby:C:/MyDB
  
 
==SQL convenience class==
 
==SQL convenience class==
 
Scout proposes a convenience singleton class to access the default SqlService: {{ScoutJavadoc|SQL|C}}
 
Scout proposes a convenience singleton class to access the default SqlService: {{ScoutJavadoc|SQL|C}}
  
Here a simple example:
+
Here a simple select statement:
 +
<source lang="java">
 +
    Object[][] s = SQL.select("" +
 +
        "SELECT COMPANY_NR," +
 +
        "      SHORT_NAME," +
 +
        "      NAME" +
 +
        " FROM  COMPANY");
 +
</source>
 +
 
 +
This update example uses binds. The data are read from the formData variable. <code>":firstName"</code> will access the value in <code>formData.getFirstName()</code> or <code>formData.getFirstName().getValue()</code>
 
<source lang="java">
 
<source lang="java">
 
     SQL.update(
 
     SQL.update(
Line 31: Line 69:
 
         );
 
         );
 
</source>
 
</source>
 +
 +
== Multiple SQL Services ==
 +
{{Note|TODO|Using 2 services, more information in [http://www.eclipse.org/forums/index.php/mv/msg/369585/902322/#msg_902322 forum thread]}}
 +
 +
== Advanced binding ==
 +
 +
=== Solve conflicts between bindings ===
 +
{{Note|TODO|Add explanations from this [https://www.eclipse.org/forums/index.php/t/803695/ forum thread] (extended to a more common case: for example read from 2 formDatas)}}
 +
 +
 +
=== Select into an array holder ===
 +
Example:
 +
<source lang="java">
 +
    BeanArrayHolder<AnswerBean> holder = new BeanArrayHolder<AnswerBean>(AnswerBean.class);
 +
    SQL.selectInto(" select question_id, name, answer_id " +
 +
        " from answers " +
 +
        " into  :{list.questionNr}, :{list.yourName}, :{list.answerNr}", new NVPair("list", holder));
 +
</source>
 +
 +
{{Note|TODO|Add explanations from this [http://www.eclipse.org/forums/index.php/mv/msg/310526/824186/#msg_824186 forum thread]}}
  
 
== See also ==
 
== See also ==

Latest revision as of 10:36, 28 August 2014


Scout
Wiki Home
Website
DownloadGit
Community
ForumsBlogTwitterG+
Bugzilla
Bugzilla


The SqlService provides access to a database.

  • implements I obj.pngISqlService
  • extends C obj.pngAbstractSqlService

Description

Minimal configuration

To be able to connect to a database, these properties needs to be configured:

  • JdbcDriverName the qualified name of the JDBC driver.
  • JdbcMappingName the JDBC path "jdbc:...".
  • Username the user name to connect to the database.
  • Password the password to connect to the database.
  • SqlStyle provides the class of the Sql Style that should be used.


Delegation of the configuration to config.ini

If the Property JdbcMappingName is set (using the Object Properties View), the Java code looks like this:

package myapp.server.services.common.sql;
 
import org.eclipse.scout.rt.services.common.jdbc.AbstractSqlService;
import org.eclipse.scout.service.IService;
 
public class MySqlService extends AbstractSqlService implements IService {
  @Override
  protected String getConfiguredJdbcMappingName() {
    return "jdbc:derby:C:/MyDB";
  }
  // + other configurations...
}

The path to the database is hard coded in the code. You might want to delegate this configuration to the config.ini to have more flexibility (e.g one path for development and one path for production). Because the C obj.pngAbstractSqlService also provides a setter for this property setJdbcMappingName(String), it is possible to set the property with the config.ini file:

<qualified name of the class>#<setter name without set prefix>=<value>

This pattern works in Eclipse Scout for all classes extending the C obj.pngAbstractService

For this example:

 myapp.server.services.common.sql.MySqlService#JdbcMappingName=jdbc:derby:C:/MyDB

SQL convenience class

Scout proposes a convenience singleton class to access the default SqlService: C obj.pngSQL

Here a simple select statement:

    Object[][] s = SQL.select("" +
        "SELECT COMPANY_NR," +
        "       SHORT_NAME," +
        "       NAME" +
        " FROM  COMPANY");

This update example uses binds. The data are read from the formData variable. ":firstName" will access the value in formData.getFirstName() or formData.getFirstName().getValue()

    SQL.update(
        " update      actor " +
        " set         first_name = :firstName," +
        "             last_name = :lastName " +
        " where       actor_id = :id ",
        formData
        );

Multiple SQL Services

Advanced binding

Solve conflicts between bindings

Select into an array holder

Example:

    BeanArrayHolder<AnswerBean> holder = new BeanArrayHolder<AnswerBean>(AnswerBean.class);
    SQL.selectInto(" select question_id, name, answer_id " +
        " from answers " +
        " into  :{list.questionNr}, :{list.yourName}, :{list.answerNr}", new NVPair("list", holder));


See also

Back to the top