Question

JAVA sql

Code an sql file that creates a 'Contractors' table. See the exercise below for an example. Give your 'Contractors' table the following fields Id (integer, primary key) CompanyName (varchar, 30 characters) Phone(varchar, 12 characters) ContactName(varchar, 30 characters) Rating (integer) OutOfStateService (boolean) Also have the sql file create at least five records for the "Contractors" table. Example record: 1, Tom's Contracting, 555-555-5555, Tom Thumb, 81, true

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

public class TestDB {

    public static void main(String[] args) {
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            String myDb = "jdbc:derby://localhost:1527/Friends";
            Connection dbCon = DriverManager.getConnection(myDb, "nbuser", "nbuser");
            Statement statement = dbCon.createStatement();
            ResultSet result = statement.executeQuery("SELECT * from Friends");
           
            while(result.next()){
                System.out.print(result.getString(1));
                System.out.print(", ");               
                System.out.print(result.getString(2));
                System.out.print(" ");                  
                System.out.print(result.getString(3));
                System.out.print(", ");                   
                System.out.println(result.getString(4));
                System.out.print(", ");   
                System.out.println(result.getString(5));               
            }
           
            dbCon.close();
        }
        catch(ClassNotFoundException | SQLException exc){
            System.err.println(exc);           
        }
    }
}

Answer 1

Note : Change the Oracle JDBC_DRIVER class and DB_RL and USER name and Password based on the Database which you have installed in your system.Then only this Program will run.Bec different persons may use different database like mysql,postgresql,oracle DB...etc...

________________________

// ContractorJDBC.java

//STEP 1. Import required packages
import java.sql.*;

public class ContractorJDBC {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";

// Database credentials
static final String USER = "system";
static final String PASS = "manager";

public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName(JDBC_DRIVER);

//STEP 3: Open a connection
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
  
//STEP 4: Execute a query
System.out.println("Creating table in given database...");
stmt = conn.createStatement();
  
String sql = "CREATE TABLE CONTRACTORS " +
"(Id INTEGER PRIMARY KEY, " +
" COMPANYNAME VARCHAR(30), " +
" PHONE VARCHAR(12), " +
" CONTACTNAME VARCHAR(30), " +
" RATING INTEGER,"+
"OUTOFSTATESERVICE BOOLEAN";

stmt.executeUpdate(sql);
  
sql = "INSERT INTO CONTRACTORS " +
"VALUES (1, 'Tom's Contracting',555-555-5555,'Tom Thumb',81,true)";
stmt.executeUpdate(sql);
  
sql = "INSERT INTO CONTRACTORS " +
"VALUES (2, 'Kanes's Contracting',123-223-2222,'Bon',82,false)";
stmt.executeUpdate(sql);
  
sql = "INSERT INTO CONTRACTORS " +
"VALUES (3, 'Ken's Contracting',222-234-4345,'Bill',83,false)";
stmt.executeUpdate(sql);
  
sql = "INSERT INTO CONTRACTORS " +
"VALUES (4, 'Thomson's Contracting',456-445-6776,'Ben',84,true)";
stmt.executeUpdate(sql);   
  
System.out.println("Created table in given database...");
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}//end

________________________