Java连接Oracle

三个文件

DbAccessOracle

XmlFileReadOracle

dboracle.xml

 

package cn.cnnic.ops.util;

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.util.Map;

public class DbAccessOracle {
    private static Connection conn = null;
    private static Statement sql = null;
    private static ResultSet rs = null;

    public static String[][] executeSql(String strSql) throws SQLException {

        String driver = "oracle.jdbc.driver.OracleDriver";
        int rowLen = 0;
        int colLen = 0;

        try {
            Class.forName(driver);
            Map<String, String> connMap = XmlFileReadOracle.getConnectionInfo("oracle");
            conn = DriverManager.getConnection(connMap.get("connUrl"), connMap.get("user"), connMap.get("password"));
            if (conn == null) {
                System.out.print("连接失败");
                System.exit(0);
            }
            sql = conn.createStatement();
            rs = sql.executeQuery(strSql);
            ResultSetMetaData rsmd = rs.getMetaData();
            colLen = rsmd.getColumnCount();
            while (rs.next()) {
                rowLen++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            rs.close();
            conn.close();
        }

        String[][] strArr = new String[rowLen][colLen];
        try {
            Class.forName(driver);
            Map<String, String> connMap = XmlFileReadOracle.getConnectionInfo("oracle");
            conn = DriverManager.getConnection(connMap.get("connUrl"), connMap.get("user"), connMap.get("password"));
            if (conn == null) {
                System.out.print("连接失败");
                System.exit(0);
            }
            sql = conn.createStatement();
            rs = sql.executeQuery(strSql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int rowIndex = 0;
            while (rs.next()) {
                for (int colIndex = 1; colIndex <= colLen; colIndex++) {
                    strArr[rowIndex][colIndex - 1] = rs.getString(rsmd.getColumnName(colIndex));
                }
                rowIndex++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            rs.close();
            conn.close();
        }
        return strArr;
    }

    public static void main(String[] args) throws SQLException {

        String strSql = "select DATA_DATE,DOMAINNAME,QUERYNUM from (select DATA_DATE,DOMAINNAME,QUERYNUM from DAILY_CHINESE_TOP10K_DATA where DATA_DATE= to_date('2016-10-11 00:00:00','yyyy-mm-dd hh24:mi:ss') order by QUERYNUM desc ) result where ROWNUM <= 10";
        String strResult = "";
        String[][] strArr = executeSql(strSql);
        for (int i = 0; i < strArr.length; i++) {
            for (int j = 0; j < strArr[i].length; j++) {
                strResult += strArr[i][j] + "|";
            }
            strResult = strResult.substring(0, strResult.length() - 1);
            strResult += "&";
        }
        strResult = strResult.substring(0, strResult.length() - 1);
        System.out.println(strResult);
    }
}

 

Java连接数据库读取数据，访问了两次数据库，浪费资源。

将数组改成List<Map<String,String>>即可搞定。 

 

package cn.cnnic.ops.util;

import java.io.File;
import java.util.HashMap;
import java.util.Map;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class XmlFileReadOracle {
    public static String fileName = "workspace/dnslogmvc/WebContent/WEB-INF/dboracle.xml";

    public static Map<String, String> getConnectionInfo(String dbType) {
        Map<String,String> connMap = new HashMap<String,String>();
        String connUrl = "";
        String driver = "";
        String ip = "";
        String port = "";
        String user = "";
        String password = "";
        
        Element element = null;
        // 可以使用绝对路劲
        File f = new File(fileName);
        // documentBuilder为抽象不能直接实例化(将XML文件转换为DOM文件)
        DocumentBuilder db = null;
        DocumentBuilderFactory dbf = null;
        try {
            // 返回documentBuilderFactory对象
            dbf = DocumentBuilderFactory.newInstance();
            // 返回db对象用documentBuilderFatory对象获得返回documentBuildr对象
            db = dbf.newDocumentBuilder();
            // 得到一个DOM并返回给document对象
            Document dt = db.parse(f);
            // 得到一个elment根元素
            element = dt.getDocumentElement();
            // 获得根节点
//            System.out.println("根元素：" + element.getNodeName());
            // 获得根元素下的子节点
            NodeList childNodes = element.getChildNodes();
            // 遍历这些子节点
            for (int i = 0; i < childNodes.getLength(); i++) {
                // 获得每个对应位置i的结点
                Node node1 = childNodes.item(i);
                if (dbType.equals(node1.getNodeName())) {
                    // 如果节点的名称为"Account"，则输出Account元素属性type
//                    System.out.println(node1.getAttributes().getNamedItem("id").getNodeValue());
                    // 获得<Accounts>下的节点
                    NodeList nodeDetail = node1.getChildNodes();
                    // 遍历<Accounts>下的节点
                    for (int j = 0; j < nodeDetail.getLength(); j++) {
                        // 获得<Accounts>元素每一个节点
                        Node detail = nodeDetail.item(j);
                        if ("driver".equals(detail.getNodeName())) // 输出code
                            driver = detail.getTextContent();
                        else if ("ip".equals(detail.getNodeName())) // 输出pass
                            ip = detail.getTextContent();
                        else if ("port".equals(detail.getNodeName())) // 输出name
                            port = detail.getTextContent();
                        else if ("user".equals(detail.getNodeName())) // 输出money
                            user = detail.getTextContent();
                        else if ("password".equals(detail.getNodeName()))
                            password = detail.getTextContent();
                    }
                }
            }
            connUrl = "jdbc:oracle:thin:@"+ip +":"+port+ ":"+"oracle";
            connMap.put("connUrl", connUrl);
            connMap.put("user", user);
            connMap.put("password", password);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return connMap;
    }

    public static void main(String[] args) {
        Map<String,String> connMap = getConnectionInfo("oracle");
        System.out.println(getConnectionInfo("oracle"));
        System.out.println(connMap.get("connUrl") + "--" + connMap.get("user") +"--"+ connMap.get("password"));
    }
}

 

<?xml version="1.0" encoding="UTF-8"?>
<accessinfo>
    <oracle id = "oracle">
        <driver>oracle.jdbc.driver.OracleDriver</driver>
        <ip>local</ip>
        <port>1521</port>
        <user>root</user>
        <password>root</password>
    </oracle>
</accessinfo>