JDBC-元数据

元数据

元数据

• 使用JDBC驱动程序处理元数据

  • 

• ResultSetMetaData类

  • 

  • /**
    	 * ResultSetMetaData: 描述结果集的元数据. 
    	 * 可以得到结果集中的基本信息: 结果集中有哪些列, 列名, 列的别名等.
    	 * 结合反射可以写出通用的查询方法. 
    	 */
    	@Test
    	public void testResultSetMetaData(){
    		Connection connection = null;
    		PreparedStatement preparedStatement = null;
    		ResultSet resultSet = null;
    		
    		try {
    			connection = (Connection) JDBCTools.getConnection();
    			String sql = "SELECT id, name customerName, email, birth " +
    					"FROM customers";
    			preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
    			resultSet = preparedStatement.executeQuery();
    
    			//1. 得到 ResultSetMetaData 对象
    			ResultSetMetaData rsmd = (ResultSetMetaData) resultSet.getMetaData();
    			
    			//2. 得到列的个数
    			int columnCount = rsmd.getColumnCount();
    			System.out.println(columnCount);
    			
    			for(int i = 0 ; i < columnCount; i++){
    				//3. 得到列名
    				String columnName = rsmd.getColumnName(i + 1);
    				
    				//4. 得到列的别名
    				String columnLabel = rsmd.getColumnLabel(i + 1);
    				
    				System.out.println(columnName + ", " + columnLabel);
    			}
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally{
    			JDBCTools.releaseDB(resultSet, preparedStatement, connection);
    		}
    	}
    

• DatabaseMetaData类

  • 

  • 
    	/**
      	 * DatabaseMetaData是描述数据库的元数据对象
      	 * 可以由Connection得到
      	 * 了解即可
      	 */
      	@Test
      	public void testDatabaseMetaData() {
      		Connection connection=null;
      		ResultSet resultSet=null;
      		
      		try {
      			connection=(Connection) JDBCTools.getConnection();
      			DatabaseMetaData data=(DatabaseMetaData) connection.getMetaData();
      			//可以得到数据库本身的一些基本信息
      			int version=data.getDatabaseMajorVersion();//得到数据库的版本号
      			System.out.println(version);
      			//得到链接数据库的用户名
      			String user=data.getUserName();
      			System.out.println(user);
      			//得到MySql中有哪些数据库
      			resultSet=data.getCatalogs();
      			while(resultSet.next()) {
      				System.out.println(resultSet.getString(1));
      			}
      		}catch(Exception e) {
      			e.printStackTrace();
      		}finally {
      			JDBCTools.releaseDB(resultSet,null, connection);
      		}
      	}
    

---

获取数据库自动生成的主键

• 

• /**
  	 * 取得数据库自动生成的主键
  	 */
  	@Test
  	public void testGetKeyValue() {
  		Connection connection=null;
  		PreparedStatement preparedStatement=null;
  		try {
  			connection=(Connection) JDBCTools.getConnection();
  			String sql = "INSERT INTO customers(name, email, birth)" +
  					"VALUES(?,?,?)";
  //			preparedStatement=(PreparedStatement) connection.prepareStatement(sql);
  
  			//使用重载的 prepareStatement(sql, flag) 
  			//来生成 PreparedStatement 对象
  			preparedStatement = (PreparedStatement) connection.prepareStatement(sql, 
  					Statement.RETURN_GENERATED_KEYS);
  
  			preparedStatement.setString(1, "ABCDE");
  			preparedStatement.setString(2, "abcde@atguigu.com");
  			preparedStatement.setDate(3, 
  					new Date(new java.util.Date().getTime()));
  			
  			preparedStatement.executeUpdate();
  			//通过 getGeneratedKeys() 获取包含了新生成的主键的 ResultSet 对象
  			//在 ResultSet 中只有一列 GENERATED_KEY, 用于存放新生成的主键值.
  			ResultSet rs = preparedStatement.getGeneratedKeys();
  			if(rs.next()){
  				System.out.println(rs.getObject(1));
  			}
  			ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
  			for(int i = 0; i < rsmd.getColumnCount(); i++){
  				System.out.println(rsmd.getColumnName(i + 1)); 
  			}
  		}catch(Exception e) {
  			e.printStackTrace();
  		}finally {
  			JDBCTools.releaseDB(null, preparedStatement, connection);
  		}
  	}
  

---

处理 Blob

• 

• 

• 插入 BLOB 类型的数据

  • /**
    	 * 插入 BLOB 类型的数据必须使用 PreparedStatement：
    	 * 因为 BLOB 类型的数据时无法使用字符串拼写的。
    	 * 
    	 * 调用 setBlob(int index, InputStream inputStream)
    	 */
    	@Test
    	public void testInsertBlob(){
    		Connection connection = null;
    		PreparedStatement preparedStatement = null;
    		
    		try {
    			connection = (Connection) JDBCTools.getConnection();
    			String sql = "INSERT INTO customers(name, email, birth, picture)" 
    					+ "VALUES(?,?,?,?)";
    			preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
    			
    			preparedStatement.setString(1, "ABCDE");
    			preparedStatement.setString(2, "abcde@atguigu.com");
    			preparedStatement.setDate(3, 
    					new Date(new java.util.Date().getTime()));
    			
    			InputStream inputStream = new FileInputStream("3.jpg");
    			preparedStatement.setBlob(4, inputStream);
    			
    			preparedStatement.executeUpdate();
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally{
    			JDBCTools.releaseDB(null, preparedStatement, connection);
    		}
    	}
    

• 读取 blob 数据

  • /**
    	 * 读取 blob 数据: 
    	 * 1. 使用 getBlob 方法读取到 Blob 对象
    	 * 2. 调用 Blob 的 getBinaryStream() 方法得到输入流。再使用 IO 操作即可. 
    	 */
    	@Test
    	public void readBlob(){
    		Connection connection = null;
    		PreparedStatement preparedStatement = null;
    		ResultSet resultSet = null;
    		
    		try {
    			connection = (Connection) JDBCTools.getConnection();
    			String sql = "SELECT id, name customerName, email, birth, picture " 
    					+ "FROM customers WHERE id = 13";
    			preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
    			resultSet = preparedStatement.executeQuery();
    			
    			if(resultSet.next()){
    				int id = resultSet.getInt(1);
    				String name = resultSet.getString(2);
    				String email = resultSet.getString(3);
    				
    				System.out.println(id + ", " + name  + ", " + email);
    				Blob picture = (Blob) resultSet.getBlob(5);
    				
    				InputStream in = picture.getBinaryStream();
    				System.out.println(in.available()); 
    				
    				OutputStream out = new FileOutputStream("flower.jpg");
    				
    				byte [] buffer = new byte[1024];
    				int len = 0;
    				while((len = in.read(buffer)) != -1){
    					out.write(buffer, 0, len);
    				}
    				
    				in.close();
    				out.close();
    			}
    			
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally{
    			JDBCTools.releaseDB(resultSet, preparedStatement, connection);
    		}
    	}