Synthetic key vs Natural key in DB world

One of the decade old debates in SQL world:
                        Which is preferred primary key- synthetic or natural?

Both have their own pros and cons, so choosing one over other is not trivial. But you can definitely do one thing to make your life easier, whichever approach you (and your team) decides; let's stick to that and try to avoid mixing these two strategies.

Natural Key

The word relational used in DB/SQL world basically means that while designing tables your need to consider how the columns relate to each other. This way all columns together will define one entity. So conceptually it makes sense to have one or more than one column from the entity to make rows unique. But sometime over a period it becomes difficult to maintain this condition and we are forced to changed the schema of table to ensure that primary or composite key(primary key composed of multiple columns)  really remain unique.

Synthetic/Surrogate/Made-Up/Artificial Key

Synthetic is a generated unique value that is used as primary key of a database table. So it's one of the columns of the table but it doesn't have any logical relationship with the rest. But good thing is that a single synthetic key can ensure that all rows have a unique key.  It's a sequence generated by an algorithm which ensures its uniqeness. This is definitely better than managing multiple columns for a key. 

References
http://www.dbta.com/Columns/DBA-Corner/Surrogate-Keys-or-Natural-Keys-84892.aspx
http://stackoverflow.com/questions/4960263/sql-primary-key-column-artificial-id-column-vs-natural-columns

Brief overview of JDBC

JDBC(Java DataBase Connectivity) is a Java API for connecting a wide range of Databases through Java program. Using JDBC API you can also access data from sources like spreadsheets and flat files. These apis are part of standard Java platform (JAVA SE). The actual driver to communicate with data sources is not part of the platform though (link).

JDBC API has two packages java.sql and javax.sql.
Below are the steps for successful communication with the data sources:

Step 1: Register Driver

Before starting the real communication with the data source, you need to load data source drivers in the memory. Recall that Java's class Class provides a static method (forName()) to load a class. It can be used to load and return a Class reference, but here, we just need to load appropriate driver class. 

    String driverName = "com.mysql.jdbc.Driver";

    Class.forName(driverName);

 

Step 2: Establish a Connection

If step 1 executes successfully without any error/exception then we are ready to move to the next step and create a Connection object. DriverManager provides a factory method to create the Connection object. 

    static final String USER = "user.id";

    static final String PASSWORD = "user.password";

    static final String URL = "jdbc.url";

    Connection con = DriverManager.getConnection(URL, USER, PASSWORD);

 

Step 3: Create Statement/s

SQL Statement is created using Connection object. This object is used for executing static SQL statement and returning the result.

    Statement stmt = con.createStatement();

Supported Statements are:

1. Statement: Used for implementing SQL statements without any parameter.
2. PreparedStatement: Used for precompiling SQL statements that might contain parameter as well.
3. CallableStatement: Used for executing stored procedures (may contain input as well as output parameter).

 

Step 4 : Execute Query 

Finally, execute the query by calling the appropriate method on Statement object.

1. execute: Used if query could return one or more ResultSet objects.
2. executeQuery: Returns one ResultSet object.
3. executeUpdate: Returns an integer representing the number of rows affected by the query. Use this method during INSERT, UPDATE and DELETE. 

     String sqlQuery = "Select name from EMPLOYEE";

     ResultSet rs = stmt.executeQuery(sqlQuery);

 

Step 5: Extract data from ResultSet

ResultSet object has response/result data which can be extracted using cursor/iterator. This is done by calling the appropriate get method (getXXX()).

    while(rs.next()){
         String name = rs.getString("name");
         //retrieve other attributes
    }

 

Step 6: Close Connection

Finally close connection and release resources held.

     }finally{
        if(stmt != null) {
           stmt.close();  //ResultSet also gets closed implicitly. 
       }
    }