问题1:ODBC连接数据库,取多张表的相同字段一同循环输出在同一个TXT文件中,如下面的代码,问循环怎么写??
问题2:如何在不同的数据库中执行问题1的操作,例如:study连接的是192.169.1.01中的表1、表2,要是想和192.169.1.02中的表3表4一起IO输出到同一个txt文件中怎么设计最合适??取得字段相同。
import java.sql.*;
import java.util.*;
import java.io.*;public class test extends ClassObj{
public static void main(String[] args) throws Exception {
try {
ArrayList<ClassObj> lst = test.getResultLst();
String contents = test.formatLst(lst);
String fileName = "C:\\result.txt";
test.saveFile(fileName, contents);
} catch (Exception e) {
e.printStackTrace();
}
}
static ArrayList<ClassObj> getResultLst() throws Exception {
String url = "jdbc:odbc:study";
String user = "sa";
String password = "123";
ArrayList <ClassObj> lst = new ArrayList <ClassObj>();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select 业务 as A,日期 as B,员工编号 as C from 表1";
String sql2 = "select 业务 as D,日期 as E,员工编号 as F from 表2";
String sql3 = "select 业务 as G,日期 as H,员工编号 as I from 表3";
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection(url,user,password);
pstmt = con.prepareStatement(sql);
pstmt2 = con.prepareStatement(sql2);
pstmt3 = con.prepareStatement(sql3);
rs = pstmt.executeQuery();
rs2 = pstmt2.executeQuery();
rs3 = pstmt3.executeQuery();
while(){ //请问循环应当怎么写??
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(rs!= null)
rs.close();
if(pstmt != null)
pstmt.close();
if(con != null)
con.close();
}catch(Exception e){}
}
}
static void saveFile(String fileName, String contents)
throws IOException {
File f = new File(fileName); if (f.exists()){
throw new IOException("file exists...");
}
if (!f.createNewFile()) {
throw new IOException("file create failure...");
} try {
BufferedWriter output = new BufferedWriter(new FileWriter(f));
output.write(contents);
output.close();
} catch (Exception e) {
throw new IOException(e);
}
} //将lst转为contents
static String formatLst(ArrayList <ClassObj> lst){
String contents = "";
for(int i=0;i <lst.size();i++){
contents +=lst.get(i).getClassId+"\t"+lst.get(i).getClassGroup+"\t"+lst.get(i).getClassNo+"\n";
}
return contents;
}
}
}
问题2:如何在不同的数据库中执行问题1的操作,例如:study连接的是192.169.1.01中的表1、表2,要是想和192.169.1.02中的表3表4一起IO输出到同一个txt文件中怎么设计最合适??取得字段相同。
import java.sql.*;
import java.util.*;
import java.io.*;public class test extends ClassObj{
public static void main(String[] args) throws Exception {
try {
ArrayList<ClassObj> lst = test.getResultLst();
String contents = test.formatLst(lst);
String fileName = "C:\\result.txt";
test.saveFile(fileName, contents);
} catch (Exception e) {
e.printStackTrace();
}
}
static ArrayList<ClassObj> getResultLst() throws Exception {
String url = "jdbc:odbc:study";
String user = "sa";
String password = "123";
ArrayList <ClassObj> lst = new ArrayList <ClassObj>();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select 业务 as A,日期 as B,员工编号 as C from 表1";
String sql2 = "select 业务 as D,日期 as E,员工编号 as F from 表2";
String sql3 = "select 业务 as G,日期 as H,员工编号 as I from 表3";
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection(url,user,password);
pstmt = con.prepareStatement(sql);
pstmt2 = con.prepareStatement(sql2);
pstmt3 = con.prepareStatement(sql3);
rs = pstmt.executeQuery();
rs2 = pstmt2.executeQuery();
rs3 = pstmt3.executeQuery();
while(){ //请问循环应当怎么写??
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(rs!= null)
rs.close();
if(pstmt != null)
pstmt.close();
if(con != null)
con.close();
}catch(Exception e){}
}
}
static void saveFile(String fileName, String contents)
throws IOException {
File f = new File(fileName); if (f.exists()){
throw new IOException("file exists...");
}
if (!f.createNewFile()) {
throw new IOException("file create failure...");
} try {
BufferedWriter output = new BufferedWriter(new FileWriter(f));
output.write(contents);
output.close();
} catch (Exception e) {
throw new IOException(e);
}
} //将lst转为contents
static String formatLst(ArrayList <ClassObj> lst){
String contents = "";
for(int i=0;i <lst.size();i++){
contents +=lst.get(i).getClassId+"\t"+lst.get(i).getClassGroup+"\t"+lst.get(i).getClassNo+"\n";
}
return contents;
}
}
}
ClassObj c1=null;
while(rs.next()){
c1=new ClassObj();
c1.setYeWu(rs.getString("yewu"));
....
lst.add(c1);
}
while(rs2.next()){
c1=new ClassObj();
c1.setYeWu(rs.getString("yewu"));
....
lst.add(c1);
}
while(rs3.next()){
c1=new ClassObj();
c1.setYeWu(rs.getString("yewu"));
....
lst.add(c1);
}
union all
select 业务 as D,日期 as E,员工编号 as F from 表2";
union all
select 业务 as G,日期 as H,员工编号 as I from 表3";
你倒是可以试试用存储过程,在存储过程中跨数据库取得数据,然后返回给你。其实跟在java中做是一样的。
如果用的是MSSQL,可以建立Linked Server;如果oracle,则建立DBlink,其它数据库有类似方法。至于怎样用,LZ可以查下这方面的资料。