Create a project called \"Problem3\" in the workspace described above and use it

Question

Create a project called "Problem3" in the workspace described above and use it for all the code for this problem. Create a Java file called Database creator that creates a Derby database called Problem3DB with one table called customer. The customer table should have a column for each of the fields shown in Problem 2 in the customer class. The primary key for the table should be the customer ID. Create a second Java file called Db writer. In this file, write code that will populate the customer table with 10 records. You can make up the data values that you use to populate the table.

Explanation / Answer

Make sure you have the derby.jar (database jar file) in your class path or the prigram won't work.

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class DatabaseCreator {

/*

* This is jdbc driver class for apache derby db

*/

private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";

/*

* 'create=true' will create the database

* All database has a unique jdbc driver

*/

private static final String URL = "jdbc:derby:Problem3DB;create=true";

public static void createDatabase() throws SQLException, ClassNotFoundException{

Connection conn = getConnection();

Statement statement = conn.createStatement();

String createTableQuery = "CREATE TABLE CUSTOMER (CUSTOMER_ID INTEGER PRIMARY KEY, NAME VARCHAR(40), ADDRESS VARCHAR(100))";

statement.execute(createTableQuery);

System.out.println("Database and table created successfully");

}

public static Connection getConnection()throws ClassNotFoundException, SQLException{

Class.forName(DRIVER); // this statement loads and registers the driver

Connection conn = DriverManager.getConnection(URL);

return conn;

}

}

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Statement;

public class DbWriter {

public static void populateData() throws ClassNotFoundException, SQLException{

Connection conn = DatabaseCreator.getConnection();

Statement st = conn.createStatement();

String query1 = "INSERT INTO CUSTOMER VALUES(1,'BOB','1ST STREET')";

String query2 = "INSERT INTO CUSTOMER VALUES(2,'JOHN','2ND STREET')";

String query3 = "INSERT INTO CUSTOMER VALUES(3,'RICK','3RD STREET')";

String query4 = "INSERT INTO CUSTOMER VALUES(4,'WILL','4TH STREET')";

String query5 = "INSERT INTO CUSTOMER VALUES(5,'DAVID','5TH STREET')";

String query6 = "INSERT INTO CUSTOMER VALUES(6,'PAUL','6TH STREET')";

String query7 = "INSERT INTO CUSTOMER VALUES(7,'JIMMY','7TH STREET')";

String query8 = "INSERT INTO CUSTOMER VALUES(8,'JACK','8TH STREET')";

String query9 = "INSERT INTO CUSTOMER VALUES(9,'JOE','9TH STREET')";

String query10 = "INSERT INTO CUSTOMER VALUES(10,'JULIAN','1ST STREET')";

st.executeUpdate(query1);

st.executeUpdate(query2);

st.executeUpdate(query3);

st.executeUpdate(query4);

st.executeUpdate(query5);

st.executeUpdate(query6);

st.executeUpdate(query7);

st.executeUpdate(query8);

st.executeUpdate(query9);

st.executeUpdate(query10);

}

}

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/*

* Make sure that the table and database creation will work only once.

* because you can not create table/Database twice.

*/

public class Problem13 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

DatabaseCreator.createDatabase();

DbWriter.populateData();

Connection conn = DatabaseCreator.getConnection();

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery("select * from customer");

while(rs.next()){

int id = rs.getInt("customer_Id");

String name = rs.getString("name");

String add = rs.getString("address");

System.out.println(id+" "+name+" "+add);

}

}

}

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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