jdbcupdate.java

使用Java更新数据库的例子。使用Access数据库

/**
 * ClassName:JdbcUpdate
 * Description: D:/jdbc/JdbcUpdate.java   一个修改数据库的综合示例
 * Copyright:    Copyright (c) 2002
 * Company:
 * @author:Liyunshan
 * @version 1.0
 */
 
import java.sql.*;
public class JdbcUpdate
{		
	public static void main(String args[])
	{
		Connection conn; //数据库的连接
		Statement stmt;  //代表一个发送到数据库来执行的数据库命令 
		ResultSet rs;    //读取的数据结果，也就是数据库返回的结果 
		String source = "jdbc:odbc:management";   //包含了被访问数据库或者数据源的名称，用URL形式表示 .
		String user = "";     //数据库的用户名 
		String password = ""; //数据库的密码 
		String sql;
		
		try	
		{
			 //打开数据库的连接
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			conn = DriverManager.getConnection(source,user,password);			
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
			 		                  ResultSet.CONCUR_READ_ONLY);		 
			
			// 1--插入学生信息
			sql = "SELECT * FROM student";
			rs = stmt.executeQuery(sql);  //从数据库中读取内容
			int num = 0;
			rs.last();
			num = rs.getRow();    //结果记录集中的行数
			rs.beforeFirst();
			
			System.out.println("原来学生表中共有 " + num + " 条记录");
			sql = "INSERT INTO student VALUES " +
			      "('bj200205','李平','男',18,'山东',null,2002)";
			
			int i = 0;
			i = stmt.executeUpdate(sql);
			System.out.println("插入语句影响了" + i + " 行!");
			sql = "SELECT * FROM student";
			rs = stmt.executeQuery(sql);
			rs.last();
			num = rs.getRow();    //结果记录集中的行数
			rs.beforeFirst();
			
			System.out.println("插入后现在学生表中共有 " + num + " 条记录");
			sql ="SELECT * from student where id='bj200205'";
			System.out.println("插入的学生信息为：");
			rs = stmt.executeQuery(sql);
			rs.next();
			String id = rs.getString(1);
			String name = rs.getString(2);
			String sex = rs.getString(3);
			int age = rs.getInt(4);
			String home = rs.getString(5);
			String department = rs.getString(6);
			int year = rs.getInt(7);
			System.out.println(id + "," + name + "," +sex+ "," + age + "," +
			                   home + "," + department + "," + year);
							   		
			
			//2---删除
			sql = "DELETE FROM student WHERE id='bj200205'";
			i = stmt.executeUpdate(sql);		
			System.out.println("删除语句影响了" + i + " 行!");
			sql = "SELECT * FROM student";
			rs = stmt.executeQuery(sql);
			
			rs.last();
			num = rs.getRow();    //结果记录集中的行数
			rs.beforeFirst();
			
			System.out.println("删除后现在学生表中共有 " + num + " 条记录");
			
			//3---update 语句 ---计算机系女生的年龄加1岁
			
			sql = "SELECT name,age FROM student WHERE sex='女' AND department='计算机'";
			System.out.println("更新前计算机系女生的姓名和年龄为:");
			rs = stmt.executeQuery(sql);
			while (rs.next())
			{
				name = rs.getString(1);
				age = rs.getInt(2);
				System.out.println("姓名: " + name + ",年龄: " + age);
			}
			
			System.out.println("年龄加1岁,更新后计算机系女生的姓名和年龄为:");
			sql = "UPDATE student set age=age+1 WHERE sex='女' AND department='计算机'";
			i = stmt.executeUpdate(sql);				
			sql = "SELECT name,age FROM student WHERE sex='女' AND department='计算机'";
			
			rs = stmt.executeQuery(sql);		
			while (rs.next())
			{
			    name = rs.getString(1);
				age = rs.getInt(2);
				System.out.println("姓名: " + name + ",年龄: " + age);
			}
			System.out.println("更新语句影响了" + i + " 行!");
			rs.close();
			//update 语句 ---计算机系女生的年龄减1岁
			sql = "UPDATE student set age=age-1 WHERE sex='女' AND department='计算机'";
			stmt.executeUpdate(sql);		
			//关闭创建的对象
			
			stmt.close();
			conn.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());
	}
  }		
}