Java 之数据库工具类

一、工具类

package utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * 
 * 数据库帮助类
 * @author 25754
 *
 */
public class DbHelper {
    public static String URL = "";
    public static String USER = "";
    public static String PWD = "";
    public static Connection conn = null;
    public static PreparedStatement pstm = null;
    public static ResultSet rs = null;
    static {
        try {
            Properties ps = new Properties();
            InputStream is = DbHelper.class.getResourceAsStream("/db.properties");
            ps.load(is);
            URL = ps.getProperty("url");
            USER = ps.getProperty("user");
            PWD = ps.getProperty("pwd");
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 连接数据库
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        return (Connection) DriverManager.getConnection(URL, USER, PWD);
    }
    
    
    /**
     * 通用增删改
     * @param sql
     * @param params
     * @return
     */
    public static int excuteUpdate(String sql, Object[] params) {
        try {
            pstm = createPreparedStatement(sql, params);
            return pstm.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            closeAll(conn, pstm, null);
        }
        return 0;
    }
    
    /**
     * 查询数据
     * @param sql
     * @param params
     * @return
     */
    public static ResultSet excuteQuery(String sql, Object[] params) {
        try {
            pstm = createPreparedStatement(sql, params);
            return pstm.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * 处理数据
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static PreparedStatement createPreparedStatement(String sql, Object[] params) throws SQLException, ClassNotFoundException{
        pstm = getConnection().prepareStatement(sql);
        if(pstm != null) {
            for(int i = 0; i < params.length; i++) {
                pstm.setObject(i+1, params[i]);
            }
        }
        return pstm;
    }
    
    /**
     * 关闭连接
     * @param conn
     * @param pstm
     * @param rs
     */
    public static void closeAll(Connection conn, PreparedStatement pstm,ResultSet rs) {
        try{
            if(rs != null) {
                rs.close();
            }
            if(pstm != null) {
                pstm.close();
            }
            if(conn != null) {
                conn.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }
    }
}

二、使用

import java.sql.ResultSet;
import java.sql.SQLException;

import utils.DbHelper;


public class Test1 {
    public static void main(String[] args) {
        try {
            Object[] params = new Object[]{};
            ResultSet rs = DbHelper.excuteQuery("select * from tp_admin", params);
            if(rs != null) {
                while(rs.next()) {
                    System.out.println(rs.getInt("id") + " " + rs.getString("username"));
                }
                rs.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}