1

i have a problem,one of my client need

a program that will connect to oracle database server,and download/store the table to a local file(ex; microsoft access) like a conversion after that client can input an excel file and it will be added to that local file

my client want the local file/database can be stored offline,inside program folder They want the program do all the job so they only give me a user&pass to their database with some example of the tables and the excel that will be inputed from the client

and the program will do the rest download the tabel,convert,input,and the last it will print the data

i know that many tutorials come with the idea to connect / update oracle with java (with OLDBC-JDBC) or open&modify msaccess file(with excel conversion)

but to combine them all?how can you do this in java?

I already saw someone do this,but in VB6 and his code is a bit messy

FYI: the data is a BIG one,its use about 600.000 rows and it is updated once per month

Improve this question

2 Answers 2

Reset to default
1

If only the data is needed, why not download it as excel which can be rendered pretty easily using Apache POI ? Even excel can be served as datasource (just not MS-Access). Is your requirement only to convert it to MS-Access ? Please add more information about your problem statement.

Improve this answer
4
  • The client want the program do all the job they only give me a user&pass to their database and the program will do the rest download the tabel,convert,input,and the last it will print the data
    – Adamanusia
    Commented Sep 15, 2012 at 10:46
  • @user115343 Well what I mean is, whether client wants it as only MS-Access ? or an Excel is acceptable as well ?
    – Chris
    Commented Sep 15, 2012 at 10:47
  • acceptable like the title said they only want,whatever it is(file) it can modifiable and can transfer to removable media,like flashdisk msaccess only an aproach from other people(VB6)
    – Adamanusia
    Commented Sep 15, 2012 at 10:49
  • but how do you download oracle database and save as excel from JAVA? thank you
    – Adamanusia
    Commented Sep 15, 2012 at 10:51
0

An approach you might want to explore if the desired output is MS Access is Jackcess. One of the examples they give on their webpage is copying an external table into MS Access, and this is all the code you seem to need:

Database.open(new File("my.mdb")).copyTable("Imported", resultSet);

I don't have first-hand experience with that library, but it looks quite nice.

For Excel output I agree with Chris, POi is the way to go. However, there's also a library called jXLS, it's a layer on top of POI and it simplifies the creation of formatted XLS sheets a lot, check out this sample that converts a JDBC resultset (the result of you querying Oracle) into a nicely looking Excel file.

EDIT: based on your comment on Chris' answer, if actually all you get is the db coordinates and user and pass, you'll have to:

Step 1: get the table names using DatabaseMetaData, schematically like this:

Connection c = DriverManager.getConnection (...);
DatabaseMetaData md = c.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
  System.out.println(rs.getString(3));
}

Step 2: iterate over the obtained list of tables and perform the table copy as shown above

Improve this answer
11
  • omg,i am really hopeless a minute ago, but like my question to chris,how can you download oracle tables right into excel (.xls) file? thank you
    – Adamanusia
    Commented Sep 15, 2012 at 10:52
  • @user115343 I am very confused now, do you want MDB or XLS or both? And check my edit, now it also shows how to extract db metadata to get a list of tables - you don't want to hardcode the list of tables to be copied in your program.
    – fvu
    Commented Sep 15, 2012 at 10:56
  • so its like i perform a table copy from oracle to java datasource and it will convert to excel? FYI the data is big,its about thousand and the VB6 guys,that i mentioned above fail in this one sorry for my late reply,its caused by slow connection i only need 1 file,whether it is MDB or XLS as long as it can store the data
    – Adamanusia
    Commented Sep 15, 2012 at 10:58
  • Thousand records? Or thousand tables? If it's 1000 tables Excel is probably not the right receptacle - POI gets quite slow when you have 10000's of rows but it's quite fast with 100's or 1000's of rows.
    – fvu
    Commented Sep 15, 2012 at 11:01
  • From first-hand experience I can assure you that 1000 rows will do just fine - And don't forget to check out jXLS, what you need to do here is super simple with that library - assuming you know the table structure(s). If not you'll have to use POI directly.
    – fvu
    Commented Sep 15, 2012 at 11:07

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.