Connecting to a database using JDBC

Two college friends asked me to create a "Hello World" level pragmatical explanation of how a java system can connect to a database and interact with data. Also i've been told, to use any java api or tool i wish to achieve this task.

I think the easiest way to make a demo of how to connect to a database, is to use the JDBC api, that is included in the JSE-SDK since years. Also i think that for a learning purposes and better understanding of how java works with databases, this would be the best(Instead of using ORM tools like Hibernate or JPA)

Ok, lets get started:

Prerequisites:
-The java SE JDK
-An IDE(I suggest you eclipse)
-A database driver(More info about JDBC drivers )
-A database server(For its simplicity to use, i would recommend you  Microsoft SQL 2005)

The source code:

1:  import java.sql.Connection;  
2:  import java.sql.DriverManager;  
3:  import java.sql.ResultSet;  
4:  import java.sql.SQLException;  
5:  import java.sql.Statement;  
6:  public class DB {  
7:   public void dbConnect() {  
8:   Connection con = null;  
9:   Statement stmt = null;  
10:   ResultSet rs = null;  
11:   String connectionURL = "jdbc:sqlserver://127.0.0.1;databaseName=forJDBC;user=george;password=george;";  
12:   try {  
13:    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  
14:    con = DriverManager.getConnection(connectionURL);  
15:    System.out.println("Connected!");  
16:    String SQL = "SELECT * FROM Samples";  
17:    stmt = con.createStatement();  
18:    rs = stmt.executeQuery(SQL);  
19:    while (rs.next()) {  
20:    System.out.println();  
21:    System.out.println(rs.getString(1) + "-" + rs.getString(2));  
22:    }  
23:   } catch (SQLException e) {  
24:    e.printStackTrace();  
25:   } catch (ClassNotFoundException e) {  
26:    e.printStackTrace();  
27:   } finally {  
28:    if (rs != null) {  
29:    try {  
30:     rs.close();  
31:    } catch (Exception e2) {  
32:     e2.printStackTrace();  
33:    }  
34:    if (stmt != null) {  
35:     try {  
36:     rs.close();  
37:     } catch (Exception e2) {  
38:     e2.printStackTrace();  
39:     }  
40:    }  
41:    if (con != null) {  
42:     try {  
43:     rs.close();  
44:     } catch (Exception e2) {  
45:     e2.printStackTrace();  
46:     }  
47:    }  
48:    }  
49:   }  
50:   }  
51:  }  

Explanation:


1- The first thing we need is to add the JDBC driver that will allow us to connect to the database into the projects builth path:

2.1-Make sure the database server is started.(Make sure you have logged in as an administrator)












2.2- Create a table in the database and add some data.
In this example i will not create the tables programatically. You can do it your selves with SQL Management Studio or similar tool. Just create a Database that meets the configuration above, and add two it a table called Sample with to VARCHAR fields. If you want to know more about how to programatically create tables with JDBC follow this link: http://www.roseindia.net/jdbc/jdbc-mysql/CreateTable.shtml


3-Create a connection URL:

1:  String connectionURL = "jdbc:sqlserver://127.0.0.1;databaseName=forJDBC;user=djoleP;password=djoleP;";  

4-Find the driver located path the ClassPath (if needed)

 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  

5-Connect to the database

 con = DriverManager.getConnection(connectionURL);  
 System.out.println("Connected!");  

6-Create a SQL Querry and an Statement, to be able to execute the query

1:  String SQL = "SELECT * FROM Samples";  
2:    stmt = con.createStatement();  
3:    rs = stmt.executeQuery(SQL); 

6-Extract data until the result set is empty

1:  while (rs.next()) {  
2:    System.out.println();  
3:    System.out.println(rs.getString(1) + "-" + rs.getString(2));  
4:    }  

7-Close the result set, connection and other streams that you might open(The transaction is finished!)

1:  finally {  
2:    if (rs != null) {  
3:    try {  
4:     rs.close();  
5:    } catch (Exception e2) {  
6:     e2.printStackTrace();  
7:    }  
8:    if (stmt != null) {  
9:     try {  
10:     rs.close();  
11:     } catch (Exception e2) {  
12:     e2.printStackTrace();  
13:     }  
14:    }  
15:    if (con != null) {  
16:     try {  
17:     rs.close();  
18:     } catch (Exception e2) {  
19:     e2.printStackTrace();  
20:     }  
21:    }  
22:    }  

Download the source codes(The database driver is included in the project):
http://www.mediafire.com/?9f6ka2y4ctp6630
This post goes dedicated to my two good colleges from college Tripo and Zoran.