java通用的jdbc数据库操作类

package com.hy.fddsvr.utils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;


public class DBManager {

    /**
     * @param args
     */
    static String driver;
    static String url;
    static String username;
    static String password;
    private Connection connection;
    private PreparedStatement pstmt;
    private ResultSet resultSet;

    public DBManager() {
        //从配置文件db.properties中读取数据库配置信息
        InputStream in = DBManager.class.getClassLoader().getResourceAsStream("db.properties");
        Properties pro = new Properties();
        try {
            pro.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //数据库驱动
        driver = pro.getProperty("driver");
        //服务器地址
        url = pro.getProperty("url");
        //数据库用户名
        username = pro.getProperty("username");
        //数据库密码
        password = pro.getProperty("password");

        try {
            //连接数据库
            getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获得数据库的连接
     *
     * @return
     * @throws ClassNotFoundException
     */
    public Connection getConnection() {
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }catch (Exception e) {
            System.out.println("fail to connect database");
        }
        return connection;
    }

    /**
     * 获得最大ID
     *
     * @param tableName
     * @return
     * @throws SQLException
     */
    public int getMaxId(String tableName) {
        Statement state = null;
        ResultSet rs = null;
        int maxId = 0;
        try {
            state = connection.createStatement();
            String sql = "select max(autoid) maxId from " + tableName;
            rs = state.executeQuery(sql);
            // 从resultset对象中将数据取出
            if (rs.next()) {
                maxId = rs.getInt("maxId");
            }
        } catch (Exception ex) {
            // TODO Auto-generated catch block
            ex.printStackTrace();
        }

        return ++maxId;
    }

    /**
     * 判断当前sql语句返回是否为空
     *
     * @param sql
     * @return 返回true表示空数据集，返回false表示非空数据集
     * @throws SQLException
     */
    public boolean CheckDataIsEmpty(String sql) {
        Statement state = null;
        ResultSet rs = null;
        boolean isempty=true;


        try {
            state = connection.createStatement();
            rs = state.executeQuery(sql);
            if (rs==null){
                isempty=true;
            }else{
                if(rs.next()){
                    isempty=false;
                }else{
                    isempty=true;
                }
            }
        } catch (Exception ex) {
            // TODO Auto-generated catch block
            ex.printStackTrace();
        }


        return isempty;
    }



    /**
     * 获取某个sql语句的首行首列值
     *
     * @param sql
     * @return 返回首行首列值
     * @throws SQLException
     */
    public String GetTopValue(String sql) {
        Statement state = null;
        ResultSet rs = null;
        String topvalue="";


        try {
            state = connection.createStatement();
            rs = state.executeQuery(sql);
            if (rs!=null){
                if(rs.next()){
                    topvalue = rs.getString(1);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }


        return topvalue;
    }

    /**
     * 获取某个sql语句的首行记录集
     *
     * @param sql
     * @return 返回首行记录集
     * @throws SQLException
     */
    public ResultSet GetTopDataSet(String sql) {
        Statement state = null;
        ResultSet rs = null;
        try {
            state = connection.createStatement();
            rs = state.executeQuery(sql);
            if (rs!=null){
                rs.next();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return rs;
    }


    /**
     * 执行某个sql语句的更新操作
     *
     * @param sql
     * @return 执行成功返回true,失败返回false
     * @throws SQLException
     */
    public boolean ExecSql(String sql) {
        Statement state = null;
        int iflag=-1;
        boolean res=false;

        try {
            pstmt = connection.prepareStatement(sql);
            iflag = pstmt.executeUpdate();
            res = (iflag > 0 ? true : false);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }


    /**
     * 增加、删除、改
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public boolean updateByPreparedStatement(String sql, List<Object> params)
            throws SQLException {
        boolean flag = false;
        int result = -1;
        pstmt = connection.prepareStatement(sql);
        int index = 1;
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        result = pstmt.executeUpdate();
        flag = result > 0 ? true : false;
        return flag;
    }

    /**
     * 查询单条记录
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public Map<String, Object> findSimpleResult(String sql, List<Object> params)
            throws SQLException {
        Map<String, Object> map = new HashMap<String, Object>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();// 返回查询结果
        ResultSetMetaData metaData = resultSet.getMetaData();
        int col_len = metaData.getColumnCount();
        while (resultSet.next()) {
            for (int i = 0; i < col_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
        }
        return map;
    }

    /**
     * 查询多条记录
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> findModeResult(String sql,
                                                    List<Object> params) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        while (resultSet.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            for (int i = 0; i < cols_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
            list.add(map);
        }

        return list;
    }

    /**
     * 通过反射机制查询单条记录
     *
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws Exception
     */
    public <T> T findSimpleRefResult(String sql, List<Object> params,
                                     Class<T> cls) throws Exception {
        T resultObject = null;
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        while (resultSet.next()) {
            // 通过反射机制创建一个实例
            resultObject = cls.newInstance();
            for (int i = 0; i < cols_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                int type = metaData.getColumnType(i + 1);
                if (cols_value == null) {
                    if (type == Types.INTEGER) {
                        cols_value = 0;
                    } else {
                        cols_value = "";
                    }
                }
                Field field = cls.getDeclaredField(cols_name.toLowerCase());
                field.setAccessible(true); // 打开javabean的访问权限
                if (type == Types.TIMESTAMP) {
                    field.set(resultObject, String.valueOf(cols_value));
                } else {
                    field.set(resultObject, cols_value);
                }
            }
        }
        return resultObject;

    }

    /**
     * 通过反射机制查询多条记录
     *
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws Exception
     */
    public <T> List<T> findMoreRefResult(String sql, List<Object> params,
                                         Class<T> cls) throws Exception {
        List<T> list = new ArrayList<T>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int cols_len = metaData.getColumnCount();
        while (resultSet.next()) {
            // 通过反射机制创建一个实例
            T resultObject = cls.newInstance();
            for (int i = 0; i < cols_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = resultSet.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                Field field = cls.getDeclaredField(cols_name);
                field.setAccessible(true); // 打开javabean的访问权限
                field.set(resultObject, cols_value);
            }
            list.add(resultObject);
        }
        return list;
    }

    /**
     * 释放数据库连接
     */
    public void releaseConn() {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

 

在 resources目录下创建一个dbo.properties文件，内容：

driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
url = jdbc:sqlserver://localhost:1433;DatabaseName=MyDB;
username = sa
password = 123

 

调用方式：

DBManager db=new DBManager();
db.ExecSql("update mytb set a1=0 where b1 is null");