CODEWITHSUNDEEP
javajakarta-eejpaderby
LuckyLuke
LuckyLuke

Reputation: 49077

How to pre populate Derby in memory with tables and some data?

I am trying to use Derby for my integration tests and I wonder how I can pre populate it with some data and table structure? I am not able to find the documentation for it (even though it for sure exists somewhere). I am using it with JPA.

Upvotes: 1

Views: 970

Answers (2)

greg-449
greg-449

Reputation: 111142

For simple stuff like creating tables and inserting values you could use the ant sql task and a file containing SQL commands.

Ant:

    <sql driver="org.apache.derby.jdbc.EmbeddedDriver" 
         url="jdbc:derby:${dbpath};create=true"
         src="${dbdir}/${name}.sql"
         keepformat="true"
         password="" userid="">
      <classpath>
         <pathelement location="${env.DERBY_INSTALL}/lib/derby.jar"/>
         <pathelement location="${env.DERBY_INSTALL}/lib/derbytools.jar"/>
      </classpath>
    </sql>

SQL file:

CREATE TABLE Cabs
 (Path VARCHAR(256) NOT NULL PRIMARY KEY,
  Description VARCHAR(64) NOT NULL,
  EngineType SMALLINT NOT NULL);

INSERT INTO Cabs
 VALUES
  .... values to insert
  ;

Upvotes: 2

Levenal
Levenal

Reputation: 3806

Same as you, I was unable to find an API of any sorts but have been able to put together the following code snippets from various examples. I hope you find it useful.

The main way I have interacted with Derby is via String SQL queries as shown in the examples below:

String create = "CREATE TABLE Pathways (" +
"ID_NUMBER VARCHAR(10) NOT NULL," +
"PATH VARCHAR(50))";
Statement st = conn.createStatement();
st.executeUpdate(create);
st.close();

This creates the table Pathways within the database, and adds two columns: ID_NUMBER and PATH

DatabaseMetaData dmd = conn.getMetaData();
ResultSet columns = dmd.getColumns(null, null, "PATHWAYS", bob);
while(columns.next()){
System.out.println(columns.getString("COLUMN_NAME"));
}
String bob = NameOfColumnIWantToReturnOrNull

This will allow you to view all of the columns within the table you have just created.

Statement st = conn.createStatement();
st.executeUpdate("INSERT INTO PATHWAYS VALUES('Test path', 'C:\\' )");
st.close();

This should allow you to insert a value into each column of the pathways table

st.executeUpdate("ALTER TABLE PATHWAYS ADD COLUMN ID_NUMBER VARCHAR(10)");

This one adds a new column to the Pathways table

st.executeUpdate("ALTER TABLE PATHWAYS DROP COLUMN ID_NUMBER");

This statement removes a column from the table

ArrayList<String> listTables = new ArrayList<>();
ResultSet tables = conn.getMetaData().getTables(null, null, null, null);
while(tables.next()){
String check = tables.getString("TABLE_NAME");
if(check.length() < 3 || !check.substring(0, 3).equals("SYS")) {
listTables.add(check);
    }
}
tables.close();

This query will list all the table in the database and put them into the ArrayList listTables, once in there you can list them by iterating over the list.. The reason for missing the ones labelled "SYS" is because they are already present in the database and I believe used by Derby for it's own purpose, and so are best left alone.

Statement st=conn.createStatement();
String tableRemove = "DROP TABLE ";
for(String i : listTables){
tableRemove += i;
st.executeUpdate(tableRemove);
}

Combined with the above code, you can remove or "DROP" the table you desire by iterating over the list and selecting the table you want to drop, in this case I am iterating over all tables and dropping them all.

ResultSet rs=st.executeQuery("SELECT * FROM USERS");
    while (rs.next()) {
        String first=rs.getString("FIRST_NAME");
        String last=rs.getString("LAST_NAME");
        System.out.println("Name: " + first + " " + last);
    }
    rs.close();

This one selects all entries from a table called USERS and places them into a ResultSet. Once done you can iterate over that ResultSet to print the values

Hope this can at the least give you a bit of a start, Good Luck!

Upvotes: 2

Related Questions