import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map.Entry;
import java.util.Random;
import java.util.TreeMap;

import org.nutz.dao.Chain;
import org.nutz.dao.Cnd;
import org.nutz.dao.impl.NutDao;
import org.nutz.trans.Atom;
import org.nutz.trans.Trans;

import cn.gbase.jiangsu.data.transfer.bean.ShopUser;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public class TestDao {

    NutDao daoOut = new NutDao();
    MysqlDataSource dsOut = null;
    
    TreeMap<Long,String> msgMap = new TreeMap<Long,String>();

    private void setOutDataSource() {
        dsOut = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();

        // 更新连接信息
        dsOut.setServerName("localhost");
        dsOut.setDatabaseName("shop");
        dsOut.setUser("root");
        dsOut.setPassword("123456");

        // 设置数据源
        daoOut.setDataSource(dsOut);
    }

    private void doWork() throws SQLException, InterruptedException {
        setOutDataSource();

        // 初始为1
        daoOut.update(ShopUser.class, Chain.make("accountBalance", 0), Cnd.where("id", "=", 6));

        msgMap.clear();
        
        // 100个线程加1
        for (int i = 0; i < 100; i++) {
            new Thread() {
                public void run() {
                    //updateMoney();
                    //updateMoney2();
                    updateMoney3();
                    //updateMoneyJdbc();
                }
            }.start();
        }
        
        Thread.sleep(10000);
        // ShopUser u = daoOut.fetch(ShopUser.class, 6);
        // System.out.println(u.getAccountBalance());
        
        StringBuilder sb =new StringBuilder();
        for(Entry<Long,String> entry:msgMap.entrySet()){
            sb.append(entry.getKey());
            sb.append(":").append(entry.getValue()).append("\n");
        }
        System.out.println(sb.toString());

    }

    // 更新金额，使用同步，先get，再update，结果是OK的
    private synchronized void updateMoney() {

        // 随机延迟
        try {
            Thread.sleep(new Random().nextInt(1000));
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        // 读取
        ShopUser u = daoOut.fetch(ShopUser.class, 6);
        BigDecimal money = u.getAccountBalance();

        // 随机延迟
        try {
            Thread.sleep(new Random().nextInt(10));
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        // 更新
        daoOut.update(ShopUser.class, Chain.make("accountBalance", money.add(BigDecimal.ONE)), Cnd.where("id", "=", 6));

    }

    // 更新金额，使用事务，先get，再update，结果是ＮＧ的
    private void updateMoney2() {
        // 随机延迟
        try {
            Thread.sleep(new Random().nextInt(1000));
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        // 有事务的话，多个sql执行使用的是同一个connection
        // 无事务的话，不论select还是update，每次都是新开的一个connection
        Trans.exec(Connection.TRANSACTION_READ_COMMITTED, new Atom() {
            public void run() {
                // 读取
                ShopUser u = daoOut.fetch(ShopUser.class, 6);
                BigDecimal money = u.getAccountBalance();

                // 随机延迟
                try {
                    Thread.sleep(new Random().nextInt(10));
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }

                // 更新
                daoOut.update(ShopUser.class, Chain.make("accountBalance", money.add(BigDecimal.ONE)), Cnd.where("id", "=", 6));
            }
        });

    }
    
    // 更新金额，使用单句update，结果是OK的
    private void updateMoney3() {
        // 随机延迟，时间长一点，
        try {
            Thread.sleep(new Random().nextInt(1000));
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        // 有事务的话，多个sql执行使用的是同一个connection
        // 无事务的话，不论select还是update，每次都是新开的一个connection
        Trans.exec(Connection.TRANSACTION_READ_COMMITTED, new Atom() {
            public void run() {

                // 随机延迟，时间短一点，这样才可能在其他进程检索之前执行update
                try {
                    Thread.sleep(new Random().nextInt(10));
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                echo("---before update ",System.nanoTime());
                
                // 更新
                daoOut.update(ShopUser.class, Chain.makeSpecial("accountBalance", "+1"), Cnd.where("id", "=", 6));
                echo("---after update ",System.nanoTime());
                
                // 随机延迟，时间短一点，这样才可能在其他进程检索之前执行update
                try {
                    Thread.sleep(new Random().nextInt(10));
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                echo("---before update ",System.nanoTime());
                
                // 更新
                daoOut.update(ShopUser.class, Chain.makeSpecial("accountBalance", "+1"), Cnd.where("id", "=", 6));
                echo("---after update ",System.nanoTime());
            }
        });

    }

    /**
     * 试验事务级别用的，结果不太理解，可能是打印的顺序不对吧？或者是commit到打印，有时间空隙，其它事务能看到commit后数据？
     */
    private void updateMoneyJdbc() {

        // 随机延迟
        try {
            Thread.sleep(new Random().nextInt(1000));
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        Connection conn = null; // 连接对象
        PreparedStatement pstmt = null; // 预编译的SQL语句对象
        try {

            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf-8";
            conn = DriverManager.getConnection(url, "root", "123456");

            // 事务级别
            conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            conn.setAutoCommit(false);

            echo(Thread.currentThread().getName() + "-----after----set level-----",System.nanoTime());
            // 带参数的更新语句
            String sql = "select account_balance from shop_users where id=6";
            Statement st = conn.createStatement();
            st.execute(sql);
            st.getResultSet().next();
            BigDecimal money = st.getResultSet().getBigDecimal(1);

            echo(Thread.currentThread().getName() + "-----after----search-----" +money,System.nanoTime());
            
            // 随机延迟
            try {
                Thread.sleep(new Random().nextInt(10));
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            
            echo(Thread.currentThread().getName() + "-----before----update-----",System.nanoTime());
            sql = "update shop_users set account_balance = ? where id=6";
            pstmt = conn.prepareStatement(sql);
            pstmt.setBigDecimal(1, money.add(BigDecimal.ONE));
            pstmt.execute();

            echo(Thread.currentThread().getName() + "-----after----update-----",System.nanoTime());
            // 提交事务
            conn.commit();
            
            echo(Thread.currentThread().getName() + "-----after----commit-----",System.nanoTime());
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            try {
                conn.rollback(); // 回滚事务
                System.out.println("事务回滚成功，没有任何记录被更新！");
            } catch (Exception re) {
                System.out.println("回滚事务失败！");
            }
            e.printStackTrace();
        } finally {
            if (pstmt != null)
                try {
                    pstmt.close();
                } catch (Exception ignore) {
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (Exception ignore) {
                }
        }
    }
    
    
    private synchronized void echo(String msg, long time){
        msgMap.put(time, msg);
    }

    public static void main(String[] args) {
        TestDao xx = new TestDao();
        try {
            try {
                xx.doWork();
            } catch (InterruptedException e) {
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}