troubleshooting Question

java.sql.SQLException: Table/view already exists ???? [URGENT]

Avatar of perdoname_
perdoname_ asked on
Programming Languages-OtherJava
8 Comments1 Solution1054 ViewsLast Modified:
Hello Experts,

I need your assistance about the following errors

java.sql.SQLException: Table/view already exists: JOB_ROLES
      at com.quadcap.sql.AddTable.prepare(AddTable.java:97)
      at com.quadcap.sql.Session.doStep(Session.java:377)
      at com.quadcap.sql.StmtCreateTable.execute(StmtCreateTable.java:98)
      at com.quadcap.sql.Session.doStatement(Session.java:264)
      at com.quadcap.jdbc.Statement.execute(Statement.java:229)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createJobRoles(createTable.java:148)
      at createTable.main(createTable.java:51)
java.sql.SQLException: Table/view already exists: EMPLOYEES
      at com.quadcap.sql.AddTable.prepare(AddTable.java:97)
      at com.quadcap.sql.Session.doStep(Session.java:377)
      at com.quadcap.sql.StmtCreateTable.execute(StmtCreateTable.java:98)
      at com.quadcap.sql.Session.doStatement(Session.java:264)
      at com.quadcap.jdbc.Statement.execute(Statement.java:229)
      at com.quadcap.jdbc.Statement.executeUpdate(Statement.java:347)
      at DB.createTables(createTable.java:101)
      at createTable.main(createTable.java:52)



Thanks in advance for any help!
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
 
public class createTable 
{
	public static void main(String[] args) throws SQLException 
	{
		DB db = new DB();
 
		/*
		try {
			Class.forName("com.quadcap.jdbc.JdbcDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		 */
 
		String username;
		String password;
 
		username = "something";
		password = "123456";
		Connection conn = db.dbConnect(username, password);
		//Connection conn = java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
		try{
			Statement st = conn.createStatement();
			//BufferedReader bf = new BufferedReader
			//(new InputStreamReader(System.in));
			//System.out.println("Enter Database name:");
			//String database = bf.readLine();
			//st.executeUpdate("CREATE DATABASE "+database);
		}
		catch (SQLException s){
			System.out.println("SQL statement is not executed!");
		}
		catch (Exception e){
			e.printStackTrace();
		}
 
		//DB db = new DB();
		//conn=db.dbConnect("jdbc:qed:mynewdb", "p");
		db.createJobRoles(conn);
		db.createTables(conn);
 
	}
}
 
class DB
{
	public DB() {}
 
	public Connection dbConnect(String db_connect_string,
			String db_userid)
	{
		try
		{
			Class.forName("com.quadcap.jdbc.JdbcDriver");
			java.util.Properties p = new java.util.Properties();
			p.setProperty("create", "true");
			java.sql.Connection conn =
				java.sql.DriverManager.getConnection("jdbc:qed:mynewdb", p);
			return conn;
 
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
	} 
 
 
	//Employee table
	public void createTables(Connection conn)
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
 
			query="create table employees " +
			"(name       varchar(32), " +
			"id          varchar(50), " +
			"telephoneNo varchar(50), " +
			"email       varchar(50), " +
			"JOB_ID      integer,     " +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
 
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('John Doe', '1', '5555999944', 'Something@mail.com', 1)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Neil Mayhem', '2', '555588888', 'sth@mail.com', 2)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Alice Lovelace', '3', '6666999944', 'alice@mail.com', 3)");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Jay Something', '4', '666669999', 'mail@mail.com',4 )");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Terry Something', '5', '622211111', 'mail20@mail.com', 1)");
 
 
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	} //createTables
 
 
 
	public void createJobRoles(Connection conn)
	{
 
		String query;
		Statement stmt = null;
 
		try
		{
 
			query="create table Job_roles " +
			"(JOB_ID INTEGER NOT NULL, " +
			"job_title varchar(32), " +
			"salary_class varchar(50), " +
			"job_description varchar(50), " +
			"primary key(JOB_ID))";
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (1, 'Garden Worker', '4', 'Cultivates and cares for plants')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (2, 'Security Guard', '4', 'Takes care of security')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (3, 'Stage Hypnotist', '2', 'Hypnotizes people on stage')");
 
			stmt.executeUpdate("INSERT INTO Job_roles " +
			"VALUES (4, 'Amusement Park Guide', '3', 'Guides people through an amusement park')");
 
 
			stmt.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
 
 
	public void printFields()
	{
		String query;
		Statement stmt;
		try {
			query = "SELECT employees.name, Job_roles.salary_class FROM employees INNER JOIN Job_roles on employees.JOB_ID = Job_roles.JOB_ID";
 
			Connection conn = null;
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				//print query
				System.out.printf("Salary of %s is %s\n", rs.getString(1), rs.getObject(2).toString());
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
 
	}
 
 
	public void addColumn()
	{
		String query;
		Statement stmt;
		Connection conn = null;
 
 
		try {
			query= "ALTER TABLE employees ADD JOB_ID";
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
 
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
 
 
 
	public void EmployeeNameSearch(String[] args) throws SQLException
	{
		List names =  Arrays.asList(args);
 
		List not = new ArrayList();
 
		//Collections.sort(names); 
 
		PreparedStatement ps = null;
 
		BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
		while (true) {
			try {
				String option = stdin.readLine();
			} catch (IOException e1) {
				e1.printStackTrace();
			}
 
			for (Object name : names) {
				try {
					ps.setString(1, name.toString());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				ResultSet rs = null;
				try {
					rs = ps.executeQuery();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				if (rs.next()) {
					// name in dba
				}
				else
				{
					System.out.println(name + "not in db!");
					not.add(name);
 
				}
			}
		}
	}
 
};
ASKER CERTIFIED SOLUTION
Mick Barry
Java Developer
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros