jdbc调用存储过程详解数据库

package com.qy.dbUtil; 
 
import java.io.IOException; 
import java.io.InputStream; 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.Properties; 
 
public class JDBCUtil { 
    private static String driver=null; 
    private static String url=null; 
    private static String username=null; 
    private static String password=null; 
     
    static{ 
        try { 
            InputStream is= JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");   
            Properties properties = new Properties(); 
            properties.load(is); 
            driver = properties.getProperty("driver").toString(); 
            url= properties.get("url").toString(); 
            username= properties.get("username").toString(); 
            password= properties.get("password").toString(); 
            Class.forName(driver); 
        } catch (IOException e) { 
            System.out.println("db.properties文件流加载失败"); 
            e.printStackTrace(); 
        } catch (ClassNotFoundException e) { 
            System.out.println("com.mysql.jdbc.Driver加载类不存在"); 
            e.printStackTrace(); 
        } 
         
    } 
     
    /** 
     * 获取Connection连接 
     * @return Connection 
     * @throws SQLException 
     */ 
    public static Connection getConnection() throws SQLException { 
            Connection con = DriverManager.getConnection(url, username, password); 
            System.out.println("JDBC链接成功"); 
            return con; 
    } 
     
    /** 
     * 释放连接 
     * @param con 
     * @param st 
     * @param rs 
     */ 
    public static void release(Connection con,Statement st,ResultSet rs) { 
        if (rs!=null) { 
            try { 
                rs.close(); 
            } catch (SQLException e) { 
                System.out.println("ResultSet关闭异常"); 
                e.printStackTrace(); 
            } 
        } 
        if (st!=null) { 
            try { 
                st.close(); 
            } catch (SQLException e) { 
                System.out.println("Statement关闭异常"); 
                e.printStackTrace(); 
            } 
        } 
        if (con!=null) { 
            try { 
                con.close(); 
            } catch (SQLException e) { 
                System.out.println("Connection关闭异常"); 
                e.printStackTrace(); 
            } 
        } 
        System.out.println("关闭成功"); 
    } 
     
    /** 
     * 测试连接 
     * TODO 
     * @param args 
     */ 
    public static void main(String[] args) { 
        try { 
            Connection con = getConnection(); 
            release(con, null, null); 
        } catch (SQLException e) { 
            System.out.println("Connection连接异常"); 
            e.printStackTrace(); 
        } 
    } 
}

    public static void main(String[] args) throws SQLException { 
        Connection con = JDBCUtil.getConnection(); /*获取连接*/ 
        /*调用sql语句*/ 
        //PreparedStatement  cs = con.prepareStatement("insert into lobtable(id,resum,clob) values(?,?,?)"); 
        /*调用存储过程*/ 
        CallableStatement cs = con.prepareCall("{call proName(?,?,?,?)}"); 
        cs.setObject(1, "id1");   //为存储过程设置参数 
        cs.setObject(2, "resum2"); 
        cs.setObject(3, "clob3"); 
        cs.execute();  //执行存储过程 
        JDBCUtil.release(con, cs, null);  /*释放连接*/ 
    }

    <context-param> 
        <param-name>contextConfigLocation</param-name> 
        <param-value>/WEB-INF/springConfig.xml</param-value> 
    </context-param> 
 
    <context-param> 
        <param-name>log4jConfigLocation</param-name> 
        <param-value>/WEB-INF/classes/log4j.properties</param-value> 
    </context-param> 
 
    <servlet> 
        <servlet-name>proxoolServletConfigurator</servlet-name> 
        <servlet-class> 
            org.logicalcobwebs.proxool.configuration.ServletConfigurator 
        </servlet-class> 
        <init-param> 
            <param-name>xmlFile</param-name> 
            <param-value>WEB-INF/classes/jdbcproxool.xml</param-value> 
        </init-param> 
        <load-on-startup>2</load-on-startup> 
    </servlet>

<?xml version="1.0" encoding="UTF-8"?> 
<something-else-entirely> 
   <proxool> 
       <alias>demodb</alias> 
       <driver-url>jdbc:mysql://127.0.0.1:3306/demo?useUnicode/=true&amp;characterEncoding/=UTF-8</driver-url> 
       <driver-class>com.mysql.jdbc.Driver</driver-class> 
       <driver-properties> 
           <property name="user" value="root"/> 
           <property name="password" value="root"/> 
       </driver-properties> 
       <minimum-connection-count>10</minimum-connection-count> 
       <!--连接池中可用的连接数量,空闲连接数小于此值时会新建连接--> 
       <prototype-count>20</prototype-count> 
       <maximum-connection-count>60</maximum-connection-count> 
       <!--house keeper保留线程处于睡眠状态的最长时间,自动侦察各个连接状态的时间间隔(毫秒),侦察到空闲的连接就马上回收,超时的销毁--> 
       <house-keeping-sleep-time>60000</house-keeping-sleep-time> 
       <!-- 一次建立的最大连接数,默认是10--> 
       <simultaneous-build-throttle>20</simultaneous-build-throttle> 
       <!--在分配连接前后是否进行有效性测试，这个是解决本问题的关键 --> 
       <test-before-use>true</test-before-use> 
       <!--用于测试的SQL语句一定要写--> 
       <house-keeping-test-sql>select now()</house-keeping-test-sql> 
       <!--允许proxool实现被代理的PreparedStatement 对象方法--> 
       <injectable-prepared-statement-interface>java.sql.PreparedStatement</injectable-prepared-statement-interface> 
   </proxool> 
</something-else-entirely>

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> 
        <property name="driverClassName" value="org.logicalcobwebs.proxool.ProxoolDriver"/> 
        <property name="url" value="proxool.demodb"/> 
    </bean> 
     
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 
        <property name="dataSource" ref="dataSource"/> 
    </bean>

@Repository("commonDao") 
public class CommonDao { 
    @Resource(name = "jdbcTemplate") 
    private JdbcTemplate jdbcTemplate;
 
    /** 
     * 调用存储过程(无返回值) 
     * TODO 
     * @param procedureName 存储过程名 
     * @param paramList 参数列表 
     */ 
    public void callProcedure(String procedureName, List paramList) { 
        String callProcedureString = "call " + procedureName + "("; 
        if (paramList != null && paramList.size() > 0) { 
            String paramString = ""; 
            for (int i = 0; i < paramList.size(); i++) { 
                paramString += "'" + paramList.get(i) + "'"; 
                if (i < paramList.size() - 1) { 
                    paramString += ","; 
                } 
            } 
            callProcedureString += paramString; 
        } 
        callProcedureString = callProcedureString + ")"; 
        this.jdbcTemplate.execute(callProcedureString); 
    }
 
    public JdbcTemplate getJdbcTemplate() { 
        return jdbcTemplate; 
    } 
 
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
        this.jdbcTemplate = jdbcTemplate; 
    } 
}