mysql存储过程脚本
drop procedure if exists sp_update_table_field;
delimiter //
create procedure sp_update_table_field()
begin
if not exists(select 1 from information_schema.columns where table_name='WB_Sys_Role' and column_name='Is_Export') then
ALTER TABLE WB_Sys_Role ADD Is_Export TINYINT NOT NULL default '0';
end if;
end //
java调用
package com.css.wbo.server.tool.initial;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class test {
String url = "jdbc:mysql://127.0.0.1:3306/userInfo";
String userName = "root";
String password = "root";
public Connection getConnection() {
Connection con=null;
try{
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
con = DriverManager.getConnection(url, this.userName, this.password);
}catch(SQLException sw){
}
return con;
}
public void testProc(){
Connection conn = getConnection();
CallableStatement stmt = null;
try{
stmt = conn.prepareCall("{call sp_update_table_field}");
stmt.execute();
}catch(Exception e){
System.out.println("hahad = "+e.toString());
}finally{
try {
stmt.close();
conn.close();
}catch (Exception ex) {
System.out.println("ex : "+ ex.getMessage());
}
}
}
public static void main(String[] args) {
new test().testProc();
}
} 总是提示找不到存储过程方法。使用下面的方法 java.sql.Statement ps = conn.createStatement();
String sql = sqlSB.toString();
ToolLog.getLog().debug(sql);
ps.execute(sql);总是提示delimiter //语法错误
drop procedure if exists sp_update_table_field;
delimiter //
create procedure sp_update_table_field()
begin
if not exists(select 1 from information_schema.columns where table_name='WB_Sys_Role' and column_name='Is_Export') then
ALTER TABLE WB_Sys_Role ADD Is_Export TINYINT NOT NULL default '0';
end if;
end //
java调用
package com.css.wbo.server.tool.initial;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class test {
String url = "jdbc:mysql://127.0.0.1:3306/userInfo";
String userName = "root";
String password = "root";
public Connection getConnection() {
Connection con=null;
try{
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
con = DriverManager.getConnection(url, this.userName, this.password);
}catch(SQLException sw){
}
return con;
}
public void testProc(){
Connection conn = getConnection();
CallableStatement stmt = null;
try{
stmt = conn.prepareCall("{call sp_update_table_field}");
stmt.execute();
}catch(Exception e){
System.out.println("hahad = "+e.toString());
}finally{
try {
stmt.close();
conn.close();
}catch (Exception ex) {
System.out.println("ex : "+ ex.getMessage());
}
}
}
public static void main(String[] args) {
new test().testProc();
}
} 总是提示找不到存储过程方法。使用下面的方法 java.sql.Statement ps = conn.createStatement();
String sql = sqlSB.toString();
ToolLog.getLog().debug(sql);
ps.execute(sql);总是提示delimiter //语法错误
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货