sforce-jdbc

Salesforce JDBC driver allows Java programs to connect to a Salesforce data services using standard, database independent Java code. Is an open source JDBC driver written in Pure Java, and communicates over SOAP/HTTP(S) protocol. The main purpose of the driver is to retrieve (only) data from Salesforce services for data analysis. Primary target platform for the driver usage is Eclipse BIRT engine.

The original Git repository for this driver is here However that version is not compatible with IntelliJ because of a lot of unsupported features:

table names and columns names filtration is not implemented
table name and column names are case sensitive
no metadata provided for queries so IntelliJ just ignores the results returned by the driver

These issues were fixed in the current version in this fork.

Watch the demo video

Supported Salesforce and Java versions

The current version of the driver should be compatible with Salesforce Partner API version 39.0 and higher and Java 8.

Get the driver

Download the driver JAR file:

Read-Only version 1.3.1 : from here
Write/Delete support version 1.4.5 : from here

Supported features

Queries support native SOQL;

 select Id, Account.Name, Owner.id, Owner.Name from Account;
 
 -- * fields reference will expand into up to 100 first fields of the entity for root entity
 select * from Account;

Nested queries are supported;
Write is supported as INSERT/UPDATE statements for version >= 1.4.0

The following functions are supported as part of calculation of new values:
- NOW()
- GETDATE()
For Example:

 INSERT INTO Account(Name, Phone) VALUES 
  ('Account01', '555-123-1111'),
  ('Account02', '555-123-2222');
  
  INSERT INTO Contact(FirstName, LastName, AccountId) 
    SELECT Name, Phone, Id 
      FROM Account
      WHERE Name like 'Account0%';

  UPDATE Contact SET LastName = 'Updated_Now_'+NOW()
      WHERE AccountId IN (
          SELECT ID from Account where Phone = '555-123-1111' AND CreatedDate > {ts '2020-01-01 00:10:12Z'}
      );

DELETE is supported for version >= 1.4.1

DELETE from Opportunity where name like 'Pushed Out Insightor Opp%';

Request caching support on local drive. Caching supports 2 modes: global and session. Global mode means that the cached result will be accessible for all system users for certain JVM session. Session cache mode works for each Salesforce connection session separately. Both modes cache stores request result while JVM still running but no longer than for 1 hour. The cache mode can be enabled with a prefix of SOQL query.

How to use:

Global cache mode:

CACHE GLOBAL SELECT Id, Name FROM Account

Session cache mode

CACHE SESSION SELECT Id, Name FROM Account

Reconnect to other organization at the same host

-- Postgres Notation
CONNECT USER admin@OtherOrg.com IDENTIFIED BY "123456"

-- Oracle Notation
CONNECT admin@OtherOrg.com/123456

-- Postgres Notation to a different host using secure connection (by default)
CONNECT 
    TO ap1.stmpa.stm.salesforce.com
    USER admin@OtherOrg.com IDENTIFIED BY "123456"

-- Postgres Notation to a different host - local host using insecure connection
CONNECT 
    TO http://localhost:6109
    USER admin@OtherOrg.com IDENTIFIED BY "123456"

P.S. You need to use the machine host name in the connection url - not MyDomain org host name.

Limitations

Version < 1.4.0 The driver is only for read-only purposes now. Insert/update/delete functionality is not implemented yet.
Version >= 1.4.0 Limited support of INSERT/UPDATE operations
Version >= 1.4.1 Limited support of DELETE operations

With Maven

Add repositories

<repositories>
    <repository>
        <id>com.ascendix.maven</id>
        <name>Ascendix Maven Repo</name>
        <url>https://github.com/ascendix/mvnrepo/raw/master</url>
    </repository>
    <repository>
        <id>mulesoft-releases</id>
        <name>MuleSoft Releases Repository</name>
        <url>http://repository.mulesoft.org/releases/</url>
        <layout>default</layout>
    </repository>
</repositories>

Add dependency

<dependency>
    <groupId>com.ascendix.salesforce</groupId>
    <artifactId>sf-jdbc-driver</artifactId>
    <version>1.4.0-SNAPSHOT</version>
 </dependency>

How to connect

Driver class name

com.ascendix.jdbc.salesforce.ForceDriver

JDBC URL format

jdbc:ascendix:salesforce://[;propertyName1=propertyValue1[;propertyName2=propertyValue2]...]

There are two ways to connect to Salesforce:

by using user and password;
by using sessionId.

User and password parameters are ignored if sessionId parameter is set.

An example for a connection URL with user and password parameters:

jdbc:ascendix:salesforce://;user=myname@companyorg.com.xre.ci;password=passwordandsecretkey

An example for a connection URL with sessionId parameter:

jdbc:ascendix:salesforce://;sessionId=uniqueIdAssociatedWithTheSession

Configuration Properties

Property	Description
user	Login username.
password	Login password is associated with the specified username. Warning! A password provided should contain your password and secret key joined in one string.
sessionId	Unique ID associated with this session.
loginDomain	Top-level domain for a login request. Default value is login.salesforce.com. Set test.salesforce.com value to use sandbox.
https	Switch to use HTTP protocol instead of HTTPS Default value is true
api	Api version to use. Default value is 50.0. Set test.salesforce.com value to use sandbox.
client	Client Id to use. Default value is empty.
insecurehttps	Allow invalid certificates for SSL.

Configure BIRT Studio to use Salesforce JDBC driver

How to add a JDBC driver
How to set configuration properties for Salesforce JDBC driver.

Birt provides various ways to set parameters for JDBC driver. For example, it can be done with the property binding feature in the data source editor and a report parameter.

See how it's done in [Salesforce JDBC report sample](docs/birt/Salesforce JDBC sample.rptdesign)

Configure IntelliJ to use Salesforce JDBC driver

How to add a JDBC driver
How to set configuration properties for Salesforce JDBC driver.

IntelliJ provides various ways to set parameters for JDBC driver. For example, it can be done with the property binding feature in the data source editor and a report parameter. Example JDBC Url:

jdbc:ascendix:salesforce://dev@Local.org:123456@localorg.localhost.internal.salesforce.com:6109?https=false&api=48.0

Please check what kind of access do you have to your org - HTTP or HTTPS and the API version to use. Here is screenshot about results output and autocomplete support for SOQL queries in IntelliJ:

In case of issues with the WSDL

Steps to update the partners.wsdl

Get and build https://github.com/forcedotcom/wsc
Run command:

java -jar target/force-wsc-50.0.0-uber.jar blt/app/main/core/shared/submodules/wsdl/src/main/wsdl/partner.wsdl sforce-partner.jar
Copy the com.sforce.soap to the driver

SOQL Parser

This project uses a bit modified version of MuleSoft SOQL Parser which also supports quotes around field names. It could be obtained from here: https://github.com/spuliaiev-sfdc/salesforce-soql-parser

Version History

1.4.4

SOQL queries will try to expand the SELECT * from Account syntax for root query entity into up to 100 fields.

1.4.3

Parsing of Date Value into SOQL date for IntelliJ.

Example: {ts '2021-10-21 12:01:02Z'}

1.4.2

SELECT of child relationship command parsing

1.4.1

DELETE command parsing

1.4.0

INSERT/UPDATE command parsing

1.3.1.3

CONNECT command parsing fixes

1.3.1.0

Re-connection to a different host using CONNECT command

1.3.0.1

Insecure HTTPS - disabling the SSL Certificate verification

1.2.6.03

Update force-partner-api to 51.0.0

1.2.6.02

Fields for SubSelects aliases

Returning flat resultset for field

1.2.6.01

Update force-partner-api to 50.0.0

Implement parameters:

loginDomain
client
https
api

Implement missing JDBC methods which are required for JetBrains IntelliJ IDE

spuliaiev-sfdc/salesforce-jdbc