I am neo java week 15

 

If any error comes comment below...

Skill builder

1. Write a Java (JDBC) program to connect with the MySQL database, insert the given data in the table, and display the table contents using ResultSet.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Scanner; class RowInsertExample{ public static void main(String args[]) throws Exception{ String url="jdbc:mysql://localhost/ri_db"; String username ="test"; String password ="test123"; String query = "INSERT INTO STUDENT (id,name,average_marks) VALUES (?,?,?);"; Scanner sc =new Scanner(System.in); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url,username,password); PreparedStatement st = con.prepareStatement(query); int n =Integer.parseInt(sc.nextLine()); for(int i=0;i<n;i++) { int id=Integer.parseInt(sc.nextLine()); String name =sc.nextLine(); int average_marks = Integer.parseInt(sc.nextLine()); st.setInt(1,id); st.setString(2,name); st.setInt(3,average_marks); st.executeUpdate(); } ResultSet rs = st.executeQuery("select *from STUDENT"); while(rs.next()) { System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getInt(3)); } st.close(); con.close(); } }

2. Write a Java program to get below input from user and insert in in table and search the record based on department.

import java.sql.*; import java.util.Scanner; class JDBCExample { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/ri_db"; static final String USER = "test"; static final String PASS = "test123"; public static void main(String[] args) { Connection conn = null; Connection myConn = null; Statement stmt = null; PreparedStatement st = null; try{ Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(DB_URL, USER, PASS); stmt = conn.createStatement(); String sql; Scanner sc =new Scanner(System.in); int n=Integer.parseInt(sc.nextLine()); for(int i=0;i<n;i++) { int sid =Integer.parseInt(sc.nextLine()); String sname =sc.nextLine(); String dname =sc.nextLine(); String address =sc.nextLine(); double gpa =Double.parseDouble(sc.nextLine()); sql = "insert into BILL (sid,sname,dname,address,gpa) values(?,?,?,?,?)"; st = conn.prepareStatement(sql); st.setInt(1, sid); st.setString(2, sname); st.setString(3, dname); st.setString(4, address); st.setDouble(5, gpa); st.execute(); } String search = sc.nextLine(); sql= "SELECT * FROM BILL Where DNAME='"+search+"'"; ResultSet rs = stmt.executeQuery(sql); int count=0; while (rs.next()) { count++; int sid = rs.getInt("SID"); String sName = rs.getString("SNAME"); String dName = rs.getString("DNAME"); String address = rs.getString("ADDRESS"); double gpa =rs.getDouble("GPA"); System.out.format("%s %s %s %s %s\n", sid, sName, dName, address,gpa); } if(count==0) System.out.println("No students in "+search+" department"); }catch(SQLException se){ se.printStackTrace(); }catch(Exception e){ e.printStackTrace(); }finally{ try{ if(stmt!=null) conn.close(); }catch(SQLException se){ } try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } } } }

3. As a principal of college, I need to find the faculty details based on their gender and department.

import java.sql.*; import java.util.Scanner; class Employee { public static void main(String args[]) throws Exception { String url="jdbc:mysql://localhost/ri_db"; String username ="test"; String password ="test123"; String query = "select *from employee where gender=? AND dept=?"; Scanner sc = new Scanner(System.in); String gender = sc.nextLine(); String dept=sc.nextLine(); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url,username,password); PreparedStatement st = con.prepareStatement(query); st.setString(1,gender); st.setString(2,dept); ResultSet rs = st.executeQuery(); while(rs.next()) { System.out.println("Employee ID : "+rs.getInt(1)+" Name : "+rs.getString(3)+" "+rs.getString(4)); } } }

4. Write a Java (JDBC) program to connect with MySQL database and print all the employee details who have salaries greater than or equal to the given input.

import java.sql.*; import java.util.*; class EmployeeDetails { public static void main(String [] args) throws Exception { String url="jdbc:mysql://localhost/ri_db"; String username ="test"; String password ="test123"; Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url,username,password); String query = "select * from EMPLOYEE where salary>=?"; Scanner sc = new Scanner(System.in); double s1 = sc.nextDouble(); PreparedStatement st = con.prepareStatement(query); st.setDouble(1, s1); ResultSet rs = st.executeQuery(); while(rs.next()) { System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getFloat(3)); } st.close(); con.close(); } }

5. Write a Java (JDBC) program to connect with MySQL database and print the book details after deleting a particular book from the database.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Scanner; class RowDelete{ public static void main(String args[]) throws Exception{ String url="jdbc:mysql://localhost/ri_db"; String username ="test"; String password ="test123"; String query = "delete from book where name =?;"; Scanner sc =new Scanner(System.in); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url,username,password); PreparedStatement st = con.prepareStatement(query); String book =sc.nextLine(); st.setString(1,book); st.execute(); ResultSet rs = st.executeQuery("select *from book"); while(rs.next()) { System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)); } st.close(); con.close(); } }

6. Write a Java (JDBC) program to connect with the MySQL database and display the student details based on the user input (dept).

import java.sql.*; import java.util.Scanner; class Employee { public static void main(String args[]) throws Exception { String url="jdbc:mysql://localhost/ri_db"; String username ="test"; String password ="test123"; String query = "select *from student where dept=?"; Scanner sc = new Scanner(System.in); String dept=sc.nextLine(); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url,username,password); PreparedStatement st = con.prepareStatement(query); st.setString(1,dept); ResultSet rs = st.executeQuery(); while(rs.next()) { System.out.println(rs.getInt(1)+" "+rs.getString(2)); } } }

7. Write a Java (JDBC) program to connect with MySQL database and check whether the given user name and password are valid

import java.sql.*; import java.util.Scanner; class LoginValidation { public static void main(String args[]) throws Exception { String url="jdbc:mysql://localhost/ri_db"; String username ="test"; String password ="test123"; String query = "select *from userdetails where user_name=?"; Scanner sc = new Scanner(System.in); String user_name = sc.nextLine(); String pass_word =sc.nextLine(); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url,username,password); PreparedStatement st = con.prepareStatement(query); st.setString(1, user_name); ResultSet rs = st.executeQuery(); rs.next(); //System.out.println(rs.getString(2)); if(rs.getString(2).equals(pass_word)) { System.out.println("Login Successful"); } else System.out.println("Invalid username/password"); } }

8. Write a Java program to get below input from user and insert in in table.

import java.sql.*; import java.util.Scanner; class JDBCExample { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/ri_db"; static final String USER = "test"; static final String PASS = "test123"; public static void main(String[] args) { Connection conn = null; Connection myConn = null; Statement stmt = null; PreparedStatement st = null; try{ Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(DB_URL, USER, PASS); stmt = conn.createStatement(); String sql; Scanner sc =new Scanner(System.in); int n=Integer.parseInt(sc.nextLine()); for(int i=0;i<n;i++) { int sid =Integer.parseInt(sc.nextLine()); String sname =sc.nextLine(); String dname =sc.nextLine(); String address =sc.nextLine(); double gpa =Double.parseDouble(sc.nextLine()); sql = "insert into BILL (sid,sname,dname,address,gpa) values(?,?,?,?,?)"; st = conn.prepareStatement(sql); st.setInt(1, sid); st.setString(2, sname); st.setString(3, dname); st.setString(4, address); st.setDouble(5, gpa); st.execute(); } sql= "SELECT * FROM BILL"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int sid = rs.getInt("SID"); String sName = rs.getString("SNAME"); String dName = rs.getString("DNAME"); String address = rs.getString("ADDRESS"); double gpa =rs.getDouble("GPA"); System.out.format("%s %s %s %s %s\n", sid, sName, dName, address,gpa); } }catch(SQLException se){ se.printStackTrace(); }catch(Exception e){ e.printStackTrace(); }finally{ try{ if(stmt!=null) conn.close(); }catch(SQLException se){ } try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } } } }

Challange yourself

9. Write a Java (JDBC) program to connect with MySQL database and insert the given data in the table and display the table contents using ResultSet.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Scanner; class RowInsertExample{ public static void main(String args[]) throws Exception{ String url="jdbc:mysql://localhost/ri_db"; String username ="test"; String password ="test123"; String query = "INSERT INTO OFFICE (EID,ENAME, ESALARY) VALUES (?,?,?);"; Scanner sc =new Scanner(System.in); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url,username,password); PreparedStatement st = con.prepareStatement(query); int n =Integer.parseInt(sc.nextLine()); for(int i=0;i<n;i++) { int eid=Integer.parseInt(sc.nextLine()); String ename =sc.nextLine(); int esalary = Integer.parseInt(sc.nextLine()); st.setInt(1,eid); st.setString(2,ename); st.setInt(3,esalary); st.executeUpdate(); } ResultSet rs = st.executeQuery("select *from OFFICE"); while(rs.next()) { System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getInt(3)); } st.close(); con.close(); } }

10. As a teacher, I need to remove few students from the database and check other students details.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Scanner; class RowDelete{ public static void main(String args[]) throws Exception{ String url="jdbc:mysql://localhost/ri_db"; String username ="test"; String password ="test123"; String query = "delete from student where dept =? AND cgpa > ?;"; Scanner sc =new Scanner(System.in); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url,username,password); PreparedStatement st = con.prepareStatement(query); String dept =sc.nextLine(); double cgpa= Double.parseDouble(sc.nextLine()); st.setString(1,dept); st.setDouble(2,cgpa); st.execute(); ResultSet rs = st.executeQuery("select *from student"); while(rs.next()) { System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) + " " + rs.getString(5) + " " + rs.getString(6) + " " + rs.getString(7)+ rs.getString(8)+" "+rs.getString(9)); } st.close(); con.close(); } }

11. Create a class ItemType with the following private attributes

import java.sql.*; import java.util.Scanner; class ItemType{ long id; String name; Double deposit; Double costPerDay; public ItemType() { super(); } public ItemType(long id, String name, Double deposit, Double costPerDay) { super(); this.id = id; this.name = name; this.deposit = deposit; this.costPerDay = costPerDay; } } class ItemTypeDAO { public ItemType getItemType(int id) throws Exception { String url="jdbc:mysql://localhost/rootuser"; String username ="rootuser"; String password ="rootuser"; String query = "select *from itemtype where id="+id; Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection(url,username,password); Statement st = con.createStatement(); ResultSet rs =st.executeQuery(query); rs.next(); String name = rs.getString(2); Double deposit =rs.getDouble(3); Double costPerDay =rs.getDouble(4); ItemType it = new ItemType(); it.id=id; it.name=name; it.deposit=deposit; it.costPerDay=costPerDay; return it; } } class Main { public static void main(String args[]) throws Exception { ItemTypeDAO dao = new ItemTypeDAO(); Scanner sc = new Scanner(System.in); int id = Integer.parseInt(sc.nextLine()); ItemType i1 =dao.getItemType(id); System.out.format("%-5s %-15s %-10s %s\n","ID","Name","Deposit","Cost per day"); System.out.format("%-5s %-15s %-10s %s\n",i1.id,i1.name,i1.deposit,i1.costPerDay); } }