[java]代碼庫package com.ww.service;
import java.lang.reflect.Array;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mysql.jdbc.PreparedStatement;
import com.ww.db.DBHelper;
import com.ww.entity.Score;
import com.ww.entity.Student;
public class SysService {
DBHelper dbHelper=new DBHelper();
//查詢所有學生的編號，姓名和分數，并返回
public ArrayList getAllInfo(){
String sql="select * from stu s,sco sc where s.stu_id=sc.stu_id";
Connection connection=dbHelper.getConnection();
ArrayList scores=new ArrayList();
try {
PreparedStatement psd=(PreparedStatement)connection.prepareStatement(sql);
ResultSet resultSet=psd.executeQuery();
while (resultSet.next()) {
Student student=new Student();
Score score=new Score();
student.setId(resultSet.getInt("stu_id"));
student.setName(resultSet.getString("stu_name"));
score.setId(resultSet.getInt("sco_id"));
score.setScos(resultSet.getInt("emg_sco"));
score.setStudent(student);
scores.add(score);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return scores;
}
//添加
public boolean addInfo(String name,int a){
String sql1="insert into stu VALUES(null,?)";
String sql2="insert into sco VALUES(null,(select MAX(stu_id) from stu),?)";
Connection connection=dbHelper.getConnection();
try {
//執行向主表添加數據
PreparedStatement psd1=(PreparedStatement)connection.prepareStatement(sql1);
//執行向從表中添加數據
PreparedStatement psd2=(PreparedStatement)connection.prepareStatement(sql2);
psd1.setString(1, name);
int b=psd1.executeUpdate();
if (b>0) {
//向主表中完成添加
psd2.setInt(1, a);
int s=psd2.executeUpdate();
if (s>0) {
return true;
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
//修改
public boolean update(int id,String name,int sc) {
String sql="update stu s,sco sc set s.stu_name=?,sc.emg_sco=? where s.stu_id=sc.stu_id and s.stu_id=?";
Connection connection=dbHelper.getConnection();
try {
PreparedStatement psd=(PreparedStatement)connection.prepareStatement(sql);
psd.setString(1, name);
psd.setInt(2, sc);
psd.setInt(3, id);
if(psd.executeUpdate()>=2){
return true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
//刪除
public boolean deleteInfo(int id){
String sql="delete from sco where stu_id=?";
Connection connection=dbHelper.getConnection();
try {
PreparedStatement psd=(PreparedStatement)connection.prepareStatement(sql);
psd.setInt(1, id);
if(psd.executeUpdate()>0){
return true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
}
[源代碼打包下載]
多表增刪改查.zip(50積分)[15 次下載]
)