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