educesql.java

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

									checkcondition = 1;
								}
							}
						});
					}
				}
				{
					btnTest = new JButton();
					jPanel1.add(btnTest);
					btnTest.setText("\u6d4b\u8bd5\u6570\u636e\u5e93");
					btnTest.setBounds(224, 49, 133, 28);
					btnTest.addActionListener(new ActionListener() {
						public void actionPerformed(ActionEvent evt) {
							close();
							if (Conn()) {
								txtMsg.setText("连接数据库正常!!!");
							} else {
								txtMsg.setText("连接数据库失败!!!");
							}
						}
					});
				}
				{
					panTime = new JPanel();
					BorderLayout panTimeLayout = new BorderLayout();
					jPanel1.add(panTime);
					panTime.setBounds(448, 126, 91, 42);
					panTime.setLayout(panTimeLayout);
					showtime = new Showtime();
					showtime.jTextPane1
							.setPreferredSize(new java.awt.Dimension(96, 42));
					showtime.jTextPane1.setFont(new java.awt.Font("Tahoma", 0,
							16));
					panTime.add(showtime, BorderLayout.CENTER);
				}
			}
			pack();
			this.setSize(558, 217);
		} 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();

		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) {
							// 防止值为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 {
							// 防止值为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) {
							// 防止值为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 {
							// 防止值为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");
			txtMsg.setText("表不存在或输入的条件有错!!!");
		} finally {
			try {
				rs.close();
				rs = 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");// 文件生成错误
				}
				// 测试所花时间
				txtMsg
						.setText("用时"
								+ ((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(SQL);
	}

	// 判断是否重新连接数据库
	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();
			}
		});
	}
}