Java+SQL+MySQL+Hive存储过程汇总

Java+SQL+MySQL+Hive存储过程汇总

存储过程概念

​ 在我们做一个复杂的项目时，会多次涉及到与数据库的连接，这时我们会一次次的来用SQL语句进行对数据库的连接，但如果我们这时用存储过程的话就只需要连接一次数据库就可以了，从而省去大量的SQL语句。

存储过程的优点

1、加快运行速度

​ 对于很简单的sql，存储过程没有什么优势。对于复杂的业务逻辑，因为在存储过程创建的时候，数据库已经对齐进行了一次解析和优化。存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用，所以执行速度会比普通快。(就相当于操作系统中的TLB快表)

2、减少网络传输

​ 存储过程直接就再数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其他服务器，所以会减少一定的网络传输，但是在存储过程中没有多次数据交互，那么实际上网络传输量和直接sql是一样的，而且我们的应用服务器通常与数据库是在同一内网，大数据的访问的瓶颈会是硬盘的速度，而不是网速。

3、提高可维护性

​ 存储过程有时候比程序更容易维护，这时因为可以实时更新DB端的存储过程，有些bug，直接改存储过程里的业务逻辑就搞定了。

4、增强安全性

​ 提高代码的安全，防止SQL注入，这一点sql语句也可以做到。

5、增加可扩展性

​ 应用程序和数据库操作分开，独立进行，而不是相互在一起，方便以后的扩展和DBA维护优化。

存储过程的缺点

1、数据更改困难

​ 如果更改范围大到需要对输入存储过程的参数进行更改 , 或者要更改由其返回的数据 , 则仍需要更新程序集中的代码以添加参数等等 ;
2、可移植性差

​ 由于存储过程将应用程序绑定到 Server , 因此使用存储过程封装业务逻辑将限制应用程序的可移植性 ; 如果应用程序的可移植性在您的环境中非常重要 , 则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择 ;

一、JAVA-存储过程调用

1、基本调用

public static void main(String[] args ){
   
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;
    try {
   
      Class.forName(driver);
      conn = DriverManager.getConnection(strUrl, "hyq", "hyq");
      CallableStatement proc = null;
      proc = conn.prepareCall("{ call hyq.testc(?) }");
      proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
      proc.execute();
      rs = (ResultSet)proc.getObject(1);
      while(rs.next())

//调用存储过程 删除流程相关记录  
            String procdure = "{Call sp_deleteInstByRootID(?)}";  
            CallableStatement cs = this.getHibernateTemplate().getSessionFactory().getCurrentSession().connection().prepareCall(procdure);  
            //this.getSession().connection().prepareCall(procdure).setString(0, orgdefid);  
            cs.setString(1, procinstid);  
            cs.execute();  
   

```````````````````````````````````````````````````````````````````````````````````````
ibatis配置文件：
<parameterMap id="parameterMapCesu" class="java.util.Map"> 

    <!-- 参数 --> 

    <parameter property="cesuDate" jdbcType="VARCHAR2"  javaType="java.lang.String" mode="IN" /> 

    <parameter property="csCount" jdbcType="DOUBLE" javaType="java.lang.Long" mode="OUT"/>

</parameterMap> 

<!-- 调用存储过程--> 

<procedure id="cesu" parameterMap="parameterMapCesu">  

    {
   call handiwork(?,?)}          

</procedure>


```````````````````````````````````````````````````````````````````````````````````````
Java代码:
public Long doTest(final String cesuDate) {
   
    return (Long) this.getSqlMapClientTemplate().execute(new SqlMapClientCallback() {
   
        @SuppressWarnings("unchecked")
        public Object doInSqlMapClient(SqlMapExecutor executor)
                throws SQLException {
   
            long csCount = 0;
            try {
      
                //设置存储过程参数
                Map cesu = new HashMap();
                cesu.put("cesuDate", cesuDate);//输入参数
                cesu.put("csCount", 0);//输出参数
                //调用存储过程
                executor.queryForObject("test.cesu",cesu);//ibatis文件的namespace是test
                csCount = (Long)cesu.get("csCount");//获取返回值   
                return csCount;
            } catch (Exception e) {
      
                e.printStackTrace();   
                return null;
            } 
        }
    });
}
        

2、框架调用

a）Hibernate

hibernate中调用存储过程:
/** 
 * 直接调用存储过程 
 * @param procString 
 * @author kongqz 
 * @throws Exception  
 * @date 2009-03-03 
 * **/  
public void callProcedure(String procString,List<Object> params) throws Exception {
     
    CallableStatement stmt = null;  
    try {
     
        stmt = this.getSession().connection().prepareCall(procString);  
        if (params != null){
     
            int idx = 1;  
            for (Object obj : params) {
     
                if (obj != null) {
     
                    stmt.setObject(idx, obj);  
                } else {
     
                    stmt.setNull(idx, Types.NULL);  
                }  
                idx++;  
            }  
        }  
        stmt.execute();  
    } catch (SQLException e) {
     
        e.printStackTrace();  
        throw new Exception("调用存储过程的时候发生错误[sql = " + procString + "]", e);  
          
    }  
}  

或

tx = session.beginTransaction();
Connection con=session.connection();

String procedure = "{call batchUpdateStudent(?) }";
CallableStatement cstmt = con.prepareCall(procedure);
cstmt.setInt(1,0); //把年龄参数设为0
cstmt.executeUpdate();
tx.commit();

b）Spring

spring调用存储过程:
1.继承StoredProcedure

org.springframework.jdbc.object.StoredProcedure是对应存储过程调用的操作对象，它通过其父类

org.springframework.jdbc.object.SqlCall获得相应的底层API支持(CallableStatementCreator)， 然

后在此基础之上构建了调用存储过程的执行方法。

2、重写父类的execute（）方法。将存储过程的参数封装成Map类型的传入该方法


3、写一个方法来封装存储过程的方法及把参数放到Map里面.

如：
  Map paraMap = new HashMap();
  paraMap.put(IN_PARAMETER_NAME, tableName);
  paraMap.put(INOUT_PARAMETER_NAME, v);
注意：key值一定要与前面构造函数里面声明的参数一致。


4、execute（）返回的map值要取到里面的value值，可以用前面构造函数声明时候用到的key值去取。
　　如：(String)resultMap.get