JDBC优化

一、单元测试 Junit

1.概述

1. 定义：单元测试是针对最小的功能单元做测试代码用的，最小的功能单元是方法，单元测试就是针对于方法的测试。

2. 为什么要使用单元测试：java 中只能有一个 main方法。

3. 使用单元测试的优点：能够让单个方法顺利执行。

2.使用

1. 使用注解 @Test 直接放在方法上

   @Test
   public void testJunit(){
       System.out.println("-----");
   }

2. 添加 junit 依赖到classpath

二、优化 JDBC 配置

1.反射回顾

• 使用反射操作配置文件给类赋值（set get）

  • 配置文件 properties

  • 使用反射获取配置文件中的内容

  • 可以设置到相应的实体

2.优化 JDBC 配置

2.1新建配置文件 db.properties

2.2获取 db.properties 中的内容

InputStream in = Env.class.getClassLoader().getResourceAsStream("db.properties");

2.3 建立获取连接的工具类

public static Connection getConnections() {
//        FileInputStream in = new FileInputStream("D:\\JDBC\\jdbc\\jdbc_day03\\src\\cn\\sycoder\\config\\db.properties");
//        InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("cn\\sycoder\\config\\db.properties");
        InputStream in = Env.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        try {
            properties.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            Class.forName(properties.getProperty("driverClassName"));
            Connection connection = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
            return connection;
        } catch (Exception e) {
            e.printStackTrace();
        }
        throw new RuntimeException("连接获取异常");

    }

public static void close(PreparedStatement statement, Connection connections, ResultSet resultSet){
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connections != null){
            try {
                connections.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

三、JDBC操作CRUD

1.建用户表

create table user_test
(
	id bigint auto_increment,
	name varchar(64) null,
	age int null comment '年龄',
	address varchar(128) null,
	sex char null,
	constraint user_test_pk
		primary key (id)
);

2.查询用户表中的数据

/*无条件查询*/
    public List<User> queryList(){
        //获取连接
        Connection connections = DbUtil.getConnections();
        String sql = "select * from user_test";
        //返回对象
        List<User> list = new ArrayList<>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //获取预编译语句
            statement = connections.prepareStatement(sql);
            //执行查询
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                long id = resultSet.getLong(1);
                String name = resultSet.getString(2);
                int age = resultSet.getInt(3);
                String address = resultSet.getString(4);
                String sex = resultSet.getString(5);

                User user = new User(id,name, age,address, sex);
                list.add(user);
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DbUtil.close(statement,connections,resultSet);
        }
        return list;
    }

3.向数据库中添加数据

/*插入用户数据*/
    public int insert(User user){
        String sql = "insert into user_test(name,age,address,sex) values(?,?,?,?)";
        Connection connections = DbUtil.getConnections();
        PreparedStatement statement = null;
        try {
            statement = connections.prepareStatement(sql);
            statement.setString(1,user.getName());
            statement.setInt(2,user.getAge());
            statement.setString(3,user.getAddress());
            statement.setString(4,user.getSex());

            int count = statement.executeUpdate();
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbUtil.close(statement,connections,null);
        }
        throw new RuntimeException("插入失败");
    }

4.jdbc 修改如下信息

/*修改：将编号2的地址改为成都，编号3的年龄增加1岁*/
    public int update(User user){
        String sql = "update user_test set name = ?,age = ?,address = ?,sex = ? where id = ?";
        Connection connections = DbUtil.getConnections();
        PreparedStatement statement = null;
        try {
            statement = connections.prepareStatement(sql);
            statement.setString(1,user.getName());
            statement.setInt(2,user.getAge());
            statement.setString(3,user.getAddress());
            statement.setString(4,user.getSex());
            statement.setLong(5,user.getId());

            int count = statement.executeUpdate();
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbUtil.close(statement,connections,null);
        }
        throw new RuntimeException("更新失败");
    }

5.删除用户信息

 /*删除数据*/
    public int delete(long id){
        String sql = "delete from user_test where id =?";
        Connection connections = DbUtil.getConnections();
        PreparedStatement statement = null;
        try {
            statement = connections.prepareStatement(sql);
            statement.setLong(1,id);

            int count = statement.executeUpdate();
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbUtil.close(statement,connections,null);
        }
        throw new RuntimeException("删除失败");
    }