Java操作MySQL数据库

Java操作数据库的最基本方式是通过JDBC进行的，在本文中将讲述通过JDBC来操作MySQL数据库的基础知识。

package framework.utils.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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

import framework.utils.xml.XmlUtil;

public class MysqlUtil {

	private static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";

	private static MysqlUtil instance;

	public static MysqlUtil getInstance() {
		if (instance == null) {
			instance = new MysqlUtil();
		}
		return instance;
	}

	private MysqlUtil() {
	}

	public Connection getConnection(String name) throws Exception {
		Class.forName(MYSQL_DRIVER);
		MysqlDBInfo dbInfo = new MysqlDBInfo(name);
		return DriverManager.getConnection(dbInfo.url, dbInfo.userName,
				dbInfo.password);
	}

	public List<Map<String, Object>> query(Connection conn, String sql)
			throws Exception {
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		Statement state = conn.createStatement();
		ResultSet set = state.executeQuery(sql);
		ResultSetMetaData metaData = set.getMetaData();
		int columnCount = metaData.getColumnCount();
		while (set.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 1; i <= columnCount; i++) {
				map.put(metaData.getColumnName(i), set.getObject(i));
			}
			result.add(map);
		}
		return result;
	}

	public boolean execute(Connection conn, String sql) throws Exception {
		Statement statement = conn.createStatement();
		return statement.execute(sql);
	}

	public int queryCount(Connection conn, String sql) throws Exception {
		Statement statement = conn.createStatement();
		ResultSet set = statement.executeQuery(sql);
		set.next();
		return set.getInt(1);
	}

	public int update(Connection conn, String sql) throws Exception {
		Statement statement = conn.createStatement();
		return statement.executeUpdate(sql);
	}

	class MysqlDBInfo {
		String url;
		String userName;
		String password;

		MysqlDBInfo(String dbConfig) throws Exception {
			String dbConfigFileName = this.getClass().getClassLoader()
					.getResource("dbconfig.xml").getFile();
			XmlUtil xmlUtil = XmlUtil.getInstance();
			Document document = xmlUtil.loadDocument(dbConfigFileName);
			Element root = xmlUtil.getRootElement(document);
			Element config = xmlUtil.getElementByAttribute(root, "name",
					dbConfig, 0, document);
			if (config == null) {
				throw new RuntimeException("未找到名称为" + dbConfig + "的数据库配置");
			}
			this.url = xmlUtil.getElementByTagName(config, "Url", 0, document)
					.getTextContent();
			this.userName = xmlUtil.getElementByTagName(config, "UserName", 0,
					document).getTextContent();
			this.password = xmlUtil.getElementByTagName(config, "Password", 0,
					document).getTextContent();
		}
	}

}

其中利用xml保存MySQL的数据库信息，操作XML的工具类和相关的配置文件如下：

package framework.utils.xml;

import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

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

public class XmlUtil {
	private static XmlUtil instance = null;

	private XmlUtil() {
	}

	public static XmlUtil getInstance() {
		if (instance == null) {
			instance = new XmlUtil();
		}
		return instance;
	}

	public Document loadDocument(String fileName) throws Exception {
		DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
		DocumentBuilder builder = factory.newDocumentBuilder();
		return builder.parse(fileName);
	}

	public Document createDocument() throws Exception {
		DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
		DocumentBuilder builder = factory.newDocumentBuilder();
		return builder.newDocument();
	}

	public Element getRootElement(Document document) {
		return document.getDocumentElement();
	}

	public Element createRootElement(String rootName, Document document) {
		Element root = document.createElement(rootName);
		document.appendChild(root);
		return root;
	}

	public List<Element> getElementsByTagName(Element parent, String tagName,
			Document document) {
		List<Element> elements = new ArrayList<Element>();
		if (parent == null) {
			parent = document.getDocumentElement();
		}
		NodeList elementsList = parent.getElementsByTagName(tagName);
		for (int i = 0; i < elementsList.getLength(); i++) {
			elements.add((Element) elementsList.item(i));
		}
		return elements;
	}

	public Element getElementByTagName(Element parent, String tagName,
			int index, Document document) {
		List<Element> elements = getElementsByTagName(parent, tagName, document);
		if (elements.size() <= 0) {
			return null;
		}
		if (index < 0 || index >= elements.size()) {
			return elements.get(0);
		}
		return elements.get(index);
	}

	public String getText(Element element) {
		return element.getTextContent();
	}

	public String getAttribute(Element element, String attrName) {
		return element.getAttribute(attrName);
	}

	public List<Element> getElementsByAttribute(Element parent,
			String attrName, String attrValue, Document document) {
		List<Element> elements = new ArrayList<Element>();
		if (parent == null) {
			parent = document.getDocumentElement();
		}
		NodeList elementsList = parent.getElementsByTagName("*");
		for (int i = 0; i < elementsList.getLength(); i++) {
			if (((Element) elementsList.item(i)).getAttribute(attrName).equals(
					attrValue)) {
				elements.add((Element) elementsList.item(i));
			}
		}
		return elements;
	}

	public Element getElementByAttribute(Element parent, String attrName,
			String attrValue, int index, Document document) {
		List<Element> elements = getElementsByAttribute(parent, attrName,
				attrValue, document);
		if (elements.size() <= 0) {
			return null;
		}
		if (index < 0 || index >= elements.size()) {
			return elements.get(0);
		}
		return elements.get(index);
	}

	public Element createElement(Element parent, String tagName, String text,
			Map<String, String> attrs, Document document) {
		if (parent == null) {
			parent = document.getDocumentElement();
		}
		Element element = document.createElement(tagName);
		if (text != null && text.length() > 0) {
			element.appendChild(document.createCDATASection(text));
		}
		if (!attrs.isEmpty()) {
			for (Entry<String, String> attr : attrs.entrySet()) {
				Attr newAttr = document.createAttribute(attr.getKey());
				newAttr.setValue(attr.getValue());
				element.setAttributeNode(newAttr);
			}
		}
		parent.appendChild(element);
		return element;
	}

	public void save(String fileName, Document document) throws Exception {
		TransformerFactory factory = TransformerFactory.newInstance();
		factory.setAttribute("indent-number", 4);
		Transformer transformer = factory.newTransformer();
		transformer.setOutputProperty(OutputKeys.INDENT, "yes");
		DOMSource source = new DOMSource(document);
		transformer.transform(source, new StreamResult(new OutputStreamWriter(
				new FileOutputStream(fileName), "UTF-8")));
	}

}

<?xml version="1.0" encoding="UTF-8"?>
<DBConfigs>
	<DBConfig name="test">
		<Url>jdbc:mysql://localhost:3306/test</Url>
		<UserName>root</UserName>
		<Password>******</Password>
	</DBConfig>
</DBConfigs>