【JAVA高階】——吃透JDBC中的事務及事務的封裝

package com.cxyzxc.examples08;

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 AccountDaoImpl {

// 新增：插入一個 Account 物件到資料庫中

public int insert(Account account,Connection connection) {

PreparedStatement preparedStatement = null;

String sql = "insert into account values(?,?,?,?)";

try {

preparedStatement = connection.prepareStatement(sql);

// 繫結引數

preparedStatement.setString(1, account.getCardNo());

preparedStatement.setString(2, account.getPassword());

preparedStatement.setString(3, account.getName());

preparedStatement.setDouble(4, account.getBalance());

// 執行 SQL

int result = preparedStatement.executeUpdate();

return result;

} catch (SQLException e) {

e.printStackTrace();

}

return 0;

}

// 刪除：根據卡號，刪除賬號

public int delete(String cardNo,Connection connection) {

PreparedStatement preparedStatement = null;

String sql = "delete from account where cardNo = ?;";

try {

preparedStatement = connection.prepareStatement(sql);

// 繫結引數

preparedStatement.setString(1, cardNo);

// 執行 SQL

int result = preparedStatement.executeUpdate();

return result;

} catch (SQLException e) {

e.printStackTrace();

}

return 0;

}

// 修改

public int update(Account account,Connection connection) {

PreparedStatement preparedStatement = null;

String sql = "update account set password = ?,name = ?,balance = ? where cardNo=?;";

try {

preparedStatement = connection.prepareStatement(sql);

// 繫結引數

preparedStatement.setString(1, account.getPassword());

preparedStatement.setString(2, account.getName());

preparedStatement.setDouble(3, account.getBalance());

preparedStatement.setString(4, account.getCardNo());

// 執行 SQL

int result = preparedStatement.executeUpdate();

return result;

} catch (SQLException e) {

e.printStackTrace();

}

return 0;

}

// 查詢單個

public Account select(String cardNo,Connection connection) {

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

Account account = null;

String sql = "select * from account where cardNo = ?";

try {

preparedStatement = connection.prepareStatement(sql);

// 繫結引數

preparedStatement.setString(1, cardNo);

// 執行 SQL

resultSet = 跟單網gendan5.compreparedStatement.executeQuery();

if (resultSet.next()) {

String cardNumber = resultSet.getString("cardNo");

String password = resultSet.getString("password");

String name = resultSet.getString("name");

double balance = resultSet.getDouble("balance");

account = new Account(cardNumber, password, name, balance);

}

return account;

} catch (SQLException e) {

e.printStackTrace();

}

return null;

}

// 查詢所有

public List<Account> selectAll(Connection connection) {

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

Account account = null;

List<Account> accountList = new ArrayList<Account>();

String sql = "select * from account;";

try {

preparedStatement = connection.prepareStatement(sql);

// 執行 SQL

resultSet = preparedStatement.executeQuery();

while (resultSet.next()) {

String cardNumber = resultSet.getString("cardNo");

String password = resultSet.getString("password");

String name = resultSet.getString("name");

double balance = resultSet.getDouble("balance");

account = new Account(cardNumber, password, name, balance);

accountList.add(account);

}

return accountList;

} catch (SQLException e) {

e.printStackTrace();

}

return null;

}

}