jdbcselect.java

使用Java检索数据库的例子。使用Access数据库

/**
 * ClassName:JdbcSelect
 * Description: D:\jdbc\JdbcSelect.java   综合查询例子
 * Copyright:    Copyright (c) 2002
 * Company:
 * @author:Liyunshan
 * @version 1.0
 */

import java.sql.*;
class JdbcSelect
{
	public static void main(String[] arguments)
	{
		String source = "jdbc:odbc:management";
		Connection con;
		String user = "";
		String password = "";
		Statement stmt;
		String sql = null;
		ResultSet rs;
		try
		{   //加载驱动程序，与数据库建立连接
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			con = DriverManager.getConnection(source, user, password);
			stmt = con.createStatement();
			System.out.println(" 2001级学生的总人数和平均年龄：");
            //把SQL语句放进字符串中
			sql = "SELECT COUNT(*),AVG(age) from student where year=2001";
			rs = stmt.executeQuery(sql);
	        //注意，开始时查询结果中指针指向第一行的前面，指针须先向后移
			//动才能指向第一行
			rs.next(); 
			System.out.println("人数：" + rs.getInt(1) + "，平均年龄：" +
			                   rs.getInt(2) );
	        //先关闭创建的对象；如果不关闭，当执行下一个SQL语句时，该
			//对象会自动关闭
			rs.close();
			System.out.println("18-20岁间的女生信息：");  //换 行
	        /*求计算机、数学系和电子系中年龄在18和20岁(不包括18岁，包括20岁)之间 
			的女生姓名、年龄和系别 */
            //注意单引号的用法
            sql = "SELECT name,age,department FROM student WHERE" +
			    " department IN ('计算机','电子','数学') AND (age BETWEEN 18" +
				" AND 20) AND age<>18 AND sex='女' ";
			rs = stmt.executeQuery(sql);
			while (rs.next())
			{
			   System.out.println("姓名：" + rs.getString(1) + ",年龄：" + 
			                   rs.getInt(2) + ",系别：" + rs.getString("department"));
			}
			rs.close();
	        //在成绩表中查询学号以bj2001开头的学生选课数目和总成绩、平
	        //均成绩以及最高分和最低分,其中平均成绩在75分以上，且按平均
			//成绩由高到低排序
            System.out.println("成绩表查询结果为：");
			sql = "SELECT student_id,COUNT(*),SUM(score), AVG(score)," +
                "MAX(score) AS maxs,MIN(score) AS mins FROM score " +
			    " WHERE student_id LIKE 'bj2001%' GROUP BY student_id " +
			    " HAVING AVG(score)>75 ORDER BY AVG(score) DESC";
			rs = stmt.executeQuery(sql);
			while (rs.next())
			{
				String sid = rs.getString(1);
				int num = rs.getInt(2);
				int sum = rs.getInt(3);
				double average = rs.getDouble(4);
                //下式也可以写为：rs.getMax(5);
				double max_score = rs.getDouble("maxs"); 
				double min_score = rs.getDouble("mins");
	            System.out.println("学号：" + sid + "，选课数：" + num +
                                   "，总成绩：" + sum +" ，平均成绩：" + average + 
                                   " ，最高分：" + max_score + " ，最低分：" + min_score);
			}
			
			rs.close();
			stmt.close();
			con.close();			
		}
        //错误处理
		catch(SQLException s) 
 		{
			System.out.println("SQL Error: " + s.toString() +
			           s.getErrorCode() + "  " + s.getSQLState());			
		}
		catch(Exception e)
		{
			System.out.println("Error: " + e.toString() + e.getMessage());
		}
		
	}
}