查询

前提准备
包结构：

========================
JDBC 操作的工具类：

package com.atguigu.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * JDBC 操作的工具类
 */
public class JdbcUtils {

    public static void close(ResultSet resultSet){
        try {
            if(resultSet != null){
                resultSet.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //关闭数据库资源
    public static void close(Connection connection){
        try {
            if(connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Statement statement){
        try {
            if(statement != null){
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //根据配置文件获取数据库连接
    public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException{
        Connection connection = null;

        //0. 读取 Properties 文件
        Properties properties = new Properties();
        InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        properties.load(in);

        //1. 准备连接数据库的四个基本信息: 
        String driverClassName = properties.getProperty("jdbc.driverClass");
        String url = properties.getProperty("jdbc.jdbcUrl");
        String user = properties.getProperty("jdbc.user");
        String password = properties.getProperty("jdbc.password");

        //2. 加载驱动
        Class.forName(driverClassName);

        //3. 调用 DriverManager.getConnection(url, user, password) 获取连接
        connection = DriverManager.getConnection(url, user, password);

        return connection;
    }

}

========================================
JDBC连接数据库相关信息配置文件/jdbc-1/src/jdbc.properties：

jdbc.user=root
jdbc.password=root
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://127.0.0.1:3306/jdbc1


#jdbc.user=scott
#jdbc.password=tiger
#jdbc.driverClass=oracle.jdbc.driver.OracleDriver
#jdbc.jdbcUrl=jdbc:oracle:thin:@127.0.0.1:1521:ORCL001

=======================================
数据库表信息：
user表

product表：

==================================
实体信息：
User.java

package com.atguigu.jdbc;

public class User {

    private Integer id;
    private String username;
    private String password;

    public User() {
        // TODO Auto-generated constructor stub
    }

    public User(Integer id, String username, String password) {
        super();
        this.id = id;
        this.username = username;
        this.password = password;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", password="
                + password + "]";
    }
}

Product.java：

package com.atguigu.jdbc;

import java.util.Date;

public class Product {

    private Integer id;
    private String name;

    private String desc;
    private Date createDate;

    public Product() {
        // TODO Auto-generated constructor stub
    }

    public Product(Integer id, String name, String desc, Date createDate) {
        super();
        this.id = id;
        this.name = name;
        this.desc = desc;
        this.createDate = createDate;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDesc() {
        return desc;
    }

    public void setDesc(String desc) {
        this.desc = desc;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

    @Override
    public String toString() {
        return "Product [id=" + id + ", name=" + name + ", desc=" + desc
                + ", createDate=" + createDate + "]";
    }
}

========================================
//第一种实现方式：查询数据表里的数据并打印出来
@Test
    public void testResultSet(){
        String sql = "SELECT id, username, password FROM users";

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            //执行查询, 将返回 ResultSet 对象
            resultSet = preparedStatement.executeQuery();

            //处理结果集
            //1. ResultSet#next() 有两个作用: 侦测结果集是否有下一条记录, 若有则
            //注意: 指针最开始时指向第一条记录的前面. 
            //1.1 返回 true
            //1.2 指针向下移动一个单位
            int i=1;
            while(resultSet.next()){
                System.out.println("第"+i+"条结果：");
                //2. 获取每一行中具体列的值. 调用 ResultSet#getXxx() 方法
                int id = resultSet.getInt(1);
                String username = resultSet.getString("username");
                String password = resultSet.getString(3);

                System.out.println(id + "," + username + "," + password);
                 i++;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            //3. 关闭结果集
            JdbcUtils.close(resultSet);
            JdbcUtils.close(preparedStatement);
            JdbcUtils.close(connection); 
        }
    }
==================================================
//第二种实现方式：实体封装数据表数据，查询所有记录
//编写获取数据表所有记录的方法
/**
     * 一般地, 
     * 数据表 对应 一个 Java 类.
     * 数据表的一条记录 对应 一个 Java 类的对象
     * 数据表的一列 对应 一个 Java 类的属性
     * 
     * ORM: Object Relation Mapping
     */
    public List<User> getAllUsers(){
        List<User> users = new ArrayList<>();

        String sql = "SELECT id, username, password FROM users";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            //执行查询, 将返回 ResultSet 对象
            resultSet = preparedStatement.executeQuery();

            //处理结果集
            //1. ResultSet#next() 有两个作用: 侦测结果集是否有下一条记录, 若有则
            //注意: 指针最开始时指向第一条记录的前面. 
            //1.1 返回 true
            //1.2 指针向下移动一个单位
            while(resultSet.next()){
                //2. 获取每一行中具体列的值. 调用 ResultSet#getXxx() 方法
                int id = resultSet.getInt(1);
                String username = resultSet.getString("username");
                String password = resultSet.getString(3);

                User user = new User(id, username, password);
                users.add(user);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            //3. 关闭结果集
            JdbcUtils.close(resultSet);
            JdbcUtils.close(preparedStatement);
            JdbcUtils.close(connection); 
        }

        return users;
    }
//测试
@Test
    public void testGetAllUsers(){
        System.out.println(getAllUsers());
    }
======================================
//第三种实现方式：实体封装数据表数据，查询一条记录
//根据id获取到user表里的一条记录的方法
public User getUserById(Integer id){
        String sql = "SELECT id, username, password FROM users WHERE id = ?";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);

            //执行查询, 将返回 ResultSet 对象
            resultSet = preparedStatement.executeQuery();

            //处理结果集
            //1. ResultSet#next() 有两个作用: 侦测结果集是否有下一条记录, 若有则
            //注意: 指针最开始时指向第一条记录的前面. 
            //1.1 返回 true
            //1.2 指针向下移动一个单位
            if(resultSet.next()){
                //2. 获取每一行中具体列的值. 调用 ResultSet#getXxx() 方法
                String username = resultSet.getString("username");
                String password = resultSet.getString(3);

                User user = new User(id, username, password);
                return user;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            //3. 关闭结果集
            JdbcUtils.close(resultSet);
            JdbcUtils.close(preparedStatement);
            JdbcUtils.close(connection); 
        }

        return null;
    }
//测试
@Test
    public void testGetById(){
        User user = getUserById(50);
        System.out.println(user);
}
//根据id获取到product表里的一条记录的方法
public Product getProductById(Integer id){
        String sql = "SELECT id, name, description, create_date FROM products WHERE id = ?";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);

            resultSet = preparedStatement.executeQuery();

            if(resultSet.next()){
                String name = resultSet.getString("name");
                String desc = resultSet.getString("description");
                Date createDate = resultSet.getDate("create_date");

                Product product = new Product(id, name, desc, createDate);
                return product;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            //3. 关闭结果集
            JdbcUtils.close(resultSet);
            JdbcUtils.close(preparedStatement);
            JdbcUtils.close(connection); 
        }

        return null;
    }
//测试
@Test
    public void testGetById(){
        Product product = getProductById(1);
        System.out.println(product);
}
======================================================
//第四种实现方式：实体封装数据表数据，查询一条记录
//编写通用的 根据 id 获取对象的方法.
    public <T> T getById(String sql, Integer id, Class<T> clazz){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);

            resultSet = preparedStatement.executeQuery();

            if(resultSet.next()){
                //该创建什么类型的对象呢 ? 该如何把查询的列的值赋给该对象对应的属性呢 ?
                //若数据表的列和类的属性不一致, 则可以使用别名的方式进行映射.
                //所以只需要为列的别名对应的属性赋值就可以了. 
                T result = clazz.newInstance();

                //如何知道查询哪些列了呢 ? 如何来获取每个列的值呢 ?
                //可以通过调用 ResultSet#getMetaData() 方法来获取 ResultSetMetaData 对象
                //ResultSetMetaData 对象中即包含了描述 ResultSet 的数据. 例如有哪些列
                ResultSetMetaData metaData = resultSet.getMetaData();

                //获取有多少列.
                int columnCount = metaData.getColumnCount();
                for(int i = 0; i < columnCount; i++){
                    //可以调用 ResultSetMetaData#getColumnName() 获取了具体列的名字
                    String columnName = metaData.getColumnName(i + 1);
//                  System.out.println(columnName);

                    //可以调用 ResultSetMetaData#getColumnName() 获取了具体列的别名
                    String columnLabel = metaData.getColumnLabel(i + 1);
//                  System.out.println(columnLabel);

                    //获取列的值的时候, 需要传入的事列的别名
                    Object columnValue = resultSet.getObject(columnLabel);

                    //为 object 的 columnLabel 属性赋值为 columnValue.
                    //可以使用反射. 也可以调用工具类的工具方法
                    PropertyUtils.setProperty(result, columnLabel, columnValue);
                }

                //返回
                return result;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            //3. 关闭结果集
            JdbcUtils.close(resultSet);
            JdbcUtils.close(preparedStatement);
            JdbcUtils.close(connection); 
        }

        return null;
    }
//测试
@Test
    public void testGetById(){

        String sql = "SELECT id, name, description as "desc", create_date as "createDate" "
                + "FROM products WHERE id = ?";
        Object object = getById(sql, 1, Product.class);
        System.out.println(object);

        sql = "SELECT id, username, password FROM users WHERE id = ?";
        User user = getById(sql, 1, User.class);
        System.out.println(user);
}

未完待续！