事务管理操作

附上之前的jdbcutil类：

 

package jdbc.mis.util;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

public class jdbcutil {

   private static Properties p=new Properties();

   static {

       ClassLoader clo=Thread.currentThread().getContextClassLoader();

       InputStream in=clo.getResourceAsStream("db.properties");

       try {

        p.load(in);

        Class.forName(p.getProperty("conect"));

    } catch (Exception e) {

        // TODO Auto-generated catch block

        e.printStackTrace();

    }

   }

   public static Connection getconn()

   {

       try {

        return DriverManager.getConnection(p.getProperty("driver"), p.getProperty("username"), p.getProperty("password"));

    } catch (SQLException e) {

        // TODO Auto-generated catch block

        e.printStackTrace();

    }

    throw new RuntimeException("数据库连接异常");

   }

 

   public static void close(Connection conn,Statement st,ResultSet re)

   {

       try {

       if(re!=null)

            re.close();

    } catch (Exception e2) {

        // TODO: handle exception

        e2.printStackTrace();

    }

    finally {

        try {

            if(st!=null)

                st.close();

        } catch (Exception e3) {

            // TODO: handle exception

            e3.printStackTrace();

        }finally {

            try {

                if(conn!=null)

                    conn.close();

            } catch (Exception e4) {

                // TODO: handle exception

                e4.printStackTrace();

            }

        }

    }

   }

}

 

 

 

 

具体代码：

package jdbc.mis.yinghang;

import static org.junit.Assert.*;

import java.sql.Connection;

import org.junit.Test;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import jdbc.mis.util.jdbcutil;

public class anli {

   @Test

public void test() throws Exception {

       /*

        *  1):检查张无忌的账户余额是否大于等于1000.

           SELECT * FROM account WHERE  name = '张无忌' AND balance >= 1000;

           2):从张无忌的账户余额中减少1000.

           UPDATE account SET balance = balance - 1000 WHERE name = '张无忌';

           3):再在赵敏的账户余额中增加1000.

           UPDATE account SET balance = balance + 1000 WHERE name = ' 赵敏';

        * */

     

     

       String sql="SELECT * FROM account WHERE  name = ? AND money >= ?";

       Connection conn=jdbcutil.getconn();

       PreparedStatement ps=conn.prepareStatement(sql);

       ps.setString(1,"张无忌");

       ps.setInt(2, 15000);

       ResultSet re=ps.executeQuery();

       if(!re.next())

       {

          throw new RuntimeException("不存在");

       }

      

       sql="UPDATE account SET money = money - ? WHERE name = ?";

       ps=conn.prepareStatement(sql);

       ps.setString(2,"张无忌");

       ps.setInt(1, 1000);

       ps.executeUpdate();

       sql="UPDATE account SET money = money + ? WHERE name = ?";

       ps=conn.prepareStatement(sql);

       ps.setString(2,"赵敏");

       ps.setInt(1, 1000);

       ps.executeUpdate();

       jdbcutil.close(conn, ps, re);

    }

   @Test

public void test2() {

      String sql=null;

       Connection conn=null;

       PreparedStatement ps=null;

     

       try {

           conn=jdbcutil.getconn();

           conn.setAutoCommit(false);

           sql="UPDATE account SET money = money - ? WHERE name = ?";

           ps=conn.prepareStatement(sql);

          

           ps.setString(2,"张无忌");

           ps.setInt(1, 1000);

           ps.executeUpdate();

           //int a=1/0;

           sql="UPDATE account SET money = money + ? WHERE name = ?";

           ps=conn.prepareStatement(sql);

           ps.setString(2,"赵敏");

           ps.setInt(1, 1000);

           ps.executeUpdate();

           conn.commit();

    } catch (Exception e) {

        // TODO: handle exception

        e.printStackTrace();

    }finally {

         jdbcutil.close(conn, ps, null);

    }

}

}