表的基础操作（No.14)

packageemployee;

importjava.sql.Connection;

importjava.sql.DriverManager;

importjava.sql.PreparedStatement;

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.util.ArrayList;

importjava.util.List;

importemployee.Work;

publicclassEmployeeimplementsWork{

privatestaticConnectionconn;

privatePreparedStatementpstmt;

privateResultSetrs;

static{

try{

Class.forName("com.mysql.jdbc.Driver");

conn=DriverManager

.getConnection("jdbc:mysql://localhost:3306/work?user=root&password=like&useUnicode=true&characterEncoding=UTF-8");

}catch(ClassNotFoundExceptione){

e.printStackTrace();

}catch(SQLExceptione){

e.printStackTrace();

}

}

@Override

publicbooleanadd(Stringname){

Booleanflag=false;

Stringsql="insertintoplace(id,pName)values(null,?)";

try{

pstmt=conn.prepareStatement(sql);

intindex=1;

pstmt.setString(index++,name);

inti=pstmt.executeUpdate();

if(i>0){

flag=true;

}

}catch(SQLExceptione){

e.printStackTrace();

}

if(pstmt!=null){

try{

pstmt.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

if(conn!=null){

try{

conn.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

returnflag;

}

@Override

publicListcheck(Stringname){

Listallentities=newArrayList();

Stringsql="select*fromemployeeasejoinplaceaspone.eId=p.idwherepName=?";

try{

pstmt=conn.prepareStatement(sql);

intindex=1;

pstmt.setString(index++,name);

rs=pstmt.executeQuery();

if(rs.next()){

System.out

.println(rs.getInt("id")+"\t"+rs.getString("eName")

+"\t"+rs.getString("eSex")+"\t"

+rs.getInt("eAge")+"\t"

+rs.getLong("eMoney")+"\t"

+rs.getLong("eMoneyout"));

}

}catch(SQLExceptione){

e.printStackTrace();

}

if(pstmt!=null){

try{

pstmt.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

if(conn!=null){

try{

conn.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

returnallentities;

}

@Override

publicListavg(Stringname){

Listallentities=newArrayList();

Stringsql="selectavg(eMoney+eMoneyout)fromemployeeasejoinplaceaspone.eId=p.idwherepName=?";

try{

pstmt=conn.prepareStatement(sql);

intindex=1;

pstmt.setString(index++,name);

rs=pstmt.executeQuery();

if(rs.next()){

System.out.println(rs.getLong("avg(eMoney+eMoneyout)"));

}

}catch(SQLExceptione){

e.printStackTrace();

}

if(pstmt!=null){

try{

pstmt.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

if(conn!=null){

try{

conn.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

returnallentities;

}

@Override

publicListcount(Stringname){

Listallentities=newArrayList();

Stringsql="selectavg(eMoney+eMoneyout)*count(e.id)fromemployeeasejoinplaceaspone.eId=p.idwherepName=?";

try{

pstmt=conn.prepareStatement(sql);

intindex=1;

pstmt.setString(index++,name);

rs=pstmt.executeQuery();

if(rs.next()){

System.out.println(rs

.getLong("avg(eMoney+eMoneyout)*count(e.id)"));

}

}catch(SQLExceptione){

e.printStackTrace();

}

if(pstmt!=null){

try{

pstmt.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

if(conn!=null){

try{

conn.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

returnallentities;

}

@Override

publicListcheck1(Stringname){

Listallentities=newArrayList();

Stringsql="select*fromemployeewhereeName=?";

try{

pstmt=conn.prepareStatement(sql);

intindex=1;

pstmt.setString(index++,name);

rs=pstmt.executeQuery();

if(rs.next()){

System.out

.println(rs.getInt("id")+"\t"+rs.getString("eName")

+"\t"+rs.getString("eSex")+"\t"

+rs.getInt("eAge")+"\t"

+rs.getLong("eMoney")+"\t"

+rs.getLong("eMoneyout"));

}

}catch(SQLExceptione){

e.printStackTrace();

}

if(pstmt!=null){

try{

pstmt.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

if(conn!=null){

try{

conn.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

returnallentities;

}

@Override

publicbooleanchange(inteMoney,Stringname){

booleanflag=false;

Stringsql="updateemployeeseteMoney=?whereeName=?";

try{

pstmt=conn.prepareStatement(sql);

intindex=1;

pstmt.setLong(index++,eMoney);

pstmt.setString(index++,name);

inti=pstmt.executeUpdate();

if(i>0){

flag=true;

}

}catch(SQLExceptione){

e.printStackTrace();

}

if(pstmt!=null){

try{

pstmt.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

if(conn!=null){

try{

conn.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

returnflag;

}

@Override

publicbooleandelete(intid){

booleanflag=false;

Stringsql="deletefromemployeewhereid=?";

try{

pstmt=conn.prepareStatement(sql);

intindex=1;

pstmt.setInt(index++,id);

inti=pstmt.executeUpdate();

if(i>0){

flag=true;

}

}catch(SQLExceptione){

e.printStackTrace();

}

if(pstmt!=null){

try{

pstmt.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

if(conn!=null){

try{

conn.close();

}catch(SQLExceptione){

//TODOAuto-generatedcatchblock

e.printStackTrace();

}

}

returnflag;

}

publicstaticvoidmain(String[]args){

Workst=newEmployee();

//st.add("美女部");

//st.check("学习部");

//st.avg("体育部");

//st.count("学习部");

//st.check1("李四");

//st.change(40000,"李四");

//st.delete(2);

}

}

///Work.java代码

packageemployee;

importjava.util.List;

publicinterfaceWork{

Listcheck(Stringname);

Listavg(Stringname);

Listcount(Stringname);

Listcheck1(Stringname);

booleanchange(inteMoney,Stringname);

booleandelete(intid);

booleanadd(Stringname);

}