文章目录

• 1.什么是JDBC？
• 2.JDBC常用接口
• 3.JDBC访问数据库的过程
• 4.代码实现
• • 查询sql语句
  • 创建表
  • 创建自增序列
  • 进行封装
  • 数据访问对象（data access object）
  • • 接口
    • Emp
    • 接口实现
  • 批量提交

1.什么是JDBC？

• JDBC全称Java Database Connectivity 是基于Java语言访问数据库的一种技术，一种用于执行SQL语言的Java API，可以为多种关系型数据库提供统一访问，由Java语言编写的类和接口组成。
• 主要的两个包
  • java.sql包：提供访问数据库基本的功能
  • javax.sql包：提供扩展功能
• JDBC可以做什么
  • 连接到数据库
  • 在Java app中执行SQL语句
  • 处理结果

2.JDBC常用接口

• java.sql.Driver：驱动
• java.sql.Connection：完成对某一指定数据库的连接
• java.sql.Statement：静态处理块，在一个给定的连接中作为SQL执行声明的容器
• java.sql.PreparedStatement：预处理块，用于执行预编译的sql声明
• java.sql.ResultSet：结果集，对于给定声明取得结果的途径
• java.sql.ResultSetMetaData：结果集元数据

3.JDBC访问数据库的过程

• 驱动管理器——加载jdbc程序
• 连接数据库——建立于数据库的连接
• sql语句——发送sql语句
• 结果集——得到查询结果

4.代码实现

查询sql语句

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

//如果需要建议连接，java中提供了一套标准，数据库厂商来进行实现，包含实现子类，实子类的jar文件在哪
//一般情况下存放在安装目录下
public class JDBCTest {
    public static void main (String[] args) throws Exception {

        //1、加载驱动:
        /*
        * 当执行了当前代码之后，会返回一个Class对象，再此对象的创建过程中，会调用具体类的静态代码块
        * */
       Class.forName ("oracle.jdbc.driver.OracleDriver");
       //2、建立连接
        //第一步中已经经driver对象初测到了drivermanager中，所以此时可以直接通过DriverManager来获取数据库的连接
        /*
        * 需要输入连接数据库的参数
        * url:数据库的地址
        * username:用户名
        * password:密码
        *
        * */
        Connection connection = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
        //3、测试连接是否成功
        System.out.println (connection);
        //4、定义sql语句
        //只要填写正常执行的sql语句即可
        String sql = "select * from emp";
        //5、准备静态处理块对象，将sql语句放置到静态处理块中,理解为sql语句放置对象
        /*
        * 在执行sql语句的过程中，需要一个对象来存放sql语句，将对象进行执行的时候调用的是数据库的服务，数据库会从当前对象中
        * 拿到对应的sql语句进行执行
        *
        * */
        Statement statement = connection.createStatement ();
        //6、执行sql语句,返回值对象是结果集合
        /*
        * 将结果放到resultset中，是返回结果的一个集合
        * 需要经过循环迭代才能获取到其中的每一条记录
        *
        * statement在执行的时候可以选择三种方式：
        * 1、execute:任何SQL语句都可以执行
        * 2、executeQueryL只能执行查询语句
        * 3、executeUpdate，只能执行DML语句
        * */
        ResultSet resultSet = statement.executeQuery (sql);
        //7、循环处理
        //使用while循环，有两种获取具体值的方式，第一种通过下表索引编号来获取，从1开始，第二种是通过列名来获取，哪种好？推荐使用列名，列明一般不会发生修改
        while(resultSet.next ()){
            int anInt = resultSet.getInt (1);
            System.out.println (anInt);
            String ename = resultSet.getString ("ename");
            System.out.println (ename);
            System.out.println ("-----------------");
        }
        //8、关闭连接
        statement.close ();
        connection.close ();
    }
}

创建表

package com.mashibing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/*
* 当执行DDL语句的时候，不会返回对应的结果
* */
public class CreateTable {
    public static void main (String[] args) throws Exception {
        Class.forName ("oracle.jdbc.driver.OracleDriver");
        Connection connection = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
        Statement statement = connection.createStatement ();
        String sql = "create table psn(id number(10) primary key,name varchar2(10))";
        boolean execute = statement.execute (sql);
        System.out. println (execute);
        statement. close ();
        connection. close ();
    }
}

创建自增序列

package com.mashibing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class CreateSeq {
    public static void main (String[] args) throws Exception{
        Class.forName ("oracle.jdbc.driver.OracleDriver");
        Connection connection = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
        String sql = "create sequence seq_1 increment by 1 start with 1";
        Statement statement = connection.createStatement ();
        boolean execute = statement.execute (sql);
        System.out.println (execute);
        statement.close ();
        connection.close ();
    }
}

进行封装

package com.mashibing.util;

import java.sql.*;

public class DBUtil {

    public static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
    public static final String USERNAME = "scott";
    public static final String PASSWORD = "tiger";

    static {
        try {
            Class.forName ("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace ();
        }
    }

    /**
     * 获取数据库连接
     * @return 返回连接对象
     */
    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 (statement!=null){
            try {
                statement.close ();
            } catch (SQLException e) {
                e.printStackTrace ();
            }
        }
        if(connection!=null){
            try {
                connection.close ();
            } catch (SQLException e) {
                e.printStackTrace ();
            }
        }
    }

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

数据访问对象（data access object）

接口

public interface EmpDao {

    //插入数据
    public void insert(Emp emp);

    //删除数据
    public void delete(Emp emp);

    //修改数据
    public void update(Emp emp);

    //查找数据
    public Emp getEmpByEmpno(Integer empno);

    public Emp getEmpByEname(String name);
}

Emp

public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private String hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;

    public Emp() {

    }

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

    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 getMgr() {
        return mgr;
    }

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

    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;
    }

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

接口实现

public class EmpDaoLmp2 implements EmpDao {

    @Override
    public void insert(Emp emp) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            connection = DBUtil.getConnection();
            // 设置事务是否自动提交，true自动
//            connection.setAutoCommit(true);
            String sql = "insert into emp values(?,?,?,?,?,?,?,?)";
            pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, emp.getEmpno());
            pstmt.setString(2, emp.getEname());
            pstmt.setString(3,emp.getJob());
            pstmt.setInt(4, emp.getMgr());
            pstmt.setDate(5, new java.sql.Date(new SimpleDateFormat("yyyy-MM-DD").parse(emp.getHiredate()).getTime()));
            pstmt.setDouble(6,emp.getSal());
            pstmt.setDouble(7, emp.getComm());
            pstmt.setInt(8, emp.getDeptno());
            System.out.println(sql);
            int i = pstmt.executeUpdate();
            System.out.println("受影响的行数是：" + i);
        } catch (SQLException | ParseException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, pstmt);
        }
    }

    @Override
    public void delete(Emp emp) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "delete from emp where empno = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,emp.getEmpno());
            System.out.println(sql);
            int i = statement.executeUpdate();
            System.out.println("受影响的行数是：" + i);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, statement);
        }
    }

    @Override
    public void update(Emp emp) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "update emp set job = ? where empno = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, emp.getJob());
            statement.setInt(2, emp.getEmpno());
            System.out.println(sql);
            int i = statement.executeUpdate();
            System.out.println("受影响的行数为：" + i);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, statement);
        }


    }

    @Override
    public Emp getEmpByEmpno(Integer empno) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Emp emp = null;
        try {
            connection = DBUtil.getConnection();
            statement = connection.createStatement();
            String sql = "select * from emp where empno = " + empno;
            System.out.println(sql);
            resultSet = statement.executeQuery(sql);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            while (resultSet.next()) {
                emp = new Emp(resultSet.getInt("empno"), resultSet.getString("ename"),resultSet.getString("job"),
                        resultSet.getInt("mgr"), sdf.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 getEmpByEname(String name) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        Emp emp = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from emp where ename = ?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, name);
            System.out.println(sql);
            resultSet = pstmt.executeQuery();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            while (resultSet.next()) {
                emp = new Emp(resultSet.getInt("empno"), resultSet.getString("ename"), resultSet.getString("job"),
                        resultSet.getInt("mgr"), sdf.format(resultSet.getDate("hiredate")), resultSet.getDouble("sal"),
                        resultSet.getDouble("comm"), resultSet.getInt("deptno"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, pstmt, resultSet);
        }
        return emp;
    }

    public static void main(String[] args) {
        EmpDao empDao = new EmpDaoLmp2();
//        Emp emp = new Emp(3333,"lala","HEHE",1111,"2019-11-09",1500.0,500.0,10);
        Emp emp = new Emp(3333,"lala","ooo",1111,"2019-11-09",1500.0,500.0,10);
//        empDao.insert(emp);
//        empDao.delete(emp);
        empDao.update(emp);
//        Emp emp1 = empDao.getEmpByEmpno(7369);
//        System.out.println(emp1);
        // sql注入，这个是PreparedStatement改后的防注入版本
//        Emp emp2 = empDao.getEmpByEname("'SMTTH' or 1 = 1");
//        Emp emp3 = empDao.getEmpByEname("SMITH");
//        System.out.println(emp2);
//        System.out.println(emp3);
    }
}

批量提交

public class BatchDaoImpl {
    public static void main(String[] args) {
        insertBatch();
    }
    public static void insertBatch() {
        Connection connection = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        String sql = "insert into emp(empno, ename) values(?,?)";
        try {
            pstmt = connection.prepareStatement(sql);
            for (int i = 0; i < 100; i++) {
                pstmt.setInt(1, i);
                pstmt.setString(2, "lx" + i);
                pstmt.addBatch();
            }
            int[] ints = pstmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(connection, pstmt);
        }
    }