Java连接mysql

三个文件

DbAccess

XmlFileRead

db.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;

public class DbAccess {

    public static String[][] executeSql(String sql) {
        
        int rowLen = 0;
        int colLen = 0;
        Connection con;
        String driver = "com.mysql.jdbc.Driver";
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(XmlFileRead.getConnectionInfo("mysql"));
            if (!con.isClosed()) {
                Statement stFirst = con.createStatement();
                ResultSet rsFirst = stFirst.executeQuery(sql);
                ResultSetMetaData rsmd = rsFirst.getMetaData();
                colLen = rsmd.getColumnCount();
                while (rsFirst.next()) {
                    rowLen++;
                }
                rsFirst.close();
                con.close();
            }
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }

        String[][] strArr = new String[rowLen][colLen];
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(XmlFileRead.getConnectionInfo("mysql"));
            if (!con.isClosed()) {
                Statement st = con.createStatement();
                ResultSet rs = st.executeQuery(sql);
                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++;
                }
                rs.close();
                con.close();
            }
        } catch (ClassNotFoundException e) {
            System.out.println("Sorry, can't find the Driver!");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return strArr;
    }

    public static void main(String[] args) {
        String strResult="";
        String sql = "select thedate,req_num from cndns.dnslog_cnnic_cn limit 1";
        String[][] strArr= executeSql(sql);
        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 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 XmlFileRead {
    public static String fileName = "workspace/dnslogmvc/WebContent/WEB-INF/db.xml";

    public static String getConnectionInfo(String dbType) {
        String connectInfo = "";
        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();
                    }
                }
            }
            connectInfo = "jdbc:mysql://"+ip +":"+port+ "/"+"zzh"+"?"+"user="+user+"&"+"password="+password;
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return connectInfo;
    }

    public static void main(String[] args) {
        System.out.println(getConnectionInfo("mysql"));
    }
}

 

<?xml version="1.0" encoding="UTF-8"?>
<accessinfo>
    <mysql id = "mysql">
        <driver>com.mysql.jdbc.Driver</driver>
        <ip>localhost</ip>
        <port>3306</port>
        <user>root</user>
        <password>root</password>
    </mysql>
</accessinfo>