简单的JDBC教程

首先去到这个路径,把Oracle的JDBC包搞出来

打开IDE,把这个包加到依赖里面

点开依赖,看到这个就代表成功了

下面是一个最基础的JDBC链接代码

/**
 * please @mio
 * @Author OkamiMio
 * @Date: 2019/11/11 18:10
 */
public class YozoraMel {
    public static void main(String[] args) throws Exception {
        // Class.forName(String className)的作用有两个,第一是CLASSPATH下指定名字的.class文件加载到Java虚拟机内存中, 第二是初始化这个类
        // 点开OracleDriver这个类看,详情请见下个代码块
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.3.71:1521:orcl", "scott", "12345678");
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from tab");
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1) + "," + resultSet.getString(2));
        }
        resultSet.close();
        statement.close();
        connection.close();
    }
}

点开OracleDriver类,可以发现如下静态代码块,这就是为什么需要Class.forName的原因,它需要对一些静态属性和静态代码块做一些初始化和赋值操作

但是会有同学问为什么不需要new出来,因为这个Driver中我们并没有调用任何方法,我们需要做的是拿到Connection来调用Driver中的各种方法去连接数据库,开发者已经帮我们做好了,至于实际的实现无需关心

static {
    try {
        if (defaultDriver == null) {
            defaultDriver = new oracle.jdbc.OracleDriver();
            DriverManager.registerDriver(defaultDriver);
        }

        AccessController.doPrivileged(new PrivilegedAction() {
            public Object run() {
                OracleDriver.registerMBeans();
                return null;
            }
        });
        Timestamp var0 = Timestamp.valueOf("2000-01-01 00:00:00.0");
    } catch (SQLException var5) {
        Logger.getLogger("oracle.jdbc.driver").log(Level.SEVERE, "SQLException in static block.", var5);
    } catch (RuntimeException var6) {
        Logger.getLogger("oracle.jdbc.driver").log(Level.SEVERE, "RuntimeException in static block.", var6);
    }

    try {
        ClassRef var7 = ClassRef.newInstance("oracle.security.pki.OraclePKIProvider");
        Object var1 = var7.get().newInstance();
    } catch (Throwable var4) {
    }

    systemTypeMap = new Hashtable(3);

    try {
        systemTypeMap.put("SYS.XMLTYPE", ClassRef.newInstance("oracle.xdb.XMLTypeFactory"));
    } catch (ClassNotFoundException var3) {
    }

    try {
        systemTypeMap.put("SYS.ANYDATA", ClassRef.newInstance("oracle.sql.AnyDataFactory"));
        systemTypeMap.put("SYS.ANYTYPE", ClassRef.newInstance("oracle.sql.TypeDescriptorFactory"));
    } catch (ClassNotFoundException var2) {
    }

    _Copyright_2007_Oracle_All_Rights_Reserved_ = null;
}

可以发现,如果有很多连接的话,那么代码会变得非常臃肿.于是可以将这些公共的连接代码抽出来一个工具类,将来统一使用这个工具类进行连接

/**
 * please @mio
 *
 * @Author OkamiMio
 * @Date: 2019/11/11 18:30
 */
public class DBUtil {
    public static final String URL = "jdbc:oracle:thin:@192.168.3.71:1521:orcl";
    public static final String USERNAME = "scott";
    public static final String PASSWORD = "12345678";

    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closeConnection(Connection connection, Statement statement) {
        if (connection != null && statement != null) {
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
        if (connection != null && statement != null && resultSet != null) {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

创建和oracle表中对应的实体类,这里我居然卡在了toString方法上,使用快捷键ctrl+o生成的toString只能默认super,如果想使用idea帮你生成的toString请使用快捷键alt+insert里面的toString

/**
 * please @mio
 *
 * @Author OkamiMio
 */
public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mrg;
    private String hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;

    @Override
    public String toString() {
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mrg=" + mrg +
                ", hiredate='" + hiredate + '\'' +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }

    public Emp(Integer empno, String ename, String job, Integer mrg, String hiredate, Double sal, Double comm, Integer deptno) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mrg = mrg;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }

    public Emp() {
    }

    public Integer getEmpno() {
        return empno;
    }

    public void setEmpno(Integer empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

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

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public Integer getMrg() {
        return mrg;
    }

    public void setMrg(Integer mrg) {
        this.mrg = mrg;
    }

    public String getHiredate() {
        return hiredate;
    }

    public void setHiredate(String hiredate) {
        this.hiredate = hiredate;
    }

    public Double getSal() {
        return sal;
    }

    public void setSal(Double sal) {
        this.sal = sal;
    }

    public Double getComm() {
        return comm;
    }

    public void setComm(Double comm) {
        this.comm = comm;
    }

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }
}
/**
 * please @mio
 *
 * @Author OkamiMio
 * @Date: 2019/11/12 10:15
 */
public class Dept {
    private int deptno;
    private String dname;
    private String loc;

    public Dept() {
    }

    public Dept(int deptno, String dname, String loc) {
        this.deptno = deptno;
        this.dname = dname;
        this.loc = loc;
    }

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "deptno=" + deptno +
                ", dname='" + dname + '\'' +
                ", loc='" + loc + '\'' +
                '}';
    }
}

CRUD

/**
 * 四大种类合起来就是传说中的CRUD接口
 * @Author: OkamiMio
 * @Date: 2019/11/11 10:35
 */
public interface EmpDao {
    /**
     * Create,俗称C
     * @param emp
     */
    void insert(Emp emp);

    /**
     * Delete,俗称D
     * @param emp
     */
    void delete(Emp emp);

    /**
     * Update,俗称U
     * @param emp
     */
    void update(Emp emp);

    /**
     * Read,俗称R
     * @param empNo
     * @return
     */
    Emp select(Integer empNo);

    /**
     * 防止SQL注入攻击
     * @param name
     * @return
     */
    Emp selectByName(String name);
}

静态处理Statement

/**
 * 传统的Statement实现,用于执行静态 SQL 语句并返回它所生成结果的对象
 * please @mio
 * @author OkamiMio
 */
public class StatementEmpDaoImpl implements EmpDao {
    @Override
    public void insert(Emp emp) {
        Connection connection = DBUtil.getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            statement.executeQuery("insert into emp values(" + emp.getEmpno() + ",'" + emp.getEname() + "','" + emp.getJob() + "',"
                    + emp.getMrg() + ",to_date('" + emp.getHiredate() + "','YYYY-MM-DD')," + emp.getSal() + ","
                    + emp.getComm() + "," + emp.getDeptno() + ")");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, statement);
        }
    }

    @Override
    public void delete(Emp emp) {
        Connection connection = DBUtil.getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            int result = statement.executeUpdate("delete from EMP where EMPNO = " + emp.getEmpno());
            System.out.println("受影响的行数是" + result);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, statement);
        }
    }

    @Override
    public void update(Emp emp) {
        Connection connection = DBUtil.getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            int result = statement.executeUpdate("update EMP set ENAME = '" + emp.getEname() + "' where EMPNO = " + emp.getEmpno());
            System.out.println("受影响的行数是" + result);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, statement);
        }

    }

    @Override
    public Emp select(Integer empNo) {
        Connection connection = DBUtil.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        Emp emp = null;
        try {
            connection.setAutoCommit(true);
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from EMP where EMPNO = " + empNo);
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            while (resultSet.next()) {
                emp = new Emp(resultSet.getInt("empno"), resultSet.getString("ename"), resultSet.getString("job"), resultSet.getInt("mgr"), simpleDateFormat.format(resultSet.getDate("hiredate")), resultSet.getDouble("sal"), resultSet.getDouble("comm"), resultSet.getInt("deptno"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, statement, resultSet);
        }
        return emp;
    }

    @Override
    public Emp selectByName(String name) {
        Connection connection = DBUtil.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        Emp emp = null;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            statement = connection.createStatement();
            String sql = "select * from EMP where ENAME = " + name;
            System.out.println(sql);
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                emp = new Emp(resultSet.getInt("empno"), resultSet.getString("ename"), resultSet.getString("job"), resultSet.getInt("mgr"), simpleDateFormat.format(resultSet.getDate("hiredate")), resultSet.getDouble("sal"), resultSet.getDouble("comm"), resultSet.getInt("deptno"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, statement, resultSet);
        }
        return emp;
    }

    public static void main(String[] args) {
        EmpDao empDao = new StatementEmpDaoImpl();
//        Emp mio = new Emp(1, "okamimio", "vtuber", 1, "2018-08-20", 1000.00, 1000.00, 1);
//        empDao.insert(emp);
//        empDao.delete(emp);
//        Emp aqua = new Emp(1, "minatoaqua", "vtuber", 1, "2018-08-20", 1000.00, 1000.00, 1);
//        empDao.insert(mio);
//        empDao.update(aqua);
//        System.out.println(empDao.select(1));

//        SQL注入攻击场景复现,由此产生PreparedStatement来避免这一现象产生
        System.out.println(empDao.selectByName("'minatoaqua' or 1 = 1"));
    }
}

占位符预处理的PreparedStatement,可以有效防止SQL攻击

/**
 * please @mio
 * 使用PreparedStatement防止SQL注入攻击
 *
 * @Author OkamiMio
 * @Date: 2019/11/12 10:23
 */
public class PreparedStatementEmpDaoImpl implements EmpDao {

    private static Connection connection = DBUtil.getConnection();
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;
    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    private static Emp emp = null;

    @Override
    public void insert(Emp emp) {
        try {
            connection.setAutoCommit(true);
            String sql = "insert into emp values(?,?,?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, emp.getEmpno());
            preparedStatement.setString(2, emp.getEname());
            preparedStatement.setString(3, emp.getJob());
            preparedStatement.setInt(4, emp.getMrg());
            preparedStatement.setDate(5, new java.sql.Date(new SimpleDateFormat("yyyy-MM-DD").parse(emp.getHiredate()).getTime()));
            preparedStatement.setDouble(6, emp.getSal());
            preparedStatement.setDouble(7, emp.getComm());
            preparedStatement.setInt(8, emp.getDeptno());
            int result = preparedStatement.executeUpdate();
            System.out.println("受影响的行数是" + result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, preparedStatement);
        }
    }

    @Override
    public void delete(Emp emp) {
        try {
            connection.setAutoCommit(true);
            String sql = "delete from EMP where EMPNO = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, emp.getEmpno());
            int result = preparedStatement.executeUpdate();
            System.out.println("受影响的行数是" + result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, preparedStatement);
        }
    }

    @Override
    public void update(Emp emp) {
        try {
            connection.setAutoCommit(true);
            String sql = "update EMP set ENAME = ? where EMPNO = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(2, emp.getEmpno());
            preparedStatement.setString(1, emp.getEname());
            int result = preparedStatement.executeUpdate();
            System.out.println("受影响的行数是" + result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, preparedStatement);
        }
    }

    @Override
    public Emp select(Integer empNo) {
        try {
            connection.setAutoCommit(true);
            String sql = "select * from emp where EMPNO = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, empNo);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                emp = new Emp(resultSet.getInt("empno"), resultSet.getString("ename"), resultSet.getString("job"), resultSet.getInt("mgr"), simpleDateFormat.format(resultSet.getDate("hiredate")), resultSet.getDouble("sal"), resultSet.getDouble("comm"), resultSet.getInt("deptno"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, preparedStatement);
        }
        return emp;
    }

    @Override
    public Emp selectByName(String name) {
        try {
            connection.setAutoCommit(true);
            String sql = "select * from EMP where ENAME = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                emp = new Emp(resultSet.getInt("empno"), resultSet.getString("ename"), resultSet.getString("job"), resultSet.getInt("mgr"), simpleDateFormat.format(resultSet.getDate("hiredate")), resultSet.getDouble("sal"), resultSet.getDouble("comm"), resultSet.getInt("deptno"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return emp;
    }

    public static void main(String[] args) {
        EmpDao empDao = new PreparedStatementEmpDaoImpl();
//        Emp mio = new Emp(2, "okamimio", "vtuber", 1, "2018-08-20", 1000.00, 1000.00, 1);
//        empDao.insert(mio);
//        empDao.delete(mio);
//        Emp mio = new Emp(1, "okamimio", "vtuber", 1, "2018-08-20", 1000.00, 1000.00, 1);
//        empDao.update(mio);
//        System.out.println(empDao.select(1));
        System.out.println(empDao.selectByName("okamimio"));
    }
}

预处理及单处理性能对比测试,可以看出来建立连接是非常耗时耗资源的

/**
 * please @mio
 * 测试批处理性能
 *
 * @Author OkamiMio
 * @Date: 2019/11/12 11:48
 */
public class BatchDaoImpl {
    public static void main(String[] args) {
        long startTime = System.currentTimeMillis();
        insertBatch();
        long endTime = System.currentTimeMillis();
        System.out.println("批处理时间:" + (endTime - startTime));

        System.out.println("-------");

        startTime = System.currentTimeMillis();
        for (int i = 201; i < 300; i++) {
            notInsertBatch(i);
        }
        endTime = System.currentTimeMillis();
        System.out.println("非批处理时间:" + (endTime - startTime));
    }

    private static void notInsertBatch(int i) {
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            connection.setAutoCommit(true);
            String sql = "insert into EMP (empno, ename) values (?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, i);
            preparedStatement.setString(2, "aqua" + i);
            int update = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, preparedStatement);
        }
    }

    private static void insertBatch() {
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            String sql = "insert into EMP (empno, ename) values (?,?)";
            connection.setAutoCommit(true);
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 101; i < 200; i++) {
                preparedStatement.setInt(1, i);
                preparedStatement.setString(2, "mio" + i);
                preparedStatement.addBatch();
            }
            int[] results = preparedStatement.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, preparedStatement);
        }
    }
}

100条SQL性能对比

利用反射进行统一的查询

/**
 * please @mio
 *
 * @Author OkamiMio
 * @Date: 2019/11/12 13:34
 */
public class BaseDaoImpl {
    /**
     * 统一查询表的方法
     *
     * @param sql    sql语句
     * @param params sql语句的参数
     * @param clazz  sql语句返回的对象
     * @return list
     */
    public List getRows(String sql, Object[] params, Class clazz) {
        List list = new ArrayList();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i, params[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
            // 取出结果集的元数据对象
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            // 查询到每一行的记录中包含多少个列
            int columnCount = resultSetMetaData.getColumnCount();
            while (resultSet.next()) {
                Object clazzInstance = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    // resultSet下标从1开始
                    Object objectValue = resultSet.getObject(i + 1);
                    // 从元数据中获取列名并转化为大写
                    String columnName = resultSetMetaData.getColumnName(i + 1).toLowerCase();
                    // 获取所有属性
                    Field declaredField = clazz.getDeclaredField(columnName);
                    // 获取所有方法
                    Method method = clazz.getMethod(getSetName(columnName), declaredField.getType());
                    if (objectValue instanceof Number) {
                        Number number = (Number) objectValue;
                        String fname = declaredField.getType().getName();
                        if ("int".equals(fname) || "java.lang.Integer".equals(fname)) {
                            method.invoke(clazzInstance, number.intValue());
                        } else if ("byte".equals(fname) || "java.lang.Byte".equals(fname)) {
                            method.invoke(clazzInstance, number.byteValue());
                        } else if ("short".equals(fname) || "java.lang.Short".equals(fname)) {
                            method.invoke(clazzInstance, number.shortValue());
                        } else if ("long".equals(fname) || "java.lang.Long".equals(fname)) {
                            method.invoke(clazzInstance, number.longValue());
                        } else if ("float".equals(fname) || "java.lang.Float".equals(fname)) {
                            method.invoke(clazzInstance, number.floatValue());
                        } else if ("double".equals(fname) || "java.lang.Double".equals(fname)) {
                            method.invoke(clazzInstance, number.doubleValue());
                        }
                    } else {
                        method.invoke(clazzInstance, objectValue);
                    }
                }
                list.add(clazzInstance);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, preparedStatement, resultSet);
        }
        return list;
    }

    private String getSetName(String columnName) {
        return "set" + columnName.substring(0, 1).toUpperCase() + columnName.substring(1);
    }

    public static void main(String[] args) {
        BaseDaoImpl baseDao = new BaseDaoImpl();
        List rows = baseDao.getRows("select deptno,dname,loc from dept", new Object[]{}, Dept.class);
        for (Iterator it = rows.iterator(); it.hasNext(); ) {
            Dept dept = (Dept) it.next();
            System.out.println(dept);
        }
    }
}