Java Tutorial/Database/Connection

Connection Object

The Connection object has the following capabilities:

1. Creates SQL statements
2. Executes SQL queries, inserts, updates, and deletes
3. Handles commits and rollbacks
4. Provides metadata regarding the database connection

By default, a Connection object is in autocommit mode, which means it automatically commits changes after executing each statement.

Connection Object Requirements

ParameterDescriptiondriverNameJDBC driverdbURLDatabase URL (uniquely identifies a database)dbUsernameDatabase usernamedbPasswordDatabase user password

Connection Properties: Pass additional properties using java.util.Properties:

Properties properties = new java.util.Properties();
properties.put("user", "root");properties.put("password","rootp");
properties.put("useUnicode","true");
properties.put("characterEncoding","utf8");
String databaseURL = "jdbc:mysql://localhost/yourDB";
Connection conn = DriverManager.getConnection(databaseURL, properties);

Connection"s Method to Print Native SQL

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,myURL CHAR);");
    String str = conn.nativeSQL("insert into survey(id) values(02)");
    System.out.println(str);
    
    conn.rumit();    
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Passing "user" and "password" Parameters as a java.util.Properties Object to Connection object

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class Main {
  public static Connection getConnection(String dbURL, String user, String password)
      throws SQLException, ClassNotFoundException {
    Class.forName("com.mysql.jdbc.Driver");
    Properties props = new Properties();
    props.put("user", user);
    props.put("password", password);
    props.put("autoReconnect", "true");
    return DriverManager.getConnection(dbURL, props);
  }
  public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = getConnection("jdbc:mysql://localhost/empDB", "root", "pass");
    } catch (Exception ex) {
      System.out.println("SQLException: " + ex.getMessage());
    }
  }
}

Test to See if Your Connection is Alive

1. For Oracle database: you may use the Connection object with query of "select 1 from dual"; if the query returns the result, then it is a valid Connection object.
2. For MySQL database: you may use the Connection object with query of "select 1"; if the query returns the result, then it is a valid Connection object.

Vendor URL Formats

Vendor                   Driver Name                                      URL Sample
Oracle 8i                oracle.jdbc.driver.OracleDriver                  jdbc:oracle:thin:@localhost:1521:yourDatabase
Oracle 9i                oracle.jdbc.driver.OracleDriver                  jdbc:oracle:thin:@localhost:1521:yourDatabase
MySQL                    org.gjt.mm.mysql.Driver                          jdbc:mysql://localhost/yourDatabase
Microsoft Access         sun.jdbc.odbc.JdbcOdbcDriver                     jdbc:odbc:scorpian

Sybase(jConnect 5.2)     com.sybase.jdbc2.jdbc.SybDriver                  jdbc:sybase:Tds:scorpian:2638

MS SQL Server 2000       com.microsoft.jdbc. sqlserver.SQLServerDriver    jdbc:microsoft:sqlserver://localhost:1433
MS SQL Server 2000       weblogic.jdbc.mssqlserver4.Driver                jdbc:weblogic:mssqlserver4:database@ localhost:port
IBM DB2                  COM.ibm.db2.jdbc.net.DB2Connection               jdbc:db2://localhost:6789/yourDatabase