代码包括数据库连接、断开，为了适应大量不同名表的查询，编写了通用的查询表查询逻辑。
package Test;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
public class DBTest{
public static String ConnectionString = null;
public static String UserName = null;
public static String PassWord = null;
public static Connection Conn;
public static Statement Stmt;
//获取主表数据
private static List<String> getData(){
String tableName = "TestTable";
ResultSet result = null;
ResultSetMetaData md=null;
String mSql=new String();
mSql = "select * from "+tableName;
if(OpenConnection()){
try {
//执行sql语句
result = Stmt.executeQuery(mSql);
//移动指针到数据库第一行
result.next();
//获取行数
md = result.getMetaData();
int colnum = md.getColumnCount();
for(int i=1;i<=colnum;i++){
dataList.add(result.getString(i));
}
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
CloseConnection();//关闭链接
}
return dataList;
}
/*
通用查表方法
queryStrs--查询条件字段名
queryVals--查询条件id串
tableNames--表名
returnStrs--查询得到字段名
*/
private static List<List<String>> getSub(List<String>queryStrs,List<String> queryVals,List<String> tableNames,List<String> returnStrs){
List<List<String>> subResults = new ArrayList<List<String>>();
for(int i=0;i<queryStrs.size();i++){
List<String> subResult = new ArrayList<String>();
ResultSet result = null;
String mSql=new String();
mSql = "select " +returnStrs.get(i)+ " from " +tableNames.get(i)+ " where " +queryStrs.get(i)+ " IN (" +queryVals.get(i)+ ")";
if(OpenConnection()){
try {
result = Stmt.executeQuery(mSql);
//迭代每一行
while(result.next()){
subResult.add(result.getString(returnStrs.get(i)));
}
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
CloseConnection();//关闭链接
}
subResults.add(subResult);
}
return subResults;
}
//连接数据库
private static boolean OpenConnection(){
ClassString="oracle.jdbc.driver.OracleDriver";
ConnectionString="jdbc:oracle:thin:@localhost:1521:orcl";
UserName="test";
PassWord="root";
boolean mResult=true;
try{
Class.forName(ClassString);
if((UserName==null) && (PassWord==null)){
Conn= DriverManager.getConnection(ConnectionString);
}else{
Conn= DriverManager.getConnection(ConnectionString,UserName,PassWord);
}
Stmt=Conn.createStatement();
mResult=true;
}
catch(Exception e){
System.out.println(e.toString());
mResult=false;
}
return (mResult);
}
//关闭数据库连接
public static void CloseConnection(){
try{
Stmt.close();
Conn.close();
}
catch(Exception e){
System.out.println(e.toString());
}
}
}
转载请注明原文地址: https://ju.6miu.com/read-39308.html
