Using Java

1.) Modify 2.) from above. This time we are add another method called insertDB(). This method will take all 9 data elements as arguments. This method will then Insert this information into the Student table, as well as fill up the object with the data provided. (Hint: You will use an SQL Insert statement here.)

Testing Code in Main() method

Student s2=new Student();

s2.insertDB(33, Frank, Mayes, 123 Main street, Atlanta, GA, 30100,fmayes@yahoo.com,3.3f);

s2.display();

-------------------------------------------------------------------------

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

public class Student {

public static String Url = "jdbc:oracle:thin:@localhost:1521:orcl";

public static String USENAME = "scott";

public static String PASSWORD = "tiger";

public static String query = "";

static PreparedStatement stmt=null;

static ResultSet rs = null;

static Connection con = null;

private int id;

private String firstName;

private String street;

private String city;

private String state;

private String zip;

private String email;

double gpa;

private String lastName;

public Student(int id, String firstName, String lastName, String street,

String city, String state, String zip,

String email, double gpa) {

super();

this.id = id;

this.firstName = firstName;

this.lastName = lastName;

this.street = street;

this.city = city;

this.state = state;

this.zip = zip;

this.email = email;

this.gpa = gpa;

}

public Student() {

super();

}

public void display() {

System.out.println("ID " + getId() + " First Name " + getFirstName()

+ " Last Name " + getLastName() + " Email " + getEmail()

+ " City " + getCity() + " Street " + getStreet()

+ " State " + getState() + " Zip " + getZip()

+ " GPA " + getGpa());

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getFirstName() {

return firstName;

}

public void setFirstName(String firstName) {

this.firstName = firstName;

}

public String getLastName() {

return lastName;

}

public void setLastName(String lastName) {

this.lastName = lastName;

}

public String getStreet() {

return street;

}

public void setStreet(String street) {

this.street = street;

}

public String getCity() {

return city;

}

public void setCity(String city) {

this.city = city;

}

public String getState() {

return state;

}

public void setState(String state) {

this.state = state;

}

public String getZip() {

return zip;

}

public void setZip(String zip) {

this.zip = zip;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public double getGpa() {

return gpa;

}

public void setGpa(double gpa) {

this.gpa = gpa;

}

public void selectDB(int id){

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection(Url,USENAME,PASSWORD);

stmt=con.prepareStatement("select * from student where id=?");

stmt.setInt(1,id);//1 specifies the first parameter in the query

rs=stmt.executeQuery();

while(rs.next()){

setId(rs.getInt("ID"));

setFirstName(rs.getString("FirstName"));

setLastName(rs.getString("LastName"));

setStreet(rs.getString("Street"));

setCity(rs.getString("City"));

setState(rs.getString("State"));

setZip(rs.getString("Zip"));

setEmail(rs.getString("EMail"));

setGpa(rs.getDouble("GPA"));

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}//method selectDB ends

//Driver method

public static void main(String args[]) {

Student s1 = new Student(1, "rahul", "chopra", "1 india", "nxyz",

"abcz", "1313", "abcz@abcz.com", 3.2);

Student s2 = new Student(2, "firstName", "lastName", "street", "city",

"state", "zip", "email", 3.2);

s1.display();

// code starts here to fetch from db table as per ID.

Student s=new Student();

s.selectDB(4);

s.display();

//code ends

}//driver method main ends

}//class Student ends

-----------------------------------------------------

create table student (ID number(10), FirstName varchar2(20), LastName varchar2(20), Street varchar2(20), City varchar2(20), State varchar2(20), Zip varchar2(20), EMail varchar2(20), GPA number(10,2));

REM INSERTING into student

SET DEFINE OFF;

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (10,'Larry','Jones','200 Larue St.','Denver','CO','89721','larry@yahoo',3.2);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (2,'Debbie', 'Gibson', '101', 'Cambell St.', 'Chicago IL', '61721', 'debra@hotmail.com', 2.9);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (3,'James', 'Henry', '9019 Par Ln.', 'Atlanta', 'GA', '30981', 'jh@yahoo.com', 3.5);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (4,'Tony', 'Danza', '100 Main St', 'San Diego', 'CA', '90890', 'tony@yahoo.com', 3.4);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (5,'Marie', 'Baker', '313 Mockingbid Lane', 'Seattle', 'WA', '98711', 'mbaker@hotmail.com', 2.2);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (6,'Mary', 'Weathers', '10 King St', 'Denver', 'CO', '65334', 'maryw@bellsouth.net', 2.1);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (7,'Biily','Wagner', '777 Blake St', 'Chicago', 'IL', '61900', 'billyw@comcast.net', 2.9);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (8,'Tim', 'Allen', '200 South St', 'Detroit','MI', '54123', 'garys@hotmail.com', 3.2);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (9, 'Gary', 'Stevens', '112 Plymouth Ln', 'Ann Arbor',' MI', '54123', 'garys@hotmail.com', 3.2);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (10, 'Betsy','Cook', '101 Freeport St', 'Atlanta', 'GA', '31010', 'bcook@yahoo.com', 3.3);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (11,'Susan','Jones', '200 West Ave', 'Marietta', 'GA', '30060', 'sujones@hotmail.com', 2.2);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (12,'Frank','Peters', '3845 Beckford Ave', 'San Diego', 'CA', '95123', 'frankperters@com.net', 3.9);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (13,'Terri','March', '9516 Hale Dr', 'St Louis', 'MO', '63321', 'tmarch@bellsouth.net', 3.1);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (14,'Jack','Hines', '2222',' Morningside Dr', 'Marietta', 'GA 30090 ', 'jhines@yahoo.com', 2.8);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (15,'Phil','Gecko', '45 East St', 'Montgomery', 'AL', '41231', 'pgecko@yahoo.com', 2.7);

Insert into student (ID,FIRSTNAME,LASTNAME,STREET,CITY,STATE,ZIP,EMAIL,GPA) values (16,'Tony','Peters', '200', 'Central Ave', 'Miami FL', '75213', 'pgecko@yahoo.com', 2.7);

Students

ID	FirstName	LastName	Street	City	State	Zip	EMail	GPA
1	Larry	Jones	200 Larue St.	Denver	CO	89721	larry@yahoo.com	3.2
2	Debbie	Gibson	101 Cambell St.	Chicago	IL	61721	debra@hotmail.com	2.9
3	James	Henry	9019 Par Ln.	Atlanta	GA	30981	jh@yahoo.com	3.5
4	Tony	Danza	100 Main St	San Diego	CA	90890	tony@yahoo.com	3.4
5	Marie	Baker	313 Mockingbid Lane	Seattle	WA	98711	mbaker@hotmail.com	2.2
6	Mary	Weathers	10 King St	Denver	CO	65334	maryw@bellsouth.net	2.1
7	Biily	Wagner	777 Blake St	Chicago	IL	61900	billyw@comcast.net	2.9
8	Tim	Allen	200 South St	Detroit	MI	54123
9	Gary	Stevens	112 Plymouth Ln	Ann Arbor	MI	54123	garys@hotmail.com	3.2
10	Betsy	Cook	101 Freeport St	Atlanta	GA	31010	bcook@yahoo.com	3.3
11	Susan	Jones	200 West Ave	Marietta	GA	30060	sujones@hotmail.com	2.2
12	Frank	Peters	3845 Beckford Ave	San Diego	CA	95123	frankperters@comcast.net	3.9
13	Terri	March	9516 Hale Dr	St Louis	MO	63321	tmarch@bellsouth.net	3.1
14	Jack	Hines	2222 Morningside Dr	Marietta	GA	30090	jhines@yahoo.com	2.8
15	Phil	Gecko	45 East St	Montgomery	AL	41231	pgecko@yahoo.com	2.7
16	Tony	Peters	200 Central Ave	Miami	FL	75213