Link Search Menu Expand Document Documentation Menu

Utilizing JDBC

After focusing on some other popular programming languages such as Python and C#, we also want to sit back and figure out how to use Babelfish in conjunction with Java and JDBC. Again, some basic operations will be shown to describe how code can be written.

As we have seen before, there is no real difference between writing JDBC client code for Microsoft SQL Server and Babelfish. Still, let’s dive in and take a look at some examples:

Sample code using JDBC

To compile the Java code you will need the Microsoft SQL Server JDBC driver. For the sake of this example, we have used the version 9.4 (mssql-jdbc-9.4.0.jre11.jar). However, since the example features pretty basic code, it should be possible to run things with other versions of the driver as well.

Here is the code:

package sample;

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

public class sample {
	public static void main(String[] args) {
		Connection connection;
		// Setting up MSSQL Credentials
		String connectionUrl =
			"jdbc:sqlserver://PUT_HOSTNAME_HERE;"
			+ "database=PUT_NAME_OF_DATABASE_HERE;"
			+ "user=PUT_USERNAME_HERE;"
			+ "password=PUT_PASSWORD_HERE;";

		try {
			// Trying to establish connection
			connection = DriverManager.getConnection(connectionUrl);
			System.out.println("Connection established for select examples!\n");
			
			// Select values example
			select_all(connection);
			
			// Transaction example
			connection.setAutoCommit(false);
			
			// Insert values into sample table
			try {
				connection.setAutoCommit(false);
			
				String insert = "INSERT INTO sample VALUES('Max', 'Mustermann', '1020')";
				Statement statement = connection.createStatement();
				statement.execute(insert);
				
				insert = "INSERT INTO sample VALUES('Erika', 'Musterfrau', '1021')";
				statement.execute(insert);
				
				connection.commit();
				System.out.println("\nInsert successful!\n");
			}
			catch (SQLException e){
				connection.rollback();
				System.out.println("\nInsert failed!\n");
			}
			
			select_all(connection);
			
			connection.setAutoCommit(true);
			
			// Removing inserted values and displaying rowcount
			try {
				String delete = "DELETE FROM sample WHERE vorname = 'Max' or vorname = 'Erika'";
				Statement statement = connection.createStatement();
				int rowcount = statement.executeUpdate(delete);
				
				System.out.println("\nDeleted Rows: " + rowcount + "\n");
				
				// Select column names
				String select = "SELECT * FROM sample";
				ResultSet result = statement.executeQuery(select);
				
				String value = "";
				
				for(int i = 1; i <= result.getMetaData().getColumnCount(); i++) {
					value += result.getMetaData().getColumnName(i) + " ";
				}
				
				System.out.println(value);
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private static void select_all(Connection connection) {
		try {
			Statement statement = connection.createStatement();
			
			String select = "SELECT * FROM sample";
			ResultSet result = statement.executeQuery(select);
			
			while(result.next()) {
				String value = "";
				for(int i = 1; i <= result.getMetaData().getColumnCount(); i++) {
					value += result.getString(i) + " ";
				}
				System.out.println(value);
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

First of all, a standard JDBC connection is created. JDBC is an abstraction layer, so the main magic is actually in the connect string: ‘jdbc:sqlserver’ will tell our program to use the Microsoft SQL Server driver to connect to Babelfish.

Otherwise, the sample code is mostly trivial and pretty much represents a standard JDBC application.

Babelfish for PostgreSQL Links