public static void duration() {
connectDb();
String sql = "select min(time), max(time), count(*) from " + tablename + ";";
try {
ResultSet rs = query.executeQuery(sql);
rs.next();
int numCols = rs.getMetaData().getColumnCount();
String minTime = rs.getString(1);
String maxTime = rs.getString(2);
String numPackets = rs.getString(3);
System.out.println(
"Experiment "
+ tablename
+ "\n\tfrom: "
+ minTime
+ "\n\tto: "
+ maxTime
+ "\n\tpackets: "
+ numPackets);
} catch (SQLException e) {
System.out.println("SQL Exception: " + e);
System.exit(1);
}
}
public static String getVersion(Connection conn) throws SQLException {
String method = "getVersion";
int location = 1000;
try {
location = 2000;
String value = "";
location = 2100;
Statement stmt = conn.createStatement();
String strSQL =
"SELECT CASE "
+ "WHEN POSITION ('HAWQ 2.0.1' in version) > 0 THEN 'HAWQ_2_0_1' "
+ "WHEN POSITION ('HAWQ 2.0.0' in version) > 0 THEN 'HAWQ_2_0_0' "
+ "WHEN POSITION ('HAWQ 1' in version) > 0 THEN 'HAWQ_1' "
+ "WHEN POSITION ('HAWQ' in version) = 0 AND POSITION ('Greenplum Database' IN version) > 0 THEN 'GPDB' "
+ "ELSE 'OTHER' END "
+ "FROM version()";
if (debug) Logger.printMsg("Getting Variable: " + strSQL);
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
value = rs.getString(1);
}
location = 2300;
return value;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static String getVariable(Connection conn, String name) throws SQLException {
String method = "getVariable";
int location = 1000;
try {
location = 2000;
String value = "";
location = 2100;
Statement stmt = conn.createStatement();
String strSQL = "SELECT os.fn_get_variable('" + name + "')";
if (debug) Logger.printMsg("Getting Variable: " + strSQL);
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
value = rs.getString(1);
}
location = 2300;
return value;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void customStartAll(Connection conn) throws SQLException {
String method = "customStartAll";
int location = 1000;
try {
Statement stmt = conn.createStatement();
int id = 0;
int gpfdistPort = 0;
String strSQL = "SELECT id\n";
strSQL += "FROM os.custom_sql";
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
id = rs.getInt(1);
gpfdistPort = GpfdistRunner.customStart(OSProperties.osHome);
strSQL = "INSERT INTO os.ao_custom_sql\n";
strSQL +=
"(id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, gpfdist_port)\n";
strSQL +=
"SELECT id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, "
+ gpfdistPort
+ "\n";
strSQL += "FROM os.custom_sql\n";
strSQL += "WHERE id = " + id;
stmt.executeUpdate(strSQL);
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
/* to search the DB table for req. video*/
boolean search(String file) {
boolean found = false;
Connection con = null;
String url =
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=D://db.mdb; DriverID=22;READONLY=true;";
try {
con = DriverManager.getConnection(url, "", "");
try {
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT video FROM p3");
while (rs.next()) {
String colvalue = rs.getString("video");
if (colvalue.equalsIgnoreCase(file)) found = true;
}
} catch (SQLException s) {
System.out.println("SQL statement is not executed!");
}
} catch (Exception e) {
System.out.println(e);
}
return found;
}
public static void cancelJobs(Connection conn) throws SQLException {
String method = "cancelJobs";
int location = 1000;
try {
List<String> jobIdList = new ArrayList<String>();
Statement stmt = conn.createStatement();
String strSQL = "SELECT id FROM os.queue WHERE status = 'processing'";
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
jobIdList.add(rs.getString(1));
}
for (String jobId : jobIdList) {
strSQL = "SELECT os.fn_cancel_job(" + jobId + ")";
rs = stmt.executeQuery(strSQL);
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void emailAlert(Connection conn, String errorMsg) throws SQLException {
String method = "emailAlert";
int location = 1000;
try {
Statement stmt = conn.createStatement();
String strSQL = "SELECT gp_elog('" + errorMsg + "',true)";
ResultSet rs = stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static boolean createSchema(Connection conn, String schema) throws SQLException {
String method = "createSchema";
int location = 1000;
try {
location = 2000;
boolean found = false;
String strSQL =
"SELECT COUNT(*) \n"
+ "FROM INFORMATION_SCHEMA.SCHEMATA \n"
+ "WHERE SCHEMA_NAME = '"
+ schema
+ "'";
location = 2100;
Statement stmt = conn.createStatement();
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2306;
while (rs.next()) {
if (rs.getInt(1) > 0) found = true;
}
location = 2400;
if (!(found)) {
location = 2500;
String schemaDDL = "CREATE SCHEMA \"" + schema + "\"";
;
if (debug) Logger.printMsg("Schema DDL: " + schemaDDL);
location = 2600;
stmt.executeUpdate(schemaDDL);
}
location = 2700;
return found;
} catch (SQLException ex) {
return true;
}
}
public static boolean checkStageTable(Connection conn, String targetSchema, String targetTable)
throws SQLException {
String method = "checkStageTable";
int location = 1000;
try {
location = 2000;
boolean found = false;
String stageTable = getStageTableName(targetSchema, targetTable);
location = 2100;
String strSQL =
"SELECT NULL \n"
+ "FROM INFORMATION_SCHEMA.TABLES \n"
+ "WHERE table_schema = '"
+ externalSchema
+ "' \n"
+ " AND table_name = '"
+ stageTable
+ "'";
if (debug) Logger.printMsg("Executing sql: " + strSQL);
location = 2200;
Statement stmt = conn.createStatement();
location = 2310;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2400;
while (rs.next()) {
found = true;
}
location = 2500;
return found;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static String getMax(Connection conn, String schema, String table, String columnName)
throws SQLException {
String method = "getMax";
int location = 1000;
try {
location = 2000;
String strSQL =
"SELECT MAX(\""
+ columnName.toLowerCase()
+ "\") \n"
+ "FROM \""
+ schema
+ "\".\""
+ table
+ "\"";
if (debug) Logger.printMsg("Executing sql: " + strSQL);
String max = "-1";
location = 2100;
Statement stmt = conn.createStatement();
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
max = rs.getString(1);
}
location = 2313;
if (max == null) {
max = "-1";
}
return max;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void setupReplicationTables(
Connection conn, String targetSchema, String targetTable, String columnName)
throws SQLException {
String method = "setupReplicationTables";
int location = 1000;
try {
location = 2000;
String archTable = getArchTableName(targetSchema, targetTable);
location = 2100;
String stageTable = getStageTableName(targetSchema, targetTable);
location = 2200;
String strSQL =
"SELECT os.fn_replication_setup('"
+ targetSchema
+ "', '"
+ targetTable
+ "', '"
+ externalSchema
+ "', '"
+ stageTable
+ "', '"
+ archTable
+ "', '"
+ columnName
+ "')";
location = 2312;
Statement stmt = conn.createStatement();
location = 2400;
stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public void login() {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String id, pw, sql;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, "hr", "hr");
stmt = con.createStatement();
sql = "select * from chatuser where id = '" + lid.getText() + "'";
rs = stmt.executeQuery(sql);
if (rs.next()) {
pw = rs.getString(2);
nick = rs.getString(3);
if (lpw.getText().equals(pw)) {
jd.setVisible(false);
setVisible(true);
} else {
lpw.setText("일치하지 않습니다.");
}
} else {
lid.setText("일치하지 않습니다.");
}
} catch (Exception e1) {
e1.printStackTrace();
System.out.println("데이터 베이스 연결 실패!!");
} finally {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (con != null) con.close();
} catch (Exception e1) {
System.out.println(e1.getMessage());
}
}
}
public static void executeReplication(
Connection conn, String targetSchema, String targetTable, String appendColumnName)
throws SQLException {
String method = "executeReplication";
int location = 1000;
try {
location = 2000;
Statement stmt = conn.createStatement();
location = 2100;
String externalTable = getExternalTableName(targetSchema, targetTable);
location = 2200;
String stageTable = getStageTableName(targetSchema, targetTable);
location = 2301;
String strSQL =
"SELECT os.fn_replication('"
+ targetSchema
+ "', '"
+ targetTable
+ "', '"
+ externalSchema
+ "', '"
+ stageTable
+ "', '"
+ appendColumnName
+ "');";
if (debug) Logger.printMsg("Executing function: " + strSQL);
location = 2400;
stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
private static void executeExt(Connection gpConn, int connectionId, String selectSQL)
throws Exception {
String method = "executeExt";
int location = 1000;
String sourceType = "";
String sourceServer = "";
String sourceInstance = "";
int sourcePort = 0;
String sourceDatabase = "";
String sourceUser = "";
String sourcePass = "";
String strSQL = "";
Connection conn = null;
int fetchSize = 10;
try {
location = 3000;
ResultSet rs;
Statement stmt;
location = 3010;
strSQL = GP.getExtConnectionDetails(gpConn, connectionId);
location = 3020;
stmt = gpConn.createStatement();
location = 3030;
rs = stmt.executeQuery(strSQL);
while (rs.next()) {
sourceType = rs.getString(1);
sourceServer = rs.getString(2);
sourceInstance = rs.getString(3);
sourcePort = rs.getInt(4);
sourceDatabase = rs.getString(5);
sourceUser = rs.getString(6);
sourcePass = rs.getString(7);
}
location = 3090;
if (sourceType.equals("sqlserver")) {
location = 3100;
conn = CommonDB.connectSQLServer(sourceServer, sourceInstance, sourceUser, sourcePass);
location = 3300;
// execute the SQL Statement
CommonDB.outputData(conn, selectSQL);
location = 3400;
conn.close();
} else if (sourceType.equals("oracle")) {
location = 4000;
fetchSize = Integer.parseInt(GP.getVariable(gpConn, "oFetchSize"));
location = 4100;
conn =
CommonDB.connectOracle(
sourceServer, sourceDatabase, sourcePort, sourceUser, sourcePass, fetchSize);
location = 4200;
// execute the SQL Statement
CommonDB.outputData(conn, selectSQL);
location = 4400;
conn.close();
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} finally {
if (conn != null) conn.close();
}
}
public static void createExternalTable(
Connection conn,
String osServer,
String refreshType,
String sourceTable,
String targetSchema,
String targetTable,
String maxId,
int queueId,
int jobPort)
throws SQLException {
String method = "createExternalTable";
int location = 1000;
try {
location = 2000;
String externalTable = getExternalTableName(targetSchema, targetTable);
location = 2100;
Statement stmt = conn.createStatement();
String createSQL =
"CREATE EXTERNAL TABLE \"" + externalSchema + "\".\"" + externalTable + "\" \n (";
location = 2309;
String strSQL =
"SELECT c.column_name, \n"
+ " CASE WHEN c.data_type = 'character' THEN c.data_type || '(' || c.character_maximum_length || ')' ELSE c.data_type END AS data_type \n"
+ "FROM INFORMATION_SCHEMA.COLUMNS c \n"
+ "WHERE table_schema = '"
+ targetSchema
+ "' \n"
+ " AND table_name = '"
+ targetTable
+ "' \n"
+ "ORDER BY ordinal_position";
location = 2400;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2500;
while (rs.next()) {
location = 2600;
if (rs.getRow() == 1) {
location = 2700;
createSQL = createSQL + "\"" + rs.getString(1) + "\" " + rs.getString(2);
} else {
location = 2800;
createSQL = createSQL + ", \n \"" + rs.getString(1) + "\" " + rs.getString(2);
}
}
location = 2900;
createSQL = createSQL + ") \n";
////////////////////////////////////////////
// Create location for External Table
////////////////////////////////////////////
location = 3000;
// replace space in the maxId because this could now be a date
maxId = maxId.replace(" ", "SPACE");
location = 3100;
String extLocation =
"LOCATION ('gpfdist://"
+ osServer
+ ":"
+ jobPort
+ "/config.properties+"
+ queueId
+ "+"
+ maxId
+ "+"
+ refreshType
+ "+"
+ sourceTable
+ "#transform=externaldata"
+ "')";
location = 3400;
extLocation = extLocation + "\n" + "FORMAT 'TEXT' (delimiter '|' null 'null' escape '\\\\')";
////////////////////////////////////////////
// Add createSQL with Java Command to exec.
////////////////////////////////////////////
location = 3500;
createSQL = createSQL + extLocation;
////////////////////////////////////////////
// Create new external web table
////////////////////////////////////////////
location = 4000;
if (debug) Logger.printMsg("Creating External Table: " + createSQL);
stmt.executeUpdate(createSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} catch (Exception e) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + e.getMessage() + ")");
}
}
public static boolean createTargetTable(
Connection conn,
String targetSchema,
String targetTable,
boolean targetAppendOnly,
boolean targetCompressed,
boolean targetRowOrientation,
String sourceType,
String sourceServer,
String sourceInstance,
int sourcePort,
String sourceDatabase,
String sourceSchema,
String sourceTable,
String sourceUser,
String sourcePass)
throws Exception {
String method = "createTargetTable";
int location = 1000;
try {
location = 2000;
boolean found = false;
String strSQL =
"SELECT COUNT(*) \n"
+ "FROM INFORMATION_SCHEMA.TABLES \n"
+ "WHERE TABLE_SCHEMA = '"
+ targetSchema
+ "' \n"
+ " AND TABLE_NAME = '"
+ targetTable
+ "'";
location = 2100;
Statement stmt = conn.createStatement();
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2307;
while (rs.next()) {
if (rs.getInt(1) > 0) found = true;
}
location = 2400;
if (!(found)) {
String tableDDL =
CommonDB.getGPTableDDL(
sourceType,
sourceServer,
sourceInstance,
sourcePort,
sourceDatabase,
sourceSchema,
sourceTable,
sourceUser,
sourcePass,
targetSchema,
targetTable,
targetAppendOnly,
targetCompressed,
targetRowOrientation);
if (debug) Logger.printMsg("Table DDL: " + tableDDL);
location = 2800;
stmt.executeUpdate(tableDDL);
}
location = 3000;
return found;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
private static void executeOS(
String sourceType,
String sourceServer,
String sourceInstance,
int sourcePort,
String sourceDatabase,
String sourceSchema,
String sourceTable,
String refreshType,
String appendColumnName,
int appendColumnMax,
Connection gpConn,
int queueId)
throws Exception {
String method = "executeOS";
int location = 1000;
String sourceUser = "";
String sourcePass = "";
String strSQL = "";
int fetchSize = 10;
Connection conn = null;
try {
location = 3000;
ResultSet rs;
Statement stmt;
location = 3010;
strSQL = GP.getQueueDetails(gpConn, queueId);
location = 3020;
stmt = gpConn.createStatement();
location = 3040;
rs = stmt.executeQuery(strSQL);
while (rs.next()) {
sourceUser = rs.getString(1);
sourcePass = rs.getString(2);
}
location = 3090;
if (sourceType.equals("sqlserver")) {
location = 3100;
conn = CommonDB.connectSQLServer(sourceServer, sourceInstance, sourceUser, sourcePass);
location = 3200;
// create SQL statement for selecting data
strSQL =
SQLServer.getSQLForData(
conn,
sourceDatabase,
sourceSchema,
sourceTable,
refreshType,
appendColumnName,
appendColumnMax);
location = 3300;
// execute the SQL Statement
CommonDB.outputData(conn, strSQL);
location = 3400;
conn.close();
} else if (sourceType.equals("oracle")) {
location = 4000;
fetchSize = Integer.parseInt(GP.getVariable(gpConn, "oFetchSize"));
location = 4100;
conn =
CommonDB.connectOracle(
sourceServer, sourceDatabase, sourcePort, sourceUser, sourcePass, fetchSize);
location = 4200;
// execute the SQL Statement
strSQL =
Oracle.getSQLForData(
conn, sourceSchema, sourceTable, refreshType, appendColumnName, appendColumnMax);
location = 4300;
// execute the SQL Statement
CommonDB.outputData(conn, strSQL);
location = 4400;
conn.close();
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} finally {
if (conn != null) conn.close();
}
}
public QueryDBFrame() {
setTitle("QueryDB");
setSize(400, 300);
addWindowListener(
new WindowAdapter() {
public void windowClosing(WindowEvent e) {
System.exit(0);
}
});
getContentPane().setLayout(new GridBagLayout());
GridBagConstraints gbc = new GridBagConstraints();
authors = new JComboBox();
authors.setEditable(false);
authors.addItem("Any");
publishers = new JComboBox();
publishers.setEditable(false);
publishers.addItem("Any");
result = new JTextArea(4, 50);
result.setEditable(false);
priceChange = new JTextField(8);
priceChange.setText("-5.00");
try {
// 连接数据库
con = getConnection();
stmt = con.createStatement();
// 将数据库中的作者名添加到组合框
String query = "SELECT Name FROM Authors";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) authors.addItem(rs.getString(1));
// 将出版社名添加到组合框
query = "SELECT Name FROM Publishers";
rs = stmt.executeQuery(query);
while (rs.next()) publishers.addItem(rs.getString(1));
} catch (Exception e) {
result.setText("Error " + e);
}
gbc.fill = GridBagConstraints.NONE;
gbc.weightx = 100;
gbc.weighty = 100;
add(authors, gbc, 0, 0, 2, 1);
add(publishers, gbc, 2, 0, 2, 1);
gbc.fill = GridBagConstraints.NONE;
JButton queryButton = new JButton("Query");
queryButton.addActionListener(this);
add(queryButton, gbc, 0, 1, 1, 1);
JButton changeButton = new JButton("Change prices");
changeButton.addActionListener(this);
add(changeButton, gbc, 2, 1, 1, 1);
gbc.fill = GridBagConstraints.HORIZONTAL;
add(priceChange, gbc, 3, 1, 1, 1);
gbc.fill = GridBagConstraints.BOTH;
add(result, gbc, 0, 2, 4, 1);
}
public static void updateStatus(
Connection conn,
int queueId,
String status,
Timestamp queueDate,
Timestamp startDate,
String errorMessage,
int numRows,
int id,
String refreshType,
String targetSchema,
String targetTable,
boolean targetAppendOnly,
boolean targetCompressed,
boolean targetRowOrientation,
String sourceType,
String sourceServer,
String sourceInstance,
int sourcePort,
String sourceDatabase,
String sourceSchema,
String sourceTable,
String sourceUser,
String sourcePass,
String columnName,
String sqlText,
boolean snapshot)
throws SQLException {
String method = "updateStatus";
int location = 1000;
String strQueueId = setSQLString(queueId);
status = setSQLString(status);
String strQueueDate = setSQLString(queueDate);
String strStartDate = setSQLString(startDate);
errorMessage = setSQLString(errorMessage);
String strNumRows = setSQLString(numRows);
String strId = setSQLString(id);
refreshType = setSQLString(refreshType);
targetSchema = setSQLString(targetSchema);
targetTable = setSQLString(targetTable);
String strTargetAppendOnly = setSQLString(targetAppendOnly);
String strTargetCompressed = setSQLString(targetCompressed);
String strTargetRowOrientation = setSQLString(targetRowOrientation);
sourceType = setSQLString(sourceType);
sourceServer = setSQLString(sourceServer);
sourceInstance = setSQLString(sourceInstance);
String strSourcePort = setSQLString(sourcePort);
sourceDatabase = setSQLString(sourceDatabase);
sourceSchema = setSQLString(sourceSchema);
sourceTable = setSQLString(sourceTable);
sourceUser = setSQLString(sourceUser);
sourcePass = setSQLString(sourcePass);
columnName = setSQLString(columnName);
sqlText = setSQLString(sqlText);
String strSnapshot = setSQLString(snapshot);
try {
location = 2000;
Statement stmt = conn.createStatement();
location = 2400;
String strSQL =
"SELECT os.fn_update_status("
+ strQueueId
+ ", "
+ status
+ ", "
+ strQueueDate
+ ", "
+ strStartDate
+ ", ";
strSQL +=
errorMessage
+ ", "
+ strNumRows
+ ", "
+ strId
+ ", "
+ refreshType
+ ", "
+ targetSchema
+ ", "
+ targetTable
+ ", ";
strSQL +=
strTargetAppendOnly
+ ", "
+ strTargetCompressed
+ ", "
+ strTargetRowOrientation
+ ", "
+ sourceType
+ ", ";
strSQL +=
sourceServer
+ ", "
+ sourceInstance
+ ", "
+ strSourcePort
+ ", "
+ sourceDatabase
+ ", "
+ sourceSchema
+ ", ";
strSQL +=
sourceTable
+ ", "
+ sourceUser
+ ", "
+ sourcePass
+ ", "
+ columnName
+ ", "
+ sqlText
+ ", "
+ strSnapshot
+ ")";
if (debug) Logger.printMsg("Updating Status: " + strSQL);
location = 2500;
stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public void run() {
ObjectInputStream din = null;
try {
din = new ObjectInputStream(socket.getInputStream());
Packet packet = (Packet) din.readObject();
username = packet.getSender();
server.usernameSocketMapping.put(username, socket);
server.isAlive.add(username);
// Sh: Login validation message
if (packet.getType() == 0) {
try {
String password = packet.getMessage();
Statement st = Server.conn.createStatement();
ResultSet rs =
st.executeQuery(
"SELECT * FROM user_info where username='******' and password='******'");
if (!rs.next()) { // User does not exist
server.forwardToReceiver(new Packet(0, "Server", username, "Invalid"));
synchronized (server.usernameSocketMapping) {
server.usernameSocketMapping.remove(username);
}
synchronized (server.isAlive) {
server.isAlive.remove(username);
}
return;
} else { // Authentic user
server.forwardToReceiver(new Packet(0, "Server", username, "Authentic"));
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
// Sh: Signup message
if (packet.getType() == 2) {
try {
String[] message = packet.getMessage().split(":");
String name = message[0];
String password = message[1];
// String sex=message[2];
String username = message[2];
Statement st = Server.conn.createStatement();
ResultSet rs =
st.executeQuery("SELECT * FROM user_info where username='******'");
if (rs.next()) { // Username already present
server.forwardToReceiver(new Packet(2, "Server", username, "Username present"));
synchronized (server.usernameSocketMapping) {
server.usernameSocketMapping.remove(username);
}
synchronized (server.isAlive) {
server.isAlive.remove(username);
}
return;
} else { // Register user
String query =
"INSERT INTO user_info VALUES('"
+ name
+ "','"
+ password
+ "',"
+ "NULL,'"
+ username
+ "')";
System.out.println(query);
st.executeUpdate(query);
server.forwardToReceiver(new Packet(2, "Server", username, "User Registererd"));
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
System.out.println("State of isAlive map is: " + server.isAlive);
try {
Statement st = Server.conn.createStatement();
ResultSet rs =
st.executeQuery(
"SELECT sender,message,status FROM msg_buffer where receiver='"
+ username
+ "' order by sender,sno");
String sendflag = null;
String sender = null;
while (rs.next()) {
sender = rs.getString("sender");
int status = rs.getInt("status");
String message = rs.getString("message");
System.out.println("sender: " + sender);
System.out.println("receiver: " + username);
System.out.println("message: " + message);
server.forwardToReceiver(new Packet(status, sender, username, message));
System.out.println("Sending " + packet);
if (!sender.equals(sendflag)) {
server.forwardToReceiver(new Packet(6, username, sender, "Message Received"));
sendflag = sender;
}
}
st.executeUpdate("DELETE FROM msg_buffer where receiver='" + username + "'");
} catch (SQLException e1) {
e1.printStackTrace();
}
while (true) {
packet = (Packet) din.readObject();
if (packet.getType() == 3) // is an OK message
{
try {
packet.setSender(username);
System.out.println("Is OK message");
Statement st = Server.conn.createStatement();
ResultSet rs =
st.executeQuery(
"SELECT * FROM user_info where username='******'");
if (rs.next()) {
if (server.addFriend(packet.getSender(), packet.getReceiver(), true) == 1) {
// packet.setMessage("Already in friend list");
server.forwardToReceiver(
new Packet(1, "Server", packet.getSender(), "Already in friend list"));
System.out.println("ALready in list");
} else {
System.out.println("Sending " + packet);
server.forwardToReceiver(
new Packet(1, "Server", packet.getReceiver(), packet.getMessage()));
server.forwardToReceiver(
new Packet(1, "Server", packet.getSender(), packet.getMessage()));
}
} else {
server.forwardToReceiver(
new Packet(1, "Server", packet.getSender(), "User does not exist"));
}
} catch (SQLException e1) {
e1.printStackTrace();
}
} else if ((packet.getType() == 8)) // is a getFriends message
{
packet.setSender(username);
System.out.println("Type 8");
/*
* try { //Thread.sleep(3000); } catch (InterruptedException
* e) { // TODO Auto-generated catch block
* e.printStackTrace(); }
*/
String FriendsMessage = getFriendsMessage(packet);
server.forwardToReceiver(
new Packet(8, "Server", packet.getSender(), "Friends|" + FriendsMessage));
} else if (packet.getType() == 16) // Random conversation.
{
packet.setSender(username);
String receiver = server.getRandomPerson(packet.getSender());
if (!receiver.equals("No One Online")) {
server.forwardToReceiver(
new Packet(1, "Server", packet.getSender(), receiver + "| will talk to you now."));
server.forwardToReceiver(
new Packet(1, "Server", receiver, packet.getSender() + "| will talk to you now."));
} else {
System.out.println("No one online");
server.forwardToReceiver(new Packet(16, "Server", packet.getSender(), "No One Online"));
server.forwardToReceiver(new Packet(1, "Server", packet.getSender(), "No One Online"));
}
} else if (packet.getType() == 13) { // Webcam of live is started
synchronized (server.webcamAliveMapping) {
server.webcamAliveMapping.put(packet.getSender(), packet.getMessage());
}
System.out.println("State of webcamAliveMapping is: " + server.webcamAliveMapping);
} else if (packet.getType() == 14) { // Webcam of client is
// closed
if (server.webcamAliveMapping.containsKey(packet.getSender())) {
synchronized (server.webcamAliveMapping) {
server.webcamAliveMapping.remove(packet.getSender());
}
}
} else if (packet.getType() == 15) { // Client requested for
// another client's webcam
if (server.webcamAliveMapping.containsKey(
packet.getReceiver())) { // User Webcam is available
server.forwardToReceiver(
new Packet(
15,
"Server",
packet.getSender(),
server.webcamAliveMapping.get(packet.getReceiver())));
} else { // User Webcam not available
server.forwardToReceiver(
new Packet(15, "Server", packet.getSender(), "Webcam Not Found"));
server.forwardToReceiver(
new Packet(
1,
"Server",
packet.getSender(),
"Webcam for " + packet.getReceiver() + " is not available"));
}
} else if (packet.getType() == 17) {
ArrayList<String> friends = new ArrayList<String>();
friends = server.getfriendlist(packet.getSender());
if (friends.isEmpty()) {
server.forwardToReceiver(
new Packet(1, "Server", packet.getSender(), "Your friendlist is empty."));
} else {
for (int i = 0; i < friends.size(); i++) {
server.forwardToReceiver(
new Packet(1, packet.getSender(), friends.get(i), packet.getMessage()));
}
}
} else {
packet.setSender(username);
System.out.println("Sending " + packet);
server.forwardToReceiver(packet);
}
}
} catch (EOFException ie) {
} catch (IOException ie) {
ie.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
din.close();
} catch (IOException e) {
e.printStackTrace();
}
synchronized (server.isAlive) {
server.isAlive.remove(username);
}
synchronized (server.usernameSocketMapping) {
server.usernameSocketMapping.remove(username);
}
System.out.println("State of isAlive map is: " + server.isAlive);
server.removeConnection(socket);
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
HttpSession session = request.getSession(true);
try {
Object accountObject = session.getValue(ACCOUNT);
// If no account object was put in the session, or
// if one exists but it is not a hashtable, then
// redirect the user to the original login page
if (accountObject == null)
throw new RuntimeException("You need to log in to use this service!");
if (!(accountObject instanceof Hashtable))
throw new RuntimeException("You need to log in to use this service!");
Hashtable account = (Hashtable) accountObject;
String userName = (String) account.get("name");
//////////////////////////////////////////////
// Display Messages for the user who logged in
//////////////////////////////////////////////
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String lookupID = request.getParameter("LookupMemberID");
out.println("<HTML>");
out.println("<HEAD>");
out.println("<TITLE>Searching for member: lookupID</TITLE>");
out.println("</HEAD>");
out.println("<BODY BGCOLOR='#EFEFEF'>");
out.println("<H3><u>Searching for Member ID: " + lookupID + "</u></H3>");
out.println("<CENTER>");
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con =
DriverManager.getConnection(
"jdbc:mysql://localhost/contacts?user=kareena&password=kapoor");
stmt = con.createStatement();
rs =
stmt.executeQuery(
"SELECT * FROM userstable WHERE UserID=" + Integer.parseInt(lookupID));
out.println("<TABLE BGCOLOR='#EFEFFF' CELLPADDING='2' CELLSPACING='4' BORDER='1'>");
out.println("<TR BGCOLOR='#D6DFFF'>");
out.println("<TD ALIGN='center'><B>Picture</B></TD>");
out.println("<TD ALIGN='center'><B>User Name</B></TD>");
out.println("<TD ALIGN='center'><B>Gender</B></TD>");
out.println("<TD ALIGN='center'><B>City / State</B></TD>");
out.println("<TD ALIGN='center'><B>Country</B></TD>");
out.println("<TD ALIGN='center'><B>About User</B></TD>");
out.println("<TD ALIGN='center'><B>User Profile</B></TD>");
out.println("<TD ALIGN='center'><B>Add to Contact List</B></TD>");
out.println("</TR>");
int i = 0;
String formName = "form";
String buttonName = "button";
while (rs.next()) {
String picture = rs.getString("FileLocation");
String user = rs.getString("UserName");
String city = rs.getString("City");
String state = rs.getString("State");
String country = rs.getString("Country");
String aboutUser = rs.getString("AboutMe1");
String gender = rs.getString("Gender");
formName += i;
buttonName += i;
out.println("<form name='" + formName + "' method='post' action='addContact'>");
out.println("<TR>");
out.println("<TD><img src='" + picture + "'</TD>");
out.println("<TD>" + user + "</TD>");
out.println("<TD>" + gender + "</TD>");
out.println("<TD>" + city + " / " + state + "</TD>");
out.println("<TD>" + country + "</TD>");
out.println("<TD>" + aboutUser + "</TD>");
out.println(
"<TD><A href='details.jsp?type=1&data="
+ lookupID
+ "'><IMG SRC='images/detail.jpg'></A></TD>");
out.println(
"<TD><input type='submit' value='Add to Contact List' name='"
+ buttonName
+ "'></TD>");
out.println("<input type='hidden' value='" + user + "' name='hiddenUser'>");
out.println("</TR>");
out.println("</form>");
i++;
}
out.println("</TABLE>");
} catch (Exception e) {
out.println("Could not connect to the users database.<P>");
out.println("The error message was");
out.println("<PRE>");
out.println(e.getMessage());
out.println("</PRE>");
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException ignore) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ignore) {
}
}
if (con != null) {
try {
con.close();
} catch (SQLException ignore) {
}
}
}
out.println("</CENTER>");
out.println("</BODY>");
out.println("</HTML>");
} catch (RuntimeException e) {
out.println("<script language=\"javascript\">");
out.println("alert(\"You need to log in to use this service!\");");
out.println("</script>");
out.println("<a href='index.html'>Click Here</a> to go to the main page.<br><br>");
out.println(
"Or Click on the button to exit<FORM><INPUT onClick=\"javascipt:window.close()\" TYPE=\"BUTTON\" VALUE=\"Close Browser\" TITLE=\"Click here to close window\" NAME=\"CloseWindow\" STYLE=\"font-family:Verdana, Arial, Helvetica; font-size:smaller; font-weight:bold\"></FORM>");
log(e.getMessage());
return;
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
HttpSession session = request.getSession(true);
try {
Object accountObject = session.getValue(ACCOUNT);
// If no account object was put in the session, or
// if one exists but it is not a hashtable, then
// redirect the user to the original login page
if (accountObject == null)
throw new RuntimeException("You need to log in to use this service!");
if (!(accountObject instanceof Hashtable))
throw new RuntimeException("You need to log in to use this service!");
Hashtable account = (Hashtable) accountObject;
String userName = (String) account.get("name");
//////////////////////////////////////////////
// Display Messages for the user who logged in
//////////////////////////////////////////////
out.println("<HTML>");
out.println("<HEAD>");
out.println("<TITLE>Contacts for " + userName + "</TITLE>");
out.println("</HEAD>");
out.println("<BODY BGCOLOR='#EFEFEF'>");
out.println("<H3>Welcome " + userName + "</H3>");
out.println("<CENTER>");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con =
DriverManager.getConnection(
"jdbc:mysql://localhost/contacts?user=kareena&password=kapoor");
stmt = con.createStatement();
rs =
stmt.executeQuery(
"SELECT * FROM contacts WHERE userName='******' ORDER BY contactID");
out.println("<form name='deleteContactsForm' method='post' action='deleteContact'>");
out.println("<TABLE BGCOLOR='#EFEFFF' CELLPADDING='2' CELLSPACING='4' BORDER='1'>");
out.println("<TR BGCOLOR='#D6DFFF'>");
out.println("<TD ALIGN='center'><B>Contact ID</B></TD>");
out.println("<TD ALIGN='center'><B>Contact Name</B></TD>");
out.println("<TD ALIGN='center'><B>Comment</B></TD>");
out.println("<TD ALIGN='center'><B>Date</B></TD>");
out.println("<TD ALIGN='center'><B>Delete Contacts</B></TD>");
out.println("</TR>");
int nRows = 0;
while (rs.next()) {
nRows++;
String messageID = rs.getString("contactID");
String fromUser = rs.getString("contactName");
String message = rs.getString("comments");
String messageDate = rs.getString("dateAdded");
out.println("<TR>");
out.println("<TD>" + messageID + "</TD>");
out.println("<TD>" + fromUser + "</TD>");
out.println("<TD>" + message + "</TD>");
out.println("<TD>" + messageDate + "</TD>");
out.println(
"<TD><input type='checkbox' name='msgList' value='" + messageID + "'> Delete</TD>");
out.println("</TR>");
}
out.println("<TR>");
out.println(
"<TD COLSPAN='6' ALIGN='center'><input type='submit' value='Delete Selected Contacts'></TD>");
out.println("</TR>");
out.println("</TABLE>");
out.println("</FORM>");
} catch (Exception e) {
out.println("Could not connect to the users database.<P>");
out.println("The error message was");
out.println("<PRE>");
out.println(e.getMessage());
out.println("</PRE>");
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException ignore) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ignore) {
}
}
if (con != null) {
try {
con.close();
} catch (SQLException ignore) {
}
}
}
out.println("</CENTER>");
out.println("</BODY>");
out.println("</HTML>");
} catch (RuntimeException e) {
out.println("<script language=\"javascript\">");
out.println("alert(\"You need to log in to use this service!\");");
out.println("</script>");
out.println("<a href='index.html'>Click Here</a> to go to the main page.<br><br>");
out.println(
"Or Click on the button to exit<FORM><INPUT onClick=\"javascipt:window.close()\" TYPE=\"BUTTON\" VALUE=\"Close Browser\" TITLE=\"Click here to close window\" NAME=\"CloseWindow\" STYLE=\"font-family:Verdana, Arial, Helvetica; font-size:smaller; font-weight:bold\"></FORM>");
log(e.getMessage());
return;
}
}
public Design() throws Exception {
super.setBackground(Color.BLACK);
this.setTitle("");
con = getContentPane();
con.setLayout(null);
dim = tk.getDefaultToolkit().getScreenSize();
this.setTitle("Customer Peer Login");
l1 = new JLabel(new ImageIcon("plain.jpg"));
l1.setBounds(0, 0, 400, 400);
con.add(l1);
l1.setBorder(BorderFactory.createEtchedBorder(5, Color.black, Color.black));
title = new JLabel("CUSTOMER PEER LOGIN ");
title.setFont(new Font("Bookman Old Style", Font.ROMAN_BASELINE, 20));
title.setForeground(Color.red);
title.setBounds(80, 30, 300, 30);
l1.add(title);
l4 = new JLabel("CMACHINE NAME");
l4.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l4.setForeground(Color.BLUE);
l4.setBounds(70, 100, 160, 20);
// l4.setBorder(BorderFactory.createEtchedBorder(5,Color.green,Color.green));
l1.add(l4);
jtf2 = new JTextField();
jtf2.setBounds(250, 100, 100, 20);
jtf2.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(jtf2);
l2 = new JLabel("CUSER LOGIN");
l2.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l2.setForeground(Color.blue);
l2.setBounds(70, 150, 120, 20);
l1.add(l2);
jtf1 = new JTextField();
jtf1.setBounds(250, 150, 100, 20);
jtf1.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(jtf1);
l3 = new JLabel("CPASSWORD");
l3.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l3.setForeground(Color.blue);
l3.setBounds(70, 200, 120, 20);
l1.add(l3);
jptf1 = new JPasswordField();
jptf1.setBounds(250, 200, 100, 20);
jptf1.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(jptf1);
JLabel l4 = new JLabel("DAgent");
l4.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l4.setForeground(Color.blue);
l4.setBounds(70, 250, 120, 20);
l1.add(l4);
box = new JComboBox();
box.setBounds(250, 250, 100, 20);
box.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(box);
b2 = new JButton("Register");
b2.setBounds(50, 300, 100, 20);
l1.add(b2);
b2.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));
b3 = new JButton("Login");
b3.setBounds(150, 300, 100, 20);
b3.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));
l1.add(b3);
b1 = new JButton("Cancel");
b1.setBounds(250, 300, 100, 20);
b1.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));
l1.add(b1);
b1.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent we) {
dispose();
}
});
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:agent");
} catch (Exception exp) {
}
try {
Statement satem = conn.createStatement();
ResultSet rsatem = satem.executeQuery("select * from Dagent");
while (rsatem.next()) {
String namem = rsatem.getString("uname");
box.addItem(namem);
}
} catch (Exception expo1) {
}
b2.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent we) {
String username = jtf1.getText().trim();
String password = jptf1.getText().trim();
String mechine = jtf2.getText().trim();
String dname = box.getSelectedItem().toString();
int porte = 0;
try {
Statement sate = conn.createStatement();
ResultSet rsate =
sate.executeQuery("select * from Dagent where uname='" + dname + "'");
if (rsate.next()) {
servermachine = rsate.getString("umechine");
porte = rsate.getInt("ulistport");
System.out.println(servermachine);
}
System.out.println(servermachine);
} catch (Exception exp) {
exp.printStackTrace();
}
try {
packet p = new packet();
p.setaction("Creg");
p.setCuser(username);
p.setCpass(password);
p.setCmname(mechine);
p.setCDpeer(dname);
Socket soc = new Socket(servermachine, porte);
ObjectOutputStream out = new ObjectOutputStream(soc.getOutputStream());
out.writeObject(p);
ObjectInputStream in = new ObjectInputStream(soc.getInputStream());
packet rpac = (packet) in.readObject();
if (rpac.getaction().equals("ok")) {
JOptionPane.showMessageDialog(null, "Sucessfully Registered");
jtf2.setText("");
jtf1.setText("");
jptf1.setText("");
} else {
JOptionPane.showMessageDialog(null, "Already Registered");
jtf2.setText("");
jtf1.setText("");
jptf1.setText("");
}
} catch (Exception exp) {
}
}
});
b3.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent we) {
String username = jtf1.getText().trim();
String password = jptf1.getText().trim();
String mechine = jtf2.getText().trim();
String Dname = box.getSelectedItem().toString();
int porte = 0;
try {
Statement sate = conn.createStatement();
ResultSet rsate =
sate.executeQuery("select * from Dagent where uname='" + Dname + "'");
if (rsate.next()) {
servermachine = rsate.getString("umechine");
porte = rsate.getInt("ulistport");
System.out.println(servermachine);
}
System.out.println(servermachine);
} catch (Exception exp) {
}
try {
packet p1 = new packet();
p1.setaction("clogin");
p1.setCuser(username);
p1.setCpass(password);
p1.setCmname(mechine);
p1.setCDpeer(Dname);
Socket soc1 = new Socket(servermachine, porte);
ObjectOutputStream out1 = new ObjectOutputStream(soc1.getOutputStream());
out1.writeObject(p1);
ObjectInputStream in1 = new ObjectInputStream(soc1.getInputStream());
packet rpac1 = (packet) in1.readObject();
if (rpac1.getaction().equals("ok")) {
int port1 = 0;
try {
int portm = rpac1.getCport();
System.out.println("XXXXXXX" + portm);
// JOptionPane.showMessageDialog(null,"Sucessfully Started");
new Listen(portm);
new process(username, portm);
dispose();
} catch (Exception exp) {
}
} else {
JOptionPane.showMessageDialog(
null, "Enter valid username and password", "Server reply", 2);
jtf1.setText("");
jtf2.setText("");
jptf1.setText("");
}
} catch (Exception exp) {
}
}
});
setSize(400, 400);
show();
setLocation(150, 100);
setResizable(false);
}