Object is null from database unit test

Question

i have two functions wrotten in java, and i am trying to create their unit tests. The first function is to add an element to databse the second is to get the element by it's id from databse. I created both of their tests, the first one inserts correctly in database. but when i did the get one it seems like the database is empty!

enter code here
package com.example.service;

import com.example.model.City;
import org.junit.Test;

import java.sql.*;

public class DatabaseService {


public static int addCity(Connection conn, City city) {
    PreparedStatement pstmt = null;
    int i = -1;
    try {
        String sql = "INSERT INTO City " + "VALUES (?,?,?,?)";
        pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, city.getIdCity());
            pstmt.setString(2, city.getName());
            pstmt.setInt(3, city.getTouristNumber());
            pstmt.setString(4, city.getDescription());
            i = pstmt.executeUpdate();
            pstmt.close();


    } catch (SQLException se) {
        se.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (pstmt != null) pstmt.close();
        } catch (SQLException se2) {
        }
    }
    return i;
}


public static City getCity(Connection conn,int idCity) {
    PreparedStatement pstmt = null;
    City city = new City();
    try {

        String sql = "SELECT * FROM City where idCity=?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,idCity);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            city.setIdCity(rs.getInt("idCity"));
            city.setName(rs.getString("name"));
            city.setTouristNumber(rs.getInt("touristNumber"));
            city.setDescription(rs.getString("description"));
        }

        rs.close();
        pstmt.close();
    } catch (SQLException se) {
        se.printStackTrace();
    } finally {
        try {
            if (pstmt != null) pstmt.close();
        } catch (SQLException se2) {
        }
    }
    return  city;
}

}

For the connection :

enter code here
 package com.example.service;

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.Statement;

  public class DatabaseConnection {

private String USER;
private String PASS;
private String JDBC_DRIVER;
private String DB_URL;

public DatabaseConnection(String USER, String PASS, String JDBC_DRIVER, String DB_URL) {
    this.USER = USER;
    this.PASS = PASS;
    this.JDBC_DRIVER = JDBC_DRIVER;
    this.DB_URL = DB_URL;
}

public  Connection connect() {
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(DB_URL, USER, PASS);
        Class.forName(JDBC_DRIVER);
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    return conn;
}

public  void disconnect(Connection conn ) {
    try {
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public void createDb(Connection conn) {
    Statement stmt = null;
    try {
        stmt = conn.createStatement();
        String sql =  "CREATE TABLE   City " +
                "(idCity INTEGER not NULL, " +
                " name VARCHAR(255), " +
                " touristNumber INTEGER, " +
                " description VARCHAR(255), " +
                " PRIMARY KEY ( idCity ))";
        stmt.executeUpdate(sql);
        stmt.close();
    } catch(SQLException se) {
        se.printStackTrace();
    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        try{
            if(stmt!=null) stmt.close();
        } catch(SQLException se2) {
        }
    }
}

}

For the Unit tests:

enter code here
 package com.example.service;

import com.example.model.City;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

 import java.sql.Connection;

import static org.junit.Assert.*;

public class DatabaseServiceTest {
City city;

Connection conn;
DatabaseConnection datab;
@Before
public void init(){
    datab= new DatabaseConnection("sa","","org.h2.Driver","jdbc:h2:mem:test") ;
   conn = datab.connect();
   datab.createDb(conn);
    city = new City(1,"Alger",123,"capital");



}
@Test
public void addCity() {

    assertEquals(1,new DatabaseService().addCity(conn, city));

}

@Test
public void getCity() {

    assertEquals(city.getIdCity(),new DatabaseService().getCity(conn, 1).getIdCity());
 /*   assertEquals(city.getTouristNumber(),new DatabaseService().getCity(conn, 1).getTouristNumber());
    assertEquals(city.getName(),new DatabaseService().getCity(conn, 1).getName());
    assertEquals(city.getDescription(),new DatabaseService().getCity(conn, 1).getDescription());*/
}

}

For the debug result i got this : (second one get:)

enter code here
 Connected to the target VM, address: '127.0.0.1:3543', transport: 'socket'
 Java HotSpot(TM) 64-Bit Server VM warning: Sharing is only supported for boot 
 loader classes because bootstrap classpath has been appended

 java.lang.AssertionError: 
 Expected :1
 Actual   :0

at org.junit.Assert.fail(Assert.java:93)
at org.junit.Assert.failNotEquals(Assert.java:647)
at org.junit.Assert.assertEquals(Assert.java:128)
at org.junit.Assert.assertEquals(Assert.java:472)
at org.junit.Assert.assertEquals(Assert.java:456)
at com.example.service.DatabaseServiceTest.getCity(DatabaseServiceTest.java:37)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:564)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
at org.junit.runner.JUnitCore.run(JUnitCore.java:157)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:220)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:53)

Disconnected from the target VM, address: '127.0.0.1:3543', transport: 'socket'

 Process finished with exit code -1

Yes, i edited the code by adding the debug of the second one (getCities) — LlMm, Dec 18 '20 at 15:37

score 0 · Answer 1 · answered Dec 18 '20 at 14:46

0

You may find interesting to use Mockito to mock the DB connection, instead of using an in-memory DB, for two reasons:

An in-memory DB may introduce issues to run the tests in parallel
Since it's not the same DB, there may be special SQL features or keywords that are not present in the in-memory version.

Check this out, it may help you understand how to test the DatabaseService with a mocked connection: How to use mockito for testing Database connection

answered Dec 18 '20 at 14:46

germanio

861
1
17
27

Hello, Thank you for your answer. For the database in memory i am obliged to work with it because i have an exercice that functions like that, but can you please explain the second reason what i understand is that it can change the database after different executions? – LlMm Dec 18 '20 at 14:59
ohhhh got it, with #2 I mean, sometimes you want to use a specific db feature or keyword (that is not standard sql) that could not be implemented in the inmemory version you are using. But if this is for an excercise, it's most probably fine. – germanio Dec 18 '20 at 15:21

Object is null from database unit test

1 Answers1