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:

   <source lang="java">

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);</source>

Connection"s Method to Print Native SQL

   <source lang="java">

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", "");
 }

}</source>

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

   <source lang="java">

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());
   }
 }

}</source>

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

   <source lang="java">

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</source>