java 同时操作数据库经常锁表的解决办法 java代码实现数据库锁

转载
幸福的地图 2024-03-04 15:41:09
文章标签 java连接mysql锁定用户 sql System java 文章分类 Java 后端开发
下面是代码：
package Main;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class DataBaseOperation {
//数据库连接
private static Connection connect() {
// SQLite connection string
String url = "jdbc:sqlite:D:/software/sqlite/BankManageSystem.db";
Connection conn = null;
try {
conn = DriverManager.getConnection(url);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}
//插入元组到管理员表
public boolean insertAdmin(String id,String account,String adminkey,int rank,String leader) {
String sql = "INSERT INTO admin(id, account,adminkey,rank,leader) VALUES(?,?,?,?,?)";
try {
Connection conn = connect();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
pstmt.setString(2, account);
pstmt.setString(3, adminkey);
pstmt.setInt(4, rank);
pstmt.setString(5, leader);
pstmt.executeUpdate();
sql = "update admin set adminlog = 'D:\\software\\sqlite\\Adminlog' || id || '.txt'";
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
return true;
} catch (SQLException e) {
System.out.println(e.getMessage());
return false;
}
}
//管理员登录 检查 密码校对
public static boolean checkAdminKey(String account,String key) {
String sql = "SELECT * FROM admin WHERE account == " + account + " AND adminkey = " + key;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
return true;
else return false;
} catch (SQLException e) {
System.out.println(e.getMessage());
return false;
}
}
//用户登录 检查 密码校对
public boolean checkUserKey(String account,String key) {
String sql = "SELECT * FROM userAccount WHERE account == " + account + " AND acckey = " + key;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
return true;
else return false;
} catch (SQLException e) {
System.out.println(e.getMessage());
return false;
}
}
//根据管理员account 或者ID 获取该普通管理员的上级超级管理员
//返回字符串“0”则表明这个管理员就是超级管理员
public String getLeader(String str) {
String result = null;
String sql = "SELECT * FROM admin WHERE account == " + str + " or id = " + str;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// loop through the result set
result = rs.getString("leader");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return result;
}
//根据管理员account返回管理员信息字符串
public String getAdminString(String account) {
String result = "";
String sql = "SELECT * FROM ADMIN WHERE ACCOUNT = " + account;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
result += rs.getString("id") + " " + rs.getString("account") +
" " + rs.getString("adminkey") +" " + rs.getString("rank") +" " +
rs.getString("leader") +" "+ rs.getString("adminLog");
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return result;
}
//插入元组到申请表
public void insertApplyQueue(String proposer,String SuperAdmin,String account,String acckey,String UserName) {
String sql = "INSERT INTO ApplyQueue(proposer, SuperAdmin,account,acckey,UserName) VALUES(?,?,?,?,?)";
try {
Connection conn = connect();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, proposer);
pstmt.setString(2, SuperAdmin);
pstmt.setString(3, account);
pstmt.setString(4, acckey);
pstmt.setString(5, UserName);
pstmt.executeUpdate();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
//根据管理员(超级或者普通)id查找他名下的开户申请条目，返回一个字符串数组
public ArrayList findApplication(String id) {
ArrayList strArray = new ArrayList ();
String sql = "SELECT * FROM APPLYQUEUE WHERE proposer = " + id + " or superAdmin = " + id;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
strArray.add(rs.getString("proposer") + " " + rs.getString("superAdmin") + " "
+ rs.getString("account") + " " + rs.getString("acckey") + " " + rs.getString("userName"));
}
}
catch (SQLException e) {
System.out.println(e.getMessage());
}
return strArray;
}
//超级管理员处理开户申请条目,根据开户号码和申请人
public boolean dealApplication(String userAccount,boolean choose) {
//调用者不是超级管理员
// if(this.rank == 0) return false;
//如果不同意那就直接把该条目从申请表applyqueue中删除
if(!choose) {
deleteApplyQueue(userAccount);
}
//如果同意那就把该条目的用户信息写入用户账号表，并将其从申请表applyqueue中删除
else {
String sql = "SELECT * FROM ApplyQueue WHERE account = " + userAccount;
//String userAccount;
String acckey = null;
String userName = null;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()) {
acckey = rs.getString("acckey");
System.out.println(acckey);
userName = rs.getString("UserName");
System.out.println(userName);
//关闭连接，如果不关闭导致读写冲突，sqlite的封锁粒度是库级，
//只要对同一个数据库同时(用一个连接)进行读写，写写等操作都会冲突
conn.close();
//向用户账户表中插入该记录，并且把余额设置为0，年限为1，
insertUserAccount(userAccount,acckey,0,1,userName);
//删队列中的已处理的条目
deleteApplyQueue(userAccount);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("异常");
}
}
return true;
}
//删除操作 对applyqueue表,根据申请人和申请开通的账户号
private boolean deleteApplyQueue(String account) {
String sql = "DELETE FROM ApplyQueue WHERE account =" + account;
try {
Connection conn = connect();
Statement pstmt = conn.createStatement();
pstmt.execute(sql);
return true;
} catch (SQLException e) {
System.out.println(e.getMessage());
return false;
}
}
//插入元组到useraccount表
public boolean insertUserAccount(String account,String acckey,double balance,int term,String UserName) {
String sql = "INSERT INTO UserAccount(account, acckey,balance,term,UserName,logtxt) VALUES(?,?,?,?,?,?)";
String logtxt = "D:\\software\\sqlite\\Accountlog" + account + ".txt";
//string sql = a + b;
try {
Connection conn = connect();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, account);
pstmt.setString(2, acckey);
pstmt.setDouble(3, balance);
pstmt.setInt(4, term);
pstmt.setString(5, UserName);
pstmt.setString(6, logtxt);
pstmt.executeUpdate();
return true;
} catch (SQLException e) {
System.out.println(e.getMessage());
return false;
}
}
//根据账号修改余额，用于转账
public boolean alterBalance(String userAccount,double increaseMent) {
String sql = "UPDATE USERACCOUNT SET BALANCE = BALANCE + " + increaseMent +" WHERE ACCOUNT = " + userAccount;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
stmt.execute(sql);
return true;
}
catch (SQLException e) {
System.out.println(e.getMessage());
return false;
}
}
//*
//根据用户账号修改用户密码
public boolean alterPassword(String userAccount,String newKey) {
String sql = "UPDATE USERACCOUNT SET acckey = " + newKey +" WHERE ACCOUNT = " + userAccount;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
stmt.execute(sql);
return true;
}
catch (SQLException e) {
System.out.println(e.getMessage());
return false;
}
}
//*
//获取用户所有账户
//注意，查询条件等于某个非数字的字符串时，要给这个字符串预加 ''
public String getAllAccounts(String userName) {
userName = "'"+userName+"'";
String result = "";
String sql = "SELECT * FROM UserAccount WHERE userName = " + userName;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
result += rs.getString("account") + " ";
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return result;
}
//*
//获取管理员表最后一个账户的id和account，用于按顺序自动生成账号, 返回一个长度为2的数组，分别代表id和account
//这里 dbo.getTheLastManager().get(0)获取id get(1)获取账号
public ArrayList getTheLastManager() {
ArrayList result = new ArrayList ();
String sql = "select *\r\n" +
"from admin\r\n" +
"order by id desc limit 0,1";
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
result.add(rs.getString("id"));
result.add(rs.getString("account"));
}
}
catch (SQLException e) {
System.out.println(e.getMessage());
}
return result;
}
//*
//获取账户表最后一个账户的account，用于按顺序自动生成账号
//可以用 Integer.parseInt(dbo.getTheLastAccount())+1 生成需要生成的账号，需要进一步toString
public String getTheLastAccount() {
String result = "";
String sql = "select account\r\n" +
"from userAccount\r\n" +
"order by account desc limit 0,1";
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
result = rs.getString("account");
}
}
catch (SQLException e) {
System.out.println(e.getMessage());
}
return result;
}
//获取用户信息，返回字符串
public String getUserInfo(String userAccount) {
String result="";
String sql = "SELECT * FROM USERACCOUNT WHERE ACCOUNT = " + userAccount;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
result += rs.getString("account") + " " + rs.getString("acckey") +
" " + rs.getString("balance") +" " + rs.getString("term") +" " +
rs.getString("userName") +" "+ rs.getString("logtxt");
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return result;
}
//获取所有用户信息，返回字符串数组
public ArrayList getAllUserAccount() {
ArrayList strArray = new ArrayList ();
String sql = "SELECT * FROM UserAccount" ;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
strArray.add(rs.getString("account") + " " + rs.getString("acckey") + " "
+ rs.getString("balance") + " " + rs.getString("term") + " " + rs.getString("userName")
+ " " + rs.getString("logtxt"));
}
}
catch (SQLException e) {
System.out.println(e.getMessage());
}
return strArray;
}
//根据用户账号返回其日志文件路径
public String getUserLogURL(String account ) {
String URL = null;
String sql = "SELECT * FROM UserAccount WHERE account == " + account ;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// loop through the result set
URL = rs.getString("logtxt");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return URL;
}
// 根据管理员账号或者ID返回其日志文件路径
public String getAdminLogURL(String str) {
String URL = null;
String sql = "SELECT * FROM ADMIN WHERE account == " + str + " or id = " + str;
try {
Connection conn = connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// loop through the result set
URL = rs.getString("adminLog");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return URL;
}
public static void main(String[] args) {
// TODO Auto-generated method stub
DataBaseOperation dbo = new DataBaseOperation();
dbo.insertAdmin("1003", "13667", "12345", 0, "1003");
//System.out.println(dbo.getAdminString("13611", "12345"));
//dbo.insertApplyQueue("1001", "1003", "30004", "15856", "jack");
//dbo.insertUserAccount("30002", "15896", 1000.5, 1, "John");
//dbo.deleteApplyQueue("1001", "1003", "30004");
/*if(dbo.checkAdminKey("13611", "12345"))
System.out.println("登陆成功！");
else
System.out.println("登陆失败！");*/
//System.out.println(dbo.getLeader("1001"));
//Admin ad = new Admin();
//ad = dbo.getAdminObject("13611","12345");
//dbo.dealApplication("1001", "30004",true);
ArrayList arrStr = new ArrayList();
//arrStr = dbo.getAllUserAccount();
//System.out.println(dbo.getAdminString("13611", "12345"));
//arrStr = dbo.getAllUserAccount();
//System.out.println(dbo.getUserLogURL("30003"));
/*String URL = "D:\\software\\sqlite\\Accountlog30003.txt";
FileOperation.createFile(URL);
System.out.println(FileOperation.readFileToString(dbo.getUserLogURL("30003")));
FileOperation.writeArrayListToFile(arrStr, URL);*/
//dbo.alterPassword("30002", "99997");
//System.out.println(dbo.getTheLastManager().get(0) + " " + dbo.getTheLastManager().get(1));
System.out.println(dbo.getAllAccounts("John"));
//System.out.println(dbo.getAdminString("13611"));
System.out.println("OK");
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。