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();
}
}

}