数据库的基本操作

淡淡的烟草味﹌ 2022-04-10 14:11 288阅读 0赞

//数据库的基础操作
    package cn.sdut.dao;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    /*
    * 数据库操作的基础类
    */
    public class BaseDao {
    Connection con;
    Statement st;
    ResultSet rs;
    public void getConn() throws ClassNotFoundException, SQLException
    {
    Class.forName("com.mysql.jdbc.Driver");
    con=DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/数据库名",
    "root", null);
    }
    public void closeAll() throws SQLException
    {
    if(rs!=null)
    {
    rs.close();
    }
    if(st!=null)
    {
    st.close();
    }
    if(con!=null)
    {
    con.close();
    }
    }
    }
    //基本的增删改查
    package cn.sdut.dao;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    import cn.sdut.po.Account;
    /*
    * 对account表的操作
    */
    public class AccountDao extends BaseDao {
    //增加一条记录
    public int addAccount(Account account) throws ClassNotFoundException, SQLException
    {
    String sql="insert into account values(null,'"+account.getName()+"',"+account.getMoney()+")";
    getConn();
    st=con.createStatement();
    return st.executeUpdate(sql);
    }
    //修改记录
    public int updateAccount(String name,double value) throws ClassNotFoundException, SQLException
    {
    String sql="update account set money=money+"+value+" where name='"+name+"'";
    getConn();
    st=con.createStatement();
    return st.executeUpdate(sql);
    }
    //查询所有记录
    public List<Account> selectAll() throws ClassNotFoundException, SQLException
    {
    String sql="select * from account";
    getConn();
    st=con.createStatement();
    rs=st.executeQuery(sql);
    List<Account> list=new ArrayList<Account>();
    while(rs.next())
    {
    int id=rs.getInt(1);
    String name=rs.getString("name");
    double money=rs.getDouble("money");
    Account account=new Account(id,name,money);
    list.add(account);
    }
    return list;
    }
    //根据name查询一条记录
    public Account selectByName(String name) throws ClassNotFoundException, SQLException
    {
    String sql="select * from account where name='"+name+"'";
    getConn();
    st=con.createStatement();
    rs=st.executeQuery(sql);
    Account account=null;
    if(rs.next())
    {
    int id=rs.getInt(1);
    String name1=rs.getString("name");
    double money=rs.getDouble("money");
    account=new Account(id,name1,money);
    }
    return account;
    }
    public int turnMoney(String nameOut,String nameIn,double value) throws ClassNotFoundException, SQLException
    {
    getConn();
    String sql1="update account set money=money-"+value+" where name='"+nameOut+"'";
    String sql2="update account set money=money+"+value+" where name='"+nameIn+"'";
    con.setAutoCommit(false);
    st=con.createStatement();
    int r1=st.executeUpdate(sql1);
    //int a=10/0;
    int r2=st.executeUpdate(sql2);
    if(r1*r2>0)
    {
    con.commit();
    }
    else
    {
    con.rollback();
    }
    return r1*r2;
    }
    }