JDBC总结

JDBC简介:

客户端机器需要与服务器进行通讯，要操作数据库中的数据，执行SQL（Structured Query Language结构化查询语言）语句以及检索查询结果,在Java中实现这些活动的技术称作JDBC。

数据库管理系统介绍

DBMS（DataBase Management System）是指数据库管理系统；
目前DBMS的生产商众多，产品也不尽相同，如：
Oracle公司的Oracle系列；
Microsoft公司的Access系列和SQL Server系列；
Microsoft公司早期的FoxPro；
IBM公司的DB2；
Sybase公司的Sybase；
还有自由开源的MySQL等等。

JDBC工作方式示意图

JDBC中要到的java内置包

java.sql包也是Java内置的包，其中包含了一系列用于与数据库进行通信的类和接口；
java.sql包中的一些interface:

接口名称 说明 Connection 连接对象，用于与数据库取得连接 Driver 用于创建连接（Connection）对象 Statement 语句对象，用于执行SQL语句，并将数据检索到结果集（ResultSet）对象中 PreparedStatement 预编译语句对象，用于执行预编译的SQL语句，执行效率比Statement高 CallableStatement 存储过程语句对象，用于调用执行存储过程 ResultSet 结果集对象，包含执行SQL语句后返回的数据的集合

java.sql包中的一些class:

接口名称 说明 SQLException 数据库异常类，是其它JDBC异常类的根类，继承于java.lang.Exception，绝大部分对数据库进行操作的方法都有可能抛出该异常 DriverManager 驱动程序管理类，用于加载和卸载各种驱动程序，并建立与数据库的连接 Date 该类中包含有将SQL日期格式转换成Java日期格式的方法 TimeStamp 表示一个时间戳，能精确到纳秒

JDBC程序访问数据库步骤

JDBC程序访问数据库步骤具体步骤

1.加载驱动程序Class.forName()

---

也就是加载驱动类(有多种方式),这里的加载驱动类应该根据要使用的驱动的类进行加载:

数据库 加载类 mysql com.mysql.jdbc.Driver oracle oracle: oracle.jdbc.driver.OracleDriver sql server com.microsoft. Sqlserver.jdbc.SQLServerDriver

下面以oracle数据库为例子:
Class.forName(“oracle.jdbc.driver.OracleDriver”);

2. 获得连接对象Connection

---

成功加载驱动后，必须使用DriverManager类的静态方法getConnection来获得连接对象.
Mysql URL:

// ?useUnicode\=true&characterEncoding\=UTF8部分是设置Unicode和字符集jdbc\:mysql\://localhost\:3306/yves?useUnicode\=true&characterEncoding\=UTF8

Sql server URL:

jdbc:sqlserver://服务器名或IP:1433;databaseName=数据库名

Oracle URL:

 jdbc:oracle:thin:@服务名或IP:1521:数据库名

下面以oracle为例:

参数名 参数格式 url jdbc:oracle:thin:@服务名或IP:1521:数据库名 username String password String

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORA", “scott", “tiger");

3.创建语句对象Statement

---

通过Connection对象的createStatement方法来创建语句对象，才可以执行SQL语句；

    //创建失败抛出 SQLException    Statement statement = con.createStatement();

4.执行SQL语句

---

(1) .DML(数据库操作语句)

是执行DELETE、UPDATE和INSERT之类的数据库操作语句（DML），这样的语句没有数据结果返回.
这里以UPDATE为例子:

int executeUpdate(String sql)throws SQLException

executeUpdate(String sql)参数sql是要执行的SQL语句，执行成功返回受影响的行数，执行失败则抛出SQLException异常，必须捕捉.
eg:

statement .executeUpdate("INSERT INTO User VALUES(88,'Yves', '男')");

(2).DQL（数据查询语句）

是执行SELECT这样的数据查询语句（DQL），这样的语句将从数据库中获得所需的数据，使用Statement对象的executeQuery 方法执行；

ResultSet executeQuery(String sql)throws SQLException

参数sql是要执行的SQL语句，查询成功返回包含有结果数据的ResultSet对象，否则抛出SQLException异常，必须捕捉
eg:

ResultSet rs = statement .executeQuery("SELECT * FROM Friend");

5.关闭资源

---

当对数据库的操作结束后，应当将所有已经被打开的资源关闭，否则将会造成资源泄漏.
Connection对象、Statement对象和ResultSet对象都有执行关闭的close方法.

    //函数原型    void close() throws SQLException    rs.close();   //关闭ResultSet对象    statement.close();  //关闭Statement对象    con.close();  //关闭Connection对象

有可能抛出SQLException异常，必须捕捉；
请注意关闭的顺序，最后打开的资源最先关闭，最先打开的资源最后关闭。

配置tomcat连接池

1.先把连接oracle的驱动放到tomcat7中的common/lib中
2.在tomcat7目录下找到conf目录下找到context.xml文件
在这个文件中 找到context标签,在标签context中加入以下标签

<Resource      name="jdbc/OracleDS_en"      type="javax.sql.DataSource"      driverClassName="oracle.jdbc.driver.OracleDriver"      password=“tiger"      maxIdle="30"      maxWait="5000"      username=“scott"      url="jdbc:oracle:thin:@127.0.0.1:1521:orcl"      maxActive="20"/>

具体代码部分(以mysql为例子)

db.properties

driverClass=com.mysql.jdbc.Driverurl=jdbc\:mysql\://localhost\:3306/yves?useUnicode\=true&characterEncoding\=UTF8user=rootpassword=root

DBUtil.java

package cn.com.yves.utill;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;/** *  * ClassName: ConnectionUtils *  * @Description: get connection *  * @author Yves He * @date 2016-10-7 */public class DBUtill {    private static String driverClass;    private static String url;    private static String userName;    private static String pwd;    static {        // read propertiesFile        Properties pro = new Properties();        // attention :db.properties in src Dir        InputStream iStream = DBUtill.class.getClassLoader()                .getResourceAsStream("db.properties");        try {            pro.load(iStream);            driverClass = pro.getProperty("driverClass");            url = pro.getProperty("url");            userName = pro.getProperty("user");            pwd = pro.getProperty("password");            // load the class            Class.forName(driverClass);        } catch (Exception e) {            e.printStackTrace();        }    }    /**     * get connecton obj     *      * @return     * @throws SQLException     */    public static Connection getConnection() throws SQLException {        Connection conn = null;        try {            conn = DriverManager.getConnection(url, userName, pwd);        } catch (SQLException e) {            throw e;        }        return conn;    }    /**     * close the connection     *      * @param conn     * @throws SQLException     */    public static void close(Connection conn) throws SQLException {        if (conn != null) {            try {                conn.close();            } catch (SQLException e) {                throw e;            }        }    }    /**     * close the connection     *      * @param conn     * @param stm     * @param rs     * @throws SQLException     */    public static void close(Connection conn, Statement stm, ResultSet rs)            throws SQLException {        try {            if (rs != null) {                rs.close();            }            if (stm != null) {                stm.close();            }            if (conn != null) {                conn.close();            }        } catch (SQLException e) {            throw e;        }    }}

dao层实现类

package cn.com.yves.dao.impl;import java.lang.reflect.InvocationTargetException;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 cn.com.yves.bean.UserBean;import cn.com.yves.constant.Constant;import cn.com.yves.dao.UserDaoInf;import cn.com.yves.utill.DBUtill;import cn.com.yves.utill.ReflectUtill;public class UserDaoImpl implements UserDaoInf {    public List<UserBean> listAllUserBean() throws SQLException {        List<UserBean> list = new ArrayList<UserBean>();        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        final String sql = "select * from user";        try {            conn = DBUtill.getConnection();            pstm = conn.prepareStatement(sql);            rs = pstm.executeQuery();            UserBean uBean = null;            while (rs.next()) {                uBean = new UserBean();                uBean.setUserId(rs.getString("userid"));                uBean.setUserName(rs.getString("name"));                uBean.setUserPwd(rs.getString("password"));                uBean.setUserCount(rs.getInt("count"));                uBean.setUserNickName(rs.getString("nickname"));                uBean.setUserDesc(rs.getString("desc"));                uBean.setUserPhoneNumber(rs.getString("phoneNumber"));                uBean.setUserPowerId(rs.getInt("powerid"));                list.add(uBean);            }        } catch (SQLException e) {            throw e;        } finally {            DBUtill.close(conn, pstm, rs);        }        return list;    }    /**     * 精确查询 UserName by userId     */    public UserBean getUserBeanById(String userId) throws SQLException {        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        UserBean uBean = null;        final String sql = "select * from user where userid = ?";        try {            conn = DBUtill.getConnection();            pstm = conn.prepareStatement(sql);            pstm.setString(1, userId);            rs = pstm.executeQuery();            while (rs.next()) {                uBean = new UserBean();                uBean.setUserId(rs.getString("userid"));                uBean.setUserName(rs.getString("name"));                uBean.setUserPwd(rs.getString("password"));                uBean.setUserCount(rs.getInt("count"));                uBean.setUserNickName(rs.getString("nickname"));                uBean.setUserDesc(rs.getString("desc"));                uBean.setUserPhoneNumber(rs.getString("phoneNumber"));                uBean.setUserPowerId(rs.getInt("powerid"));            }        } catch (SQLException e) {            throw e;        } finally {            DBUtill.close(conn, pstm, rs);        }        return uBean;    }    /**     * 精确查询 UserName by allCount     */    public UserBean getUserBeanByAllCount(String allCount, int countType)            throws SQLException {        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        UserBean uBean = null;        if (countType == Constant.USER_COUNT_NUMBER) {        } else if (countType == Constant.USER_COUNT_NUMBER) {// 数字账号        } else if (countType == Constant.USER_COUNT_EMAIL) {// 邮箱账号            final String sql = "select * from user where name=?";            try {                conn = DBUtill.getConnection();                pstm = conn.prepareStatement(sql);                pstm.setString(1, allCount);                rs = pstm.executeQuery();                while (rs.next()) {                    uBean = new UserBean();                    uBean.setUserId(rs.getString("userid"));                    uBean.setUserName(rs.getString("name"));                    uBean.setUserPwd(rs.getString("password"));                    uBean.setUserCount(rs.getInt("count"));                    uBean.setUserNickName(rs.getString("nickname"));                    uBean.setUserDesc(rs.getString("desc"));                    uBean.setUserPhoneNumber(rs.getString("phoneNumber"));                    uBean.setUserPowerId(rs.getInt("powerid"));                }            } catch (SQLException e) {                throw e;            } finally {                DBUtill.close(conn, pstm, rs);            }        } else if (countType == Constant.USER_COUNT_PHONENUMBER) {// 手机账号        }        return uBean;    }    /**     * 模糊查询 UserName by allCount     */    public List<UserBean> getUserBeanByAllCountFuzzy(String allCount,            int countType) throws SQLException {        // 思考 : 判断账号的类型可以在前台做 也可以在后台做, 用正则表达式验证 ,具体在哪里做比较好呢        /* 传入的账户类型来建立模糊查询 类型有 email 手机 和数字账号 1: 代表数字账号 2: 邮箱 3: 手机 */        List<UserBean> list = new ArrayList<UserBean>();        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        if (countType == Constant.USER_COUNT_NUMBER) {        } else if (countType == Constant.USER_COUNT_NUMBER) {// 数字账号        } else if (countType == Constant.USER_COUNT_EMAIL) {// 邮箱账号            final String sql = "select * from user where name like " + "'%"                    + allCount + "%'";            try {                conn = DBUtill.getConnection();                pstm = conn.prepareStatement(sql);                rs = pstm.executeQuery();                while (rs.next()) {                    UserBean uBean = new UserBean();                    uBean.setUserId(rs.getString("userid"));                    uBean.setUserName(rs.getString("name"));                    uBean.setUserPwd(rs.getString("password"));                    uBean.setUserCount(rs.getInt("count"));                    uBean.setUserNickName(rs.getString("nickname"));                    uBean.setUserDesc(rs.getString("desc"));                    uBean.setUserPhoneNumber(rs.getString("phoneNumber"));                    uBean.setUserPowerId(rs.getInt("powerid"));                    list.add(uBean);                }            } catch (SQLException e) {                throw e;            } finally {                DBUtill.close(conn, pstm, rs);            }        } else if (countType == Constant.USER_COUNT_PHONENUMBER) {// 手机账号        }        return list;    }    /**     * 模糊查询 by userNickName     */    public List<UserBean> getUserBeanByNickName(String userNickName)            throws SQLException {        List<UserBean> list = new ArrayList<UserBean>();        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        // 思考: 如何防止sql注入,先用?代替,应该有两种方式实现 一种是包括% , 一种是不包括%        // 正确的语句:select * from user where name like "%yves%" ;        final String sql = "select * from user where nickname like " + "'%"                + userNickName + "%'";        try {            conn = DBUtill.getConnection();            pstm = conn.prepareStatement(sql);            rs = pstm.executeQuery();            UserBean uBean = null;            while (rs.next()) {                uBean = new UserBean();                uBean.setUserId(rs.getString("userid"));                uBean.setUserName(rs.getString("name"));                uBean.setUserPwd(rs.getString("password"));                uBean.setUserCount(rs.getInt("count"));                uBean.setUserNickName(rs.getString("nickname"));                uBean.setUserDesc(rs.getString("desc"));                uBean.setUserPhoneNumber(rs.getString("phoneNumber"));                uBean.setUserPowerId(rs.getInt("powerid"));                list.add(uBean);            }        } catch (SQLException e) {            throw e;        } finally {            DBUtill.close(conn, pstm, rs);        }        return list;    }    /**     * 新增 userBean     */    public boolean addUserBean(UserBean uBean) throws SQLException {        boolean bool = false;        Connection conn = null;        PreparedStatement pstm = null;        // id 为int 可以设置自增,String类型的id用uuid类唯一化        final String sql = "insert into user values(?,?,?,?,?,?,?,?)";        conn = DBUtill.getConnection();        try {            pstm = conn.prepareStatement(sql);            // 注意 ? 号的顺序应该与数据库字段的顺序一致            pstm.setString(1, uBean.getUserId());            pstm.setString(2, uBean.getUserName());            pstm.setInt(3, (int) uBean.getUserCount());            pstm.setString(4, uBean.getUserPhoneNumber());            pstm.setString(5, uBean.getUserPwd());            pstm.setString(6, uBean.getUserNickName());            pstm.setString(7, uBean.getUserDesc());            pstm.setInt(8, uBean.getUserPowerId());            int result = pstm.executeUpdate();            if (result != -1) {                bool = true;            }        } catch (SQLException e) {            throw e;            // 新增失败回滚。        } finally {            DBUtill.close(conn, pstm, null);        }        return bool;    }    /**     * 删除 userBean by userId     */    public boolean deleteUserBean(String userId) throws SQLException {        boolean bool = false;        Connection conn = null;        PreparedStatement pstm = null;        final String sql = "delete from user where userid=?";        try {            conn = DBUtill.getConnection();            pstm = conn.prepareStatement(sql);            pstm.setString(1, userId);            int result = pstm.executeUpdate();            if (result != -1) {                bool = true;            }        } catch (SQLException e) {            throw e;        } finally {            DBUtill.close(conn, pstm, null);        }        return bool;    }    /**     * 更新 userBean     */    public boolean updateUserBean(UserBean userBean, String[] updateAtrributes)            throws SQLException {        // 如何只更新某一部分的字段.        boolean bool = false;        int result = -1;        Connection conn = null;        PreparedStatement pstm = null;        // String sql = "update user set name=?,password=? where userid=?";        String sql = "update user set ";        for (String ss : updateAtrributes) {            try {                sql += ss + "=" + (String) ReflectUtill.getValue(userBean, ss)                        + ",";                sql = sql.substring(0, sql.lastIndexOf(","));                sql += " where userid=?";                // 如果反射没出错执行                try {                    conn = DBUtill.getConnection();                    pstm = conn.prepareStatement(sql);                    pstm.setString(1, userBean.getUserId());                    result = pstm.executeUpdate();                    if (result != -1) {                        bool = true;                    }                } catch (SQLException e) {                    // update失败 事务回滚.                    e.printStackTrace();                    throw e;                }            } catch (IllegalArgumentException e) {                e.printStackTrace();            } catch (IllegalAccessException e) {                e.printStackTrace();            } catch (InvocationTargetException e) {                e.printStackTrace();            }        }        return bool;    }    /**     * 更新userBean (全量)     */    public boolean updateUserBean(UserBean userBean) throws SQLException {        boolean bool = false;        Connection conn = null;        PreparedStatement pstm = null;        // id 为int 可以设置自增,String类型的id用uuid类唯一化        final String sql = "update user set name=?,phonenumber=?,password=?,nickname=?,`desc`=?,powerid=? where userid=? ";        conn = DBUtill.getConnection();        try {            pstm = conn.prepareStatement(sql);            // 注意 ? 号的顺序应该与数据库字段的顺序一致            pstm.setString(1, userBean.getUserName());            pstm.setString(2, userBean.getUserPhoneNumber());            pstm.setString(3, userBean.getUserPwd());            pstm.setString(4, userBean.getUserNickName());            pstm.setString(5, userBean.getUserDesc());            pstm.setInt(6, userBean.getUserPowerId());            pstm.setString(7, userBean.getUserId());            int result = pstm.executeUpdate();            if (result != -1) {                bool = true;            }        } catch (SQLException e) {            throw e;            // 新增失败回滚。        } finally {            DBUtill.close(conn, pstm, null);        }        return bool;    }    /**     * 验证账号和密码: 三种登录方式验证(用于登录验证)     */    public boolean validateUserBean(String userName, int countType,            String userPwd) throws SQLException {        boolean bool = false;        Connection conn = null;        PreparedStatement pstm = null;        ResultSet rs = null;        if (countType == Constant.USER_COUNT_NUMBER) {// 系统分配的账号登录        } else if (countType == Constant.USER_COUNT_EMAIL) {// 邮箱登录验证            // 暂时所有的验证都在这里做(手机号登录,邮箱登录,账号登录),现在只支持userName登录,也就是邮箱登录,但是邮箱登录也还验证.            final String sql = "select * from user where name=? and password=?";            try {                conn = DBUtill.getConnection();                pstm = conn.prepareStatement(sql);                pstm.setString(1, userName);                pstm.setString(2, userPwd);                rs = pstm.executeQuery();                while (rs.next()) {                    return true;                }            } catch (SQLException e) {                throw e;            } finally {                DBUtill.close(conn, pstm, rs);            }        } else if (countType == Constant.USER_COUNT_PHONENUMBER) {// 手机号登录验证        }        return bool;    }    /**     * 验证所有的账号是否存在     */    public boolean validateCount(String allCount, int countType)            throws SQLException {        boolean bool = false;        if (countType == Constant.USER_COUNT_NUMBER) {// 是系统分配的数字账号        } else if (countType == Constant.USER_COUNT_EMAIL) {// 是邮箱账号            // 邮箱登录验证            Connection conn = null;            PreparedStatement pstm = null;            ResultSet rs = null;            final String sql = "select * from user where name=?";            try {                conn = DBUtill.getConnection();                pstm = conn.prepareStatement(sql);                pstm.setString(1, allCount);                rs = pstm.executeQuery();                while (rs.next()) {                    return true;                }            } catch (SQLException e) {                throw e;            } finally {                DBUtill.close(conn, pstm, rs);            }        } else if (countType == Constant.USER_COUNT_PHONENUMBER) {// 是手机账号        }        return bool;    }}

总结

1. JDBC是使用Java程序操作数据库的技术；
2. 使用 Class类的forName 方法可以将驱动程序加载到 Java 解释器中；使用DriverManager类的getConnection方法获得Connection对象，从而建立与数据库的连接；使用Connection对象的createStatement方法创建语句对象，以便执行SQL语句；使用Statement对象的executeQuery或executeUpdate方法执行SQL语句，并使用ResultSet对象来操作结果数据；PreparedStatement接口允许创建预编译的SQL语句，并使得在后续阶段可以指定语句的参数。