Java SE 之数据库操作工具类(DBUtil)设计

JDBC创建数据库基本连接

//1.加载驱动程序
Class.forName(driveName);
//2.获得数据库连接
Connection connection = DriverManager.getConnection(dbResource,username,password);
//
String sql = "";
//3.获得SQL处理语句
Statement statement = connection.createStatement();
PreparedStatement preparedStatement = connection.prepareStatement(sql); //更为安全
//4.获得SQL执行结果intint
boolean result1 = statement.execute(sql); //执行
int result2 = statement.executeUpdate(sql); //执行更新
ResultSet resultSet = statement.executeQuery(sql);//查询

DBUtil设计

关键问题

　　+ 通过JDBC实现对数据库记录的增、删、查、改。

　　+ 如何高效利用好数据库与服务器之间实现通信的有限的数据库连接资源？

　　　　答案：JDBC+数据库连接池。

　　　　备注：

　　　　　　1.每创建一Connection类对象，就是占用一条连接资源。
　　　　　　2.更佳的设计，可参考开源工具C3P0数据库连接池解决方案。

ComboPooledDataSource pool = new ComboPooledDataSource(“demo”);
//pool.setUser("johnny");// (重新)设置用户姓名
//pool.setPassword("123456");// 用户密码
//pool.setJdbcUrl("databaseUrl");// MySQL数据库连接url
//pool.setDriverClass("com.mysql.jdbc.Driver");
//若是空参，自动到classpath目录下面加载“c3p0-config.xml”配置文件，若是maven项目，则放置于/resources目录下---配置文件的存储位置和名称必须是这样，且使用“默认配置”
Connection con = pool.getConnection();
//连接关闭之后，内存会被释放，下次取时会重新开(内存地址不共用)

　　　　　　另，推荐Apache开源工具Commons DBUtils：对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。

　　+ 如何设计架构良好（可扩展性好、高内聚低耦合、代码重用度高等）DAO（Database Access Object）层？

　　　　答案：【IBaseDao + BaseDaoImpl】 + IBusinessDao + BusinessDaoImpl

　　　　博文：Java SE 之 DAO层接口设计思想

设计方案

　　通过此工具，操纵JDBC基础层的连接和配置。

　　依赖工具：Eclipse + mysql-connector-java-5.1.7-bin.jar + junit-4.12.jar[可选项]

//dbutil.properties

user:root
password:123456
driver:com.mysql.jdbc.Driver
url:jdbc:mysql://127.0.0.1:3306/CorporationPropertyMS


//BDUtil.java


package com.cpms.test.junit;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 数据库操作工具类【通过配置文件dbutil.properties配置】
 *
 * @author johnny zen
 * @since 2017-11-3 19:43
 * 
 * @param user
 * @param password
 * @param driver
 * @param url
 */
public class DBUtil{
	private static String packagePath = "src.com.cpms.test.junit";//default current package's path
	private static Connection connection = null;
	private static Statement statement = null;
	private static Properties properties = null;
	private static String _propertiesFilePath = "dbutil.properties";//default properties file's  path
	
	static{ 
		// get current file's work path(notice:absolute path)
		String url = System.getProperty("user.dir") + "\" + packagePath.replace(".", "\") + "\" + _propertiesFilePath;
		
		//open property file
		FileInputStream fileInputStream = null;
		try {
			fileInputStream = new FileInputStream(url);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("[ DBUtil:ERROR ] "+ packagePath +" > open file failed!");
			e.printStackTrace();
		}
		
		properties = new Properties();
		try {
			properties.load(fileInputStream);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println("[ DBUtil:ERROR ] " + packagePath + " > load properties failed!");
			e.printStackTrace();
		}
		try {
			fileInputStream.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println("[DBUtil:ERROR ] " + packagePath + " > close file failed!");
			e.printStackTrace();
		}
		System.out.println("[DBUtil:SUCCESS ] " + packagePath + " > load properties success!");
	}
	
	//init properties
	private static void initProperties(){
		String driver = properties.getProperty("driver");
		String url = properties.getProperty("url");
		String user = properties.getProperty("user");
		String password = properties.getProperty("passsword");

		if((driver == null)||(url == null)||(user == null)||(password == null)){
			System.out.println("[DBUtil:ERROR ] " + packagePath + " > arguments[url/user/password/driver]'s value is not complete.");
		}
	}
	
	//(lazy) load connection
	private static void loadConnection(){
		
		//load Connection
		try{
			Class.forName(properties.getProperty("driver"));
		}catch(ClassNotFoundException e){
			System.out.println("[DBUtil:ERROR ] " + packagePath + " > load jdbc driver faild.");
			e.printStackTrace();
		}
		
		//connection database
		try {
			connection = DriverManager.getConnection(properties.getProperty("url"), properties);
		} catch (SQLException e) {
			System.out.println("[DBUtil:ERROR ] " + packagePath + " > [loadConnection] connection database faild.");
			e.printStackTrace();
		}
		
		System.out.println("[DBUtil:SUCCESS ] " + packagePath + " > [loadConnection] connection database success!");

	}
	
	//get connection
	public static Connection getConnection(){
		if(connection == null){
			loadConnection();
		}
		return connection;
	}
		
	//reset properties file path
	public static void setPropertiesFilePath(String propertiesFilePath){
		_propertiesFilePath = propertiesFilePath;
	}
	
	
	//get dbutil's proprties
	public static Properties getProperties(){
		return properties;
	}
	
}

测试：

@Test
	public void  DBUtilTest() {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int index = 0;//resultSet's index
		String sql = "select * from employee";
		
		//init prepareStatement
		try {
			preparedStatement = connection.prepareStatement(sql);
		} catch (SQLException e) {
			System.out.println("[Test:execute error]: load prepareStatement failed!");
			e.printStackTrace();
		}
		
		//execute SQL:get resultSet
		try {
			
			resultSet = preparedStatement.executeQuery();
		} catch (SQLException e) {
			System.out.println("[Test:execute error]: execute sql failed!");
			e.printStackTrace();
		}
		
		
		try {
			while(resultSet.next()){
				//notice: resultSet.getObject(var):default: from 1 to n
				System.out.println("[Test] " + index + ":" + resultSet.getObject(index + 1).toString());
				index++;
			}
			System.out.println("[Test] row total is " + resultSet.getRow() + ".");
		} catch (SQLException e) {
			System.out.println("[Test:execute error]: iterate resultset failed!");
			e.printStackTrace();
		}
	
	}

输出：

[DBUtil:SUCCESS ] src.com.cpms.test.junit > load properties success!
[DBUtil:SUCCESS ] src.com.cpms.test.junit > [loadConnection] connection database success!
[Test] 0:employeeabcdefghijklmnopqrstvuwa
[Test] 1:department005abcdefghijklmnopqrs
[Test] 2:201611389
[Test] 3:毕雨兰
[Test] 4:123456
[Test] 5:M
[Test] 6:19
[Test] 7:501928199205291867
[Test] 8:2012-08-10 08:23:59.0
[Test] 9:1
[Test] row total is 0.