Skip to content

Commit 15ffcff

Browse files
DhirajGadekarDhirajGadekar
committed
JDBC
1 parent 5fc2551 commit 15ffcff

8 files changed

+393
-0
lines changed

‎JDBC/1_Program.java

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
import java.sql.*;
2+
3+
class JDBCDemo {
4+
5+
public static void main(String[] args) throws ClassNotFoundException {
6+
7+
String url = "jdbc:mysql://localhost:3306/College";
8+
String usr = "root";
9+
String pwd = "Dhiraj@7465";
10+
11+
try {
12+
13+
//Class.forName("com.mysql.cj.jdbc.Driver");
14+
Connection con = DriverManager.getConnection(url, usr, pwd);
15+
16+
if(con.isClosed()) {
17+
18+
System.out.println("Connection is Closed");
19+
} else {
20+
21+
System.out.println("Connection is Created");
22+
}
23+
} catch(SQLException s) {
24+
25+
s.printStackTrace();
26+
}
27+
}
28+
}

‎JDBC/2_CreateTable.java

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,32 @@
1+
import java.sql.*;
2+
3+
class CreateTable {
4+
5+
public static void main(String[] args) {
6+
7+
String url = "jdbc:mysql://localhost:3306/College";
8+
String usr = "root";
9+
String pwd = "Dhiraj@7465";
10+
11+
try {
12+
13+
Class.forName("com.mysql.cj.jdbc.Driver");
14+
15+
Connection con = DriverManager.getConnection(url, usr, pwd);
16+
17+
Statement smt = con.createStatement();
18+
String query = "Create Table Student(Roll_No int(4) not null, Name varchar(100) not null, marks float not null, city varchar(50), grade varchar(2))";
19+
20+
smt.executeUpdate(query);
21+
System.out.println("Table Created...");
22+
23+
con.close();
24+
} catch(ClassNotFoundException cnf) {
25+
26+
System.out.println("Class Not Found...");
27+
} catch(Exception se) {
28+
29+
se.toString();
30+
}
31+
}
32+
}

‎JDBC/3_InsertValueInTable.java

Lines changed: 79 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,79 @@
1+
2+
import java.io.*;
3+
import java.sql.*;
4+
5+
class InsertValue {
6+
7+
void addData(Connection con) {
8+
9+
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
10+
11+
int rollno = 0;
12+
String name = null;
13+
float marks = 0.0f;
14+
String city = null;
15+
String grade = null;
16+
try {
17+
System.out.print("Enter Roll No : ");
18+
rollno = Integer.parseInt(br.readLine());
19+
20+
System.out.print("Enter Name : ");
21+
name = br.readLine();
22+
23+
System.out.print("Enter Marks : ");
24+
marks = Float.parseFloat(br.readLine());
25+
26+
System.out.print("Enter City : ");
27+
city = br.readLine();
28+
29+
System.out.print("Enter Grade : ");
30+
grade = br.readLine();
31+
32+
} catch(IOException ioe) {
33+
34+
ioe.printStackTrace();
35+
}
36+
try {
37+
String q = "Insert into Student (Name, marks, city, grade) value (?, ?, ?, ?)";
38+
39+
PreparedStatement psmt = con.prepareStatement(q);
40+
41+
//psmt.setInt(1, rollno);
42+
psmt.setString(1, name);
43+
psmt.setFloat(2, marks);
44+
psmt.setString(3, city);
45+
psmt.setString(4, grade);
46+
47+
int num = psmt.executeUpdate();
48+
49+
System.out.println("No row Affected : " + num);
50+
con.close();
51+
} catch(SQLIntegrityConstraintViolationException e) {
52+
53+
System.out.println("Enter Different Roll no : ");
54+
} catch(Exception e) {
55+
56+
e.printStackTrace();
57+
}
58+
59+
}
60+
public static void main(String[] args) {
61+
62+
InsertValue iv = new InsertValue();
63+
String url = "jdbc:mysql://localhost:3306/College";
64+
String usr = "root";
65+
String pwd = "Dhiraj@7465";
66+
67+
try {
68+
69+
Class.forName("com.mysql.cj.jdbc.Driver");
70+
Connection con = DriverManager.getConnection(url, usr, pwd);
71+
72+
iv.addData(con);
73+
con.close();
74+
} catch(Exception e) {
75+
76+
e.printStackTrace();
77+
}
78+
}
79+
}

‎JDBC/4_ImageInsert.java

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,31 @@
1+
import java.sql.*;
2+
import java.io.*;
3+
class ImageInsert {
4+
5+
public static void main(String[] args) {
6+
7+
String url = "jdbc:mysql://localhost:3306/College";
8+
String usr = "root";
9+
String pwd = "Dhiraj@7465";
10+
try {
11+
12+
Class.forName("com.mysql.cj.jdbc.Driver");
13+
14+
Connection con = DriverManager.getConnection(url, usr, pwd);
15+
16+
String q = "Insert into StudentImage (image) value(?)";
17+
18+
PreparedStatement psmt = con.prepareStatement(q);
19+
20+
FileInputStream file = new FileInputStream("profile.jpeg");
21+
22+
psmt.setBinaryStream(1, file, file.available());
23+
24+
int num = psmt.executeUpdate();
25+
System.out.println(num);
26+
con.close();
27+
} catch(Exception e) {
28+
29+
}
30+
}
31+
}

‎JDBC/5_UpdateQuery.java

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
import java.io.*;
2+
import java.sql.*;
3+
4+
class UpdateData {
5+
6+
public static void main(String[] args) throws IOException {
7+
8+
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
9+
String url = "jdbc:mysql://localhost:3306/College";
10+
String usr = "root";
11+
String pwd = "Dhiraj@7465";
12+
try {
13+
Class.forName("com.mysql.cj.jdbc.Driver");
14+
Connection con = DriverManager.getConnection(url, usr, pwd);
15+
16+
String q = "Update Student Set grade = ? where Roll_No = ?";
17+
18+
PreparedStatement psmt = con.prepareStatement(q);
19+
20+
System.out.println("Enter New Grade : ");
21+
String grade = br.readLine();
22+
23+
System.out.println("Enter Roll_No : ");
24+
int Roll_No = Integer.parseInt(br.readLine());
25+
26+
psmt.setString(1, grade);
27+
psmt.setInt(2, Roll_No);
28+
29+
int num = psmt.executeUpdate();
30+
31+
System.out.println(num);
32+
33+
con.close();
34+
} catch(Exception e) {
35+
36+
}
37+
}
38+
}

‎JDBC/6_SelectQuery.java

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
import java.sql.*;
2+
3+
class SelectQuery {
4+
5+
public static void main(String[] args) {
6+
7+
String url = "jdbc:mysql://localhost:3306/College";
8+
String usr = "root";
9+
String pwd = "Dhiraj@7465";
10+
11+
try {
12+
13+
Class.forName("com.mysql.cj.jdbc.Driver");
14+
Connection con = DriverManager.getConnection(url, usr, pwd);
15+
16+
Statement stmt = con.createStatement();
17+
ResultSet set = stmt.executeQuery("Select * from Student");
18+
19+
while(set.next()) {
20+
21+
System.out.println(set.getInt(1) + " : " + set.getString(2) + " : " + set.getFloat(3) + " : " + set.getString(4) + " : " + set.getString(5));
22+
}
23+
con.close();
24+
} catch(Exception obj) {
25+
26+
27+
}
28+
}
29+
}

‎JDBC/7_AggregateFunctions.java

Lines changed: 156 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,156 @@
1+
import java.sql.*;
2+
import java.io.*;
3+
class AggregateFunction {
4+
5+
private static Connection con;
6+
7+
int totalStudents() {
8+
9+
String q = "Select count(Name) from Student";
10+
try {
11+
Statement stmt = con.createStatement();
12+
13+
ResultSet set = stmt.executeQuery(q);
14+
if(set.next()) {
15+
16+
return set.getInt(1);
17+
}
18+
} catch(Exception e) {
19+
20+
e.printStackTrace();
21+
}
22+
return -1;
23+
}
24+
float avgMarks() {
25+
26+
String q = "Select avg(marks) from Student";
27+
try {
28+
Statement stmt = con.createStatement();
29+
30+
ResultSet set = stmt.executeQuery(q);
31+
if(set.next()) {
32+
33+
return set.getFloat(1);
34+
}
35+
} catch(Exception e) {
36+
37+
e.printStackTrace();
38+
}
39+
return 0.0f;
40+
}
41+
void maxMarksDetails() {
42+
43+
44+
String q = "Select * from Student where marks = (Select max(marks) from Student)";
45+
try {
46+
Statement stmt = con.createStatement();
47+
48+
ResultSet set = stmt.executeQuery(q);
49+
if(set.next()) {
50+
51+
System.out.println("Roll No : " + set.getInt(1));
52+
System.out.println("Name : " + set.getString(2));
53+
System.out.println("Marks : " + set.getFloat(3));
54+
System.out.println("City : " + set.getString(4));
55+
System.out.println("Grade : " + set.getString(5));
56+
} else {
57+
58+
System.out.println("Student Not Found....!");
59+
}
60+
} catch(Exception e) {
61+
62+
e.printStackTrace();
63+
}
64+
}
65+
void minMarksDetails() {
66+
67+
String q = "Select * from Student where marks = (Select min(marks) from Student)";
68+
try {
69+
Statement stmt = con.createStatement();
70+
71+
ResultSet set = stmt.executeQuery(q);
72+
if(set.next()) {
73+
74+
System.out.println("Roll No : " + set.getInt(1));
75+
System.out.println("Name : " + set.getString(2));
76+
System.out.println("Marks : " + set.getFloat(3));
77+
System.out.println("City : " + set.getString(4));
78+
System.out.println("Grade : " + set.getString(5));
79+
} else {
80+
81+
System.out.println("Student Not Found....!");
82+
}
83+
} catch(Exception e) {
84+
85+
e.printStackTrace();
86+
}
87+
}
88+
float sumMarks() {
89+
90+
String q = "Select sum(marks) from Student";
91+
try {
92+
Statement stmt = con.createStatement();
93+
94+
ResultSet set = stmt.executeQuery(q);
95+
if(set.next()) {
96+
97+
return set.getFloat(1);
98+
}
99+
} catch(Exception e) {
100+
101+
e.printStackTrace();
102+
}
103+
return 0.0f;
104+
}
105+
public static void main(String[] args) throws IOException{
106+
107+
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
108+
AggregateFunction obj = new AggregateFunction();
109+
110+
String url = "jdbc:mysql://localhost:3306/College";
111+
String usr = "root";
112+
String pwd = "Dhiraj@7465";
113+
try {
114+
115+
con = DriverManager.getConnection(url, usr, pwd);
116+
char ch;
117+
do {
118+
119+
System.out.println("1. Count of Total Students : ");
120+
System.out.println("2. Average of Student Marks : ");
121+
System.out.println("3. Max Marks Student Details : ");
122+
System.out.println("4. Min Marks Student Details : ");
123+
System.out.println("5. Sum of Student Marks : \n");
124+
125+
System.out.println("Enter your Choice : ");
126+
int choice = Integer.parseInt(br.readLine());
127+
128+
switch(choice) {
129+
130+
case 1 :
131+
System.out.println("Total Student Count : " + obj.totalStudents() + "\n");
132+
break;
133+
case 2 :
134+
System.out.println("Average of Student Marks: " + obj.avgMarks() + "\n");
135+
break;
136+
case 3 :
137+
obj.maxMarksDetails();
138+
break;
139+
case 4 :
140+
obj.minMarksDetails();
141+
break;
142+
case 5 :
143+
System.out.println("Sum of Student Marks : " + obj.sumMarks() + "\n");
144+
break;
145+
default :
146+
System.out.println("Wrong Choice");
147+
}
148+
System.out.println("Do want Continue : ");
149+
ch = (char)br.readLine().charAt(0);
150+
} while(ch == 'Y' || ch == 'y');
151+
} catch(Exception e) {
152+
153+
e.printStackTrace();
154+
}
155+
}
156+
}

‎JDBC/profile.jpeg

17.6 KB
Loading

0 commit comments

Comments
 (0)