[access][jdbc]java连接access数据库

我就是我 2022-09-17 11:20 233阅读 0赞

package db;
    
    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.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    public class DbAccessTest {
    	public final static String COLUMN_ATTR_NAME = "COLUMN_ATTR_NAME";
    	public final static String COLUMN_ATTR_TYPENAME = "COLUMN_ATTR_TYPENAME";
    	public final static String COLUMN_ATTR_LABEL = "COLUMN_ATTR_LABEL";
    	// {}之内的空格必须保留,jdbc:odbc:driver不区分大小写,=右边允许空格
    	public final static String DB_URL_PREX1 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=";
    	public final static String DB_URL_PREX2 = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=";
    	public final static String DB_URL_PREX3 = "jdbc:odbc:driver={Driver do Microsoft Access (*.mdb)};DBQ=";
    	public final static String DB_URL_FILE = "D:\\docs\\MyDocs\\icloud.mdb";
    	public final static String DB_URL = DB_URL_PREX1 + DB_URL_FILE;
    	public final static String DB_USER = "";
    	public final static String DB_PASSWORD = "";
    
    	static {
    		try {
    			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		}
    	}
    
    	private static Connection getConnection() throws SQLException {
    		Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
    		return conn;
    	}
    
    	public static List<Map<String, Object>> queryForList(String sql) throws SQLException {
    		Connection conn = getConnection();
    		List<Map<String, Object>> list = queryForList(sql, conn);
    		conn.close();
    		return list;
    	}
    
    	public static List<Map<String, Object>> queryForList(String sql, Connection conn) throws SQLException {
    		Statement stmt = conn.createStatement();
    		ResultSet rs = stmt.executeQuery(sql);
    
    		// 获取字段信息,名称、类型
    		ResultSetMetaData rsmd = rs.getMetaData();
    		List<Map<String, String>> metacols = new ArrayList<Map<String, String>>();
    		for (int i = 1; i <= rsmd.getColumnCount(); i++) {
    			Map<String, String> metacol = new HashMap<String, String>();
    			metacol.put(COLUMN_ATTR_NAME, rsmd.getColumnName(i));
    			metacol.put(COLUMN_ATTR_TYPENAME, rsmd.getColumnTypeName(i));
    			metacol.put(COLUMN_ATTR_LABEL, rsmd.getColumnLabel(i));
    			metacols.add(metacol);
    		}
    		System.out.println(metacols);
    
    		// 获取数据,封装为List<Map>型数据
    		List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
    		while (rs.next()) {
    			Map<String, Object> row = new HashMap<String, Object>();
    			for (Map<String, String> metacol : metacols) {
    				String colname = metacol.get(COLUMN_ATTR_NAME);
    				row.put(colname, rs.getObject(colname));
    			}
    			rows.add(row);
    		}
    		System.out.println(rows);
    		rs.close();
    		stmt.close();
    		return rows;
    	}
    
    	public static int update(String sql) throws SQLException {
    		Connection conn = getConnection();
    		conn.setAutoCommit(false);
    		Statement stmt = conn.createStatement();
    		int ret = stmt.executeUpdate(sql);
    		conn.commit();
    		System.out.println("更新记录数:" + ret);
    		return ret;
    	}
    
    	public static int insert(String sql) throws SQLException {
    		return update(sql);
    	}
    
    	public static int delete(String sql) throws SQLException {
    		return update(sql);
    	}
    
    	public static void main(String[] args) throws Exception {
    		System.out.println(queryForList("select count(*) from 联系人"));
    		delete("delete from 联系人");
    		System.out.println(queryForList("select count(*) from 联系人"));
    		insert("insert into 联系人(名)  values('名1')");
    		insert("insert into 联系人(名)  values('名2')");
    		insert("insert into 联系人(名)  values('名3')");
    		System.out.println(queryForList("select count(*) from 联系人"));
    	}
    }