Java JDBC Update

We can use java jdbc update statements in a java program to update the data for a Table. Below is a program showing the use of jdbc executeupdate (uses jdbc update query) to update a table. The return value for a jdbc sql update is an int that indicates how many rows of a table were updated.

For instance in a statement like int n = stmt.executeUpdate();

For my website I am creating the following 2 tables (Employee, Orders) as a part of the JDBC update table statement.

Employee_ID is the primary key which forms a relation between the 2 tables.

CREATE TABLE Employees (
Employee_ID INTEGER,
Name VARCHAR(30)
);

Employees:

Employee_ID	Name
6323	Hemanth
5768	Bob
1234	Shawn
5678	Michaels

Orders:
CREATE TABLE Orders (
Prod_ID INTEGER,
ProductName VARCHAR(20),
Employee_ID INTEGER
);

Prod_ID	Product Name	Employee_ID
543	Belt	6323
432	Bottle	1234
876	Ring	5678

Below is a jdbc update example

import javax.swing.JOptionPane;
import java.sql.*;
public class JDBCProgram{

static String userid=”scott”, password = “tiger”;
static String url = “jdbc:odbc:bob”;
// String url = “jdbc:mySubprotocol:myDataSource”; ?
static Statement stmt;
static Connection con;
public static void main(String args[]){

JOptionPane.showMessageDialog
(null,”JDBC Programming showing Updation of Table Data”);
		int choice = -1;

		do{
			choice = getChoice();
			if (choice != 0){
				getSelected(choice);
			}
		}
		while ( choice !=  0);
			System.exit(0);
	}

	public static int getChoice()
	{
		String choice;
		int ch;
		choice = JOptionPane.showInputDialog(null,
			“1. Create Employees Table\n”+
			“2. Create Products Table\n”+
			“3. Insert data into Employees Table\n”+
			“4. Insert data into Orders Table\n”+
			“5. Retrieve data for Employees Table\n”+
			“6. Retrieve data for Orders Table\n”+
			“7. Update Employees Table\n”+
			“0. Exit\n\n”+
			“Enter your choice”);
		ch = Integer.parseInt(choice);
		return ch;

	}

	public static void getSelected(int choice){
		if(choice==1){
			createEmployees();
		}
		if(choice==2){
			createOrders();
		}
		if(choice==3){
			insertEmployees();
		}
		if(choice==4){
			insertOrders();
		}
		if(choice==5){
			retrieveEmployees();
		}
		if(choice==6){
			retrieveOrders();
		}
		if(choice==7){
			updateEmployees();	//Uses JDBC Update Statement
		}
	}

	public static Connection getConnection()
	{

		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);

		} catch(SQLException ex) {
			System.err.println(”SQLException: ” + ex.getMessage());
		}

		return con;
	}

	/*CREATE TABLE Employees (
		    Employee_ID INTEGER,
		    Name VARCHAR(30)
		);*/

	public static void createEmployees()
	{
		Connection con = getConnection();

		String createString;
		createString = “create table Employees (” +
							“Employee_ID INTEGER, ” +
							“Name VARCHAR(30))”;
		try {
			stmt = con.createStatement();
	   		stmt.executeUpdate(createString);
			stmt.close();
			con.close();

		} catch(SQLException ex) {
			System.err.println(”SQLException: ” + ex.getMessage());
		}
JOptionPane.showMessageDialog(null,”Employees Table Created”);
	}

	/*CREATE TABLE Orders (
		    Prod_ID INTEGER,
		    ProductName VARCHAR(20),
		    Employee_ID INTEGER
		);*/

	public static void createOrders()
	{
		Connection con = getConnection();

		String createString;
		createString = “create table Orders (” +
						“Prod_ID INTEGER, ” +
						“ProductName VARCHAR(20), “+
						“Employee_ID INTEGER )”;

		try {
			stmt = con.createStatement();
	   		stmt.executeUpdate(createString);

			stmt.close();
			con.close();

		} catch(SQLException ex) {
			System.err.println(”SQLException: ” + ex.getMessage());
		}
JOptionPane.showMessageDialog(null,”Orders Table Created”);
	}

	/*Employee_ID 	Name
	 	6323 		Hemanth
	 	5768 		Bob
	 	1234 		Shawn
	 	5678 		Michaels */
	public static void insertEmployees()
	{
		Connection con = getConnection();

		String insertString1, insertString2, insertString3, insertString4;
		insertString1 = “insert into Employees values(6323, ‘Hemanth’)”;
		insertString2 = “insert into Employees values(5768, ‘Bob’)”;
		insertString3 = “insert into Employees values(1234, ‘Shawn’)”;
		insertString4 = “insert into Employees values(5678, ‘Michaels’)”;

		try {
			stmt = con.createStatement();
	   		stmt.executeUpdate(insertString1);
	   		stmt.executeUpdate(insertString2);
	   		stmt.executeUpdate(insertString3);
	   		stmt.executeUpdate(insertString4);

			stmt.close();
			con.close();

		} catch(SQLException ex) {
			System.err.println(”SQLException: ” + ex.getMessage());
		}
JOptionPane.showMessageDialog(null,”Data Inserted into Employees Table”);
	}

	/*	Prod_ID 	ProductName 	Employee_ID
	 		543 	Belt 			6323
	 		432 	Bottle 			1234
	 		876 	Ring			5678
 */
	public static void insertOrders()
	{
		Connection con = getConnection();

		String insertString1, insertString2, insertString3, insertString4;
		insertString1 = “insert into Orders values(543, ‘Belt’, 6323)”;
		insertString2 = “insert into Orders values(432, ‘Bottle’, 1234)”;
		insertString3 = “insert into Orders values(876, ‘Ring’, 5678)”;

		try {
			stmt = con.createStatement();
	   		stmt.executeUpdate(insertString1);
	   		stmt.executeUpdate(insertString2);
	   		stmt.executeUpdate(insertString3);

			stmt.close();
			con.close();

		} catch(SQLException ex) {
			System.err.println(”SQLException: ” + ex.getMessage());
		}
JOptionPane.showMessageDialog(null,”Data Inserted into Orders Table”);
	}

	public static void retrieveEmployees(){
		Connection con = getConnection();
		String result = null;
		String selectString;
		selectString = “select * from Employees”;
	    result =”Employee_ID\t\tName\n”;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(selectString);
			while (rs.next()) {
			    int id = rs.getInt(”Employee_ID”);
			    String name = rs.getString(”Name”);
			    result+=id+”\t\t”+ name+”\n”;
			}
			stmt.close();
			con.close();

		} catch(SQLException ex) {
			System.err.println(”SQLException: ” + ex.getMessage());
		}
JOptionPane.showMessageDialog(null, result);
	}

	public static void retrieveOrders(){
		Connection con = getConnection();
		String result = null;
		String selectString;
		selectString = “select * from Orders”;
		result =”Prod_ID\t\tProductName\t\tEmployee_ID\n”;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(selectString);
			while (rs.next()) {
				int pr_id = rs.getInt(”Prod_ID”);
				String prodName = rs.getString(”ProductName”);
			    int id = rs.getInt(”Employee_ID”);
			    result +=pr_id+”\t\t”+ prodName+”\t\t”+id+”\n”;
			}
			stmt.close();
			con.close();

		} catch(SQLException ex) {
			System.err.println(”SQLException: ” + ex.getMessage());
		}
JOptionPane.showMessageDialog(null, result);
	}

	public static void updateEmployees(){
		Connection con = getConnection();

String updateString1;
updateString1 = “update Employees set name = ‘hemanthbalaji’
where Employee_id = 6323″;

		try {
			stmt = con.createStatement();
	   		stmt.executeUpdate(updateString1);

			stmt.close();
			con.close();

		} catch(SQLException ex) {
			System.err.println(”SQLException: ” + ex.getMessage());
		}
JOptionPane.showMessageDialog(null,”Data Updated into Employees Table”);
	} 

}//End of class 

Source: http://www.jdbc-tutorial.com