AccessToMySql数据库的导入以及java生成.exe文件

来源：互联网发布：ubuntu进不去系统编辑：程序博客网时间：2024/05/17 00:13

一、AccessToMySql

最近做了一个Access数据库导入MySql的小工具，期间遇到诸多问题，这里小计一下。

表名为cur_rec，共有5个字段

比较奇葩的是这个表居然是四个字段的联合主键，要实现的功能为从Access数据库里面导一个时间段的数据到Mysql，例如：起始日期：2015-02-27 截止日期：2015-03-02

一开始是准备用ODBC，后来觉得还要配置数据源，比较麻烦，还有路径问题（后续会制作成.exe）

于是下载了一个Access_JDBC30.jar的驱动（官网的似乎是需要付费的，下载的驱动包有一个问题就是一次最大能够读取1000条数据）

于是乎想到了分页查询，使用的时候发现Mysql的分页语句不能使用，纠结了好久.....

但是在分页查询的时候查找网上资料，一般都是只有一个主键的，最后请教了一下我们的牛人，

什么都不说了，直接上代码：

MysqlTest.java

  1 package com.cn.jpz.mysql;  2   3 import java.awt.EventQueue;  4   5 import javax.swing.JFrame;  6 import javax.swing.JLabel;  7 import javax.swing.JOptionPane;  8   9 import java.awt.BorderLayout; 10 import java.awt.Color; 11 import java.awt.event.ActionEvent; 12 import java.awt.event.ActionListener; 13 import java.sql.Connection; 14 import java.sql.Date; 15 import java.sql.DriverManager; 16 import java.sql.PreparedStatement; 17 import java.sql.ResultSet; 18 import java.sql.SQLException; 19 import java.sql.Statement; 20 import java.text.ParseException; 21 import java.text.SimpleDateFormat; 22 import java.util.ArrayList; 23 import java.util.List; 24  25 import javax.swing.JTextField; 26 import javax.swing.JButton; 27  28 public class MysqlTest { 29  30     private JFrame frame; 31     private JTextField textField; 32     private JTextField textField_1; 33  34     private JButton btnInsert; 35     private String sDriver; 36     private Connection conn; 37     private PreparedStatement pstmt = null; 38     private Statement stmt; 39     private PreparedStatement pst = null; 40     private List<ChildVista> datas = new ArrayList<ChildVista>(); 41     private JButton btnNewButton; 42  43     public static String startDateString; 44     public static String endDateString; 45     public static boolean isValidDate; 46     private JTextField textField_2; 47     private PreparedStatement pst2 = null; 48     private ResultSet rst = null; 49     private ResultSet rs; 50     public static int equipNo; 51     private static final int PAGE_SIZE=1000; 52  53     /** 54      * Launch the application. 55      */ 56     public static void main(String[] args) { 57         EventQueue.invokeLater(new Runnable() { 58             public void run() { 59                 try { 60                     MysqlTest window = new MysqlTest(); 61                     window.frame.setVisible(true); 62                 } catch (Exception e) { 63                     e.printStackTrace(); 64                 } 65             } 66         }); 67     } 68  69     /** 70      * Create the application. 71      */ 72     public MysqlTest() { 73         initialize(); 74     } 75  76     /** 77      * Initialize the contents of the frame. 78      */ 79     private void initialize() { 80         frame = new JFrame(); 81         frame.getContentPane().setBackground(Color.WHITE); 82         frame.setBounds(100, 100, 450, 300); 83         frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); 84         frame.getContentPane().setLayout(null); 85  86         JLabel lblNewLabel = new JLabel("\u8D77\u59CB\u65E5\u671F\uFF1A"); 87         lblNewLabel.setBounds(24, 69, 69, 15); 88         frame.getContentPane().add(lblNewLabel); 89  90         JLabel lblNewLabel_1 = new JLabel("\u622A\u6B62\u65E5\u671F\uFF1A"); 91         lblNewLabel_1.setBounds(246, 69, 66, 15); 92         frame.getContentPane().add(lblNewLabel_1); 93  94         textField = new JTextField(); 95         textField.setText("2015-02-27"); 96         textField.setBounds(103, 66, 79, 21); 97         frame.getContentPane().add(textField); 98         textField.setColumns(10); 99 100         textField_1 = new JTextField();101         textField_1.setText("2015-03-02");102         textField_1.setBounds(322, 66, 79, 21);103         frame.getContentPane().add(textField_1);104         textField_1.setColumns(10);105 106         btnInsert = new JButton("\u5F00\u59CB");107         btnInsert.addActionListener(new ActionListener() {108             public void actionPerformed(ActionEvent e) {109                 batchInsertIntoMysql();110             }111         });112         btnInsert.setBounds(164, 154, 93, 23);113         frame.getContentPane().add(btnInsert);114 115         btnNewButton = new JButton("\u83B7\u53D6\u65E5\u671F");116         btnNewButton.addActionListener(new ActionListener() {117             public void actionPerformed(ActionEvent e) {118                 isValidDate = getDate();119                 if (isValidDate) {120                     ConnODBC();121                     getDataFromAccess();122                 } else {123                     JOptionPane.showMessageDialog(null, "日期、设备号格式不正确，请重新输入！！");124                 }125             }126         });127         btnNewButton.setBounds(164, 114, 93, 23);128         frame.getContentPane().add(btnNewButton);129 130         JLabel lblNewLabel_2 = new JLabel(131                 "\u65E5\u671F\u683C\u5F0F\uFF1A2015-03-20");132         lblNewLabel_2.setBounds(24, 23, 129, 21);133         frame.getContentPane().add(lblNewLabel_2);134 135         JLabel lblNewLabel_3 = new JLabel(136                 "\u6CE8\uFF1A\u8F93\u5165\u8D77\u59CB\u65E5\u671F\u548C\u622A\u6B62\u65E5\u671F\u4EE5\u540E\uFF0C\u70B9\u51FB\u83B7\u53D6\u65E5\u671F\uFF0C\u7136\u540E\u70B9\u51FB\u5F00\u59CB");137         lblNewLabel_3.setForeground(Color.MAGENTA);138         lblNewLabel_3.setBounds(10, 187, 414, 23);139         frame.getContentPane().add(lblNewLabel_3);140 141         JLabel lblNewLabel_4 = new JLabel(142                 "\u5219\u5F00\u59CB\u6570\u636E\u7684\u5BFC\u5165\uFF0C\u5F00\u59CB\u6309\u94AE\u6062\u590D\u6B63\u5E38\uFF0C\u6216\u8005\u7A0B\u5E8F\u9000\u51FA\u5219\u5BFC\u5165\u7ED3\u675F");143         lblNewLabel_4.setForeground(Color.MAGENTA);144         lblNewLabel_4.setBounds(24, 208, 400, 21);145         frame.getContentPane().add(lblNewLabel_4);146 147         JLabel label = new JLabel("\u8BBE\u5907\u53F7\uFF1A");148         label.setBounds(203, 26, 54, 15);149         frame.getContentPane().add(label);150 151         textField_2 = new JTextField();152         textField_2.setText("100");153         textField_2.setBounds(272, 23, 66, 21);154         frame.getContentPane().add(textField_2);155         textField_2.setColumns(10);156 157         JLabel lbls = new JLabel("\u8BF7\u8010\u5FC3\u7B49\u5F855s");158         lbls.setForeground(Color.MAGENTA);159         lbls.setBackground(Color.BLUE);160         lbls.setBounds(267, 118, 93, 19);161         frame.getContentPane().add(lbls);162     }163 164     /**165      * 获取mysql连接166      * 167      * @return168      */169     public Connection getMysqlConn() {170         sDriver = "com.mysql.jdbc.Driver";171         String url = "jdbc:mysql://localhost:3306/vista";172         String user = "boctek";173         String passwd = "BCERA1688";174         try {175             Class.forName("com.mysql.jdbc.Driver");176             conn = DriverManager.getConnection(url, user, passwd);177         } catch (Exception e1) {178             e1.printStackTrace();179         }180         return conn;181     }182 183     /**184      * 关闭mysql连接185      */186     public void closeMysqlConn(Connection conn) {187         try {188             if (conn != null || !conn.isClosed()) {189                 conn.close();190             }191         } catch (SQLException e) {192             e.printStackTrace();193         }194 195     }196 197     // 批量插入数据198     public boolean batchInsertIntoMysql() {199         System.out.println("开始插入");200         List<ChildVista> initialize = datas;201         for (ChildVista cv : initialize) {202             insertToMysql(cv);203         }204         return false;205 206     }207 208     public void insertToMysql(ChildVista childVista) {209         conn = getMysqlConn();210         try {// 执行数据库查询，返回结果211             String sql = "insert into cur_rec values(?,?,?,?,?)";212             pst = conn.prepareStatement(sql);213             pst.setDate(1, childVista.getTmDate());214             pst.setInt(2, childVista.getStan());215             pst.setInt(3, childVista.getEquipNo());216             pst.setInt(4, childVista.getYcNo());217             pst.setString(5, childVista.getData().toString());218             pst.execute();219             pst.close();220         } catch (SQLException e) {221             System.out.println(e.getMessage());222         } finally {223             // 关闭数据库连接224             closeMysqlConn(conn);225         }226     }227 228     /*229      * // 批量插入数据 public boolean batchInsertIntoMysql() { List<ChildVista>230      * initialize = datas; try { insertToMysql(initialize); } catch231      * (ParseException e) { e.printStackTrace(); } return false;232      * 233      * }234      * 235      * public void insertToMysql(List<ChildVista> childVista) throws236      * ParseException { conn = getMysqlConn(); SimpleDateFormat sdf = new237      * SimpleDateFormat("yyyy-MM-dd"); java.util.Date startDate =238      * sdf.parse(startDateString); java.util.Date endDate =239      * sdf.parse(endDateString);240      * 241      * String deleteSql = "delete from cur_rec where tmdate >= '"+ new242      * Date(startDate.getTime()) +"' and tmdate <= '"+new243      * Date(endDate.getTime())+"'"; try { System.out.println(deleteSql); pst =244      * conn.prepareStatement(deleteSql); pst.executeUpdate(); } catch245      * (SQLException e1) { e1.printStackTrace(); }246      * 247      * String sql = "insert into cur_rec values(?,?,?,?,?)"; try {//248      * 执行数据库查询，返回结果 pst = conn.prepareStatement(sql); int count = 0; for (int i249      * = 0; i < childVista.size(); i++) { ChildVista child = childVista.get(i);250      * pst.setDate(1, child.getTmDate()); pst.setInt(2, child.getStan());251      * pst.setInt(3, child.getEquipNo()); pst.setInt(4, child.getYcNo());252      * pst.setString(5, child.getData().toString()); pst.addBatch(); }253      * pst.executeBatch(); } catch (Exception e) { e.printStackTrace(); }254      * finally { try{ //关闭步骤3所开启的state对象 pst.close();255      * System.out.println("关闭pst对象"); } catch(SQLException e){} try{ //关闭数据库连接256      * conn.close(); System.out.println("关闭sql数据库连接对象"); } catch(SQLException257      * e){258      * 259      * } } }260      */261     public boolean getDate() {262         boolean convertSuccess = true;263         startDateString = textField.getText();264         endDateString = textField_1.getText();265         String equipNoString = textField_2.getText();266         if (startDateString == null || startDateString.length() <= 0267                 || endDateString == null || endDateString.length() <= 0268                 || equipNoString == null || equipNoString.length() <= 0) {269             JOptionPane.showMessageDialog(null, "日期、设备号不能为空");270             return false;271         }272         SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");273         format.setLenient(false);274         try {275             format.parse(startDateString);276             format.parse(endDateString);277             equipNo = Integer.parseInt(equipNoString);278         } catch (ParseException e) {279             // TODO Auto-generated catch block280             convertSuccess = false;281             e.printStackTrace();282         }283 284         return convertSuccess;285     }286 287     public void ConnODBC() {288 289         // 步骤1：加载驱动程序290         sDriver = "com.hxtt.sql.access.AccessDriver";291         try {292             Class.forName(sDriver);293         } catch (Exception e) {294             System.out.println("无法加载驱动程序");295             e.printStackTrace();296 297         }298         System.out.println("步骤1：加载驱动程序--成功！");299         conn = null;300         stmt = null;301         String sCon = "jdbc:access:///ChildVista.mdb";302 303         try {304             conn = DriverManager.getConnection(sCon);305             if (conn != null) {306                 System.out.println("步骤2：连接数据库--成功！");307             }308             // 步骤3：建立JDBC的Statement对象309             stmt = conn.createStatement();310         } catch (SQLException e) {311             System.out.println("连接错误：" + sCon);312             System.out.println(e.getMessage());313 314         }315     }316 317     public void getDataFromAccess() {318         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");319 320         try {// 执行数据库查询，返回结果321             java.util.Date startDate = sdf.parse(startDateString);322             java.util.Date endDate = sdf.parse(endDateString);323             // where tmdate between #"+new Date(startDate.getTime())+"# and324             // #"+new Date(endDate.getTime())+"#"325             326             //Sql语句  获取规定时间段内的数据327             String sSQL = "SELECT count (*) FROM cur_rec where tmdate >= ? and tmdate <= ?";328             pst = conn.prepareStatement(sSQL);329             pst.setDate(1, new Date(startDate.getTime()));330             pst.setDate(2, new Date(endDate.getTime()));331             System.out.println(sSQL);332             rs = pst.executeQuery();333             ChildVista childVista;334             Date date;335             int count = 0;336             if (rs.next()) {337                 count = rs.getInt(1);338             }339             //获取分页340             int page=count/PAGE_SIZE;341             page = page +( count % PAGE_SIZE > 0 ? 1 : 0);342             System.out.println("page"+page);343             for (int i = 0; i < page; i++) {344                 int s = count - i * PAGE_SIZE;345                 int pageSize = s > PAGE_SIZE ? PAGE_SIZE : s;346                 //Access分页获取规定时间段内的数据347                 String sql = "SELECT TOP "+pageSize+" * FROM( SELECT * FROM (SELECT TOP "+PAGE_SIZE*(i+1)+" * FROM cur_rec where tmdate >= ? and tmdate <= ? ORDER BY tmdate DESC,STA_N DESC,EQUIP_NO DESC,YC_NO DESC) ORDER BY tmdate,STA_N,EQUIP_NO,YC_NO)";348                 //String sql = "SELECT * FROM cur_rec where tmdate >= ? and tmdate <= ?";349                 pst2  = conn.prepareStatement(sql);350                 pst2.setDate(1, new Date(startDate.getTime()));351                 pst2.setDate(2, new Date(endDate.getTime()));352                 rst =pst2.executeQuery();353                 while (rst.next()) {354                     date = rst.getDate("tmdate");355                     childVista = new ChildVista(date, rst.getInt("STA_N"),rst.getInt("EQUIP_NO") + equipNo,rst.getInt("YC_NO"), rst.getBytes("data"));356                     datas.add(childVista);357                 }358                 System.out.println("第"+(i+1)+"次插入数据。。。");359             }360             System.out.println(count + "条");361         } catch (SQLException e) {362             e.printStackTrace();363         } catch (ParseException e1) {364             e1.printStackTrace();365         } finally {366             try {367                 // 关闭步骤3所开启的state对象368                 rst.close();369                 pst2.close();370                 rs.close();371                 pst.close();372                 System.out.println("关闭statement对象");373             } catch (SQLException e) {374             }375             try {376                 // 关闭数据库连接377                 stmt.close();378                 conn.close();379                 System.out.println("关闭数据库连接对象");380             } catch (SQLException e) {381 382             }383         }384     }385 }

ChildVista.java

 1 /* 2  * ChildVista 模型 3  */ 4 public class ChildVista { 5     public Date tmDate; 6     public int stan; 7     public int equipNo; 8     public int ycNo; 9     public byte[] data;10 11     public ChildVista() {12         super();13     }14 15     public ChildVista(Date tmDate, int stan, int equipNo, int ycNo, byte[] data) {16         super();17         this.tmDate = tmDate;18         this.stan = stan;19         this.equipNo = equipNo;20         this.ycNo = ycNo;21         this.data = data;22     }23 24     public Date getTmDate() {25         return tmDate;26     }27 28     public void setTmDate(Date tmDate) {29         this.tmDate = tmDate;30     }31 32     public int getStan() {33         return stan;34     }35 36     public void setStan(int stan) {37         this.stan = stan;38     }39 40     public int getEquipNo() {41         return equipNo;42     }43 44     public void setEquipNo(int equipNo) {45         this.equipNo = equipNo;46     }47 48     public int getYcNo() {49         return ycNo;50     }51 52     public void setYcNo(int ycNo) {53         this.ycNo = ycNo;54     }55 56     public byte[] getData() {57         return data;58     }59 60     public void setData(byte[] data) {61         this.data = data;62     }63 64 }

以上即完成了Access往MySql里的数据插入。

二、java打包生成.exe(参考ice world的博客)

前言：

我们都知道Java可以将二进制程序打包成可执行jar文件，双击这个jar和双击exe效果是一样一样的，但感觉还是不同。其实将java程序打包成exe也需要这个可执行jar文件。

准备：

第一步：

eclipse、exe4j

选择Java文件夹下的Runnable JAR file，点击Next，

点击finish即可。完成了AccessToMySql.jar的导出

第二步：

将项目打包成exe，这里要明确一点，并不是把所有的文件都打包成一个exe，资源文件是不能包进去的，往下看↓

首先，在任意目录创建一个文件夹，最好命名和项目名相同，我在D盘创建了一个“AccessToMySql”文件夹，之后将所有的资源文件以及我们生成的可执行jar文件(我的AccessToMySql.jar)都拷贝到这个文件夹里，lib目录，如果你怕执行该程序的机器上没安装jre，那么你需要将自己机器上的jre目录也拷贝进来，我安装的jre1.8.0_25，最后的目录结构：