本文共 4891 字,大约阅读时间需要 16 分钟。
java如何操作access数据库呢?请看下面的例子:[不知道为什么,插入代码时报错,所以就直接贴出来]
package com.ria.utils.common;
import java.sql.Connection;
import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;public class MsAccessDBUtils {
private static MsAccessDBUtils instance = null; public static MsAccessDBUtils getInstance() { if (instance == null) { instance = new MsAccessDBUtils(); } return instance; }private MsAccessDBUtils() {}
//最先被调用 mdbFile=d:/xxx.mdb
public void loadConfig(String mdbFile,String user,String psw) throws Exception{ mdb_file = mdbFile; user = user; pwd = psw; loadDriver(); } public void loadConfig(String mdbFile) throws Exception{ mdb_file = mdbFile; loadDriver(); } private static String dirverClass = "sun.jdbc.odbc.JdbcOdbcDriver"; //jdbc:odbc:Driver={MicroSoft Access Driver *.mdb)};DBQ = Northwind.mdb //jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=C://test.mdb private static String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ="; private static String mdb_file = null;//access 文件[这里要绝对路径] private static String user = ""; private static String pwd = ""; private static Connection conn; private static Statement stmt; private static ResultSet rs;private void loadDriver() throws Exception{
try { Class.forName(dirverClass); } catch (Exception e) { throw e; } }//创建不可滚动的连接
public static void connect() throws Exception{ try { System.out.println(url+mdb_file); System.out.println(user); System.out.println(pwd); conn = DriverManager.getConnection(url+mdb_file, user, pwd); stmt = conn.createStatement(); } catch (Exception e) { throw e; } } public static void connect(boolean autocommit) throws Exception{ try { conn = DriverManager.getConnection(url+mdb_file, user, pwd); conn.setAutoCommit(autocommit); stmt = conn.createStatement(); } catch (Exception e) { throw e; } } //创建可以滚动的连接 public static void connect2() throws Exception{ try { conn = DriverManager.getConnection(url+mdb_file, user, pwd); stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); } catch (Exception e) { throw e; } }//关闭连接
public static void close() throws Exception{ try { if (rs != null) { rs.close(); rs=null; } if (stmt != null) { stmt.close(); stmt=null; } if (conn != null) { conn.close(); conn=null; } } catch (Exception e) { throw e; } }//查询语句
public static List executeQuery(String sql) throws Exception{ List l = new ArrayList(); try { if (stmt == null) { connect(); } rs = stmt.executeQuery(sql); l = orgResultSet4List(rs); //close(); } catch (Exception e) { throw e; } return l; }public static int executeUpdate(String sql) throws Exception{
try { if (stmt == null) { connect(); } int res = stmt.executeUpdate(sql); //close(); return res; } catch (Exception e) { throw e; } } public static int[] executeUpdate(String[] sql) throws Exception{ try { if (stmt == null) { connect(false); } for (int i = 0; i < sql.length; i++) { stmt.addBatch(sql[i]); } int[] res = stmt.executeBatch(); conn.commit(); //close(); return res; } catch (Exception e) { throw e; } } private static List orgResultSet4List(ResultSet rs)throws Exception{ try { ResultSetMetaData rsmd = rs.getMetaData(); int cols = rsmd.getColumnCount(); List l = new ArrayList(); Map recordMap = null; while (rs.next()) { recordMap = new HashMap(); for (int i = 0; i < cols; i++) { recordMap.put((String)(rsmd.getColumnName(i+1)).toLowerCase(),rs.getObject(i+1)); } l.add(recordMap); } return l; } catch (Exception ex) { //ex.printStackTrace(); throw ex; } }public static void main(String[] args) {
if (args!=null && args.length>0) { System.out.println("args[0]="+args[0]); try { MsAccessDBUtils.getInstance().loadConfig(args[0],"",""); } catch (Exception ex) { ex.printStackTrace(); System.exit(-1); } MsAccessDBUtils.getInstance().go(); }else{ System.exit(0); } } void go(){ try{ for (int k = 0; k < 3; k++) { String sql = "insert into leave_words(cmp_name,tell,web_url,addr,mail,conts,notes)values"; sql += "('公司名称2','0100003333','http://sdfa.com','北京大sdf砍刀饭卡第三','aa@aa.com','做得很好','[无]')"; int res = MsAccessDBUtils.getInstance().executeUpdate(sql); System.out.println("insert=" + res); sql = "select * from leave_words"; List l = MsAccessDBUtils.getInstance().executeQuery(sql); MsAccessDBUtils.getInstance().close(); if (l != null) { System.out.println("==" + (l.get(0)).toString()); java.text.SimpleDateFormat df = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss W E"); Map rowMap; for (int i = 0; i < l.size(); i++) { rowMap = (Map) l.get(i); java.util.Date crt_time = new java.util.Date( ( (java.sql.Timestamp) rowMap.get( "crt_time")).getTime()); System.out.println(df.format(crt_time)); }// java.sql.Date d = new java.sql.Date();
}
}
}catch(Exception ex){
ex.printStackTrace(); System.exit(-1); } }}转载地址:http://fbfmb.baihongyu.com/