java调用mysql存储过程的实例
ConnUtils连接工具类:用来获取连接、释放资源
package StoredProcedure;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
public class ConnUtils {
private static String url="jdbc:mysql://localhost:3306/msystem?useUnicode=true&characterEncoding=utf8";
private static String user="root";
private static String password="admin";
private ConnUtils(){}
static{
try{
Class.forName("com.mysql.jdbc.Driver");
}catch(ClassNotFoundException e){
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException{
return (Connection) DriverManager.getConnection(url, user, password);
}
public static void free(ResultSet rs,Statement statement,Connection con){
try{
if(rs!=null)
rs.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
try{
if(statement!=null)
statement.close();
}catch(SQLException ex){
ex.printStackTrace();
}finally{
if(con!=null){
try{
con.close();
}catch(SQLException ex){
ex.printStackTrace();
}
}
}
}
}
}
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `msystem`.`data_proc` (OUT title INT)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
SELECT
COUNT(*) INTO title
FROM
`data` ;
END $$
DELIMITER ;
使用java调用带out参数的存储过程
package StoredProcedure;
import java.sql.SQLException;
import java.sql.Types;
import com.mysql.jdbc.CallableStatement;
import com.mysql.jdbc.Connection;
public class ProcedureTest {
public static void main(String[] args) {
Connection con = null;
CallableStatement statement = null;
String sql= "{call data_proc(?)}";
try{
con = ConnUtils.getConnection();
statement = (CallableStatement) con.prepareCall(sql);
statement.registerOutParameter(1, Types.VARCHAR);
statement.executeUpdate();
String title = statement.getString(1);
System.out.println(title);
}catch(SQLException ex){
ex.printStackTrace();
}finally{
ConnUtils.free(null, statement, con);
}
}
}
创建带out和in参数存储过程代码
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `msystem`.`data2_proc`(IN id INT, OUT title VARCHAR(1000))
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
SELECT d.title INTO title FROM `data` d WHERE d.id = id;
END$$
DELIMITER ;
使用JAVA调用带out和in参数存储过程
package StoredProcedure;
import java.sql.SQLException;
import java.sql.Types;
import com.mysql.jdbc.CallableStatement;
import com.mysql.jdbc.Connection;
public class ProcedureTest {
public static void main(String[] args) {
Connection con = null;
CallableStatement statement = null;
//String sql= "{call data_proc(?)}";
String sqlInOut="{call data2_proc(?,?)}";
try{
con = ConnUtils.getConnection();
statement = (CallableStatement) con.prepareCall(sqlInOut);
statement.setInt(1, 23);
statement.registerOutParameter(2, Types.VARCHAR);
statement.executeUpdate();
/**
* 以 Java 编程语言中 String 的形式获取 JDBC CHAR、VARCHAR 或 LONGVARCHAR 参数的值。
* 2 代表第二个参数
*/
String title = statement.getString(2);
System.out.println(title);
}catch(SQLException ex){
ex.printStackTrace();
}finally{
ConnUtils.free(null, statement, con);
}
}
}
事务范例:
public class Transaction{
public static void main(String[] args) throws Exception{
Connection conn = null;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:DSSchool");
Statement statement = conn.createStatement();
conn.setAutoCommit(false);
String sql1="UPDATE1";
String sql2="UPDATE2";
statement.executeUpdate(sql1);
statement.executeUpdate(sql2);
con.commit();
}catch(Exception ex){
conn.rollback(); //回滚
}finally{
conn.close();
}
}