Skip to main content

Working With JDBC

Connecting to a database

In order to connect to a database, you need to perform some initialization first. Your JDBC driver has to be loaded by the Java Virtual Machine classloader, and your application needs to check to see that the driver was successfully loaded. We'll be using the ODBC bridge driver, but if your database vendor supplies a JDBC driver, feel free to use it instead.

// Attempt to load database driver

try
{

  // Load Sun's jdbc-odbc driver
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
}
catch (ClassNotFoundException cnfe) // driver not found
{
 System.err.println ("Unable to load database driver");
 System.err.println ("Details : " + cnfe);
 System.exit(0);
}

We try to load the JdbcOdbcDriver class, and then catch the ClassNotFoundException if it is thrown. This is important, because the application might be run on a non-Sun virtual machine that doesn't include the ODBC bridge, such as Microsoft's JVM. If this occurs, the driver won't be installed, and our application should exit gracefully.

Once our driver is loaded, we can connect to the database. We'll connect via the driver manager class, which selects the appropriate driver for the database we specify. In this case, we'll only be using an ODBC database, but in more complex applications, we might wish to use different drivers to connect to multiple databases. We identify our database through a JDBC URL.

A JDBC URL starts with "jdbc:" This indicates the protocol (JDBC). We also specify our database name in the URL. As an example, here's the URL for an ODBC datasource called 'demo'. Our final URL looks like this :

jdbc:odbc:demo
To connect to the database, we create a string representation of the database. We take the name of the datasource from the command line, and attempt to connect as user "dba", whose password is "sql".


// Create a URL that identifies database
String url = "jdbc:odbc:" + args[0];


// Now attempt to create a database connection
Connection db_connection = DriverManager.getConnection (url, "dba", "sql");


As you can see, connecting to a database doesn't take much code.

Executing database queries


In JDBC, we use a statement object to execute queries. A statement object is responsible for sending the SQL statement, and returning a set of results, if needed, from the query. Statement objects support two main types of statements - an update statement(executeUpdate) that is normally used for operations which don't generate a response, and a query statement(executeQuery) that returns data.

Once we have an instance of a statement object, you can call its executeUpdate and executeQuery methods. To illustrate the executeUpdate command, we'll create a table that stores information about employees. We'll keep things simple and limit it to name and employee ID.

Now that there's data in the table, we can execute queries. The response to a query will be returned by the executeQuery method as a ResultSet object. ResultSet objects store the last response to a query for a given statement object. Instances of ResultSet have methods following the pattern of getXX where XX is the name of a data type. Such data types include numbers (bytes, ints, shorts, longs, doubles, big-decimals), as well as strings, booleans, timestamps and binary data.


// Create a statement to send SQL
Statement db_statement = db_connection.createStatement();


// Create a simple table, which stores an employee ID and name
db_statement.executeUpdate("create table employee { int id, char(50) name };");
// Insert an employee, so the table contains data
db_statement.executeUpdate("insert into employee values (1, 'John Doe');");
// Commit changes
db_connection.commit();


// Execute query
ResultSet result = db_statement.executeQuery("select * from employee");

// While more rows exist, print them
while (result.next() )
{
// Use the getInt method to obtain emp. id
System.out.println ("ID : " + result.getInt("ID"));
// Use the getString method to obtain emp. name
System.out.println ("Name : " + result.getString("Name"));
System.out.println ();
}

More Posts on JDBC will be added here, note that System.out is just for demonstrating purpose in production best practice is to use Logger framework for logging.

Comments

Popular posts from this blog

Converting Java Map to String

Java Collections framework, String manipulation etc is something that we often encounter in Development process.
For processing collections (like checking null/empty, Intersection, Disjunction) We do have some of the very use full libraries.

Some of the Collection related libraries are Apche Commons Collections and Google  Collections(Guava).

Problem Use Case

This article explains how to convert a Java Map to String(and vice versa) using different libraries and technique.

One way is to use StringBuilder(Or String) and loop though the Map and build the String by applying some sort of separator ( for key:value and entry). Here we have to take care of the null value etc.

Without Any Library
If we want to convert the map to a String with key value separator and also individual entry seperator in the resulting String, we have to write code for that. For a simple Map, we have to iterate though the map, take care of the null values etc. Following is a sample to get String built out from Map C…

Masking Credit Card number in Java

Sometimes we need to mask crucial information like Credit Card Numbers, CVV numbers etc before storing  or logging the information. This example mask Credit Card Number (Except last 4 Digit) from a Text which contains information along with Credit Card Number.

The following example demonstrates how we can easily mask the credit card with Matcher and Pattern Classes. This Sample Code uses Matcher and Pattern.
Pattern Used in this sample is not optimized for Credit Card Numbers, this pattern will get any numerical numbers in the String Content.  Based on the Credit Card Type a more efficient and Strict RegEx can be used to mask the Credit Card.
/**Mask the Credit card number but last four digit value **/   Pattern PATTERN = Pattern.compile("[0-9]+"); String message = content; Matcher matcher = PATTERN.matcher(message); String maskingChar = "*"; StringBuilder finalMask = new StringBuilder(maskingChar); while …

Invoking EJB deployed on a remote machine

Invoking EJB deployed on a remote machineIn case we are calling remote ejb( ejb deployed on remote machines),The JNDI lookup might lookup like,Properties env = new Properties();env.put(Context.INITIAL_CONTEXT_FACTORY, "org.jnp.interfaces.NamingContextFactory");env.put(Context.PROVIDER_URL, "XX.XXX.XX.XX:1099");env.put("java.naming.factory.url.pkgs", "org.jboss.naming:org.jnp.interfaces"); Context ctx = new InitialContext(env);If we are calling local ejb then we can simply create InitialContext without any parameters.Like,Context ctx = new InitialContext();