package com.saas.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;

public class TestTransactionDemo01 {

	public static String _STR=" PreparedStatement:  1.addBatch() 将一组参数添加到 PreparedStatement对象内部  2.executeBatch() 将一批参数提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。*  ";

	public static String _URL="jdbc:mysql://127.0.0.1:3306/shopping_thpad_com?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true";
	
	public static String _LOGIN_NAME="root";
	
	public static String _LOGIN_PASSWORD="123456";
	
	private static Connection connect = null;
	
	private PreparedStatement pstmt=null;
	
	private ResultSet rs = null;
	
	public static Connection getConnection() {
		try{
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		
		try {
			connect = DriverManager.getConnection(_URL, _LOGIN_NAME, _LOGIN_PASSWORD);	
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		return connect;
		
	}
	
	public void execInsert() {
		connect = getConnection();
		//添加
		String insert_sql ="insert into item_spec(item_spec_id,name,remarks) values(?,?,?)";
		try {
			connect.setAutoCommit(false);//事务开始，默认设置为false,表示不自动提交
			Calendar cal =	Calendar.getInstance();
		 	pstmt= connect.prepareStatement(insert_sql);
		 	pstmt.setString(1,String.valueOf(cal.getTimeInMillis()));
		 	pstmt.setString(2, _STR);
		 	pstmt.setString(3, "test333333333");
		 	//pstmt.executeUpdate();
		 	pstmt.addBatch();
		 	Calendar cal1 =	Calendar.getInstance();
		 	pstmt.setString(1,String.valueOf(cal1.getTimeInMillis()+2));
		 	pstmt.setString(2, "test_zhudansheng");
		 	pstmt.setString(3, "test_zhudansheng_3333333");
		 	pstmt.addBatch();
		 	Calendar cal2 =	Calendar.getInstance();
		 	pstmt.setString(1,String.valueOf(cal2.getTimeInMillis()+3));
		 	pstmt.setString(2, "g_test");
		 	pstmt.setString(3, "test_zhudansheng_test");
		 	
		 	pstmt.addBatch();
		 	pstmt.executeBatch();	//	执行批量处理语句。
		 	connect.commit();//执行事务
		 	System.out.println("提交事务成功");
		 	
		 	connect.setAutoCommit(true);
		 	this.doClose();
		}
		catch(SQLException e) {
			e.printStackTrace();
			
			try {
				if(!connect.isClosed()) {
					connect.rollback();
					System.out.println("提交事务失败");
				 	
					connect.setAutoCommit(true);
				}
			}
			catch(SQLException ee)  {
				ee.printStackTrace();
			}
			
		}
		finally {
			// 抛出异常则关闭所有链接
			this.doClose();
		}
	}
	
	public void execStatement() {
		connect = getConnection();
		
		String result_sql = "select * from item_spec ";//where item_spec_id=?
		
		try {
			pstmt = connect.prepareStatement(result_sql);
			//pstmt.setString(1, "1319729859937");
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				System.out.println(rs.getString("item_spec_id")+"-----------"+rs.getString("name")+"---------------"+rs.getString("remar