Connecting to SQL Databases via JDBC/ODBC — Access Your Data Securely from Applications

A Java application and an Excel report need access to the same school database. Each client needs the correct driver and connection configuration.

JDBC and ODBC are database access interfaces used by clients through compatible drivers. A connection configuration identifies the server and authentication context.

JDBC uses Java driver APIs and connection URLs. ODBC uses a driver manager and either a configured data source name or a connection string. The interface standard does not make every SQL dialect identical; statements still run under the connected database’s rules.

Related concepts: Set Up SQL Tools and Users and Permissions and SQL with Java.

• JDBC (Java Database Connectivity) is a Java API to connect and execute SQL queries from Java applications.
• ODBC (Open Database Connectivity) is a standard interface that applications (like Excel or Python) use to connect to any SQL-compliant database via a driver.

Java · JDBC

Step 1: Add MySQL JDBC Driver

Download from: https://dev.mysql.com/downloads/connector/j/

Include the JAR file in your project’s classpath (via IDE or build tool).

Step 2: Java Code to Connect

import java.sql.*;

public class JDBCConnection {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String password = "your_password";

        try {
            Connection conn = DriverManager.getConnection(url, user, password);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM students");

            while (rs.next()) {
                System.out.println(rs.getInt("roll_no") + " - " + rs.getString("name"));
            }

            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

1 - Aarav  
2 - Diya

Python · sqlite3 or MySQL driver as specified

Step 1: Install ODBC Driver

• For MySQL: Install MySQL ODBC Connector from [https://dev.mysql.com/downloads/connector/odbc/](https://dev.mysql.com/downloads/connector/odbc/)
• For SQL Server: Use built-in Microsoft ODBC Driver

Step 2: Create DSN (Data Source Name)

1. Go to Control Panel → Administrative Tools → ODBC Data Sources (64-bit)
2. Click Add → Choose your driver (e.g., MySQL)
3. Give a name (e.g., school_dsn), set host, port, user, password, and database

Step 3: Use DSN in Excel

1. Open Excel → Data → Get Data → From Other Sources → From ODBC
2. Select school_dsn
3. Choose students table
4. Use filters and transforms as needed via Power Query

Step 4: Use DSN in Python (optional)

import pyodbc

conn = pyodbc.connect("DSN=school_dsn;UID=root;PWD=your_password")
cursor = conn.cursor()

cursor.execute("SELECT name, city FROM students WHERE class = '10A'")
for row in cursor.fetchall():
    print(row)

('Aarav', 'Pune')  
('Diya', 'Chennai')

• Java application pulling student records in real-time via JDBC
• Excel dashboard using ODBC to pull marks directly from MySQL
• Python scripts automating daily attendance upload via DSN
• Connecting centralized school database to admin tools securely

• Always close connections after use
• Use PreparedStatements (JDBC) to avoid SQL injection
• Limit query permissions for ODBC users (read-only if needed)
• Use connection pooling for scalable JDBC apps

Match driver architecture and platform requirements, secure credentials and validate TLS settings. A successful connection does not imply permission to every table. Do not deploy the sample server addresses or passwords unchanged.

After connecting through either interface, run a small check before testing access to the school tables.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT 1 AS connection_check;

This confirms statement submission and result retrieval. Table access, write privileges, schema compatibility and secure production settings need separate checks.

JDBC and ODBC are database access interfaces used by clients through compatible drivers. A connection configuration identifies the server and authentication context.

This confirms statement submission and result retrieval. Table access, write privileges, schema compatibility and secure production settings need separate checks.

Continue your understanding with Set Up SQL Tools and Users and Permissions and SQL with Java.

Source for the engine-specific behavior: MySQL Connector/J documentation.