查询mysql当前连接并删除_JAVA与Mysql数据库的连接，并实现增加、删除、修改、查询...

UserAccount类：

public class UserAccount {

private String account;　　//账户卡号

private String uname;　　 //名字

private String upwd;　　　//密码

private double balance;　 //余额

//构造方法

public UserAccount(String account, String uname, String upwd, double balance) {

//super();

this.account = account;

this.uname = uname;

this.upwd = upwd;

this.balance = balance;

}

public String getAccount() {

return account;

}

public void setAccount(String account) {

this.account = account;

}

public String getUname() {

return uname;

}

public void setUname(String uname) {

this.uname = uname;

}

public String getUpwd() {

return upwd;

}

public void setUpwd(String upwd) {

this.upwd = upwd;

}

public double getBalance() {

return balance;

}

public void setBalance(double balance) {

this.balance = balance;

}

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.List;

import java.util.Scanner;

public class MysqlConnect {

private static final String DRIVER="com.mysql.jdbc.Driver";

private static final String URL="jdbc:mysql://localhost:3306/bank";

private static final String USER="root";

private static final String PWD="root";

static Connection conn=null;　　//链接对象

static Statement st=null;　　　　//操作

static PreparedStatement ps=null;

static ResultSet rs=null;　　　　//结果集

//连接数据库    封装

public static void getConnection(){

try {

Class.forName(DRIVER);

conn =DriverManager.getConnection(URL, USER, PWD);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

//关闭资源

public static void closeAll(){

try {

if(rs!=null)

rs.close();

if(st!=null)

st.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

//查询全部数据库中的数据

public static ListgetAllUsers(){

Listlist=new ArrayList();

try {

//获得连接

getConnection();

//构建SQL语句

String sql ="select account,uname,upassword,balance from useraccount ";

//创建Statement对象

st = conn.createStatement();

//执行SQL语句

rs=st.executeQuery(sql);

//处理结果集

while(rs.next()){

UserAccount user=new UserAccount(rs.getString(1), rs.getString(2), rs.getString(3), rs.getDouble(4));

list.add(user);

}

} catch (SQLException e) {

e.printStackTrace();

}finally{

closeAll();

}

return list;

}

//删除数据库中的数据(通过账号直接删除)

public static int deleteUserAccount(String user){

try {

//获得连接

getConnection();

//构建SQL语句

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

//构建Stated对象

ps=conn.prepareStatement(sql);

ps.setString(1, user);

return ps.executeUpdate();

}catch (SQLException e) {

e.printStackTrace();

}finally{

closeAll();

}

return -1;

}

//增加数据库中的数据

public static int addUserAccount(UserAccount user){

try {

//获得连接

getConnection();

//构建SQL语句

String sql="insert into useraccount(account,uname,upassword,balance) values(?,?,?,?)";

//构建Stated对象

ps=conn.prepareStatement(sql);

ps.setString(1, user.getAccount());

ps.setString(2, user.getUname());

ps.setString(3, user.getUpwd());

ps.setDouble(4, user.getBalance());

return ps.executeUpdate();

}catch (SQLException e) {

e.printStackTrace();

}finally{

closeAll();

}

return -1;

}

//修改数据库中的数据

public static int updateUserByPstmt(UserAccount user){

try {

//获得连接

getConnection();

//构建SQL语句

String sql="update useraccount set uname=?,upassword=? where account=?";

//构建Stated对象

ps=conn.prepareStatement(sql);

ps.setString(1, user.getUname());

ps.setString(2, user.getUpwd());

ps.setString(3, user.getAccount());

return ps.executeUpdate();

}catch (SQLException e) {

e.printStackTrace();

}finally{

closeAll();

}

return -1;

}

//通过账号查Mysql数据库单条数据

public static UserAccount getUserByAccount(String account){

UserAccount user=null;

try{

getConnection();

String sql="select account,uname,upassword,balance from useraccount where account=‘"+account+"‘";

st=conn.createStatement();

rs=st.executeQuery(sql);

if(rs.next()){

user=new UserAccount(rs.getString(1), rs.getString(2), rs.getString(3), rs.getDouble(4));

}

}catch (Exception e) {

}finally{

closeAll();

}

return user;

}