Frequently Asked JDBC Interview Questions and Answers

36. How do I insert a .jpg into a mySQL data base? 
I have tried inserting the file as byte[], but I recieve an error message stating that the syntax is incorrect.
Binary data is stored and retrieved from the database using streams in connection with prepared statements and resultsets. This minimal application stores an image file in the database,
then it retrieves the binary data from the database and converts it back to an image.
import java.sql.*;
import java.io.*;
import java.awt.*;
import java.awt.Image;
/**
* Storing and retrieving images from a MySQL database
*/
public class StoreBinary {
private static String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
private Statement stmt = null;
private Connection conn = null;

public StoreBinary() {}
/**
* Strips path prefix from filenames
* @param fileName
* @return the base filename
*/
public static String getBaseName(String fileName) {
int ix=fileName.lastIndexOf("\\");
if (ix < 0) return fileName;
return fileName.substring(ix+1);
}
/**
* Store a binary (image) file in the database using a
* prepared statement.
* @param fileName
* @return true if the operation succeeds
* @throws Exception
*/
public boolean storeImageFile(String fileName) throws Exception {
if (!connect("test", "root", "")) {
return false;
}

FileInputStream in = new FileInputStream(fileName);
int len=in.available();
String baseName=StoreBinary.getBaseName(fileName);
PreparedStatement pStmt = conn.prepareStatement
("insert into image_tab values (?,?,?)");
pStmt.setString(1, baseName);
pStmt.setInt(2,len);
pStmt.setBinaryStream(3, in, len);
pStmt.executeUpdate();
in.close();
System.out.println("Stored: "+baseName+", length: "+len);
return true;
}
/**
* Retrieve the biary file data from the DB and convert it to an image
* @param fileName
* @return
* @throws Exception
*/
public Image getImageFile(String fileName) throws Exception {
String baseName=StoreBinary.getBaseName(fileName);

ResultSet rs=stmt.executeQuery("select * from image_tab
where image_name='"+baseName+"'");

if (!rs.next()) {
System.out.println("Image:"+baseName+" not found");
return null;
}
int len=rs.getInt(2);

byte [] b=new byte[len];
InputStream in = rs.getBinaryStream(3);
int n=in.read(b);
System.out.println("n: "+n);
in.close();
Image img=Toolkit.getDefaultToolkit().createImage(b);
System.out.println("Image: "+baseName+" retrieved ok, size: "+len);
return img;
}
/**
* Establish database connection
* @param dbName
* @param dbUser
* @param dbPassword
* @return true if the operation succeeds
*/
public boolean connect(String dbName, String dbUser, String dbPassword) {
try {
Class.forName(driverName);
}
catch (ClassNotFoundException ex) {
ex.printStackTrace();
return false;
}
try {
conn = DriverManager.getConnection("jdbc:odbc:" + dbName,
dbUser,
dbPassword);
stmt = conn.createStatement();
}
catch (SQLException ex1) {
ex1.printStackTrace();
return false;
}
return true;
}

/******************************************
* MAIN stub driver for testing the class.
*/
public static void main(String[] args) {
String fileName="c:\\tmp\\f128.jpg";
StoreBinary sb = new StoreBinary();
try {
if (sb.storeImageFile(fileName)) {
// stored ok, now get it back again
Image img=sb.getImageFile(fileName);
}
}
catch (Exception ex) {
ex.printStackTrace();
}
}
}

37. How can I know when I reach the last record in a table, since JDBC doesn't provide an EOF method? 
You can use last() method of java.sql.ResultSet, if you make it scrollable.
You can also use isLast() as you are reading the ResultSet.
One thing to keep in mind, though, is that both methods tell you that you have reached the end of the current ResultSet, not necessarily the end of the table. SQL and RDBMSes make no guarantees about the order of rows, even from sequential SELECTs, unless you specifically use ORDER BY. Even then, that doesn't necessarily tell you the order of data in the table.

38. Where can I find info, frameworks and example source for writing a JDBC driver? 
There a several drivers with source available, like MM.MySQL, SimpleText Database, FreeTDS, and RmiJdbc. There is at least one free framework, the jxDBCon-Open Source JDBC driver framework. Any driver writer should also review For Driver Writers.

39. How can I create a custom RowSetMetaData object from scratch? 
One unfortunate aspect of RowSetMetaData for custom versions is that it is an interface. This means that implementations almost have to be proprietary. The JDBC RowSet package is the most commonly available and offers the sun.jdbc.rowset.RowSetMetaDataImpl class. After instantiation, any of the RowSetMetaData setter methods may be used. The bare minimum needed for a RowSet to function is to set the Column Count for a row and the Column Types for each column in the row. For a working code example that includes a custom RowSetMetaData,

40. How does a custom RowSetReader get called from a CachedRowSet?
The Reader must be registered with the CachedRowSet using CachedRowSet.setReader(javax.sql.RowSetReader reader). Once that is done, a call to CachedRowSet.execute() will, among other things, invoke the readData method.

More Questions & Answers :-

Part1  Part2  Part3  Part4  Part5  Part6  Part7  Part8