Introduction to Database Connectivity

A database is an organized collection of data. There are many different strategies for organizing data to facilitate easy access and manipulation. A database management system (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data for many users. Database management systems allow for the access and storage of data without concern for the internal representation of data.

A database is an organized, integrated collection of data held on a computer.

A relational database management system (RDBMS) is a logical representation of data that allows the data to be accessed without consideration of its physical structure. A relational database stores data in tables. For example, the table name is Employee, and its primary purpose is to store the attributes of an employee i.e. employee name, designation, salary etc. Tables are composed of rows, and rows are composed of columns in which values are stored.

A language called SQL—pronounced “sequel,” or as its individual letters—is the international standard language used almost universally with relational databases to perform queries (i.e., to request information that satisfies given criteria) and to manipulate data.

Some popular relational database management systems (RDBMSs) are Microsoft SQL Server, Oracle, Sybase, IBM DB2, Informix, PostgreSQL and MySQL. The JDK now comes with a pure-Java RDBMS called Java DB—Sun’s version of Apache Derby. Java programs communicate with databases and manipulate their data using the JDBC™ API. A JDBC driver enables Java applications to connect to a database in a particular DBMS and allows you to manipulate that database using the JDBC API.

Mysql is a free open source powerful database which is most widely used. Mysql offers all important database features. Install Mysql database and create a sample “Test” database. Create an Employee table in Test database and insert some sample records in the table. Employee table should have id, number, firstName, lastName, designation, and salary columns.

Also download JDBC driver MySQL Connector/J from www.mysql.com. MySQL Connector/J is distributed as a .zip or .tar.gz archive containing the sources, the class files, and the JAR archive named mysql-connector-java-[version]-bin.jar. Extract the zip file. Once you have extracted the distribution archive, you can install the driver by placing mysql-connector-java-[version]-bin.jar in your classpath, by adding the full path to it to your CLASSPATH environment variable.

Example

1.  /**
2.  * @(#)DatabaseDemo.java
3.  *
4.  *
5.  * @author
6.  * @version 1.00 2009/11/23
7.  */
8.
9.  import java.sql.Connection;
10. import java.sql.Statement;
11. import java.sql.DriverManager;
12. import java.sql.ResultSet;
13. import java.sql.ResultSetMetaData;
14. import java.sql.SQLException;
15.
16. public class DatabaseDemo {
17.        
18.    static final String DRIVER = "com.mysql.jdbc.Driver";
19.    static final String DATABASE_URL = "jdbc:mysql://localhost/test";
20.
21.    /**
22.     * Creates a new instance of DatabaseDemo.
23.     */
24.    public DatabaseDemo() {
25.    }
26.    
27.    /**
28.     * @param args the command line arguments
29.     */
30.    public static void main(String[] args) {
31.        // TODO code application logic here
32.    
33.         Connection connection = null; // manages connection
34.         Statement statement = null; // query statement
35.         ResultSet resultSet = null; // manages results
36.
37.         // connect to database books and query database
38.         try {
39.            
40.            // load the driver class
41.            Class.forName( DRIVER );
42.            // establish connection to database
43.            connection =
44.                DriverManager.getConnection( DATABASE_URL, "root", "" );
45.                
46.            // create Statement for querying database
47.            statement = connection.createStatement();
48.            
49.            // query database
50.            resultSet = statement.executeQuery(
51.            "SELECT number, firstName, lastName, salary FROM employee" );
52.            
53.            ResultSetMetaData metaData = resultSet.getMetaData();
54.            
55.            int numberOfColumns = metaData.getColumnCount();
56.            
57.            System.out.println( "Employee Table of Test Database:n" );
58.            
59.            for ( int i = 1; i <= numberOfColumns; i++ )
60.                System.out.printf( "%-8st", metaData.getColumnName( i ));
61.            
62.            System.out.println();
63.                
64.            while(resultSet.next( )) {
65.                
66.                 for ( int i = 1; i <= numberOfColumns; i++ )
67.                         System.out.printf( "%-8st", resultSet.getObject( i ));
68.                
69.                System.out.println();
70.                        
71.            } // end while
72.            
73.     } // end try            
74.            
75.    catch(SQLException sqlException ) {
76.        
77.            sqlException.printStackTrace();
78.            
79.    } // end catch
80.    catch(ClassNotFoundException classNotFound) {
81.        
82.        classNotFound.printStackTrace();
83.        
84.    } // end catch
85.    
86.    // ensure resultSet, statement and connection are closed
87.    finally {
88.        
89.        try    {
90.            resultSet.close();
91.            statement.close();
92.            connection.close();
93.        } // end try
94.        catch ( Exception exception )    {
95.            
96.            exception.printStackTrace();
97.            
98.        } // end catch
99.    } // end finally
100.    
101.  } // end main
102. } // end class

Output

Employee Table of Test Database: