java-9: JDBC+xml灵活操作数据库

       使用JDBC可以方便地操作mysql，oracle，sqlserver数据库，但是每次换数据库都要改源码重新编译，挺麻烦，这里使用xml配置数据库连接信息，然后用DOM读取xml文件获取信息，进而可以灵活连接数据库。代码如下：

第一部分：xml脚本

<?xml version="1.0" encoding="UTF-8"?>
<books>
	<book id = "mysql">
		<driverName>com.mysql.cj.jdbc.Driver</driverName>
		<url>jdbc:mysql://127.0.0.1:3306/crm?useSSL=false&amp;serverTimezone=GMT&amp;allowPublicKeyRetrieval=true</url>
		<user>root</user>
		<passWord>admin</passWord>	
	</book>
	<book id = "oracle">
		<driverName>oracle.jdbc.driver.OracleDriver</driverName>
		<url>jdbc:oracle:thin:@127.0.0.1:1521:ORCL</url>
		<user>crm</user>
		<passWord>crm</passWord>	
	</book>
	<book id = "sqlserver">
		<driverName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverName>
		<url>jdbc:sqlserver://localhost:1029;DatabaseName=crm</url>
		<user>sa</user>
		<passWord>xxxxxxx</passWord>	
	</book>
</books>

 

第二部分：DOM解析XML

package org.szxu.JavaCoreTest;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class ParserXmlByDom {	
	public static DBConnectConfig GetConfig(String dbName) {
		DBConnectConfig config = new DBConnectConfig();
		
		//创建DOM工厂
		DocumentBuilderFactory domFactory = DocumentBuilderFactory.newInstance();
		InputStream input = null;
		String fileName = "E:\\Java-Code\\JavaCoreTest\\src\\org\\szxu\\JavaCoreTest\\Config_JDBC.xml";
		
		try {
			//获得DOM解析器（通过DOM工厂）
			DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
			
			//获取Document（通过文件输入流）
			input = new FileInputStream(fileName);
			Document doc = domBuilder.parse(input);
			
			Element root = doc.getDocumentElement();
			NodeList books = root.getChildNodes();
			
			if(books != null) {
				for(int i = 0; i < books.getLength(); i++) {
					Node book = books.item(i);
					if(book.getNodeType() == Node.ELEMENT_NODE) {
						//获取ID属性
						String innerDBName = book.getAttributes().getNamedItem("id").getNodeValue();
						//System.out.println("id is: " + dbName);
						
						if(!innerDBName.equalsIgnoreCase(dbName)) {
							continue;
						}
						
						//遍历book下的子节点
						for(Node node = book.getFirstChild(); node != null; node = node.getNextSibling()) {
							if(node.getNodeType() == Node.ELEMENT_NODE) {
								//driverName
								if(node.getNodeName().equals("driverName")) {
									config.driverName = node.getFirstChild().getNodeValue();
									//System.out.println("driverName is: " + config.driverName);
								}
								
								//url
								if(node.getNodeName().equals("url")) {
									config.url = node.getFirstChild().getNodeValue();
									//System.out.println("url is: " + config.url);
								}
								
								//user
								if(node.getNodeName().equals("user")) {
									config.user = node.getFirstChild().getNodeValue();
									//System.out.println("user is: " + config.user);
								}
								
								//passWord
								if(node.getNodeName().equals("passWord")) {
									config.passWord = node.getFirstChild().getNodeValue();
									//System.out.println("passWord is: " + config.passWord);
								}
							}
						}
					}
				}
			}
		}catch(ParserConfigurationException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SAXException e) {
			e.printStackTrace();
		}finally {
			try {
				input.close();
			}catch(IOException ee) {
				ee.printStackTrace();
			}
		}
		
		return config;
	}
}

class DBConnectConfig{
	public String driverName;
	public String url;
	public String user;
	public String passWord;
}
 

 

第三部分：java代码

package org.szxu.JavaCoreTest;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

public class JDBC_DBTest {
	public static JDBC_DBTest dbTest = null;
	public static Connection connection = null;
	private static String obj = "lock";
	private static String outDBName = null;
	
	public static DBConnectConfig config = null;
	
	private JDBC_DBTest() {}
	
	private JDBC_DBTest(String dbName) {
		outDBName = dbName;
		
		//获取数据库配置信息;
		config = ParserXmlByDom.GetConfig(dbName);
		System.out.println("JDBC_DBTest获取当前数据库配置为：");
		System.out.println("driverName: " + config.driverName);
		System.out.println("url: " + config.url);
		System.out.println("user: " + config.user);
		System.out.println("passWord: " + config.passWord + "\r\n");
		
		//驱动加载
		mySqlDBInite(config.driverName);
	}
	
	public static JDBC_DBTest instance(String dbName) {
		if(dbTest == null){
			synchronized(obj) {
				if(dbTest == null) {
					dbTest = new JDBC_DBTest(dbName);
				}
			}
		}
		return dbTest;
	}
	
	/**
	 * JDBC初始化驱动装载
	 * @param driverName
	 */
	public static boolean mySqlDBInite(String driverName){
		//装载驱动
		try {			
			Class.forName(driverName);		
			System.out.println(String.format("%s JDBC Driver is loaded successfully!\r\n", outDBName));
			return true;
		}catch(ClassNotFoundException e){
			System.out.println(String.format("Where is your %s JDBC Driver?\r\n", outDBName));
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 获取数据库连接
	 * @return
	 */
	public static synchronized Connection getConnection(){
		if(connection == null) {
			try {
				connection = DriverManager.getConnection(config.url, config.user, config.passWord);
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
			if(connection == null) {
				System.out.println(String.format("Wrong, %s database connects failed!\r\n", outDBName));
			}
			else {
				System.out.println(String.format("%s database connects successfully!\r\n", outDBName));
			}
		}
		
		return connection;
	}
	
	/**
	 * 操作数据库:statement方式
	 */
	public void StatementOperate(){
		Connection connection = getConnection();
		if(connection == null){
			return;
		}
		
		Statement stmt = null;    // 仅需要创建一次即可
		try {
			//statement对象
			stmt = connection.createStatement();
			
			//查
			printStudentData(connection);
			System.out.printf("\r\n");
			
			//改
			String sql = "update student set score = 85 where id = 3";
			int affectedRows = stmt.executeUpdate(sql);
			System.out.printf(String.format("student表，更新数量=%d%n", affectedRows));
			printStudentData(connection);
			System.out.printf("\r\n");
			
			//增
			sql = "select max(id) as maxID from student";
			ResultSet rs_MaxID = stmt.executeQuery(sql);
			int maxID = 0;
			if(rs_MaxID.next()){
				maxID = rs_MaxID.getInt("maxID") + 1;
			}
			sql = String.format("insert into student(id, name, score) values(%d, 'jlxu', 60)", maxID);
			affectedRows = stmt.executeUpdate(sql);
			System.out.printf("student表，插入数量=%d\r\n", affectedRows);
			printStudentData(connection);
			System.out.printf("\r\n");
			
			//删
			sql = "delete from student where name = 'jlxu'";
			affectedRows = stmt.executeUpdate(sql);
			System.out.printf("student表，删除数量=%d\r\n", affectedRows);
			printStudentData(connection);
			System.out.printf("\r\n");
		}catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			try {
				connection.close();
				stmt.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	
	/**
	 * 操作数据库：prepared方式
	 */
	public void preparedOperate() {
		Connection connection = getConnection();
		if(connection == null){
			return;
		}
		
		PreparedStatement ps = null;    // 每次操作均需重新创建
		try {
			//查
			printStudentData(connection);
			
			//改
			String sql = "update student set score = 85 where id = 3";
			ps = connection.prepareStatement(sql);
			int affectedRows = ps.executeUpdate();
			
			//增
			sql = "insert into student(id, name, score) select 4, 'jlxu', '65'";
			 
			ps = connection.prepareStatement(sql);
			affectedRows = ps.executeUpdate();
			System.out.printf("student表，插入数量=%d\r\n", affectedRows);
			printStudentData(connection);
			
			//删
			sql = "delete from student where name = 'jlxu'";
			ps = connection.prepareStatement(sql);
			affectedRows = ps.executeUpdate();
			System.out.printf("student表，删除数量=%d\r\n", affectedRows);
			printStudentData(connection);
			
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				connection.close();
				ps.close();
			}catch(SQLException ex) {
				ex.printStackTrace();
			}
		}
	}
	
	/**
	 * 查询学生表数据
	 * @param connection
	 */
	public void printStudentData(Connection connection) {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			//statement对象
			stmt = connection.createStatement();
			
			//查(使用ResultSet)
			System.out.print("查询student表数据：\r\n");
			String sql = "select id, name, score from student";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				System.out.print("id:" + rs.getString("id") + " ");
				System.out.print("name:" + rs.getString("name") + " ");
				System.out.print("score:" + rs.getInt("score") + "\r\n");
			}
		}catch(SQLException e) {
			e.printStackTrace();
			
		}finally {
			try {
				stmt.close();
				rs.close();
			}catch(SQLException ex) {
				ex.printStackTrace();
			}
		}
	}
}