CD part VI: Custom persistence layer between the database and soap-services

Johan Tuitel

  • 16/12/2016
  • 7 minuten leestijd
Johan Tuitel

CD part VI: Custom persistence layer between the database and soap-services

In this blog i will show you what i've done to create a custom persistence layer to access our data in the Postgresql database
  1. create a folder projects on the C disk
  2. navigate in the Command Prompt to the c:\projects file
  3. create project with maven
    mvn archetype:generate -DgroupId=nl.com.devnl -DartifactId=EasyDatabase -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false
  1. finish the project by building it with maven mvn clean install

create property-file

  1. first we create the property-file in which we determine the database configuration
  2. create a folder in the project src\main\resources
  3. in this folder we create a file called database.properties
  4. this is the content of the file hostname=localhost port=5432 databasename=postgres username=postgres password=admin

read property file

  1. rename the App.java to Reader.java

    package nl.com.devnl;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.Properties;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class Reader {
    
        private static final String FILENAME = "database.properties";
    
        public Properties getDatabaseConfiguration(){
            Properties props = new Properties();
    
            InputStream inputStream = getClass().getClassLoader().getResourceAsStream(FILENAME);
            if(null != inputStream){
                try {
                    props.load(inputStream);
                } catch (IOException ex) {
                    Logger.getLogger(Reader.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            return props;
        }
    }
  2. update the junit dependency, in my case it was version 4.12
  3. Now we want this the Reader class to be tested
  4. rename the AppTest.java to ReaderTest.java

    package nl.com.devnl;
    
    import java.util.Properties;
    import junit.framework.Assert;
    import org.junit.Before;
    import org.junit.Test;
    import org.junit.internal.runners.JUnit4ClassRunner;
    import org.junit.runner.RunWith;
    
    @RunWith(JUnit4ClassRunner.class)
    public class ReaderTest{
    
       private static final String HOSTNAME="localhost";
       private static final String PORT="5432";
       private static final String DATABASENAME="postgres";
       private static final String USERNAME="admin";
       private static final String PASSWORD="admin";
    
       private Reader reader;
    
       @Before
       public void setup(){
           reader = new Reader();
       }
    
       @Test
       public void read(){
           Properties props = reader.getDatabaseConfiguration();
           Assert.assertEquals(HOSTNAME, props.get("hostname"));
           Assert.assertEquals(PORT, props.get("port"));
           Assert.assertEquals(DATABASENAME, props.get("databasename"));
           Assert.assertEquals(USERNAME, props.get("username"));
           Assert.assertEquals(PASSWORD, props.get("password"));
       }
    }

create a database connection

  1. first we need to add the postgresql dependency in maven
  2. we now create a class to create a database connection and we call it DBConnection

    package nl.com.devnl;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.Properties;
    
    public class DBConnection {
    
       private Reader reader;
       private Connection connection;
    
       public Connection createConnection(){
           reader = new Reader();
           Properties props = reader.getDatabaseConfiguration();
           String hostname = props.getProperty("hostname");
           String port = props.getProperty("port");
           String databasename = props.getProperty("databasename");
           String user = props.getProperty("username");
           String password = props.getProperty("password");
           String url = "jdbc:postgresql://"+hostname+":"+port+"/"+databasename;
    
           try{
               connection = DriverManager.getConnection(url, user, password);
           }catch(SQLException ex){
               ex.printStackTrace();
           }
           return connection;
       }
    
       public void closeConnection(){
           try {
               connection.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
    }
  3. so we need to test the DBConnection class to test if we can connect to our database postgres
  4. we create a DBConnectionTest

    package nl.com.devnl;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    import junit.framework.Assert;
    import org.junit.Before;
    import org.junit.Test;
    import org.junit.internal.runners.JUnit4ClassRunner;
    import org.junit.runner.RunWith;
    
    @RunWith(JUnit4ClassRunner.class)
    public class DBConnectionTest {
    
       private static final String QUERY = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES";
       private DBConnection dbConn;
    
       @Before
       public void setup(){
           dbConn = new DBConnection();
       }
    
       @Test
       public void testConnection(){
           Assert.assertNotNull(dbConn.createConnection());
           dbConn.closeConnection();
       }
    
       @Test
       public void testResult(){
           Connection conn = dbConn.createConnection();
           try {
               Statement stmt = conn.createStatement();
               ResultSet rs = stmt.executeQuery(QUERY);
               while(rs.next()){
                   int aantal = rs.getInt(1);
                   Assert.assertEquals(173, aantal);
               }
           } catch (SQLException ex) {
               Logger.getLogger(DBConnectionTest.class.getName()).log(Level.SEVERE, null, ex);
           }
           dbConn.closeConnection();
       }
    }

final jar file

  1. build project by mvn clean install
  2. navigate to C:\repository\nl\com\devnl\EasyDatabase\1.0-SNAPSHOT
  3. yeah, we have a jar file, which we will be using for the services we are going to build!!!