Java学习之路-第七周小结-下

day33、封装BaseDao

通过自己封装的类，对数据库进行增删改查

1.先复制JdbcUtil这个工具类到咱们的utils文件夹中
2.复制db.properties到src文件夹下面
3.在src下面新建lib文件夹 将mysqljar包导入当前工程中

BaseDao代码如下：

public class BaseDao {
    public int updata(String sql, Object[] objs){
        //创建数据库连接
        Connection connection = JdbcUtil.getConnection();
        //预处理搬运工对象
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            //处理sql参数
            //要获取参数元数据
            //处理参数
            int count = preparedStatement.getParameterMetaData().getParameterCount();
            if(objs != null && count == objs.length){
                for (int i = 1; i <= count; i++) {
                    preparedStatement.setObject(i, objs[i-1]);
                }
            }
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(preparedStatement, connection);
        }
        return 0;
    }

    public <T> List<T> query (String sql, Object[] objs, Class<T> cls){
        //获取数据库连接
        Connection connection = JdbcUtil.getConnection();
        //获得预处理搬运工对象
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //新建一个返回的集合
        List<T> list = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            //获得参数元数据
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
            //获得参数个数
            int parameterCount = parameterMetaData.getParameterCount();
            //处理sql
            if(objs != null && parameterCount == objs.length){
                for (int i = 1; i <= parameterCount; i++) {
                    preparedStatement.setObject(i, objs[i-1]);
                }
            }
            //执行，获得结果集
            resultSet = preparedStatement.executeQuery();

            //获得结果集元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while(resultSet.next()){
                T t = cls.getDeclaredConstructor(null).newInstance(null);
                for (int i = 1; i <= columnCount ; i++) {
                    String columnName = metaData.getColumnName(i);
                    System.out.println(columnName);
                    System.out.println(resultSet.getObject(columnName));
                    BeanUtils.setProperty(t, columnName, resultSet.getObject(columnName));
                }
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(resultSet, preparedStatement, connection);
        }
        return list.size() != 0 ? list: null;
    }
}

day34、连接池

1、连接池

在代码中使用连接池的步骤

1、导包 druid的jar

2、在src下面的去新建一个druid.properties配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java2304?useSSL=false
username=root
password=123456
initialSize=5
maxActive=10
maxWait=2000

3、写核心类

//读取druid.properties
Properties properties = new Properties();
//以IO流的方式，读取配置信息
properties.load(new FileInputStream("src/druid.properties"));
//创建Druid的核心类, druid数据源的工厂
//DataSource  数据源中  是获取配置文件的信息，并赋值给数据源
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//获取连接数据库的对象
Connection connetion = dataSource.getConnetion();

4、将之前封装的JdbcUtil改成连接池的写法

public class JdbcUtil {
    private static DataSource dataSource = null;
    static {
        try {
            //Properties类表示一组持久的属性
            Properties properties = new Properties();
            //读取Druid.properties
            properties.load(new FileInputStream("src/Druid.properties"));
            //dataSource 数据源工厂，获取了配置文件中的数据，并赋值给数据源
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    public static void close (Connection connection) {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void close (Statement statement, Connection connection) {
        try {
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void close (ResultSet resultSet, Statement statement, Connection connection) {
        try {
            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2、JDBC中的事务

对应sql语句中的

set autocommit = 0;

commit;

rollback;

 //1.关闭自动提交
connection.setAutoCommit(false);

//提交方法
connection.commit();
//回滚方法
connection.rollback();

3、DBUtils框架

是轻量级持久层框架，apache组织创建的一个框架，已经没人维护了。

使用步骤

1.导包 dbUtils.jar

2.直接使用核心类 QueryRunner 即可

主要代码如下：

//查询 
public static void main(String[] args) throws SQLException {
    //连接数据库
    Connection connection = JdbcUtil.getConnection();
    //DBUtils核心类  QueryRunner
    QueryRunner queryRunner = new QueryRunner();
    String sql = "select * from examStudent";
    
    List<Examstudent> query = queryRunner.query(connection, sql, new BeanListHandler<Examstudent>(Examstudent.class));
    System.out.println(query);
}

//更新
public static void main(String[] args) throws SQLException {
    Connection connection = JdbcUtil.getConnection();
    QueryRunner queryRunner = new QueryRunner();
    String sql = "insert into work (name, age, info) values(?, ?, ?)";
    //update(Connection conn, String sql, Object... params)
    int update = queryRunner.update(connection, sql, "小猫", 56, "南山南");//
    System.out.println(update);

}