JDBC(5)：读取properties文件以及优化SqlHelper

读取properties文件

读取datasource.properties文件的属性，设置各参数。实现参数文件的自由替换而不影响到代码，解除硬编码。
代码目录图片：

代码：

private static String driver = "";
    private static String url = "";
    private static String user = "";
    private static String password = "";
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;
    private static InputStream fileIs= null;
    private static Properties properties = new Properties();
    static{
        //加载驱动
        try {
             fileIs = SqlHelper.class.getResourceAsStream("datasource.properties");  //相对路径写法
             // “/” 代表classpath的根目录
             // 在java项目下，classpath的根目录从 bin目录开始
             // 在Web项目下，classpath的根目录从 WEB-INF/classes/目录开始
             //不以’/’开头时默认是从此类所在的包下取资源，以’/’开头则是从ClassPath根下获取。 
             //fileIs = SqlHelper.class.getResourceAsStream("/myjdbc/demo4/datasource.properties");  //绝对路径写法
             properties.load(fileIs);

             url = properties.getProperty("jdbc.url");
             driver = properties.getProperty("jdbc.driver");
             user = properties.getProperty("jdbc.username");
             password = properties.getProperty("jdbc.password");
             Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("jdbc驱动程序注册失败！");  //正式代码用log输出
        } catch (IOException e) {
            System.out.println("properties读取配置文件失败");
            e.printStackTrace();
        }
    }

优化工具类SqlHelper

新增配置文件的读取以及存储过程的调用
SqlHelper.java

package myjdbc.demo4;

import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class SqlHelper {
    private static String driver = "";
    private static String url = "";
    private static String user = "";
    private static String password = "";
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;
    private static CallableStatement callableStatement = null;
    private static ResultSet resultSet = null;
    private static InputStream fileIs= null;
    private static Properties properties = new Properties();
    static{
        //加载驱动
        try {
             // “/” 代表classpath的根目录
             // 在java项目下，classpath的根目录从 bin目录开始
             // 在Web项目下，classpath的根目录从 WEB-INF/classes/目录开始
             //不以’/’开头时默认是从此类所在的包下取资源，以’/’开头则是从ClassPath根下获取。 
             //fileIs = SqlHelper.class.getResourceAsStream("/myjdbc/demo4/datasource.properties");  //绝对路径写法
             fileIs = SqlHelper.class.getResourceAsStream("datasource.properties");  //相对路径写法
             properties.load(fileIs);
             url = properties.getProperty("jdbc.url");
             driver = properties.getProperty("jdbc.driver");
             user = properties.getProperty("jdbc.username");
             password = properties.getProperty("jdbc.password");
             Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("jdbc驱动程序注册失败！");  //正式代码用log输出
        } catch (IOException e) {
            System.out.println("properties读取配置文件失败");
            e.printStackTrace();
        }
    }

    /**
     * 方法名：getConnection
     * 详述：获取连接对象的方法
     * 开发人员：nowuseeme
     * @return Connection
     */
    public static Connection getConnection() {
        try {
            return (Connection) DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }


    /**
     * 方法名：executeQuery
     * 详述：查询
     * 开发人员：nowuseeme
     * @param sql
     * @param parameters
     * @return ResultSet
     */
    public static ResultSet executeQuery(String sql,String[] parameters)
    {
        try
        {
            connection = getConnection();           
            preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
            if(null != parameters){
                for(int i=0;i<parameters.length;i++)
                {
                    preparedStatement.setString(i+1,parameters[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
        }catch(Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally{

        }
        return resultSet;
    }

    /**
     * 方法名：executeUpdate
     * 详述：实现增删改
     * 开发人员：nowuseeme
     * @param sql
     * @param parameters void
     */
    public static Integer executeUpdate(String sql,String[] parameters){
        Integer temp = 0;
        try {
             connection = getConnection();
             preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
             if(null != parameters){
                 for(int i=0;i<parameters.length;i++)
                 {
                    preparedStatement.setString(i+1,parameters[i]);
                 }
             }
            temp = preparedStatement.executeUpdate();
        } catch (SQLException e) {
             e.printStackTrace();
        }finally{
             close(resultSet, connection, preparedStatement);
        }
        return temp;
    }

    /**
     * 方法名：executeUpateTransaction
     * 详述：实现多条sql语句的增删改，且保持一致性(事务)
     * 开发人员：nowuseeme
     * @param sqls
     * @param parameters void
     */
    public static void executeUpateTransaction(String[] sqls, String[][] parameters){       
        try {
            connection = getConnection();
            connection.setAutoCommit(false);
            for (int i = 0; i < sqls.length; i++) {
                preparedStatement = (PreparedStatement) connection.prepareStatement(sqls[i]);
                if (null != parameters[i]) {                    
                    for (int j = 0; j < parameters[i].length; j++) {
                        preparedStatement.setString(j+1, parameters[i][j]);
                    }
                }   

            }
            connection.commit();

        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            throw  new RuntimeException(e.getMessage());
        }finally{
            close(resultSet, connection, preparedStatement);
        }
    }


    /***
     * 调用存储过程1
     */
    public static ResultSet callPro1(String sql, String[] parameters)
    {
        try{
            connection = getConnection();
            callableStatement = connection.prepareCall(sql);
            if(parameters!=null){
                for(int i=0;i<parameters.length;i++){
                    callableStatement.setObject(i+1,parameters[i]);
                }
            }   
           resultSet = callableStatement.executeQuery();
        }catch(Exception e) {
            e.printStackTrace(); 
        }finally{
            close(resultSet, connection, callableStatement);
        }
        return resultSet;
    }


    /**
     * 方法名：close
     * 详述：释放资源
     * 开发人员：nowuseeme
     * @param rs
     * @param conn
     * @param stmt void
     */
    public static void close(ResultSet rs, Connection conn, Statement stmt){
         //关闭资源(先开后关)
        if(null != rs)
        {
            try{
                rs.close();
            }
            catch(SQLException e){
                e.printStackTrace();
            }
            rs=null;
        }
        if(null != stmt){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        if(null != conn){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
    }

}

同级目录下的datasource.properties文件：

jdbc.url=jdbc\:mysql\://127.0.0.1\:3306/address?useUnicode\=true&characterEncoding\=utf-8
jdbc.username=root
jdbc.password=password
jdbc.driver=com.mysql.jdbc.Driver