Coding Interview on Database - JDBC (java.sql)

Explain Connecting to a Database ?

This example uses the JDBC-ODBC bridge to connect to a database called ''mydatabase''.

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:mydatabase";
Connection con = DriverManager.getConnection(
url, "login", "password");
} catch (ClassNotFoundException e) {
} catch (SQLException e) {
}

Creating a Table
This example creates a table called ''mytable'' with three columns: COL_A which holds strings, COL_B which holds integers, and COL_C which holds floating point numbers.

try {
Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE TABLE mytable (
COL_A VARCHAR(100), COL_B INTEGER, COL_C FLOAT)");
} catch (SQLException e) {
}

Entering a New Row into a Table

This example enters a row containing a string, an integer, and a floating point number into the table called ''mytable''.
try {
Statement stmt = connection.createStatement();
stmt.executeUpdate("INSERT INTO mytable
VALUES ('Patrick Chan', 123, 1.23)");
connection.close();
} catch (SQLException e) {
}

Getting All Rows from a Table
This example retrieves all the rows from a table called ''mytable''. A row in ''mytable'' consists of a string, integer, and floating point number.

try {
Statement stmt = connection.createStatement();
// Get data using colunm names.
ResultSet rs = stmt.executeQuery(
"SELECT * FROM mytable");
while (rs.next()) {
String s = rs.getString("COL_A");
int i = rs.getInt("COL_B");
float f = rs.getFloat("COL_C");
process(s, i, f);
}

// Get data using colunm numbers.
rs = stmt.executeQuery(
"SELECT * FROM mytable");
while (rs.next()) {
String s = rs.getString(1);
int i = rs.getInt(2);
float f = rs.getFloat(3);
process(s, i, f);
}
} catch (SQLException e) {
}

Getting Particular Rows from a Table
This example retrieves all rows from a table called ''mytable'' whose column COL_A equals ``Patrick Chan''. A row in ''mytable'' consists of a string, integer, and floating point number.
try {
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT * FROM mytable WHERE COL_A = 'Patrick Chan'");
rs.next();
String s = rs.getString("COL_A");
int i = rs.getInt("COL_B");
float f = rs.getFloat("COL_C");
process(s, i, f);
} catch (SQLException e) {
}

Updating a Row of Data in a Table
This example updates a row in a table called ``mytable''. In particular, for all rows whose column COL_B equals 123, column COL_A is set to ''John Doe''.
try {
Statement stmt = connection.createStatement();
int numUpdated = stmt.executeUpdate(
"UPDATE mytable SET COL_A = 'John Doe'
WHERE COL_B = 123");
connection.close();
} catch (SQLException e) {
}

Using a Prepared Statement
A prepared statement should be used in cases where a particular SQL statement is used frequently. The prepared statement is more expensive to set up but executes faster than a statement. This example demonstrates a prepared statement for getting all rows from a table called ''mytable'' whose column COL_A equals ''Patrick Chan''. This example also demonstrates a prepared statement for updating data in the table. In particular, for all rows whose column COL_B equals 123, column COL_A is set to ''John Doe''.

try {
// Retrieving rows from the database.
PreparedStatement stmt = connection.prepareStatement(
"SELECT * FROM mytable WHERE COL_A = ?");
int colunm = 1;
stmt.setString(colunm, "Patrick Chan");
ResultSet rs = stmt.executeQuery();
// Updating the database.
stmt = connection.prepareStatement(
"UPDATE mytable SET COL_A = ? WHERE COL_B = ?");
colunm = 1;
stmt.setString(colunm, "John Doe");
colunm = 2;
stmt.setInt(colunm, 123);
int numUpdated = stmt.executeUpdate();
} catch (SQLException e) {
}