Service操作

最新推荐文章于 2024-06-17 08:27:15 发布

原创最新推荐文章于 2024-06-17 08:27:15 发布 · 227 阅读

CC 4.0 BY-SA版权

本文详细介绍了如何使用Java实现数据库连接池，并通过线程本地变量确保多线程环境下的数据库操作安全。通过具体代码示例，展示了如何进行数据库的增删改查操作，以及在转账场景中如何利用连接池和事务管理实现数据的一致性和原子性。

package com.qf.day44.t3.Servise;

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

public class Util{



    private static final Properties properties = new Properties();
   private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();

    static {
        InputStream resourceAsStream = Util.class.getResourceAsStream("/db.properties");
        try {
            properties.load(resourceAsStream);

            Class.forName(properties.getProperty("Driver"));


        } catch (Exception e) {
            e.printStackTrace();
        }


    }


    public static Connection getConnection(){
        Connection connection = threadLocal.get();
        try {
            if (threadLocal.get() == null){
                connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username"),properties.getProperty("password"));
                threadLocal.set(connection);
            }

              return connection;
        } catch (SQLException 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();
                threadLocal.remove();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



}

package com.qf.day44.t3.Servise;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *  private String carId;
 *     private String password;
 *     private String username;
 *     private double balance;
 *     private String phone;
 */
public class UtilAccountDaoimpl {


    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    public static int insert(Account account){
        connection = Util.getConnection();
        String sql = "insert into Account(carId,password,username,balance,phone)values(?,?,?,?,?);";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,account.getCarId());
            preparedStatement.setString(2,account.getPassword());
            preparedStatement.setString(3,account.getUsername());
            preparedStatement.setDouble(4,account.getBalance());
            preparedStatement.setString(5,account.getPhone());


            return preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Util.closeAll(connection,preparedStatement,null);
        }


        return 0;
    }

    public static int delete(String id){
        connection = Util.getConnection();
        String sql = "delete from Account where carId = ?;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,id);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Util.closeAll(connection,preparedStatement,null);
        }
    return 0;

    }


    public static int update(Account account){
        connection = Util.getConnection();
        System.out.println("service:"+connection);
        String sql = "update Account set password = ?,username = ?,balance = ?,phone =? where carId = ? ";
        try {
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1,account.getPassword());
            preparedStatement.setString(2,account.getUsername());
            preparedStatement.setDouble(3,account.getBalance());
            preparedStatement.setString(4,account.getPhone());
            preparedStatement.setString(5,account.getCarId());

            return preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Util.closeAll(null,preparedStatement,null);
        }


        return 0;
    }


    public static Account select(String carId){
        System.out.println(Thread.currentThread().getName());

        connection = Util.getConnection();
        System.out.println("service:"+connection);
        String sql = "select carId,password,username,balance,phone from account where carId = ?";

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,carId);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                String carId2 = resultSet.getString("carId");
                String password = resultSet.getString("password");
                String username = resultSet.getString("username");
                double balance = resultSet.getDouble("balance");
                String phone = resultSet.getString("phone");
                Account account = new Account(carId2,password,username,balance,phone);

                //account = new Account(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3),resultSet.getDouble(4),resultSet.getString(5));
                return account;
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Util.closeAll(null,preparedStatement,null);
        }

        return  null;

    }


}

package com.qf.day44.t3.Servise;

public class Account {
    private String carId;
    private String password;
    private String username;
    private double balance;
    private String phone;


    public Account(String carId) {

        this.carId = carId;
    }

    @Override
    public String toString() {
        return "Account{" +
                "carId='" + carId + '\'' +
                ", password='" + password + '\'' +
                ", username='" + username + '\'' +
                ", balance=" + balance +
                ", phone='" + phone + '\'' +
                '}';
    }

    public String getCarId() {
        return carId;
    }

    public void setCarId(String carId) {
        this.carId = carId;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public Account(String carId, String password, String username, double balance, String phone) {
        this.carId = carId;
        this.password = password;
        this.username = username;
        this.balance = balance;
        this.phone = phone;
    }
}

package com.qf.day44.t3.Servise;

import java.sql.Connection;
import java.sql.SQLException;

public class Serviceimpl {



    public String login(String carId ,String password){
        //通过carId查询是否有此用户
        Account selects = UtilAccountDaoimpl.select(carId);

        if (selects != null){
            if (selects.getPassword().equals(password)){
                return "登录成功";
            }
            else {
                return "密码错误";
            }

        }else {
            return "账户不存在";
        }



    }

    public String transfer(String carId,String password ,String toCarId,double money){
        Connection connection = null;
        System.out.println(Thread.currentThread().getName());
        String result = "转账失败";

        try {
            connection = Util.getConnection();
            System.out.println("service:"+connection);
            connection.setAutoCommit(false);
            //1验证用户是否存在
            Account select = UtilAccountDaoimpl.select(carId);
            if (select == null){
                throw new RuntimeException("用户不存在");
            }
            //2验证密码是否正确
            if (!select.getPassword().equals(password)){
                throw new RuntimeException("密码错误");
            }
            //3验证余额是否充足
            if(select.getBalance()<money){
                throw new RuntimeException("余额不足");
            }

            //4验证对方卡号是否存在
            Account select1 = UtilAccountDaoimpl.select(toCarId);
            if (select1 == null){
                throw new RuntimeException("对方卡号不存在");
            }
            //5自身用户减钱
            select.setBalance(select.getBalance()-money);
            UtilAccountDaoimpl.update(select);


            //6对方用户加钱
            select1.setBalance(select1.getBalance()+money);
            UtilAccountDaoimpl.update(select1);
            result = "转账成功";
            connection.commit();

        } catch (Exception e) {
            System.out.println("出现异常，执行回滚");
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            Util.closeAll(connection,null,null);
        }


        return result;
    }

    
}

package com.qf.day44.t3.Servise;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.qf.dau44.t2.Druid;

public class TestMain {
    public static void main(String[] args) {
        Serviceimpl serviceimpl = new Serviceimpl();

//        String login = serviceimpl.login("2", "1234");
//        System.out.println(login);


        String transfer = serviceimpl.transfer("3", "1234", "2", 100);
        System.out.println("主线程"+transfer);
        CheckThread checkThread = new CheckThread();
        checkThread.start();




    }
}
class CheckThread extends Thread{
    Serviceimpl serviceimpl = new Serviceimpl();
    public void run(){

        String transfer = serviceimpl.transfer("1", "123", "2", 10);
        System.out.println("线程"+transfer);


    }
}


main
service:com.mysql.jdbc.JDBC4Connection@76ccd017
main
service:com.mysql.jdbc.JDBC4Connection@76ccd017
main
service:com.mysql.jdbc.JDBC4Connection@76ccd017
service:com.mysql.jdbc.JDBC4Connection@76ccd017
service:com.mysql.jdbc.JDBC4Connection@76ccd017
主线程转账成功
Thread-1
service:com.mysql.jdbc.JDBC4Connection@49d05754
Thread-1
service:com.mysql.jdbc.JDBC4Connection@49d05754
Thread-1
service:com.mysql.jdbc.JDBC4Connection@49d05754
service:com.mysql.jdbc.JDBC4Connection@49d05754
service:com.mysql.jdbc.JDBC4Connection@49d05754
线程转账成功

Process finished with exit code 0

通过结果可以看出多个线程会获得不同的ThreadLocal对象