educesql_fpg_1.java

一些平时可能用到的工具,比如导出数据,比较数据

								break;
							case 13:
								// HOST = 172.21.1.225 SID = gjora
								host = "172.21.1.225";
								sid = "gjora";
								username = "u1?p";
								passwd = "u1?p";
								break;
							case 14:
								// ?
								host = "";
								sid = "";
								username = "u1?p";
								passwd = "u1?p";
								break;
							case 15:
								// HOST = 172.21.2.2 SID = tycdb
								host = "172.21.2.2";
								sid = "tycdb";
								username = "u1?p";
								passwd = "u1?p";
								break;
							case 16:
								// ?
								host = "";
								sid = "";
								username = "u1?p";
								passwd = "u1?p";
								break;
							case 17:
								// ?
								host = "";
								sid = "";
								username = "u1?p";
								passwd = "u1?p";
								break;
							case 18:
								// HOST=172.21.1.71 SID=tlwh
								host = "172.21.1.71";
								sid = "tlwh";
								username = "u2?p";
								passwd = "u2?p";
								break;
							case 19:
								// 主機名稱 : SANDB
								// IP : 172.21.2.18
								// ORACLE SID : SANDB
								host = "172.21.2.18";
								sid = "SANDB";
								username = "u1?p";
								passwd = "u1?p";
								break;
							case 20:
								// 主機名稱 : FPG100
								// IP : 172.21.2.33
								// ORACLE SID : FPG100
								host = "172.21.2.33";
								sid = "FPG100";
								username = "u1?p";
								passwd = "u1?p";
								break;
							case 21:
								// 主機名稱 : FPG100T
								// IP : 172.21.2.33
								// ORACLE SID : FPG100T
								host = "172.21.2.33";
								sid = "FPG100T";
								username = "u1?p";
								passwd = "u1?p";
								break;
							case 22:
								// 主機名稱 :
								// IP : 192.168.14.103
								// ORACLE SID : erpdb
								host = "192.168.14.103";
								sid = "erpdb";
								username = "u1?p";
								passwd = "u1?p";
								break;
							case 23:
								// 主機名稱 :
								// IP : 192.168.14.103
								// ORACLE SID : erpdbt
								host = "192.168.14.103";
								sid = "erpdbt";
								username = "u1?p";
								passwd = "u1?p";
								break;
							case 24:
								// 主機名稱 :
								// IP :192.168.1.80
								// ORACLE SID : abcdb
								host = "192.168.1.80";
								sid = "abcdb";
								username = "u1?p";
								passwd = "u1?p";
								break;
							case 25:
								// 主機名稱 :
								// IP : 192.168.1.80
								// ORACLE SID : abcdb
								host = "192.168.1.80";
								sid = "abcdb";
								username = "u2?p";
								passwd = "u2?p";
								break;
							case 26:
								// 主機名稱 : E147DB
								// IP : 172.21.2.50
								// ORACLE SID : E147DB
								host = "172.21.2.50";
								sid = "E147DB";
								username = "u1?p";
								passwd = "u1?p";
								break;
							default:
								break;
							}
							txtHost.setText(host);
							txtPort.setText(port);
							txtSid.setText(sid);
							txtUsername.setText(username);
							txtPassword.setText(passwd);
						}
					});
				}
			}
			pack();
			this.setSize(558, 244);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean Conn() {
		username = txtUsername.getText();
		password = txtPassword.getText();
		host = txtHost.getText();
		port = txtPort.getText();
		sid = txtSid.getText();
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection("jdbc:oracle:thin:@" + host + ":"
					+ port + ":" + sid, username, password);
		} catch (ClassNotFoundException e) {
			System.out.println("载入数据驱动出错！！！！！！");
			errmsg.append("载入数据驱动出错！！！！！！");
			txtMsg.setText("载入数据驱动出错！！！！！！\n");
			e.printStackTrace();
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			if (e.toString().contains("ORA-12505")) {
				System.out.println("数据库未启动！！！！");
				errmsg.append("数据库未启动！！！！");
				txtMsg.setText("数据库未启动！！！！");
				return false;
			} else if (e.toString().contains("ORA-12528")
					|| e.toString().contains("ORA-01033")) {
				System.out.println("数据库正在启动！！！！");
				errmsg.append("数据库正在启动！！！！");
				txtMsg.setText("数据库正在启动！！！！");
				return false;
			} else if (e.toString().contains("ORA-01017")) {
				System.out.println("用户或密码错误！！！！");
				errmsg.append("用户或密码错误！！！！");
				txtMsg.setText("用户或密码错误！！！！");
				return false;
			} else if (e.toString().contains(
					"The Network Adapter could not establish the connection")) {
				System.out.println("数据库未启动！！！！");
				errmsg.append("数据库未启动！！！！");
				txtMsg.setText("数据库未启动！！！！");
				return false;
			} else {
				System.out.println("未知错误！！！！");
				errmsg.append("未知错误！！！！");
				txtMsg.setText("未知错误！！！！");
				return false;
			}
		}
		return true;
	}

	private String getcolumn() {
		StringBuffer all_column_name = new StringBuffer();
		vec_column_name = new Vector();
		ResultSet rs = null;
		// PreparedStatement ps_column = null;
		try {
			if (ps_column == null) {
				ps_column = con.prepareStatement(sql_column_name);
			}
			ps_column.setString(1, str_table);
			rs = ps_column.executeQuery();
			while (rs.next()) {
				vec_column_name.add(rs.getString("COLUMN_NAME"));// 取得列名
			}
		} catch (SQLException e) {
			errmsg.append("err");
			txtMsg.setText("此表不存在！");
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
			} catch (SQLException e) {
				errmsg.append("err");
				txtMsg.setText("数据出错！");
			}
		}
		if (vec_column_name.size() != 0) {
			for (int i = 0; i < vec_column_name.size(); i++) {
				if (i != vec_column_name.size() - 1) {
					all_column_name.append(vec_column_name.get(i).toString()
							+ ",");
				} else {
					all_column_name.append(vec_column_name.get(i).toString());
				}
			}
		} else {
			errmsg.append("err");
			txtMsg.setText("没有资料！");
		}
		return all_column_name.toString();

	}

	private Vector getcolumnvalues() {
		ResultSet rs = null;
		PreparedStatement ps = null;
		Vector all_column_values = new Vector();
		condition = txtCondition.getText();
		txemp = str_table.substring(6, 7) + "POWER";
		try {
			// 20081205 增加条件查询
			if (condition.equals("只需要输入条件,前面不用加where") || condition.equals("")
					|| condition == null) {
				ps = con.prepareStatement("select * from " + str_table);
				rs = ps.executeQuery();
				while (rs.next()) {
					Vector vec_column_value = new Vector();
					for (int i = 0; i < vec_column_name.size(); i++) {
						if (i != vec_column_name.size() - 1) {
							// 增加公司替换为X
							if (chkCo.isSelected()
									&& vec_column_name.get(i).toString()
											.equalsIgnoreCase("CO")) {
								vec_column_value.add("'X',");
							} else {
								// 增加異動人員替换
								if (chkTxemp.isSelected()
										&& str_table.length() == 11
										&& vec_column_name.get(i).toString()
												.equalsIgnoreCase("TXEMP")) {
									vec_column_value.add("'" + txemp + "',");
								} else {
									// END
									// 防止值为null时，导出为'null'
									if (rs.getString(vec_column_name.get(i)
											.toString()) == null) {
										vec_column_value.add("null,");
									} else {
										vec_column_value.add("'"
												+ rs.getString(vec_column_name
														.get(i).toString())
												+ "',");
									}
								}
							}
						} else {
							// 增加公司替换为X
							if (chkCo.isSelected()
									&& vec_column_name.get(i).toString()
											.equalsIgnoreCase("CO")) {
								vec_column_value.add("'X'");
							} else {
								// END
								// 增加異動人員替换
								if (chkTxemp.isSelected()
										&& str_table.length() == 11
										&& vec_column_name.get(i).toString()
												.equalsIgnoreCase("TXEMP")) {
									vec_column_value.add("'" + txemp + "'");
								} else {
									// 防止值为null时，导出为'null'
									if (rs.getString(vec_column_name.get(i)
											.toString()) == null) {
										vec_column_value.add("null");
									} else {
										vec_column_value.add("'"
												+ rs.getString(vec_column_name
														.get(i).toString())
												+ "'");
									}
								}
							}
						}
					}

					all_column_values.add(vec_column_value);
				}
			} else {
				ps = con.prepareStatement("select * from " + str_table
						+ " where " + condition);
				rs = ps.executeQuery();
				while (rs.next()) {
					Vector vec_column_value = new Vector();
					for (int i = 0; i < vec_column_name.size(); i++) {
						if (i != vec_column_name.size() - 1) {
							// 增加公司替换为X
							if (chkCo.isSelected()
									&& vec_column_name.get(i).toString()
											.equalsIgnoreCase("CO")) {
								vec_column_value.add("'X',");
							} else {
								// 增加異動人員替换
								if (chkTxemp.isSelected()
										&& str_table.length() == 11
										&& vec_column_name.get(i).toString()
												.equalsIgnoreCase("TXEMP")) {
									vec_column_value.add("'" + txemp + "',");
								} else {
									// END
									// 防止值为null时，导出为'null'
									if (rs.getString(vec_column_name.get(i)
											.toString()) == null) {
										vec_column_value.add("null,");
									} else {
										vec_column_value.add("'"
												+ rs.getString(vec_column_name
														.get(i).toString())
												+ "',");
									}
								}
							}
						} else {
							// 增加公司替换为X
							if (chkCo.isSelected()
									&& vec_column_name.get(i).toString()
											.equalsIgnoreCase("CO")) {
								vec_column_value.add("'X'");
							} else {
								// END
								// 增加異動人員替换
								if (chkTxemp.isSelected()
										&& str_table.length() == 11
										&& vec_column_name.get(i).toString()
												.equalsIgnoreCase("TXEMP")) {
									vec_column_value.add("'" + txemp + "'");
								} else {
									// 防止值为null时，导出为'null'
									if (rs.getString(vec_column_name.get(i)
											.toString()) == null) {
										vec_column_value.add("null");
									} else {
										vec_column_value.add("'"
												+ rs.getString(vec_column_name
														.get(i).toString())
												+ "'");
									}
								}
							}
						}
					}

					all_column_values.add(vec_column_value);
				}

			}
		} catch (SQLException e) {
			errmsg.append("err" + e);
			txtMsg.setText("表不存在或输入的条件有错!!!");
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (ps != null) {
					ps.close();
					ps = null;
				}
			} catch (SQLException e) {
				errmsg.append("err");
				txtMsg.setText("关闭错误!!!");
			}
		}
		return all_column_values;
	}

	private void generatesql() {
		long start = System.currentTimeMillis();
		errmsg = new StringBuffer();
		if (checkischange()) {
			close();// 防止改变用户和密码
			Conn();
		}
		if (errmsg.length() == 0) {
			String SQL = "";
			str_table = txtTABLE.getText().toUpperCase();
			String column = getcolumn();
			Vector columnvalues = getcolumnvalues();
			for (int i = 0; i < columnvalues.size(); i++) {
				String a = "";
				Vector v = (Vector) columnvalues.get(i);
				for (int j = 0; j < v.size(); j++) {
					a = a + v.get(j).toString();
				}
				SQL = SQL + "INSERT INTO " + str_table + "(" + column + ")"
						+ "VALUES" + "(" + a + ");\r\n";
			}
			if (errmsg.length() == 0) {
				try {
					filepath = "C://" + str_table + ".sql";
					fostream = new FileOutputStream(filepath, false);
					fostream.write(SQL.getBytes());
					fostream.close();
				} catch (FileNotFoundException e) {
					System.out.print(this.getClass().toString() + "toFile" + e);
					txtMsg.setText("\u751f\u6210\u6210\u529f");// 文件未找到
				} catch (IOException e) {
					System.out
							.print(this.getClass().toString() + "toFile1" + e);
					txtMsg.setText("\u6587\u4ef6\u751f\u6210\u9519\u8bef");// 文件生成错误
				}
				// 测试所花时间
				DecimalFormat df = new DecimalFormat("#,##0.###");// 格式化时间
				txtMsg
						.setText("用时"
								+ (df.format(Double.valueOf(System
										.currentTimeMillis()
										- start) / 1000))
								+ "秒!\n"
								+ "资料导出成功！"
								+ "\u6587\u4ef6\u4e3a"
								+ "\n"
								+ "C:\\"
								+ str_table
								+ ".sql"
								+ "\n"
								+ "\u5982\u8981\u67e5\u770b\u751f\u6210\u7684SQL\uff0c\u8bf7\u70b9\u67e5\u770b");// 生成成功,文件为,如要查看生成的SQL，请点查看
				// txtMsg.setText("资料导出成功！");

			}
		}
		System.out.print(errmsg);
	}

	// 判断是否重新连接数据库
	private boolean checkischange() {
		// 第一次進行連接
		if (checkconn == 0) {
			checkconn++;
			return true;
		} else {
			temp_username = txtUsername.getText();
			temp_password = txtPassword.getText();
			temp_host = txtHost.getText();
			temp_port = txtPort.getText();
			temp_sid = txtSid.getText();
			if (!temp_username.equalsIgnoreCase(username)) {
				return true;
			}
			if (!temp_password.equalsIgnoreCase(password)) {
				return true;
			}
			if (!temp_host.equalsIgnoreCase(host)) {
				return true;
			}
			if (!temp_port.equalsIgnoreCase(port)) {
				return true;
			}
			if (!temp_sid.equalsIgnoreCase(sid)) {
				return true;
			}
		}
		return false;

	}

	private void close() {
		try {
			if (con != null) {
				con.close();
				con = null;
			}
			if (ps_column != null) {
				ps_column.close();
				ps_column = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void Exit() {
		this.addWindowListener(new WindowAdapter() {
			public void windowClosing(WindowEvent evt) {
				// showtime.stop();
				close();
				showtime.stop();
				dispose();
			}
		});
	}
}