jdbc调用存储过程

来源：互联网发布：python爬虫视频教程编辑：程序博客网时间：2024/06/03 22:54

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

一：连接信息

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();        }    }

阅读全文

1 0