JDBC编程步骤

导入MySQL jar包(mysql-connector-java-5.0.8-bin.jar  ojdbc14.jar)

步骤一 装载驱动 DriverManager.registerDriver(new Driver());//该方法会将驱动装载两次,开发中通常使用Class.forName("com.mysql.jdbc.Driver");的方式加载驱动
步骤二 建立连接 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day13", "root", "abc");
步骤三 操作数据SQL 对于结果集处理
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
　　System.out.println(rs.getString("username"));
　　System.out.println(rs.getString("email"));
}
步骤四 释放资源
rs.close();
statement.close();
conn.close();

-----------------------------------------------------------------------------------------------------------------------------------------------------

基础操作代码:

package cn.itcast.jdbc;

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

import org.junit.Test;

public class JDBCTest {
    @Test
    public void demo4() {
        // 查询user表所有数据
        // 装载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("数据库装载驱动失败！");
        }

        // 数据库操作
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection("jdbc:mysql:///day13", "root",
                    "abc");
            String sql = "select * from user";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                System.out.println(rs.getString("username"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源时，先开的资源后关
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }

    }

    @Test
    public void demo3() throws Exception {
        // 滚动结果集 案例：将wangwu的username 改为 王五
        Class.forName("com.mysql.jdbc.Driver");

        Connection conn = DriverManager.getConnection("jdbc:mysql:///day13",
                "root", "abc");

        // 指定结果集支持滚动，并且可以修改
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

        String sql = "select * from user";

        ResultSet rs = stmt.executeQuery(sql);

        rs.absolute(3); // 等价于 next 3次
        // 修改username 值为王五
        rs.updateString("username", "王五");
        // 提交修改
        rs.updateRow();

        rs.close();
        stmt.close();
        conn.close();
    }

    @Test
    public void demo2() throws Exception {
        // 通过JDBC连接Oracle
        // 步骤一 加载mysql驱动实现类
        // DriverManager.registerDriver(new Driver());
        Class.forName("oracle.jdbc.driver.OracleDriver");

        // 步骤二 通过驱动管理器，获得数据库连接
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:xe", "system", "123");

        // 步骤三 向数据库发送SQL 语句 执行
        String sql = "select * from users";
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);

        // 对结果集进行操作
        while (rs.next()) {
            System.out.println(rs.getString("username"));
            System.out.println(rs.getString("email"));
        }

        // 步骤四 操作数据库后 ，关闭资源
        rs.close();
        statement.close();
        conn.close();
    }

    @Test
    public void demo1() throws Exception {
        // 编写第一个JDBC程序
        // 步骤一 加载mysql驱动实现类
        // DriverManager.registerDriver(new Driver());
        Class.forName("com.mysql.jdbc.Driver");

        // 步骤二 通过驱动管理器，获得数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql:///day13",
                "root", "abc");

        // 步骤三 向数据库发送SQL 语句 执行
        String sql = "select * from user";
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);

        // 对结果集进行操作
        while (rs.next()) {
            System.out.println(rs.getString("username"));
            System.out.println(rs.getString("email"));
        }

        // 步骤四 操作数据库后 ，关闭资源
        rs.close();
        statement.close();
        conn.close();
    }
}

JDBC的CRUD操作:

package cn.itcast.jdbc;

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

import org.junit.Test;

import cn.itcast.utils.JDBCUtils;

/**
 * JDBC对user表进行增删改查 c - create r--- read u --- update d --- delete
 * 
 * @author seawind
 * 
 */
public class JDBCCRUD {
    @Test
    // 数据的查询
    public void testSelect() {
        // 建立连接
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from user";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString("username"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.release(rs, stmt, conn);
        }
    }

    @Test
    // 数据的删除
    public void testDelete() {
        // 建立连接
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = JDBCUtils.getConnection();
            // 操作SQL
            String sql = "delete from user where username='王五'";
            stmt = conn.createStatement();
            int affectedRowNum = stmt.executeUpdate(sql);
            System.out.println(affectedRowNum);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.release(stmt, conn);
        }
    }

    @Test
    // 数据的修改
    public void testUpdate() {
        // 建立连接
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = JDBCUtils.getConnection();
            // 操作SQL
            String sql = "update user set password = 111"; // 修改所有人密码111
            stmt = conn.createStatement();
            int affectedRowNum = stmt.executeUpdate(sql);
            System.out.println(affectedRowNum);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.release(stmt, conn);
        }
    }

    @Test
    // 数据的增加
    public void testAdd() {
        // 建立连接
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = JDBCUtils.getConnection();
            // 操作SQL
            String sql = "insert into users values(null,'赵六','123','zhaoliu@itcast.cn')";
            stmt = conn.createStatement();
            int affectedRowNum = stmt.executeUpdate(sql);
            System.out.println(affectedRowNum);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.release(stmt, conn);
        }

    }
}

自定义工具类:

package cn.itcast.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

/**
 * 将增删改查操作中重复代码提取出来
 * 
 * @author seawind
 * 
 */
public class JDBCUtils {
    // 将与数据库相关参数，抽取出来 放到配置文件中
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;

    static {
        driverClass = ResourceBundle.getBundle("db").getString("driverClass");
        url = ResourceBundle.getBundle("db").getString("url");//db.properties文件位于src目录下即可
        user = ResourceBundle.getBundle("db").getString("user");
        password = ResourceBundle.getBundle("db").getString("password");
    }

    // 建立连接
    public static Connection getConnection() throws SQLException {
        loadDriver();
        return DriverManager.getConnection(url, user, password);
    }

    private static void loadDriver() {
        // 装载驱动
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("驱动加载失败！");
        }
    }

    public static void release(ResultSet rs, Statement stmt, Connection conn) {
        // 释放资源
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        release(stmt, conn);
    }

    public static void release(Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}

db.properties(位于工程src目录下):

#mysql
driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql:///day13
user = root
password =root

#oracle
#driverClass = oracle.jdbc.driver.OracleDriver
#url = jdbc:oracle:thin:@localhost:1521:xe
#user = system
#password =123