[大数据]2021-09-25

jdbc阶段总结——jdbc_stage_summary

首先使用JDK1.8新建一个项目

然后新建libs（依赖文件存放目录）

然后向libs文件夹下导入相应jar包

下一步：编写mysql所需properties配置文件和druid（德鲁伊）连接池所需配置文件

编写工具类Jdbc_druidUtil

package com.ccl.Utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author: 13749
 * @date: 2021/9/25 20:10
 * @description:
 */
public class JdbcUtil_Druid {
    //新建数据源
    //static代码块在类加载完成之后执行
    private static DataSource ds;
    //同理，类加载完成之后执行该静态代码快
    static{
        Properties properties = null;
        try {
            properties.load(new FileInputStream("src\\com\\ccl\\properties\\mysql.properties"));
            //创建数据工厂
            ds= DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接方法
    public static Connection connection () throws SQLException {
        return ds.getConnection();
    }
    //关闭方法
    public static void close(ResultSet set , Statement statement , Connection connection){
        try {
            if(set != null){
                set.close();
            }
            if(statement != null){
                statement.close();
            }
            if(connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

根据上面的数据库编写JavaBean（Dao）对象

package com.ccl.JavaBean;

import java.util.Date;

/**
 * @author: 13749
 * @date: 2021/9/25 20:30
 * @description:
 */
public class emp {
    private Integer id;
    private String ename;
    private Integer job_id;
    private String mgr;
    private Date joindate;
    private Double salary;
    private Double bonus;
    private Integer dept_id;

    public emp() {
    }

    public emp(Integer id, String ename, Integer job_id, String mgr, Date joindate, Double salary, Double bonus, Integer dept_id) {
        this.id = id;
        this.ename = ename;
        this.job_id = job_id;
        this.mgr = mgr;
        this.joindate = joindate;
        this.salary = salary;
        this.bonus = bonus;
        this.dept_id = dept_id;
    }

    public Integer getId() {
        return id;
    }

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

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public Integer getJob_id() {
        return job_id;
    }

    public void setJob_id(Integer job_id) {
        this.job_id = job_id;
    }

    public String getMgr() {
        return mgr;
    }

    public void setMgr(String mgr) {
        this.mgr = mgr;
    }

    public Date getJoindate() {
        return joindate;
    }

    public void setJoindate(Date joindate) {
        this.joindate = joindate;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public Double getBonus() {
        return bonus;
    }

    public void setBonus(Double bonus) {
        this.bonus = bonus;
    }

    public Integer getDept_id() {
        return dept_id;
    }

    public void setDept_id(Integer dept_id) {
        this.dept_id = dept_id;
    }

    @Override
    public String toString() {
        return "emp{" +
                "id=" + id +
                ", ename='" + ename + '\'' +
                ", job_id=" + job_id +
                ", mgr='" + mgr + '\'' +
                ", joindate=" + joindate +
                ", salary=" + salary +
                ", bonus=" + bonus +
                ", dept_id=" + dept_id +
                '}';
    }
}

使用dbutils封装数执行select语句查询数据库

package com.ccl.summary;

import com.ccl.JavaBean.emp;
import com.ccl.Utils.JdbcUtil_Druid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import java.sql.*;
import java.util.List;

/**
 * @author: 13749
 * @date: 2021/9/25 20:22
 * @description:
 */
public class utils_use {
    @Test
    public void testSelect() throws SQLException {
        Connection connection = JdbcUtil_Druid.getConnection();
        QueryRunner queryRunner = new QueryRunner();

        String sql = "select * from emp where id >= ?";
        //创建queryrunner对象


        List<emp> list =
                queryRunner.query(connection , sql,new BeanListHandler<>(emp.class),1001);
        System.out.println("输出查询集合");
        for (emp emp : list) {
            System.out.println(emp);
        }

        //由于dbutils的底层会关闭ResultSet和PrepareStament对象，这里传入两个null值
        JdbcUtil_Druid.close(null,null,connection);


    }

}

运行结果：

测试DBUtils insert语句

@Test
public void testInsert(){
    //获取连接
    Connection  connection = null ;

    try {
        connection = JdbcUtil_Druid.getConnection();
        //创建queryrunner方法
        QueryRunner queryRunner = new QueryRunner();
        //编写sql语句
        String sql = "insert into emp values(null,?,?,?,?,?,?,?)";
        //执行sql语句
        queryRunner.update(connection,sql,"黄蓉","2","1004","2021-09-25","23000","200","10");
    } catch (SQLException e) {
        e.printStackTrace();
    }
    JdbcUtil_Druid.close(null,null,connection);
}