java调用存储过程和事务

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

创建带out参数的存储过程

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