Access数据库创建及相关查询
//首先需要创建empty.mdb文件放在src目录下!作为要创建access数据库文件的模版,切记,一定....
//把数据库的相关操作封装在database.DAO类中

package database;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DAO {
private final String dbpath;//数据库路径
private Connection conn;
private PreparedStatement ps;
static{
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public DAO(String dbpath){
this.dbpath=dbpath;
}
/**
* 查询
*/
public ResultSet executeQuery(String sql){
try {
init(sql);
return ps.executeQuery();
}catch(SQLException e) {
//e.printStackTrace();
}
return null;
}
/**
* 增删改
*/
public int executeUpdate(String sql){
try {
init(sql);
return ps.executeUpdate();
}catch(SQLException e) {
//e.printStackTrace();
}finally{
close(null);
}
return 0;
}
/**
* 创建数据库对象
*/
public void createDB(){
try {
//把模版文件empty.mdb复制到准备创建access文件的路径
FileInputStream input=new FileInputStream(System.getProperty("user.dir")+"/src/empty.mdb");
FileOutputStream output=new FileOutputStream(dbpath);
byte[] b=new byte[128];
while(input.read(b)!=-1)
output.write(b);
output.close();
input.close();
}catch (Exception e){
e.printStackTrace();
}
}
private void init(String sql) throws SQLException{
conn=DriverManager.getConnection("jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ="+dbpath);
ps=conn.prepareStatement(sql);
}
/**
* 释放资源
*/
public void close(ResultSet rs){
try {
if(rs!=null){
rs.close();
rs=null;
}
if(ps!=null){
ps.close();
ps=null;
}
if(conn!=null){
if(!conn.isClosed())
conn.close();
conn=null;
}
}catch(Exception e){
e.printStackTrace();
}

}
public String getDbpath() {
return dbpath;
}
}


//新建个测试类database.AccessDemo
package database;
import java.sql.ResultSet;
public class AccessDemo {
public static void main(String[] args){
try {
//准备创建access文件的路径
String dbpath="d:\\项目临时存放目录\\stu.mdb";
DAO dao=new DAO(dbpath);
dao.createDB();
// try {
// dao.executeUpdate("drop table stu");
// }catch(Exception e){}
//建表
dao.executeUpdate("create table stu(name varchar(20) primary key,age int not null,sex char(2) not null)");
//增
dao.executeUpdate("insert into stu(name,age,sex)values('first',20,'女')");
dao.executeUpdate("insert into stu(name,age,sex)values('second',24,'男')");
dao.executeUpdate("insert into stu(name,age,sex)values('third',22,'女')");
dao.executeUpdate("insert into stu(name,age,sex)values('fourth',25,'女')");
dao.executeUpdate("insert into stu(name,age,sex)values('fifth',22,'男')");
dao.executeUpdate("insert into stu(name,age,sex)values('fifth',30,'女')");
//删
dao.executeUpdate("delete from stu where age=(select max(age) from stu)");
//改
dao.executeUpdate("update stu set sex='女' where name like '%h%'");
//查
ResultSet rs=dao.executeQuery("select * from stu order by age asc");
while(rs.next()){
System.out.print("name:"+rs.getObject("name")+"\t");
System.out.print("sex:"+rs.getObject("sex")+"\t");
System.out.println("age:"+