JDBC2 四种连接方式

package TEST_BYME.day02;

import TEST_BYME.day01.entity.StoreDetails;
import TEST_BYME.day02.utils.JDBCUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;



import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @Description: TODO
 * @author: azzhu
 * @date:2021/6/24 9:10
 */
public class TestDBUtils {

    //
    @Test
    public void test05() throws SQLException {
        //1.不传参数
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();

        //2.执行DML
        String sql = "select store_name,count(*) cnt from store_details group by store_name";
        //n行m列 n>1 m>1  MapListHandler
        //n行m列 n=1,m>1 MapHandler

        List<Map<String, Object>> list = queryRunner.query(conn, sql, new MapListHandler());

        System.out.println(list);

        DbUtils.closeQuietly(conn);
    }

    @Test
    public void test04() throws SQLException {
        //1.不传参数
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();

        //2.执行DML
        String sql = "select count(store_id) from store_details";   //一行一列 int long
        Object query = queryRunner.query(conn, sql, new ScalarHandler<>());

        //todo 期望是将Object转为 int long
        Long result = (Long)query;

        System.out.println(query);
        System.out.println(result);

        System.out.println(result.intValue());

        DbUtils.closeQuietly(conn);
    }

    @Test
    public void test03() throws SQLException {
        //1.不传参数
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();

        //2.执行DML
        String sql = "update store_details set store_name=? where store_id = ?";
        int result = queryRunner.update(conn, sql, "fdfsdfsdfdsf", "100");

        DbUtils.closeQuietly(conn);
    }


    @Test
    public void test02() throws SQLException {
        //1.不传参数
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();

        //2.执行查询
        String sql = "select store_id as storeId,store_name storeName from store_details where store_id = ?";
        StoreDetails storeDetails = queryRunner.query(conn, sql,
                new BeanHandler<>(StoreDetails.class),"1");

        System.out.println(storeDetails);

        DbUtils.closeQuietly(conn);
    }

    @Test
    public void test01() throws SQLException {
        //1.不传参数
        QueryRunner queryRunner = new QueryRunner();

        //2.执行查询
        String sql = "select store_id as storeId from store_details";
        List<StoreDetails> list = queryRunner.query(JDBCUtils.getConn(), sql, new BeanListHandler<>(StoreDetails.class));

        for (StoreDetails storeDetails : list) {
            System.out.println(storeDetails);
        }
    }
}

utils包

package TEST_BYME.day02.utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

/**
 * @Description: TODO
 * @author: azzhu
 * @date:2021/6/23 16:55
 */
public class JDBCUtils {
    /*
    * 只需要赋值一次，jdbc.properties配置文件只需要读取一次即可
    **/
    private static String url;
    private static String user;
    private static String pwd;
    private static String driver;

    static {
        //1.读取到配置文件
        try {
           // FileReader fr = new FileReader("src/jdbc.properties");

            //todo 直接从类路径【xx.class编译之后的那个路径】下加载资源
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");

            //2.根据key获取配置文件中的value，为上面的类变量赋值
            Properties properties = new Properties();
            properties.load(is);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            pwd = properties.getProperty("pwd");

            //todo 加载驱动类，虽然DriverManager 已经帮我们注册好了  DriverManager.registerDriver(new Driver());
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 获取连接
     * @return
     */
    public static Connection getConn() {
        Connection connection = null;
        try{
            connection = DriverManager.getConnection(url, user, pwd);
        }catch (Exception e) {
            e.printStackTrace();
        }
         return connection;
    }

    /**
     * 释放资源  目前这个方法非线程安全  ThreadLocal 使用方式类似map，可以实现线程安全
     * @param conn
     * @param rs
     * @param ps
     */
    public static void release(Connection conn, ResultSet rs, PreparedStatement ps) {
        try {
            if(rs != null) {
                rs.close();
            }

            if(ps != null) {
                ps.close();
            }

            if(conn != null) {
                conn.close();
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
    }
}