Smile CDR v2024.08.PRE
On this page:

39.4.1Java Execution Environment Examples

 

Examples on this page may be helpful when building your own Hybrid Provider Classes, Interceptor Classes, etc.

39.4.2Database Access using JDBC

 

If you need to read from a relational database, it is recommended to use the commons-jdbc2 and spring-jdbc libraries to ensure efficient and safe use of database connection resources.

The following example is intended to be created as a Spring Bean. If you are manually constructing it (e.g. from an interceptor) you will need to manually call the start() and stop() methods.

/**
 * This is an example of a Bean class that connects to a database
 * and queries it for data. The {@link DatabaseAccess#start()} method
 * must be called before it can be used, and the
 * {@link DatabaseAccess#findPatientsByFamilyName(String, int, int)}
 * method queries the DB.
 */
public class DatabaseAccess {

   /** An SLF4j logger, which logs to the system log */
   private static final Logger ourLog = LoggerFactory.getLogger(DatabaseAccess.class);

   private ProxyDataSource myDataSource;
   private JdbcTemplate myJdbcTemplate;

   /**
    * This method is called once on initialization, so it will be used to
    * set up our database connection pool.
    */
   @PostConstruct
   public void start() {
      BasicDataSource pooledDataSource = new BasicDataSource();

      // Here we're pulling the database URL and credentials from
      // system properties, which are set on the command line when
      // Smile CDR is started.
      pooledDataSource.setUrl(System.getProperty("example_db.url"));
      pooledDataSource.setUsername(System.getProperty("example_db.username"));
      pooledDataSource.setPassword(System.getProperty("example_db.password"));

      /*
       * All the settings below are optional, but many of them are useful
       * for real production deployments. Consider these values carefully
       * for your use case.
       */

      // How many connections should the pool keep open when none are
      // currently needed or in use?
      pooledDataSource.setMaxIdle(2);

      // What is the maximum number of connections the pool should grow
      // to when multiple threads are requesting connections?
      pooledDataSource.setMaxTotal(10);

      // How long should the pool wait before throwing an error if no
      // connections are available, i.e. because MaxTotal has already
      // been reached?
      pooledDataSource.setMaxWait(Duration.ofSeconds(30));

      // How long should an executing SQL statement wait before timing out
      // with an error?
      pooledDataSource.setDefaultQueryTimeout(Duration.ofSeconds(60));

      /*
       * Here, we wrap the datasource in a ttddyy-datasource-proxy
       * instance. This is optional, but can be useful since this
       * proxy will automatically log slow running queries to the
       * system log.
       */
      myDataSource = ProxyDataSourceBuilder.create(pooledDataSource)
         .logSlowQueryBySlf4j(10, TimeUnit.SECONDS, SLF4JLogLevel.WARN)
         .build();

      myJdbcTemplate = new JdbcTemplate(myDataSource);
   }

   /**
    * This method is called when the bean is shutting down. It is used
    * to free any resources held by the instance.
    */
   @PreDestroy
   public void stop() throws IOException {
      myDataSource.close();
   }

   /**
    * Searches the database for any patients with the given family name.
    *
    * @param theFamilyName The family name to search for.
    * @param theOffset The
    * @return A collection of mapped FHIR Patient resources.
    */
   public List<Patient> findPatientsByFamilyName(String theFamilyName, int theOffset, int theCount) {
      // Validate our method parameters
      Validate.notEmpty(theFamilyName, "No family name provided");
      Validate.isTrue(theOffset >= 0, "Invalid offset: %d", theOffset);
      Validate.isTrue(theCount > 0, "Invalid count: %d", theCount);

      // This is the SQL that will be executed. Note that it is a good
      // practice to always include a limit in order to avoid accidentally
      // retrieving unlimited amounts of data. When using a limit, an
      // order should also be used in order to ensure stable paging.
      String query = """
         select
            PID, FAMILY, GIVEN, GENDER
         from
            PATIENTS
         where
            FAMILY = ?
         order by
            PID
         offset ?
         fetch first ? rows only
         """;

      // Arguments to the SQL statement above
      Object[] arguments = { theFamilyName, theOffset, theCount };

      // The SQL types for the arguments above
      int[] argumentTypes = { Types.VARCHAR, Types.INTEGER, Types.INTEGER };

      RowMapper<Patient> rowMapper = new PatientRowMapper();
      List<Patient> patients = myJdbcTemplate.query(query, arguments, argumentTypes, rowMapper);

      ourLog.info("Found {} patients in PATIENTS database table", patients.size());

      return patients;
   }


   /**
    * The RowMapper is called once for each row that is returned by the
    * executed SQL query, and maps the columns in that row to a Java
    * object. In this example, we're mapping rows from a PATIENT table
    * to a FHIR Patient object.
    *
    * This is a very basic mapping example with only a few columns,
    * but a real example could be much more complex.
    */
   private static class PatientRowMapper implements RowMapper<Patient> {
      @Override
      public Patient mapRow(ResultSet theResultSet, int theRowNum) throws SQLException {
         long pid = theResultSet.getLong("PID");
         String family = theResultSet.getString("FAMILY");
         String given = theResultSet.getString("GIVEN");

         String gender = theResultSet.getString("GENDER");

         Patient patient = new Patient();
         patient.setId(new IdType("Patient/" + pid));
         patient.addName()
            .setFamily(family)
            .addGiven(given);

         switch (gender) {
            case "m":
               patient.setGender(Enumerations.AdministrativeGender.MALE);
               break;
            case "f":
               patient.setGender(Enumerations.AdministrativeGender.FEMALE);
               break;
            default:
               patient.setGender(Enumerations.AdministrativeGender.UNKNOWN);
               break;

         }

         return patient;
      }
   }
}