Service操作

原创 于 2020-04-01 22:27:04 发布 · 227 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 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对象

S9264L
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值