首页 > 代码库 > 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数据库的连接,并实现增加、删除、修改、查询