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) + " "); if ((i == 2) && (rs.getString(i).length() < 8)) { System.out.print(" "); } } 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; } }