JDBC编程六步

JDBC编程

• JDBC完成数据库DML语句

import java.sql.*;

/*
JDBC完成delete
 */
public class JDBCTest02 {
    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        try {

            // 第一步：注册驱动
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());

            // 第二步：获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123");

            // 第三步：获取数据库连接对象
            stmt = conn.createStatement();

            // 第四步：执行sql语句 JDBC中SQL语句不需要提供分号结尾
            int count = stmt.executeUpdate("delete from dept where deptno=50");
            System.out.println(count == 1 ? "执行成功" : "执行失败");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

-将连接数据库中的信息配置到配置文件中

import java.sql.*;
import java.util.ResourceBundle;

// 将连接数据库中所有信息配置到配置文件中
public class JDBCTest04 {
    public static void main(String[] args) {

        // 使用资源绑定器绑定属性配置文件
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");


        Connection conn = null;
        Statement stmt = null;
        try {

            // 第一步：注册驱动
            try {
                //String driver = "com.mysql.jdbc.Driver";
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }

            // 第二步：获取连接
            //String url = "jdbc:mysql://localhost:3306/bjpowernode";
            //String user = "root";
            //String password = "123";
            conn = DriverManager.getConnection(url,user,password);

            // 第三步：获取数据库连接对象
            stmt = conn.createStatement();

            // 第四步：执行sql语句 JDBC中SQL语句不需要提供分号结尾
            String sql = "delete from dept where deptno=50";
            int count = stmt.executeUpdate(sql);
            System.out.println(count == 1 ? "执行成功" : "执行失败");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

• 处理查询结果集

/*
处理查询结果集
 */
import java.sql.*;
import java.util.ResourceBundle;

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

        // 使用资源绑定器绑定配置文件
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String user = bundle.getString("user");
        String password = bundle.getString("password");

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


        try {
            // 第一步：注册驱动
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }

            // 第二步：获取连接
            conn = DriverManager.getConnection(url,user,password);

            // 第三步：获取连接对象
            stmt = conn.createStatement();

            // 第四步：执行SQL语句
            // int executeUpdate(insert/delete/update)
            // ResultSet executeQuery(select)
            String sql = "select empno,ename,sal from emp";
            rs = stmt.executeQuery(sql); // 专门执行DQL语句的方法

            // 第五步：处理查询结果集
            while (rs.next()){
                /*
                // 以列的下标获取（JDBC中下标以1开始）
                String empno = rs.getString(1);
                String ename = rs.getString(2);
                String sal = rs.getString(3);
                System.out.println(empno + "," + ename + "," + sal);

                 */

               /*
               // 以列的名字获取（列名称是查询结果集的列名称）
                String empno = rs.getString("empno");
                String ename = rs.getString("ename");
                String sal = rs.getString("sal");
                System.out.println(empno + "," + ename + "," + sal);
                */

                // 除了可以以String类型取出外，还可以以其他类型取出
                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                double sal = rs.getDouble("sal");
                System.out.println(empno + "," + ename + "," + (sal + 100));

            }

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

    }
}