cherish's blog
大道至简
完整JDBC操作流程

在此记录一下JDBC流程,本文分为两个部分。

首先是传统的方式,其次是封装Utils方式。使用的数据库是mysql5.7,数据库表结构如下图所示:

mysql> use db
Database changed

mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| account      |
+--------------+
1 row in set (0.00 sec)

mysql> desc account;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(25)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(25)  | YES  |     | NULL    |                |
| balance | varchar(255) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

首先是executeUpdate(String sql)方法执行DML(insert、update、delete)、DDL(create、alter、drop语句,因为用的较少不再演示)语句。完整的Java代码如下:

首先注册了mysql数据库驱动,通过DriverManager获取一个Connection对象,然后使用Connection对象创建一个Statament对象,Statement对象通过executeUpdate()方法执行SQL语句,并返回受影响的行数 account,然后关闭数据库连接,回收数据库资源。

public class JdbcDemo6 {
    public static void main(String args[]){

        //1.添加jar包

        Connection conn = null;
        Statement stmt = null;
        try {
            //2.注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            //3.获取数据库连接对象
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db?characterEncoding=utf-8", "root", "root");
            //4.定义sql语句
            String sql = "insert into account values(null,'王二',1000)";
            //5.获取执行sql语句的对象
            stmt = conn.createStatement();
            //6.执行sql语句
            int account = stmt.executeUpdate(sql);
            //处理结果
            System.out.println(account);
            if (account > 0)
            {
                System.out.println("插入一条记录");
            }else{
                System.out.println("插入失败!");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //7.释放资源,防止空指针异常
            if ( stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

由于executeUpdate(String sql)方法返回一个结果集,所以用while循环遍历结果集。如下:

/**
 * ResultSet的应用
 */
public class JdbcDemo7 {
    public static void main(String agrs[]){

        //添加jar包

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接对象
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db", "root", "root");
            //获取执行sql语句的对象Statement
            stmt = conn.createStatement();
            //定义sql语句
            String sql = "select * from account";
            //执行sql语句,返回结果
            //int account = stmt.executeUpdate(sql);

            rs = stmt.executeQuery(sql);
            //游标向下移动一行
            //rs.next();

            //循环判断是否是最后一行的行尾
            while (rs.next()){
                //获取数据
                int id = rs.getInt(1);
                String name = rs.getString("name");
                double balance = rs.getDouble(3);

                //处理结果
                System.out.println(id+"----"+name+"----"+balance);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            //关闭资源,防止空指针异常。最先开启的最后关闭。
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

查询结果如下:

"C:\Program Files\Java\jdk1.8.0_202\bin\java.exe"...
1----王二----1000.0
2----李四----1100.0

Process finished with exit code 0

如上文所述,每次需要重复做很多事情,比如注册驱动,执行sql语句,释放资源。那么就产生了JDBCUtils工具类。如下:

在src目录下新建jdbc.properties配置文件

url=jdbc:mysql://localhost:3306/db1
user=root
password=root
driver=com.mysql.jdbc.Driver

JDBCUtils工具类:

/**
 * JDBC工具类
 */
public class JDBCUtils {
    //声明成员变量
    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    /**
     * 文件的读取,只需要读取一次即可拿到这些值。使用静态代码块的方式。
     */
    static {
        //读取资源文件,获取值

        try {
            //1.创建Properties集合类
            Properties pro = new Properties();

            //获取src路径下的文件方式 ClassLoader 类加载器
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL res = classLoader.getResource("jdbc.properties");
            String path = res.getPath();

            System.out.println(path);

            //2.加载文件
            pro.load(new FileReader(path));


            //3.获取属性,赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            password = pro.getProperty("driver");

            //注册驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    /**
     * 获取连接
     * @return 连接对象
     */
    public static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(url,user,password);
    }


    /**
     * 释放资源
     * @param stmt
     * @param conn
     */
    public static void close(Statement stmt,Connection conn){

        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.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 throwables) {
                throwables.printStackTrace();
            }
        }

        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}

UtilsTest.java文件:

/**
 * 定义一个方法,查询emp表的数据将其封装为对象, 然后装载集合,返回。
 */
public class UtilsTest {

    public static void main(String[] args) {
        List<Emp> list = new JdbcDemo9().findAll();
        System.out.println(list);
        System.out.println(list.size());
    }

    /**
     * 查询所有emp对象
     * @return
     */
    public List<Emp> findAll(){

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        List<Emp> list = null;
        try {
           
            JDBCUtils.getConnection();

            //获取执行sql语句的对象
            stmt = conn.createStatement();
            //定义sql语句
            String sql = "select * from emp";
            //执行sql语句
            rs = stmt.executeQuery(sql);

            //遍历结果集,封装对象,装载集合
            Emp emp = null;
            list = new ArrayList<Emp>();
            while (rs.next()){
                //获取数据
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");

                //创建emp对象,并赋值
                emp = new Emp();

                emp.setId(id);
                emp.setEname(ename);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoindate(joindate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);

                //装载集合
                list.add(emp);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //释放资源
            JDBCUtils.close(rs,stmt,conn);
        }

        return list;
    }
}