10 - JDBC Insert Tutorial

Inserting Data into SQL Tables

We will insert data into the  Employees and Orders tables as created in the previous tutorial, one row at a time, supplying the information to be stored in each column of that row using a jdbc insert query statement. The values to be inserted into the columns are listed in the same order that the columns were declared when the table was created. Below is a JDBC Program showing the use of executeUpdate() to create a table and insert row into using java jdbc insert data statement.

For my website I am creating the following 2 tables (Employee, Orders) as a part of the JDBC tutorial for insert in jdbc insert query.

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_IDName
6323Hemanth
5768Bob
1234Shawn
5678

Michaels

Orders: 

CREATE TABLE Orders (
Prod_ID INTEGER,
ProductName VARCHAR(20),
Employee_ID INTEGER
)
 
Prod_IDProduct NameEmployee_ID
543Belt6323
432Bottle1234
876Ring5678
 
 
import javax.swing.JOptionPane;
import java.sql.*;
public class JDBCProgram{
	//JDBC Insert Example
	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 Insertion 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 Products 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();
		}
	}

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

}//End of class
 

 

 

Like us on Facebook

Wideskills