82_Java_JDBC3_DAO_事物
【摘要】 DAO_事物
DAO
1 通用DAO
public abstract class BaseDAO {
// 通用增删改
public int update(Connection conn, String sql, Object ...args) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1,args[i]);
}
int i = ps.executeUpdate();
return i;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResource(null,ps);
}
return 0;
}
// 查询单条记录
public <T> T getInstance (Connection conn, Class<T> clazz, String sql, Object ...args ) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int count = metaData.getColumnCount();
if (rs.next()){
T t = clazz.newInstance();
for (int i = 0; i < count; i++) {
Object value = rs.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,value);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
// 查询多条记录集合
public <T> List<T> getForList(Connection conn, Class<T> clazz, String sql, Object ...args) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int count = metaData.getColumnCount();
ArrayList<T> list = new ArrayList<T>();
while (rs.next()){
T t = clazz.newInstance();
for (int i = 0; i < count; i++) {
Object value = rs.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, value);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
}
finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
public <E> E getValue(Connection conn, String sql, Object ...args) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
if (rs.next()){
return (E) rs.getObject(1);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResource(null, ps, rs );
}
return null;
}
}
2 某表接口
public interface CustomerDAO {
// 插入数据库 insert into xxx value (xx,xx,xx)
void insert(Connection conn, Customer cust);
// 删除数据
void deleteid(Connection conn, int id);
//更新 update zz set name =xx ...
void update(Connection conn, Customer cust );
// 查询返回一条,select xxx,xxx,xxx from xx where id =?
Customer getCustomerById(Connection conn, int id);
// 查询返回多条,select xxx,xxx,xxx from xx where id >?
List<Customer> getCustomer(Connection conn);
List<Customer>getCustomer2(Connection conn, int id);
// 查询特殊值 select count(*) from xx
Long getCount(Connection conn);
// 查询特殊值 select max(xx) from xx
Date getMaxDate(Connection conn);
}
3 实现
public class CustomerDAOImpl extends BaseDAO implements CustomerDAO{
@Override
public void insert(Connection conn, Customer cust) {
String sql = "insert into customers(name,email,birth)values(?,?,?)";
update(conn, sql, cust.getName(),cust.getEmail(),cust.getBirth());
}
@Override
public void deleteid(Connection conn, int id) {
String sql = "delete from customers where id=?";
update(conn, sql, id);
}
@Override
public void update(Connection conn, Customer cust) {
String sql= "update customers set name=?, email=?, birth=? where id=?";
update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth(),cust.getId());
}
@Override
public Customer getCustomerById(Connection conn, int id) {
String sql = "select id,name,email,birth from customers where id =?";
Customer cust = (Customer) getInstance(conn, Customer.class,sql, id);
return cust;
}
@Override
public List<Customer> getCustomer(Connection conn) {
String sql = "select id, name, email,birth from customers";
List<Customer> list = getForList(conn, Customer.class, sql);
return list;
}
@Override
public List<Customer> getCustomer2(Connection conn, int id ) {
String sql = "select id, name, email,birth from customers where id > ? ";
List<Customer> list = getForList(conn, Customer.class, sql, id);
return list;
}
@Override
public Long getCount(Connection conn) {
String sql = "select count(id) from customers";
Long value = getValue(conn, sql);
return value;
}
@Override
public Date getMaxDate(Connection conn) {
String sql = "select max(birth) from customers";
Date value = getValue(conn, sql);
return value;
}
}
事物
@Test
public void test1() throws Exception {
Connection connection =null; // 确保传进来的必须是同一个连接,同时关闭自动提交
try {
connection = JDBCUtils.getConnection();
// connection.setAutoCommit(false);
// System.out.println(connection.getAutoCommit());
connection.setAutoCommit(false);
String sql1 = "update user_table set balance = balance - 100 where user = ?";
update(connection,sql1,"AA"); // 确保传进来的必须是同一个连接,同时关闭自动提交
System.out.println(10/0);
String sql2 = "update user_table set balance = balance + 100 where user = ?";
update(connection,sql2, "BB"); // 确保传进来的必须是同一个连接,同时关闭自动提交
System.out.println("ok");
connection.setAutoCommit(true);
} catch (Exception e) {
connection.rollback();
// System.out.println(connection.getAutoCommit());
e.printStackTrace();
} finally {
connection.setAutoCommit(true);
JDBCUtils.closeResource(connection,null);
}
}
@Test
public void test2() throws Exception {
Connection connection = JDBCUtils.getConnection();
System.out.println(connection.getTransactionIsolation());
connection.setTransactionIsolation(2); //TRANSACTION_READ_COMMITTED 2
// int rr = Connection.TRANSACTION_REPEATABLE_READ; int TRANSACTION_REPEATABLE_READ = 4;
System.out.println(connection.getTransactionIsolation());
connection.close();
Connection connection1 = JDBCUtils.getConnection();
System.out.println(connection1.getTransactionIsolation());
}
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)