欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Java的JDBC中Statement与CallableStatement对象实例

程序员文章站 2024-03-07 22:16:09
jdbc statement对象实例 以下是利用以下三种查询以及打开和关闭说明的例子: boolean execute(string sql) : 返回一个布尔值tru...

jdbc statement对象实例
以下是利用以下三种查询以及打开和关闭说明的例子:

boolean execute(string sql) : 返回一个布尔值true,如果resultset对象可以被检索,否则返回false。使用这个方法来执行sql ddl语句,或当需要使用真正的动态sql。

int executeupdate(string sql) : 返回受影响的sql语句执行的行数。使用此方法来执行,而希望得到一些受影响的行的sql语句 - 例如,insert,update或delete语句。

resultset executequery(string sql) : 返回resultset对象。当希望得到一个结果集使用此方法,就像使用一个select语句。

基于对环境和数据库安装在前面的章节中做此示例代码已被写入。

复制下面的例子中jdbcexample.java,编译并运行,如下所示:

//step 1. import required packages
import java.sql.*;

public class jdbcexample {
  // jdbc driver name and database url
  static final string jdbc_driver = "com.mysql.jdbc.driver"; 
  static final string db_url = "jdbc:mysql://localhost/emp";

  // database credentials
  static final string user = "username";
  static final string pass = "password";
  
  public static void main(string[] args) {
  connection conn = null;
  statement stmt = null;
  try{
   //step 2: register jdbc driver
   class.forname("com.mysql.jdbc.driver");

   //step 3: open a connection
   system.out.println("connecting to database...");
   conn = drivermanager.getconnection(db_url,user,pass);

   //step 4: execute a query
   system.out.println("creating statement...");
   stmt = conn.createstatement();
   string sql = "update employees set age=30 where id=103";
   
   // let us check if it returns a true result set or not.
   boolean ret = stmt.execute(sql);
   system.out.println("return value is : " + ret.tostring() );

   // let us update age of the record with id = 103;
   int rows = stmt.executeupdate(sql);
   system.out.println("rows impacted : " + rows );

   // let us select all the records and display them.
   sql = "select id, first, last, age from employees";
   resultset rs = stmt.executequery(sql);

   //step 5: extract data from result set
   while(rs.next()){
     //retrieve by column name
     int id = rs.getint("id");
     int age = rs.getint("age");
     string first = rs.getstring("first");
     string last = rs.getstring("last");

     //display values
     system.out.print("id: " + id);
     system.out.print(", age: " + age);
     system.out.print(", first: " + first);
     system.out.println(", last: " + last);
   }
   //step 6: clean-up environment
   rs.close();
   stmt.close();
   conn.close();
  }catch(sqlexception se){
   //handle errors for jdbc
   se.printstacktrace();
  }catch(exception e){
   //handle errors for class.forname
   e.printstacktrace();
  }finally{
   //finally block used to close resources
   try{
     if(stmt!=null)
      stmt.close();
   }catch(sqlexception se2){
   }// nothing we can do
   try{
     if(conn!=null)
      conn.close();
   }catch(sqlexception se){
     se.printstacktrace();
   }//end finally try
  }//end try
  system.out.println("goodbye!");
}//end main
}//end jdbcexample

现在编译上面的例子如下:

c:>javac jdbcexample.java

当运行jdbcexample,它会产生以下结果:

c:>java jdbcexample
connecting to database...
creating statement...
return value is : false
rows impacted : 1
id: 100, age: 18, first: zara, last: ali
id: 101, age: 25, first: mahnaz, last: fatma
id: 102, age: 30, first: zaid, last: khan
id: 103, age: 30, first: sumit, last: mittal
goodbye!


jdbc callablestatement对象实例
下面是利用callablestatement连同下列getempname()的mysql存储过程的例子:

请确定已经在emp数据库中创建该存储过程。可以使用mysql查询浏览器来完成它。

delimiter $$

drop procedure if exists `emp`.`getempname` $$
create procedure `emp`.`getempname` 
  (in emp_id int, out emp_first varchar(255))
begin
  select first into emp_first
  from employees
  where id = emp_id;
end $$

delimiter ;

基于对环境和数据库安装在前面的章节中进行,这个范例程式码已被写入。

复制下面的例子中jdbcexample.java,编译并运行,如下所示:

//step 1. import required packages
import java.sql.*;

public class jdbcexample {
  // jdbc driver name and database url
  static final string jdbc_driver = "com.mysql.jdbc.driver"; 
  static final string db_url = "jdbc:mysql://localhost/emp";

  // database credentials
  static final string user = "username";
  static final string pass = "password";
  
  public static void main(string[] args) {
  connection conn = null;
  callablestatement stmt = null;
  try{
   //step 2: register jdbc driver
   class.forname("com.mysql.jdbc.driver");

   //step 3: open a connection
   system.out.println("connecting to database...");
   conn = drivermanager.getconnection(db_url,user,pass);

   //step 4: execute a query
   system.out.println("creating statement...");
   string sql = "{call getempname (?, ?)}";
   stmt = conn.preparecall(sql);
   
   //bind in parameter first, then bind out parameter
   int empid = 102;
   stmt.setint(1, empid); // this would set id as 102
   // because second parameter is out so register it
   stmt.registeroutparameter(2, java.sql.types.varchar);
   
   //use execute method to run stored procedure.
   system.out.println("executing stored procedure..." );
   stmt.execute();

   //retrieve employee name with getxxx method
   string empname = stmt.getstring(2);
   system.out.println("emp name with id:" + 
        empid + " is " + empname);
   stmt.close();
   conn.close();
  }catch(sqlexception se){
   //handle errors for jdbc
   se.printstacktrace();
  }catch(exception e){
   //handle errors for class.forname
   e.printstacktrace();
  }finally{
   //finally block used to close resources
   try{
     if(stmt!=null)
      stmt.close();
   }catch(sqlexception se2){
   }// nothing we can do
   try{
     if(conn!=null)
      conn.close();
   }catch(sqlexception se){
     se.printstacktrace();
   }//end finally try
  }//end try
  system.out.println("goodbye!");
}//end main
}//end jdbcexample

现在编译上面的例子如下:

c:>javac jdbcexample.java

当运行jdbcexample,它会产生以下结果:

c:>java jdbcexample
connecting to database...
creating statement...
executing stored procedure...
emp name with id:102 is zaid
goodbye!