JDBC API and connection

import java.sql.* ;  // for standard JDBC programs
import java.math.* ; // for BigDecimal and BigInteger support

try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ex) {
   System.out.println("Error: unable to load driver class!");
   System.exit(1);
}

try {
   Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
}
catch(ClassNotFoundException ex) {
   System.out.println("Error: unable to load driver class!");
   System.exit(1);
catch(IllegalAccessException ex) {
   System.out.println("Error: access problem while loading!");
   System.exit(2);
catch(InstantiationException ex) {
   System.out.println("Error: unable to instantiate driver!");
   System.exit(3);
}

try {
   Driver myDriver = new oracle.jdbc.driver.OracleDriver();
   DriverManager.registerDriver( myDriver );
}
catch(ClassNotFoundException ex) {
   System.out.println("Error: unable to load driver class!");
   System.exit(1);
}

jdbc:oracle:thin:@amrood:1521:EMP

String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
String USER = "username";
String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS);

DriverManager.getConnection(String url);

jdbc:oracle:driver:username/password@database

String URL = "jdbc:oracle:thin:username/password@amrood:1521:EMP";
Connection conn = DriverManager.getConnection(URL);

DriverManager.getConnection(String url, Properties info);

import java.util.*;

String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
Properties info = new Properties( );
info.put( "user", "username" );
info.put( "password", "password" );

Connection conn = DriverManager.getConnection(URL, info);

conn.close();

• Create database

  To create the EMP database, use the following steps:
  

  Step 1:

  Open a Command Prompt and change to the installation directory as follows:
  

  C:\> C:\>cd Program Files\MySQL\bin C:\Program Files\MySQL\bin>

  Note: The path to mysqld.exe may vary depending on the install location of MySQL on your system. You can also check documentation on how to start and stop your database server.
  

  Step 2:

  Start the database server by executing the following command, if it is already not running.
  

  C:\Program Files\MySQL\bin>mysqld C:\Program Files\MySQL\bin>

  Step 3:

  Create the EMP database by executing the following command
  

  C:\Program Files\MySQL\bin> mysqladmin create EMP -u root -p Enter password: ******** C:\Program Files\MySQL\bin>

  Create Table

  To create the Employees table in EMP database, use the following steps:
  

  Step 1:

  Open a Command Prompt and change to the installation directory as follows:
  

  C:\> C:\>cd Program Files\MySQL\bin C:\Program Files\MySQL\bin>

  Step 2:

  Login to database as follows
  

  C:\Program Files\MySQL\bin>mysql -u root -p Enter password: ******** mysql>

  Step 3:

  Create the table Employee as follows:
  

  mysql> use EMP; mysql> create table Employees -> ( -> id int not null, -> age int not null, -> first varchar (255), -> last varchar (255) -> ); Query OK, 0 rows affected (0.08 sec) mysql>

  Create Data Records

  Finally you create few records in Employee table as follows:
  

  mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali'); Query OK, 1 row affected (0.05 sec) mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal'); Query OK, 1 row affected (0.00 sec) mysql>

• JDBC- Storage Procedure

  I have explained how to use Stored Procedures in JDBC while discussing JDBC - Statements. This tutorial is similar to that section but it would give you additional information about JDBC SQL escape syntax.
  Just as a Connection object creates the Statement and PreparedStatement objects, it also creates the CallableStatement object which would be used to execute a call to a database stored procedure.
  

  Creating CallableStatement Object:

  Suppose, you need to execute the following Oracle stored procedure:
  

  CREATE OR REPLACE PROCEDURE getEmpName (EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS BEGIN SELECT first INTO EMP_FIRST FROM Employees WHERE ID = EMP_ID; END;

  
  NOTE: Above stored procedure has been written for Oracle, but we are working with MySQL database so let us write same stored procedure for MySQL as follows to create it in EMP database:
  

  DELIMITER $$ DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$ CREATE PROCEDURE `EMP`.`getEmpName` (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255)) BEGIN SELECT first INTO EMP_FIRST FROM Employees WHERE ID = EMP_ID; END $$ DELIMITER ;

  Three types of parameters exist: IN, OUT, and INOUT. The PreparedStatement object only uses the IN parameter. The CallableStatement object can use all three.
  Here are the definitions of each:
  

  Parameter	Description
  IN	A parameter whose value is unknown when the SQL statement is created. You bind values to IN parameters with the setXXX() methods.
  OUT	A parameter whose value is supplied by the SQL statement it returns. You retrieve values from theOUT parameters with the getXXX() methods.
  INOUT	A parameter that provides both input and output values. You bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

  The following code snippet shows how to employ the Connection.prepareCall() method to instantiate a CallableStatement object based on the preceding stored procedure:
  

  CallableStatement cstmt = null; try { String SQL = "{call getEmpName (?, ?)}"; cstmt = conn.prepareCall (SQL); . . . } catch (SQLException e) { . . . } finally { . . . }

  The String variable SQL represents the stored procedure, with parameter placeholders.
  Using CallableStatement objects is much like using PreparedStatement objects. You must bind values to all parameters before executing the statement, or you will receive an SQLException.
  If you have IN parameters, just follow the same rules and techniques that apply to a PreparedStatement object; use the setXXX() method that corresponds to the Java data type you are binding.
  When you use OUT and INOUT parameters you must employ an additional CallableStatement method, registerOutParameter(). The registerOutParameter() method binds the JDBC data type to the data type the stored procedure is expected to return.
  Once you call your stored procedure, you retrieve the value from the OUT parameter with the appropriate getXXX() method. This method casts the retrieved value of SQL type to a Java data type.
  

  Closing CallableStatement Obeject:

  Just as you close other Statement object, for the same reason you should also close the CallableStatement object.
  A simple call to the close() method will do the job. If you close the Connection object first it will close the CallableStatement object as well. However, you should always explicitly close the CallableStatement object to ensure proper cleanup.
  

  CallableStatement cstmt = null; try { String SQL = "{call getEmpName (?, ?)}"; cstmt = conn.prepareCall (SQL); . . . } catch (SQLException e) { . . . } finally { cstmt.close(); }

• Database connection - Microsoft® SQL Server

  
  Use the New Database Connection wizard to create a JDBC connection to Microsoft SQL Server.
  There are three types of drivers for Microsoft SQL Server: DataDirect SequeLink Driver, Microsoft DataDirect Connect JDBC Driver and WebSphere® Connect JDBC Driver.
  When connecting to MS SQL Server you must have database role membership in the roles 'public' and 'db_datareader' in order to retrieve tables that are not owned by you.
  To change the database role membership:
  Start the MS SQL Server Enterprise Manager.
  Under the Console Root, navigate to the Users folder under the database you are working with. A list of users for this database is shown in the right hand pane.
  Right-click on the user which you would like to change.
  Select Properties.
  Select the appropriate database role memberships in the list.
  Select db_datareader for read access.
  Select db_datawriter for insert, update and delete access.
  Select db_ddladmin for table and view delete access.
  Click OK for the changes to take affect.

• Database connection - MySQL

  
  Use the New Database Connection wizard to create a JDBC connection to MySQL.
  You may obtain both of these JDBC drivers at www.mysql.com/downloads/api-jdbc.html.
  In order to use the MySQL drivers, you need to start the service wherever MySQL is installed.
  There are two types of MySQL drivers: MM.MySQL JDBC App Driver and MM.MySQL JDBC Net Driver.
  Use the MM.MySQL JDBC App Driver if you want to connect to MM.MySQL databases that are cataloged on your local machine. Use the MM.MySQL JDBC Net Driver if you want to connect remotely to MM.MySQL servers that are installed on the same platform and are of the same version.

•  

   connection - Oracle8i or Oracle9i with the Oracle JDBC Driver

  
  Use the New Database Connection wizard to connect to Oracle8i or Oracle9i with an Oracle JDBC Driver.
  The Oracle JDBC Driver is shipped with the Oracle Server. You may also obtain this JDBC driver at www.oracle.com/.
  To use this JDBC driver, follow these steps:
  Access the DB Servers view via the Data perspective (Window > Open Perspective > Data).
  Anywhere in the empty area of the DB Server view, click New Connection to open the New Database Connection wizard.
  Type the name of the database connection the Connection name field.
  Type the name of the database that you want to connect to in the Database field.
  If required, type the user ID and password.
  Select either Oracle8i or Oracle9i from the Database vendor type pull-down list.
  For Oracle8i, select Oracle THIN JDBC Driver from the JDBC driver pull-down list. For Oracle9i, select Oracle JDBC Driver from the JDBC driver pull-down list.
  Type the port number to the database. Default is 1521.
  For Oracle8i, the JDBC driver class field is pre-filled with oracle.jdbc.driver.OracleDriver. For Oracle9i, the JDBC driver class field is pre-filled with oracle.jdbc.OracleDriver. This field is disabled. However, if Other Driver was selected from JDBC driver pull-down list, then the JDBC driver class field is enabled allowing you to either type or select the appropriate JDBC driver class.
  Type the class location containing the JDBC driver in the Class location field. For example, WS_instdir\runtimes\base_v5\lib\sljc.jar; (where WS_instdir is the path where you have installed this product).
  The Connection URL field is pre-filled and disabled with the URL connection. This URL is in the form of jdbc:oracle:thin:@host_name: port_number: database_name. However, if Other Driver was selected from JDBC driver pull-down list, then the Connection URL field is enabled allowing you to either type or select the appropriate URL connection.
  Click Filters to limit the database schemas that are loaded. For more information about creating filters, refer to Creating a JDBC connection filter.
  Click Finish to create the database connection. The database meta data is collected and used to create a transient model. This model can be viewed in the DB Servers view. You can expand the folders under the database to browse its design.

• JDBC example

  
  The code explains you how creating and closing of connection is done.
  For this program code we have a class Jdbc Connection, We have a list of method required to connect and close the connection. Before the implementation of class we need to
  import a package java.sql.* needed for network interface between url and back end.
  Loading a driver is done by calling a class.forName ( ) and accept the driver class as argument.
  DriverManager.getConnection ( ) :This method returns you an connection object.DriverManager.getConnection built a connection with database.
  
  Using this method we establish a connection between url and database. Once the connection is built,println print the connection is created
  
  con.close ( ) : An object of connection is used to close the connection between database and url.
  
  Finally the println print the connection is closed.In case there is an exception in try block, during the establishment of connection between the front end in java application(url) and backend. The catch block is responsible for handling the exception.
  
  JdbcConnection.java
  

  import java.sql.*; public class JdbcConnection { public static void main(String args[]) { Connection con = null; String url = "jdbc:mysql://localhost:3306/"; String db = "komal"; String driver = "com.mysql.jdbc.Driver"; String user = "root"; String pass = "root"; try { Class.forName(driver); con = DriverManager.getConnection(url + db, user, pass); System.out.println("jdbc driver for mysql : " + driver); System.out.println("Connection url : " + url + db); System.out.println("Connection is created..."); con.close(); System.out.println("Connection is closed..."); } catch (Exception e) { System.out.println(e); } } }

  Output
  

  Connection is created... Connection is closed...