JDBC技术

JDBC简介

a）Java DataBase Connectivity，java数据库连接
b）SUN公司提供的一套标准，是一种用于执行SQL语句的
Java API
a）DriverManager（C），管理驱动
b）Connection（I），数据库连接
c）Statement（I），SQL语句发送器
d）ResultSet（I），结果集

JDBC连接数据库的步骤

a）注册驱动
b）建立数据库连接（Connection）
c）准备SQL语句
d）获取SQL语句发送器（Statement）
e）发送并执行SQL语句，得到结果集（ResultSet）
f）处理结果集
g）关闭资源（ResultSet,Statement,Connection）

package com.java.jdbc;

import java.sql.*;

/**
 * jdbc连接数据库，执行查询操作
 * @Auther:penghao
 * @Date:2019/4/2
 * @Description:com.java.jdbc
 * @version:1.0
 */

public class TestJdbcQuery {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //声明连接参数
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";//数据库的路径
        String user = "scott"; //用户名
        String password = "tiger"; //密码
        //注册驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //建立数据库连接，需要用到驱动管理器
        Connection conn = DriverManager.getConnection(url, user, password);
        //System.out.println(conn);
        //定义SQL语句
        String sql = "select empno,ename,hiredate from emp";
        //创建sql发送器，是由连接对象创建的
        Statement stmt = conn.createStatement();
        //发送并执行sql语句，得到结果集
        ResultSet rs = stmt.executeQuery(sql);
        //处理结果集
        while (rs.next()){
            //取出该行的每一列数据，依据数据类型取值
            int empno = rs.getInt(1);//数据库列索引从1开始
            String ename = rs.getString("ename");
            Date hiredate = rs.getDate(3);
            System.out.println(empno + "\t" +ename + "\t" + hiredate.toLocaleString());
        }
        //关闭资源，先开的后关
        rs.close();
        stmt.close();
        conn.close();
    }
}

JDBC异常处理_关闭资源

package com.java.jdbc;

import java.sql.*;

/**
 * @Auther:penghao
 * @Date:2019/4/2
 * @Description:com.java.jdbc
 * @version:1.0
 */
public class JdbcQuery {
    public static void main(String[] args){
        // [1] 声明连接参数
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "scott";
        String passworld = "tiger";
        // [2] 声明连接对象
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // [3] 注册驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // [4] 获取数据库连接
            conn = DriverManager.getConnection(url, user, passworld);
            // [5] 定义SQL语句
            String sql = "select * from emp";
            // [6] 创建发送器
                stmt = conn.createStatement();
            // [7] 发送并执行，得到结果集
            rs = stmt.executeQuery(sql);
            // [8] 处理结果集
            while(rs.next()){
                String ename = rs.getString("ename");
                double sal = rs.getDouble("sal");
                Date hiredate = rs.getDate("hiredate");
                System.out.println(ename + "\t" + sal + "\t" + hiredate);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // [9] 关闭资源
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(stmt!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

JDBC_执行DML操作

package com.java.jdbc;

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

/**
 * jdbc执行DML操作
 * @Auther:penghao
 * @Date:2019/4/2
 * @Description:com.java.jdbc
 * @version:1.0
 */
public class JdbcUpdate {
    public static void main(String[] args){
        // [1] 声明参数
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String user = "scott";
        String password = "tiger";
        // [2] 声明连接对象
        Connection conn = null;
        Statement stmt = null;
        try {
            // [3] 注册驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // [4] 获取连接对象
            conn = DriverManager.getConnection(url,user,password);
            // [5] 定义SQL语句
            //String sql = "insert into emp values (1234,'小伟','SALESMAN',7369,sysdate,500,200,20)";
            //String sql = "update emp set sal=sal*2 where empno=1234";
            String sql = "delete from emp where empno=1234";
            // [6] 发送器
                stmt = conn.createStatement();
            // [7] 发送并执行，得到结果
            int rowCount = stmt.executeUpdate(sql);
            // [8] 处理结果
            if(rowCount>0){
                System.out.println("操作成功");
            }else{
                System.out.println("操作失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

JDBC 事务管理

a）默认情况下，JDBC会自动提交事务
b）当执行多条SQL语句时，自动提交事务就变得不安全。因此，需要手动管理事务。
c）需要关闭事务的自动提交，并在事务成功时进行提交，失败或发生异常时进行回滚，保证事务的一致性。

package com.java.jdbc;

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

/**
 * @Auther:penghao
 * @Date:2019/4/2
 * @Description:com.java.jdbc
 * @version:1.0
 */
public class JdbcTransaction {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String user = "penghao";
        String password = "1234";
        Connection conn = null;
        Statement stmt1 = null;
        Statement stmt2 = null;
        String sql1 = "update t_account set money=money-1000 where id = 1";
        String sql2 = "update t_account set money=money+1000 where id = 2";

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(url,user,password);
            // 关闭jdbc的自动提交功能
            conn.setAutoCommit(false);
            stmt1 = conn.createStatement();
            stmt2 = conn.createStatement();
            int num = stmt1.executeUpdate(sql1);
            num += stmt2.executeUpdate(sql2);

            if(num == 2){
                System.out.println("转账成功！");
                System.out.println("提交事务");
                //提交事务
                conn.commit();
            }else{
                System.out.println("转账失败！");
                System.out.println("事务回滚");
                //事务回滚
                conn.rollback();
            }
        } catch (Exception e) {
            System.out.println("转账失败，异常信息："+e.getMessage());
            //发生异常时，进行事务回滚
            if(conn != null){
                try {
                    conn.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        } finally {
            //重新开启自动提交
            if(conn != null){
                try {
                    conn.setAutoCommit(true);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            //关闭资源
            if(stmt2 != null){
                try {
                    stmt2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(stmt1 != null){
                try {
                    stmt1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

简单封装DBUtil

由于jdbc连接数据库的步骤都一样，所以为了减少代码冗余，可以对这个过程进行封装，形成一个工具类。

package com.java.util;

import java.sql.*;

/**
 * @Auther:penghao
 * @Date:2019/4/2
 * @Description:com.java.util
 * @version:1.0
 */
public class DBUtil {
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
    private static final String USER = "penghao";
    private static final String PASSWORD = "1234";

    static {
        //注册驱动
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConn(){
       Connection conn = null;
        try {
            conn= DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            System.out.println("连接创建失败，请检查[url]:"+URL+",[user]:"+USER+",[password]:"+PASSWORD+"}");
        }
        return conn;
    }

    /**
     * 获取SQL发送器
     * @param conn
     * @return
     */
    public static Statement getStmt(Connection conn){
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stmt;
    }

    /**
     * 统一关闭资源
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void close(ResultSet rs,Statement stmt,Connection conn){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

使用工具类练习登录

package com.java.login;

import com.java.util.DBUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/**
 * @Auther:penghao
 * @Date:2019/4/2
 * @Description:com.java.login
 * @version:1.0
 */
public class Login {
    public static void main(String[] args){
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.nextLine();

        //定义SQL语句
        String sql = "select count(*) from t_user where username = '" + username + "' and password = '" + password +"'";
        System.out.println(sql);
        //获取数据库连接
        Connection conn = DBUtil.getConn();
        //获取发送器
        Statement stmt = DBUtil.getStmt(conn);
        //声明结果集
        ResultSet rs = null;

        try {
            //发送并执行
            rs = stmt.executeQuery(sql);
            //处理结果集
            if (rs.next()){
                if(rs.getInt(1)==0){
                    System.out.println("登录失败，用户名或密码错误！");
                }else{
                    System.out.println("欢迎" + username + "登录成功！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs,stmt,conn);
        }
        sc.close();
    }
}

PreparedStatement

防止SQL注入，并且效率高。

package com.java.login;

import com.java.util.DBUtil;

import java.sql.*;
import java.util.Scanner;

/**
 * @Auther:penghao
 * @Date:2019/4/2
 * @Description:com.java.login
 * @version:1.0
 */
public class Login2 {
    public static void main(String[] args){
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.nextLine();

        //定义SQL语句
        String sql = "select count(*) from t_user where username =?  and password = ?";
        System.out.println(sql);
        //获取数据库连接
        Connection conn = DBUtil.getConn();
        //声明预处理发送器
        PreparedStatement pstmt = null;
        //声明结果集
        ResultSet rs = null;

        try {
            //创建预处理发送器
            pstmt = conn.prepareStatement(sql);
            // 绑定参数
            pstmt.setString(1,username);
            pstmt.setString(2,password);
            //发送并执行
            rs = pstmt.executeQuery();
            //处理结果集
            if (rs.next()){
                if(rs.getInt(1)==0){
                    System.out.println("登录失败，用户名或密码错误！");
                }else{
                    System.out.println("欢迎" + username + "登录成功！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs,pstmt,conn);
        }
        sc.close();
    }
}

DBUtil封装PreparedStatement

package com.java.util;

import java.sql.*;

/**
 * @Auther:penghao
 * @Date:2019/4/2
 * @Description:com.java.util
 * @version:1.0
 */
public class DBUtil {
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
    private static final String USER = "penghao";
    private static final String PASSWORD = "1234";

    static {
        //注册驱动
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConn(){
       Connection conn = null;
        try {
            conn= DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            System.out.println("连接创建失败，请检查[url]:"+URL+",[user]:"+USER+",[password]:"+PASSWORD+"}");
        }
        return conn;
    }

    /**
     * 获取SQL发送器
     * @param conn
     * @return
     */
    public static Statement getStmt(Connection conn){
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stmt;
    }

    /**
     * 获取预处理发送器
     * @param conn
     * @param sql
     * @return
     */
    public static PreparedStatement getPstmt(Connection conn,String sql){
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pstmt;
    }

    /**
     * 动态绑定参数
     * @param pstmt
     * @param params
     */
    public static void bindParam(PreparedStatement pstmt,Object... params){
        try {
            for (int i = 1;i <= params.length;i++){
                pstmt.setObject(i,params[i-1]);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * 统一关闭资源
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void close(ResultSet rs,Statement stmt,Connection conn){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

package com.java.login;

import com.java.util.DBUtil;

import java.sql.*;
import java.util.Scanner;

/**
 * @Auther:penghao
 * @Date:2019/4/2
 * @Description:com.java.login
 * @version:1.0
 */
public class Login2 {
    public static void main(String[] args){
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.nextLine();

        //定义SQL语句
        String sql = "select count(*) from t_user where username =?  and password = ?";
        System.out.println(sql);
        //获取数据库连接
        Connection conn = DBUtil.getConn();
        //声明预处理发送器
        PreparedStatement pstmt = DBUtil.getPstmt(conn,sql);
        //绑定参数
        DBUtil.bindParam(pstmt,username,password);
        //声明结果集
        ResultSet rs = null;

        try {
            //发送并执行
            rs = pstmt.executeQuery();
            //处理结果集
            if (rs.next()){
                if(rs.getInt(1)==0){
                    System.out.println("登录失败，用户名或密码错误！");
                }else{
                    System.out.println("欢迎" + username + "登录成功！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs,pstmt,conn);
        }
        sc.close();
    }
}

使用Properties优化工具类

a）Properties是一个工具类，表示属性集，属性列表中每个键及其对应值都是一个字符串。可以保存在流中，也可以从流中加载。

jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
jdbc.user=penghao
jdbc.password=1234

b）将数据库连接的信息存放在properties文件中，便于后续的维护，修改后不用重新编译代码

package com.java.util;

import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * 软编码：将配置信息提取生成一个配置文件，然后让程序在执行过程中，读取配置信息
 * 好处：可以动态的获取配置信息，有助于后续代码的维护
 *
 * Java中，提供了一个类，叫Properties类，用于读取properties配置文件
 * @Auther:penghao
 * @Date:2019/4/2
 * @Description:com.java.util
 * @version:1.0
 */
public class DBUtil {
    private static  String driver;
    private static  String url;
    private static  String user;
    private static  String password;

    static {

        try {
            //创建Properties对象
            Properties prop = new Properties();
            //加载配置文件
            prop.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));
            //获取信息并进行初始化
            driver = prop.getProperty("jdbc.driver").trim();
            url = prop.getProperty("jdbc.url").trim();
            user = prop.getProperty("jdbc.user").trim();
            password = prop.getProperty("jdbc.password").trim();
            //注册驱动
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConn(){
       Connection conn = null;
        try {
            conn= DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            System.out.println("连接创建失败，请检查[url]:"+url+",[user]:"+user+",[password]:"+password+"}");
        }
        return conn;
    }

    /**
     * 获取SQL发送器
     * @param conn
     * @return
     */
    public static Statement getStmt(Connection conn){
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stmt;
    }

    /**
     * 获取预处理发送器
     * @param conn
     * @param sql
     * @return
     */
    public static PreparedStatement getPstmt(Connection conn,String sql){
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pstmt;
    }

    /**
     * 动态绑定参数
     * @param pstmt
     * @param params
     */
    public static void bindParam(PreparedStatement pstmt,Object... params){
        try {
            for (int i = 1;i <= params.length;i++){
                pstmt.setObject(i,params[i-1]);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * 统一关闭资源
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void close(ResultSet rs,Statement stmt,Connection conn){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

插入大量数据

package com.java.num;

import com.java.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.UUID;

/**
 * @Auther:penghao
 * @Date:2019/4/6
 * @Description:com.java.num
 * @version:1.0
 */
public class TestNum {
    public static void main(String[] args){
        //1.将2-100000之间的数字插入数据库，并记录数字的类型
        long start = System.currentTimeMillis();
        int num = 100000;
        /*for (int i = 2;i <= num; i++){
            insertNum1(i);
        }*/
        //insertNum2(num);
        //insertNum3(num);
        //insertNum4(num);
        insertNum5(num);
        long stop = System.currentTimeMillis();
        System.out.println("耗时："+(stop-start)+"ms.");

    }

    /**
     * 使用PreparedStatement插入数据，采用批处理的方式进行
     * 
     * @param num
     */
    private static void insertNum5(int num){
        String sql = "insert into t_num values(?,?,?)";
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        try {
            //关闭自动提交
            conn.setAutoCommit(false);
            for (int i = 2; i <= num;i++){
                //绑定参数
                DBUtil.bindParam(pstmt,getUUID(),i,getNumType(i));
                System.out.println(sql);
                //加入批处理
                pstmt.addBatch();
            }
            //统一执行批处理
            pstmt.executeBatch();
            //提交事务
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            DBUtil.close(null,pstmt,conn);
        }
    }
    /**
     * 使用PreparedStatement插入数据，手动提交事务
     *
     * @param num
     */
    private static void insertNum4(int num){
        String sql = "insert into t_num values(?,?,?)";
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        try {
            //关闭自动提交
            conn.setAutoCommit(false);
            for (int i = 2; i <= num;i++){
                DBUtil.bindParam(pstmt,getUUID(),i,getNumType(i));
                System.out.println(sql);
                pstmt.executeUpdate();
            }
            //提交事务
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            DBUtil.close(null,pstmt,conn);
        }
    }
    /**
     * 使用PreparedStatement插入数据
     *
     * @param num
     */
    private static void  insertNum3(int num){
        String sql = "insert into t_num values(?,?,?)";
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        try {
            for (int i = 2; i <= num;i++){
                DBUtil.bindParam(pstmt,getUUID(),i,getNumType(i));
                System.out.println(sql);
                pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null,pstmt,conn);
        }
    }
    /**
     * 通过Statement插入数据，只开关一次连接
     *
     * @param num
     */
    private static void insertNum2(int num){
        Connection conn = DBUtil.getConn();
        Statement stmt = DBUtil.getStmt(conn);
        try {
            for (int i = 2;i <= num;i++){
                String sql = "insert into t_num values('"+getUUID()+"', "+i+" ,'"+getNumType(i)+"')";
                System.out.println(sql);
                stmt.executeUpdate(sql);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null,stmt,conn);
        }
    }
    /**
     * 通过Statement插入一个数字
     *
     * @param num
     */
    private static void insertNum1(int num){
        String sql = "insert into t_num values('"+getUUID()+"', "+num+" ,'"+getNumType(num)+"')";
        System.out.println(sql);
        Connection conn = DBUtil.getConn();
        Statement stmt = DBUtil.getStmt(conn);
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null,stmt,conn);
        }
    }
    /**
     * 生成UUID
     *
     * @return
     */
    private static String getUUID(){
        return UUID.randomUUID().toString().replace("-","");
    }

    /**
     * 判断num是ZS还是HS
     *
     * @param num
     * @return
     */
    private static String getNumType(int num){
        if(num<4){
            return "ZS";
        }
        for (int i = 2;i <= Math.sqrt(num);i++){
            if (num % i == 0){
                return "HS";
            }
        }
        return "ZS";
    }
}

批量处理

a）当向数据库插入大量数据时，需要提高执行效率

1. 不能频繁开关数据库连接
2. 要使用PreparedStatement而不使用Statement
3. 要手动提交事务，不要自动提交
4. 使用批处理，addBatch,executeBatch

/**
     * 使用PreparedStatement插入数据，采用批处理的方式进行
     *
     * @param num
     */
    private static void insertNum5(int num){
        String sql = "insert into t_num values(?,?,?)";
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        try {
            //关闭自动提交
            conn.setAutoCommit(false);
            for (int i = 2; i <= num;i++){
                //绑定参数
                DBUtil.bindParam(pstmt,getUUID(),i,getNumType(i));
                System.out.println(sql);
                //加入批处理
                pstmt.addBatch();
            }
            //统一执行批处理
            pstmt.executeBatch();
            //提交事务
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            DBUtil.close(null,pstmt,conn);
        }
    }

查询某个数字时质数还是合数

统计质数有多少，合数有多少（使用Map封装查询记录）

package com.java.num;

import com.java.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

/**
 * @Auther:penghao
 * @Date:2019/4/6
 * @Description:com.java.num
 * @version:1.0
 */
public class TestNum2 {
    //2.查询某个数字是质数还是合数
    //3.统计质数有多少，合数有多少
    public static void main(String[] args){
        //selectType(50);
        Map<String, Integer> map = selectCount();
        Set<String> keySet = map.keySet();
        for (String key:keySet){
            System.out.println((key.equals("ZS")?"质数":"合数")+"有"+map.get(key)+"个.");
        }
    }

    /**
     * 统计质数合数的数量
     *
     * @return
     */
    private static Map<String,Integer> selectCount(){
        Map<String, Integer> map = new HashMap<>();
        //orm object relational mapping
        String sql = "select type,count(*) from t_num group by type";
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        ResultSet rs = null;

        try {
            rs = pstmt.executeQuery();
            while(rs.next()){
                map.put(rs.getString(1),rs.getInt(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs,pstmt,conn);
        }

        return map;
    }
    /**
     * 查询某个数字是质数还是合数
     *
     * @param num
     */
    private static void selectType(int num){
       String sql = "select type from t_num where num=?";
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        DBUtil.bindParam(pstmt,num);
        ResultSet rs = null;
        try {
            rs = pstmt.executeQuery();
            if (rs.next()){
                System.out.println(num + "是一个：" + (rs.getString("type").equals("ZS")?"质数":"合数"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  finally {
            DBUtil.close(rs,pstmt,conn);
        }
    }
}

分层开发

a）当一个项目功能比较多，代码量比较大的时候，需要使用分层开发。
b）ORM，Object Relational Mapping，对象关系映射
c）代码的分层是通过包(package)来区分的，常见的包名有：
实体类：entity，bean，pojo
工具类：util
数据访问层：dao，Data Access Object，用于和数据库进行交互，通常用于定义接口。

package com.java.dao;

import com.java.pojo.Emp;

import java.util.List;

/**
 * @Auther:penghao
 * @Date:2019/4/7
 * @Description:com.java.dao
 * @version:1.0
 */
public interface EmpDao {
    /**
     * 查询所有员工信息
     *
     * @return
     */
    List<Emp> selAll();

    /**
     * 根据员工编号查询员工信息
     *
     * @param empno
     * @return
     */
    Emp selByEmpno(int empno);

    /**
     * 新增员工信息
     * @param emp
     * @return
     */
    boolean insEmp(Emp emp);

    /**
     * 根据编号修改工资
     * @param empno
     * @param sal
     * @return
     */
    boolean upSalByEmpno(int empno,double sal);

    /**
     * 根据编号删除员工
     *
     * @param empno
     * @return
     */
    boolean delEmp(int empno);
}

提取BaseDao，封装update方法

增删改操作本质上都是用executeUpdate方法，因此，可以被提取，后续进行代码复用

/**
     * 执行DML操作的方法
     *
     * @param sql
     * @param params
     * @return
     */
    protected boolean update(String sql,Object... params){
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        DBUtil.bindParam(pstmt,params);
        try {
            int num = pstmt.executeUpdate();
            if (num > 0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null,pstmt,conn);
        }
        return false;
    }

使用BeanUtils封装jdbc查询

使用的Jar包

commons-beanutils-1.9.3.jar 
commons-collections-3.2.2.jar 
commons-logging-1.2.jar 

BaseDao完整代码

package com.java.dao.impl;

import com.java.util.DBUtil;
import org.apache.commons.beanutils.BeanUtils;

import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 执行DML操作的方法
 *
 * @Auther:penghao
 * @Date:2019/4/7
 * @Description:com.java.dao.impl
 * @version:1.0
 */
class BaseDao {
    /**
     * 执行DML操作的方法
     *
     * @param sql
     * @param params
     * @return
     */
    protected boolean update(String sql,Object... params){
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        DBUtil.bindParam(pstmt,params);
        try {
            int num = pstmt.executeUpdate();
            if (num > 0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null,pstmt,conn);
        }
        return false;
    }

    /**
     * 返回list集合的查询
     *
     * @param cls
     * @param sql
     * @param params
     * @param <T>
     * @return
     */
    protected <T> List<T> queryAll(Class<T> cls,String sql,Object... params){
        List<T> list = new ArrayList<>();
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        DBUtil.bindParam(pstmt,params);
        ResultSet rs = null;
        try {
            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            while(rs.next()){
                T bean = cls.newInstance();
                for (int i = 0;i < metaData.getColumnCount();i++){
                    BeanUtils.setProperty(bean,metaData.getColumnLabel(i+1).toLowerCase(),rs.getObject(i+1));
                }
                list.add(bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null,pstmt,conn);
        }
        return list;
    }

    protected <T> T queryone(Class<T> cls,String sql,Object... params){
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        DBUtil.bindParam(pstmt,params);
        ResultSet rs = null;
        try {
            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            if(rs.next()){
                T bean = cls.newInstance();
                for (int i = 0;i < metaData.getColumnCount();i++){
                    BeanUtils.setProperty(bean,metaData.getColumnLabel(i+1).toLowerCase(),rs.getObject(i+1));
                }
                return bean;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null,pstmt,conn);
        }
        return null;
    }
}

package com.java.dao.impl;

import com.java.dao.EmpDao;
import com.java.pojo.Emp;
import com.java.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Auther:penghao
 * @Date:2019/4/7
 * @Description:com.java.dao.impl
 * @version:1.0
 */
public class EmpDaoImpl extends BaseDao implements EmpDao{
    @Override
    public List<Emp> selAll() {
        String sql = "select * from emp";
        return queryAll(Emp.class,sql);
    }

    @Override
    public Emp selByEmpno(int empno) {
        String sql = "select * from emp where empno=?";
        return queryone(Emp.class,sql,empno);
    }

    @Override
    public boolean insEmp(Emp emp) {
        String sql = "insert into emp values (?,?,?,?,sysdate,?,?,?)";
        Object[] params = {
                emp.getEmpno(),
                emp.getEname(),
                emp.getJob(),
                emp.getMgr(),
                emp.getSal(),
                emp.getComm(),
                emp.getDeptno()
        };
        return update(sql,params);
    }

    @Override
    public boolean upSalByEmpno(int empno, double sal) {
        String sql = "update emp set sal=? where empno=?";
        return update(sql,sal,empno);
    }

    @Override
    public boolean delEmp(int empno) {
        return update("delete from emp where empno=?",empno);
    }
}

package com.java.pojo;

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

/**
 *  Apache
 *  实体类的特征：
 *  1.私有化的成员变量
 *  2.公开的getter和setter
 *  3.至少提供一个无参构造器
 *  4.重写hashCode和equals方法
 *  5.重写toString方法
 *  6.实现序列号接口
 * @Auther:penghao
 * @Date:2019/4/7
 * @Description:com.java.pojo
 * @version:1.0
 */
public class Emp implements Serializable{
    private int empno;
    private String ename;
    private String job;
    private int mgr;
    private Date hiredate;
    private double sal;
    private double comm;
    private int deptno;

    public Emp() {
    }

    public Emp(int empno, String ename, String job, int mgr, Date hiredate, double sal, double comm, int 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;
    }

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

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

    @Override
    public int hashCode() {

        return Objects.hash(empno, ename, job, mgr, hiredate, sal, comm, deptno);
    }

    public int getEmpno() {
        return empno;
    }

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

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

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date 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 int getDeptno() {
        return deptno;
    }

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

package com.java.view;

import com.java.dao.EmpDao;
import com.java.dao.impl.EmpDaoImpl;
import com.java.pojo.Emp;

import java.util.List;
import java.util.Scanner;

/**
 * @Auther:penghao
 * @Date:2019/4/7
 * @Description:com.java.view
 * @version:1.0
 */
public class MenuView {
    Scanner sc = new Scanner(System.in);
    EmpDao dao = new EmpDaoImpl();

    public void showMenu(){
        System.out.println("*********************************");
        System.out.println("********欢迎使用员工管理系统********");
        System.out.println("*********************************");
        System.out.println("请输入对应数字进行操作:");
        while (true){
            System.out.println("1.添加员工");
            System.out.println("2.查询所有员工");
            System.out.println("3.根据编号查询员工");
            System.out.println("4.修改员工工资");
            System.out.println("5.删除员工");
            System.out.println("6.退出");
            int num = sc.nextInt();
            switch (num){
                case 1:
                    addEmp();
                    continue;
                case 2:
                    selAll();
                    continue;
                case 3:
                    selByEmpno();
                    continue;
                case 4:
                    updSal();
                    continue;
                case 5:
                    delEmp();
                    continue;
                case 6:
                    System.out.println("谢谢使用！拜拜~");
                    break;
                default:
                    System.out.println("输入有误，请重新输入！");
                    continue;
            }
            break;//结束循环
        }
    }
    private void delEmp(){
        System.out.println("请输入员工编号：");
        int empno = sc.nextInt();
        if (dao.delEmp(empno)){
            System.out.println("删除成功！");
        }else{
            System.out.println("删除失败！");
        }
    }
    private void updSal(){
        System.out.println("请输入员工编号：");
        int empno = sc.nextInt();
        System.out.println("请输入工资：");
        double sal = sc.nextDouble();
        if (dao.upSalByEmpno(empno,sal)){
            System.out.println("修改成功！");
        }else{
            System.out.println("修改失败！");
        }
    }
    private void selByEmpno(){
        System.out.println("请输入员工编号：");
        int empno = sc.nextInt();
        Emp emp = dao.selByEmpno(empno);
        System.out.println(emp);
    }
    private void selAll() {
        List<Emp> list = dao.selAll();
        for (Emp emp:list) {
            System.out.println(emp);
        }
    }

    private void addEmp() {
        System.out.println("请输入编号：");
        int empno = sc.nextInt();
        System.out.println("请输入姓名：");
        String ename = sc.next();
        System.out.println("请输入职位：");
        String job = sc.next();
        System.out.println("请输入领导编号：");
        int mgr = sc.nextInt();
        System.out.println("请输入工资：");
        double sal = sc.nextDouble();
        System.out.println("请输入提成：");
        double comm = sc.nextDouble();
        System.out.println("请输入部门编号：");
        int deptno = sc.nextInt();

        Emp emp = new Emp(empno, ename, job, mgr, null, sal, comm, deptno);
        if (dao.insEmp(emp)){
            System.out.println("添加成功！");
        } else{
            System.out.println("添加失败！");
        }
    }
}

package com.java.test;

import com.java.view.MenuView;

/**
 * @Auther:penghao
 * @Date:2019/4/7
 * @Description:com.java.test
 * @version:1.0
 */
public class TestEmp {
    public static void main(String[] args){
        new MenuView().showMenu();
    }
}