How to Synchronize Customer Data Between Salesforce and Kintone (Java-Simple JDBC)

In another article, I introduced a method of synchronizing customer data between Salesforce and kintone using a Windows Forms application (C # / ADO.NET) that uses the ** CData driver **. CData drivers are available in a variety of technologies, not just ADO.NET. This time, I would like to introduce a Java example of an application that synchronizes customer data between Salesforce and kintone.

000_構成図.png

The outline of this application is as follows.

JDK 8
JavaFX desktop application
Eclipse 4.6
Load data from Salesforce and kintone
Select the row you want to import and import the data from Salesforce to kintone

Now let's take a look at the development steps for this sample application.

Source code

Click here for the source code of this sample application (https://github.com/kasoorimethi/Salesforce2KintoneJDBC).

CData product installation

Install CData Software. The following two are required this time.

Development environment preparation

Use Eclipse 4.6. Please have the following features installed in Eclipse.

e(fx)clipse - IDE
Use http://download.eclipse.org/efxclipse/updates-released/2.4.0/site for the location of the site.

Project creation

Create a new project. Select JavaFX Project.

001_プロジェクト作成.png

Keep the defaults and click the "Next" button in sequence. In the final dialog, specify "FXML" for "Language", "javax.scene.layout.AnchorPane" for "Root-Type", "MainPane" for "File name", and "MainPaneController" for "Controller Name". Click the Finish button.

002_プロジェクト作成2.png

Implementation of processing

Data model class

Implement the class corresponding to Contact on the Salesforce side and the customer list on the kintone side. This is a simple DTO.

package application;


public class Contact {
	
	private String id;

	private String accountId;

	private String department;

	private String email;

	private String fax;

	private String name;

	private String phone;
	
	private String accountName;
	
	private String accountAddress;
	
	private String accountBillingPostalCode;

	//The following are omitted for each setter/getter
}

package application;


public class customer list{

	private int recordId;

	private String fax_Numbers only_;

	private String tel_Numbers only_;

private String email address;
	
private String record number;

private String company name;

private String address;

private String Contact name;

private String department name;

private String zip code_Numbers only_;

	//The following are omitted for each setter/getter
}

MainPaneController This is the main class of this application. Implement javafx.fxml.Initializable so that it can be initialized. In addition, add a JDBC URL to connect to Salesforce / kintone, and a TableView field to display their data.

The format of the JDBC URL is described in the manual.

public class MainPaneController implements Initializable {

	private String salesforceJdbcUrl = "<Salesforce JDBC URL>";
	
	private String kintoneJdbcUrl = "<kintone JDBC URL>";
	
	@FXML
	private TableView<Contact> salesforceTable;
	
	@FXML
	private TableView<Customer list> kintoneTable;

Data reading

Implement the process to read Salesforce / kintone data into TableView in MainPaneController. It's a simple one using JDBC.

	private void loadSalesforceTableView() {
		
		salesforceTable.getItems().clear();
		
		try (
			Connection conn = DriverManager.getConnection(salesforceJdbcUrl);
			PreparedStatement pstmt = conn.prepareStatement(
				"SELECT c.*, " +
				"a.Name AccountName, a.BillingPostalCode AccountBillingPostalCode, CONCAT(a.BillingState, a.BillingCity, a.BillingStreet) AccountAddress " +
				"FROM Contact c INNER JOIN Account a ON a.Id=c.AccountId");
			ResultSet rs = pstmt.executeQuery();
			) {

			while (rs.next()) {
				Contact c = new Contact();
				c.setId(rs.getString("Id"));
				c.setAccountId(rs.getString("AccountId"));
				c.setDepartment(rs.getString("Department"));
				c.setEmail(rs.getString("Email"));
				c.setFax(rs.getString("Fax"));
				c.setName(rs.getString("Name"));
				c.setPhone(rs.getString("Phone"));
				c.setAccountName(rs.getString("AccountName"));
				c.setAccountAddress(rs.getString("AccountAddress"));
				c.setAccountBillingPostalCode(rs.getString("AccountBillingPostalCode"));
				salesforceTable.getItems().add(c);
			}
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

	private void loadKintoneTableView() {
	
		kintoneTable.getItems().clear();
		
		try (
			Connection conn = DriverManager.getConnection(kintoneJdbcUrl);
			PreparedStatement pstmt = conn.prepareStatement("SELECT *FROM Customer list");
			ResultSet rs = pstmt.executeQuery();
			) {

			while (rs.next()) {
Customer list c=new customer list();
				c.setRecordId(rs.getInt("RecordId"));
				c.setFax_Numbers only_(rs.getString("Fax(Numbers only)"));
				c.setTel_Numbers only_(rs.getString("Tel(Numbers only)"));
				c.set email address(rs.getString("mail address"));
				c.set record number(rs.getString("Record number"));
				c.set company name(rs.getString("company name"));
				c.set address(rs.getString("Street address"));
				c.set person in charge name(rs.getString("Person in charge"));
				c.set department name(rs.getString("Department name"));
				c.set zip code_Numbers only_(rs.getString("Postal code(Numbers only)"));
				kintoneTable.getItems().add(c);
			}
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

Application initialization

Implement initialize of MainPaneController. The main process is just to call the data reading method.

	@Override
	public void initialize(URL location, ResourceBundle resources) {
		
		salesforceTable.getSelectionModel().setSelectionMode(SelectionMode.MULTIPLE);
		
		loadSalesforceTableView();
		loadKintoneTableView();
	}

Import process

Implement the import process. What we are doing is the data of the row selected in the TableView on the Salesforce side, and simply executing the INSERT / UPDATE statement on the kintone side in JDBC.

	@FXML
	private void importOnClick(MouseEvent event) {
		
		try (Connection conn = DriverManager.getConnection(kintoneJdbcUrl)) {
			
			conn.setAutoCommit(false);
			
			for (Contact contact : salesforceTable.getSelectionModel().getSelectedItems()) {
				
Customer list kintoneItem= kintoneTable.getItems().stream()
					.filter(o -> o.get email address().equals(contact.getEmail()))
					.findFirst()
					.orElse(new customer list());
				
				PreparedStatement pstmt;
				
				if (kintoneItem.getRecordId() == 0) {
					pstmt = conn.prepareStatement("INSERT INTO customer list(mail address,company name,Street address,Person in charge,Department name, [Postal code(Numbers only)], [Tel(Numbers only)], [Fax(Numbers only)]) VALUES(?, ? ,? ,? ,? ,? ,? ,?)");
				} else {
					pstmt = conn.prepareStatement("UPDATE Customer List SET Email Address=?,company name=?,Street address=?,Person in charge=?,Department name=?, [Postal code(Numbers only)]=?, [Tel(Numbers only)]=?, [Fax(Numbers only)]=? WHERE RecordId=?");
					pstmt.setInt(9, kintoneItem.getRecordId());
				}
				
				pstmt.setString(1, contact.getEmail());
				pstmt.setString(2, contact.getAccountName());
				pstmt.setString(3, contact.getAccountAddress());
				pstmt.setString(4, contact.getName());
				pstmt.setString(5, Optional.ofNullable(contact.getDepartment()).orElse(""));
				pstmt.setString(6, Optional.ofNullable(contact.getAccountBillingPostalCode()).orElse("").replace("-", ""));
				pstmt.setString(7, contact.getPhone());
				pstmt.setString(8, contact.getFax());
				
				pstmt.execute();
				pstmt.close();
			}
			
			conn.commit();
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		
		loadKintoneTableView();
	}

Creating a screen

Finally, create the screen. Open application / MainPane.fxml and implement the following code. It maps each property of the data model class to the columns of the table.

<?xml version="1.0" encoding="UTF-8"?>

<?import javafx.scene.control.Button?>
<?import javafx.scene.control.TableColumn?>
<?import javafx.scene.control.TableView?>
<?import javafx.scene.control.cell.PropertyValueFactory?>
<?import javafx.scene.layout.AnchorPane?>

<AnchorPane prefHeight="660.0" prefWidth="1200.0" xmlns="http://javafx.com/javafx/8.0.102" xmlns:fx="http://javafx.com/fxml/1" fx:controller="application.MainPaneController">
   <children>
      <TableView fx:id="salesforceTable" layoutX="14.0" layoutY="53.0" prefHeight="600.0" prefWidth="485.0">
        <columns>
          <TableColumn prefWidth="150.0" text="Person in charge">
            <cellValueFactory><PropertyValueFactory property="name" /></cellValueFactory>
          </TableColumn>
          <TableColumn prefWidth="150.0" text="mail address">
            <cellValueFactory><PropertyValueFactory property="email" /></cellValueFactory>
          </TableColumn>
          <TableColumn prefWidth="150.0" resizable="false" sortable="false" text="company name">
          	<cellValueFactory><PropertyValueFactory property="accountName" /></cellValueFactory>
          </TableColumn>
          <TableColumn prefWidth="150.0" text="Department name">
            <cellValueFactory><PropertyValueFactory property="department" /></cellValueFactory>
          </TableColumn>
          <TableColumn prefWidth="150.0" text="Street address">
            <cellValueFactory><PropertyValueFactory property="accountAddress" /></cellValueFactory>
          </TableColumn>
        </columns>
      </TableView>
      <Button layoutX="542.0" layoutY="263.0" mnemonicParsing="false" onMouseClicked="#importOnClick" prefHeight="35.0" prefWidth="116.0" text="import" />
      <TableView fx:id="kintoneTable" layoutX="696.0" layoutY="53.0" prefHeight="600.0" prefWidth="485.0">
        <columns>
          <TableColumn editable="false" prefWidth="75.0" sortable="false" text="Record number">
            <cellValueFactory><PropertyValueFactory property="Record number" /></cellValueFactory>
          </TableColumn>
          <TableColumn editable="false" prefWidth="150.0" sortable="false" text="Person in charge">
            <cellValueFactory><PropertyValueFactory property="Person in charge" /></cellValueFactory>
          </TableColumn>
          <TableColumn editable="false" prefWidth="150.0" sortable="false" text="mail address">
            <cellValueFactory><PropertyValueFactory property="mail address" /></cellValueFactory>
          </TableColumn>
          <TableColumn editable="false" prefWidth="150.0" sortable="false" text="company name">
            <cellValueFactory><PropertyValueFactory property="company name" /></cellValueFactory>
          </TableColumn>
          <TableColumn editable="false" prefWidth="100.0" sortable="false" text="Department name">
            <cellValueFactory><PropertyValueFactory property="Department name" /></cellValueFactory>
          </TableColumn>
          <TableColumn editable="false" prefWidth="150.0" sortable="false" text="Street address">
            <cellValueFactory><PropertyValueFactory property="Street address" /></cellValueFactory>
          </TableColumn>
        </columns>
      </TableView>
   </children>
</AnchorPane>

Sets the size of the window. Open application / Main.java and resize the Scene.

Scene scene = new Scene(root, 1200, 660);

That's all for the implementation.

Run

Create an execution configuration. Open Run-> Run Configuration, right-click Java Application and select New. Open the Classpath tab, specify two JDBC drivers for CData, and click the Run button.

018_実行構成.png

When executed, a window with Salesforce and kintone data displayed in the table view will be launched.

019_実行直後.png

Select the row you want to migrate data and click the "Import" button, and the selected data will be registered in kintone.

020_移行後.png

021_移行後kintone.png