Java操作数据库的最基本方式是通过JDBC进行的,在本文中将讲述通过JDBC来操作MySQL数据库的基础知识。
其中利用xml保存MySQL的数据库信息,操作XML的工具类和相关的配置文件如下:
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>