JDBC - 02.怎么实现增删改查（Create Retrieve Update Delete，CRUD）

1、ORM（Object Relational Mapping）思想：

• 数据库中的一张表对应一个Java类
• 表中的一条记录对应Java类的一个对象
• 表中的一个字段对应Java类的一个属性

2、查询时必须用Java类中的属性名为字段命名别名。
3、PreparedStatement与Statement的区别：

import org.junit.Test;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class PreparedStatementTest {
    @Test
    public void updateOption() {
        //增
        String insertSQL = "insert into country set code=?,name=?";
        int insertColumnCount = JDBCUtils.generalUpdate(insertSQL, "AAA", "AAA");
        System.out.println(insertColumnCount);

        //删
        String deleteSQL = "delete from country where code=?";
        int deleteColumnCount = JDBCUtils.generalUpdate(deleteSQL, "AAA");
        System.out.println(deleteColumnCount);

        //改
        String updateSQL = "update country set name=? where code=?";
        int updateColumnCount = JDBCUtils.generalUpdate(updateSQL, "aruba", "ABW");
        System.out.println(updateColumnCount);
    }

    @Test
    public void queryOption() {
        //查
        String querySQL1 = "select code code,name name,continent continent,surfaceArea surfaceArea,population population from country where population>? order by population desc";
        List<Country> countries = JDBCUtils.generalQuery(Country.class, querySQL1, 100000000);
        for (Object obj : countries) {
            System.out.println(obj);
        }
        String querySQL2 = "select countryCode countryCode,language language,percentage percentage from countryLanguage where percentage>? order by language,percentage desc";
        List<CountryLanguage> countryLanguages = JDBCUtils.generalQuery(CountryLanguage.class, querySQL2, 50);
        for (Object obj : countryLanguages) {
            System.out.println(obj);
        }
    }
}

/**
 * JDBC工具类
 */
class JDBCUtils {
    /**
     * 获取数据库的连接
     *
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        //1、读取配置文件中的基本信息
        InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties properties = new Properties();
        properties.load(inputStream);

        String driverClass = properties.getProperty("driverClass");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

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

        //3、返回数据库的连接
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 关闭增删改资源
     *
     * @param connection
     * @param preparedStatement
     */
    public static void closeResource(Connection connection, PreparedStatement preparedStatement) {
        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
    }

    /**
     * 关闭查询资源
     *
     * @param connection
     * @param preparedStatement
     * @param resultSet
     */
    public static void closeResource(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
    }

    /**
     * 通用的增删改操作
     *
     * @param sql
     * @param args
     * @return
     */
    public static int generalUpdate(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1、连接数据库
            connection = JDBCUtils.getConnection();
            //2、预编译SQL语句
            preparedStatement = connection.prepareStatement(sql);
            //3、填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            //4、执行增删改操作，返回增删改操作影响的行数
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5、关闭资源
            JDBCUtils.closeResource(connection, preparedStatement);
        }
        return 0;
    }

    /**
     * 通用的查询操作
     *
     * @param tClass
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public static <T> List<T> generalQuery(Class<T> tClass, String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //1、连接数据库
            connection = JDBCUtils.getConnection();
            //2、预编译SQL语句
            preparedStatement = connection.prepareStatement(sql);
            //3、填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            //4、执行查询操作，获取结果集
            resultSet = preparedStatement.executeQuery();
            //5、通过结果集，获取元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //6、通过元数据，获取结果集的列数
            int columnCount = metaData.getColumnCount();
            ArrayList<T> list = new ArrayList<>();
            while (resultSet.next()) {
                T t = tClass.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    //7、通过结果集，获取每个列的值
                    Object value = resultSet.getObject(i + 1);
                    //8、通过元数据，获取每个列的别名
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    //9、通过反射，给泛型类T的对象的对应属性赋值
                    Field field = tClass.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, value);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //10、关闭资源
            JDBCUtils.closeResource(connection, preparedStatement, resultSet);
        }
        return null;
    }
}

class Country {
    private String code;
    private String name;
    private String continent;
    private double surfaceArea;
    private int population;

    public Country() {

    }

    public Country(String code, String name, String continent, double surfaceArea, int population) {
        this.code = code;
        this.name = name;
        this.continent = continent;
        this.surfaceArea = surfaceArea;
        this.population = population;
    }

    @Override
    public String toString() {
        return "Country{" +
                "code='" + code + '\'' +
                ", name='" + name + '\'' +
                ", continent='" + continent + '\'' +
                ", surfaceArea=" + surfaceArea +
                ", population=" + population +
                '}';
    }
}

class CountryLanguage {
    private String countryCode;
    private String language;
    private double percentage;

    public CountryLanguage() {

    }

    public CountryLanguage(String countryCode, String language, double percentage) {
        this.countryCode = countryCode;
        this.language = language;
        this.percentage = percentage;
    }

    @Override
    public String toString() {
        return "CountryLanguage{" +
                "countryCode='" + countryCode + '\'' +
                ", language='" + language + '\'' +
                ", percentage=" + percentage +
                '}';
    }
}