JDBC常用工具类

package com.sj.util;


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.List;


import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;


import com.sun.rowset.CachedRowSetImpl;

//jdbc—工具1
/**
 * 基本增删改查的工具类
 * 
 */
public class DbUtils {
	/**
	 * 条件查询RowSet
	 * RowSet 接口扩展了 ResultSet 接口。这样 RowSet 对象就有了 ResultSet 对象所有的功能
	 * @param sql
	 * @param params
	 * @return
	 */
	public static RowSet exeQuery(String sql, Object... params){
		Connection conn = null;
		CachedRowSet crs = null;
		try {
			conn = getConnection();
			PreparedStatement pstmt = getPstmt(sql, conn, params);
			ResultSet rs = pstmt.executeQuery();
			crs = new CachedRowSetImpl();
			crs.populate(rs);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			freeConnection(conn);
		}
		return crs;
	}
	/**
	 * 执行动态SQL查询
	 * 
	 * @param sql
	 *            可带参数的sql
	 * @param params
	 *            给SQL占位符提供参数的数组
	 * @return 离线结果集
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static ResultSet query(String sql, Object... params)
			throws SQLException {
		Connection conn = getConnection();
		try {
			PreparedStatement pstmt = getPstmt(sql, conn, params);


			ResultSet rs = pstmt.executeQuery();
			CachedRowSet crs = new CachedRowSetImpl();
			crs.populate(rs);
			return crs;
		} finally {
			freeConnection(conn);
		}
	}


	/**
	 * 执行增删改
	 * 
	 * @param sql
	 * @param params
	 *            给SQL占位符提供参数的数组
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void update(String sql, Object... params) throws SQLException {
		Connection conn = getConnection();
		try {
			PreparedStatement pstmt = getPstmt(sql, conn, params);
			pstmt.executeUpdate();
		} finally {
			// 7
			freeConnection(conn);
		}
	}


	public static void executeBacth(String[] sqls) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);// 批量执行sql必须配合事务
			stmt = conn.createStatement();
			for (int i = 0; i < sqls.length; i++) {
				stmt.addBatch(sqls[i]);
			}
			stmt.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw e;
			}


		} finally {
			freeConnection(conn);
		}
	}


	public static void executeBacth(List<String> sqls) throws SQLException{
		executeBacth((String[]) sqls.toArray());
	}


	private static PreparedStatement getPstmt(String sql, Connection conn,
			Object... params) throws SQLException {
		PreparedStatement pstmt = conn.prepareStatement(sql);
		for (int i = 0; i < params.length; i++) {
			pstmt.setObject(i + 1, params[i]);
		}
		return pstmt;
	}


	/**
	 * @return
	 * @throws SQLException 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private static Connection getConnection() throws SQLException{
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		}
		String url = "jdbc:oracle:thin:@localhost:1521:XE";// 协议+地址+端口+SID
		String username = "oa";
		String passwd = "oa123";
		return DriverManager.getConnection(url, username, passwd);
				
	}


	/**
	 * 释放连接
	 * 
	 * @param conn
	 */
	private static void freeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
	}

/**
 * 测试
 * @param args
 * @throws SQLException
 */

	public static void main(String[] args) throws SQLException {




		ResultSet rs = query("select * from users where username=? and password=?",
				"dev", "dev123");
		if (rs.next()){
			System.out.println("输入的用户名和密码正确！");
		System.out.println(rs.getInt(1));
		System.out.println(rs.getString(2));
		}
		else{
			System.out.println("输入的用户名和密码错误！");
		}
//		
		//update("insert into users values(?,?,?)",7,"dev","dev123");
		//update("insert into users values(?,?,?)",new Object[]{23,"hello","dev123"});
	}
}

package com.db.demo.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.sql.DataSource;
import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;
//jdbc-工具类2，得到Connection方法不同，换成第一种得到此方法即可
public class DbUtil {
	private static DbPool pool = DbcpPool.newInstance();
	/**
	 * 有查询条件的查询方法
	 * @param sql
	 * @param params
	 * @return
	 */
	public static RowSet exeQuery(String sql, Object... params){
		Connection conn = null;
		CachedRowSet crs = null;
		try {
			conn = pool.createConnection();
			PreparedStatement pstmt = getPstmt(sql, conn, params);
			ResultSet rs = pstmt.executeQuery();
			crs = new CachedRowSetImpl();
			crs.populate(rs);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			freeConnection(conn);
		}
		return crs;
	}
	private static PreparedStatement getPstmt(String sql, Connection conn,
			Object... params) throws SQLException {
		PreparedStatement pstmt = conn.prepareStatement(sql);
		for (int i = 0; i < params.length; i++) {
			pstmt.setObject(i + 1, params[i]);
		}
		return pstmt;
	}
	
	/*	public static int update(String sql,Object... params) {
	boolean bl = false;
	Connection conn = null;
	try {
		conn = pool.createConnection();
		PreparedStatement pstmt = getPstmt(sql, conn, params);
		return pstmt.executeUpdate(sql);
	} catch (SQLException e) {
		throw new RuntimeException(e);
	} finally {
		freeConnection(conn);
	}
}*/

	
	/**
	 * 插入数据，并显示插入的行数
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static int update(String sql, Object... params) throws SQLException {
		Connection conn = pool.createConnection();
		try {
			PreparedStatement pstmt = getPstmt(sql, conn, params);
			return pstmt.executeUpdate();
		} finally {
			// 7
			freeConnection(conn);
		}
	}
	public static boolean executeBacth(String[] sqls) {
		boolean b = true;
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = pool.createConnection();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();

			for (int i = 0; i < sqls.length; i++) {
				stmt.addBatch(sqls[i]);
			}
			stmt.executeBatch();

			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw new RuntimeException(e);
			}
			b = false;
			throw new RuntimeException(e);
		} finally {
			freeConnection(conn);
		}
		return b;
	}

	public static boolean executeBacth(List<String> sqls) {
		boolean b = true;
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = pool.createConnection();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			
			for (int i = 0; i < sqls.size(); i++) {
				stmt.addBatch((String) sqls.get(i));
			}
			stmt.executeBatch();
			
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw new RuntimeException(e);
			}
			b = false;
		} finally {
			freeConnection(conn);
		}
		return b;
	}
	/**
	 * 查询语句并判断结果是否有值
	 * @param sql
	 * @param params
	 * @return
	 */
	public static boolean exists(String sql,Object... params) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = pool.createConnection();
			pstmt = getPstmt(sql, conn, params);
			rs = pstmt.executeQuery();
			boolean bool = rs.next();
			return bool;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			freeConnection(conn);
		}
	}

	public static ColumnData getColumnData(String table) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = pool.createConnection();
			stmt = conn.createStatement();
			ResultSetMetaData rsm = stmt.executeQuery(
					"select * from (" + table + ")  where rownum<2")
					.getMetaData();
			
			int count = rsm.getColumnCount();
			String[] columnsName = new String[count];
			String[] columnsType = new String[count];
			for (int i = 1; i <= count; i++) {
				columnsName[(i - 1)] = rsm.getColumnName(i);
				columnsType[(i - 1)] = rsm.getColumnTypeName(i);
			}
			ColumnData localColumnData = new ColumnData(columnsName,
					columnsType);
			return localColumnData;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			freeConnection(conn);
		}
	}

	public static void freeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	public static Connection getConnection() throws SQLException {
		return pool.createConnection();
	}
	public static DataSource getDataSource(){
		return pool.getDataSource();
	}
	public static void main(String[] args) throws SQLException {
		System.out.println(getConnection());
		boolean b=exists("select * from users");
		if(b){
			System.out.println("有值！");
		}
		else{
			System.out.println("没有值！");
		}
		RowSet rs = exeQuery("select * from users where username=? and password=?",
				"dev", "dev123");
		if (rs.next()){
			System.out.println("输入的用户名和密码正确！");
		System.out.println(rs.getInt(1));
		System.out.println(rs.getString(2));
		}
		else{
			System.out.println("输入的用户名和密码错误！");
		}
		update("insert into users values(?,?,?)",67,"hhh",
		"hhh123");
	}
}