Need a unique gift idea?
A Pastebin account makes a great Christmas gift
SHARE
TWEET

DBClassCode

a guest Nov 21st, 2018 111 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. package dboperationdemo;
  2. import java.sql.*;
  3. import java.util.Scanner;
  4.  
  5. public class DBOperationDemo {
  6.     static Connection conn;
  7.     static Statement stmt;
  8.     static DatabaseMetaData metadata = null;
  9.  
  10.  
  11.     public static void main(String[] args) {
  12.         loadDriver();
  13.         createConnection();
  14.         createStatement();
  15.         //insertData();
  16.         //readData();
  17.         //System.out.println("Data after update operation.................");  
  18.         //updateData();
  19.         //System.out.println("Data after delete operation.................");  
  20.         //deleteData();
  21.         //createTable("AllStudents");
  22.         System.out.println("Data with MetaData");
  23.         readDataWithMetadata();
  24.         System.out.println();
  25.         readDataByeCourseName();
  26.     }    
  27.     static void loadDriver(){
  28.         try{
  29.             Class.forName("com.mysql.cj.jdbc.Driver");
  30.             System.out.println("Driver loaded successfully....!!");        
  31.         }
  32.         catch (Exception ex){
  33.             System.out.println("Driver load ERROR: " + ex);
  34.         }
  35.     }  
  36.     static void createConnection(){
  37.         try{
  38.             conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "***********");
  39.             System.out.println("Connection established....!!");
  40.         }
  41.         catch(SQLException ex){
  42.             System.out.println("Connection  ERROR: " + ex);
  43.         }        
  44.     }
  45.    
  46.     static void createStatement(){
  47.         try{
  48.         stmt = conn.createStatement();
  49.         System.out.println("Statement created successfully....!!! ");
  50.         }
  51.         catch(SQLException ex){
  52.             System.out.println("Statement  ERROR: " + ex);
  53.         }    
  54.     }
  55.     static void insertData(){
  56.         String insertQuery = "insert into course values (88, 'MySQL')";
  57.         try{
  58.             stmt.executeUpdate(insertQuery);
  59.              System.out.println("Data inserted.....!!");
  60.         }
  61.         catch(SQLException ex){
  62.              System.out.println("Insertion  ERROR: " + ex);            
  63.         }
  64.         }
  65.     static void readData(){
  66.         String readQuery = "select * from course";
  67.         try{
  68.             ResultSet rs = stmt.executeQuery(readQuery);
  69.             while(rs.next()){
  70.                 String cName = rs.getString(2);
  71.                 int cid = rs.getInt(1);
  72.                
  73.                 System.out.println("Course ID = " + cid + ", Course Name = " + cName);            
  74.             }
  75.            
  76.         }
  77.         catch(SQLException ex){
  78.             System.out.println("Data read  ERROR: " + ex);
  79.         }
  80.     }
  81.     static void updateData(){
  82.         String updateQuery = "update course set CourseName = 'Algorithm' where courseID = 11";
  83.         try{
  84.             stmt.executeUpdate(updateQuery);
  85.             readData();
  86.         }
  87.         catch(SQLException ex){
  88.             System.out.println("Update ERROR: " + ex);
  89.         }
  90.     }
  91.    
  92.     static void deleteData(){
  93.         String deleteQuery = "delete from course where courseID = 11";
  94.         try{
  95.             stmt.executeUpdate(deleteQuery);
  96.             System.out.println("Deleted successfully....!!");
  97.             readData();
  98.         }
  99.         catch(SQLException ex){
  100.             System.out.println("Deletion ERROR: " + ex);
  101.         }
  102.     }
  103.     static void createTable(String tName){
  104.         String createTableQuery = "create table " +tName + " (id int primary key, name varchar(20), city varchar(20))";
  105.         try{
  106.             stmt.executeUpdate(createTableQuery);
  107.             System.out.println("Table created successfully....!!");            
  108.         }
  109.         catch(SQLException ex){
  110.             System.out.println("Table creation ERROR: " + ex);
  111.         }
  112.     }
  113.    
  114.     public static void readDataWithMetadata(){
  115.         String selectQuery = "select * from coure";
  116.         try{
  117.             ResultSet rs = stmt.executeQuery(selectQuery);
  118.             int noOfColoumn = metadata.getColumnCount();
  119.             for(int i =1; i <= noOfColoumn; i++){
  120.                 System.out.printf("%-10s\t",metadata.getColumnName(i));
  121.                 System.out.println();
  122.             }
  123.                 while(rs.next()){
  124.                     for (int i =1; i <= noOfColoumn; i++)
  125.                         System.out.printf("%-10s\t",rs.getObject(i));
  126.                     System.out.println();
  127.                    
  128.                     DatabaseMetaData dbMetaData = conn.getMetaData();
  129.                     System.out.println("databse URL: " +dbMetaData.getURL());
  130.                     System.out.println("database username: " + dbMetaData.getUserName());
  131.                     System.out.println("database product name: " + dbMetaData.getDatabaseProductName());
  132.                     System.out.println("database product version: " + dbMetaData.getDatabaseProductVersion());
  133.  
  134.                 }
  135.             }
  136.          catch(SQLException ex){
  137.             System.out.println("Meta Data Readd ERROR! "+ex);
  138.         }
  139.     }
  140.    
  141.    
  142.    
  143.    
  144.     public static void readDataByeCourseName(){
  145.         Scanner input = new Scanner(System.in);
  146.         System.out.println("Please Enter course id");
  147.         int id = input.nextInt();
  148.         System.out.println("Please enter course name");
  149.         String course = input.next();
  150.         String query = "Select * from Course where courseID = ? and CourseName = ?";
  151.         try{
  152.             PreparedStatement  preparedStmt = conn.prepareStatement(query);
  153.             preparedStmt.setInt(1,id);
  154.             preparedStmt.setString(2,course);
  155.             ResultSet rs = preparedStmt.executeQuery();
  156.             while(rs.next()){
  157.                 System.out.println(rs.getInt(1));
  158.                 System.out.println(rs.getString(2));
  159.             }
  160.         }
  161.         catch(SQLException ex){
  162.             System.out.println("Prepared Statment ERROR! "+ex);
  163.         }
  164.     }
  165. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top