JDBC

JDBC 简介

将java语言和jdbc结合起来使程序不必为不同的平台编写不同的应用程序，只需写一遍程序就可以让它在任何平台上运行，这就是java语言"编写一次，处处运行 "的优势.

• a) Java DataBase Connectivity, java 数据库连接.
• b) SUN 公司提供的一套标准, 是一种用于执行 SQL 语句的

 

Java API

 

• > DriverManager(C), 管理驱动
• > Connection(I), 数据库连接
• > Statement(I), SQL 语句发送器
• > ResultSet(I), 结果集,返回结果。

 

JDBC 连接数据的步骤

 

1. a) 注册驱动
2. b) 建立数据库连接(Connection)
3. c) 准备 SQL 语句
4. d) 获取 SQL 语句发送器(Statement)
5. e) 发送并执行 SQL 语句, 得到结果集(ResultSet)
6. f) 处理结果集
7. g) 关闭资源(ResultSet, Statement, Connection)

package wq.jdbc;

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

/**
 * jdbc连接数据库，执行查询操作
 * @author Administrator
 *
 */
public class TestjdbcQuery {


    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //jdbc:oracle:thin:@127.0.0.1:1521:XE
        String url="jdbc:oracle:thin:@127.0.0.1:1522: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);//数据库的列的索引从一开始
             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();
         
    }

}

注：sid是一个数据库的唯一标识符，创造唯一的实例。

通常情况下：oracle 数据库的监听端口是 1521，也可能不是，不能思维固化

                      mysql 是3306 。

 

JDBC 执行查询操作

带异常处理和资源关闭

package wq.jdbc;

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

public class JdbcQuery {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
           //创建变量
        String url="jdbc:oracle:thin:@127.0.0.1:1522:orcl";
        String user="scott";
        String password="tiger";
         Connection conn=null;
         Statement stmt=null;
         ResultSet rs=null;
        try {
            //（1）注册驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //(2)创建数据库连接
             try {
                 conn=DriverManager.getConnection(url, user, password);
                //(3)准备sql语句
                String sql="select * from emp";
                //(4)创建发送器
                 stmt = conn.createStatement();
                //(5)发送sql语句，接收结果集
                 rs=stmt.executeQuery(sql);
                //(6)处理结果集
                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 (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            
        }
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

}

 

JDBC 执行 DML 操作

 

• DML 操作其实是更新数据的操作, 所以调用 executeUpdate
• 方法. 返回结果是一个 int, 代表受影响的行数.

package wq.jdbc;

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

public class JdbcUpdate {

    public static void main(String[] args) {
      String url="jdbc:oracle:thin:@localhost:1522:orcl";
      String user="scott";
      String password="tiger";
      
      Connection conn=null;
      Statement stmt=null;
      
      try {
        Class.forName("oracle.jdbc.OracleDriver");
         try {
            conn = DriverManager.getConnection(url, user, password);
            String sql="insert into emp values(7888,'xiaowei','clerk',7902,sysdate,500,200,20)";
            stmt=conn.createStatement();
             int ex = stmt.executeUpdate(sql);
             if(ex>0){
                 System.out.println("创建成功");
             }else{
                 System.out.println("创建失败");
             }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
        finally{
            if(stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

}

 

JDBC 的事务管理

 

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

 

package wq.jdbc;

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

import oracle.jdbc.driver.OracleDriver;

public class JdbcTransaction {

    public static void main(String[] args)  {
           String url="jdbc:oracle:thin:@localhost:1522:orcl";
           String user="scott";
           String password="tiger";
           
           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=4";
           
        
            try {
                Class.forName("oracle.jdbc.OracleDriver");

                conn=DriverManager.getConnection(url, user, password);
                //关闭事务的自动提交
                 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.setAutoCommit(true);
                }else{
                    System.out.println("转账失败");
                    System.out.println("事务回滚");
                    conn.rollback();
                }
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (SQLException e) {
            System.out.println("转账失败,异常信息"+e.getMessage());
            }
            try {
                if(stmt2!=null){
                    stmt2.close();
                }
                if(stmt1!=null){
                    stmt1.close();
                }
                if(conn!=null){
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
         
    }

}

 

简单封装 DBUtil

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

package wq.jdbc.util;

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

public class DBUtil {

    private static final String DRIVER = "oracle.jdbc.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@localhost:1522:orcl";
    private static final String USER = "scott";
    private static final String PASSWORD = "tiger";

    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(
                    "DBUtil.getConn(连接创建失败, 请检查[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 wq.jdbc.login;

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

import wq.jdbc.util.DBUtil;

public class Login {
   public static void main(String[] args) {
    
       Scanner sc = new  Scanner(System.in);
        System.out.println("请输入用户名");
        String user = sc.nextLine();
        System.out.println("请输入密码");
        String password =sc.nextLine();
        //准备sql语句
        String sql="select count(*)from t_user where username='"+user+"'and password='"+password+"'";
        System.out.println(sql);
        //创建数据库连接
        Connection conn = DBUtil.getConn();
        //创建发送器
       Statement stmt = DBUtil.getStmt(conn);
       ResultSet rs=null;
       //发送sql语句，得到结果集
       try {
        rs = stmt.executeQuery(sql);
        //解析结果集
        if(rs.next()){
            if(rs.getInt(1)==0){
                System.out.println("登录失败");
            }else{
                System.out.println("欢迎"+user+"登录");
            }
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        DBUtil.close(rs, stmt, conn);
        sc.close();
    }
}
}

 

 

PreparedStatement

 

• a) PreparedStatement 接口继承 Statement 接口
• b) 预处理发送器, 可以将 sql 语句进行预编译, 提高效率,防止 SQL 注入(1=1)
• c) SQL 语句中需要配合占位符(?)来使用

package wq.jdbc.login;

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

import wq.jdbc.util.DBUtil;
//sql注入1=1；
//?是一个占位符
public class Login {
   public static void main(String[] args) {
    
       Scanner sc = new  Scanner(System.in);
      
        System.out.println("请输入用户名");
        String user = 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;
       //发送sql语句，得到结果集
       try {
           //创建发送器

         pstmt= conn.prepareStatement(sql);
          
          //动态绑定参数
         
          pstmt.setString(1, user);
          pstmt.setString(2, password);
        //rs = pstmt.executeQuery(sql);
          rs=pstmt.executeQuery();
         
        //解析结果集
        if(rs.next()){
            if(rs.getInt(1)==0){
                System.out.println("登录失败");
            }else{
                System.out.println("欢迎"+user+"登录");
            }
        }
    } catch (SQLException e) {
        
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        DBUtil.close(rs, pstmt, conn);
        sc.close();
    }
}
}

封装后：

package com.bjsxt.login;

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

import com.bjsxt.util.DBUtil;

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

}

 

1. 使用 Properties 优化工具类

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

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

jdbc.driver=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
jdbc.user=bjsxt
jdbc.password=123

package com.bjsxt.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 软编码： 将配置信息提取生成一个配置文件， 然后让程序在执行过程中， 读取配置信息
 * 好处： 可以动态的获取配置信息， 有助于后续代码的维护
 * 
 * Java中， 提供了一个类， 叫Properties类， 用于读取properties配置文件
 */
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(
                    "DBUtil.getConn(连接创建失败, 请检查[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();
            }
        }
    }
}

 

1. 批量处理

a)当向数据库插入大量数据时, 需要提高执行效率
  不能频繁开关数据库连接
 要使用 PreparedStatement 而不使用 Statement
 要手动提交事务, 不要自动提交
 使用批处理, addBatch, executeBatch

 

package com.bjsxt.num;

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

import com.bjsxt.util.DBUtil;

public class TestNum {

    public static void main(String[] args) {
        // 1. 将2~100000之间的数字插入数据库, 并记录数字的类型
        int num = 100000;
        long start = System.currentTimeMillis();
        /*
         * 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));
                // 加入批处理
                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));
                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));
                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) + "')";
                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);
        }
    }

    /**
     * 判断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";
    }

    /**
     * 生成UUID
     * 
     * @return
     */
    private static String getUUID() {
        return UUID.randomUUID().toString().replace("-", "");
    }

}

 

package com.bjsxt.num;

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;

import com.bjsxt.util.DBUtil;

public class TestNum2 {

    public static void main(String[] args) {
        // 2. 查询某个数字是质数还是合数
        // selectType(199);
        // 3. 统计质数有多少, 合数有多少
        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);
        }
    }

}

 

 

1. 分层开发

 

• a)当一个项目功能比较多, 代码量比较大的时候, 需要使用分层开发.
• b)ORM, Object Relational Mapping, 对象关系映射
• c)代码的分层是通过包(package)来区分的, 常见的包名有:
•  实体类: entity, bean, pojo
•  工具类: util

数据访问层: dao, Data Access Object, 用于和数据库进行交互, 通常用于定义接口

 

1. 提取 BaseDao, 封装 update 方法

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

2) 使用 BeanUtils 封装 jdbc 查询

dao

package com.bjsxt.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.bjsxt.util.DBUtil;

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

package com.bjsxt.dao.impl;

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

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

public class EmpDaoImpl extends BaseDao implements EmpDao {

    @Override
    public List<Emp> selAll() {
        List<Emp> list = new ArrayList<>();
        
        String sql = "select * from emp";
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        ResultSet rs = null;
        try {
            rs = pstmt.executeQuery();
            while(rs.next()) {
                Emp emp = new Emp();
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emp.setMgr(rs.getInt("mgr"));
                emp.setHiredate(rs.getDate("hiredate"));
                emp.setSal(rs.getDouble("sal"));
                emp.setComm(rs.getDouble("comm"));
                emp.setDeptno(rs.getInt("deptno"));
                list.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, pstmt, conn);
        }
        
        return list;
    }

    @Override
    public Emp selByEmpno(int empno) {
        String sql = "select * from emp where empno=?";
        Connection conn = DBUtil.getConn();
        PreparedStatement pstmt = DBUtil.getPstmt(conn, sql);
        DBUtil.bindParam(pstmt, empno);
        ResultSet rs = null;
        try {
            rs = pstmt.executeQuery();
            if(rs.next()) {
                Emp emp = new Emp();
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emp.setMgr(rs.getInt("mgr"));
                emp.setHiredate(rs.getDate("hiredate"));
                emp.setSal(rs.getDouble("sal"));
                emp.setComm(rs.getDouble("comm"));
                emp.setDeptno(rs.getInt("deptno"));
                return emp;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, pstmt, conn);
        }
        return null;
    }

    @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 updSalByEmpno(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.bjsxt.dao;

import java.util.List;

import com.bjsxt.pojo.Emp;

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 updSalByEmpno(int empno, double sal);

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

pojo

package com.bjsxt.pojo;

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

/**
 * Apache
 * 实体类的特征:
 *     1. 私有化的成员变量
 *  2. 公开的getter和setter
 *  3. 至少提供一个无参构造器
 *  4. 重写hashCode和equals方法
 *  5. 重写toString方法
 *  6. 实现序列号接口
 * @author Administrator
 *
 */
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) {
        super();
        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 int hashCode() {
        final int prime = 31;
        int result = 1;
        long temp;
        temp = Double.doubleToLongBits(comm);
        result = prime * result + (int) (temp ^ (temp >>> 32));
        result = prime * result + deptno;
        result = prime * result + empno;
        result = prime * result + ((ename == null) ? 0 : ename.hashCode());
        result = prime * result + ((hiredate == null) ? 0 : hiredate.hashCode());
        result = prime * result + ((job == null) ? 0 : job.hashCode());
        result = prime * result + mgr;
        temp = Double.doubleToLongBits(sal);
        result = prime * result + (int) (temp ^ (temp >>> 32));
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Emp other = (Emp) obj;
        if (Double.doubleToLongBits(comm) != Double.doubleToLongBits(other.comm))
            return false;
        if (deptno != other.deptno)
            return false;
        if (empno != other.empno)
            return false;
        if (ename == null) {
            if (other.ename != null)
                return false;
        } else if (!ename.equals(other.ename))
            return false;
        if (hiredate == null) {
            if (other.hiredate != null)
                return false;
        } else if (!hiredate.equals(other.hiredate))
            return false;
        if (job == null) {
            if (other.job != null)
                return false;
        } else if (!job.equals(other.job))
            return false;
        if (mgr != other.mgr)
            return false;
        if (Double.doubleToLongBits(sal) != Double.doubleToLongBits(other.sal))
            return false;
        return true;
    }

    // Alt + Shift + s --> r --> Alt + a
    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;
    }

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

}

util

package com.bjsxt.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 软编码： 将配置信息提取生成一个配置文件， 然后让程序在执行过程中， 读取配置信息
 * 好处： 可以动态的获取配置信息， 有助于后续代码的维护
 * 
 * Java中， 提供了一个类， 叫Properties类， 用于读取properties配置文件
 */
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(
                    "DBUtil.getConn(连接创建失败, 请检查[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();
            }
        }
    }
}

view

package com.bjsxt.view;

import java.sql.Date;
import java.util.List;
import java.util.Scanner;

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

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.updSalByEmpno(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("添加失败!");
        }
    }
}

 

 

test

package com.bjsxt.test;

import com.bjsxt.view.MenuView;

public class TestEmp {

    public static void main(String[] args) {
        new MenuView().showMenu();
    }

}