JDBC basics

The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database.

JDBC helps you to write java applications that manage these three programming activities:

1. Connect to a database
2. Send queries and update statements to the database
3. Retrieve and process the results with database.
   
   

   Connection con = DriverManager.getConnection
              ( "jdbc:myDriver:wombat", "myLogin","myPassword");
        
   Statement stmt = con.createStatement();
   ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
   while (rs.next()) {
    int x = rs.getInt("a");
    String s = rs.getString("b");
    float f = rs.getFloat("c");
    }

   This short code fragment instantiates a DriverManager object to connect to a database driver and log into the database, instantiates a Statement object that carries your SQL language query to the database; instantiates a ResultSet object that retrieves the results of your query, and executes a simple while loop, which retrieves and displays those results.

JDBC includes four components:

1. The JDBC API 
   
   The JDBC™ API provides programmatic access to relational data from the Java™ programming language. Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source. The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment.
   
   The JDBC API is part of the Java platform, which includes the Java™ Standard Edition (Java™ SE ) and the Java™ Enterprise Edition (Java™ EE). The JDBC 4.0 API is divided into two packages: java.sql and javax.sql. Both packages are included in the Java SE and Java EE platforms.
   
2. JDBC Driver Manager 
   
   The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture.
   
   The Standard Extension packages javax.naming and javax.sql let you use a DataSource object registered with a Java Naming and Directory Interface™ (JNDI) naming service to establish a connection with a data source. You can use either connecting mechanism, but using a DataSource object is recommended whenever possible.
   
3. JDBC Test Suite
   
   The JDBC driver test suite helps you to determine that JDBC drivers will run your program. These tests are not comprehensive or exhaustive, but they do exercise many of the important features in the JDBC API.
   
4. JDBC-ODBC Bridge
   
   The Java Software bridge provides JDBC access via ODBC drivers. Note that you need to load ODBC binary code onto each client machine that uses this driver. As a result, the ODBC driver is most appropriate on a corporate network where client installations are not a major problem, or for application server code written in Java in a three-tier architecture.

This Trail uses the first two of these these four JDBC components to connect to a database and then build a java program that uses SQL commands to communicate with a test Relational Database. The last two components are used in specialized environments to test web applications, or to communicate with ODBC-aware DBMSs.

JDBC Architecture

Two-tier and Three-tier Processing Models

The JDBC API supports both two-tier and three-tier processing models for database access.

Figure 1: Two-tier Architecture for Data Access.

In the two-tier model, a Java applet or application talks directly to the data source. This requires a JDBC driver that can communicate with the particular data source being accessed. A user's commands are delivered to the database or other data source, and the results of those statements are sent back to the user. The data source may be located on another machine to which the user is connected via a network. This is referred to as a client/server configuration, with the user's machine as the client, and the machine housing the data source as the server. The network can be an intranet, which, for example, connects employees within a corporation, or it can be the Internet.

In the three-tier model, commands are sent to a "middle tier" of services, which then sends the commands to the data source. The data source processes the commands and sends the results back to the middle tier, which then sends them to the user. MIS directors find the three-tier model very attractive because the middle tier makes it possible to maintain control over access and the kinds of updates that can be made to corporate data. Another advantage is that it simplifies the deployment of applications. Finally, in many cases, the three-tier architecture can provide performance advantages.

Figure 2: Three-tier Architecture for Data Access.

Until recently, the middle tier has often been written in languages such as C or C++, which offer fast performance. However, with the introduction of optimizing compilers that translate Java bytecode into efficient machine-specific code and technologies such as Enterprise JavaBeans™, the Java platform is fast becoming the standard platform for middle-tier development. This is a big plus, making it possible to take advantage of Java's robustness, multithreading, and security features.

With enterprises increasingly using the Java programming language for writing server code, the JDBC API is being used more and more in the middle tier of a three-tier architecture. Some of the features that make JDBC a server technology are its support for connection pooling, distributed transactions, and disconnected rowsets. The JDBC API is also what allows access to a data source from a Java middle tier.

Storing image in db using jdbc

Assuming you have a table with a blob field and you want to save a file and store it in the database table or you want to read from a blob object stored in a database, you can use these below java code snippets to write file and read file from database.

The sample table is created as below to have a blob field to store file.

CREATE TABLE t1 (c1 INT PRIMARY KEY NOT NULL, c2 BLOB(5M));

Then use the below code snippet to insert an image file as follows.
 
PreparedStatement pstmt = conn.prepareStatement ("INSERT INTO t1 VALUES (?,?)");
pstmt.setInt (1, 100);
File fBlob = new File ( "image1.gif" );
FileInputStream is = new FileInputStream ( fBlob );
pstmt.setBinaryStream (2, is, (int) fBlob.length() );
pstmt.execute ();
...
 
Retrieving a BLOB or in other words retrieving the image file stored in the database as follows:
 
Statement stmt = conn.createStatement ();
ResultSet rs= stmt.executeQuery("SELECT * FROM t1");
while(rs.next()) {
int val1 = rs.getInt(1);
InputStream val2 = rs.getBinaryStream(2);
...
} rs.close();

sending mail through java

SENDING MAIL THROUGH JAVA
You can use the JavaMail API to send emails from your Java application. Though the JavaMail API allows you to do many things including the ability to retrieve and read the emails or sending emails etc, this sample java program demonstrates how to send email from your Java application. Remember to change the email host (String host) to your email server host, otherwise it won't work. import java.util.*;

import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;
public class SimpleSendEmail {
public static void main(String[] args) {
// Collect the necessary information to send a simple message
// Make sure to replace the values for host, to, and from with
// valid information.
// host - must be a valid smtp server that you currently have
// access to.
// to - whoever is going to get your email
// from - whoever you want to be. Just remember that many smtp
// servers will validate the domain of the from address
// before allowing the mail to be sent.
String host = "server.myhost.com";
String to = "YourFriend@someemail.com";
String from = "Me@myhost.com";
String subject = "My First Email";
String messageText = "I am sending a message using the"
+ " JavaMail API.\n"
+ "Here type your message.";
boolean sessionDebug = false;
// Create some properties and get the default Session.
Properties props = System.getProperties();
props.put("mail.host", host);
props.put("mail.transport.protocol", "smtp");
Session session = Session.getDefaultInstance(props, null);
// Set debug on the Session so we can see what is going on
// Passing false will not echo debug info, and passing true
// will.
session.setDebug(sessionDebug);
try {
// Instantiate a new MimeMessage and fill it with the
// required information.
Message msg = new MimeMessage(session);
msg.setFrom(new InternetAddress(from));
InternetAddress[] address = {new InternetAddress(to)};
msg.setRecipients(Message.RecipientType.TO, address);
msg.setSubject(subject);
msg.setSentDate(new Date());
msg.setText(messageText);
// Hand the message to the default transport service
// for delivery.
Transport.send(msg);
}
catch (MessagingException mex) {
mex.printStackTrace();
}
}
}