I am working on a project that is using an Oracle database with stored procedures. I am unit testing with junit.
What I'd like to do is use an embedded database to emulate the Oracle database. However the application is using calls to stored procedures. The stored procedure takes in two variables and outs 15.
It would be simple to emulate a this stored procedure with a simple query to a table full of test data.
What embedded databases are available that can be used to emulate this functionality?
web_services.search_registrants(
cert_nbr_in => :cert_nbr_in,
last_4_ssn_in => :last_4_ssn_in,
status_out => :status_out,
cert_nbr_out => :cert_nbr_out,
last_4_ssn_out => :last_4_ssn_out,
last_name_out => :last_name_out,
first_name_out => :first_name_out,
mid_name_out => :mid_name_out,
email_addr_out => :email_addr_out,
st_address1_out => :st_address1_out,
st_address2_out => :st_address2_out,
city_out => :city_out,
state_out => :state_out,
zip_code_out => :zip_code_out,
home_phone_out => :home_phone_out,
work_phone_out => :work_phone_out,
cell_phone_out => :cell_phone_out);
This is the java code, which works fine against Oracle.
public Applicant find(String certNumber, String last4Ssn) {
Connection conn = null;
CallableStatement cs = null;
Applicant applicant = null;
try {
try {
conn = this.getConnection();
cs = conn.prepareCall(
"{call NARS.web_services.search_registrants(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}") ;
cs.setString("CERT_NBR_IN", certNumber);
cs.setString("LAST_4_SSN_IN", last4Ssn);
cs.registerOutParameter("STATUS_OUT", Types.VARCHAR);
cs.registerOutParameter("CERT_NBR_OUT", Types.VARCHAR);
cs.registerOutParameter("LAST_4_SSN_OUT", Types.VARCHAR);
cs.registerOutParameter("LAST_NAME_OUT", Types.VARCHAR);
cs.registerOutParameter("FIRST_NAME_OUT", Types.VARCHAR);
cs.registerOutParameter("MID_NAME_OUT", Types.VARCHAR);
cs.registerOutParameter("EMAIL_ADDR_OUT", Types.VARCHAR);
cs.registerOutParameter("ST_ADDRESS1_OUT", Types.VARCHAR);
cs.registerOutParameter("ST_ADDRESS2_OUT", Types.VARCHAR);
cs.registerOutParameter("CITY_OUT", Types.VARCHAR);
cs.registerOutParameter("STATE_OUT", Types.VARCHAR);
cs.registerOutParameter("ZIP_CODE_OUT", Types.VARCHAR);
cs.registerOutParameter("HOME_PHONE_OUT", Types.VARCHAR);
cs.registerOutParameter("WORK_PHONE_OUT", Types.VARCHAR);
cs.registerOutParameter("CELL_PHONE_OUT", Types.VARCHAR);
cs.execute();
// 3 STATUS_OUT OUT VARCHAR2 -- 0 = found,
// 1 = not found, 2 = expired certification, 3 = abuse
String status = cs.getString("STATUS_OUT");
System.out.println("status is: " + status);
if(!status.equals("1")){
System.out.println("certNumber " + cs.getString("CERT_NBR_OUT"));
// Create new Applicant
applicant = new Applicant(certNumber, last4Ssn);
// 4 CERT_NBR_OUT OUT VARCHAR2 -- 2 expired certification
if(status.equals("2")){
applicant.setExpired(true);
}
if(status.equals("3")){
applicant.setAbuse(true);
}
applicant.setLastName(cs.getString("LAST_NAME_OUT"));
applicant.setFirstName(cs.getString("FIRST_NAME_OUT"));
applicant.setMidInit(cs.getString("MID_NAME_OUT"));
applicant.setEmailAddress(cs.getString("EMAIL_ADDR_OUT"));
applicant.setAddressLine1(cs.getString("ST_ADDRESS1_OUT"));
applicant.setAddressLine2(cs.getString("ST_ADDRESS2_OUT"));
applicant.setCity(cs.getString("CITY_OUT"));
applicant.setState(cs.getString("STATE_OUT"));
applicant.setZipCode(cs.getString("ZIP_CODE_OUT"));
applicant.setHomePhone(cs.getString("HOME_PHONE_OUT"));
applicant.setWorkPhone(cs.getString("WORK_PHONE_OUT"));
applicant.setCellPhone(cs.getString("CELL_PHONE_OUT"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(cs != null) cs.close();
if(conn != null) conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
return applicant;
}
I solved this problem by using HSQLDB. I was able to write a stored procedures that return out variables. It's really cool. Here is an example of my HSQLDB setup script.
create table NARS_WEB_SERVICES.APPLICANTS (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
cert_nbr VARCHAR(10),
status VARCHAR(1),
last_name VARCHAR(40),
first_name VARCHAR(40),
mid_name VARCHAR(30),
gender_code VARCHAR(7),
lst_4_ssn VARCHAR(4),
ssn VARCHAR(9),
addr_1 VARCHAR(30),
addr_2 VARCHAR(30),
city VARCHAR(30),
stat VARCHAR(2),
zip VARCHAR(10),
home_phone VARCHAR(20),
work_phone VARCHAR(20),
cell_phone VARCHAR(20),
email_addr VARCHAR(50));
CREATE PROCEDURE NARS_WEB_SERVICES.SEARCH_REGISTRANTS
( IN CERT_NBR_IN VARCHAR(30),
IN LAST_4_SSN_IN VARCHAR(30),
OUT STATUS_OUT VARCHAR(30),
OUT CERT_NBR_OUT VARCHAR(30),
OUT LAST_4_SSN_OUT VARCHAR(30),
OUT LAST_NAME_OUT VARCHAR(30),
OUT FIRST_NAME_OUT VARCHAR(30),
OUT MID_NAME_OUT VARCHAR(30),
OUT EMAIL_ADDR_OUT VARCHAR(30),
OUT ST_ADDRESS1_OUT VARCHAR(30),
OUT ST_ADDRESS2_OUT VARCHAR(30),
OUT CITY_OUT VARCHAR(30),
OUT STATE_OUT VARCHAR(30),
OUT ZIP_CODE_OUT VARCHAR(30),
OUT HOME_PHONE_OUT VARCHAR(30),
OUT WORK_PHONE_OUT VARCHAR(30),
OUT CELL_PHONE_OUT VARCHAR(30) )
READS SQL DATA
BEGIN ATOMIC
SELECT status, cert_nbr, last_name, first_name, mid_name, lst_4_ssn,
addr_1, addr_2, city, stat, zip, home_phone, work_phone,
cell_phone, email_addr
INTO STATUS_OUT, CERT_NBR_OUT, LAST_NAME_OUT, FIRST_NAME_OUT,
MID_NAME_OUT, LAST_4_SSN_OUT, ST_ADDRESS1_OUT, ST_ADDRESS2_OUT,
CITY_OUT, STATE_OUT, ZIP_CODE_OUT, HOME_PHONE_OUT, WORK_PHONE_OUT,
CELL_PHONE_OUT, EMAIL_ADDR_OUT
FROM NARS_WEB_SERVICES.APPLICANTS
WHERE cert_nbr = CERT_NBR_IN
OR lst_4_ssn = LAST_4_SSN_IN;
END
.;
And that's it! So of course I set the database to my HSQLDB and tested against that.