执行sql server 存储过程 是不是这样，怎么不对啊。

Question

CREATE PROCEDURE SP_isUser@urId varchar(16),
@urPass varchar(16),
@urlResult  int outputAS
      SELECT @urlResult=count(*)  FROM tbUser  WHERE (urId=@urId)  and   (urPass=@urPass)
GO//java
Connection con=this.getConnection();
CallableStatement callableStatement=null;
int intResult=0;
try
{
  callableStatement=con.prepareCall("{?=call SP_isUser(?,?)}");
  callableStatement.setString(2, userName);
  callableStatement.setString(3, password);
  callableStatement.setInt(1,intResult);
  
  callableStatement.execute();
  if (callableStatement.getInt(3)>0)
  {
  return true;
  }
  else 
  {
  return false;
  }

Answer 1

此回复为自动发出,仅用于显示而已，并无任何其他特殊作用
楼主【wufongming】截止到2008-06-26 21:13:59的历史汇总数据(不包括此帖)：
发帖数:37                 发帖分:1580               
结贴数:36                 结贴分:1480               
未结数:1                  未结分:100                
结贴率:97.30 %            结分率:93.67 %            
值得尊敬

Answer 2

不报，可能是我在基它类里try了。

Answer 3

不报，可能是我try了，sqlserver 的存储过程是这样执行的吗

Answer 4

我发现connection为null,谁帮我看看结构哪有问题。
public class ConnectionFactory implements DataSource
{

private String userName;
private String password;
private String driverName;
private String url;
    private java.sql.Connection connection;
    
private Connection getNewConnection()
    {
     try
{
if (this.connection!=null)
{
this.connection.close();
this.connection=null;
}
Class.forName(this.driverName);
this.connection=DriverManager.getConnection(this.url,this.userName,
                                                        this.password);
}
     catch(SQLException e)
     {
     e.printStackTrace();
     }
     catch(Exception e)
{
     e.printStackTrace();
}
     return this.connection;

    }

public Connection getConnection() throws SQLException
{
return this.getNewConnection();

} public Connection getConnection(String username, String password)
throws SQLException
{
this.setUserName(userName);
this.setPassword(password);
return this.getConnection();
} public PrintWriter getLogWriter() throws SQLException
{
// TODO Auto-generated method stub
return null;
} public int getLoginTimeout() throws SQLException
{
// TODO Auto-generated method stub
return 0;
} public void setLogWriter(PrintWriter out) throws SQLException
{
// TODO Auto-generated method stub } public void setLoginTimeout(int seconds) throws SQLException
{
// TODO Auto-generated method stub } public boolean isWrapperFor(Class<?> iface) throws SQLException
{
// TODO Auto-generated method stub
return false;
} public <T> T unwrap(Class<T> iface) throws SQLException
{
// TODO Auto-generated method stub
return null;
} /**
 * @param args
 */
public static void main(String[] args)
{
// TODO Auto-generated method stub } /**
 * @return the userName
 */
public String getUserName()
{
return userName;
} /**
 * @param userName the userName to set
 */
public void setUserName(String userName)
{
this.userName = userName;
} /**
 * @return the password
 */
public String getPassword()
{
return password;
} /**
 * @param password the password to set
 */
public void setPassword(String password)
{
this.password = password;
} /**
 * @return the driverName
 */
public String getDriverName()
{
return driverName;
} /**
 * @param driverName the driverName to set
 */
public void setDriverName(String driverName)
{
this.driverName = driverName;
} /**
 * @return the url
 */
public String getUrl()
{
return url;
} /**
 * @param url the url to set
 */
public void setUrl(String url)
{
this.url = url;
}}

Answer 5

2种方法都可以
http://blog.csdn.net/net_lover/archive/2007/08/29/1764117.aspx

Answer 6

public abstract class DatabaseObject
{
protected Connection connection=null;
protected ResultSet resultSet=null;
protected ResultSetMetaData resultSetMetaData=null;
private ConnectionFactory connectionFactory=null;
private java.sql.Statement statement=null;
private javax.sql.DataSource dataSource;

public DatabaseObject()
{
connection=null;
dataSource=null;
} public DatabaseObject(ConnectionFactory connectionFactory)
{
//this.set
}

public ResultSet getResultSet(String sql)
{
try
{
resultSet=statement.executeQuery(sql);
} catch (SQLException e)
{
e.printStackTrace();
this.resultSet=null;
}
return this.resultSet;
}

public ResultSetMetaData getResultSetMetaData(ResultSet resultSet)
{
ResultSetMetaData resultSetMetaData=null;
try
{
resultSetMetaData=resultSet.getMetaData();
} catch (SQLException e)
{
resultSetMetaData=null;
}
return resultSetMetaData;
}

public ResultSetMetaData getResultSetMetaData()
{
return (this.getResultSetMetaData(this.resultSet));
}

public ResultSet Execute(String spName)
{
ResultSet resultSet=null;
try
{
resultSet=statement.executeQuery(spName);
} catch (SQLException e)
{
System.out.print("執行錯誤:"+e.getMessage());
}
return resultSet;
}

public void setConnectionFactory(ConnectionFactory connectionFactory)
{
this.connectionFactory=connectionFactory;


try
{
connection=this.connectionFactory.getConnection();
statement=connection.createStatement();

} catch (SQLException e)
{
System.err.println(e);
}
}

public Connection getConnection()
{
return connection;
}

public java.sql.Statement getStatement()
{
return statement;
}

public javax.sql.DataSource getDataSource()
{
return dataSource;
}}

Answer 7

public class SqlServerConnectionFactory extends ConnectionFactory
{
    private final String dbDriver="com.microsoft.jdbc.sqlserver.SQLServerDriver";
    private String host;
    private int port;
    private String databaseName;
    
    /**
 * @param args
 */
public static void main(String[] args)
{
// TODO Auto-generated method stub } public SqlServerConnectionFactory(String host, int port, String databaseName,
                          String userName,String password)
{
super();
this.host = host;
this.port = port;
this.databaseName = databaseName;
this.setUserName(userName);
this.setPassword(password);
this.init();
}

private void init()
{
super.setDriverName(dbDriver);
super.setUrl("jdbc:microsoft:sqlserver://" 
     + host.trim() + ":" 
     + new Integer(port).toString() 
     + ";DatabaseName=" 
     + databaseName.trim());

}

public void setHost(String host)
{
if ((host==null)||host.equals("")||host.equals(".")||host.equals("local"))
{
host="localhost";
}
int index=host.indexOf("//",0);
if (index==0)
{
host=host.substring(2);
}
index=host.indexOf("//",0);
if (index>=0)
{
try
{
throw new Exception("SQL SERVER 主機名參數錯誤　！");
} catch (Exception e)
{

}
}
this.host=host;
}

public void setPort(int port)
{
if (port<0)
{
port=1433;
}
this.port=port;
}

public void setDatabaseName(String databaseName)
{
this.databaseName=databaseName;
}}

Answer 8

package elegant.modules.data;import elegant.modules.data.SqlServerConnectionFactory;public class DbObject extends DatabaseObject
{ public DbObject()
{
super(new SqlServerConnectionFactory("xxx.vicp.net", 1433, "FSYH", "sa","stywa128ab"));
} public DbObject(ConnectionFactory connectionFactory)
{
super(connectionFactory);
// TODO Auto-generated constructor stub
}}

Answer 9

public class LoginDB extends DbObject implements ILogin
{
public  String userName="";
public  String password="";
public  String encrypt="";

//private SqlServerConnectionFactory sqlServerConnectionFactory; public LoginDB()
{
super();
// TODO Auto-generated constructor stub
} public LoginDB(ConnectionFactory connectionFactory)
{
super(connectionFactory);
// TODO Auto-generated constructor stub
}

public void setUser(String userName)
{
   this.userName=userName;
}

public void setPassWord(String passWord)
{
   this.password=passWord;
}

public void setEncrypt(String encrypt)
{
this.encrypt=encrypt;
}

public boolean isUser()
{
Connection con=this.getConnection();
CallableStatement callableStatement=null;
int intResult=0;
try
{
  callableStatement=con.prepareCall("{?=call SP_isUser(?,?)}");
  callableStatement.setString(2, userName);
  callableStatement.setString(3, password);
  callableStatement.setInt(1,intResult);
  
  callableStatement.execute();
  if (callableStatement.getInt(3)>0)
  {
  return true;
  }
  else 
  {
  return false;
  }
  
  
} catch (Exception e)
{
return false;
}

}

public static void  main(String[] args)
{

LoginDB loginDB=new LoginDB();
loginDB.setUser("wufongming");
loginDB.setPassWord("123456");
if(loginDB.isUser())
{
 System.out.println(true);
}
else
{
    System.out.println(false);
}
}

}

Answer 10

类的问题改好了，以下可以得到正确结果。可是存储过程一直不行。999
String reString="";
ResultSet resultSet=this.Execute("select urId from tbUser");
try
{
  if(resultSet.next())
  {
reString= resultSet.getString("urId");
  }
}
catch(SQLException e)
{
e.printStackTrace();
}
return reString;

Answer 11

callableStatement=con.prepareCall("{?=call SP_isUser(?,?,?)}");
  //callableStatement.setString(2,this.userName);
 // callableStatement.setString(3,this.password);
  //callableStatement.registerOutParameter(1, Types.INTEGER);
  callableStatement.registerOutParameter("urResult", Types.INTEGER);
  callableStatement.setString("urId", "wufongming");
  
  callableStatement.setString("urPass","123456");
一執行到setString 就出錯。
Exception in thread "main" java.lang.AbstractMethodError: com.microsoft.jdbc.base.BaseCallableStatement.registerOutParameter(Ljava/lang/String;I)V

Answer 12

callableStatement=con.prepareCall("{?=call SP_isUser(?,?)}");
callableStatement.setString(2, userName);
callableStatement.setString(3, password);
callableStatement.setInt(1,intResult); 调用存储过程能这么写吗?我不清楚...望高人解答我只能说说我平时是怎么调用存储过程的...
http://topic.csdn.net/u/20080416/17/318670f0-d76d-4f12-82fc-c24cb5f7edbd.html

Answer 13

那个返回值的应该是registerOutParameter．
问题是现在我一，setString ,get,反正就是set,get 就出错．
Exception in thread "main" java.lang.AbstractMethodError: com.microsoft.jdbc.base.BaseCallableStatement

Answer 14

终于搞定了，晕死，其实是参数的顺序错了．
而用．set("urId","wufongming") 
这种好象一直都不行，