How to use JDBC

 
 
Before explaining how to use JDBC you should understand the following terms:

Database
A database contains a collection of data structured in a way that information can be retrieved from it.

DBMS
DBMS is software that stores and retrieves the information in the database.

Java DataBase Connectivity (JDBC)
JDBC is a Java API which is used to manipulate relational databases. The set of core classes that form the JDBC API can be found in the java.sql package.

Metadata
A database also contains information about the data it stores and how it is organized. This information is called metadata.

Structured Query Language (SQL)
SQL is the standard language to manipulate relational databases.

JDBC driver
A JDBC driver is a class which implements the JDBC driver interface. There a four JDBC driver types:
  1. JDBC-ODBC bridge + ODBC driver
  2. Native-API partly-Java driver
  3. JDBC-Net pure Java driver
  4. native-protocol pure Java driver








JDBC code example.



Information
none

Operating system used
Windows XP Home Edition Version 5.1 SP 2

Software prerequisites
Java 2 Standard Edition (J2SE) SDK, version 1.2 or higher.

Procedure
  1. Create a database and tables, see quick guide "Create MySQL database and tables."

  2. Download the JDBC driver.
    Oracle
    1. Goto: http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc817.html
    2. Download Oracle8i 8.1.7.1 JDBC: oracle12.zip

    MySQL
    1. Goto: http://www.mysql.com/products/connector/j/index.html
    2. If you have installed MySQL 4.0.18,download mysql-connector-java-3.0.17-ga.zip
    3. Unzip the file and retrieve file mysql-connector-java-3.0.17-ga-bin.jar

  3. Place the drivers in directory c:\tools\jdbc_drivers

  4. Download JDBCOracleDemo.java file and place it in directory c:\demo

    import java.sql.*;

    public class JDBCOracleDemo {

       public static void main (String args []) throws SQLException{
          // Load the driver
          DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

          // Make a conection to the database
          // jdbc:oracle:thin:@host:port:SID,userid,password
          Connection conn = DriverManager.getConnection(
          "jdbc:oracle:thin:@localhost:1521:orcl", "root", "mysecret");

          Statement stmt = conn.createStatement();
          ResultSet rset = stmt.executeQuery("select * from USER");
          while (rset.next()){
             System.out.println (rset.getString(2)); // Print col 2
          }
          stmt.close();
       }
    }


    Note: The Oracle System Identifier (SID) or Oracle service name can be found in the tnsnames.ora file.



    Download JDBCMySQLDemo.java and place it in directory c:\demo

    import java.sql.*;

    public class JDBCMySQLDemo {

       public static void main (String args []) throws SQLException{
          // Load the MySQL driver
          DriverManager.registerDriver(new com.mysql.jdbc.Driver());
          // An alternative way
          // Class.forName("com.mysql.jdbc.Driver").newInstance();

          // Make a conection to the database
          // jdbc:mysql://host:port/database?user=name&password=pass
          Connection conn = DriverManager.getConnection(
          "jdbc:mysql://localhost:3306/mobilefishdb?user=root&password=mysecret");

          Statement stmt = conn.createStatement();
          ResultSet rset = stmt.executeQuery("select * from USER");
          while (rset.next()){
             System.out.println (rset.getString(2)); // Print col 2
          }
          stmt.close();
       }
    }


  5. Compile the java code.

    Oracle
    c:\demo> javac -classpath c:/tools/jdbc_drivers/oracle12.zip JDBCOracleDemo.java

    MySQL
    c:\demo> javac -classpath c:/tools/jdbc_drivers/mysql-connector-java-3.0.17-ga.zip JDBCMySQLDemo.java

  6. Run the class file.

    Oracle
    c:\demo> java -cp .;c:/tools/jdbc_drivers/oracle12.zip JDBCOracleDemo

    MySQL
    c:\demo> java -cp .;c:/tools/jdbc_drivers/mysql-connector-java-3.0.17-ga.zip JDBCMySQLDemo