JDBC使用方法

JDBC

1. JDBC基本步骤

• 加载驱动（选择数据库）
• 获取连接（与数据库建立连接）
• 准备sql
• 封装处理块
• 发送执行sql语句，得到结果集
• 处理结果
• 关闭

配置文件代码：

classpath=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:XE
user=SCOTT
pwd=TIGER

工具类代码：

import java.io.IOException;
import java.sql.*;
import java.util.Properties;
//查询emp表中的所有数据，并将结果打印至控制台
public class JDBCDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
        Properties pro = new Properties();
        pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("JDBCpath.properties"));
        //加载驱动（选择数据库）
        Class.forName(pro.getProperty("classpath"));
        //获取连接
        Connection conn = DriverManager.getConnection(pro.getProperty("url"),pro.getProperty("user"),pro.getProperty("pwd"));
        //准备sql
        String sql = "select * from emp";
        //封装静态处理块
        Statement statement = conn.createStatement();
        //执行sql，得到结果集
        ResultSet result = statement.executeQuery(sql);
        //处理结果
        while (result.next()){
            int empno = result.getInt(1);
            String ename = result.getString("ename");
            String job = result.getString(3);
            int mgr = result.getInt("mgr");
            Date hiredate = result.getDate("hiredate");
            int sal = result.getInt(6);
            int comm = result.getInt(7);
            int deptno = result.getInt("deptno");
            System.out.println(empno+"->"+ename+"->"+job+"->"+mgr+"->"+hiredate+"->"+sal+"->"+comm+"->"+deptno);
        }
        conn.close();
    }
}

封装工具类，后面使用，代码如下：

//封装工具类
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
    private static Properties pro = new Properties();
    static {
        try {    pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("JDBCpath.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            Class.forName(pro.getProperty("classpath"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        Connection conn = DriverManager.getConnection(pro.getProperty("url"),pro.getProperty("user"),pro.getProperty("pwd"));
        return conn;
    }
    //关闭
    public static void close(Connection conn, PreparedStatement ps, ResultSet result){
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (result!=null){
            try {
                result.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //关闭的重载方法
    public static void close(Connection conn, PreparedStatement ps){
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //关闭的重载方法
    public static void close(Connection conn, PreparedStatement ps1,PreparedStatement ps2){
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (ps1!=null){
            try {
                ps1.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (ps2!=null){
            try {
                ps2.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

2. JDBC中的预处理块及手动提交事务

• JDBC中事务默认自动提交
• SQL注入的问题，SQL注入即恶意的把传输到后台的数据进行拼接，或者前台用户输入的内容不符合正常情况，造成数据的不安全。使用静态处理块会遇到该问题，因为SQL语句是由字符串拼接成功的。解决这种情况需要使用预处理块，预处理块会预先编译，提高效率，预防SQL注入问题的发生

由于JDBC中事务是自动提交的，有时会出现数据的不安全情况，例如一个人转账给另一个人，会出现钱转出对方未收到的情况，钱也就不见了。

代码：

//zhangsan转账1000给lisi
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCDemo02 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            //获取连接
            conn = JDBCUtils.getConnection();
            //设置手动提交事务
            conn.setAutoCommit(false);
            //准备sql
            String sql1 = "update test_commit set account=account-1000 where uname='zhangsan'";
            String sql2 = "update test_commit set account=account+1000 where uname='lisi'";
            ps1 = conn.prepareStatement(sql1);
            ps2 = conn.prepareStatement(sql2);
            int num1 = ps1.executeUpdate();
            int num2 = ps2.executeUpdate();
            if (num1>0&&num2>0){
                conn.commit();
                System.out.println("转账成功");
            }else {
                conn.rollback();
                System.out.println("转账失败");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭
            JDBCUtils.close(conn,ps1,ps2);
        }
    }
}

3. 使用JDBC实现对用户的基本操作

注：在Oracle数据库中的字符串类型对应java中的String类型，number类型对应java中的java.math.BigDecimal类型。当number转为java中的BigDecimal类型时，与javabean中的属性类型不一致时有两种解决方案。

• 将属性的类型变为BigDecimal类型
• 将数据转为对应的基本数据类型，再赋值给对应类型的属性

封装工具类，增删改为一个方法，查询为一个方法，代码：

//定义工具类
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BaseDao<T> {
    //增删改功能
    public static boolean modify(String sql,Object ... arr){
        boolean flag = false;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //获取连接
            conn = JDBCUtils.getConnection();
            conn.setAutoCommit(false);
            //创建预处理块
            ps = conn.prepareStatement(sql);
            //给sql语句中的?赋值
            if(arr!=null && arr.length>0){
                for(int i=0;i<=arr.length-1;i++){
                    ps.setObject(i+1,arr[i]);
                }
            }
            //执行
            int num = ps.executeUpdate();
            if (num>0){
                flag = true;
                conn.commit();
            }else {
                conn.rollback();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(conn,ps);
        }
        return flag;
    }

    //查询功能
    public List<T> search(String sql, Class<T> cls, Object ... arr){
        //存储查询数据的集合
        List<T> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet result = null;
        ResultSetMetaData metaData = null;
        try {
            //获取连接
            conn = JDBCUtils.getConnection();
            //创建预处理块
            ps = conn.prepareStatement(sql);
            //给sql语句中的？赋值
            if(arr!=null && arr.length>0){
                for(int i=0;i<=arr.length-1;i++){
                    ps.setObject(i+1,arr[i]);
                }
            }
            //执行
            result = ps.executeQuery();
            //结果集
            metaData = result.getMetaData();
            //结果集中字段个数
            int count = metaData.getColumnCount();
            //对结果集进行处理
            while (result.next()) {
                //创建对应类型的对象
                T obj = cls.getConstructor().newInstance();
                //遍历结果集中的每一个字段,获取值,获取javabean类中对应这个字段的属性,为当前属性赋值
                for (int i = 1; i <= count; i++) {
                    //获取值
                    Object value = result.getObject(i);
                    if ("java.math.BigDecimal".equals(value.getClass().getName())) {
                        value = ((BigDecimal) value).intValue();
                    }
                    //获取属性名
                    String fieldName = metaData.getColumnLabel(i);
                    //获取javabean的属性
                    Field field = cls.getDeclaredField(fieldName);
                    //私有属性设置访问权限
                    field.setAccessible(true);
                    //赋值
                    field.set(obj, value);
                    //关闭访问权限
                    field.setAccessible(false);
                }
                //将对象放入集合
                list.add(obj);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(conn,ps,result);
        }
        return list;
    }
}

自定义类：

import java.io.Serializable;
import java.util.Date;
import java.util.Objects;

public class Employee implements Serializable {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Integer sal;
    private Integer comm;
    private Integer deptno;

    public Employee() {
    }

    public Employee(Integer empno, String ename, String job, Integer mgr, Date hiredate, Integer sal, Integer 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 Date getHiredate() {
        return hiredate;
    }

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

    public Integer getSal() {
        return sal;
    }

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

    public Integer getComm() {
        return comm;
    }

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

    public Integer getDeptno() {
        return deptno;
    }

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

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Employee employee = (Employee) o;
        return Objects.equals(empno, employee.empno) &&
                Objects.equals(ename, employee.ename) &&
                Objects.equals(job, employee.job) &&
                Objects.equals(mgr, employee.mgr) &&
                Objects.equals(hiredate, employee.hiredate) &&
                Objects.equals(sal, employee.sal) &&
                Objects.equals(comm, employee.comm) &&
                Objects.equals(deptno, employee.deptno);
    }

    @Override
    public int hashCode() {
        return Objects.hash(empno, ename, job, mgr, hiredate, sal, comm, deptno);
    }

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

测试代码：

import java.util.List;
public class UserDemo03 {
    public static void main(String[] args) {
        //测试增删改功能
        System.out.println(BaseDao.modify("insert into test_user values(?,?)","zhangsan","10086"));
        System.out.println(BaseDao.modify("insert into test_user values(?,?)","lisi","10087"));
        System.out.println(BaseDao.modify("insert into test_user values(?,?)","wangwu","10088"));
        System.out.println(BaseDao.modify("delete from test_user where uname=?","wangwu"));
        System.out.println(BaseDao.modify("update test_user set pwd=? where uname=?","22222","lisi"));
        //测试查询方法
        BaseDao<Employee> bd = new BaseDao<>();
        List<Employee> list = bd.search("select empno \"empno\",ename \"ename\" from emp where empno in (?,?) ",Employee.class,7369,7499);
        list.forEach(System.out::println);
    }
}