JAVA

Database Programming With Java + JDBC: Part 1/2

No Java programmer is complete without knowledge of Java database connectivity, or JDBC for short. In this 2 part article Nitin teaches us JDBC from the inside out.

Author Info:

By: Nitin Patil

Poor Best

Rating:

/ 39

August 20, 2002

TABLE OF CONTENTS:

· Database Programming With Java + JDBC: Part 1/2
· What is JDBC?
· Before We Start Coding
· Our Address Book App (contd.)
· The Sample Code... Explained
· Conclusion

Database Programming With Java + JDBC: Part 1/2 - Our Address Book App (contd.)
(Page 4 of 6 )

// JDBCDemo1.java

import java.sql.*;
import java.io.*;

/** Demonstrates the use of JDBC to interact with a database. */
public class JDBCDemo1 {

/** The JDBC driver. */
private static final String DB_DRIVER = "org.gjt.mm.mysql.Driver";
/** The database URL. */
private static final String DB_URL = "jdbc:mysql:///test_db";

/** Returns a database connection. */
public Connection getConnection()
throws ClassNotFoundException, SQLException {

Connection con = null;

Class.forName(DB_DRIVER);
con = DriverManager.getConnection(DB_URL, "", "");

return con;

}

/** Creates the addressbook table. */
public void createTable(Connection con) throws SQLException {

Statement stmt = null;
String query;

try {

query = "create table address_book("
+ " nickname varchar(20) not null,"
+ " name varchar(30),"
+ " email varchar(50),"
+ " primary key(nickname))";

stmt = con.createStatement();
stmt.executeUpdate(query);

}
finally {

// Clean up
if (stmt != null) {
stmt.close();
}

}
}

/** Adds an entry to the addressbook. Returns 1 if the entry is added
successfully. */
public int add(Connection con, String nickName, String name, String email)
throws SQLException {

// The return value
int n = 0;

Statement stmt = null;
String query;

try {

query = "insert into address_book(nickname, name, email)"
+ " values('"
+ nickName
+ "', '"
+ name
+ "', '"
+ email
+ "')";

stmt = con.createStatement();
n = stmt.executeUpdate(query);

}
finally {

// Clean up
if (stmt != null) {
stmt.close();
}

}

return n;

}

/** Searches for an entry in the addressbook. */
public AddressBookEntry lookup(Connection con, String nickName)
throws SQLException {

// The return value
AddressBookEntry entry = null;

Statement stmt = null;
ResultSet rs = null;
String query;
int j;
String name;
String email;

try {

query = "select nickname, name, email from address_book"
+ " where nickname = '"
+ nickName
+ "'";
stmt = con.createStatement();
rs = stmt.executeQuery(query);
j = 1;
if(rs.next()) {

// Fetch values
nickName = rs.getString(j++);
name = rs.getString(j++);
email = rs.getString(j++);

// Update the return value
entry = new AddressBookEntry(nickName, name, email);

}

}
finally {

// Clean up
try {
if (rs != null) {
rs.close();
}
}
catch(Exception ex) {}
try {
if (stmt != null) {
stmt.close();
}
}
catch(Exception ex) {}

}

return entry;

}

/** Main. */
public static void main(String[] args) {

JDBCDemo1 jdbcDemo = new JDBCDemo1();

Connection con = null;
BufferedReader in;
boolean continueFlag = true;
String choiceStr;
int choice;
String nickName;
String name;
String email;
AddressBookEntry entry;

try {

// Get a database connection
con = jdbcDemo.getConnection();

// Obtain a reader for reading standard input for convenience
in = new BufferedReader(new InputStreamReader(System.in));

while(continueFlag) {

System.out.println();
System.out.println();
System.out.println(" *** Address Book Menu ***");
System.out.println();
System.out.println("Choose your option from 1-4 below");
System.out.println("1. Create the addressbook table");
System.out.println("2. Add an entry to the addressbook");
System.out.println("3. Search an entry in the addressbook");
System.out.println("4. Quit");
System.out.print("Enter your choice [1-4]: ");
choiceStr = in.readLine();

try {

// Determine the option
choice = Integer.parseInt(choiceStr);

// Handle option
switch(choice) {

case 1:
// Create table
jdbcDemo.createTable(con);
printResult("Table created successfully.");
break;

case 2:
// Add an entry
System.out.println("Enter the following information.");
System.out.print("Nickname: ");
nickName = in.readLine();
System.out.print("Name: ");
name = in.readLine();
System.out.print("Email: ");
email = in.readLine();
jdbcDemo.add(con, nickName, name, email);
printResult("Entry added successfully.");
break;

case 3:
// Search an entry
System.out.print("Enter the nickname to search: ");
nickName = in.readLine();
entry = jdbcDemo.lookup(con, nickName);
if (entry != null) {
printResult(entry.toString());
}
else {
printResult("No such nickname in the addressbook.");
}
break;

case 4:
// Quit
continueFlag = false;
break;

default:
// Invalid choice
System.out.println();
System.out.println("Invalid choice.");
break;

}

}
catch(NumberFormatException ex) {
System.out.println();
System.out.println("Invalid choice.");
}

}

}
catch(Exception ex) {
ex.printStackTrace();
}
finally {

// Clean up
try {
if (con != null) {
con.close();
}
}
catch(SQLException ex) {
}

// Check if it was a normal exit
if (!continueFlag) {
System.out.println();
System.out.println("Thank you for using the addressbook.");
System.out.println();
}

}

}

/** Prints a result. */
public static void printResult(String msg) {
System.out.println();
System.out.println("RESULT: " + msg);
}

}

Next: The Sample Code... Explained >>