博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

Java数据库操作的一些注意

Posted on 2019-10-22 22:25  淼哥学习园地  阅读(189)  评论(0编辑  收藏  举报

练习一:插入一个学生的四六级成绩:

//课后练习 
public class task3 {
	
	//添加一条数据
	@Test
	public void testInsert()
	{
		int id=1;
		Scanner scanner =new Scanner(System.in);
		System.out.println("四季/六级");
		int type=scanner.nextInt();
		
		System.out.println("身份证号");
		String idCard = scanner.next();
		System.out.println("准考证号");
		String examCard=scanner.next();
		
		System.out.println("学生姓名:");
		String name=scanner.next();
		
		System.out.println("学生成绩:");
		int grade=scanner.nextInt();
		
		String sql="insert into examstudent(flowId,type,idCard,examCard,name,Grade)values(?,?,?,?,?,?)"; //
	      int insertCount=update(sql,id++,type,idCard,examCard,name,grade); //根据返回值判断是否插入成功
		if(insertCount>0)
		{
			System.out.println("插入成功");
		}
		else{
			System.out.println("插入失败");
		}
	}
	
	//插入数据函数
	public int  update(String sql,Object ...args)
	{
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			 conn=JDBCUtils.getConnction5();
			
			 ps=(PreparedStatement) conn.prepareStatement(sql);
			for(int i=0;i<args.length;i++){
				ps.setObject(i+1, args[i]);
			}
			
			return  ps.executeUpdate();  //返回影响了多少条数据
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtils.closeResource(conn, ps);
		}
		
		return 0;
	}
}

  

作业二:四六级成绩查询

@Test
	public void queryWithId()
	{
		System.out.println("请选择您要输入的类型:");
		System.out.println("a.准考证号");
		System.out.println("b.身份证号");
		Scanner scanner=new Scanner(System.in);
		String slection =scanner.next();
		if("a".equalsIgnoreCase(slection))
		{
			System.out.println("请输入注考证号:");
			String examCard=scanner.next();
			
			String sql="select flowId flowId,idCard IDCard,examCard examCard,name name,Grade grade from examstudent where examCard=?"; //为每个字段取别名,别名需要和Student对象中的数据类型一致
			Student student=getInstance(Student.class, sql, examCard);
					System.out.println(student);
			
		}else if("b".equalsIgnoreCase(slection))
		{
			System.out.println("请输入身份证号:");
			String Idcard=scanner.next();
			
			String sql="select flowId flowId,idCard IDCard,examCard examCard,name name,Grade grade from examstudent where examCard=?";
			Student student=getInstance(Student.class, sql, Idcard);   //调用通用查询接口
					System.out.println(student);
		}
		else
		{
			System.out.println("您输入的数据有误,请重新输入!");
		}
		
	}
	
	//通用查询的函数
public <T> T getInstance(Class<T> clazz,String sql,Object ...args){
		
		Connection conn =null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try {
			conn=JDBCUtils.getConnction5();
			ps=(PreparedStatement) conn.prepareStatement(sql);
			
			for(int i=0;i<args.length;i++)
			{
				ps.setObject(i+1,args[i]);
			}
			
			rs=ps.executeQuery();
			
			ResultSetMetaData rsmd=(ResultSetMetaData) rs.getMetaData();
			int columnCount=rsmd.getColumnCount();
			
			if(rs.next()){
				T t=clazz.newInstance();  //反射
				
				for(int i=0;i<columnCount;i++){
					Object columValue=rs.getObject(i+1);
					String columnLable1=rsmd.getColumnLabel(i+1);
					
					java.lang.reflect.Field field=clazz.getDeclaredField(columnLable1);
					field.setAccessible(true);
					field.set(t,columValue);
				}
				
				return t;
			
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
	}
	
	
}