java调用存储过程示例--查询,修改,删除

一、根据UserName查询Users

第一步：新建Users表

userid            int                      not null

username      nvarchar(50)      not null

age                int                      not null                        

第二步：新建存储过程SeacchUsers

 create procedure SearchUsers@username nvarchar(50),@age int,@userid intAsselect * from Users Where username like '%'+@username+'%'

 

第三步：用java程序调用存储过程

 

import java.sql.*;public class Search { public static void main(String[] args) { CallableStatement cmd = null; Connection cn = null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String url = "jdbc:sqlserver://databaseip:port;databaseName=db;user=sa;password=******"; String sql = "{? = call SearchUsers(?,?,?)}"; cn = DriverManager.getConnection(url); cmd = cn.prepareCall(sql); cmd.setString("username", "lee"); cmd.setString("age", ""); cmd.setInt("userid", 0); cmd.registerOutParameter(1, java.sql.Types.INTEGER); ResultSet rs = cmd.executeQuery(); while(rs.next()) { int userid = rs.getInt("userid"); String username = rs.getString("username"); int age = rs.getInt("age"); System.out.println("userid is:"+userid); System.out.println("username is:"+username); System.out.println("age is:"+age); } } catch(Exception ex) { ex.printStackTrace(); } finally { try { if (cmd != null) { cmd.close(); cmd = null; } if (cn != null) { cn.close(); cn = null; } } catch (Exception e) { e.printStackTrace(); } } }}  

二、根据UserName修改Users的年龄age

存储过程

create procedure UpdateUsers@username nvarchar(50),@age int,@userid intasupdate users set age = @age where username = @username

调用存储过程的java代码

import java.sql.*;public class Update{public static void main(String[] args) { CallableStatement cmd = null; Connection cn = null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String url = "jdbc:sqlserver://databaseip:port;databaseName=db;user=sa;password=******"; String sql = "{? = call UpdateUsers(?,?,?)}";cn = DriverManager.getConnection(url);cmd = cn.prepareCall(sql);cmd.setString("username", "johnny");cmd.setInt("age", 30);cmd.setInt("userid", 0);cmd.registerOutParameter(1, java.sql.Types.INTEGER);int num = cmd.executeUpdate();if(num>0){System.out.println("update successful!");}else{System.out.println("update failed!");}} catch(Exception ex) {ex.printStackTrace();} finally {try {if (cmd != null) {cmd.close();cmd = null;}if (cn != null) {cn.close();cn = null;}} catch (Exception e) {e.printStackTrace();}}}}

三、根据UserName刪除Users

类似上面