troubleshooting Question

database and table

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


I need your assistance how can i ensure that the connectivity between the CreateDatabase class and createTable class
I've added in the folder which are the two the jdbc drivers from QED but nothing seems to work :S

Thanks in advance for any help !
//CreateDatabase class
 
 
import java.io.*;
import java.sql.*;
 
public class CreateDatabase{
  public static void main(String[] args) {
    Connection con = null;
    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);
      try{
        Statement st = con.createStatement();
        BufferedReader bf = new BufferedReader
(new InputStreamReader(System.in));
        System.out.println("Enter Database name:");
        String database = bf.readLine();
        st.executeUpdate("CREATE DATABASE "+database);
        System.out.println("1 row(s) affacted");
      }
      catch (SQLException s){
        System.out.println("SQL statement is not executed!");
      }
    }
    catch (Exception e){
      e.printStackTrace();
    }
  }
}
 
 
 //createTable
 
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
 
public class createTable 
{
	public static void main(String[] args) 
	{
		DB db = new DB();
		Connection conn=db.dbConnect(
				"jdbc:qed:mynewdb://localhost:1925", "p");
		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      int,     " +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";
 
 
 
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('John Doe', '1', '5555999944', 'Something@mail.com', 'Garden Worker')");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Neil Mayhem', '2', '555588888', 'sth@mail.com')");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Alice Lovelace', '3', '6666999944', 'alice@mail.com')");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Jay Something', '4', '666669999', 'mail@mail.com')");
 
			stmt.executeUpdate("INSERT INTO employees " +
			"VALUES ('Terry Something', '5', '622211111', 'mail20@mail.com')");
 
 
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
			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.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 = conn.createStatement();
			stmt.executeUpdate(query);
			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();
 
		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);
 
				}
			}
		}
	}
 
};
qed-3.4.txt
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 10 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 10 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