Simple way to connect to database - Java JDBC explained with examples

In Java ,  JDBC (Java DataBase Connectivity) API is used for connecting to relational databases from clients. This JDBC API uses Drivers (Java library files) and Connection Strings/URLs to connect to the databases.

How do you connect to SQL server to execute some queries. See the code below to have an idea.

DataSource object to get a connection


SQLServer
public Connection getConnection() throws SQLException {


    Properties connectionProps = new Properties();
    connectionProps.put("user", this.userName);
    connectionProps.put("password", this.password);
   
    String url = "jdbc:sqlserver://localhost:3306/database"; //format of MySQL connection    string
// Connection con = datasource.getConnection(username, password);  OR
    Connection con = DriverManager.getConnection(url, "username", "password");     
    System.out.println("Connected to database");

    return con;
}


public static void viewTable(Connection con, String dbName)
    throws SQLException {

    Statement stmt = null;
    String query = "select NAME, ID, PRICE, " +
                   "SALES, TOTAL " +
                   "from " + dbName + ".COFFEES";
    try {
      stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            String coffeeName = rs.getString("NAME");
            int supplierID = rs.getInt("ID");
            float price = rs.getFloat("PRICE");
            int sales = rs.getInt("SALES");
            int total = rs.getInt("TOTAL");
       rs.moveToInsertRow();
       rs.updateString("NAME", coffeeName);
       rs.updateInt("ID", supplierID);
       rs.updateFloat("PRICE", price);
       rs.updateInt("SALES", sales);
       rs.updateInt("TOTAL", total);
       rs.insertRow();
       rs.beforeFirst();
            System.out.println(coffeeName + "\t" + supplierID +
                               "\t" + price + "\t" + sales +
                               "\t" + total);
        }
    } catch (SQLException e ) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

Classes and Methods used

DriverManager.getConnection(url, "username", "password")
datasource.getConnection(username, password); 

connection.createStatement();
connection.close()

 
statement.executeQuery(query);

ResultSet.next()
ResultSet.getString("NAME");
ResultSet.getInt("SALES");
ResultSet.getFloat("PRICE");
ResultSet.moveToInsertRow();
ResultSet.insertRow();
ResultSet.beforeFirst();
ResultSet.updateString("NAME", coffeeName);
ResultSet.updateInt("ID", supplierID);
ResultSet.updateFloat("PRICE", price);


MySQL

try{
    Class.forName("com.mysql.jdbc.Driver").newInstance(); //using MySQL JDBC Driver
    String url = "jdbc:mysql://hostname:port/database"; //format of MySQL connection string
    conn = DriverManager.getConnection(url, "username", "password");  
    
} catch (Exception e){...}
finally {
    conn.close();
}