createsql.java

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

			}

			{
				btnNEW = new JButton();
				jPanel1.add(btnNEW);
				btnNEW.setText("\u65b0\u589e");
				btnNEW.setBounds(84, 56, 70, 28);
				btnNEW.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						jTable1Model.addRow(new String[] {});
						txtMSG.setText("\u65b0\u589e\u6210\u529f");// 新增成功
					}
				});
			}
			{
				btnDELETE = new JButton();
				jPanel1.add(btnDELETE);
				btnDELETE.setText("\u5220\u9664");
				btnDELETE.setBounds(252, 56, 70, 28);
				btnDELETE.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						try {
							jTable1Model.removeRow(jTable1.getSelectedRow());
							txtMSG.setText("\u5220\u9664\u6210\u529f");// 删除成功
						} catch (Exception e) {
							txtMSG
									.setText("\u8bf7\u9009\u62e9\u5220\u9664\u7684\u5217");// 请选择删除的列
						}
					}
				});
			}
			{
				btnVIEW = new JButton();
				jPanel1.add(btnVIEW);
				btnVIEW.setText("\u67e5\u770b");
				btnVIEW.setBounds(413, 56, 70, 28);
				btnVIEW.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						// 打开生成的sql
						Runtime rt = Runtime.getRuntime();
						try {
							rt.exec("CMD /C" + filepath.replace("//", "\\"));
							txtMSG
									.setText("\u6253\u5f00\u6587\u4ef6\u6210\u529f");// 打开文件成功
						} catch (Exception e) {
							txtMSG
									.setText("\u6253\u5f00\u6587\u4ef6\u5931\u8d25");// 打开文件失败
							rt.gc();
						}
					}
				});
			}
			{
				panTIME = new JPanel();
				BorderLayout panTIMELayout = new BorderLayout();
				panTIME.setLayout(panTIMELayout);
				jPanel1.add(panTIME);
				panTIME.setBounds(707, 49, 70, 42);
				showtime = new Showtime();
				panTIME.add(showtime);
			}
			{
				chboxDrop = new JCheckBox();
				jPanel1.add(chboxDrop);
				chboxDrop.setText("\u542b\u5220\u9664\u6307\u4ee4");
				chboxDrop.setBounds(504, 56, 91, 28);
			}
			{
				labTABLEMSG = new JLabel();
				jPanel1.add(labTABLEMSG);
				labTABLEMSG.setText("\u8868\u8bf4\u660e");
				labTABLEMSG.setBounds(518, 14, 56, 28);
			}
			{
				txtTablemsg = new JTextField();
				jPanel1.add(txtTablemsg);
				txtTablemsg.setBounds(581, 14, 196, 28);
			}
			{
				jScrollPane3 = new JScrollPane();
				jPanel1.add(jScrollPane3);
				jScrollPane3.setBounds(35, 427, 721, 63);
				{
					tableFKModel = new DefaultTableModel(new String[][] { {} },
							new String[] { "本表外鍵", "外鍵表", "外鍵列" });
					tableFK = new JTable();
					jScrollPane3.setViewportView(tableFK);
					tableFK.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
					tableFK.setModel(tableFKModel);
					tableFK.getTableHeader().setBounds(0, 0, 721, 42);
					tableFK.setRowHeight(30);
					tableFK.addMouseListener(new MouseAdapter() {
						public void mouseClicked(MouseEvent evt) {
							if (tableFK
									.getValueAt(tableFK.getRowCount() - 1, 0) != null
									&& !tableFK.getValueAt(
											tableFK.getRowCount() - 1, 0)
											.toString().equals("")) {
								if (evt.getClickCount() == 1)
									tableFKModel.addRow(new String[] {});
							}
						}
					});
					tableFK.getAutoResizeMode();
				}
			}

		}
	} // 生成表

	private void savetofile(Vector v, Vector vFK) {

		table = txtTable.getText().toUpperCase();
		tablenm = txtTablenm.getText().toUpperCase();
		tablemsg = txtTablemsg.getText().toUpperCase();
		if (table.equals("")) {
			txtMSG.setText("\u8f93\u5165\u8868\u4ee3\u53f7");// 输入表代号
		} else if (tablenm.equals("")) {
			txtMSG.setText("\u8f93\u5165\u8868\u540d\u79f0");// 输入表名称
		} else if (!Character.isLetter(table.charAt(0))) {
			txtMSG
					.setText("\u8868\u540d\u5fc5\u987b\u662f\u4ee5\u5b57\u6bcd\u5f00\u5934");// 表名必须是以字母开头
		} else if (table.length() > 30) {
			txtMSG
					.setText("\u8868\u7684\u957f\u5ea6\u4e3a1\u201330\u4e2a\u5b57\u7b26\u957f\u5ea6");// 表的长度为1–30个字符长度
		}
		// \b[a-zA-Z\u4e00-\u9fa5][a-zA-Z0-9#$_\u4e00-\u9fa5]{0,29}正则表达式
		// else if (table.matches("[ABC]")) {
		// System.out.print("aaaaaaa");
		// }
		else {
			tablemes = l_diagonal + star + enter + tableNM + blank + tablenm
					+ enter + tableMSG + blank + tablemsg + enter + createtime
					+ blank + showtime.jTextPane1.getText() + enter + star
					+ l_diagonal + enter;

			sql = tablemes + create + table + enter + left + getvalue(v);
			if (vectorPK.size() != 0) {
				// 有主键
				sql = sql
						+ constraint
						+ blank
						+ table
						+ _pk
						+ blank
						+ primarykey
						+ left
						+ vectorPK.toString().substring(1,
								vectorPK.toString().length() - 1) + right
						+ enter + right + semicolon + enter + l_diagonal
						+ enter;
			} else {
				// 没有主键
				sql = sql + right + semicolon + enter + l_diagonal + enter;
			}
			if (chboxDrop.isSelected()) {
				sql = drop + blank + table + semicolon + enter + sql;
			}
			// 增加外键
			sql += getFKvalue(vFK);
			if (err.toString().equals("")) {
				System.out.print(sql);
				try {
					filepath = "D://" + 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("\u751f\u6210\u6210\u529f"
								+ ",\u6587\u4ef6\u4e3a"
								+ "\n"
								+ "D:\\"
								+ table
								+ ".sql"
								+ "\n"
								+ "\u5982\u8981\u67e5\u770b\u751f\u6210\u7684SQL\uff0c\u8bf7\u70b9\u67e5\u770b");// 生成成功,文件为,如要查看生成的SQL，请点查看
			} else {
				txtMSG.setText(err.toString());
			}
		}
	} // 取得表内容

	private String getvalue(Vector v) {
		String value = "";// 每次点生成时清空，以免刚点生成生成成功后，又点生成，造成生成的sql列重复
		vectorPK = new Vector();
		boolean check = true;
		v2 = new Vector();// 存放一列的值
		for (int i = 0; i < v.size(); i++) {
			v2 = (Vector) v.get(i);
			columnnm = v2.get(0).toString().toUpperCase();
			columnid = v2.get(1).toString().toUpperCase();
			type = v2.get(2).toString().toUpperCase();
			size = v2.get(3).toString().toUpperCase();
			pk = v2.get(4).toString().toUpperCase();
			notnull = v2.get(5).toString().toUpperCase();
			remarks = v2.get(6).toString().toUpperCase();
			if (columnnm.equals("")) {
				err.append("\u7b2c" + (i + 1) + "\u884c"
						+ "\u5217\u540d\u4e0d\u53ef\u4e3a\u7a7a,");
			}
			if (columnid.equals("")) {
				err.append("\u7b2c" + (i + 1) + "\u884c"
						+ "\u5217\u4ee3\u53f7\u4e0d\u53ef\u4e3a\u7a7a,");
			}
			if (type.equals("")) {
				err.append("\u7b2c" + (i + 1) + "\u884c"
						+ "\u7c7b\u578b\u4e0d\u53ef\u4e3a\u7a7a,");
			}
			if (size.equals("")) {
				err.append("\u7b2c" + (i + 1) + "\u884c"
						+ "\u5927\u5c0f\u4e0d\u53ef\u4e3a\u7a7a");
			} else {
				try {
					Integer.valueOf(size);
				} catch (Exception e) {
					check = false;
					err.append("\u7b2c" + (i + 1) + "\u884c"
							+ "\u5927\u5c0f\u53ea\u53ef\u4e3a\u6570\u5b57");
				}
				if (check) {
					if (Integer.valueOf(size) <= 0) {
						check = false;
						err.append("\u7b2c" + (i + 1) + "\u884c"
								+ "\u5927\u5c0f\u5fc5\u987b\u5927\u4e8e\u96f6");// 大小必须大于零
					} else {
						if (pk.equalsIgnoreCase("Y")) {
							vectorPK.add(columnid);// 存放主键的列代号
						}
						// 最后一行
						if (i == v.size() - 1) {
							//没有PK
							if (vectorPK.size() == 0) {
								if (remarks != null && !remarks.equals(""))
									value = value + columnid + blank + type
											+ blank + left + size + right
											+ blank + notnull + mark + columnnm
											+ blank + left + remarks + right
											+ enter;
								else {
									value = value + columnid + blank + type
											+ blank + left + size + right
											+ blank + notnull + mark + columnnm
											+ enter;
								}
							}
							//有PK
							else {
								if (remarks != null && !remarks.equals(""))
									value = value + columnid + blank + type
											+ blank + left + size + right
											+ blank + notnull + comma+mark + columnnm
											+ blank + left + remarks + right
											+ enter;
								else {
									value = value + columnid + blank + type
											+ blank + left + size + right
											+ blank + notnull +comma+ mark + columnnm
											+ enter;
								}
							}
						}
						//不是最后一行
						else {
							if (remarks != null && !remarks.equals(""))
								value = value + columnid + blank + type + blank
										+ left + size + right + blank + notnull
										+ comma+ mark + columnnm + blank + left
										+ remarks + right +enter;
							else {
								value = value + columnid + blank + type + blank
										+ left + size + right + blank + notnull
										+ comma+mark + columnnm + enter;
							}
						}

					}
				}
			}
		}
		return value;
	}

	private String getFKvalue(Vector v) {
		String value = "";// 每次点生成时清空，以免刚点生成生成成功后，又点生成，造成生成的sql列重复
		String table = txtTable.getText().toUpperCase();
		v2 = new Vector();// 存放一列的值
		for (int i = 0; i < v.size(); i++) {
			v2 = (Vector) v.get(i);
			String fk = v2.get(0).toString().toUpperCase();
			String fktable = v2.get(1).toString().toUpperCase();
			String fkcolumn = v2.get(2).toString().toUpperCase();
			if (fk.equals("")) {
				err.append("\u7b2c" + (i + 1) + "\u884c"
						+ "\u672c\u8868\u5916\u952e\u4e0d\u53ef\u4e3a\u7a7a,");
			}
			if (fktable.equals("")) {
				err.append("\u7b2c" + (i + 1) + "\u884c"
						+ "\u5916\u952e\u8868\u4e0d\u53ef\u4e3a\u7a7a,");
			}
			if (fkcolumn.equals("")) {
				err.append("\u7b2c" + (i + 1) + "\u884c"
						+ "\u5916\u952e\u8868\u5217\u4e0d\u53ef\u4e3a\u7a7a,");
			} else {
				// ALTER TABLE &&user..TXD000FCEG1 ADD CONSTRAINTS
				// FK_TFCEG1_THGA61_1
				// FOREIGN KEY (CO,STK) REFERENCES TXD000HGA61(CO,STK)
				// ;
				value += altertable + blank + table + blank + add + blank
						+ constraint + blank + fk_ + table + underline
						+ fktable + underline + (i + 1) + blank + foreignkey
						+ blank + left + fk + right + blank + references
						+ blank + fktable + left + fkcolumn + right + semicolon
						+ enter + l_diagonal + enter;
			}
		}
		return value;
	}

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

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		new CreateSql();

	}
}