web11天mysql高级知识

来源：互联网发布：一代宗师经典台词知乎编辑：程序博客网时间：2024/06/05 14:50

1.jdbc事物

禁止自动提交con.setAutoCommit(false);
提交事物con.commit();
事物回滚con.rollback();
设置回滚点Savepoint savepoint = con.setSavepoint(); con.rollback(savepoint);

使用jdbc事物，是为了防止银行转账过程中出现断电导致客户端和服务端数据不统一改变的这种情况

列子：         Class.forName("com.mysql.jdbc.Driver");    // 第二步：创建连接    conn = DriverManager.getConnection(url, user, pwd);    // 第三步：准备sql语句    conn.setAutoCommit(false);// 禁止自动提交    // 买家 减少 钱    String sql = "update TbAccount set accbalance = accbalance - ? where accid = 22334455";    PreparedStatement stmt =  conn.prepareStatement(sql);    stmt.setInt(1, 500);    int affectedRows = stmt.executeUpdate();    System.out.println(affectedRows);    System.out.println( 1/0 );// 异常模拟    if(affectedRows >= 1) {        //卖家 加钱        String sql2 = "update TbAccount set accbalance = accbalance + ? where accid = 11223344";        PreparedStatement stmt2 =  conn.prepareStatement(sql2);        stmt2.setInt(1, 500);        int affectedRows2 = stmt2.executeUpdate();        System.out.println(affectedRows2);        conn.commit();    }

2.jdbc封装工具类

        实列：    private static final String JDBC_DRV = "com.mysql.jdbc.Driver";    private static final String JDBC_URL = "jdbc:mysql://127.0.0.1:3306/bank?Unicode=true&characterEncoding=utf8&useSSL=false";    private static final String JDBC_UID = "root";    private static final String JDBC_PWD = "123456";    //私有化 构造器    public DbUtil   () {        // TODO Auto-generated constructor stub        throw new AssertionError();//约定 一般这样写               防止建立对象    }//  初始化连接驱动    static{        try {            Class.forName(JDBC_DRV);        } catch (ClassNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    //连接数据库    public static Connection openConnection() {        try {            return DriverManager.getConnection(JDBC_URL, JDBC_UID, JDBC_PWD);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return null;    }    //关闭数据库    public static void closeConnection(Connection connection) {        try {            if (connection!=null&&!connection.isClosed()) {                connection.close();            }        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    //查询数据库    public static ResultSet excuteQuery(Connection connection,String sql,Object...params) {        PreparedStatement statement=null;        try {            statement=connection.prepareStatement(sql);            for(int i=0;i<params.length;i++){                statement.setObject(i+1,params[i]);            }            return statement.executeQuery();        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return null;        }    //增删改    public static int update(Connection connection,String sql,Object...params) {        PreparedStatement statement=null;        try {            statement=connection.prepareStatement(sql);            for (int i = 0; i < params.length; i++) {                statement.setObject(i+1, params);            }            return statement.executeUpdate();        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return 0;       }    2.如何使用这个封装好的工具类        Connection connection=DbUtil.openConnection();        String sql="select*from TbAccount where accid=?";        ResultSet set=DbUtil.excuteQuery(connection, sql, 11223344);        try {            while (set.next()) {                System.out.println(set.getString("accowner"));                System.out.println(set.getFloat("accbalance"));            }        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        DbUtil.closeConnection(connection);    }

3.连接池dbcp

数据库连接池负责分配、管理和释放数据库连，数据库连接池：（池用map来实现居多），用处：为了可重用（销毁创建麻烦，开销大）

    private static DataSource source;//?    private static final String JDBC_DRV = "com.mysql.jdbc.Driver";    private static final String JDBC_URL = "jdbc:mysql://127.0.0.1:3306/bank?Unicode=true&characterEncoding=utf8&useSSL=false";    private static final String JDBC_UID = "root";    private static final String JDBC_PWD = "123456";    //私有化 构造器    public DbUtil2  () {        // TODO Auto-generated constructor stub        throw new AssertionError();//约定 一般这样写               防止建立对象    }//  初始化连接驱动    static{    BasicDataSource basicDataSource=    new BasicDataSource();//基础数据源的创建    basicDataSource.setMinIdle(10);//设置空闲的连接数    basicDataSource.setMaxIdle(100);//设置最大并发数    basicDataSource.setMaxTotal(100);//设置最大连接数字    basicDataSource.setDriverClassName(JDBC_DRV);    basicDataSource.setUrl(JDBC_URL);    basicDataSource.setUsername(JDBC_UID);    basicDataSource.setPassword(JDBC_PWD);    source=basicDataSource;    }    //返回数据库连接池    public static DataSource getDataSource() {        return source;      }    //连接数据库    public static Connection openConnection() {        try {            return source.getConnection();        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return null;    }........其余一样    如何使用连接池:Connection connection=DbUtil2.openConnection();    String sql="select*from TbAccount where accid=?";    ResultSet set=DbUtil2.excuteQuery(connection, sql, 11223344);    try {        while (set.next()) {            System.out.println(set.getString("accowner"));            System.out.println(set.getFloat("accbalance"));            System.out.println("这是连接池");        }    } catch (SQLException e) {        // TODO Auto-generated catch block        e.printStackTrace();    }    DbUtil.closeConnection(connection);

4.Apache–cmmons-dbutil

Apache组织提供的一个资源JDBC工具类库，它是对JDBC的简单封装，学习成本低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不影响程序的性能。
DbUtils：提供如关闭连接、转载JDBC驱动程序等常规工作的工具等，里面的所有方法都是静态的。

QueryRunner类：该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。

    实列:    // TODO Auto-generated method stub    //增删查改    QueryRunner queryRunner=new QueryRunner(DbUtil2.getDataSource());    String sql="update TbAccount set accbalance = accbalance - ? where accid = ?";    try {    int nu= queryRunner.update(sql, 400,11223344);    System.out.println(nu==1?"更新成功":"更新失败");    } catch (SQLException e) {        // TODO Auto-generated catch block        e.printStackTrace();    }    //查询一条数据String  sql1="select * from TbAccount where accid = ?";    ResultSetHandler<People> rs=new ResultSetHandler<People>() {        @Override        public People handle(ResultSet rSet) throws SQLException {            // TODO Auto-generated method stub            if (rSet.next()) {                return People.creatPeople(rSet);            } else {                return null;            }        }    };    try {    People pl=(People)queryRunner.query(sql1, rs,22334455);    System.out.println(pl);    } catch (SQLException e) {        // TODO Auto-generated catch block        e.printStackTrace();    }    //查询多条语句    sql="select * from TbAccount";    ResultSetHandler<List<People>> rsh=new ResultSetHandler<List<People>>() {        @Override        public List<People> handle(ResultSet set) throws SQLException {            // TODO Auto-generated method stub            List<People>list =new ArrayList<People>();            while (set.next()) {                list.add(People.creatPeople(set));            }            return list;        }    };    try {        List<People> list=queryRunner.query(sql, rsh);        Iterator iterator=list.iterator();        while(iterator.hasNext()){            System.out.println((People)iterator.next());        }    } catch (SQLException e) {        // TODO Auto-generated catch block        e.printStackTrace();    }}//people类中的最重要的一个方法创建一个people类接收数据库的查询值public static People creatPeople(ResultSet set) {People pl=new People();try {    pl.setAccid(set.getString("accid"));    pl.setAccowner(set.getString("accowner"));    String string=String.valueOf(set.getFloat("accbalance"));    pl.setAccbalance(string);    return pl;} catch (SQLException e) {    // TODO Auto-generated catch block    e.printStackTrace();}return null;

}

阅读全文

0 0