在ETL的时候,连接oracle并load sql执行是基础操作,需要注意的是记得执行结束后记得关闭connection, 现记录一下:
其中xml文件:
select carname
from tb_info_car
where CARCODE = ?
ORDER BY carname
其中需要传参数使用?占位
java代码:
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.Iterator;
import java.util.List;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;
import oracle.jdbc.OracleDriver;
import org.apache.log4j.Logger;
public class SqlLoaderFromXML {
static Logger logger = Logger.getLogger(SqlLoaderFromXML.class);
private static String sqlPath = "./OracleSQL/";
private static String sqlFile = sqlPath + "Car.xml";
private static Connection DBConn = null;
private static final String DEDICATED_CONNECTION_STR = "jdbc:oracle:thin:@(description=(address=(host=#IP)(protocol=tcp)(port=#PORT))(connect_data=(service_name=#SID)(SERVER = DEDICATED)))";
public static String loadSqlFromXml(String xmlFile, String xpath) throws FileNotFoundException, DocumentException {
InputStream input = new FileInputStream(xmlFile);
SAXReader saxReader = new SAXReader(false);
Document sqlDoc = saxReader.read(input);
Node node = sqlDoc.selectSingleNode(xpath);
return node.getText();
}
public static Connection getConnection(String ip,String port, String dbName, String userName,
String password) throws SQLException {
DriverManager.registerDriver(new OracleDriver());
String connStr = "";
connStr = SqlLoaderFromXML.DEDICATED_CONNECTION_STR;
connStr = connStr.replaceAll("#IP", ip);
connStr = connStr.replaceAll("#SID", dbName);
connStr = connStr.replaceAll("#PORT", port);
return DriverManager.getConnection(connStr, userName, password);
}
public static void DBConnect() throws Exception {
DBConn = getConnection(IP,port,dbname,username,password);
DBConn.setAutoCommit(false);
logger.info(" Connet DB success");
}
public static void main(String[] args) throws Exception {
try {
DBConnect();
}
catch(Exception ex){
ex.printStackTrace();
logger.error("........."+ex.getMessage());
logger.error("Connect DB fail");
return;
}
PreparedStatement psQueryCarID = null;
ResultSet rsQueryCarID = null;
String sqlString = loadSqlFromXml(sqlFile, "//SQLSTRING/Products");
logger.info("SQL From XML File: \n" + sqlString);
try{
psQueryCarID = DBConn.prepareStatement(sqlString);
psQueryCarID.setString(1, "Audi");
rsQueryCarID = psQueryCarID.executeQuery();
while(rsQueryCarID.next()){
logger.info("car id: " + rsQueryCarID.getString("carname"));
}
} catch (SQLException e) {
logger.info(e);
}
finally{
if (DBConn != null)
try {
//DBConn.commit();
DBConn.close();
logger.info("close conn.");
} catch (SQLException e) {
e.printStackTrace();
}
if (psQueryCarID != null) {
try {
psQueryCarID.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rsQueryCarID != null) {
try {
rsQueryCarID.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
其中可以把连接oracle部分和load sql部分抽取出来打包以便后续直接调用, 也可以把DB参数放到properties通过读取配置的方式来使用