实验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级