Java ODBC 连接 MS Access 和 MS Excel

Java ODBC 连接 MS Access 和 MS Excel ：

1，ACCESS ODBC 连接url，如：
 不采用dsn："jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=e:/mydb201411.mdb"; //这种方式不需要配置ODBC DSN，需要写明物理路径
 需要配置系统dsn： "jdbc:odbc:mydb201411"

2，EXCEL ODBC 连接url，如：
 不采用dsn：
  查询： "jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};DBQ=e:/myexceldb201411.xls"; //这种方式不需要配置ODBC DSN，需要写明物理路径
  更新： "jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};DBQ=e:/myexceldb201411.xls;ReadOnly=0"
 需要配置系统dsn： "jdbc:odbc:myexceldb201411"

3，查询EXCEL ，表名用"[]"包裹sheet名，如：
 "select * from [logsheet2$] "
4，访问均支持 PreparedStatement

5，数据插入Access表，不支持一语句多条记录的方式，比如："insert into xxx values ('',''),('','')"

 

--------------------------------------------------------------------------------------------------------------------------------------------

 

 public static void logquery_Access(){

  Connection con = null;
  ResultSet rs = null;  
  Statement st = null;
  try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=e:/mydb201411.mdb"; //这种方式不需要配置ODBC DSN
   //String url = "jdbc:odbc:mydb201411";   //这种url也能访问,但是，需要配置 ODBC DSN
   con = DriverManager.getConnection(url);
   String sql = "select * from matterlogs ";   
   st = con.createStatement();
   rs = st.executeQuery(sql);
   while (rs.next()) {
    String s = "";
    for (int i = 1; i < 6; i++) {
     s+= rs.getString(i)+",";
    }
    System.out.println(s);    
   }    
   
  } catch (ClassNotFoundException e) {   
   e.printStackTrace();
  } catch (SQLException e) {   
   e.printStackTrace();
  } finally{   
   try {
    if( rs!=null )   rs.close();
   } catch (SQLException e) {    
    e.printStackTrace();
   }
   try {
    if( st!=null ) st.close();
   } catch (SQLException e) {    
    e.printStackTrace();
   }
   try {
    if( !con.isClosed() ) con.close();
   } catch (SQLException e) {    
    e.printStackTrace();
   }   
  }  
 }
 
 public static int loginsert_Access(){
  int res = 0 ;
  Connection con = null;  
  Statement st = null;
  try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=e:/mydb201411.mdb";//这种方式不需要配置ODBC DSN
   //String url = "jdbc:odbc:mydb201411";  //这种url也能访问,但是，需要配置 ODBC DSN
   con = DriverManager.getConnection(url);
   String sql = "insert into matterlogs (id,htime,host,matters) values('14','2014-02-03 12:34:56','OA','geton') ;";   
   st = con.createStatement();
   res = st.executeUpdate(sql);      
   
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally{   
   try {
    if( st!=null ) st.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   try {
    if( !con.isClosed() ) con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }   
  } 
  return res; 
 }
 
 public static void logsinsert_Access(){
  
  Connection con = null;  
  //Statement st = null;
  PreparedStatement ps = null;
  try {
   
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=e:/mydb201411.mdb";//这种方式不需要配置ODBC DSN
   //String url = "jdbc:odbc:mydb201411";  //这种url也能访问,但是，需要配置 ODBC DSN
   con = DriverManager.getConnection(url);
   String sql = "insert into matterlogs (id,htime,host,matters) values(?,?,?,?) ;"; 
   con.setAutoCommit(false);
   ps = con.prepareStatement(sql);
   for (int i = 0; i < 5; i++) {
    ps.setString(1, "15");
    ps.setString(3, "SP");
    ps.setString(2, DateFormat.getDateInstance().format(new Date(System.currentTimeMillis())));
    ps.setString(4, "get"+String.valueOf(i));
    ps.addBatch();
   }
   ps.executeBatch();
   con.commit();   
         
   
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally{   
   try {
    if( ps!=null ) ps.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   try {
    if( !con.isClosed() ) con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }   
  } 
  
 }
 
 public static void logquery_Excel(){

  Connection con = null;
  ResultSet rs = null;  
  Statement st = null;
  try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   String url = "jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};DBQ=e:/myexceldb201411.xls"; //这种方式不需要配置ODBC DSN
   //String url = "jdbc:odbc:myexceldb201411";   //这种url也能访问,但是，需要配置 ODBC DSN
   con = DriverManager.getConnection(url);
   String sql = "select * from [logsheet2$] where host='admin' ";   
   st = con.createStatement();
   rs = st.executeQuery(sql);
   while (rs.next()) {
    String s = "";
    s+= rs.getString(1)+",";
    s+= rs.getString(2)+",";
    s+= rs.getString(3)+",";
    s+= rs.getString(4)+",";    
    System.out.println(s);    
   }    
   
  } catch (ClassNotFoundException e) {   
   e.printStackTrace();
  } catch (SQLException e) {   
   e.printStackTrace();
  } finally{   
   try {
    if( rs!=null )   rs.close();
   } catch (SQLException e) {    
    e.printStackTrace();
   }
   try {
    if( st!=null ) st.close();
   } catch (SQLException e) {    
    e.printStackTrace();
   }
   try {
    if( !con.isClosed() ) con.close();
   } catch (SQLException e) {    
    e.printStackTrace();
   }   
  }  
 
 }
 
 public static void logsinsert_Excel(){
  
  Connection con = null;   
  Statement st = null;
  try {
   
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   String url = "jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};DBQ=e:/myexceldb201411.xls;ReadOnly=0";//这种方式不需要配置ODBC DSN   
   con = DriverManager.getConnection(url);
   String sql = "insert into [logsheet2$] (id,htime,host,matters) values('22','2014-12-12 01:02:03','admin','logoff') ;"; 
   st = con.createStatement();
   st.executeUpdate(sql);         
   
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  } finally{   
   try {
    if( st!=null ) st.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   try {
    if( !con.isClosed() ) con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }   
  } 
  
 }