java数据库操作

DataSource.class文件

package day1216zuoye.common;

import java.sql.*;

public class DataSource {
    protected String driver="com.mysql.jdbc.Driver";
    protected String url="jdbc:mysql://localhost:3306/student";
    private Connection con=null;
    private PreparedStatement ps=null;
    protected ResultSet res=null;
    public Connection getConnection() throws Exception{
        Class.forName(driver);
        return DriverManager.getConnection(url,"root","root");
    }
    public void execute(String sql) throws Exception{
        con=getConnection();
        ps=con.prepareStatement(sql);
        ps.execute();
    }
    public void execute(String sql,Object[] obj) throws Exception{
        con=getConnection();
        ps=con.prepareStatement(sql);
        int i=1;
        for (Object o:obj){
            ps.setObject(i,o);
            i++;
        }
        ps.execute();
    }
    public ResultSet query(String sql) throws Exception{
        con=getConnection();
        ps=con.prepareStatement(sql);
        return ps.executeQuery();
    }
    public ResultSet query(String sql,Object []obj) throws Exception{
        con=getConnection();
        ps=con.prepareStatement(sql);
        int i=1;
        for (Object o:obj){
            ps.setObject(i,o);
            i++;
        }
        return ps.executeQuery();
    }
    public void close(){
        try {
            if(res!=null){
                res.close();
            }
            if(ps!=null){
                ps.close();
            }
            if(con!=null){
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

操作文件

package day1216zuoye.dao;


import day1216zuoye.common.DataSource;
import day1216zuoye.entity.StudentEntity;

import java.util.ArrayList;
import java.util.List;

public class StudentDao extends DataSource {
    public void studentAdd(StudentEntity student){
        String sql="insert into t_student2(sid, sname, sgender, sbirthday, sadd, stel) values (null,?,?,?,?,?)";
        Object[]obj={student.getSname(),student.getSgender(),student.getSbirthday(),student.getSadd(),student.getStel()};
        try {
            execute(sql,obj);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close();
        }
    }
    public List<StudentEntity> studentSelect(){
        String sql="select * from t_student2";
        List<StudentEntity> students=new ArrayList<>();
        try {
            res=query(sql);
            while (res.next()){
                StudentEntity student=new StudentEntity();
                student.setSid(res.getInt("Sid"));
                student.setSname(res.getString("Sname"));
                student.setSgender(res.getString("Sgender"));
                student.setSbirthday(res.getDate("Sbirthday"));
                student.setSadd(res.getString("Sadd"));
                student.setStel(res.getString("Stel"));
                students.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return students;
    }public List<StudentEntity> studentSelect(Integer i){
        String sql="select * from t_student2 where sid="+i;
        List<StudentEntity> students=new ArrayList<>();
        try {
            res=query(sql);
            while (res.next()){
                StudentEntity student=new StudentEntity();
                student.setSid(res.getInt("Sid"));
                student.setSname(res.getString("Sname"));
                student.setSgender(res.getString("Sgender"));
                student.setSbirthday(res.getDate("Sbirthday"));
                student.setSadd(res.getString("Sadd"));
                student.setStel(res.getString("Stel"));
                students.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return students;
    }
    public void studentDelete(Integer i){
        String sql="delete from t_student2 where sid="+i;
        try {
            execute(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close();
        }
    }
    public void studentalter(StudentEntity s,Integer i){
        String sql="update t_student2 set sname=?,sgender=?,sbirthday=?,sadd=?,stel=? where sid="+i;
        Object[] obj={s.getSname(),s.getSgender(),s.getSbirthday(),s.getSadd(),s.getStel()};
        try {
            execute(sql,obj);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close();
        }

    }
}