数据采集--连接Access数据库

1、*.properties文件

driverClassName=sun.jdbc.odbc.JdbcOdbcDriver
url=jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=D\:\\WorkSpace\\fcs\\database\\Local.mdb
username=
password=

 2、将这些属性注入spring容器

 

<context:property-placeholder location="/WEB-INF/jdbc.properties" />
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" p:driverClassName="${jdbc.driverClassName}"
		p:url="${jdbc.url}" p:username="${jdbc.username}" p:password="${jdbc.password}" />

 

 

3、数据源class

public class DataAcquireUtils {
	
	/**
	 * 建立数据源
	 * @return DataSource
	 */
	public static DataSource createDataSource(AcquireDBConfig dbConfig) {
		
		final String driver = dbConfig.getDriverClassName();
		final String url = dbConfig.getUrl();
		final String username = dbConfig.getUsername();
		final String password = dbConfig.getPassword();

		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		}
		DataSource ds = new DataSource() {
			String _url = url;
			String _username = username;
			String _password = password;

			@Override
			public Connection getConnection() throws SQLException {
				return DriverManager.getConnection(_url, _username, _password);
			}

			@Override
			public Connection getConnection(String username, String password)
					throws SQLException {
				throw new UnsupportedOperationException();
			}

			@Override
			public int getLoginTimeout() throws SQLException {
				return 0;
			}

			@Override
			public PrintWriter getLogWriter() throws SQLException {
				throw new UnsupportedOperationException();
			}

			@Override
			public void setLoginTimeout(int seconds) throws SQLException {
				throw new UnsupportedOperationException();
			}

			@Override
			public void setLogWriter(PrintWriter out) throws SQLException {
				throw new UnsupportedOperationException();
			}

			@Override
			public boolean isWrapperFor(Class<?> iface) throws SQLException {
				throw new UnsupportedOperationException();
			}

			@Override
			public <T> T unwrap(Class<T> iface) throws SQLException {
				throw new UnsupportedOperationException();
			}

		};
		return ds;
	}

	/**
	 * 关闭数据库连接
	 * @param con
	 */
	public static void closeConnection(Connection con) {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException ex) {
			} catch (Throwable ex) {
			}
		}
	}

	/**
	 * Close the given JDBC Statement and ignore any thrown exception. This is
	 * useful for typical finally blocks in manual JDBC code.
	 * 
	 * @param stmt
	 *            the JDBC Statement to close (may be <code>null</code>)
	 */
	public static void closeStatement(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException ex) {
			} catch (Throwable ex) {
			}
		}
	}

	/**
	 * Close the given JDBC ResultSet and ignore any thrown exception. This is
	 * useful for typical finally blocks in manual JDBC code.
	 * 
	 * @param rs
	 *            the JDBC ResultSet to close (may be <code>null</code>)
	 */
	public static void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException ex) {
			} catch (Throwable ex) {
			}
		}
	}
	
	
	public static boolean testdb(AcquireDBConfig dbConfig) {
		boolean b = false;
		DataSource ds = createDataSource(dbConfig);
		try {
			Connection c = ds.getConnection();
			c.close();
			b = true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return b;
	}

}

 

3、查询数据库，其中用到2个以上的left join 如下

select r.*,f.Name as FoodName,c.ItemDes,u.Name as CheckerName from (((tResult r left join tFoodClass f on r.FoodCode=f.SysCode) left join tCheckItem c on r.CheckTotalItem=c.SysCode) left join tUserInfo u on r.Checker = u.UserCode) where 1=1

 写法与其它数据库有些区别。

 

 4、当Access数据库表及字段都是用中文表示时，需要用方括号将它括起来

select max(CLng([编号])) from [边口肉磅码单] where 1=1 and [日期] >? and [日期]< ?