JDBC via Cloud Connector TCP

README.md

Files for Tutorial on: (todo: insert link as soon as published; let me know if i forgot)

access_control.zip

ConnectivityServlet.java

package com.sap.connectivity;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.net.Authenticator;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Base64;

import javax.annotation.Resource;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;
import com.google.gson.stream.JsonWriter;
import com.mysql.cj.core.exceptions.MysqlErrorNumbers;
import com.sap.cloud.account.TenantContext;

public class ConnectivityServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public ConnectivityServlet() {
		super();
	}
	
	@Resource
	private TenantContext tenantContext;
	
	@Override
	public void init(ServletConfig config) throws ServletException {
		super.init(config);
		
		final String accountid  = tenantContext.getTenant().getAccount().getId();
		final String locationid = config.getInitParameter("locationid");
		
		final String encodedSubaccount = new String(Base64.getEncoder().encode(accountid.getBytes()));
		final String encodedLocationId = new String(Base64.getEncoder().encode((locationid == null ? "" : locationid).getBytes()));
		Authenticator.setDefault(new Authenticator() {
			@Override
			protected java.net.PasswordAuthentication getPasswordAuthentication() {
				return new java.net.PasswordAuthentication("1." + encodedSubaccount + "." + encodedLocationId, new char[] {});
			}
		});
	}
	
	private static final String HEADER_USERNAME = "x-database-username";
	private static final String HEADER_PASSWORD = "x-database-password";
	private static final String DEFAULT_USERNAME = "root";
	private static final String DEFAULT_PASSWORD = "";

	private static final Base64.Decoder decoder = Base64.getDecoder();
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		try (Connection conn = DBConnection.getConnection(
				getDecodedHeaderOrDefault(request, HEADER_USERNAME, DEFAULT_USERNAME),
				getDecodedHeaderOrDefault(request, HEADER_PASSWORD, DEFAULT_PASSWORD))
			){
			String sql = "SELECT Country.Code, Country.Name, sum(City.Population) / Country.Population FROM City JOIN Country on City.CountryCode = Country.Code group by CountryCode order by Country.Name desc"; //having Country.Name LIKE \"U%\"";

			if (conn != null) {

				Statement statement1 = conn.createStatement();

				ResultSet rs = statement1.executeQuery(sql);
				
				try(JsonWriter writer = new JsonWriter(response.getWriter())) {
					writer.beginArray();
					
					while (rs.next()) {
						writer.beginObject();
						
						writer.name("countryCode").value(rs.getString(1));
						writer.name("countryName").value(rs.getString(2));
						writer.name("populationFraction").value(rs.getFloat(3));
						
						writer.endObject();
					}
					
					writer.endArray();
				}

			}

		} catch (Exception e) {
			
			StringWriter writer = new StringWriter();
			e.printStackTrace(new PrintWriter(writer));
			
			ErrorDTO dto = new ErrorDTO();
			dto.msg = writer.toString();
			dto.type = getErrorTypeFromException(e);
			
			dto.msg = ((SQLException) e).getErrorCode() + " " + dto.msg;
			System.err.println(dto.msg);
			
			if(dto.type == ErrorType.LOGIN) {
				dto.msg = e.getMessage();
				response.setStatus(401);
			}
			else {
				response.setStatus(500);
			}
			
			Gson gson = new Gson();
			gson.toJson(dto, response.getWriter());
		}
	}
	
	private static String getDecodedHeaderOrDefault(HttpServletRequest request, String headername, String defaultValue) {
		String value = request.getHeader(headername);
		System.out.println(headername + " " + value);
		if(value == null) {
			return defaultValue;
		}
		return new String(decoder.decode( value ));
	}
	
	private class ErrorDTO {
		private ErrorType type;
		private String msg;
	}
	
	private enum ErrorType {
		LOGIN, SQL, UNKOWN;
	}

	private ErrorType getErrorTypeFromException(Exception e) {
		
		if(! (e instanceof SQLException)) {
			return ErrorType.UNKOWN;
		}
		
		SQLException sqlex = (SQLException) e;
		if(sqlex.getErrorCode() == MysqlErrorNumbers.ER_ACCESS_DENIED_ERROR ||
				sqlex.getErrorCode() == MysqlErrorNumbers.ER_DBACCESS_DENIED_ERROR ) {
			return ErrorType.LOGIN;
		}
		return ErrorType.SQL;
	}
}

DBConnection.java

package com.sap.connectivity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.cj.core.conf.PropertyDefinitions;

public class DBConnection {

	private static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
	private static final String URL = "jdbc:mysql://virtualhost:3306/world";

	public static Connection getConnection(String username, String password) throws SQLException {
		try {
			Class.forName(DRIVER_CLASS);
			Properties props = new Properties();
			props.setProperty(PropertyDefinitions.PNAME_socketFactory, SocketFactory.class.getCanonicalName());
			props.setProperty(PropertyDefinitions.PNAME_user, username);
			if(password.length() > 0) {
				props.setProperty(PropertyDefinitions.PNAME_password, password);
			}
			return DriverManager.getConnection(URL,  props);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		}
	}

}

SocketFactory.java

package com.sap.connectivity;

import java.io.IOException;
import java.net.InetAddress;
import java.net.InetSocketAddress;
import java.net.Socket;
import java.net.SocketException;
import java.util.Properties;

import com.mysql.cj.core.conf.PropertyDefinitions;
import com.mysql.cj.core.io.SocksProxySocketFactory;


/**
 * This is mostly a copy of StandardSocketFactory
 */
public class SocketFactory extends SocksProxySocketFactory {

	@Override
	public Socket connect(String hostname, int portNumber, Properties props, int loginTimeout) throws SocketException, IOException {

		if(!props.containsKey(PropertyDefinitions.PNAME_socksProxyHost)) {
			props.setProperty(PropertyDefinitions.PNAME_socksProxyHost, "localhost");
			props.setProperty(PropertyDefinitions.PNAME_socksProxyPort, "20004");
		}
		
		this.loginTimeoutCountdown = loginTimeout;

		if (props != null) {
			this.host = hostname;
			this.port = portNumber;

			String localSocketHostname = props.getProperty(PropertyDefinitions.PNAME_localSocketAddress);
			InetSocketAddress localSockAddr = null;
			if (localSocketHostname != null && localSocketHostname.length() > 0) {
				localSockAddr = new InetSocketAddress(InetAddress.getByName(localSocketHostname), 0);
			}

			String connectTimeoutStr = props.getProperty(PropertyDefinitions.PNAME_connectTimeout);

			int connectTimeout = 0;

			if (connectTimeoutStr != null) {
				try {
					connectTimeout = Integer.parseInt(connectTimeoutStr);
				} catch (NumberFormatException nfe) {
					throw new SocketException("Illegal value '" + connectTimeoutStr + "' for connectTimeout");
				}
			}

			if (this.host != null) {
				String[] possibleAddresses = this.host.split(",");

				if (possibleAddresses.length == 0) {
					throw new SocketException("No addresses for host");
				}

				// save last exception to propagate to caller if connection
				// fails
				SocketException lastException = null;

				// Need to loop through all possible addresses. Name lookup may
				// return multiple addresses including IPv4 and IPv6 addresses.
				// Some versions of
				// MySQL don't listen on the IPv6 address so we try all
				// addresses.
				for (int i = 0; i < possibleAddresses.length; i++) {
					try {
						this.rawSocket = createSocket(props);

						configureSocket(this.rawSocket, props);

						InetSocketAddress sockAddr = InetSocketAddress.createUnresolved(possibleAddresses[i], this.port);
						// bind to the local port if not using the ephemeral
						// port
						if (localSockAddr != null) {
							this.rawSocket.bind(localSockAddr);
						}

						this.rawSocket.connect(sockAddr, getRealTimeout(connectTimeout));

						break;
					} catch (SocketException ex) {
						lastException = ex;
						resetLoginTimeCountdown();
						this.rawSocket = null;
					}
				}

				if (this.rawSocket == null && lastException != null) {
					throw lastException;
				}

				resetLoginTimeCountdown();

				return this.rawSocket;
			}
		}

		throw new SocketException("Unable to create socket");
	}

	/**
	 * Configures socket properties based on properties from the connection
	 * (tcpNoDelay, snd/rcv buf, traffic class, etc).
	 * 
	 * @param props
	 * @throws SocketException
	 * @throws IOException
	 */
	private void configureSocket(Socket sock, Properties props) throws SocketException, IOException {
		sock.setTcpNoDelay(Boolean.valueOf(props.getProperty(PropertyDefinitions.PNAME_tcpNoDelay, TCP_NO_DELAY_DEFAULT_VALUE)).booleanValue());

		String keepAlive = props.getProperty(PropertyDefinitions.PNAME_tcpKeepAlive, TCP_KEEP_ALIVE_DEFAULT_VALUE);

		if (keepAlive != null && keepAlive.length() > 0) {
			sock.setKeepAlive(Boolean.valueOf(keepAlive).booleanValue());
		}

		int receiveBufferSize = Integer.parseInt(props.getProperty(PropertyDefinitions.PNAME_tcpRcvBuf, TCP_RCV_BUF_DEFAULT_VALUE));

		if (receiveBufferSize > 0) {
			sock.setReceiveBufferSize(receiveBufferSize);
		}

		int sendBufferSize = Integer.parseInt(props.getProperty(PropertyDefinitions.PNAME_tcpSndBuf, TCP_SND_BUF_DEFAULT_VALUE));

		if (sendBufferSize > 0) {
			sock.setSendBufferSize(sendBufferSize);
		}

		int trafficClass = Integer.parseInt(props.getProperty(PropertyDefinitions.PNAME_tcpTrafficClass, TCP_TRAFFIC_CLASS_DEFAULT_VALUE));

		if (trafficClass > 0) {
			sock.setTrafficClass(trafficClass);
		}
	}

}

map-app.war