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_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 |
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