数据库表Dao层SQL语句拼接生成工具

最近在用数据库管理三层写一个项目，没有用框架，在建dao层的时候遇到了一个令人十分dan疼的事情，那就是像写插入和更新这种函数的时候SQL语句的拼接简直是要了老命呀，急着赶进度，谁有耐心去写那玩意！就是这：

于是就想要不要写一个小工具选了表就能自动生成这么一块，一拷贝一粘贴多方便，所以这么个小东西就诞生了，随便连个数据库选个表感受一下

把源码放在底下，想用的朋友可拿去玩玩~

package sqlsethelp.itbobby;import java.awt.Color;import java.awt.event.ActionEvent;import java.awt.event.ActionListener;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;import javax.swing.JButton;import javax.swing.JFrame;import javax.swing.JLabel;import javax.swing.JOptionPane;import javax.swing.JScrollPane;import javax.swing.JTextArea;import javax.swing.JTextField;/** *  * @author itBobby *  */@SuppressWarnings("serial")public class Sql_set extends JFrame implements ActionListener {JTextField tab0 = new JTextField("");JTextField obj0 = new JTextField("");static JTextField ip0 = new JTextField("127.0.0.1");static JTextField port0 = new JTextField("3306");static JTextField dbname0 = new JTextField("");static JTextField username0 = new JTextField("");static JTextField userpass0 = new JTextField("");JLabel tab1 = new JLabel("表        名：");JLabel obj1 = new JLabel("对  象  名：");JLabel ip1 = new JLabel("本  地   ip：");JLabel port1 = new JLabel("端  口  号：");JLabel dbname1 = new JLabel("数据库名：");JLabel username1 = new JLabel("用  户  名：");JLabel userpass1 = new JLabel("密        码：");JLabel status = new JLabel("提示：数据库连接已更改…如需更换请重启程序！");JButton bt1 = new JButton("Insert");JButton bt2 = new JButton("Update");JButton bt3 = new JButton("Reset");JTextArea sql = new JTextArea();static {try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static class sqlHelper {public static String dbname = dbname0.getText();public static String ip = ip0.getText();public static String port = port0.getText();public static String username = username0.getText();public static String userpass = userpass0.getText();public static Connection getConnection() throws SQLException {return DriverManager.getConnection("jdbc:mysql://" + ip + ":"+ port + "/" + dbname, username, userpass);}private static Statement getStatement() throws Exception {return getConnection().createStatement();}public static ResultSet executeQuery(String sql) {Statement st = null;try {st = getStatement();return st.executeQuery(sql);} catch (Exception e) {}return null;}}public Sql_set() {setTitle("Tool By itBobby");setSize(340, 480);setDefaultCloseOperation(3);setLocationRelativeTo(null);setLayout(null);setResizable(false);tab0.setBounds(110, 200, 200, 25);obj0.setBounds(110, 230, 200, 25);ip0.setBounds(110, 20, 200, 25);port0.setBounds(110, 50, 200, 25);dbname0.setBounds(110, 80, 200, 25);username0.setBounds(110, 110, 200, 25);userpass0.setBounds(110, 140, 200, 25);status.setBounds(25, 170, 300, 25);status.setForeground(Color.orange);tab1.setBounds(25, 200, 70, 25);obj1.setBounds(25, 230, 70, 25);ip1.setBounds(25, 20, 70, 25);port1.setBounds(25, 50, 70, 25);dbname1.setBounds(25, 80, 70, 25);username1.setBounds(25, 110, 70, 25);userpass1.setBounds(25, 140, 70, 25);bt1.setBounds(25, 270, 80, 25);bt2.setBounds(130, 270, 80, 25);bt3.setBounds(230, 270, 80, 25);JScrollPane s = new JScrollPane(sql);s.setBounds(25, 310, 285, 120);add(tab0);add(obj0);add(ip0);add(port0);add(dbname0);add(username0);add(userpass0);add(tab1);add(obj1);add(ip1);add(port1);add(dbname1);add(username1);add(userpass1);add(bt1);add(bt2);add(bt3);add(s);bt1.addActionListener(this);bt2.addActionListener(this);bt3.addActionListener(this);setVisible(true);}public void Reset() {tab0.setText("");obj0.setText("");}public static void main(String[] args) {new Sql_set();}@Overridepublic void actionPerformed(ActionEvent e) {if (e.getSource().equals(bt1)) {add(status);repaint();try {ResultSet rs = getrs();if (rs == null) {JOptionPane.showMessageDialog(this, "数据库信息不完整或错误！");System.out.println("select * from " + tab0.getText()+ " limit 1");return;}ResultSetMetaData m = rs.getMetaData();StringBuilder str = new StringBuilder("\"insert into "+ tab0.getText() + " (");for (int i = 1; i <= m.getColumnCount(); i++) {if (m.getColumnName(i).equals("id"))continue;str.append(m.getColumnName(i));if (i < m.getColumnCount())str.append(",");}str.append(")\"\n + \"values(\"+\n");for (int i = 1; i <= m.getColumnCount(); i++) {if (m.getColumnName(i).equals("id"))continue;String s = m.getColumnName(i);String col = obj0.getText() + ".get"+ ("" + s.charAt(0)).toUpperCase() + s.substring(1)+ "()";String c = "";if (i < m.getColumnCount())c = ",";if (m.getColumnType(i) == Types.INTEGER|| m.getColumnType(i) == Types.DOUBLE) {str.append(col + "+\"" + c + "\"+\n");} else {str.append("\"'\"+" + col + "+\"'" + c + "\"+\n");}}str.append("\")\"");sql.setText(str.toString());} catch (Exception e1) {}} else if (e.getSource().equals(bt2)) {add(status);repaint();try {ResultSet rs = getrs();System.out.println(111);if (rs == null) {JOptionPane.showMessageDialog(this, "数据库信息不完整或错误！");System.out.println("select * from " + tab0.getText()+ " limit 1");return;}ResultSetMetaData m = rs.getMetaData();StringBuilder str = new StringBuilder("\"update "+ tab0.getText() + " set \"+\n");for (int i = 1; i <= m.getColumnCount(); i++) {if (m.getColumnName(i).equals("id"))continue;str.append("\"" + m.getColumnName(i) + "=\"+");String s = m.getColumnName(i);String col = obj0.getText() + ".get"+ ("" + s.charAt(0)).toUpperCase() + s.substring(1)+ "()";String c = "";if (i < m.getColumnCount())c = ",";if (m.getColumnType(i) == Types.INTEGER|| m.getColumnType(i) == Types.DOUBLE) {str.append(col + "+\"" + c + "\"+\n");} else {str.append("\"'\"+" + col + "+\"'" + c + "\"+\n");}}str.append("\" where id=\" + " + obj0.getText() + ".getId());");sql.setText(str.toString());} catch (Exception e2) {}} else if (e.getSource().equals(bt3)) {Reset();}}public ResultSet getrs() {return sqlHelper.executeQuery("select * from " + tab0.getText()+ " limit 1");}}

 

#使用过程中如有问题请与本人联系，转载请注明出处#