jdbc调用存储过程

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

jdbc调用存储过程

    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);  /*释放连接*/
    }

JdbcTemplate调用存储过程

涉及内容：org.springframework.jdbc.core.JdbcTemplate、proxool-0.9.1.jar

在web.xml配置springConfig.xml和jdbcproxool.xml路径

    <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>

jdbcproxool.xml

<?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-keeping-sleep-time>60000</house-keeping-sleep-time>
       <simultaneous-build-throttle>20</simultaneous-build-throttle>
       <test-before-use>true</test-before-use>
       <house-keeping-test-sql>select now()</house-keeping-test-sql>
       <injectable-prepared-statement-interface>java.sql.PreparedStatement</injectable-prepared-statement-interface>
   </proxool>
</something-else-entirely>

springConfig.xml

    <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>

java调用

@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;
    }
}

 

jdbc调用存储过程

标签：inf   java   web   inject   sys   etc   except   tostring   throws