dbdemo.java

java关于JDBC编程的几个简单实例

import java.sql.*; 

public class DBDemo {
	private Connection conn;
	private Statement stmt;
	private ResultSet rs;
	
	//构造方法,以JDBC-ODBC桥方式建立数据库连接
	public DBDemo(String url, String userName, String password ){
		try {
			// 加载JDBC-ODBC驱动程序
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			// 创建连接
			conn = DriverManager.getConnection(url, userName, password);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	//获取Statement
	public Statement getStmt(){
    	try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
            return stmt;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    //一般查询操作
   public ResultSet query(String sql) {
        try {
            stmt = getStmt();
            rs = stmt.executeQuery(sql);
            return rs;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
	//显示查询结果,显示学生相关信息
   public void display(ResultSet rs) {
        try {
			while (rs.next()) {
				String id = rs.getString(1);
				String name = rs.getString(2);
				String sex = rs.getString("性别");
				String major = rs.getString("专业方向");
				String dormitory = rs.getString("宿舍");
				System.out.println("学号:" + id + "\t姓名:" + name + "\t性别:"
						+ sex + "\t专业方向:" + major + "\t宿舍:"
						+ dormitory);
			}
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

	//统计查询记录的个数
    public int getRowCount(String strSql) {
        int intCount = 0;
        try {
            stmt =getStmt();
            rs = stmt.executeQuery("SELECT COUNT(*) FROM " + strSql);
            if (rs.next()) {
                intCount = rs.getInt(1);
            } else {
                intCount = -1;
            }
        } catch (Exception e) {
            intCount = -2;
            e.printStackTrace();
        } finally {
            return intCount;
        }
    }
    
	//插入操作,返回受此操作影响的行数
    public int insert(String sql) {
        int count = 0;
        stmt =getStmt();
        try {
            count = stmt.executeUpdate(sql);
        } catch (Exception e) {
            count = -2;
            e.printStackTrace();
        } finally {
            return count;
        }
    }
    
	//更新操作,返回受此操作影响的行数
    public int update(String sql) {
        int count = 0;
        stmt =getStmt();
        try {
            count = stmt.executeUpdate(sql);
        } catch (Exception e) {
            count = -2;
            e.printStackTrace();
        } finally {
            return count;
        }
    }
        
   	//删除操作,返回受此操作影响的行数
    public int delete(String sql) {
        int count = 0;
        stmt =getStmt();
        try {
            count = stmt.executeUpdate(sql);
        } catch (Exception e) {
            count = -2;
            e.printStackTrace();
        } finally {
            return count;
        }
    }

	//关闭操作
   public void close() {
        try {
			if (rs!=null)
  				rs.close(); 
			if (stmt!=null)
				stmt.close();
			if (conn!=null)
				conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }	
	
	public static void main(String[] args) {
		DBDemo db=new DBDemo("jdbc:odbc:myAccess","","");//创建与"软件系06级本科学生信息.mdb"的连接

		int count=db.getRowCount("student");//统计student表的人数
		System.out.println("软件系06级本科生的人数为:"+count);	
				
		db.display(db.query("select * from student where 性别='女'"));//显示所有女生的信息

		//增加一条记录:0123456789,张小山, 男,计算机科学与技术(软件开发),绿杨楼 G1234 
		db.insert("insert into student (学号,姓名,性别,专业方向,宿舍) values ('0123456789','张小山','男','计算机科学与技术(软件开发)','绿杨楼 G1234')");
		db.display(db.query("select * from student where 学号='0123456789'"));//显示刚插入的记录
		
		//修改张小山的记录:宿舍调整为"青枫阁 M2345" 
		db.update("update student set 宿舍='青枫阁 M2345' where 学号='0123456789'");
		db.display(db.query("select * from student where 学号='0123456789'"));//显示"张小山"的记录		

		//删除张小山的记录 
		db.delete("delete from student  where 学号='0123456789'");

		db.close();//执行关闭操作
	}
}