事务

一、事务

特性【ACID】

原子性【A】

一致性【C】

隔离性【I】

持久性【D】

package com.rr.transaction; ​ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Savepoint; ​ import com.rr.utils.JdbcUtil; ​ public class Demo { ​ public static void main(String[] args) { // TODO Auto-generated method stub // account(); account2(); } public static void account(){ Connection conn = JdbcUtil.getConnection(); PreparedStatement statement1 = null; PreparedStatement statement2 = null; try { //取消自动提交，相当于开启事务 conn.setAutoCommit(false); String sql1 = "update account set money=money-1000 where name='zhangsan'"; statement1 = conn.prepareStatement(sql1); statement1.executeUpdate(); String sql2 = "update1 account set money=money+1000 where name='lisi'"; statement2 = conn.prepareStatement(sql2); statement2.executeUpdate(); //如果执行语句没有异常，提交 conn.commit(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); try { //如果执行sql语句异常，回滚 conn.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } }finally{ try { statement1.close(); statement2.close(); conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public static void account2(){ Connection conn = JdbcUtil.getConnection(); PreparedStatement statement1 = null; PreparedStatement statement2 = null; //还原点 Savepoint savePoint = null; try { //取消自动提交，相当于开启事务 conn.setAutoCommit(false); String sql1 = "update account set money=money-1000 where name='zhangsan'"; statement1 = conn.prepareStatement(sql1); statement1.executeUpdate(); String sql2 = "update account set money=money+1000 where name='lisi'"; statement2 = conn.prepareStatement(sql2); statement2.executeUpdate(); //设置还原点 savePoint = conn.setSavepoint(); sql1 = "update account set money=money-1000 where name='zhangsan'"; statement1 = conn.prepareStatement(sql1); statement1.executeUpdate(); sql2 = "update2 account set money=money+1000 where name='lisi'"; statement2 = conn.prepareStatement(sql2); statement2.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); try { //如果执行sql语句异常，回滚到指定还原点 conn.rollback(savePoint); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } }finally{ //如果执行语句没有异常，提交 try { conn.commit(); statement1.close(); statement2.close(); conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } ​ }

二、BeanUtils组件

Apache 提供， 用来方便的操作 javabean【java会用好的第三方组件】

要用commons-beanutils.jar包，但是依赖logging包，所以都要导

​ User u = new User(); u.setName("zhangsan"); try { //通过BeanUtils给对象中的属性赋值 //本质上，"age"表示实体类中的getAge和setAge方法 BeanUtils.setProperty(u, "age", 20); System.out.println(u.getName()); System.out.println(u.getAge()); Class clz = Class.forName("com.rr.beanutil.User"); Object obj = clz.newInstance(); BeanUtils.setProperty(obj, "name", "lisi"); //给属性赋值 BeanUtils.copyProperty(obj, "age", 30); System.out.println(BeanUtils.getProperty(obj, "name")); //对象间属性的值的拷贝 BeanUtils.copyProperties(obj, u); System.out.println(obj); } catch (IllegalAccessException | InvocationTargetException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }

三、JdbcUtil

​ public class JdbcUtil { ​ private static String url; private static String driver; private static String user; private static String password; static{ try { Properties pro = new Properties(); InputStream inputStream = JdbcUtil.class.getResourceAsStream("/jdbc.properties"); pro.load(inputStream); driver = pro.getProperty("mysql.driver"); url = pro.getProperty("mysql.url"); user = pro.getProperty("mysql.user"); password = pro.getProperty("mysql.password"); Class.forName(driver); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //获得连接 public static Connection getConnection(){ Connection connection = null; try { connection = DriverManager.getConnection(url, user, password); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return connection; } }

四、DBUtils组件

Apache 提供，对jdbc的

超轻量级的ORM(对象关系映射)框架，简化jdbc操作

/*1.*/ QueryRunner qr = new QueryRunner(); String sql = "SELECT * FROM user WHERE id=2"; User user = qr.query(conn ,sql ,new ResultSetHandler<User>(){   @Override  public User handle(ResultSet rs) throws SQLException {      User u = null;   if(rs.next()){            u = new User();         u.setId(rs.getInt("id"));         u.setUsername(rs.getString("username"));         u.setPassword(rs.getString("password"));       }   return u ; } });

/*2.获取查询到的第一行数据，BeanHandler内部将查询出的用户数据封装到User对象中，并返回*/ QueryRunner qr = new QueryRunner(); String sql = "SELECT * FROM user WHERE id=2"; //表中字段的名称必须和实体类型属性名相同 User user = qr.query(conn ,sql ,new BeanHandler<>(User.class)); user.getName();

/*3.返回集合数据，将查询到的数据封装到List集合中*/ QueryRunner qr = new QueryRunner(); String sql = "SELECT * FROM user WHERE id = 2"; User user = qr.query(coon ,sql ,new BeanListHandler<>(User.class));

/*4.获取查询到的第一行数据，BeanHandler内部将查询出的用户数据封装到User对象中，并返回*/ QueryRunner qr = new QueryRunner(); String sql = "SELECT * FORM user WHERE id = ?"; /*表中字段的名称必须和实体类型属性名相同*/ /*通过query方法向sql中的参数传参时，对应类型是 Object... */ User user = qr.query(conn ,sql ,new BeanHandler<>(User.class), 2); ​

/*5.*/ QueryRunner qr = new QueryRunner(); String sql = "SELECT * FROM user WHERE id = 2"; /*将查询到的数据放到Object数组中*/ Object[] arr = qr.query(conn ,sql ,new ArrayHandler()); Arrays.toString(arr);

/*6.将查询到的数据放到Object数组集合中*/ QueryRunner qr = new QueryRunner(); String sql = "SELECT * FROM user"; List<Object[]> arrList = qr.query(conn ,sql ,new ArrayListHandler()); for(Object[] objects : arrList){    Arrays.toString(objects); } ​

/*7.获得查询结果中第一行第一列的数据*/ QueryRunner qr = new QueryRunner(); String sql ="SELECT count(1) FROM user"; Long count = qr.query(connection ,sql ,new ScalarHandler<Long>()); ​ ​

插入

Connection connection = JdbcUtil.getConnection(); QueryRunner qr = new QueryRunner(); String sql = "insert into user(username,password) values(?,?)"; ​ //方案一 //qr.update(connection,sql,"aaa","bbb"); ​ //方案二 Object[] params = new Object[]{"aaa","bbb"}; qr.update(connection,sql,params);

public void add(Product pro){ Connection connection = JdbcUtil.getConnection(); String sql = "insert into products(productId, name, price, stock, addTime, uid) values(?,?,?,?,now(),?)"; QueryRunner qr = new QueryRunner(); Object[] params = new Object[]{ pro.getProductId(), pro.getName(), pro.getPrice(), pro.getStock(), GlobalInfo.userInfo.getId() }; try { qr.update(connection, sql, params); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { try { DbUtils.close(connection); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }