首页 > 代码库 > JAVA与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 List<UserAccount>getAllUsers(){
  List<UserAccount>list=new ArrayList<UserAccount>();
  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;
}

 

JAVA与Mysql数据库的连接,并实现增加、删除、修改、查询