jdk1.8 导出mdb格式的文件 使用的是ucanaccess

jdk1.8 导出mdb格式的文件 这里使用的是ucanaccess

在这里我使用的maven项目

导包 其中有几个包可能是没有用的 需要你下去自己删除掉测试
*

net.sf.ucanaccess ucanaccess 4.0.4 org.glassfish.jdbc.jdbc-ra.jdbc30 jdbc30 10.0-b28 provided org.glassfish.main.jdbc.jdbc-ra.jdbc30 jdbc30 5.0 org.glassfish.main.jdbc.jdbc-ra jdbc30 3.1.2.2

先从数据库导出有个mdb格式的文件，放在resources下（作为服务上的mdb文件）

编写Access工具类 从resources下的mdb文件中读取数据，到要下载生成的文件路径和文件名

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Types;

import com.healthmarketscience.jackcess.ColumnBuilder;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.Row;
import com.healthmarketscience.jackcess.Table;
import com.healthmarketscience.jackcess.TableBuilder;
public class Access {
public static void main(String[] args) throws IOException {
//getAccessDataTable();
Access access= new Access();
access.createTable();
}
public static void getAccessDataTable() throws IOException{
Table table = DatabaseBuilder.open(new File(“D:/works/live/src/main/resources/test.mdb”)).getTable(“表名”);
for(Row row : table) {
System.out.println("–ID–" + row.get(“字段名”)+"–Name–" + row.get(“字段名”));
}
}
/**
* 创建表并写入数据
* @throws IOException
*/
public void createTable() throws IOException{
Database db = DatabaseBuilder.create(Database.FileFormat.V2000, new File(“C:/Users/啊/Documents/new.mdb”));
Table table = DatabaseBuilder.open(new File(“D:/works/live/src/main/resources/test.mdb”)).getTable(“表名”);
Table newTable;
try {
newTable = new TableBuilder(“表名”)
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.CHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.BIGINT))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.BIGINT))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.BIGINT))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.DATE))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.DATE))
.addColumn(new ColumnBuilder(“字段名”)
.setSQLType(Types.NVARCHAR))
.toTable(db);
for(Row row : table) {
newTable.addRow(row.get(“字段名”), row.get(“字段名”), row.get(“字段名”), row.get(“字段名”), row.get(“字段名”),
row.get(“字段名”), row.get(“字段名”), row.get(“字段名”), row.get(“字段名”), row.get(“字段名”), row.get(“字段名”), row.get(“字段名”)
, row.get(“字段名”), row.get(“VideoAddress”), row.get(“字段名”), row.get(“字段名”), row.get(“字段名”), row.get(“字段名”), row.get(“字段名”));
}

    } catch (SQLException e) {  
        e.printStackTrace();  
    }  
}  

}

编写一个Test1类，每次导出时数据库的数据都先查询并添加到resources下的mdb文件中

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test1 {
public static final String sqll=“select * from student”;
public static void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException {
Connection conn = null;
Statement stmt;
ResultSet rs;
String url = “jdbc:sqlserver://连接数据库;”;
String sql = “select * from student”;
try {
conn = DriverManager.getConnection(url, “用户名”, “密码”);
stmt = conn.createStatement();
rs = stmt.executeQuery(sqll);
Class.forName(“net.ucanaccess.jdbc.UcanaccessDriver”).newInstance();
Connection connn = DriverManager.getConnection(“jdbc:ucanaccess://D:/works/live/src/main/resources/test.mdb”,“用户名”,“密码”);
Statement stmtt = connn.createStatement();
stmtt.execute(“delete * from student”);
while(rs.next()){
stmtt.execute(“insert into student(字段名,字段名,字段名,字段名,字段名,字段名,字段名,字段名,字段名,字段名,字段名,字段名,字段名,字段名,字段名,字段名) values(”+rs.getString(1)+",’"+rs.getString(2)+"’,’"+rs.getString(3)+"’,’"+rs.getString(4)+"’,’"+rs.getString(5)+"’,’"+rs.getString(6)+"’,’"+rs.getString(7)+"’,’"+rs.getString(8)+"’,’"+rs.getString(9)+"’,’"+rs.getString(10)+"’,1,1,1,‘1’,‘1’,‘2018-12-1 00:00:00’)");
}
} catch (SQLException e1) {
e1.printStackTrace();
}
Access access = new Access();
try {
access.createTable();
} catch (IOException e) {
e.printStackTrace();
}
}
}