数据库的基本操作

//数据库的基础操作
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;
}
}