jdbc调用存储过程

今天工作的时候经理写了个存储过程给我让我优化折线图表，由于从来没用过存储过程，苦恼了一会。为了方便以后使用，现记录一下。

一：连接信息

package com.cj.monitoringplatform.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.cj.monitoringplatform.model.CarLog;
import com.jfinal.kit.PathKit;

public class CallStoredProcedures{
	Logger logger = Logger.getLogger(CallStoredProcedures.class);
    private static String jdbcConfig = PathKit.getRootClassPath() + File.separator + "db.properties";

    protected Connection connection;
    protected Statement statement;
    protected PreparedStatement preparedStatement;
    protected ResultSet resultSet;
    
    //连接信息
    public Connection getCon() throws ClassNotFoundException, SQLException {
        try {
            Properties properties = new Properties();
            InputStream inputStream = new FileInputStream(jdbcConfig);
            properties.load(inputStream);

            String driverClassName = properties.getProperty("mysql.driverClass");
            String url = properties.getProperty("mysql.jdbcUrl");
            String username = properties.getProperty("mysql.userName");
            String password = properties.getProperty("mysql.passWord");

            Class.forName(driverClassName);
            connection = DriverManager.getConnection(url, username, password);
        } catch (IOException e) {
            throw new RuntimeException("初始化失败，配置文件 = " + jdbcConfig, e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return connection;
    }

二：调用存储过程

  public List<CarLog> importFansFocusRecord(String devid,String startTime,String endTime,String type,Integer index) {
    	List<CarLog> list=new ArrayList<CarLog>();
        try {
            getCon();
            CallableStatement callableStatement = connection.prepareCall("{call tpline(?,?,?,?,?)}");//问号代表存储过程的参数
            callableStatement.setString(1,devid);
            callableStatement.setString(2,startTime);
            callableStatement.setString(3,endTime);
            callableStatement.setString(4, type);
            callableStatement.setInt(5,index);
            callableStatement.execute();
            ResultSet resultSet = callableStatement.getResultSet();
            System.out.println("开始输出存储过程所查询的所有数据");
            while (resultSet.next()) {
            	//System.out.println("time:"+resultSet.getString("time")+"\t pressure1："+resultSet.getInt("pressure1")/1000+"\t pressure2："+resultSet.getInt("pressure2")/1000+"\t pressure3："+resultSet.getInt("pressure3")/1000);				
            	CarLog cLog=new CarLog();
            	 cLog.set("time", resultSet.getDate("time"));
                 cLog.set("pressure1", resultSet.getInt("pressure1")/1000);
                 cLog.set("pressure2", resultSet.getInt("pressure2")/1000);
                 cLog.set("pressure3", resultSet.getInt("pressure3")/1000);	
            	
                list.add(cLog);
            }
            closeAll();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

三：释放资源

// 释放资源
    public void closeAll() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null) {
                statement.close();
            }

            if (preparedStatement != null) {
                preparedStatement.close();
            }

            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }