jdbc练习

用户登录后，实现如下功能：
1.输入姓名、性别…等添加学生
2.输入学号删除学生
3.输入每页的行数，当前页码，输出本页数据
源代码：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;

public class StudentManager {
	Scanner input=new Scanner(System.in);
	public Connection getConnection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@//localhost:1521/ORCL";
			String user = "scott";
			String password = "123";
			Connection conn = DriverManager.getConnection(url, user, password);
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("连接出错:"+e.getMessage());
		} 
	}
	
	
	public void login() {
		Scanner input=new Scanner(System.in);
		System.out.println("请输入用户名：");
		String uname=input.nextLine();
		System.out.println("请输入密码：");
		String upwd=input.nextLine();
		String sql="select * from user_info where uname=? and upwd=?";;
		try {			
			Connection conn=getConnection();
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1, uname);
			ps.setString(2, upwd);
			ResultSet rs=ps.executeQuery();
			boolean flag=rs.next();
			rs.close();
			conn.close();
			if(flag) {
				menu();
			}else {
				System.out.println("用户名或密码错误，请重新登录");
				login();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("执行"+sql+"出错:"+e.getMessage());
		}
		
	}
	
	public void menu() {
		System.out.println("1--添加学生信息");
		System.out.println("2--删除学生信息");
		System.out.println("3--分页查询学生信息");
		System.out.print("请选择:");
	    switch(input.next()) {
	    case "1":
	    	addStudnet();
	    	break;
	    case "2":
	    	removeStudent();
	    	break;
	    case "3":
	    	queryStudent();
	    	break;
	    default:
	    		System.out.println("您的输入有误");
	    		break;
	    }
	    System.out.println("是否继续<y/n>");
	    if(input.next().equalsIgnoreCase("y")) {
	    	menu();
	    }else {
	    	System.out.println("系统即将退出");
	    	System.exit(0);
	    }
	}


	private void queryStudent() {
		Connection conn=getConnection();
		String sql="select * from(";
		   sql+=" select t.* ,rownum as rn from(";
		   sql+=" select * from student order by stuid";
		   sql+=")t";
		   sql+=")t1 where t1.rn between ? and ?";
		   System.out.println("请输入每页的行数");
		   int pagesize=input.nextInt();
		   System.out.println("请输入当前页码");
		   int pageindex=input.nextInt();
	    try {
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, pagesize*(pageindex-1)+1);
			ps.setInt(2, pagesize*pageindex);
			ResultSet rs=ps.executeQuery();
			System.out.println("第"+pageindex+"页的数据");
			while(rs.next()) {
				System.out.println(rs.getString("stuname")+"\t"+rs.getString("gender")+"\t"+rs.getString("phone")+"\t"+rs.getDate("borndate")+"\t"+rs.getString("address"));
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("执行"+sql+"出错:"+e.getMessage());
		}
	}


	private void removeStudent() {
		System.out.println("请输入要删除的学号");
		int stuid=input.nextInt();
		String name=getName(stuid);
		if(name==null) {
			System.out.println("该学生不存在");
		}else {
			System.out.println("你确定要删除学号是"+stuid+"  姓名是"+name+"的学生吗?<y/n>");
			if(input.next().equalsIgnoreCase("y")) {
				String sql="delete from student where stuid=?";
				try {
					Connection conn=getConnection();					
					PreparedStatement ps=conn.prepareStatement(sql);
					ps.setInt(1, stuid);
					int rows=ps.executeUpdate();
					ps.close();
					conn.close();
					if(rows>0) {
						System.out.println("删除成功");
					}else {
						System.out.println("删除失败");
					}
				} catch (SQLException e) {
					e.printStackTrace();
					throw new RuntimeException("执行"+sql+"出错:"+e.getMessage());
				}
			}
		}
		
	}
	
	private String getName(int stuid) {
		Connection conn=getConnection();
		String sql="select stuname from student where stuid=?";
		try {
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, stuid);
			ResultSet rs=ps.executeQuery();
			String name=null;
			if(rs.next()) {
				name=rs.getString("stuname");
			}
			rs.close();
			ps.close();
			conn.close();
			return name;
		} catch (SQLException e) {			
			e.printStackTrace();
			throw new RuntimeException("执行"+sql+"出错:"+e.getMessage());
		}
	}


	private void addStudnet() {
		String sql="";
		try {
			System.out.println("请输入学生姓名");
			String name=input.next();
			System.out.println("请输入学生性别");
			String gender=input.next();
			//System.out.println("请输入学生出生日期");
			//String bornstr=input.next();
			Date borndate=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2005-5-15 14:35:26");
			System.out.println("请输入学生电话号码");
			String phone=input.next();
			System.out.println("请输入学生地址");
			String address=input.next();
			
			Connection conn=getConnection();
			sql="insert into student values(seq_student_stuid.nextval,?,?,?,?,?)";
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, gender);
			ps.setString(3, phone);
			//ps.setDate(4, new java.sql.Date(borndate.getTime()));
			ps.setTimestamp(4, new Timestamp(borndate.getTime()));
			ps.setString(5, address);
			int rows=ps.executeUpdate();
			ps.close();
			if(rows>0) {
				String sql1="select seq_student_stuid.currval as seq from dual";
				PreparedStatement ps1=conn.prepareStatement(sql1);
				ResultSet rs=ps1.executeQuery();
				if(rs.next()) {
					int stuid=rs.getInt("seq");
					System.out.println("添加成功，请牢记你的学号:"+stuid);
				}
				rs.close();
				ps1.close();
				conn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("执行"+sql+"出错:"+e.getMessage());
		} 
	}
	
}

执行：

public class Start {
	public static void main(String[] args) {
		new StudentManager().login();
	}
}