Skip to content

Instantly share code, notes, and snippets.

@phil9909
Last active August 25, 2022 02:11
Embed
What would you like to do?
JDBC via Cloud Connector TCP

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

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;
}
}
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;
}
}
}
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);
}
}
}
This file has been truncated, but you can view the full file.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment