EX-NO : 7 DEVELOP DATABASE APPLICATIONS USING IDE
TOOLS
DATE : (EG., NETBEANS,MySQL)
AIM:
To create a Database Driven applications with Java Netbean with MySQL.
ALGORITHM:
Step-1: Open Mysql and create the DB like as Following
Database name : studentrecord
Table Name : marklist
Field Name Data type
RollNo Integer
Names Varchar(30)
M1 Numberic(5,2)
M2 Numberic(5,2)
M3 Numberic(5,2)
Tot Numberic(5,2)
Ave Numberic(5,2)
Result Varchar(20)
Queries
1. Create database and table like as following
create database studentrecord;
use studentrecord;
2. Insert record into table
create table marklist(RollNo int primary key , Names varchar(30),m1 numeric(5,2)
,m2 numeric(5,2),m3 numeric(5,2),Tot numeric(5,2),ave numeric(5,2),Result varchar(20));
3. View table
Select *from marklist
Step-2: Open Net Bean
1. Create a project like as following
File New Project click java Application click Next
Project name: StudentReport
Project Location : E:\ Finish
2. Create a new form like as following
Right click on projectname (Ex:StudentReport) newJFrame form
Class name : resultform click finish
now form will Appear on the screen ,drag necessary labels and textboxes and buttons
3. If you want Edit text for control ?
Just right click on control click edit text
4. If you want to give name for control ?
Just right click on control click change variable name
Control Text Variable Name
JTextField -1 Rno
JTextField -2 Name
JTextField -3 Sub1
JTextField -4 Sub2
JTextField -5 Sub3
JTextField -6 Total
JTextField -7 Average
JTextField -8 Result1
JButton-1 Result
JButton-2 Insert
JButton-3 Update
JButton-4 Delete
JButton-5 Search
Now form will be like as following
Step-3: How to Make connection drive
1. Go to window menu click Services (Ctrl+5)
2. Right click on word (Database) New Connection
3. Driver : MYSQL(Connector/J/driver)
4. See below we got a link (i.e: http://dev.mysql.com/downloads/connector/j/)
5. Visit this link
6. We got the web page like as following
7. In Selection Operating System Drop down box take ( Platform independent)
8. Now we got the page like as following
9. Click Download the 2-nd file (i.e: 4.8M – file )
10. We got the another page like as following
11. Finally click ( No thanks ,just Start my download ) . …
12. Now we got the Zip file (i.e: mysql-connector-java-8.0.27.zip)
13. Copy it and paste into some where you wish
14. Then right click on Zip file and click extract File click ok
15. Wait few minutes … now we got a folder (mysql-connector-java-8.0.27)
16. Open it and see there is a file called (mysql-connector-java-8.0.27.jar)
Step-4: Now again try to Make Connection Drive
1. Go to window menu click Services (Ctrl+5)
2. Right click on word (Database) New Connection
3. Driver : MYSQL(Connector/J/driver)
4. Click Add choose your mysql jar-file location
5. Ex: E:\Employees\mysql-connector-java-8.0.27
6. Select mysql-connector-java-8.0.27.jar – file
7. Then select E:\Employees\mysql-connector-java-8.0.27\ mysql-connector-java-8.0.27.jar
8. Click next
9. Check the following instructions
Driver Name : MySQL(Connector/J Driver)
Host : localhost Port: 3306
Database : studentrecord
User Name : root
Password : while you have installed
Click ( Test Connection ) …. If you all the above information is true means we go the
message ( i ) – Connection Succeeded..
Finally click finish
10. Now we got the link in the services section like as following
11. jdbc:mysql://localhost:3306/peacock?zeroDateTimeBehavior=convertToNull [root on Default
schema]
Step-5:Now Add Jar-file also in our Project
1. Right click on our Project name (Employees) Click Properties
2. Click Libraries click Add JAR/Folder
3. choose your mysql jar-file location
4. Ex: E:\Employees\mysql-connector-java-8.0.27
5. Select mysql-connector-java-8.0.27.jar – file press ok
Step-6: Double click on Result Button and type the following codes
private void resultActionPerformed(java.awt.event.ActionEvent evt) {
String s1 = sub1.getText();
String s2 = sub2.getText();
String s3 = sub3.getText();
int n1 = Integer.parseInt(s1);
int n2 = Integer.parseInt(s2);
int n3 = Integer.parseInt(s3);
if(n1>35&&n2>35&&n3>35&&n1<100&&n2<=100&&n3<=100)
{
int t = n1+n2+n3;
float avg = (float)t/3;
total.setText(""+t);
average.setText(""+avg);
result1.setText("PASS");
}
else
{
result1.setText("FAIL/INVALID DATA");
total.setText("");
average.setText("");
}}
To run the Form : Press Shift + F6 ( the form getting result like as following )
Step-6: Insert records in to the Mysql Table
Double click INSERT button and type the following codes
private void insertActionPerformed(java.awt.event.ActionEvent evt) {
try
{
String reg = Rno.getText();
int r=Integer.parseInt(reg);
String name1 = Name.getText();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/studentrecord","root","Krishnan@90") ;
PreparedStatement pst = con.prepareStatement("insert into marklist values (?,?,?,?,?,?,?,?)");
pst.setInt(1, r);
pst.setString(2, name1);
pst.setInt(3,Integer.parseInt(Sub1.getText()));
pst.setInt(4,Integer.parseInt(Sub2.getText()));
pst.setInt(5,Integer.parseInt(Sub3.getText()));
pst.setInt(6, Integer.parseInt(Total.getText()));
pst.setFloat(7, Float.parseFloat(Average.getText()));
pst.setString(8, Result1.getText());
int count = pst.executeUpdate();
System.out.println("Record Inserted");
pst.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Error "+se);
}}
To run the Form : Press Shift + F6 ( the form getting result like as following )
Step-7: Search record from Mysql Table
Double click on Search button
private void searchActionPerformed(java.awt.event.ActionEvent evt) {
String s1=JOptionPane.showInputDialog("Enter Roll Number ");
int r1=Integer.parseInt(s1);
try
{
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/studentrecord","root","Krishnan@90") ;
Statement s = con.createStatement();
ResultSet rs;
String se="select *from marklist where RollNo="+r1;
rs=s.executeQuery(se);
while(rs.next())
{
Rno.setText(""+rs.getInt(1));
Name.setText(""+rs.getString(2));
Sub1.setText(""+rs.getInt(3));
Sub2.setText(""+rs.getInt(4));
Sub3.setText(""+rs.getInt(5));
Total.setText(""+rs.getInt(6));
Average.setText(""+rs.getFloat(7));
Result1.setText(""+rs.getString(8));
}
s.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Error "+se);
}
}
To run the Form : Press Shift + F6 ( the form getting result like as following )
Step-8: Delete Record From a Table in Mysql
Double click Delete button
private void deleteActionPerformed(java.awt.event.ActionEvent evt) {
String s1=JOptionPane.showInputDialog("Enter Roll Number to Delete ");
int r1=Integer.parseInt(s1);
try
{
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/studentrecord","root","Krishnan@90") ;
Statement s = con.createStatement();
String se="delete from marklist where RollNo="+r1;
int rs=s.executeUpdate(se);
JOptionPane.showMessageDialog(rootPane, "Record Deleted");
s.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Error "+se);
}
}
To run the Form : Press Shift + F6 ( the form getting result like as following )
Step-9: Update record from a table
Double click on Update button
private void UpdateActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
try
{
String s1=JOptionPane.showInputDialog("Enter Id number to Modify");
int r1=Integer.parseInt(s1);
Rno.setText(""+r1);
String n=JOptionPane.showInputDialog("Enter New Name");
Name.setText(""+n);
String n1=JOptionPane.showInputDialog("Enter New Mark1");
int m1=Integer.parseInt(n1);
Sub1.setText(""+m1);
String n2=JOptionPane.showInputDialog("Enter New Mark2");
int m2=Integer.parseInt(n2);
Sub2.setText(""+m2);
String n3=JOptionPane.showInputDialog("Enter New Mark3");
int m3=Integer.parseInt(n3);
Sub3.setText(""+m3);
int t = m1+m2+m3;
float avg = (float)t/3;
Total.setText(""+t);
Average.setText(""+avg);
If(m1>=35 && m2>=35 && m3>=35)
{
Result1.setText("PASS");
}
else
{
Result1.setText("Fail");
}
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/studentrecord","root","Krishnan@90") ;
PreparedStatement pst=con.prepareStatement("update marklist set sname=?,m1=?,m2=?,m3=?,tot=?,ave=?,result=? where RollNo=?");
pst.setString(1,n);
pst.setInt(2,m1);
pst.setInt(3,m2);
pst.setInt(4,m3);
pst.setInt(5,t);
pst.setFloat(6,avg);
pst.setString(7,Result1.getText());
pst.setInt(8,r1);
int c=pst.executeUpdate();
System.out.println("Record Updated");
con.close();
pst.close();
}
catch(SQLException se)
{
System.out.println("Error:"+se);
}
}
RESULT:
Thus database driven application was successfully created using NetBeans IDE with Mysql.
