java（JDBC连接数据库）[对Statement进行封装]

package com.iflytec.ex02;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/** * 数据连接类 *  * @author iflytek * @version 1.0 */public class ConnectionDB {/** * 数据库驱动类名称 */private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";/** * 连接字符串 */private static final String URLSTR = "jdbc:sqlserver://localhost:1433; databaseName=Northwind";/** * 用户名 */private static final String USERNAME = "sa";/** * 密码 */private static final String USERPASSWORD = "111111";/** * 创建数据库连接类 */private Connection connnection = null;/** * 创建操作对象 */private Statement statement = null;/** * 创建结果集对象 */private ResultSet resultSet = null;static {try {// 加载数据库驱动程序Class.forName(DRIVER);} catch (ClassNotFoundException e) {System.out.println("加载驱动错误");System.out.println(e.getMessage());}}/** * 建立数据库连接 *  * @return */public Connection getConnection() {try {// 获取连接connnection = DriverManager.getConnection(URLSTR, USERNAME,USERPASSWORD);} catch (SQLException e) {System.out.println(e.getMessage());}return connnection;}/** * insert update delete 统一的方法 *  * @param sql *            insert,update,delete SQL 语句 * @return 受影响的行数 */public int executeUpdate(String sql) {int affectedLine = 0;try {// 获得连接connnection = this.getConnection();// 创建对象statement = connnection.createStatement();// 执行SQL语句affectedLine = statement.executeUpdate(sql);} catch (SQLException e) {System.out.println(e.getMessage());} finally {// 释放资源closeAll();}return affectedLine;}/** * 查询数据库方法 *  * @param sql sql语句 *             * @return 结果集 */private ResultSet executeQueryRS(String sql) {try {// 获得连接connnection = this.getConnection();// 创建Statement对象statement = connnection.createStatement();// 执行SQL 语句resultSet = statement.executeQuery(sql);} catch (SQLException e) {System.out.println(e.getMessage());}return resultSet;}/** * 获取结果集，并将结果放在List中 * @param sql SQL语句 * @return List结果集 */public List<Object> excuteQuery(String sql) { ResultSet rs = executeQueryRS(sql);ResultSetMetaData rsmd = null;int columnCount = 0;try {rsmd = rs.getMetaData();} catch (SQLException e2) {System.out.println(e2.getMessage());}try {columnCount = rsmd.getColumnCount();} catch (SQLException e1) {System.out.println(e1.getMessage());}List<Object> list = new ArrayList<Object>();try {while(rs.next()) {Map<String, Object> map = new HashMap<String, Object>();for(int i = 1; i <= columnCount; i++) {map.put(rsmd.getColumnLabel(i), rs.getObject(i));}list.add(map);}} catch (SQLException e) {System.out.println(e.getMessage());} finally {closeAll();}return list;}/** * 释放所有资源 */private void closeAll() {// 释放结果集连接if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {System.out.println(e.getMessage());}}// 释放声明连接if (statement != null) {try {statement.close();} catch (SQLException e) {System.out.println(e.getMessage());}}// 释放数据库连接if (connnection != null) {try {connnection.close();} catch (SQLException e) {System.out.println(e.getMessage());}}}}