Data Transfer from Access to ERP: Java Implementation

This Java class, named InsertHRAccess, appears to be responsible for loading data from a database (presumably Microsoft Access) and performing some data processing and transformation before sending the data to an ERP (Enterprise Resource Planning) system using a web service. Let’s break down the key components and functionalities of the class:

Imports: The class imports various Java libraries and classes to handle database connections, IO operations, character encoding, and other functionalities.
Main Method: The main method serves as the entry point for the application. It primarily does the following:

Calls the loadConfig method to load configuration properties from a config.properties file.
Invokes the getMaxNo method to fetch the highest record number from the ERP database through a Web Service.
If the maximum record number is greater than 0, it calls the getDataToERP method to fetch data from the database and send it to the ERP system.

Utility Methods:

isUTF8 checks if a given string is encoded in UTF-8.
toUTF8 converts a string to UTF-8 encoding.
loadConfig reads configuration properties from a file named config.properties and assigns the value of access_path from it. If the file doesn’t exist, it creates a default properties file.
createConfigProperties creates the default config.properties file with an initial access_path value.

Database Interaction:

getDataToERP method establishes a database connection using JDBC, fetches records from a table named RecordTable where the record number (no) is greater than a specified value (maxno), and for each record, it converts character encodings if needed and then invokes the addToERPByWebService method to send the data to the ERP system.
addToERPByWebService method uses a RunProcess object (which likely handles the interaction with the ERP system) to add the retrieved data to the ERP system.

Web Service Interaction:

The RunProcess class (which is not provided in the code) is presumably a custom class responsible for interacting with the ERP system’s web service. It appears to handle adding data to the ERP system.

Overall, this class seems to be part of an integration process between a local database and an ERP system. It reads data from a database table, performs some encoding conversions, and uses a custom RunProcess class to communicate with the ERP system’s web service for data insertion. It also has functionality to manage configuration properties using a properties file.

package tw.topgiga.hr;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.Properties;
import tw.topgiga.webservice.RunProcess;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class InsertHRAccess {

	private static String access_path;

	public static void main(String[] args) {
		try {
			System.out.println("loading configuration.");

			loadConfig();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		// testDB();
		int maxno = getMaxNo();
		if (maxno > 0) {
			getDataToERP(maxno);
		}
	}

	public static boolean isUTF8(String input) {
		Charset utf8Charset = StandardCharsets.UTF_8;
		byte[] bytes = input.getBytes(utf8Charset);
		String converted = new String(bytes, utf8Charset);
		return input.equals(converted);
	}

	public static String toUTF8(String input) {
		// Convert to UTF-8 bytes
		byte[] utf8Bytes = input.getBytes(StandardCharsets.UTF_8);

		// Convert bytes back to string
		return new String(utf8Bytes, StandardCharsets.UTF_8);
	}

	private static void getDataToERP(int maxno) {
		Connection connection = null;
		String dbPath = "jdbc:ucanaccess:" + access_path;

		try {
			connection = DriverManager.getConnection(dbPath);

			String query = "SELECT * FROM RecordTable WHERE [no] > ?";

			PreparedStatement preparedStatement = connection.prepareStatement(query);

			// Set the parameter value
			preparedStatement.setInt(1, maxno);

			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				int id = resultSet.getInt("no");
				String cardnumber = resultSet.getString("cardnumber");
				String UserName = resultSet.getString("UserName");
				String userworkno = resultSet.getString("userworkno");
				String Location = resultSet.getString("Location");
				String Message = resultSet.getString("Message");
				Timestamp actiontime = resultSet.getTimestamp("ActionTime");
				System.out.println(id + " " + cardnumber + " " + UserName + "" + actiontime);
				addToERPByWebService(id, cardnumber, UserName, userworkno, Location, Message, actiontime);
			}

			resultSet.close();
			preparedStatement.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private static void addToERPByWebService(int id, String cardnumber, String userName, String userworkno,
			String location, String message, Timestamp actiontime) {
		RunProcess process = new RunProcess("HRAccessAddData");

		if (!isUTF8(userName))
			userName = toUTF8(userName);
		if (!isUTF8(userworkno))
			userworkno = toUTF8(userworkno);
		if (!isUTF8(location))
			location = toUTF8(location);
		if (!isUTF8(message))
			message = toUTF8(message);

		process.addField("SeqNo", id);
		process.addField("CardNumber", cardnumber);
		process.addField("UserName", userName);
		process.addField("userworkno", userworkno);
		process.addField("Location", location);
		process.addField("Message", message);
		process.addField("actiontime", actiontime);
		process.performed();

	}

	private static int getMaxNo() {
		RunProcess process = new RunProcess("HRAccessMaxNo");
		String rtn = process.performed();

		return Integer.valueOf(rtn);
	}

	private static void loadConfig() throws ParseException {
		try {
			InputStream input = new FileInputStream("config.properties");
			Properties prop = new Properties();
			prop.load(input);
			access_path = prop.getProperty("access_path");

		} catch (IOException ex) {

			ex.getMessage();
			System.out.println("<" + ex.getMessage() + ">");
			if (ex.getMessage().equalsIgnoreCase("config.properties (No such file or directory)")) {
				System.out.println("Create properties");
				try {
					createConfigProerites();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			ex.printStackTrace();
		}

	}

	private static void createConfigProerites() throws IOException {
		FileOutputStream output = new FileOutputStream("config.properties");
		Properties prop = new Properties();
		prop.setProperty("access_path", "db1.mdb");
		prop.setProperty("AD_Client_ID", "1000000");
		prop.setProperty("AD_Org_ID", "1000000");
		prop.setProperty("AD_Role_ID", "1000000");
		prop.setProperty("M_Warehouse_ID", "1000000");
		prop.setProperty("User", "User");
		prop.setProperty("Password", "Password");
		prop.setProperty("URI", "https://test.idempiere.org");
		prop.store(output, "HR Access Properties");
		System.out.println("I have created a sample configuration properties file for you.");
	}

}

English Version

Imports: The class imports various Java libraries and classes to handle database connections, IO operations, character encoding, and other functionalities.
Main Method: The main method serves as the entry point for the application. It primarily does the following:

Calls the loadConfig method to load configuration properties from a config.properties file.
Invokes the getMaxNo method to fetch the highest record number from the ERP database through a Web Service.
If the maximum record number is greater than 0, it calls the getDataToERP method to fetch data from the database and send it to the ERP system.

Utility Methods:

isUTF8 checks if a given string is encoded in UTF-8.
toUTF8 converts a string to UTF-8 encoding.
loadConfig reads configuration properties from a file named config.properties and assigns the value of access_path from it. If the file doesn’t exist, it creates a default properties file.
createConfigProperties creates the default config.properties file with an initial access_path value.

Database Interaction:

getDataToERP method establishes a database connection using JDBC, fetches records from a table named RecordTable where the record number (no) is greater than a specified value (maxno), and for each record, it converts character encodings if needed and then invokes the addToERPByWebService method to send the data to the ERP system.
addToERPByWebService method uses a RunProcess object (which likely handles the interaction with the ERP system) to add the retrieved data to the ERP system.

Web Service Interaction:

The RunProcess class (which is not provided in the code) is presumably a custom class responsible for interacting with the ERP system’s web service. It appears to handle adding data to the ERP system.

package tw.topgiga.hr;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.Properties;
import tw.topgiga.webservice.RunProcess;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class InsertHRAccess {

	private static String access_path;

	public static void main(String[] args) {
		try {
			System.out.println("loading configuration.");

			loadConfig();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		// testDB();
		int maxno = getMaxNo();
		if (maxno > 0) {
			getDataToERP(maxno);
		}
	}

	public static boolean isUTF8(String input) {
		Charset utf8Charset = StandardCharsets.UTF_8;
		byte[] bytes = input.getBytes(utf8Charset);
		String converted = new String(bytes, utf8Charset);
		return input.equals(converted);
	}

	public static String toUTF8(String input) {
		// Convert to UTF-8 bytes
		byte[] utf8Bytes = input.getBytes(StandardCharsets.UTF_8);

		// Convert bytes back to string
		return new String(utf8Bytes, StandardCharsets.UTF_8);
	}

	private static void getDataToERP(int maxno) {
		Connection connection = null;
		String dbPath = "jdbc:ucanaccess:" + access_path;

		try {
			connection = DriverManager.getConnection(dbPath);

			String query = "SELECT * FROM RecordTable WHERE [no] > ?";

			PreparedStatement preparedStatement = connection.prepareStatement(query);

			// Set the parameter value
			preparedStatement.setInt(1, maxno);

			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				int id = resultSet.getInt("no");
				String cardnumber = resultSet.getString("cardnumber");
				String UserName = resultSet.getString("UserName");
				String userworkno = resultSet.getString("userworkno");
				String Location = resultSet.getString("Location");
				String Message = resultSet.getString("Message");
				Timestamp actiontime = resultSet.getTimestamp("ActionTime");
				System.out.println(id + " " + cardnumber + " " + UserName + "" + actiontime);
				addToERPByWebService(id, cardnumber, UserName, userworkno, Location, Message, actiontime);
			}

			resultSet.close();
			preparedStatement.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private static void addToERPByWebService(int id, String cardnumber, String userName, String userworkno,
			String location, String message, Timestamp actiontime) {
		RunProcess process = new RunProcess("HRAccessAddData");

		if (!isUTF8(userName))
			userName = toUTF8(userName);
		if (!isUTF8(userworkno))
			userworkno = toUTF8(userworkno);
		if (!isUTF8(location))
			location = toUTF8(location);
		if (!isUTF8(message))
			message = toUTF8(message);

		process.addField("SeqNo", id);
		process.addField("CardNumber", cardnumber);
		process.addField("UserName", userName);
		process.addField("userworkno", userworkno);
		process.addField("Location", location);
		process.addField("Message", message);
		process.addField("actiontime", actiontime);
		process.performed();

	}

	private static int getMaxNo() {
		RunProcess process = new RunProcess("HRAccessMaxNo");
		String rtn = process.performed();

		return Integer.valueOf(rtn);
	}

	private static void loadConfig() throws ParseException {
		try {
			InputStream input = new FileInputStream("config.properties");
			Properties prop = new Properties();
			prop.load(input);
			access_path = prop.getProperty("access_path");

		} catch (IOException ex) {

			ex.getMessage();
			System.out.println("<" + ex.getMessage() + ">");
			if (ex.getMessage().equalsIgnoreCase("config.properties (No such file or directory)")) {
				System.out.println("Create properties");
				try {
					createConfigProerites();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			ex.printStackTrace();
		}

	}

	private static void createConfigProerites() throws IOException {
		FileOutputStream output = new FileOutputStream("config.properties");
		Properties prop = new Properties();
		prop.setProperty("access_path", "db1.mdb");
		prop.setProperty("AD_Client_ID", "1000000");
		prop.setProperty("AD_Org_ID", "1000000");
		prop.setProperty("AD_Role_ID", "1000000");
		prop.setProperty("M_Warehouse_ID", "1000000");
		prop.setProperty("User", "User");
		prop.setProperty("Password", "Password");
		prop.setProperty("URI", "https://test.idempiere.org");
		prop.store(output, "HR Access Properties");
		System.out.println("I have created a sample configuration properties file for you.");
	}

}

日本語版

このJavaクラスInsertHRAccessは、データベース（おそらくMicrosoft Access）からデータを読み込み、データの処理と変換を行った後、Webサービスを使用してERP（Enterprise Resource Planning）システムにデータを送信する役割を担っています。このクラスの主要なコンポーネントと機能を分解してみましょう：

インポート：このクラスは、データベース接続、IO操作、文字エンコーディング、その他の機能を処理するために、様々なJavaライブラリとクラスをインポートしています。
Mainメソッド：mainメソッドはアプリケーションのエントリーポイントとして機能します。主に以下の処理を行います：

loadConfigメソッドを呼び出して、config.propertiesファイルから設定プロパティを読み込みます。
getMaxNoメソッドを呼び出して、Webサービスを通じてERPデータベースから最大レコード番号を取得します。
最大レコード番号が0より大きい場合、getDataToERPメソッドを呼び出してデータベースからデータを取得し、ERPシステムに送信します。

ユーティリティメソッド：

isUTF8は、指定された文字列がUTF-8でエンコードされているかどうかを確認します。
toUTF8は、文字列をUTF-8エンコーディングに変換します。
loadConfigは、config.propertiesというファイルから設定プロパティを読み込み、そこからaccess_pathの値を割り当てます。ファイルが存在しない場合は、デフォルトのプロパティファイルを作成します。
createConfigPropertiesは、初期のaccess_path値を持つデフォルトのconfig.propertiesファイルを作成します。

データベースインタラクション：

getDataToERPメソッドは、JDBCを使用してデータベース接続を確立し、レコード番号（no）が指定された値（maxno）より大きいRecordTableテーブルからレコードを取得します。各レコードについて、必要に応じて文字エンコーディングを変換し、addToERPByWebServiceメソッドを呼び出してERPシステムにデータを送信します。
addToERPByWebServiceメソッドは、RunProcessオブジェクト（ERPシステムとのインタラクションを処理すると思われる）を使用して、取得したデータをERPシステムに追加します。

Webサービスインタラクション：

RunProcessクラス（コード中には提供されていない）は、ERPシステムのWebサービスとのインタラクションを担当するカスタムクラスと思われます。ERPシステムへのデータ追加を処理するようです。

全体として、このクラスはローカルデータベースとERPシステム間の統合プロセスの一部のようです。データベーステーブルからデータを読み取り、エンコーディング変換を行い、カスタムのRunProcessクラスを使用してERPシステムのWebサービスと通信してデータを挿入します。また、プロパティファイルを使用して設定プロパティを管理する機能も備えています。

package tw.topgiga.hr;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.Properties;
import tw.topgiga.webservice.RunProcess;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class InsertHRAccess {

	private static String access_path;

	public static void main(String[] args) {
		try {
			System.out.println("loading configuration.");

			loadConfig();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		// testDB();
		int maxno = getMaxNo();
		if (maxno > 0) {
			getDataToERP(maxno);
		}
	}

	public static boolean isUTF8(String input) {
		Charset utf8Charset = StandardCharsets.UTF_8;
		byte[] bytes = input.getBytes(utf8Charset);
		String converted = new String(bytes, utf8Charset);
		return input.equals(converted);
	}

	public static String toUTF8(String input) {
		// UTF-8バイトに変換
		byte[] utf8Bytes = input.getBytes(StandardCharsets.UTF_8);

		// バイトを文字列に戻す
		return new String(utf8Bytes, StandardCharsets.UTF_8);
	}

	private static void getDataToERP(int maxno) {
		Connection connection = null;
		String dbPath = "jdbc:ucanaccess:" + access_path;

		try {
			connection = DriverManager.getConnection(dbPath);

			String query = "SELECT * FROM RecordTable WHERE [no] > ?";

			PreparedStatement preparedStatement = connection.prepareStatement(query);

			// パラメータ値を設定
			preparedStatement.setInt(1, maxno);

			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				int id = resultSet.getInt("no");
				String cardnumber = resultSet.getString("cardnumber");
				String UserName = resultSet.getString("UserName");
				String userworkno = resultSet.getString("userworkno");
				String Location = resultSet.getString("Location");
				String Message = resultSet.getString("Message");
				Timestamp actiontime = resultSet.getTimestamp("ActionTime");
				System.out.println(id + " " + cardnumber + " " + UserName + "" + actiontime);
				addToERPByWebService(id, cardnumber, UserName, userworkno, Location, Message, actiontime);
			}

			resultSet.close();
			preparedStatement.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private static void addToERPByWebService(int id, String cardnumber, String userName, String userworkno,
			String location, String message, Timestamp actiontime) {
		RunProcess process = new RunProcess("HRAccessAddData");

		if (!isUTF8(userName))
			userName = toUTF8(userName);
		if (!isUTF8(userworkno))
			userworkno = toUTF8(userworkno);
		if (!isUTF8(location))
			location = toUTF8(location);
		if (!isUTF8(message))
			message = toUTF8(message);

		process.addField("SeqNo", id);
		process.addField("CardNumber", cardnumber);
		process.addField("UserName", userName);
		process.addField("userworkno", userworkno);
		process.addField("Location", location);
		process.addField("Message", message);
		process.addField("actiontime", actiontime);
		process.performed();

	}

	private static int getMaxNo() {
		RunProcess process = new RunProcess("HRAccessMaxNo");
		String rtn = process.performed();

		return Integer.valueOf(rtn);
	}

	private static void loadConfig() throws ParseException {
		try {
			InputStream input = new FileInputStream("config.properties");
			Properties prop = new Properties();
			prop.load(input);
			access_path = prop.getProperty("access_path");

		} catch (IOException ex) {

			ex.getMessage();
			System.out.println("<" + ex.getMessage() + ">");
			if (ex.getMessage().equalsIgnoreCase("config.properties (No such file or directory)")) {
				System.out.println("Create properties");
				try {
					createConfigProerites();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			ex.printStackTrace();
		}

	}

	private static void createConfigProerites() throws IOException {
		FileOutputStream output = new FileOutputStream("config.properties");
		Properties prop = new Properties();
		prop.setProperty("access_path", "db1.mdb");
		prop.setProperty("AD_Client_ID", "1000000");
		prop.setProperty("AD_Org_ID", "1000000");
		prop.setProperty("AD_Role_ID", "1000000");
		prop.setProperty("M_Warehouse_ID", "1000000");
		prop.setProperty("User", "User");
		prop.setProperty("Password", "Password");
		prop.setProperty("URI", "https://test.idempiere.org");
		prop.store(output, "HR Access Properties");
		System.out.println("I have created a sample configuration properties file for you.");
	}

}

Data Transfer from Access to ERP: Java Implementation

相關文章

iDempiere Technical Guide: How to Add Custom Toolbar Buttons

Integrating REST API with iDempiere 12: A Comprehensive Installation Guide

iDempiere Technical: Achieving Sub-Second Real-Time Updates with OSGi EventAdmin