Write an application in Java that creates a database in Microsoft SQL Express na

Question

Write an application in Java that creates a database in Microsoft SQL Express named Personnel. The database should have a table named Employee, with columns for employee ID, name, position, and hourly pay rate. The employee ID should be the primary key. Write a GUI application to add these employees to the database:

ID Number Name Position    Pay Rate
00385    Jean Jackson Chairman    35.65
02468    Susan Peterson    President    50.95
1234 Alan Cook    Instructor    15.15
13579 Bill Jones Director 32.75
24759    Greg Gomez Vice President    42.25
98680    Geri Smith    General Manager 30.6
9999    John James    Student 5.5

Then write a GUI application that allows the user to look up an employee in the Personnel database using the employee ID, then change any of the employee's information except the employee ID.

Explanation / Answer

ANS:-

Given that,

The database should have a table named Employee, with columns for employee ID, name, position, and hourly pay rate. The employee ID should be the primary key.

program:-

import java.sql.*;

public class DatabaseCreator {
  
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
   static final String DB_URL = "jdbc:mysql://localhost/";

   // Database credentials
   static final String USER = "username";
   static final String PASS = "password";
  
   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
      Class.forName("com.mysql.jdbc.Driver");

      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL, USER, PASS);

      System.out.println("Creating database...");
      stmt = conn.createStatement();
     
      String sql = "CREATE DATABASE Personnel";
      stmt.executeUpdate(sql);
      System.out.println("Database created successfully...");
      System.out.println("Creating table in given database...");
     
      sql = "CREATE TABLE Employee " +
                   "(id INTEGER not NULL, " +
                   " name VARCHAR(255), " +
                   " position VARCHAR(255), " +
                   " pay_rate INTEGER, " +
                   " PRIMARY KEY ( id ))";
      stmt.executeUpdate(sql);
      System.out.println("Created table in given database...");
      String query = " insert into Employee (id, name, position, pay_rate)"
        + " values (?, ?, ?, ?)";
      PreparedStatement preparedStmt = conn.prepareStatement(query);
      preparedStmt.setInt(1, 1);
      preparedStmt.setString(2, "John");
      preparedStmt.setString(3, "Manager");
      preparedStmt.setInt(4, 100);
      preparedStmt.execute();
     
      preparedStmt = conn.prepareStatement(query);
      preparedStmt.setInt(1, 2);
      preparedStmt.setString(2, "Smith");
      preparedStmt.setString(3, "Accountant");
      preparedStmt.setInt(4, 200);
      preparedStmt.execute();

      preparedStmt = conn.prepareStatement(query);
      preparedStmt.setInt(1, 3);
      preparedStmt.setString(2, "Barry");
      preparedStmt.setString(3, "Seceretary");
      preparedStmt.setInt(4, 150);
      preparedStmt.execute();
  
      preparedStmt = conn.prepareStatement(query);
      preparedStmt.setInt(1, 4);
      preparedStmt.setString(2, "Hary");
      preparedStmt.setString(3, "Programmer");
      preparedStmt.setInt(4, 250);
      preparedStmt.execute();

      preparedStmt = conn.prepareStatement(query);
      preparedStmt.setInt(1, 4);
      preparedStmt.setString(2, "Sally");
      preparedStmt.setString(3, "Engineer");
      preparedStmt.setInt(4, 250);
      preparedStmt.execute();
     
      conn.close();
   }catch(SQLException se){
     
      se.printStackTrace();
   }catch(Exception e){
     
      e.printStackTrace();
   }finally{
    
      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }
   }
  
}
}

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.