org.springframework.web.client.HttpServerErrorException$InternalServerError: 500 Spring Boot

[Alejandro Barrero]

I am trying to access a REST controller with a Java client. The code in the controller is

package com.paperless.controler;

import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.orm.PersistentObject;
import com.paperless.business.DocumentEvent;
import com.paperless.business.DocumentType;
import com.paperless.business.EnteredString;
import com.paperless.business.Licenses;
import com.paperless.business.Signature;
import com.paperless.persistent.Attachment;
import com.paperless.persistent.Control;
import com.paperless.persistent.Document;
import com.paperless.persistent.ProcessingStep;
import com.paperless.persistent.User;

@Controller
public class PaperlessController {

	@ResponseBody
	@RequestMapping(path="/resultSet/{selectStatement}")
  @GetMapping()
	public Object listOfObjects(@PathVariable String selectStatement) {
		ArrayList<PersistentObject> listOfObjects = new ArrayList<>();	
		String[] words = selectStatement.split(" "); // The name of the table in SQL statement "Select * From table ...".
		String table = words[3];
		Connection conn = null;
		try {
			conn = getConnection();
			Statement statement = conn.createStatement();
			ResultSet resultSet = statement.executeQuery(selectStatement);
			while(resultSet.next()) {
				PersistentObject nextObject = persistentObject(table);
				nextObject.copyProperties(resultSet);
				listOfObjects.add(nextObject);
			}
			return listOfObjects;
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				return null;
			}
		}
	}

	private PersistentObject persistentObject(String table) {
		PersistentObject persistentObject = null;
		switch (table) {
		case "attachment" :
			persistentObject = new Attachment();
			break;
		case "control" :
			persistentObject = new Control();
			break;
		case "document" :
			persistentObject = new Document();
			break;
		case "document_event" :
			persistentObject = new DocumentEvent();
			break;
		case "document_type" :
			persistentObject = new DocumentType();
			break;
		case "entered_string" :
			persistentObject = new EnteredString();
			break;
		case "licenses" :
			persistentObject = new Licenses();
			break;
		case "processing_step" :
			persistentObject = new ProcessingStep();
			break;
		case "signatures" :
			persistentObject = new Signature();
			break;
		case "user" :
			persistentObject = new User();
		}
		return persistentObject;
	}

  private Connection getConnection() throws SQLException {
    URL url = getClass().getResource("PaperlessOffice.db");
    String filePath = url.getFile();
    String fullConnectionString = "jdbc:sqlite:" + filePath;
    Connection conn = DriverManager.getConnection(fullConnectionString);
    return conn;
}
}

The controller works with Postman although at the end posts an Internal Server Error.
The code of the client is

package com.paperless.client;

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.springframework.web.client.RestTemplate;

import com.orm.PersistentObject;
import com.paperless.Select_Conn;

public class SelectClient {

	public static void main(String[] args) {
		String selectStatement = "select * from user";
		ArrayList<PersistentObject> resultSet = getResultSet(selectStatement);
	}

	public static ArrayList<PersistentObject> getResultSet(String selectStatement) {
		ArrayList<PersistentObject> listOfObjects;
		RestTemplate restTemplate = new RestTemplate();
		Object result = restTemplate.getForObject("http://localhost:8080/resultSet/{selectStatement}", Object.class, selectStatement);
		listOfObjects = (ArrayList<PersistentObject>) result;
		return listOfObjects;
	}
}

[Tim Holloway]

A "500" error indicates a bug in your Java code that threw an Exception.

More than that we cannot tell unless you show us the server logs with the stack trace in them.

[Dave Tolls]

I'd lay good odds on there not being anything useful in the server logs:

        } catch (SQLException e) {
            return null;
        } finally {
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                return null;
            }
        }

Those catch blocks should log the exception.
The second catch block should not return null, but should do nothing other than log the problem.

[Rob Spoor]

Alejandro, what you've written there is very dangerous. Unless when you're making an actual database management tool, you shouldn't allow users to specify the query to execute in any way. Definitely not like this, but you should also be aware of SQL injection. Right now, a user can send in any query, as long as it produces a result set. That could even be a compound statement that performs a DELETE or even a DROP TABLE statement.

If you want this service to be as dynamic as now, let the user only send the table name. Validate that it's one you expect, then create your SQL query yourself ("SELECT * FROM " + table is now safe because you validated that table is white listed

[Tim Holloway]

Dave Tolls wrote:I'd lay good odds on there not being anything useful in the server logs:

        } catch (SQLException e) {
            return null;
        } finally {
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                return null;
            }
        }

Those catch blocks should log the exception.
The second catch block should not return null, but should do nothing other than log the problem.

Do NOT eat exceptions silently if you ever expect me to respect your code. I get very angry.  

Either handle an exception or report it. Don't create an unnecessary mystery.