Create an RDBMS personal development environment creating a

Create an RDBMS personal development environment, creating a simple schema and INSERT, UPDATE AND DELETE operations on database using JDBC and interactive mode. 1. Use JDBC driver( TYPE 4) 2. Create database with a schema of sales transaction that consist of 1 table and have a PRIMARY KEY (customer ID) in that table. 3. insert SQL statements with many entries representing sales transactions 4. Query table with different SELECT statements such as Aggregate functions, ORDER BY, GROUP BY..

Solution

Going to create sales Database
   create database Sales;
   use Sales;

Going to create customer table   

CREATE TABLE `customer` (
    `id` int(11)            NOT NULL    AUTO_INCREMENT,
    `name` varchar(45)        NOT NULL,
    `password` varchar(45)    NOT NULL,
    `age` varchar(45)        NOT NULL,
     PRIMARY KEY (`id`)
);

-----------Create database access using JDBC Driver ---------------------
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class customer {

    private Integer id;
    private String name;
    private String password;
    private Integer age;
    public customer() {

    }
    public customer(String name, String password, Integer age) {

        this.name = name;
        this.password = password;
        this.age = age;
    }
    public customer(Integer id, String name, String password, Integer age) {

        this.id = id;
        this.name = name;
        this.password = password;
        this.age = age;
    }
    public Integer get_Age() {

       return age;
    }
    public void set_Age(Integer age) {

        this.age = age;
    }
    public Integer get_Id() {

        return id;
    }
    public void set_Id(Integer id) {

        this.id = id;
    }
    public String get_Name() {

        return name;
    }

    public void set_Name(String name) {

        this.name = name;
    }
    public String get_Pass() {

        return password;
    }
    public void set_Pass(String password) {

        this.password = password;
    }
}
/**----- Connect to Database ----------*/

public class Databaseconnection {

    public static final String URL = \"jdbc:mysql://localhost:3201/sales\";
    public static final String USER = \"user\";
    public static final String PASS = \"password\";

    /** connecting to database */

    public static Connection getConnection()
    {
      try {
           DriverManager.registerDriver(new Driver());
           return DriverManager.getConnection(URL, USER, PASS);
      }catch (SQLException exception) {

           throw new RuntimeException(\"Database connection error\", exception);
      }
    }
    /**------ Connection testing ---------*/

    public static void main(String[] args) {

        Connection conn = Databaseconnection.getConnection();
    }
}

/* -------- Select statement ------------------*/
public customer getcustomer(int id) {

    Connection conn = Databaseconnection.getConnection();

        try {
            Statement stmt = conn.createStatement();
          
           /* select particular customer data */
            ResultSet rset = stmt.executeQuery(\"SELECT * FROM customer WHERE id=\" + id);
          
           /* select all customer data */
           //ResultSet rset = stmt.executeQuery(\"SELECT * FROM customer \");
          
            if(rset.next())
            {
                customer cust = new customer();
                cust.set_Id( rset.getInt(\"id\") );
                cust.set_Name( rset.getString(\"name\") );
                cust.set_Pass( rset.getString(\"password\") );
                cust.set_Age( rset.getInt(\"age\") );
                return cust;
            }
        } catch (SQLException exception) {

            exception.printStackTrace();
        }
    return null;
}

/ *** ------ inserting into customer table ------------*/
public boolean insertcustomer(customer cust) {

    Connector con = new Connector();
    Connection conn = con.getConnection();
  
    try {

        PreparedStatement pstatment = conn.prepareStatement(\"INSERT INTO cust(name, password, age) VALUES (NULL, ?, ?, ?)\");
      
        pstatment.setString(1, cust.get_Name());
        pstatment.setString(2, cust.get_Pass());
        pstatment.setInt(3, cust.get_Age());
        int i = pstatment.executeUpdate();
       if(i == 1) {

        return true;
      }
    } catch (SQLException exception) {

           exception.printStackTrace();
    }
    return false;
}

/* -------- udate the customer table ------*/
public boolean updatecustomer(customer cust) {

    Connector con = new Connector();
    Connection conn = con.getConnection();
    try {

        PreparedStatement pstatment = conn.prepareStatement(\"UPDATE cust SET name=?, pass=?, age=? WHERE id=?\");
        pstatment.setString(1, cust.get_Name());
        pstatment.setString(2, cust.get_Pass());
        pstatment.setInt(3, cust.get_Age());
        pstatment.setInt(4, cust.get_Id());
       int i = pstatment.executeUpdate();
       if(i == 1) {

           return true;
      }
    } catch (SQLException exception) {

        exception.printStackTrace();
    }
    return false;
}
/* ------- delete data from customer table -----------*/
//public boolean deletecustomer(string name)

public boolean deletecustomer(int id) {

    Connector con = new Connector();
    Connection conn = con.getConnection();
    try {

        Statement st = conn.createStatement();
        int i = st.executeUpdate(\"DELETE FROM cust WHERE id=\" + id);
       // int i = st.executeUpdate(\"DELETE FROM cust WHERE name=\" + name);
      if(i == 1) {
           return true;
      }
    } catch (SQLException exception) {
           exception.printStackTrace();
    }
        return false;
}