本人微信公众号"aeolian"~

jdbc调用存储过程

JDBC调用存储过程

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

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>contextConfigLocationparam-name>
        <param-value>/WEB-INF/springConfig.xmlparam-value>
    context-param>

    <context-param>
        <param-name>log4jConfigLocationparam-name>
        <param-value>/WEB-INF/classes/log4j.propertiesparam-value>
    context-param>

    <servlet>
        <servlet-name>proxoolServletConfiguratorservlet-name>
        <servlet-class>
            org.logicalcobwebs.proxool.configuration.ServletConfigurator
        servlet-class>
        <init-param>
            <param-name>xmlFileparam-name>
            <param-value>WEB-INF/classes/jdbcproxool.xmlparam-value>
        init-param>
        <load-on-startup>2load-on-startup>
    servlet>

jdbcproxool.xml

xml version="1.0" encoding="UTF-8"?>
<something-else-entirely>
   <proxool>
       <alias>demodbalias>
       <driver-url>jdbc:mysql://127.0.0.1:3306/demo?useUnicode\=true&characterEncoding\=UTF-8driver-url>
       <driver-class>com.mysql.jdbc.Driverdriver-class>
       <driver-properties>
           <property name="user" value="root"/>
           <property name="password" value="root"/>
       driver-properties>
       <minimum-connection-count>10minimum-connection-count>
       <prototype-count>20prototype-count>
       <maximum-connection-count>60maximum-connection-count>
       <house-keeping-sleep-time>60000house-keeping-sleep-time>
       <simultaneous-build-throttle>20simultaneous-build-throttle>
       <test-before-use>truetest-before-use>
       <house-keeping-test-sql>select now()house-keeping-test-sql>
       <injectable-prepared-statement-interface>java.sql.PreparedStatementinjectable-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; } }

 

点赞

Leave a Reply

Your email address will not be published. Required fields are marked *