Ip practical

IP PROJECT





















Made by: Manjinder Singh
                  12th –I
                  31 roll no.
Submitted to: Mr. Rajeev Kumar
                        (IP teacher)
















Acknowledgement
This project would have never been a success without the help of my friends, family and our IP teacher Mr. Rajeev Kumar. Special thanks to the uploaded for providing the info on the project online.











Java Data Base Connectivity (JDBC)
A JDBC driver is a software component enabling a Java application to interact with a database. To connect with individual databases, JDBC (the Java Database Connectivity API) requires drivers for each database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.
In this project we will make two tables’ “teacher” and “user”. We will make a program in net beans to connect to the table “user” to authorize the username and password and then we will make program to delete, update, select and insert in the table “teacher”.



SQL query for forming table “user”
Cerate table users (‘username’ varchar(20) , ‘password’ varcahr(16) , primary key(‘username’) ) ;
Insert into users values (‘admin’, ‘abc123’);

Username    Password

admin
abc123

SQL query for forming table “teacher”
Create table teacher (‘TEACHER_ID’ integer(6) , ‘TEACHER_NAME’ varchar(20) , ‘TEACHER_SALARY’ integer(6) , primary key(TEACHER_ID) )

TEACHER_ID  TEACHER_NAME    TEACHER_SALARY
-   -   -



i.  Login page
> Login button
String un, pass ;
try
{

un =jTextField1.getText ();
pass =jPasswordField1.getText ();
master   ob=new master();
Class.forName ("com.mysql.jdbc.Driver");
String connectionUrlTOSQL = "jdbc:mysql://localhost/school?user=root";
Connection con = DriverManager.getConnection (connectionUrlTOSQL);
Statement ss=con.createStatement ();
ResultSet rs=ss.executeQuery ("select * from users where uname='" + un + "' and pwd='" + pass + "'");

If (rs.first ())
{ob.setVisible (true);}

Else

{
JOptionPane.showMessageDialog (this, “Wrong user name or pwd");
}

}

catch(Exception e)
{
System.out.println("SQL Exception: "+ e.toString());
}




Login fail


Login successful

ii. Main menu

> Insert button

insert o=new insert();
o.setVisible(true);

> delete button

delete od=new delete();
od.setVisible(true);

> update button

update ou=new update();
ou.setVisible(true);

> select button

select os=new select();
os.setVisible(true);


















Main page







iii.    Insert
> Insert button

String teachID, teachName, teachSal ,q;
teachID   =jTextField1.getText();
teachName =jTextField2.getText();
teachSal  =jTextField3.getText();

try
{
Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://localhost/school?user=root";
Connection con = DriverManager.getConnection(connectionUrl);
Statement ss=con.createStatement();
q="insert into teacher values(" + teachID + ",'" + teachName + "'," + teachSal + ")";
ss.executeUpdate(q);
JOptionPane.showMessageDialog(this, "Row Inserted");
}

catch(SQLException e)
{
System.out.println("SQL Exception: "+ e.toString());
}

catch(Exception e)
{
System.out.println("SQL Exception: "+ e.toString());
}





Insertion



iv. Delete
> Delete button
 string teachID,q;
teachID=jTextField1.getText();

try {
Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://localhost/school?user=root";
Connection con = DriverManager.getConnection(connectionUrl);
Statement ss=con.createStatement();
q="delete from teacher where TEACHER_ID =" + teachID ;
ss.executeUpdate(q);
JOptionPane.showMessageDialog(this, "Row Deleted");
}

 catch(SQLException e)
{
System.out.println("SQL Exception: "+ e.toString());

} catch(Exception e)
{
System.out.println("SQL Exception: "+ e.toString());







Delete from the table






v.  Update
> update button
 String teachID , teachName , teachsal ;
  teachID    =jTextField1.getText();
  teachName  =jTextField2.getText();
  teachsal   =jTextField3.getText();

  try {
  Class.forName("com.mysql.jdbc.Driver");
  String connectionUrl = "jdbc:mysql://localhost/school?user=root";
  Connection con = DriverManager.getConnection(connectionUrl);
  Statement ss=con.createStatement();
  q ="update teacher set TEACHER_NAME ='" + teachName + "',sal=" + teachsal + " where
  TEACHER_ID=" + teachID;
  ss.executeUpdate(q);
  JOptionPane.showMessageDialog(this, "Row Updated");
  }
  catch(SQLException e)
  { System.out.println("SQL Exception: "+ e.toString()); }
  catch(Exception e)
 { System.out.println("SQL Exception: "+ e.toString()); }










Update







vi. Select
> select button
String teachID;
teachID =jTextField1.getText();
DefaultTableModel model =(DefaultTableModel) jTable1.getModel();

if (rows>0)
{
for (int i=0; i<rows; i++)
{ model.removeRow(i); }
}
try
{
Class.forName("com.mysql.jdbc.Driver");
Connectioncon=(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/school?user=root");
Statement stmt = (Statement) con.createStatement();
String query;
if(teachID.compareTo("")==0)
query="SELECT * FROM teacher";
else
query="SELECT * FROM teacher where TEACH_ID=" + teachID;
ResultSet rs = stmt.executeQuery(query);

while(rs.next())
{
String ID = rs.getString ("teachID");
String eName = rs.getString ("teachName");
String sal = rs.getString ("teachSal");
model.addRow (new Object[]{teachID, teachName, teachSal });
}
}
catch(Exception e)
{ System.out.println("SQL Exception: "+ e.toString()); }













Select










Bibliography:

www.google.com

www.wikipedia.com

www.ncert.com

www.hackingstars.com






Thank YOU