asked on

run database

Hello Experts,

Can someone run the database code below for me and tell me whether it works ?? because i've tried adding the jdbc drivers in it from the http://www.quadcap.com/ and still nothing

Thanks in advance!

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();
		Connection conn=db.dbConnect(
				"jdbc:qed:mynewdb://localhost:1925", "p");
		db.createTables(conn);
 
 
		try {
			Class.forName("com.quadcap.jdbc.JdbcDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		java.util.Properties p = new java.util.Properties();
		p.setProperty("create", "true");
		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();
		}
 
	}
}
 
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.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 = 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();
 
		//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);
 
				}
			}
		}
	}
 
};

Open in new window

Muhammad Khan

What error are you receiving?

MicheleMarcon

Are you running the database? The application can't work without it.

CEHJ

Don't need to run it to see it won't work - you create the tables before the database is even created

CEHJ

On another matter, why buy an embedded DB when Java already comes with one for free?

perdoname_

ASKER

How can it be as the database to be created first ??

CEHJ

You need to call a method that creates the database before you call the method that creates the tables

perdoname_

ASKER

So in other words change the order like that ??

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 
	{
		try {
			Class.forName("com.quadcap.jdbc.JdbcDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		java.util.Properties p = new java.util.Properties();
		p.setProperty("create", "true");
		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://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      integer,     " +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";
 
 
 
 
			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 = 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();
 
		//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);
 
				}
			}
		}
	}
 
};

Open in new window

CEHJ

Yes, but you can use the same connection. Use the DB() at the outset

perdoname_

ASKER

So what do you mean is something like that or not ??

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();
		}
		java.util.Properties p = new java.util.Properties();
		p.setProperty("create", "true");
		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://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      integer,     " +
			"foreign key (JOB_ID) references Job_roles (JOB_ID))";
 
 
 
 
			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 = 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();
 
		//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);
 
				}
			}
		}
	}
 
};

Open in new window

CEHJ

Why would you connect explicitly when DB already has a method to connect?

perdoname_

ASKER

So do i have to remove that from the DB class or not ??
      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;

Can you please be a bit more precise ?? :)

Thanks

CEHJ

No, you need to remove the code that gets the connection in the other class. Get DB to do it instead

perdoname_

ASKER

Do you mean something like that or not ??

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();
		}
		 */
 
		java.util.Properties p = new java.util.Properties();
		p.setProperty("create", "true");
		Connection conn = null;
		//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.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.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 = 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();
 
		//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);
 
				}
			}
		}
	}
 
};

Open in new window

CEHJ

Replace

>>
java.util.Properties p = new java.util.Properties();
p.setProperty("create", "true");
Connection conn = null;
>>

with

Connection conn = db.dbConnect(username, password);

Open in new window

perdoname_

ASKER

Is it now okay or still not ?
[please have a look to the code]

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 = null;
		String password = null;
		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.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.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 = 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();
 
		//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);
 
				}
			}
		}
	}
 
};

Open in new window

ASKER CERTIFIED SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

perdoname_

ASKER

Is it a necessity to have a password ??
Is it right now ?

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.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.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 = 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();
 
		//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);
 
				}
			}
		}
	}
 
};

Open in new window

CEHJ

Looks reasonable now, have you tested it?

perdoname_

ASKER

Im trying to do so but i get a permission denied :S

set /Users/name/Desktop/dbfolder = %/Users/name/Desktop/dbfolder%,/Users/name/Desktop/qed-3.4/lib/qed.jar;/Users/name/Desktop/qed-3.4/lib/antlr.jar

CEHJ

You probably haven't got the right username and password

perdoname_

ASKER

If its not difficult for you can you please try run it ?
cuz i've tried several times to set the classpath but always unsuccessful

CEHJ

>>If its not difficult for you can you please try run it ?

I can't - i haven't got the software. If you haven't set the classpath properly, how can it run to tell you that the permissions are wrong?

perdoname_

ASKER

It tells me that the permissions are wrong during the set of the drivers
set CLASSPATH = %CLASSPATH%;c:/qed/lib/qed.jar;c:/qed/lib/antlr.jar

The software is just 4.5 mb + cross platform
qed-3.4.txt

Mick Barry

> set CLASSPATH = %CLASSPATH%;c:/qed/lib/qed.jar;c:/qed/lib/antlr.jar

thats wrong
assuing they are in /Users/user/Desktop/qed-3.4/lib you should try something like:

export CLASSPATH=$CLASSPATH:/Users/user/Desktop/qed-3.4/lib/qed.jar :/Users/user/Desktop/qed-3.4/lib/antlr.jar

or copy them to the ext directory of your jdk

SOLUTION

Mick Barry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CEHJ

:-)