10.JDBC操作

讲到JDBC，这里面可讲的东西太多太多了，这地方主要讲几点：
1、连接数据库，connection
2、PrepareStatement
3、ResultSet
4、增删改查
5、批处理
6、数据库事务
代码参照：com.critc.JdbcTest

public class JdbcTest {
    /**
     * 获取数据库连接
     *
     * @return
     */
    public Connection getConn() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 新增
     */
    public void add() {
        Connection conn = getConn();
        String sql = "insert into staff(name) values(?)";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "张三");
            pstmt.executeUpdate();
            pstmt = null;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 修改
     */
    public void update() {
        Connection conn = getConn();
        String sql = "update staff set name=? where id=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "张三");
            pstmt.setInt(2, 1);
            pstmt.executeUpdate();
            pstmt = null;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 删除
     */
    public void delete() {
        Connection conn = getConn();
        String sql = "delete from staff  where id=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, 2);
            pstmt.executeUpdate();
            pstmt = null;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /***
     * 查询
     * @return
     */
    public List<Staff> query() {
        List<Staff> list = new ArrayList<>();
        Connection conn = getConn();
        String sql = "select * from  staff ";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Staff staff = new Staff();
                staff.setId(rs.getInt("id"));
                staff.setName(rs.getString("name"));
                list.add(staff);

            }
            pstmt = null;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    /**
     * 批量新增
     */
    public void addBatch() {
        Connection conn = getConn();
        String sql = "insert into staff(name) values(?)";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < 100; i++) {
                pstmt.setString(1, "张三" + i);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            pstmt = null;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 测试事务
     */
    public void testTransaction() {
        Connection conn = getConn();
        String sql = "insert into staff(name) values(?)";
        try {
            conn.setAutoCommit(false);
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "张三");
            pstmt.executeUpdate();
            PreparedStatement pstmt2 = conn.prepareStatement(sql);
            pstmt2.setString(1, "张三22");
            pstmt2.executeUpdate();
            conn.commit();
            pstmt = null;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        JdbcTest jdbcTest = new JdbcTest();
        Connection conn = jdbcTest.getConn();
        System.out.println("数据库连接：" + conn);
        jdbcTest.add();
        jdbcTest.update();
        jdbcTest.delete();
        jdbcTest.addBatch();
        List<Staff> list = jdbcTest.query();
        for (Staff staff : list) {
            System.out.println(staff.getName());
        }
        jdbcTest.testTransaction();
    }
}

下面主要说几点

• sql写法，sql一定要用占位符"?"，不要直接拼写，这地方是为了防止注入式攻击，另一个原因是减少数据库解析sql时间，提高sql的执行效率。
• 在批处理时，如果一次executeBatch的量过多，比如超过3000，可以分批次执行，这样提高效率
• 事务操作，这里面是最简单的事务，如果涉及两个库之上的就不能这么写了，需要采用跨库事务来解决。

源码下载

本例子详细源码