My approach in standardizing SQLite in a Spring framework project

In a software development world were rapid project delivery is needed. The only way for us, developer having a sense of sanity is to build standardize code for future development. In doing so, it shortens the development time but having tested and functioning code base.

ISqliteObjectAssembler.java

This interface will serve as my IOC to have my own implementation of parsing from resultset to my specific Object

/**
 * Created by mrprintedwall on 11/02/17.
 */

public interface ISqliteObjectAssembler
{
	T assemble(ResultSet resultSet) throws SQLException;
}

ISqliteRepository.java

/**
 * Created by mrprintedwall on 11/02/17.
 */

public interface ISqliteRepository
{
	//region CONNECTION

	Connection getConnection() throws SQLException;

	//endregion

	//region ROW METHODS

	/**
	 * INSERT INTO SQLITE AND RETURNS THE GENERATED PRIMARY ID
	 * @param sql
	 * @param parameters
	 * @return
	 * @throws SQLException
	 */
	int insert(String sql, Map parameters) throws SQLException;

	/**
	 * UPDATE ROWS IN SQLITE AND RETURN AFFECTED NUMBER OF ROWS
	 * @param sql
	 * @param parameters
	 * @return
	 * @throws SQLException
	 */
	int update(String sql, Map parameters) throws SQLException;

	/**
	 * DELETE ROWS IN SQLITE AND RETURN DELETED NUMBER OF ROWS
	 * @param sql
	 * @param parameters
	 * @return
	 * @throws SQLException
	 */
	int delete(String sql, Map parameters) throws SQLException;

	/**
	 * GET SINGLE ITEM FROM RESULTSET USES INJECTED OBJECT MAPPER FOR RESULTBINDING
	 * @param sql
	 * @param sqliteObjectAssembler
	 * @param 
	 * @return
	 * @throws SQLException
	 */
	 T getSingle(String sql, ISqliteObjectAssembler sqliteObjectAssembler) throws SQLException;

	/**
	 * GET LIST OF ITEMS FROM RESULTSET USES INJECTED OBJECT MAPPER FOR RESULTBINDING
	 * @param sql
	 * @param sqliteObjectAssembler
	 * @param 
	 * @return
	 * @throws SQLException
	 */
	 List getList(String sql, ISqliteObjectAssembler sqliteObjectAssembler) throws SQLException;
	
	/**
	 * GET SINGLE ITEM USING MAP OF PARAMETERS FROM RESULTSET USES INJECTED OBJECT MAPPER FOR RESULTBINDING
	 * @param sql
	 * @param parameters
	 * @param sqliteObjectAssembler
	 * @return
	 * @throws SQLException
	 */
	 T getSingle(String sql, Map parameters, ISqliteObjectAssembler sqliteObjectAssembler) throws SQLException;

	/**
	 * GET LIST OF ITEMS USING MAP OF PARAMETERS FROM RESULTSET USES INJECTED OBJECT MAPPER FOR RESULTBINDING
	 * @param sql
	 * @param parameters
	 * @param sqliteObjectAssembler
	 * @return
	 * @throws SQLException
	 */
	 List getList(String sql, Map parameters, ISqliteObjectAssembler sqliteObjectAssembler) throws SQLException;
	//endregion

	//region TABLE METHODS

	void createTable(String sql) throws SQLException;
	void alterTable(String sql) throws SQLException;
	void dropTable(String sql) throws SQLException;

	//endregion

}

SqliteDbRepository.java

/**
 * Created by mrprintedwall on 11/02/17.
 */

@Service
public class SqliteDbRepository implements ISqliteRepository
{
	private static final Logger logger = LoggerFactory.getLogger(SqliteDbRepository.class);

	private AppConfig appConfig;

	@Autowired
	public SqliteDbRepository(AppConfig appConfig)
	{
		this.appConfig = appConfig;
	}

	@Override
	public Connection getConnection() throws SQLException
	{
		try
		{
			Class.forName("org.sqlite.JDBC");
			Connection connection = DriverManager.getConnection(appConfig.getSqliteConnectionString());
			PreparedStatement preparedStatement = connection.prepareStatement("PRAGMA foreign_keys=ON");
			preparedStatement.execute();
			return connection;
		}
		catch (ClassNotFoundException classNotFoundException)
		{
			logger.error(classNotFoundException.getMessage());
		}
		return null;
	}

	@Override
	public int insert(String sql, Map parameters) throws SQLException
	{
		try(Connection connection = getConnection())
		{
			PreparedStatement preparedStatement = connection.prepareStatement(sanitizeSqlString(sql));
			if (parameters != null)
			{
				injectParameterToPreparedStatement(parameters, preparedStatement);
			}
			preparedStatement.executeUpdate();
			return getLastInsertedId(connection);
		}
	}

	@Override
	public int update(String sql, Map parameters) throws SQLException
	{
		try (Connection connection = getConnection())
		{
			PreparedStatement preparedStatement = connection.prepareStatement(sanitizeSqlString(sql));
			if (parameters != null)
			{
				injectParameterToPreparedStatement(parameters, preparedStatement);
			}
			return preparedStatement.executeUpdate();
		}
	}

	@Override
	public int delete(String sql, Map parameters) throws SQLException
	{
		try (Connection connection = getConnection())
		{
			PreparedStatement preparedStatement = connection.prepareStatement(sanitizeSqlString(sql));
			if (parameters != null)
			{
				injectParameterToPreparedStatement(parameters, preparedStatement);
			}
			return preparedStatement.executeUpdate();
		}
	}

	@Override
	public T getSingle(String sql, ISqliteObjectAssembler sqliteObjectAssembler) throws SQLException
	{
		try (Connection connection = getConnection())
		{
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet != null)
			{
				return (T) sqliteObjectAssembler.assemble(resultSet);
			}
		}
		return null;
	}

	@Override
	public List getList(String sql, ISqliteObjectAssembler sqliteObjectAssembler) throws SQLException
	{
		try (Connection connection = getConnection())
		{
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet != null)
			{
				List results = new ArrayList<>();
				while (resultSet.next())
				{
					try
					{
						results.add((T) sqliteObjectAssembler.assemble(resultSet));
					}
					catch (Exception exception)
					{
						logger.error(exception.getMessage());
					}
				}
				return results;
			}
		}
		return new ArrayList<>();
	}

	@Override
	public  T getSingle(String sql, Map parameters, ISqliteObjectAssembler sqliteObjectAssembler)
			throws SQLException
	{
		try (Connection connection = getConnection())
		{
			PreparedStatement preparedStatement = connection.prepareStatement(sanitizeSqlString(sql));
			if (parameters != null)
			{
				injectParameterToPreparedStatement(parameters, preparedStatement);
			}
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet != null)
			{
				return (T) sqliteObjectAssembler.assemble(resultSet);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public  List getList(String sql, Map parameters,
	                           ISqliteObjectAssembler sqliteObjectAssembler) throws SQLException
	{
		try (Connection connection = getConnection())
		{
			PreparedStatement preparedStatement = connection.prepareStatement(sanitizeSqlString(sql));
			if (parameters != null)
			{
				injectParameterToPreparedStatement(parameters, preparedStatement);
			}
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet != null)
			{
				List results = new ArrayList<>();
				while (resultSet.next())
				{
					try
					{
						results.add((T) sqliteObjectAssembler.assemble(resultSet));
					}
					catch (Exception exception)
					{
						logger.error(exception.getMessage());
					}
				}
				return results;
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public void createTable(String sql) throws SQLException
	{
		tableSqlExecutor(sql);
	}

	/*

	ALTER TABLE EXAMPLE:

	ALTER TABLE table RENAME TO temp_table;

	 */

	@Override
	public void alterTable(String sql) throws SQLException
	{
		tableSqlExecutor(sql);
	}

	@Override
	public void dropTable(String sql) throws SQLException
	{
		tableSqlExecutor(sql);
	}

	/**
	 * GENERIC TABLE CREATE/ALTER/DROP SQL EXECUTOR/HANDLER
	 *
	 * @param sql
	 * @throws SQLException
	 */
	private void tableSqlExecutor(String sql) throws SQLException
	{
		try (Connection connection = getConnection())
		{
			Statement statement = connection.createStatement();
			statement.execute(sql);
		}
	}

	/**
	 * THIS INJECTS THE HASH MAP INTO THE PREPARED STATEMENT
	 *
	 * @param parameters
	 * @param preparedStatement
	 * @throws SQLException
	 */
	private void injectParameterToPreparedStatement(Map parameters, PreparedStatement preparedStatement) throws SQLException
	{
		if (parameters != null && preparedStatement != null)
		{
			for (Map.Entry entry : parameters.entrySet())
			{
				preparedStatement.setObject((int) entry.getKey(), entry.getValue());
			}
		}
	}

	/**
	 * A LITTLE BIT OF RAINBOW, AND SUGAR STRING SANITATION
	 *
	 * @param sql
	 * @return
	 */
	private String sanitizeSqlString(String sql)
	{
		if (sql != null)
		{
			return sql.trim();
		}
		return "";
	}

	/**
	 * THIS WILL RETRIEVE THE LAST GENERATED ID IN SQLITE
	 *
	 * @param connection
	 * @return
	 * @throws SQLException
	 */
	private int getLastInsertedId(Connection connection) throws SQLException
	{
		if (connection != null)
		{
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("SELECT last_insert_rowid()");
			if (resultSet != null)
			{
				return resultSet.getInt(1);
			}
		}
		return 0;
	}

}