JDBK连接数据库操作

package day29.demo;import java.io.BufferedWriter;import java.io.FileWriter;import java.io.PrintWriter;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import
 javax.sql.rowset.JdbcRowSet;/* * (1)查询女性,成绩80以上的学生数量 (2)将姓张的男同学的的成绩改为100 (3)查询成绩大于60的女性,显示姓名,性别,成绩 (4)分别统计所有男同学的平均分，所有女同学的平均分及总平均分 (5)按照分数从小到大的顺序打印分数大于总平均分的学员信息(id-name-sex-score),并将分数大于总平均分的学员信息(按照分数从小到大的顺序)(id-name-sex-score)写入到studentInfo.txt文件中(写入格式：id-name-sex-score)
 (6)定义查询所有学生的方法public List<Student> getAllStudent(){} (7)定义根据id查询学生的方法public Student getStudentById(String id){} (8)定义根据id删除学生的方法public int deleteStudentById(String id){}//注意只有数据库中有才能删除，没有无法删除 (9)定义添加学员的方法public int addStudent(){}//注意只有数据库中没有有才能添加，有无法添加 (10)定义根据id修改学员的信息public
 int updateStudentById(String id){}//注意只有数据库中有才能修改，没有无法修改 */public class student {public static void main(String[] args) throws Exception {//自制连接数据库JDBK工具类Connection con = JDBKUtils.getcon();String sql = "SELECT * FROM student WHERE score > (SELECT AVG(score)FROM
 student ) ORDER BY score DESC ";PreparedStatement pst = con.prepareStatement(sql);getAllStudent(con);std s = getStudentById("4");System.out.println(s.name);//System.out.println(deleteStudentById("2"));System.out.println(addStudent(new std("9", "dwafda", "dddd",
 10)));System.out.println(updateStudentById("1"));}//(10)定义根据id修改学员的信息public int updateStudentById(String id){}//注意只有数据库中有才能修改，没有无法修改public static int updateStudentById(String id) throws SQLException {Connection con = JDBKUtils.getcon();String sql = "update
 student set name=?,sex=?,score=? where id=? ";PreparedStatement pst = con.prepareStatement(sql);pst.setString(4, id);pst.setString(1, "aaa");pst.setString(2, "man");pst.setInt(3, 97);return pst.executeUpdate();}//(9)定义添加学员的方法public int addStudent(){}//注意只有数据库中没有有才能添加，有无法添加public
 static int addStudent(std s) throws SQLException{Connection con = JDBKUtils.getcon();//判断数据库中是否存在sString sql = "SELECT * FROM student where id=? and name=? and sex=? and score=?";PreparedStatement pst = con.prepareStatement(sql);pst.setString(1, s.id);pst.setString(2,
 s.name);pst.setString(3, s.sex);pst.setInt(4, s.score);ResultSet rs = pst.executeQuery();while(!rs.next()){String sql2 ="insert student(id,name,sex,score) values (?,?,?,?)";PreparedStatement pst2 = con.prepareStatement(sql2);pst2.setString(1, s.id);pst2.setString(2,
 s.name);pst2.setString(3, s.sex);pst2.setInt(4, s.score);return pst2.executeUpdate();}return 0;}// (8)定义根据id删除学生的方法public int deleteStudentById(String// id){}//注意只有数据库中有才能删除，没有无法删除public static int deleteStudentById(String id) throws SQLException {Connection
 con = JDBKUtils.getcon();String sql = "delete from student where id=? ";PreparedStatement pst = con.prepareStatement(sql);pst.setString(1, id);return pst.executeUpdate();}// (7)定义根据id查询学生的方法public Student getStudentById(String id){}public static std getStudentById(String
 id) throws SQLException {Connection con = JDBKUtils.getcon();String sql = "select * from student where id=? ";PreparedStatement pst = con.prepareStatement(sql);pst.setString(1, id);ResultSet rs = pst.executeQuery();while (rs.next()) {return new std(rs.getString("id"),
 rs.getString("name"),rs.getString("sex"), rs.getInt("score"));}return null;}// 6)定义查询所有学生的方法public List<Student> getAllStudent(){}public static List<std> getAllStudent(Connection con) throws SQLException {String sql = "select * from student ";PreparedStatement
 pst = con.prepareStatement(sql);ResultSet rs = pst.executeQuery();ArrayList<std> list = new ArrayList<std>();while (rs.next()) {list.add(new std(rs.getString("id"), rs.getString("name"), rs.getString("sex"), rs.getInt("score")));}System.out.println(list);return
 list;}}

///
JDBK工具类
package day29.demo;


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

public class JDBKUtils {
	private static Connection con;
	private JDBKUtils(){}
	static {
		try{
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/mybase";
			String user = "root";
			String password = "123";
			con = DriverManager.getConnection(url, user, password);
		}catch(Exception ex){
			throw new RuntimeException(ex);
		}
	}
	public static void colse(Connection con,Statement st,ResultSet rs){
		if(con != null){
		try {
			con.close();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}}
		if(st != null){
			try {
				st.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}}
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}}
	}
	public static void colse(Connection con,Statement st){
		if(con != null){
			try {
				con.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}}
		if(st != null){
			try {
				st.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}}
		
	}
	public static Connection getcon(){
		return con;
	}
}