第十五章 JDBC深入编程

1.练习

通过JDBC实现图书的添加和查询。

图书表: 编号，书名，作者，价格，出版日期。

#建表
create table book(
	id int primary key auto_increment,
	name char(20),
	auther char(10),
	price decimal(6,2),
	pucdate date
);

 2. 预编译命令对象

2.1 PreparedStatement

说明：

 ①继承自statement接口；

②预编译的sql语句对象；

③对sql语句进行预编译，存储到PreparedStatement对象中，可以多次执行命令，提高效率；

④杜绝注入式攻击，安全性高；使用的是参数式的sql命令。

insert into table (id,name,sex) values(?,?,?); 

2.2 使用PreparedStatement实现数据库操作

        通过连接对象获取PreparedStatement对象

 public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String Driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/lib?useSSL=false&characterEnding=utf8&serverTimezone=Asia/Shanghai";
        String user = "muxiaowen";
        String password = "123456";

        Connection con = null;
        PreparedStatement pst= null;

        Class.forName(Driver);
        con = DriverManager.getConnection(url,user,password);

        String name = "羽";
        String author ="五";
        int price = 21;
        String pucDate = "2013-3-3";
        String sql = "insert into book(name,author,price,pucdate) " +
                "value " +
                "(?,?,?,?)";

        pst = con.prepareStatement(sql);
        pst.setObject(1,name);
        pst.setString(2,author);
        pst.setInt(3,price);
        pst.setDate(4, Date.valueOf(pucDate));
        //pst.setObject(5,"wen");#没有第5个? 会直接报错

        /**
         * 直接打印pst 显示最终的sql语句
         */
        System.out.println(pst);
        
        /**
         * 执行sql语句插入一条数据
         */
        int count = pst.executeUpdate();
        System.out.println("受影响的数据条数："+count);
    }

方法	说明
executeUpdate()	执行增删改操作	返回影响行数
executeQuery()	执行查询操作	返回ResultSet结果集对象
setObject(参数索引,参数值)	设置sql中 ? 参数，替换为对象数据(也可以是字符串，整形，日期等类型的数据)

2.3 获取自增列的值

插入数据后，将该条记录的自增值获取

        ......
        /**
         * pst = con.prepareStatement(sql);
         * 创建预编译命令对象时，要提供获取自增值的参数
         */
        pst = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

        pst.setObject(1,name);
        pst.setString(2,author);
        pst.setInt(3,price);
        pst.setDate(4, Date.valueOf(pucDate));
        //pst.setObject(5,"wen");#没有第5个? 会直接报错

        /**
         * 直接打印pst 显示最终的sql语句
         */
        System.out.println(pst);

        /**
         * 执行sql语句插入一条数据
         */
        int count = pst.executeUpdate();
        System.out.println("受影响的数据条数："+count);

        /**
         * 获取自增返回的结果集对象
         */
        ResultSet rs = null;
        rs = pst.getGeneratedKeys();
        if(rs.next()){
            int id = rs.getInt(1);
            System.out.println("自增值:"+id);
        }

       rs.close();
       pst.close();
       con.close();

3. 事务

        一种机制，确保一系列的数据操作作为原子(整体)去执行，都执行或者回滚。

事务的特性： 

 原子性，一致性，隔离性，持久性(同数据库事务)

        JDBC的事务是用Connection对象控制的，Connection 接口(java.sql.connection)提供了两种事务模式：自动提交(默认)和手动提交 ；

java.sql.Connection 提供了以下控制事务的方法：

        public void setAutoCommit(boolean) 设置事务的提交方式

        public boolean getAutoCommit() --获取自动提交的状态

        public void commit() --提交事务

        public void rollback() --回滚事务

con = getConnection();
//设置手动事务
con.setAutoCommit(false);
//提交事务
con.commit();
//回滚事务
con.rollback();

        String Driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/test_0720_1?useSSL=false&characterEnding=utf8&serverTimezone=Asia/Shanghai";
        String user = "muxiaowen";
        String password = "123456";

        Connection con = null;
        PreparedStatement pst= null;

        try {
            Class.forName(Driver);
            con = DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        String sql = "update bank" +
                "   set money = money + ? " +
                "   where account = ?";
        /**
         * 使用手动事务
         * 模拟张三给李四转账
         */
        try {
            con.setAutoCommit(false);

            pst = con.prepareStatement(sql);

            pst.setInt(1,-1000);
            pst.setString(2,"张三");
            int res = pst.executeUpdate();
            System.out.println("更新记录数:"+res);
            /**
             * 模拟中途出错
             */
            int n = 1/0 ;
            pst.setInt(1,1000);
            pst.setString(2,"李四");
            res = pst.executeUpdate();
            System.out.println("更新记录数:"+res);

            //提交事务
            con.commit();
            System.out.println("事务提交成功!");
        } catch (Exception ex) {
            ex.printStackTrace();

            try {
                //回滚事务
                con.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }finally {
            try {
                pst.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

 4. 封装BaseDAO

        减少冗余代码

public class BaseDAO {
    /**
     * 数据库属性
     */
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/lib?useSSL=false&characterEnding=utf8&serverTimezone=Asia/Shanghai";
    private static final String USER = "muxiaowen";
    private static final String PASSWORD = "123456";
    /**
     * 获取数据库连接
     */
    public static Connection getConnection(){
        Connection con = null;
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            con= DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return con;
    }

    /**
     * 设置预编译命令对象
     * @param con
     * @param sql
     * @param params
     * @return
     */
    public static PreparedStatement setPst(Connection con,String sql,Object[] params){
        PreparedStatement pst = null;
        try {
            pst = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        if(params==null){
            return pst;
        }
        for (int i = 0; i < params.length; i++) {
            try {
                pst.setObject(i+1,params[i]);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return pst;
    }

    /**
     * 通用增删改
     * @return
     */
    public static int executeUpdate(PreparedStatement pst){
        int count = -1;
        try {
            count =  pst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }
    /**
     * 通用查询
     */
    public static List<Map<String,Object>> executeQuery(PreparedStatement pst){
        List<Map<String,Object>> rows = new ArrayList<>();
        ResultSet rs = null;
        try {
            rs = pst.executeQuery();

            ResultSetMetaData rSMD = rs.getMetaData();
            int colCount = rSMD.getColumnCount();
            while (rs.next()){
                Map map = new HashMap();
                for (int i = 1; i <= colCount; i++) {
                    String colName = rSMD.getColumnName(i);
                    Object colVal = rs.getObject(i);
                    map.put(colName,colVal);
                }
                rows.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rows;
    }

    /**
     * 关闭数据库对象
     * @param con
     * @param pst
     * @param rs
     */
    private static void closeAll(Connection con,PreparedStatement pst,ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(pst!=null){
            try {
                pst.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(con!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试：

@Test
    public void MyTest1(){
        String name = "羽";
        String auther ="五";
        int price = 21;
        String pucdate = "2013-3-3";
        Object[] params = {name,auther,price,pucdate};
        String sql = "insert into book(name,author,price,pucdate) " +
                "value " +
                "(?,?,?,?)";

        Connection con = BaseDAO.getConnection();
        PreparedStatement pst = BaseDAO.setPst(con,sql,params);

        System.out.println(BaseDAO.executeUpdate(pst));

        params = new Object[]{"羽"};
        sql = "select id,name,author,price,pucdate " +
                "   from book" +
                "   where name = ? ";
        pst = BaseDAO.setPst(con,sql,params);

        List<Map<String,Object>> list = BaseDAO.executeQuery(pst);

        for (Map map:list) {
            System.out.println(map);
        }
    }