Skip to content

README.md

Latest commit

 

History

History
263 lines (184 loc) · 12.4 KB

README.md

File metadata and controls

263 lines (184 loc) · 12.4 KB

Note: This file is written in Markdown and is best viewed with a Markdown viewer (e.g., GitHub, GitLab, VS Code, or a dedicated Markdown reader). Viewing it in a plain text editor may not render the formatting as intended.

Copyright (c) 2026 Software Tree

JDX_StoredProceduresExample

Overview

This project demonstrates how JDX ORM can invoke database stored procedures and receive their results in an object-oriented way using the jdxHandle.storedProc() API.

Stored procedures are defined in the database independently of JDX ORM. JDX provides a simple mechanism to call them and map their output — whether result sets, scalar values, or update counts — back to Java objects or raw values. The stored procedures and their related tables must be created in the database before running this example. The included StoredProcedures_Script.txt file provides the MySQL DDL statements to do this.

The domain model is a single SimpleAddr (address) class mapped to the SPTest_Address table. Five stored procedures are declared in the ORM mapping file and demonstrated in the application:

Stored Procedure Input Parameters Output
SP_AllAddresses None All SimpleAddr objects
SP_AddressesByState state (String) SimpleAddr objects matching the state
SP_AddressCountByState state (String) Count of addresses in the state (scalar)
SP_ZipUpdate addrId, zip (String) Update count + updated row count
SP_Square number (int) Square of the number (scalar)

Prerequisites

  • Java JDK 8 or higher installed and on the system PATH.
  • JDX ORM SDK installed. Set the environment variable JX_HOME to the SDK's top-level installation directory.
  • MySQL database with the stored procedures and SPTest_Address table already created. Use StoredProcedures_Script.txt to create them (see Getting Started below).

Important: Unlike other JDX examples, the database schema and stored procedures for this example must be created manually before running the application. JDX does not create stored procedures automatically, and forceCreateSchema is set to false in main() for this reason.

Project Structure

JDX_StoredProceduresExample/
├── config/
│   └── storedprocedures_example.jdx   # ORM mapping specification file
├── src/
│   └── com/softwaretree/jdxstoredproceduresexample/
│       ├── StoredProceduresExample.java  # Main application entry point
│       └── model/
│           └── SimpleAddr.java           # Address model class
├── bin/                                  # Compiled .class files (generated)
├── sources.txt                           # List of Java source files for compilation
├── StoredProcedures_Script.txt           # MySQL DDL to create the table and stored procedures
├── Overview.txt                          # High-level overview of using stored procedures with JDX ORM
├── compile.cmd                           # Windows: compile the Java source files
├── compile.sh                            # Mac/Linux: compile the Java source files
├── setEnvironment.bat                    # Windows: sets classpath environment variable
├── setEnvironment.sh                     # Mac/Linux: sets classpath environment variable
├── runJDXExample.bat                     # Windows: run the sample application
├── runJDXExample.sh                      # Mac/Linux: run the sample application
├── forward.bat                           # Windows: generate schema from the .jdx mapping file
├── forward.sh                            # Mac/Linux: generate schema from the .jdx mapping file
├── JDXDemo.bat                           # Windows: launch the JDXDemo GUI application
├── JDXDemo.sh                            # Mac/Linux: launch the JDXDemo GUI application
└── README.md                             # This file

Domain Model

SimpleAddr

Field Type DB Column SQL Type Notes
addrId String addrId VARCHAR(20) Primary key
addr1 String addr1 VARCHAR(255)
addr2 String addr2 VARCHAR(255) Nullable
city String city VARCHAR(255)
state String state VARCHAR(20)
zip String zip VARCHAR(10)
country String country VARCHAR(255) Nullable

Unlike most other examples, SimpleAddr in this project uses public fields directly (no private fields with getters/setters), though getters and setters are also present for compatibility.

Key Components

StoredProcedures_Script.txt — Database Setup Script

Contains the MySQL DDL statements to create the SPTest_Address table and the five stored procedures used by this example. Execute this script against your MySQL database before running the application. The stored procedures are written for MySQL 5.7+; adapt them for other databases as needed.

The five stored procedures created are:

  • SP_AllAddresses() — SELECT * FROM SPTest_Address — returns all rows.
  • SP_AddressesByState(IN p_state) — returns rows where state = p_state.
  • SP_AddressCountByState(IN p_state) — returns COUNT(addrId) for the given state.
  • SP_ZipUpdate(IN p_id, IN p_zip) — updates the zip for a given address ID and returns ROW_COUNT().
  • SP_Square(IN p_number) — returns p_number * p_number.

config/storedprocedures_example.jdx — ORM Mapping File

This file maps the SimpleAddr class and declares all five stored procedures. Key elements:

  • JDX_DATABASE and JDBC_DRIVER — pre-configured for MySQL. Update these to match your local setup.
  • JDX_OBJECT_MODEL_PACKAGE — the base Java package for model classes.
  • CLASS .SimpleAddr TABLE SPTest_Address — maps to the SPTest_Address table with PRIMARY_KEY addrId, explicit SQLTYPE for addrId and zip, and NULLABLE for addr2 and country.
  • STORED_PROC SP_AllAddresses / SP_AddressesByState — declared with SP_CLASS .SimpleAddr, telling JDX to map the result set rows back to SimpleAddr objects.
  • STORED_PROC SP_AddressCountByState / SP_ZipUpdate / SP_Square — declared without SP_CLASS; their results are returned as raw scalar values or update counts.

Refer to the JDX Database & JDBC Driver Specification Guide for configuring other databases.

Note: Update JDX_DATABASE and JDBC_DRIVER to match your local database setup before running.

src/.../StoredProceduresExample.java — Main Application

The entry point assumes the database table and stored procedures already exist (forceCreateSchema = false). The application:

  1. Deletes all existing SimpleAddr objects from SPTest_Address.
  2. Inserts 5 SimpleAddr objects using jdxHandle.insert(list, ...).
  3. Queries all objects with a standard jdxHandle.query() call to verify the data.
  4. Invokes stored procedures using jdxHandle.storedProc(name, paramList, ...):
    • SP_AllAddresses — no parameters; returns all SimpleAddr objects.
    • SP_AddressesByState("CA") — returns SimpleAddr objects for California.
    • SP_AddressCountByState("NY") — returns the count of addresses in New York.
    • SP_ZipUpdate("SPTestId1", "12345") — updates the zip for address spTestId1 and returns the row count.
    • SP_Square(5) — returns 25.

Results from each storedProc() call are printed using JXUtilities.printSPResults(). For stored procedures declared with SP_CLASS, results are returned as a List of SimpleAddr objects; for others, results are raw scalar values or counts.

sources.txt — Source File List

Lists all .java source files to be compiled, one per line:

./src/com/softwaretree/jdxstoredproceduresexample/model/SimpleAddr.java
./src/com/softwaretree/jdxstoredproceduresexample/StoredProceduresExample.java

This file is passed to javac using the @sources.txt argument syntax.

compile.cmd / compile.sh — Compilation Scripts

Compiles all Java source files listed in sources.txt and outputs .class files into the bin/ directory.

  • Requires JX_HOME to be set to the JDX ORM SDK installation directory.
  • Links against jxclasses.jar (JDX ORM library) and json-20240303.jar (JSON support).
  • compile.cmd — Windows batch script (supports JDK 8; a commented line supports JDK 9+).
  • compile.sh — Mac/Linux shell script equivalent.

Windows:

compile.cmd

Mac/Linux:

chmod +x compile.sh   # first time only
./compile.sh

setEnvironment.bat / setEnvironment.sh — Environment Setup

Sets the CLASSPATH environment variable to include the JDX ORM libraries and the appropriate JDBC driver JAR. Edit this file to point to the correct JDBC driver for your database before running the application.

  • setEnvironment.bat — Windows (uses ; as classpath separator).
  • setEnvironment.sh — Mac/Linux (uses : as classpath separator; sourced via source ./setEnvironment.sh).

runJDXExample.bat / runJDXExample.sh — Run Script

Invokes the environment setup script to configure the classpath, then runs the StoredProceduresExample main class.

Windows:

runJDXExample.bat

Mac/Linux:

chmod +x runJDXExample.sh   # first time only
./runJDXExample.sh

forward.bat / forward.sh — Schema Generation

Generates the SPTest_Address table schema from the ORM mapping file. Note that this does not create the stored procedures — those must be created separately using StoredProcedures_Script.txt.

Windows:

forward -create

Mac/Linux:

chmod +x forward.sh   # first time only
./forward.sh -create

JDXDemo.bat / JDXDemo.sh — JDXDemo GUI

Launches the JDXDemo desktop GUI application, which provides a graphical way to browse and interact with the SPTest_Address table and invoke the stored procedures using the JDX ORM configuration.

Windows:

JDXDemo.bat

Mac/Linux:

chmod +x JDXDemo.sh   # first time only
./JDXDemo.sh

Getting Started

  1. Set JX_HOME to the root of your JDX ORM SDK installation.

  2. Configure the database by editing config/storedprocedures_example.jdx:

    • Update JDX_DATABASE with the correct MySQL connection URL and credentials.
    • Update JDBC_DRIVER if using a different MySQL driver version.
    • Update setEnvironment.bat (Windows) or setEnvironment.sh (Mac/Linux) to include the JDBC driver JAR on the classpath.

  3. Create the database table and stored procedures by executing the statements in StoredProcedures_Script.txt against your MySQL database using MySQL Workbench, the MySQL CLI, or another database tool.

  4. Compile the source files:

    compile.cmd          # Windows
./compile.sh         # Mac/Linux

  5. Run the sample application:

    runJDXExample.bat    # Windows
./runJDXExample.sh   # Mac/Linux

Mac/Linux tip: Run chmod +x *.sh once in the project directory to make all shell scripts executable.

Importing into Eclipse

This project can be imported directly into the Eclipse IDE as an existing Java project using File → Import → Existing Projects into Workspace.

Additional Resources

  • Overview.txt — a brief conceptual overview of how JDX ORM works with stored procedures (included in this directory).
  • JDX Database & JDBC Driver Specification Guide
  • JDX ORM SDK User Manual — see the Stored Procedures chapter for full details on STORED_PROC, SP_CLASS, parameter passing, and result handling.