java oracle xml_java连接oracle并load sql从xml执行查询

在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通过读取配置的方式来使用