db.java

我实习时候的设计

package cn.pzb.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DB {
	private Connection conn = null;
	private PreparedStatement stmt = null;
	private ResultSet rs = null;
	private Statement st=null;
	public DB() {
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			conn = DriverManager
					.getConnection("jdbc:mysql://localhost:3306/xuanke?user=root&password=1234&useUnicode=true&characterEncoding=gbk");
		} catch (Exception e) {
		}
	}
	public ResultSet showScore(String teacherid)//通过teacherid显示当前教师的所有课程及对应学生的信息
	{
		try {
			stmt=conn.prepareStatement("SELECT sc.scid,c.courseid,c.coursename,s.studentid,s.name,sc.score FROM teacher t,course c,sc sc,student s WHERE t.teachername=c.teachername AND c.courseid=sc.courseid AND sc.studentid=s.studentid AND t.teacherid=?;");
			stmt.setString(1, teacherid);
			rs=stmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	public ResultSet showScore2(String scid)//通过scid显示当前教师的所有课程及对应学生的信息
	{
		try {
			stmt=conn.prepareStatement("SELECT sc.scid,c.courseid,c.coursename,s.studentid,s.name,sc.score FROM course c,sc sc,student s WHERE sc.courseid=c.courseid AND  sc.studentid=s.studentid AND sc.scid=?;");
			stmt.setString(1, scid);
			rs=stmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	public void updateScore(int newscore,String scid) //根据scid修改score
	{
		try {
			stmt=conn.prepareStatement("UPDATE sc SET score=? where scid=?;");
			stmt.setInt(1, newscore);
			stmt.setString(2, scid);
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//得到教师信息的方法
	public String getTeacherName(String teacherid) {
		String sql ="select teachername from teacher where teacherid =?";
		String teachername = null;
		try {
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, teacherid);
			rs = stmt.executeQuery();
			rs.next();
			teachername = rs.getString(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return teachername;
	}

}