千锋学习的44天：连接池，业务事务

千锋学习的44天：连接池，业务事务

连接池，实现connection池数据重用，注意配置文件书写正确性，学习的druiud连接池，重写connect.close()方法，实现回收连接池。

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;


import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class ConnPool {
    private static DruidDataSource ds;
    static {
        Properties properties=new Properties();
        InputStream inputStream=ConnPool.class.getResourceAsStream("database.properties");
        try {
            properties.load(inputStream);
            ds=(DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection get(){
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

业务是一种功能，事务是一个改变数据库事件，要保持线程数

据连接为同一个连接，才能保证数据提交和回滚

---------------------
业务模块
import java.sql.Connection;

public class TestDao {

    public String  upBalean(int uid,String pwd,int touid,int money){
        UserInfoDao userInfoDao=new UserInfoDao();
        String resu="失败";
        Connection connection=null;
        try {
            connection=AccessDb.get();
            connection.setAutoCommit(false);
            UserInfo ui=userInfoDao.select(uid);
            if (ui == null) {
               throw new RuntimeException("卡号不存在");
            }
            if (!ui.getUser_pwd().equals(pwd)) {
                throw new RuntimeException("密码错误");
            }
            UserInfo toui=userInfoDao.select(touid);
            if (toui==null){
                throw new RuntimeException("对方不存在");
            }
            ui.setBalance(ui.getBalance()-money);
            userInfoDao.update(ui);
            toui.setBalance(toui.getBalance()+money);
            userInfoDao.update(toui);
            resu="成功";
            connection.commit();
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            AccessDb.closeAll(connection,null,null);
        }
        return resu;
    }
}

---

dao方法

import com.sun.xml.internal.fastinfoset.tools.FI_StAX_SAX_Or_XML_SAX_SAXEvent;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserInfoDao {
   private Connection connection=null;
   private PreparedStatement preparedStatement=null;
   private ResultSet resultSet=null;
    public int insert(UserInfo userInfo){
         connection = AccessDb.get();
         String sql="insert into userinfo(user_id,user_name,user_pwd,user_borndate,user_email,user_address,balance)values(?,?,?,?,?,?,?);";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,userInfo.getUser_id());
            preparedStatement.setString(2,userInfo.getUser_name());
            preparedStatement.setString(3,userInfo.getUser_pwd());
            preparedStatement.setDate(4,UtilDate.toSqlDate(userInfo.getBorndate()));
            preparedStatement.setString(5,userInfo.getUser_email());
            preparedStatement.setString(6,userInfo.getUser_address());
            preparedStatement.setInt(7,userInfo.getBalance());
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            AccessDb.closeAll(null,preparedStatement,null);
        }
        return 0;
    }
    public int delete(int id){
        connection=AccessDb.get();
        String sql="delete from userinfo where=?";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            AccessDb.closeAll(null,preparedStatement,null);
        }
        return 0;
    }
    public int update(UserInfo userInfo){
        connection = AccessDb.get();
        String sql="update userinfo set user_name=?,user_pwd=?,user_borndate=?,user_email=?,user_address=?,balance=? where user_id=?;";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,userInfo.getUser_name());
            preparedStatement.setString(2,userInfo.getUser_pwd());
            preparedStatement.setDate(3,UtilDate.toSqlDate(userInfo.getBorndate()));
            preparedStatement.setString(4,userInfo.getUser_email());
            preparedStatement.setString(5,userInfo.getUser_address());
            preparedStatement.setInt(6,userInfo.getBalance());
            preparedStatement.setInt(7,userInfo.getUser_id());
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            AccessDb.closeAll(null,preparedStatement,null);
        }
        return 0;

    }
    public UserInfo select(int id){
        connection = AccessDb.get();
        String sql="select user_id,user_name,user_pwd,user_borndate,user_email,user_address,balance from userinfo where user_id=?;";
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                UserInfo user=new UserInfo(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getDate(4),resultSet.getString(5),resultSet.getString(6),resultSet.getInt(7));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            AccessDb.closeAll(null,preparedStatement,resultSet);
        }
        return null;
    }
    public List<UserInfo> selectAll(){
       connection= AccessDb.get();
       String sql="select user_id,user_name,user_pwd,user_borndate,user_email,user_address,balance from userinfo;";
        try {
           preparedStatement= connection.prepareStatement(sql);
          resultSet= preparedStatement.executeQuery();
          List<UserInfo> ul=new ArrayList<>();
          while(resultSet.next()){
              UserInfo userInfo=new UserInfo(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getDate(4),resultSet.getString(5),resultSet.getString(6),resultSet.getInt(7));
              ul.add(userInfo);
          }
          return ul;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            AccessDb.closeAll(null,preparedStatement,resultSet);
        }
        return null;
    }
}

数据库连接

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class AccessDb {
    private static final Properties properties=new Properties();
    private static final ThreadLocal<Connection> th=new ThreadLocal<Connection>();
    static {
        InputStream input=AccessDb.class.getResourceAsStream("db.properties");
        try {
            properties.load(input);
            Class.forName(properties.getProperty("driver"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection get(){
        Connection connection = th.get();
        try {
            if(connection==null) {
                connection = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("user"), properties.getProperty("password"));
            }
            return connection;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    public static void closeAll(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        try {
            if (resultSet!=null){
                resultSet.close();
            }
            if (preparedStatement!=null){
                preparedStatement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}