【06】数据库辅助类

1. db.properties

#Oracle
driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:XE
username=scott
password=tiger

2. JdbcUtil

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

public class JdbcUtil {

	private static String DRIVER;
	private static String URL;
	private static String USERNAME;
	private static String PASSWORD;
	static Properties properties = new Properties();
	private static DataSource dataSource; //数据库连接池
	
	static{
		FileInputStream fis;
		try {
			fis = new FileInputStream("config/db.properties");
			properties.load(fis);
			DRIVER = properties.getProperty("driver");
			URL = properties.getProperty("url");
			USERNAME = properties.getProperty("username");
			PASSWORD = properties.getProperty("password");
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	// 加载DBCP配置文件
	static {
		try {
			FileInputStream is = new FileInputStream("config/dbcp.properties");
			properties.load(is);
		} catch (IOException e) {
			e.printStackTrace();
		}

		try {
			dataSource = BasicDataSourceFactory.createDataSource(properties);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获得连接 -- JDBC
	 * @return
	 */
	public static Connection getConnection(){
		try {
			Class.forName(DRIVER);
			Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			return conn;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 获得连接 -- DBCP
	 * @return
	 */
	public static Connection getConnection() {
		try {
			Connection conn = dataSource.getConnection();
			return conn;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 *  释放资源
	 * @param rs
	 * @param stmt
	 * @param conn
	 */
	public static void closeResource(ResultSet rs,Statement stmt,Connection conn){
		if(null != rs){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (null != stmt) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	/**
	 *  增删改封装
	 */
	public static int executeUpdate(String sql, Object... objects) throws SQLException {
		PreparedStatement pstmt = getConnection().prepareStatement(sql);
		if (objects != null) {
			for (int i = 0; i < objects.length; i++) {
				pstmt.setObject((i + 1), objects[i]);
			}
		}
		return pstmt.executeUpdate();
	}
	
	/**
	 *  查询封装
	 */
	public static ResultSet executeQuery(String sql, Object... objects) throws SQLException {
		PreparedStatement pstmt = getConnection().prepareStatement(sql);
		if (objects != null) {
			for (int i = 0; i < objects.length; i++) {
				pstmt.setObject((i + 1), objects[i]);
			}
		}
		return pstmt.executeQuery();
	}
}

3. 工具类的使用

@Test
	public void testUpdate() {
		// 获得连接
		Connection conn = JdbcUtil.getConnection();
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			// sql语句
			String sql = "insert into Dept(DeptNo,Dname,loc) values (51,'dname51','loc51')";
			int i;
			i = stmt.executeUpdate(sql);
			// 5. 处理结果
			if (i > 0) {
				System.out.println("操作成功！");
			} else {
				System.out.println("操作失败！");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 关闭
		JdbcUtil.closeResource(null, stmt, conn);

	}

	@Test
	public void testQuery() {
		// 获得连接
		Connection conn = JdbcUtil.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			// sql语句
			String sql = "select deptNo,dname,loc from Dept";
			rs = stmt.executeQuery(sql);
			// 5. 处理结果
			while (rs.next()) {
				System.out.println(rs.getInt("deptNo") + " , " + rs.getString("dname") + " , " + rs.getString("loc"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 关闭
		JdbcUtil.closeResource(rs, stmt, conn);
	}