JDBC数据库增删改查操作及事务操作

JDBC数据处理层面数据库常用增删改查操作

全文以Mysql中的User表为例

create table `User`(
id int(6),
username varchar(20),
password varchar(20),
realname varchar(20),
email varchar(20),
birthday date,
gendar char(1)
)

数据库常用操作：增删改查CRUD
涉及到解耦操作：面向接口开发
即，先写接口DAO，确定规范，再写实现类
一张数据表->一个实体类->一个DAO
下面是接口UserDao的代码：

public interface UserDao{
//增
    void saveUser(User user);
    //删
    void deleteUserById(int id);
    //改
    void modifyUserById(User user);
    //查
    User getUserByNameAndPwd(String name,String pwd);
    //查
    List<User> getAllUsers();
    //查
    int getUserCounts();
    //查
    User getUserById(int id);
}

通过实现类，实现接口中的所有方法：
UserDaoImpl

public class UserDaoImpl  implements UserDao{
//创建连接池
private ComboPooledDataSource ds = new ComboPooledDataSource();
//获得QueryRunner对象
private QueryRunner qu = new QueryRunner(ds);

通过传递一个User对象参数，将新建的user对象插入到表中 - update
对应sql语句：insert into user values()

    @Override
    public void saveUser(User user) {
        try {
            String sql = "insert into user values(null,?,?,?,?,?,?)";//预编译,id是自动增长的代理主键可传null
            qu.update(sql,user.getUsername(),user.getPassword(),user.getRealname(),user.getEmail(),user.getBirthday(),user.getGendar());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

通过获得的ID删除对应一行数据
对应sql语句：delete from user where id = ?;

    @Override
    public void deleteUserById(int id) {
    
    }

    @Override
    public void modifyUserById(User user) {
    String sql = "delete from user where id=?";
        try {
            qu.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

各种查询 selcet-query

    @Override
    public User getUserByNameAndPwd(String name, String pwd) {
    String sql = "select * from user where username=? and password = ?";
        try {
            User user = qu.query(sql, new BeanHandler<User>(User.class),name,pwd);
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> getAllUsers() {
     String sql = "select * from user";
        try {//将数据库的数据封装成对象
            List<User> users = qu.query(sql, new BeanListHandler<User>(User.class));
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

查询数量，query中结果集操作用ScalarHandler

    @Override
    public int getUserCounts() {
    String sql = "select count(*) from user";
        try {
            long count =(Long) qu.query(sql, new ScalarHandler());//将Object对象强转成Long包装类对象，在由long强转成int输出
            return (int)count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User getUserById(int id) {
    String sql = "select * from user where id=?";
        try {
            User users = qu.query(sql, new BeanHandler<User>(User.class), id);
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

事务操作

以转账操作为例：
转账操作中有两个方法，一个是转账方金额的减少，另一个是收款方金额的同等增加
问题一、一旦两个方法中间出现了异常，便会出现，一个钱增加了但另一个钱却减少了的情况
因为在JDBC操作事务时，默认是自动提交的，但是出现异常时我们需要回滚，这时候就要设置不自动提交：

connection.setAutoCommit(false);
//手动提交/回滚
connection.rollback();
connection.commit();

又因为业务中不能拆分的最小的单位是事务
事务：原子性、一致性、隔离、持久
必须保证连接是同一个

事务和连接绑定在一起
结论：要保证业务中开始事务的连接和Dao中操作的数据的连接是同一个
解决方案：事务绑定在线程中 ThreadLocal
ThreadLocal:用空间换时间
把公用的对象，给不同的线程创建了一个独立的副本
get()
set(T)
remove()

private static ComboPooledDataSource dataSource;
private static ThreadLocal<Connection> tl;//一个键值对
tl = new ThreadLocal<Connection>();
//通过set/get传入和得到当前连接
public static Connection getConnection(){
        if(tl.get() == null){
            try {
                tl.set(dataSource.getConnection());
                return tl.get();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }else{
            return tl.get();
        }
    }

public interface AcoountDao {
    void decMoney(int fromId,double money);
    void incMoney(int toId,double money);
}


public class AccountDaoImpl implements AcoountDao {
//    private ComboPooledDataSource ds = new ComboPooledDataSource();
    QueryRunner qu = new QueryRunner(C3P0Utils.getDataSource());
    @Override
    public void decMoney(int fromId, double money) {
        String sql = "update Account set money = money-? where id=?";
        try {
            qu.update(C3P0Utils.getConnection(),sql,money,fromId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void incMoney(int toId, double money) {
        String sql = "update Account set money = money +? where id=?";
        try {
            qu.update(C3P0Utils.getConnection(),sql,money,toId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

测试类：

public void test() throws SQLException {
        Connection connection = C3P0Utils.getConnection();
        connection.setAutoCommit(false);
        try {
            ad.decMoney(1, 100);
            ad.incMoney(2, 100);
        }catch (Exception e){
            connection.rollback();
        }
        connection.commit();
    }

问题二：
每个DAO中都创建了一个连接池，浪费资源
结论：每个项目中，连接池只创建一个
解决：封装一个DataSource工具类
工具类：

public class C3P0Utils {
    private static ComboPooledDataSource dataSource ;
    private static ThreadLocal<Connection> tl;
    static {
        dataSource = new ComboPooledDataSource();
        tl = new ThreadLocal<Connection>();
    }
    public static DataSource getDataSource(){
        return dataSource;
    }
    public static Connection getConnection(){
        if(tl.get() == null){
            try {
                tl.set(dataSource.getConnection());
                return tl.get();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }else{
            return tl.get();
        }
    }

}