Skip to Content

Having written a web application for SAP Netweaver Cloud, most developers will very soon have the need to update their database schemas. Doing that manually is surely a possible way, but fun is not really the right word for this…

Luckily there are solutions out there, in this case even open source tools that can take that job. This blog shows as an example how to use Liquibase (http://www.liquibase.org/) within SAP Netweaver Cloud to migrate the database schema of an already deployed web application without data loss. The migration will happen automatically on redeployment of the application.

Prerequisites

To keep things simple we take the JDBC persistence tutorial as basis. The example is created here: https://help.netweaver.ondemand.com/default.htm?add_pers_jdbc.html. Create the web application, start it and add a few persons using the web UI to have an existing set of data that can be migrated later.

Initial Data.png

Download Liquibase from http://www.liquibase.org/download/. You can use the latest stable Liquibase Core version (currently 2.0.5) and unpack the archive to a folder of your local hard disk.

Preparing the Web Application to Use Liquibase

In order to have the migration of the schema happen automatically on deployment or redeployment of the application we use Liquibase’s ability to be started from a servlet listener. To enable that copy the liquibase.jar from the folder where you unpacked Liquibase to the WebContent/WEB-INF/lib folder of your dynamic web project:

LiquibaseJar.png
To configure Liquibase you need to add the following lines to your application’s web.xml. Place them right behind the resource-ref tag:

  <context-param>
    <param-name>liquibase.changelog</param-name>
    <param-value>hello/db.changelog.xml</param-value>
  </context-param>
  <context-param>
    <param-name>liquibase.datasource</param-name>
    <param-value>java:comp/env/jdbc/DefaultDB</param-value>
  </context-param>
  <listener>
    <listener-class>liquibase.integration.servlet.LiquibaseServletListener</listener-class>
  </listener>

This configures Liquibase to use the given data source (the same as the rest of the application uses on SAP Netweaver Cloud) with the given database changelog file to perform any necessary migration steps on application startup. The last step is now to create the database changelog file in the hello package. Create a new file named db.changelog.xml there:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
</databaseChangeLog>

Redeploy the application to your server. Since there are no migration steps defined, Liquibase will simply do nothing for now. When you deploy the application to a local SAP Netweaver Cloud server, you will find some Liquibase log messages in the OSGI console. Browse through them to check that everything is set up correctly, they should look somewhat like this:

Updating file with path [C:\Development\persistence\Workspace\Servers\SAP NetWeaver Cloud at localhost-config\.\pickup\HelloWorld.war]
INFO 04.09.12 16:33:liquibase: Successfully acquired change log lock
INFO 04.09.12 16:33:liquibase: Creating database history table with name: DATABASECHANGELOG
INFO 04.09.12 16:33:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 16:33:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 16:33:liquibase: Successfully released change log lock
Update of file with path [C:\Development\persistence\Workspace\Servers\SAP NetWeaver Cloud at localhost-config\.\pickup\HelloWorld.war] is successful.
INFO 04.09.12 16:33:liquibase: Successfully acquired change log lock
INFO 04.09.12 16:33:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 16:33:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 16:33:liquibase: Successfully released change log lock

Doing a Migration

Now we can go forward and define a migration for our database schema. We will add a new column to our PERSON table that stores the nickname for the person. For this we modify the database changelog file like this:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
  <changeSet id="1" author="michael">
    <addColumn tableName="person">
      <column name="nickname" type="varchar(255)" />
    </addColumn>
  </changeSet>
</databaseChangeLog>

This defines one change set that will add the additional column to your table and defines the correct type for it. Now simply redeploy the web application to the cloud server and the database schema will be migrated automatically. Here’s the output Liquibase produces on the OSGI console when the application is deployed onto the local SAP Netweaver Cloud server:

osgi> Deploying file with path [C:\Development\persistence\Workspace\Servers\SAP NetWeaver Cloud at localhost-config\.\pickup\HelloWorld.war]
INFO 04.09.12 10:49:liquibase: Successfully acquired change log lock
INFO 04.09.12 10:49:liquibase: Creating database history table with name: DATABASECHANGELOG
INFO 04.09.12 10:49:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 10:49:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 10:49:liquibase: ChangeSet hello/db.changelog.xml::1::michael ran successfully in 16ms
INFO 04.09.12 10:49:liquibase: Successfully released change log lock
Deployment of file with path [C:\Development\persistence\Workspace\Servers\SAP NetWeaver Cloud at localhost-config\.\pickup\HelloWorld.war] is successful.

Finally, in order to really check that everything has been migrated, we change the tutorial coding to use the additional column as well. Following is the full source code of the classes we need to adapt (sorry for the lengthy listings, the actual modifications are rather minor):

Person.java

package hello.persistence;
public class Person {
       private String id;
       private String firstName;
       private String lastName;
       private String nickName;
       public String getId() {
             return id;
       }
       public void setId(String id) {
             this.id = id;
       }
       public String getFirstName() {
             return this.firstName;
       }
       public void setFirstName(String firstName) {
             this.firstName = firstName;
       }
       public String getLastName() {
             return this.lastName;
       }
       public void setLastName(String lastName) {
             this.lastName = lastName;
       }
       public String getNickName() {
             return this.nickName;
       }
       public void setNickName(String nickName) {
             this.nickName = nickName;
       }
}

PersonDAO.java

package hello.persistence;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
public class PersonDAO {
       private static final String TABLE_NAME = "PERSON";
       private static final String STMT_CREATE_TABLE = "CREATE TABLE "
                    + TABLE_NAME + " (ID INTEGER PRIMARY KEY, "
                    + "FIRSTNAME VARCHAR (255), LASTNAME VARCHAR (255))";
       private static final String STMT_SELECT_ALL = "SELECT ID, FIRSTNAME, LASTNAME, NICKNAME FROM "
                    + TABLE_NAME;
       private static final String STMT_INSERT = "INSERT INTO " + TABLE_NAME
                    + " (ID, FIRSTNAME, LASTNAME, NICKNAME) VALUES (?, ?, ?, ?)";
       private DataSource dataSource;
       public PersonDAO(DataSource dataSource) throws SQLException {
             setDataSource(dataSource);
       }
       public DataSource getDataSource() {
             return dataSource;
       }
       public void setDataSource(DataSource dataSource) throws SQLException {
             this.dataSource = dataSource;
             checkTable();
       }
       public void addPerson(Person person) throws SQLException {
             Connection conn = dataSource.getConnection();
             try {
                    PreparedStatement pstmt = conn.prepareStatement(STMT_INSERT);
                    pstmt.setString(1, person.getId());
                    pstmt.setString(2, person.getFirstName());
                    pstmt.setString(3, person.getLastName());
                    pstmt.setString(4, person.getNickName());
                    pstmt.executeUpdate();
             } finally {
                    if (conn != null) {
                           conn.close();
                    }
             }
       }
       public List<Person> selectAllPersons() throws SQLException {
             Connection conn = dataSource.getConnection();
             try {
                    PreparedStatement pstmt = conn.prepareStatement(STMT_SELECT_ALL);
                    ResultSet rs = pstmt.executeQuery();
                    ArrayList<Person> list = new ArrayList<Person>();
                    while (rs.next()) {
                           Person p = new Person();
                           p.setId(rs.getString(1));
                           p.setFirstName(rs.getString(2));
                           p.setLastName(rs.getString(3));
                           p.setNickName(rs.getString(4));
                           list.add(p);
                    }
                    return list;
             } finally {
                    if (conn != null) {
                           conn.close();
                    }
             }
       }
       private void checkTable() throws SQLException {
             Connection conn = null;
             try {
                    conn = dataSource.getConnection();
                    if (!existsTable(conn)) {
                           createTable(conn);
                    }
             } finally {
                    if (conn != null) {
                           conn.close();
                    }
             }
       }
       private boolean existsTable(Connection conn) throws SQLException {
             DatabaseMetaData meta = conn.getMetaData();
             ResultSet rs = meta.getTables(null, null, TABLE_NAME, null);
             while (rs.next()) {
                    String name = rs.getString("TABLE_NAME");
                    if (name.equals(TABLE_NAME)) {
                           return true;
                    }
             }
             return false;
       }
       private void createTable(Connection conn) throws SQLException {
             PreparedStatement pstmt = conn.prepareStatement(STMT_CREATE_TABLE);
             pstmt.executeUpdate();
       }
}

HelloWorldServlet.java

package hello;
import hello.persistence.Person;
import hello.persistence.PersonDAO;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import com.sap.security.core.server.csi.IXSSEncoder;
import com.sap.security.core.server.csi.XSSEncoder;
public class HelloWorldServlet extends HttpServlet {
       private static final long serialVersionUID = 1L;
       private PersonDAO personDAO;
       protected void doGet(HttpServletRequest request,
                    HttpServletResponse response) throws ServletException, IOException {
             createResponse(response);
       }
       protected void doPost(HttpServletRequest request,
                    HttpServletResponse response) throws ServletException, IOException {
             try {
                    doAdd(request);
             } catch (SQLException e) {
                    throw new ServletException(e);
             }
             createResponse(response);
       }
       private void createResponse(HttpServletResponse response)
                    throws ServletException, IOException {
             response.setContentType("text/html");
             PrintWriter out = response.getWriter();
             out.write("<html><head></head><body>");
             List<Person> resultList;
             try {
                    resultList = personDAO.selectAllPersons();
             } catch (SQLException e) {
                    throw new ServletException(e);
             }
             out.write("<table border=\"1\"><tr><th colspan=\"4\">"
                           + (resultList.isEmpty() ? "" : resultList.size() + " ")
                           + "Entries in the Database</th></tr>");
             if (resultList.isEmpty()) {
                    out.write("<tr><td colspan=\"4\">Database is empty</td></tr>");
             } else {
                    out.write("<tr><th>First name</th><th>Last name</th><th>Nickname</th><th>Id</th></tr>");
             }
             IXSSEncoder xssEncoder = XSSEncoder.getInstance();
             for (Person p : resultList) {
               String nickName = p.getNickName();
               if (nickName == null) {
                nickName = " ";
               } else {
                nickName = xssEncoder.encodeHTML(p.getNickName()).toString();
               }
                    out.write("<tr><td>" + xssEncoder.encodeHTML(p.getFirstName())
                                  + "</td><td>" + xssEncoder.encodeHTML(p.getLastName())
                                  + "</td><td>" + nickName
                                  + "</td><td>" + p.getId() + "</td></tr>");             }
             out.write("</table>");
             out.write("<form action=\"\" method=\"post\">"
                           + " First name:<input type=\"text\" name=\"FirstName\">"
                           + " Last name:<input type=\"text\" name=\"LastName\">"
                           + " Nickname:<input type=\"text\" name=\"NickName\">"
                           + " Id:<input type=\"text\" name=\"Id\">"
                           + " <input type=\"submit\" value=\"Add Person\">"
                           + " </form>");
             out.write("</body></html>");
       }
       private void doAdd(HttpServletRequest request) throws ServletException,
                    IOException, SQLException {
             String firstName = request.getParameter("FirstName");
             String lastName = request.getParameter("LastName");
             String nickName = request.getParameter("NickName");
             String id = request.getParameter("Id");
             if (firstName != null && lastName != null && id != null
                           && !firstName.trim().isEmpty() && !lastName.trim().isEmpty()
                           && !nickName.trim().isEmpty() && !id.trim().isEmpty()) {
                    Person person = new Person();
                    person.setFirstName(firstName.trim());
                    person.setLastName(lastName.trim());
                    person.setNickName(nickName.trim());
                    person.setId(id.trim());
                    personDAO.addPerson(person);
             }
       }
       @Override
       public void init() throws ServletException {
             try {
                    InitialContext ctx = new InitialContext();
                    DataSource ds = (DataSource) ctx
                                  .lookup("java:comp/env/jdbc/DefaultDB");
                    personDAO = new PersonDAO(ds);
             } catch (SQLException e) {
                    throw new ServletException(e);
             } catch (NamingException e) {
                    throw new ServletException(e);
             }
       }
}

Again redeploy the web application and now there’s the additional table column appearing in the web UI. The person data you have added in the beginning has been preserved during the migration and the new column is available. After adding a new entry the UI looks like this.

MigratedData.png

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

    1. Michael Wenz Post author

      Not out-of-the-box, as Liquibase does not (yet? 😉 ) offer an extension for HANA. I heared people were using some tricks to get it working nevertheless, but didn’t yet find the time to get into the details here…

      (0) 
  1. Chris Paine

    Hi Michael, a very interesting blog. Harald pointed me here after I complained there wasn’t an easy method to drop a nwcloud db.

    As I mentioned to Harald over Twitter, having half your dictionary definition in Liquibase and half in the DAO would be very confusing for support. Is it possible to have the entire table definition/creation in Liquibase and then just the manipulation of the table in the DAO? How would one set that up?

    On another topic – how did you manage to format the source code in your blog so nicely?

    Thanks for sharing.

    Cheers,

    Chris

    (0) 
  2. Luigi Bitonti

    Hi Michael,

    Thanks for the article.

    I have released a Liquibase extension for Hana which does the job nicely for me. It can be found at:

    https://liquibase.jira.com/wiki/display/CONTRIB/Hana+extension

    If you have time to give it a go, a would be happy to hear your thoughts.

    Patching the Liquibase 2.0.x source with

    https://liquibase.jira.com/browse/CORE-1200 unfortunately does not seem to be enough for Liquibase to work with Hana as the initial Liquibase tables creation fails.

    Cheers,

    Luigi

    (0) 
  3. Barry Ryan

    Hi Micheal,

    I have a HTML page with a UI front end.

    I have this page running connecting with my servlet.

    The servlet I have running is the persistence with JDBC servlet, as seen above.

    I need to retrieve data from backend from this servlet,

    the HTML page launches on HANA Cloud and I enter some data.

    for example: name, ID, DOB, etc.

    I click submit. but i get a 500 error.

    Why is this?

    Thanks.

    Best Regards,

    /Barry

    (0) 
  4. Sachin Pattan

    Hi Michael,

    I followed the steps mentioned by you . I am trying to configure liquibase with the existing Web project which runs on SAP hana cloud (and hence using Apache Derby data base for local ) , and uses maven.

    I configured the web.xml and downloaded and kept the JAR too (in fact , I have even added the maven dependency for liquibase ) . I am still not able to see liquibase related logs in the OSGI logs .

    (0) 
  5. Haipeng Chen

    Hi Michael,

    With this way, I can only update changes to database by liquibase, but how can I use liquibase to do database version control?

    And I use HCP console client to generate a JDBC url, but I can’t connect to it using liquibase. How can I connect hana db in cmd.

    Thank you.

    (0) 
    1. Britta Varwig

      Hello Haipeng,

      what exactly do you want to do?

      You can use the neo command open-db-tunnel

      to open a local db tunnel and than use your prefered sql client to run sql scripts.

      Please also check if SAP HANA Delivery Units helps in your scenario.

      Regards Britta

      (0) 
      1. Haipeng Chen

        Hello Varwig,

        I want to use liquibase to manage changes in HANA DB. Issue happens when I try to connect to DB. And finally I find that the issue is caused by the special symbol in password.Now I can execute database updating in cmd using liquibase. But when I try to add listeners as this article and deploy it on HCP, it’s useless.

        And by the way, if there is some way that liquibase can record DB changes automatically.

        Thank you.

        (0) 

Leave a Reply