Connecting to SQL with Java

File: dcris.properties

# DCRIS database.
database=dcris
host=rsbohr.wlu.ca
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://bohr.wlu.ca/dcris
user=dcris
password=dcris

The connection URL consists of the protocol//[hosts][/database][?properties]. In the above case the protocol is:

jdbc:mysql:

Java's DriverManager class manages the establishment of connections. Every different type of database has its own driver manager.

MySQL example:

  // Do not import com.mysql.cj.jdbc.*, register it with a DriverManager

    private final Properties dbProperties = new Properties();
    private Connection conn = null;
    private ResultSetMetaData metaData = null;
    private ResultSet resultSet = null;
    private String dbDriver = null;
    private String dbURL = null;
    private String errorString = null;

    // Read the properties file.
    final InputStream is = this.getClass().getResourceAsStream("dcris.properties");
    this.dbProperties.load(is);
    this.dbDriver = this.dbProperties.getProperty("driver");
    this.dbURL = this.dbProperties.getProperty("url");

    // OR: Get the user and login from somwhere external and add them to the properties
    this.dbProperties.setProperty("user", user);
    this.dbProperties.setProperty("password", password);

    // Register the driver
    Class.forName(this.dbDriver).newInstance();
    // Make a connection
    this.conn = DriverManager.getConnection(this.dbURL, this.dbProperties);

    String statement = "SELECT * FROM keyword WHERE keyword_id = ?";

    PreparedStatement pstmt = this.conn.prepareStatement(statement);
    // Set the first parameter value - set*Type*(index, value of correct type)
    pstmt.setInt(1, 17);
    this.resultSet = pstmt.executeQuery();
    this.metaData = pstmt.getMetaData();
    // Process the result set and meta data (if desired)

    public static void displayData(final ResultSetMetaData metaData,
            final ResultSet rs) {
        try {
            while (rs.next()) {
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    final String column = metaData.getColumnLabel(i);
                    System.out.print(rs.getString(column) + "\t");
                }
                System.out.println();
            }
        } catch (final SQLException e) {
            System.err.println(e.toString());
        }
    }

For Oracle:

DriverManager - oracle.jdbc.driver.OracleDriver
protocol - jdbc:oracle:thin:

Connecting to SQL with VBA

Set conn = New ADODB.Connection

conn.Open "DRIVER={MySQL ODBC 8.0 Unicode Driver}" _
          & ";SERVER=rsbohr.wlu.ca" _
          & ";DATABASE=dcris" _
          & ";UID=dcris" _
          & ";PWD=dcris"

    ' Reset the other connection objects.
    Set cmd = New ADODB.Command
    Set pm = New ADODB.Parameter
    Set rs = New ADODB.Recordset

    With cmd
        .ActiveConnection = conn
        .CommandText = "SELECT * FROM keyword WHERE keyword_id = ?"
        .CommandType = adCmdText
        .Prepared = True
        .Parameters.Append .CreateParameter("keyword_id", adInteger, adParamInput, 6)
    End With

    Set startCell = Range("A5")
    ' Assign the new parameter values
    cmd("keyword_id") = 11
     Set rs = cmd.Execute
    ' Copy the data to the spreadsheet
    startCell.Offset(1, 0).CopyFromRecordset rs