Question

Create a table called product containing a product number, company name, model number, product name. What is my primary key? Which datatypes should I use? Please submit a printout of the commands used for creating the above table and the results of your queries/commands.

PLEASE USE JAVA & H2DATABASE

Answer 1

Solution:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Product {

static final String driver = "org.h2.Driver";   
static final String url = "jdbc:h2:~/test";

static final String user = "sa";
static final String pass = "";
  
public static void main(String[] args) {
  
Connection con = null;
Statement s1 = null;
  
try {

Class.forName(driver);
con = DriverManager.getConnection(url,user,pass);

s1 = con.createStatement();

String sql = "CREATE TABLE product " + "(productnumber INTEGER not NULL, " + " companyname VARCHAR(255), " + " modelnumber VARCHAR(255), " + " productname VARCHAR(255), " + " PRIMARY KEY ( productnumber ))";
  
s1.executeUpdate(sql);
s1.close();
con.close();

}
  
catch(Exception e) {
e.printStackTrace();
}
  
catch(SQLException e1) {
e1.printStackTrace();
}
  
finally {

try{
if(s1!=null)
s1.close();
}

catch(SQLException se2) {
}

try {
if(con!=null)
con.close();
}

catch(SQLException sqe){
sqe.printStackTrace();
}
}
}
}

----------------------------------------------------------------------------------------------------------------------------------------

This is the code using Java and H2 database

The datatypes which I've used are as follows:

product number: Integer as it'll be easy and I am declaring that as a primary key so it'll be not null and unique

company name, model number, product name: VARCHAR as it might contain alphanumeric values.

Code in which the QUERY is given for the creation of table:

String sql = "CREATE TABLE product " + "(productnumber INTEGER not NULL, " + " companyname VARCHAR(255), " + " modelnumber VARCHAR(255), " + " productname VARCHAR(255), " + " PRIMARY KEY ( productnumber ))";

In the String sql, the query is stored.