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;}}