/* ================================
JDBC Programming Examples
================================
courtesy to :
Author: Hemanth. Balaji
--------------------------
1. Substitute your driver's JDBC URL for the generic JDBC URL
that appears in the code. In other words, put your driver's JDBC URL
between the quotation marks in the follwoing line:
String url = "jdbc:mySubprotocol:myDataSource";
The documentation for your driver should give you this URL.
2. Substitute the driver's full class name for "myDriver.ClassName" in
the following line:
Class.forName("myDriver.ClassName");
3. Substitute the username and password you use for your database
in the following:
"userid", "password"
*/
import javax.swing.JOptionPane;
import java.sql.*;
public class JdbcDemo
{
public static void main(String args[])
{
JOptionPane.showMessageDialog(null,"Welcome to JDBC Demo");
int choice = -1;
String userid="scott";
String password = "tiger";
do
{
choice = getChoice();
if (choice != 0)
{
getSelected(choice, userid, password);
}
}
while ( choice != 0);
System.exit(0);
}
public static int getChoice()
{
String choice;
int ch;
choice = JOptionPane.showInputDialog(null,
"1. Create Coffees Table\n"+
"2. Insert Values into Coffees Table\n"+
"3. Create Suppliers Table\n"+
"4. Insert Values into Suppliers Table\n"+
"5. Update Table Example on Coffees Table\n"+
"6. A PreparedStatement Demo On Coffees Table\n"+
"7. A PreparedStatement Demo On Coffees Table using a FOR Statement\n"+
"8. List of the coffees he buys from Acme, Inc [Supplier]\n"+
"9. Using Transactions Demo"+
"10. Creating a Stored Procedue Demo\n"+
"11. Using Callable Statement to call a Stored Procedure\n"+
"12. Batch Update Demo\n"+
"0. Exit\n\n"+
"Enter your choice");
ch = Integer.parseInt(choice);
return ch;
}
public static void getSelected(int choice, String userid, String password)
{
if(choice==1)
{
createCoffees(userid, password);
}
else if(choice==2)
{
insertCoffees(userid, password);
}
else if(choice==3)
{
createSuppliers(userid, password);
}
else if(choice==4)
{
insertSuppliers(userid, password);
}
else if(choice==5)
{
updateCoffees(userid, password);
}
else if(choice==6)
{
prepare1Demo(userid, password);
}
else if(choice==7)
{
prepare2Demo(userid, password);
}
else if(choice==8)
{
joinDemo(userid, password);
}
else if(choice==9)
{
transDemo(userid, password);
}
else if(choice==10)
{
createProcedure1(userid, password);
}
else if(choice==11)
{
callableDemo(userid, password);
}
else if(choice==12)
{
batchUpdateDemo(userid, password);
}
}
// Create Coffees Table
public static void createCoffees(String userid, String password)
{
String url = "jdbc:odbc:bob"; // String url = "jdbc:mySubprotocol:myDataSource"; ?
// jdbc:subprotocol:subname
Connection con;
String createString;
createString = "create table COFFEES " +
"(COF_NAME varchar(32), " +
"SUP_ID int, " +
"PRICE float, " +
"SALES int, " +
"TOTAL int)";
Statement stmt;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //Class.forName("myDriver.ClassName"); ?
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"userid", "password");
stmt = con.createStatement();
stmt.executeUpdate(createString);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
// Insert values into Coffees Table
public static void insertCoffees(String userid, String password)
{
String url = "jdbc:odbc:bob";
Connection con;
Statement stmt;
String query = "select COF_NAME, PRICE from COFFEES";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"userid", "password");
stmt = con.createStatement();
stmt.executeUpdate("insert into COFFEES " +
"values('Colombian', 00101, 7.99, 0, 0)");
stmt.executeUpdate("insert into COFFEES " +
"values('French_Roast', 00049, 8.99, 0, 0)");
stmt.executeUpdate("insert into COFFEES " +
"values('Espresso', 00150, 9.99, 0, 0)");
stmt.executeUpdate("insert into COFFEES " +
"values('Colombian_Decaf', 00101, 8.99, 0, 0)");
stmt.executeUpdate("insert into COFFEES " +
"values('French_Roast_Decaf', 00049, 9.99, 0, 0)");
ResultSet rs = stmt.executeQuery(query);
System.out.println("Coffee Break Coffees and Prices:");
while (rs.next()) {
String s = rs.getString("COF_NAME"); // OR rs.getString(1);
float f = rs.getFloat("PRICE"); // OR rs.getFloat(3);
System.out.println(s + " " + f);
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
// Create Suppliers Table
public static void createSuppliers(String userid, String password)
{
String url = "jdbc:odbc:bob";
Connection con;
String createString;
createString = "create table SUPPLIERS " +
"(SUP_ID int, " +
"SUP_NAME varchar(40), " +
"STREET varchar(40), " +
"CITY varchar(20), " +
"STATE char(2), ZIP char(5))";
Statement stmt;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"userid", "password");
stmt = con.createStatement();
stmt.executeUpdate(createString);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
// Insert values into Coffees Table
public static void insertSuppliers(String userid, String password)
{
String url = "jdbc:odbc:bob";
Connection con;
Statement stmt;
String query = "select SUP_NAME, SUP_ID from SUPPLIERS";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"userid", "password");
stmt = con.createStatement();
stmt.executeUpdate("insert into SUPPLIERS " +
"values(49, 'Superior Coffee', '1 Party Place', " +
"'Mendocino', 'CA', '95460')");
stmt.executeUpdate("insert into SUPPLIERS " +
"values(101, 'Acme, Inc.', '99 Market Street', " +
"'Groundsville', 'CA', '95199')");
stmt.executeUpdate("insert into SUPPLIERS " +
"values(150, 'The High Ground', '100 Coffee Lane', " +
"'Meadows', 'CA', '93966')");
ResultSet rs = stmt.executeQuery(query);
System.out.println("Suppliers and their ID Numbers:");
while (rs.next()) {
String s = rs.getString("SUP_NAME");
int n = rs.getInt("SUP_ID");
System.out.println(s + " " + n);
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
// Update Coffees Table
public static void updateCoffees(String userid, String password)
{
String url = "jdbc:odbc:bob";
Connection con;
String updateString;
updateString = "UPDATE COFFEES " +
"SET SALES = 75 " +
"WHERE COF_NAME LIKE 'Colombian'";
String query = "SELECT COF_NAME, SALES FROM COFFEES " +
"WHERE COF_NAME LIKE 'Colombian'";
Statement stmt;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"userid", "password");
stmt = con.createStatement();
stmt.executeUpdate(updateString);
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String s = rs.getString("COF_NAME"); //1
int n = rs.getInt("SALES"); //2
System.out.println(n + " pounds of " + s +
" sold this week.");
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
// Update Coffees Table using a prepared Statement
public static void prepare1Demo(String userid, String password)
{
String url = "jdbc:odbc:bob";
Connection con;
Statement stmt=null;
String query = "SELECT COF_NAME, SALES FROM COFFEES ";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"userid", "password");
PreparedStatement updateSales = con.prepareStatement(
"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String s = rs.getString("COF_NAME"); //1
int n = rs.getInt("SALES"); //2
System.out.println(s + " " + n);
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
// Update Coffees Table using a prepared Statement
public static void prepare2Demo(String userid, String password)
{
String url = "jdbc:odbc:bob";
Connection con;
Statement stmt=null;
String query = "SELECT COF_NAME, SALES FROM COFFEES ";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"userid", "password");
PreparedStatement updateSales;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME like ?";
updateSales = con.prepareStatement(updateString);
int [] salesForWeek = {175, 150, 60, 155, 90};
String [] coffees = {"Colombian", "French_Roast", "Espresso",
"Colombian_Decaf", "French_Roast_Decaf"};
int len = coffees.length;
for(int i = 0; i < len; i++) {
updateSales.setInt(1, salesForWeek[i]);
updateSales.setString(2, coffees[i]);
updateSales.executeUpdate();
// int n= updateSales.executeUpdate() to find out how may rows have been updated
}
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String s = rs.getString("COF_NAME"); //1
int n = rs.getInt("SALES"); //2
System.out.println(s + " " + n);
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
//Using join on 2 table to retrieve results
public static void joinDemo(String userid, String password)
{
String url = "jdbc:odbc:bob";
Connection con;
String query = "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
"from COFFEES, SUPPLIERS " +
"where SUPPLIERS.SUP_NAME like 'Acme, Inc.' and " +
"SUPPLIERS.SUP_ID = COFFEES.SUP_ID";
Statement stmt;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection (url,
"userid", "password");
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
System.out.println("Supplier, Coffee:");
while (rs.next()) {
String supName = rs.getString(1);
String cofName = rs.getString(2);
System.out.println(" " + supName + ", " + cofName);
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.print("SQLException: ");
System.err.println(ex.getMessage());
}
}
// Using Transaction Autocommit Option
public static void transDemo(String userid, String password)
{
String url = "jdbc:odbc:bob";
Connection con=null;
Statement stmt;
PreparedStatement updateSales;
PreparedStatement updateTotal;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME = ?";
String updateStatement = "update COFFEES " +
"set TOTAL = TOTAL + ? where COF_NAME = ?";
String query = "select COF_NAME, SALES, TOTAL from COFFEES";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"userid", "password");
updateSales = con.prepareStatement(updateString);
updateTotal = con.prepareStatement(updateStatement);
int [] salesForWeek = {175, 150, 60, 155, 90};
String [] coffees = {"Colombian", "French_Roast",
"Espresso", "Colombian_Decaf",
"French_Roast_Decaf"};
int len = coffees.length;
con.setAutoCommit(false);
for (int i = 0; i < len; i++) {
updateSales.setInt(1, salesForWeek[i]);
updateSales.setString(2, coffees[i]);
updateSales.executeUpdate();
updateTotal.setInt(1, salesForWeek[i]);
updateTotal.setString(2, coffees[i]);
updateTotal.executeUpdate();
con.commit();
}
con.setAutoCommit(true);
updateSales.close();
updateTotal.close();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String c = rs.getString("COF_NAME");
int s = rs.getInt("SALES");
int t = rs.getInt("TOTAL");
System.out.println(c + " " + s + " " + t);
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
if (con != null) {
try {
System.err.print("Transaction is being ");
System.err.println("rolled back");
con.rollback();
} catch(SQLException excep) {
System.err.print("SQLException: ");
System.err.println(excep.getMessage());
}
}
}
}
/*Creating a Stored procedure involving the coffees and the suppliers table
create procedure SHOW_SUPPLIERS
as
select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
from SUPPLIERS, COFFEES
where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
order by SUP_NAME
*/
public static void createProcedure1(String userid, String password)
{
String url = "jdbc:odbc:bob";
Connection con;
Statement stmt;
String createProcedure = "create procedure SHOW_SUPPLIERS " +
"as " +
"select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
"order by SUP_NAME";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"DPST_TRNG", "DPST_TRNG4321");
stmt = con.createStatement();
stmt.executeUpdate(createProcedure);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
//Using Callable Statement to call a Stored Procedure
public static void callableDemo(String userid, String password)
{
String url = "jdbc:odbc:bob";
Connection con;
Statement stmt=null;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"DPST_TRNG", "DPST_TRNG4321");
CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
String c = rs.getString("SUP_NAME");
String s = rs.getString("COF_NAME");
System.out.println(c + " " + s );
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
//A code showing the Batch Update Syntax
public static void batchUpdateDemo(String userid, String password)
{
ResultSet rs = null;
PreparedStatement ps = null;
String url = "jdbc:odbc:bob";
Connection con;
Statement stmt;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"userid", "password");
con.setAutoCommit(false);
stmt = con.createStatement();
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('Amaretto', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('Hazelnut', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
int [] updateCounts = stmt.executeBatch();
con.commit();
con.setAutoCommit(true);
ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");
System.out.println("Table COFFEES after insertion:");
while (uprs.next()) {
String name = uprs.getString("COF_NAME");
int id = uprs.getInt("SUP_ID");
float price = uprs.getFloat("PRICE");
int sales = uprs.getInt("SALES");
int total = uprs.getInt("TOTAL");
System.out.print(name + " " + id + " " + price);
System.out.println(" " + sales + " " + total);
}
uprs.close();
stmt.close();
con.close();
} catch(BatchUpdateException b) {
System.err.println("-----BatchUpdateException-----");
System.err.println("SQLState: " + b.getSQLState());
System.err.println("Message: " + b.getMessage());
System.err.println("Vendor: " + b.getErrorCode());
System.err.print("Update counts: ");
int [] updateCounts = b.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i++) {
System.err.print(updateCounts[i] + " ");
}
System.err.println("");
} catch(SQLException ex) {
System.err.println("-----SQLException-----");
System.err.println("SQLState: " + ex.getSQLState());
System.err.println("Message: " + ex.getMessage());
System.err.println("Vendor: " + ex.getErrorCode());
}
}
}//End of class
No comments:
Post a Comment