一、当数据量较大时,传递Connection
DBConnection类:
public class DBConnection {
public Connection getConnection() throws Exception{
//Conneciton放在方法里
Connection conn=null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection(url,userName,pwd);
}catch(Exception e){
throw e;
}
return conn;
}
}
Start类:
Connection conn = new DBConnection().getConnection();
String sql_station = "select * from tableName";
PreparedStatement ps = conn.prepareStatement(sql_station);
ResultSet rs = ps.executeQuery();
二、当数据量较小时,传递整个数据表格:包含String[]的ArrayList
DBConnection类:
public class DBConnection{
//Connecion放在类中,后面的方法还要用到
private Connection conn;
private Statement statement;
//初始化时执行该代码块
{
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
if(!conn.isClosed())
System.out.println("Succeeded connecting to the Database!");
statement = conn.createStatement();
}
catch(Exception e) {
e.printStackTrace();
}
}
public ArrayList<String[]> select( String sql){
try {
ResultSet rs;
rs = statement.executeQuery(sql);
ArrayList<String[]> arr=parse(rs);
rs.close();
return arr;
} catch (SQLException e) {
e.printStackTrace();
System.out.println("exception return null for select");
}
return null;
}
public static ArrayList<String[]> parse( ResultSet rs){
ResultSetMetaData md;
try {
md = rs.getMetaData();
int columnCount = md.getColumnCount();
ArrayList<String[]> arr=new ArrayList<String[]>();
while(rs.next()) {
String[] str=new String[columnCount];
for(int i=1;i<=columnCount;i++){
str[i-1] = rs.getString(md.getColumnName(i));
}
arr.add(str);
}
return arr;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
Start类:
String sql = "select cell_id from tableName";
ArrayList<String[]> arr0=Easy.jdbc.select(sql);
两种方法相比之下,还是第一种好。因为第一种方法中,将连接数据库这一功能单独剥离出来,作为一个类。无论是对代码的可读性,还是后续的可扩展性都有好处。而反观第二种方法,使用statement,而不是preparedStatement,导致数据库操作效率较低。