事务
四大特性(ACID):
原子性(atomicity):组成事务处理的语句形成了一个逻辑单元,不能只执行其中一部分;
一致性(consistentcy):在事务处理前后,数据是一致的(数据库的数据完整性约束);
隔离性(isolcation):一个事务对另一个事务的影响;
持续性(durability):事务处理的效果能够被永久的保存下来;
connection.setAutoCommit(false); //启动事务
connection.commit(); //提交事务
connection.rollback(); //回滚事务
当只想撤销事务中的部分操作时,可使用SavePoint
SavePoint sp = connection.setSavePoint(); //设置保存点
connection.rollback(savePoint); //回滚到保存点
connection.commit();
//事务&回滚测试
static void test() throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
st = conn.createStatement();
String sql = "update user set money=money-10 where id=1";
st.executeUpdate(sql);
sql = "select money from user where id=2";
rs = st.executeQuery(sql);
float money = 0.0f;
if (rs.next()) {
money = rs.getFloat("money");
}
if (money > 400)
throw new RuntimeException("已经超过最大值!");
sql = "update user set money=money+10 where id=2";
st.executeUpdate(sql);
conn.commit();
} catch (SQLException e) {
if (conn != null)
conn.rollback();
throw e;
} finally {
JdbcUtils.free(rs, st, conn);
}
}
设置SavePoint事务保存点
static void test() throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
Savepoint sp = null;
try {
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false);
st = conn.createStatement();
String sql = "update user set money=money-10 where id=1";
st.executeUpdate(sql);
sp = conn.setSavepoint();
sql = "update user set money=money-10 where id=3";
st.executeUpdate(sql);
sql = "select money from user where id=2";
rs = st.executeQuery(sql);
float money = 0.0f;
if (rs.next()) {
money = rs.getFloat("money");
}
if (money > 300)
throw new RuntimeException("已经超过最大值!");
sql = "update user set money=money+10 where id=2";
st.executeUpdate(sql);
conn.commit();
} catch (RuntimeException e) {
if (conn != null && sp != null) {
conn.rollback(sp);
conn.commit();
}
throw e;
} catch (SQLException e) {
if (conn != null)
conn.rollback();
throw e;
} finally {
JdbcUtils.free(rs, st, conn);
}
}
JTA:需要使用weblogic,websphere类的容器
跨越多个数据源的事务,使用JTA容器实现事务。分成两阶段提交。
javax.transaction.UserTransaction tx = (UserTransaction)ctx.lookup(“jndiName");
tx.begin();
//connection1 connection2 (可能来自不同的数据库)…
tx.commit();
//tx.rollback();
隔离级别多线程并发读取数据时的正确性
脏读:一个事务读取了另一个未提交的并行事务写的数据;
不可重复读:一个事务读取前面已经读取过的数据,发现数据已被另一个已提交的事务修改过;
幻读:一个事务再次读取数据,能够读取到另一个已提交事务增加的数据(另外一个事务提交的一行新的记录)。
隔离级别 | 不可重复读 | 幻读 | |
读未提交(Read uncommitted) | √ | √ | √ |
读已提交(Read committed) | × | √ | √ |
可重复读(Repeatable read) | × | × | √ |
可串行化(Serializable) | × | × | × |
select @@tx_isolation;
mysql设置数据库隔离级别:
set transaction isolation level read uncommitted;
mysql打开事务:
start transaction;
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
一般情况下我们不修改数据库的隔离级别
存储过程
难点在于怎样写而不是怎样调用
DELIMITER $$
DROP PROCEDURE EXISTS 'jdbc' . 'addUsers' $$
CREATE PROCEDURE 'jdbc' . 'addUsers' (in pname varchar(45), in birthday date, in
money float, out pid int)
BEGIN
insert into user(name,birthday,money) values(pname,birthday,money);
select last_insert_id() into pid;
END$$
DELIMITER ;
static void ps() throws SQLException {
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// conn = JdbcUtilsSing.getInstance().getConnection();
// 3.创建语句
String sql = "{ call addUser(?,?,?,?) } ";
cs = conn.prepareCall(sql);
cs.registerOutParameter(4, Types.INTEGER);
cs.setString(1, "ps name");
cs.setDate(2, new java.sql.Date(System.currentTimeMillis()));
cs.setFloat(3, 100f);
cs.executeUpdate();
int id = cs.getInt(4);
System.out.println("id=" + id);
} finally {
JdbcUtils.free(rs, cs, conn);
}
}
以上程序通过存储过程取得了插入记录的主键,下面介绍一个API来获取主键。
获取主键:
PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKey();
int id = rs.getInt(1);
static int create() throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// conn = JdbcUtilsSing.getInstance().getConnection();
// 3.创建语句
String sql = "insert into user(name,birthday, money) values ('name2 gk', '1987-01-01', 400) ";
ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.executeUpdate();
rs = ps.getGeneratedKeys();//返回结果是ResultSet可以防止联合主键的存在
int id = 0;
if (rs.next())
id = rs.getInt(1);
return id;
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
批处理,可以大幅度提升大量增删改的速度:
PreparedStatement.addBatch();
PreparedStatement.executeBatch();
我们直接使用批处理的可能性不大
static void createBatch() throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "insert into user(name,birthday, money) values (?, ?, ?) ";
ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < 100; i++) {
ps.setString(1, "batch name" + i);
ps.setDate(2, new Date(System.currentTimeMillis()));
ps.setFloat(3, 100f + i);
ps.addBatch();
}
int[] is = ps.executeBatch();
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
可滚动的结果集
Statement st = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(sql);
rs.beforeFirst(); rs.afterLast();rs.first();rs.isFirst();rs.last();rs.isLast();
rs.absolute(9);rs.moveToInsertRow();
可更新的结果集
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs.updateString("col name", "new value");
rs.updateRow();
自动更新数据库记录后,读取的为更新后的新数据;此功能一般少用,将其查询与修改混合在一起操作,不利于模块化。
DatabaseMetaData和ParameterMetaData
DatabaseMetaData meta = connection.getMetaData();
通过DatabaseMetaData可以获得数据库相关的信息如:数据库版本、数据库名、数据库厂商信息、是否支持事务、是否支持某种事务隔离级别,是否支持滚动结果集等。
java.sql.Connection conn = JdbcUtils.getConnection();
DatabaseMetaData dbmd = conn.getMetaData();
System.out.println("db name: " + dbmd.getDatabaseProductName());
System.out.println("tx: " + dbmd.supportsTransactions());
conn.close();
ParameterMetaData pmd = preparedStatement.getParameterMetaData();
通过 ParameterMetaData可以获得参数信息。
public class ParameterMetaTest {
public static void main(String[] args) throws SQLException {
Object[] params = new Object[] { "lisi", 100f };
read("select * from user where name=? and money > ?", params);
}
static void read(String sql, Object[] params) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
// ParameterMetaData pmd = ps.getParameterMetaData();
// int count = pmd.getParameterCount();
for (int i = 1; i <= params.length; i++) {
// System.out.print(pmd.getParameterClassName(i) + "\t");
// System.out.print(pmd.getParameterType(i) + "\t");
// System.out.println(pmd.getParameterTypeName(i));
ps.setObject(i, params[i - 1]);
}
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t"
+ rs.getString("name") + "\t" + rs.getDate("birthday")
+ "\t" + rs.getFloat("money"));
}
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
}
ResultSetMetaData
end