package com.toby.mysql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;

public class Proc {
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/test", "root", "123456");
			/*
			 * in
			 * 
			 * CREATE PROCEDURE proc_city_findById(in n int) 
			 * BEGIN SELECT * FROM
			 * City WHERE id=n; 
			 * END
			 */

			CallableStatement cs = conn
					.prepareCall("call proc_city_findById(?)");
			cs.setInt(1, 2);
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				System.out.println("id:" + rs.getInt(1) + "~" + "name:"
						+ rs.getString(2));
			}

			/*
			 * out
			 * 
			 * CREATE PROCEDURE proc_city_count(out n int) 
			 * BEGIN SELECT
			 * count(id) FROM City;
			 * END
			 */

			cs = conn.prepareCall("call proc_city_count(?)");
			cs.registerOutParameter(1, Types.INTEGER);
			rs = cs.executeQuery();
			while (rs.next()) {
				System.out.println("count: " + rs.getInt(1));
			}

			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			
			if (cs != null){
				try{
					cs.close();
				} catch (Exception e){
					e.printStackTrace();
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
}