连接数据库,JDBC-ODBC数据源,及java调用存储过程
需用到的包java.sql.*;
<一>java连接
1)加载驱动:Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");//java
Class.forName("sun.jdbc.odbc.jdbcodbcDriver");//jdbc-odbc数据源
2)建立连接:Connection conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=数据库名","用户名","密码");//java
Connection conn=DriverManager.getConnection("jdbc;odbc;数据源名称","用户名","密码");
3)数据存放:Statement stm=conn.createStatement();
stm.executeUpdate(SQL语句);
如果有结果集:ResultSet rs=stm.executeQuery(SQL语句);
while(rs.next())
{
}
rs.getString();//取某列的值
获得列数:ResultSetMetaData meta=rs.getMetaData();
for(int i=1;i<=meta.getColumnCount();i++)
{
System.out.print(rs.getString(i));
}
4)关闭:rs.close();stm.close();conn.close();
<二>用prepareStatement代替Statament
例:添加 PreparaStatement pt=null;
pt=conn.prepareStatement("insert into student values(?,?,?)");
pt.setString(1,name);//为参数赋值
pt.setInt(2,age);//同上
pt.setString(3,sex);//同上
int num=pt.executeUpdate();//num表示影响的行数,除查询外,都要用executeUpdate
System.out.print(num);
<三>java调用存储过程--CallableStatement
1)无参
CallableStatement call=null;
call.conn.prepareCall("{call 存储过程名}");
rs=call.executeQuery();
while(rs.next())
{
}
关闭call,rs,conn
2)带输入参数
call=conn.prepareCall("{call prco_chaxunById(?)}");
call.setInt(1,id);//为输入参数赋值
rs=call.executeQuery();//查询语句要用executeQuery
if(rs.next()){System.out.println(rs.getString(2));}
else{System.out.println(no data);}
关闭call,rs,conn
3)带输出参数
call=conn.prepareCall("{call proc_getNameById(?,?)}");
call.setInt(1,id);
call.registerOutParameter(2,java.sql.Types.VARCHAR);//为输出参数赋值
call.execute();带输出参数的用execute
String name=call.getString(2);
System.out.print(name);
关闭call,conn
注:关闭时要将代码放入finally中
(转载)java调用存储过程
一、调用存储过程(无结果集返回)
Connection connection = ConnectionHelper.getConnection();
CallableStatement callableStatement = connection.prepareCall("{ call procedureName(?,?) }");
callableStatement.setString(1, "xxxxxxxx");
callableStatement.setString(2, "xxxxxxxx");
callableStatement.execute();
//获得sql的消息并输出,这个估计很多人都需要
SQLWarning sqlWarning = callableStatement.getWarnings();
while (sqlWarning != null) {
System.out.println("sqlWarning.getErrorCode() = " + sqlWarning.getErrorCode());
System.out.println("sqlWarning.getSQLState() = " + sqlWarning.getSQLState());
System.out.println("sqlWarning.getMessage() = " + sqlWarning.getMessage());
sqlWarning = sqlWarning.getNextWarning();
}
//close
ConnectionHelper.closeConnection(callableStatement, connection);
二、调用存储过程,返回sql类型数据(非记录集)
Connection connection = ConnectionHelper.getConnection();
CallableStatement callableStatement = connection.prepareCall("{ call procedureName(?,?,?) }");
callableStatement.setString(1, "xxxxxxxx");
callableStatement.setString(2, "xxxxxxxx");
//重点是这句1
callableStatement.registerOutParameter(3, Types.INTEGER);
callableStatement.execute();
//取返回结果,重点是这句2
//int rsCount = callableStatement.getInt(3);
//close
ConnectionHelper.closeConnection(callableStatement, connection);
三、重点来了,返回记录集,多记录集
注意,不需要注册返回结果参数,只需要在sql中select出结果即可
例如:select * from tableName
即可得到返回结果
Connection connection = ConnectionHelper.getConnection();
CallableStatement callableStatement = connection.prepareCall("{ call procedureName(?) }");
//此处参数与结果集返回没有关系
callableStatement.setString(1, "xxxxxxxx");
callableStatement.execute();
ResultSet resultSet = callableStatement.getResultSet();
//以上两个语句,可以使用ResultSet resultSet = callableStatement.executeQuery();替代
//多结果返回
ResultSet resultSet2;
if (callableStatement.getMoreResults()) {
resultSet2 = callableStatement.getResultSet();
while (resultSet2.next()) {
}
}
//close
ConnectionHelper.closeConnection(callableStatement, connection);
提示:多结果返回可以使用如下代码(以上主要让大家明白,单一结果和多结果的区别):
Boolean hasMoreResult = true;
while (hasMoreResult) {
ResultSet resultSet = callableStatement.getResultSet();
while (resultSet.next()) {
}
hasMoreResult = callableStatement.getMoreResults();
}
Connection connection = ConnectionHelper.getConnection();
CallableStatement callableStatement = connection.prepareCall("{ call procedureName(?,?) }");
callableStatement.setString(1, "xxxxxxxx");
callableStatement.setString(2, "xxxxxxxx");
callableStatement.execute();
//获得sql的消息并输出,这个估计很多人都需要
SQLWarning sqlWarning = callableStatement.getWarnings();
while (sqlWarning != null) {
System.out.println("sqlWarning.getErrorCode() = " + sqlWarning.getErrorCode());
System.out.println("sqlWarning.getSQLState() = " + sqlWarning.getSQLState());
System.out.println("sqlWarning.getMessage() = " + sqlWarning.getMessage());
sqlWarning = sqlWarning.getNextWarning();
}
//close
ConnectionHelper.closeConnection(callableStatement, connection);
二、调用存储过程,返回sql类型数据(非记录集)
Connection connection = ConnectionHelper.getConnection();
CallableStatement callableStatement = connection.prepareCall("{ call procedureName(?,?,?) }");
callableStatement.setString(1, "xxxxxxxx");
callableStatement.setString(2, "xxxxxxxx");
//重点是这句1
callableStatement.registerOutParameter(3, Types.INTEGER);
callableStatement.execute();
//取返回结果,重点是这句2
//int rsCount = callableStatement.getInt(3);
//close
ConnectionHelper.closeConnection(callableStatement, connection);
三、重点来了,返回记录集,多记录集
注意,不需要注册返回结果参数,只需要在sql中select出结果即可
例如:select * from tableName
即可得到返回结果
Connection connection = ConnectionHelper.getConnection();
CallableStatement callableStatement = connection.prepareCall("{ call procedureName(?) }");
//此处参数与结果集返回没有关系
callableStatement.setString(1, "xxxxxxxx");
callableStatement.execute();
ResultSet resultSet = callableStatement.getResultSet();
//以上两个语句,可以使用ResultSet resultSet = callableStatement.executeQuery();替代
//多结果返回
ResultSet resultSet2;
if (callableStatement.getMoreResults()) {
resultSet2 = callableStatement.getResultSet();
while (resultSet2.next()) {
}
}
//close
ConnectionHelper.closeConnection(callableStatement, connection);
提示:多结果返回可以使用如下代码(以上主要让大家明白,单一结果和多结果的区别):
Boolean hasMoreResult = true;
while (hasMoreResult) {
ResultSet resultSet = callableStatement.getResultSet();
while (resultSet.next()) {
}
hasMoreResult = callableStatement.getMoreResults();
}
创建存储过程的脚本,
使用sqlserver2000 中的pubs 数据库中的 jobs表为例.
create procedure showAll
as
select * from jobs
create procedure obtainJob_desc
@outputParam varchar(20) output,
@id int
as
select @outputParam = job_desc from jobs where job_id = @id
create procedure obtainReturn
as
declare @ret int
select @ret = count(*) from jobs
return @ret
declare @ret int
exec @ret = obtainReturn
print @ret
用来获得连接的函数
public Connection getConnection()...{
Connection con = null;
try ...{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databasename=pubs","sa","");
} catch (Exception e) ...{
e.printStackTrace();
}
return con ;
}
Connection con = null;
try ...{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databasename=pubs","sa","");
} catch (Exception e) ...{
e.printStackTrace();
}
return con ;
}
1,调用得到结果集的存储过程
public void getResultSet()...{
//获得连接
Connection con = this.getConnection();
try ...{
//showAll为存储过程名
java.sql.CallableStatement cstm = con.prepareCall("{call showAll }");
ResultSet rs = cstm.executeQuery();
while(rs.next())...{
//这里写逻辑代码。
System.out.println(rs.getString(1));
}
rs.close();
con.close();
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获得连接
Connection con = this.getConnection();
try ...{
//showAll为存储过程名
java.sql.CallableStatement cstm = con.prepareCall("{call showAll }");
ResultSet rs = cstm.executeQuery();
while(rs.next())...{
//这里写逻辑代码。
System.out.println(rs.getString(1));
}
rs.close();
con.close();
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
2,调用带有输入 ,输出参数的存储过程。
public void getOutParameter(int inParam)...{
String outParam;
Connection con = this.getConnection();
try ...{
CallableStatement cstm = con.prepareCall("{call obtainJob_desc (?,?)}");
cstm.registerOutParameter(1, Types.VARCHAR);
cstm.setInt(2, inParam);
cstm.execute();;
//得到输出参数。
String outParma = cstm.getString(2);
System.out.println(outParma);
cstm.close();
con.close();
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
String outParam;
Connection con = this.getConnection();
try ...{
CallableStatement cstm = con.prepareCall("{call obtainJob_desc (?,?)}");
cstm.registerOutParameter(1, Types.VARCHAR);
cstm.setInt(2, inParam);
cstm.execute();;
//得到输出参数。
String outParma = cstm.getString(2);
System.out.println(outParma);
cstm.close();
con.close();
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
3,调用带返回值的存储过程。
public void getReturn()...{
int ret;
Connection con = this.getConnection();
try ...{
CallableStatement cstm = con.prepareCall("{?=call obtainReturn()}");
cstm.registerOutParameter(1, Types.INTEGER);
cstm.execute();
//得到返回值
ret = cstm.getInt(1);
System.out.println(ret);
cstm.close();
con.close();
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
int ret;
Connection con = this.getConnection();
try ...{
CallableStatement cstm = con.prepareCall("{?=call obtainReturn()}");
cstm.registerOutParameter(1, Types.INTEGER);
cstm.execute();
//得到返回值
ret = cstm.getInt(1);
System.out.println(ret);
cstm.close();
con.close();
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
我找了一些关于数据库中存储过程的使用格式,想与大家分享!