近期整理的 JDBCTools

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import com.mysql.jdbc.PreparedStatement;

/**
 * JDBC 的工具类
 * 
 * 其中包含: 获取数据库连接, 关闭数据库资源等方法.
 */
public class JDBCTools {

	// 处理数据库事务
	// 提交事务
	public static Connection getConn(String ServiceDb) throws IOException {
		String driver = "com.mysql.jdbc.Driver";
		String db = null;
		String username = null;
		String password = null;
		Connection conn = null;
		
		ClassLoaderTest properties = new ClassLoaderTest();
		db = properties.getProperties("db.properties", "db_test_env") + ServiceDb;
		username = properties.getProperties("db.properties", "username_test_env");
		password = properties.getProperties("db.properties", "password_test_env");
		//System.out.println("@@@@@@ db = " + db +"@@@@@@ username = " + username +"@@@@@@ password = " + password );
		
		try {
			Class.forName(driver); // classLoader,加载对应驱动
			conn = (Connection) DriverManager.getConnection(db, username, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static int insert(String sql,String ServiceDb, String table, String column) throws IOException {
		Connection conn = getConn(ServiceDb);
		int i = 0;
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setString(1, table + "." + column + "()");
			i = pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	public static int update(String sql, String ServiceDb) throws IOException {
		Connection conn = getConn(ServiceDb);
		int i = 0;
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			i = pstmt.executeUpdate();
			System.out.println("resutl: " + i);
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	public static String getAll(String sql, String ServiceDb) throws IOException {
		Connection conn = getConn(ServiceDb);
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			int col = rs.getMetaData().getColumnCount();
			System.out.println("============================");
			while (rs.next()) {
				for (int i = 1; i <= col; i++) {
					System.out.print(rs.getString(i) + "\t");
					if ((i == 2) && (rs.getString(i).length() < 8)) {
						System.out.print("\t");
					}
				}
				System.out.println("");
			}
			System.out.println("============================");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static String selectResult(String sql, String ServiceDb) throws SQLException, IOException {
		String str = null;
		Connection conn = getConn(ServiceDb);
		PreparedStatement pstmt;
		pstmt = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next())
        {
            int col=1;
            str=rs.getString(col);
        }

		return str;
	}

	public static int delete(String sql, String ServiceDb) throws IOException {
		Connection conn = getConn(ServiceDb);
		int i = 0;
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			i = pstmt.executeUpdate();
			System.out.println("resutl: " + i);
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	
	public static ResultSet selectResultSet(String sql, String ServiceDb) throws SQLException, IOException {
		Connection conn = getConn(ServiceDb);
		PreparedStatement pstmt;
		pstmt = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		return rs;
	}
			
	public static String selectResultRandom(String sql, String ServiceDb) throws SQLException, IOException {
		String str = null;
		Connection conn = getConn(ServiceDb);
		PreparedStatement pstmt;
		pstmt = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		List<String> list=new ArrayList<String>();		
		while(rs.next())
        {
            int col=1;
            str=rs.getString(col);
            list.add(str);                               
        }
		 Random rnd=new Random();
         int b = rnd.nextInt(list.size());
         str = list.get(b);
		return str;
	}
	
	
}