java 2实用教程（第五版）JDBC数据库操作

实验1 抽取样本

代码如下

import java.sql.*;
import java.util.*;
public class RandomGetRecord {
 public static void main(String args[]) {
  int wantRecordAmount=10;//随机抽取的记录数目
  Random random=new Random();
  try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//加载JDBC-ODBC
  }
  catch(ClassNotFoundException e) {
   System.out.println(e);
  }
  Connection con;
  Statement sql;
  ResultSet rs;
  try {
   String uri="jdbc:odbc:dataSource";
   String id="";
   String password="";
   con=DriverManager.getConnection(uri, id, password);
   sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
   rs=sql.executeQuery("SELECT*FROM goods");//sql调用.executeQuery方法查询goods表中的全部记录
   rs.last();
   int count=rs.getRow();
   Vector<Integer>vector=new Vector<Integer>();
   for(int i=0;i<count;i++) {
    vector.add(new Integer(i));
   }
   int itemAmount=Math.min(wantRecordAmount, count);
   System.out.println("随机抽取"+itemAmount+"条记录");
   double sum=0,n=itemAmount;
   while(itemAmount>0) {
    int randomIndex=random.nextInt(vector.size());
    int index=(vector.elementAt(randomIndex)).intValue();
    //将rs的游标移到index
    String number=rs.getString(1);
    String name=rs.getString(2);
    java.util.Date data=rs.getDate(3);
    double price=rs.getDouble(4);
    sum=sum+price;
    itemAmount--;
    vector.removeElementAt(randomIndex);
   }
   con.close();
   double aver=sum/n;
   System.out.println("均价"+aver+"元");
  }
  catch(SQLException e) {
   System.out.println(""+e);
  }
 }
}

实验2 用户转账

代码如下

import java.sql.*;
public class TurnMoney {
 public static void main(String args[]) {
  Connection con=null;
  Statement sql;
  ResultSet rs;
  try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  }
  catch(ClassNotFoundException e){
   System.out.println(""+e);
  }
  try {
   double n=100;
   con=DriverManager.getConnection("jdbc:odbc:tom","","");
   con.setAutoCommit(false);//关闭自动提交模式
   sql=con.createStatement();
   rs=sql.executeQuery("SELECT * FORM cardl WHERE number='zhangsan'");
   rs.next();
   double amountOne=rs.getDouble("amount");
   System.out.println("转账操作之前zhangsan的钱款数额"+amountOne);
   rs=sql.executeQuery("SELECT * FORM card2 WHERE number='xidanshop'");
   rs.next();
   double amountTwo=rs.getDouble("amount");
   System.out.println("转账操作之前xidanShop的钱款数额"+amountTwo);
   amountOne=amountOne-n;
   amountTwo=amountTwo+n;
   sql.executeUpdate("UPDATE card1 SET amount ="+amountOne+"WHERE number ='zhangsan'");
   sql.executeUpdate("UPDATE card2 SET amount ="+amountTwo+"WHERE number ='xidnashop'");
   con.commit();
   con.setAutoCommit(true);//恢复自动提交模式
   rs=sql.executeQuery("SELECT * FROM card1 WHERE number ='zhangsan'");
   rs.next();
   amountOne=rs.getDouble("amount");
   System.out.println("转账操作之后zhangsan的钱款数额"+amountOne);
   rs=sql.executeQuery("SELECT * FROM card2 WHERE number ='xidanshop'");
   rs.next();
   amountTwo=rs.getDouble("amount");
   System.out.println("转账操作之后zhangsan的钱款数额"+amountTwo);
   con.close();
  }
  catch(SQLException e){
   try {
    con.rollback();//撤销事务所做的操作
   }
   catch(SQLException exp) {}
   System.out.println(e.toString());
  }
 }
}

实验3 查询Excel电子表格

代码如下

import java.sql.*;
public class QueryExcel {
 public static void main(String args[]) {
  try {
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  }
  catch(ClassNotFoundException e) {
   System.out.print(e);
  }
  Connection con;
  Statement sql;
  ResultSet rs;
  try {
   con=DriverManager.getConnection("jdbc:odbc:myExcel","","");
   DatabaseMetaData metadata=con.getMetaData();
   ResultSet rs1=metadata.getColumns(null, null, "message", null);
   int 字段个数=0;
   while(rs1.next()) {
    字段个数++;
   }
   sql=con.createStatement();
   rs=sql.executeQuery("");
   while(rs.next()) {
    for(int k=1;k<=字段个数;k++) {
     System.out.print(""+rs.getShort(k)+" ");
    }
    System.out.println("");
   }
  }
  catch(SQLException e) {
   System.out.println(""+e);
  }
 }
}

PS:ZZU_CZZ_2018级