一.批處理sql語句
1.addBatch(String sql)：添加批處理
2. executeBatch()：執(zhí)行批處理
3.clearBatch()：清空批處理
package com.huan.statemennt;
import com.huan.dao.Dao;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
/**
* Created by pc on 17-5-1.
*/
public class Dml_Demo {
String user ="root";
String password = "root";
String url = "jdbc:mysql://localhost:3306/eurasia_echarts?characterEncoding=UTF-8";
Connection conn = null;
PreparedStatement pstmt = null;
@Test
public void test() throws Exception{
conn =Dao.getDao();
//插入數(shù)據(jù)
String sql = " insert into mahuan (name,age) values(?,?); ";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"aaa");
pstmt.setInt(2,22);
pstmt.addBatch(); //添加一次預(yù)定義參數(shù)
pstmt.setString(1, "ccc");
pstmt.setInt(2, 45);
pstmt.addBatch(); //再添加一次預(yù)定義參數(shù)
//批量執(zhí)行預(yù)定義SQL
pstmt.executeBatch();
Dao.close(pstmt,conn);
}
}
二.插入獲取自增長
如何獲饶浔纭孕惜？
1.在參數(shù)中制定返回自增長列
`pstmt = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);`
2.在執(zhí)行更新后果录，獲取自增長列
`ResultSet rs = pstmt.getGeneratedKeys();`
案例：
package com.huan.auto;
import com.huan.dao.Dao;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* Created by pc on 17-5-3.
*/
public class Demo {
Connection conn = null;
PreparedStatement pstmt = null;
//普通插入沒有獲取自增長
@Test
public void testAutoIncrement() throws Exception {
String sql = " insert into mahuan (name,age) values(?,?); ";
conn = Dao.getDao();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"huan");
pstmt.setInt(2,22);
pstmt.executeUpdate();
Dao.close(pstmt,conn);
}
//插入獲取自增長
@Test
public void testAutoIncrement1() throws Exception {
String sql = " insert into mahuan (name,age) values(?,?); ";
conn = Dao.getDao();
//在參數(shù)中制定返回自增長列
pstmt = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
pstmt.setString(1, "sansan");
pstmt.setInt(2, 55);
pstmt.executeUpdate();
//在執(zhí)行更新后胸嘴，獲取自增長列
ResultSet rs = pstmt.getGeneratedKeys();
while(rs.next()){
System.out.println(rs.getInt(1));
}
Dao.close(rs,pstmt, conn);
}
}
三.事務(wù)編程
事務(wù)是指一組最小的邏輯操作單元，里面有多個操作組成。組成事務(wù)的每一部分都要同時提交成功矫钓，否則一旦操作失敗，整個操作會回滾
1.事務(wù)的ACID特性：
1.原子行（Atomicitu）：原子行是指食物是一個不可分割的整體，事務(wù)中要么全部都發(fā)生新娜，要么不發(fā)生赵辕。
2.一致性（Consistency）：事務(wù)必須使數(shù)據(jù)庫從一個一致性狀態(tài)轉(zhuǎn)換到另一個一致性的狀態(tài)。
3.隔離性（Lsolation）：當(dāng)并發(fā)訪問數(shù)據(jù)庫時概龄，數(shù)據(jù)庫會為每一個訪問者提供一個事務(wù)还惠，各事務(wù)之間時沒有關(guān)聯(lián)的。
4.持久性（Durability）：持久性是指一旦事務(wù)被提交私杜，他對數(shù)據(jù)庫中數(shù)據(jù)的改變就是永久性的蚕键。
2.方法:
1.void setAutoCommit （boolean autoCommit）：設(shè)置事務(wù)是否自動提交，如果設(shè)置為false,表示手動提交事務(wù)
2. void commit()：手動提交事務(wù)
3.void rollback()：回滾衰粹，當(dāng)出現(xiàn)異常時锣光，所有已執(zhí)行成功的代碼需要回退到事務(wù)開始前狀態(tài)。
4.Sacepoint setSavepoint(String name):回滾到指定位置
手動設(shè)置事務(wù)
package com.huan.test;
import com.huan.dao.Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Created by pc on 17-5-3.
*/
public class AccountDao {
private Connection conn;
private PreparedStatement pstmt;
//轉(zhuǎn)賬使用事務(wù)
public void trans() {
try {
//設(shè)置事物為手動提交
conn.setAutoCommit(false);
String sql_zz = " update account set money=money-100 where accountname='mahuan'; ";
String sql_sz = " update account set money=money+100 where accountname='xiaoming'; ";
conn = Dao.getDao();
pstmt = conn.prepareStatement(sql_zz);
pstmt.executeUpdate();
System.out.println("轉(zhuǎn)賬成功");
pstmt = conn.prepareStatement(sql_sz);
pstmt.executeUpdate();
System.out.println("收賬成功");
}catch (Exception e){
try {
//出現(xiàn)異陈脸埽回滾
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
try {
//提交
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
Dao.close(pstmt,conn);
}
}
}
指定回滾位置
package com.huan.test;
import com.huan.dao.Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Created by pc on 17-5-3.
*/
public class AccountDao {
private Connection conn;
private PreparedStatement pstmt;
//轉(zhuǎn)賬使用事務(wù)
public void trans() {
try {
//設(shè)置事物為手動提交
conn.setAutoCommit(false);
//第一次轉(zhuǎn)賬
String sql_zz = " update account set money=money-100 where accountname='mahuan'; ";
String sql_sz = " update account set money=money+100 where accountname='xiaoming'; ";
SavePoint sp = conn.setSavepoint();//回滾位置
//第二次轉(zhuǎn)賬
String sql_zz = " update account set money=money-200 where accountname='mahuan'; ";
String sql_sz = " update account set money=money+200 where accountname='xiaoming'; ";
conn = Dao.getDao();
pstmt = conn.prepareStatement(sql_zz);
pstmt.executeUpdate();
System.out.println("轉(zhuǎn)賬成功");
pstmt = conn.prepareStatement(sql_sz);
pstmt.executeUpdate();
System.out.println("收賬成功");
}catch (Exception e){
try {
//出現(xiàn)異程艿回滾到指定代碼塊
conn.rollback(sp);
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
try {
//提交
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
Dao.close(pstmt,conn);
}
}
}
