JAVA连接数据库执行SQL语句的技术2--JDBC（Java DatabasesConnectivity）

1.首先在sqlserver中建立一张teacher表：

/****************table1 老师基本信息表*******************/
create table teacher(
   teaNum               char(8)                        not null,
   teaName              varchar(20)                    not null,
   teaTitle             varchar(20)                    not null,
   teaTypeId            varchar(20)                    not null,
   -- 1上课老师 2 导员
   teaBtd               datetime                       not null,
   pwd                  varchar(20)                    not null,
   static               int                            not null,
   roleId               int                            not null,
   key1                 varchar(20)                    null,
   key2                 int                            null
);
alter table teacher  add constraint PK_teaNum  primary key (teaNum)
alter table teacher 
	add constraint DF_roleId default ('1') for roleId
go

2.在idea开发工具中建一个项目（project），在项目中加一个模块，在模块中加三个包（packge）,分别叫dao，db和test。dao中写对SQLserver中的teacher表的操作具体实现(save, update, delete, findAll, findById等方法)，db中写连接数据库驱动程序的代码，test中写具体的测试。（其中部分代码引用了下面没有给的其他块中的代码，大家一看程序应该就知道是什么意思了，如果有不清楚的友友可以评论或私信我）

//TeacherDAO参考代码
public class TeacherDAO {
    public boolean save(Teacher t){
        String sql = "insert into teacher " +
                " (teaNum, teaName, teaTitle, teaTypeId, teaBtd, " +
                " pwd, static, roleId, key1, key2) " +
                " values " +
                " ('" + t.getTeaNum() + "', '" + t.getTeaName() + "'," +
                " '" + t.getTeaTitle() + "', " + t.getTeaTypeId() + "," +
                " '" + DateConvert.convertToStr(t.getTeaBtd()) + "',
                '" + t.getPwd() + "'," +
                " " + t.getState() + ", " + t.getRoleId() + "," +
                " '" +t.getKey1()+ "', " + t.getKey2() + ")";
        DBManager dbManager = new DBManager();
        /*int rows = dbManager.update(sql);
        if (rows == 1){
            return true;
        } else{
            return false;
        }*/
        return (dbManager.update(sql) == 1);
    }
    public boolean update(Teacher t){
        String sql = "update teacher " +
                " set teaNum = '" + t.getTeaNum() + "', teaName = '" + t.getTeaName() + 
                   "', " +
                " teaTitle = '" + t.getTeaTitle() + "', teaTypeId = " + t.getTeaTypeId() 
                 + "," +
                " teaBtd = '" + DateConvert.convertToStr(t.getTeaBtd()) + "', pwd = '" + 
                 t.getPwd() + "', " +
                " static = " + t.getState() + ", roleId = " + t.getRoleId() + "," +
                " key1 = '" + t.getKey1() + "', key2 = " + t.getKey2() + " " +
                " where teaNum = '" + t.getTeaNum() + "'";
        DBManager dbManager = new DBManager();
        int rows = dbManager.update(sql);
        return (rows == 1);
    }
    public boolean delete(String id) {
        String sql = "delete " +
                " teacher where teaNum = '" + id + "'";
        DBManager dbManager = new DBManager();
        int rows = dbManager.update(sql);
        return rows == 1;
    }
    public List<Teacher> findAll() {
        String sql = "select " +
                " teaNum, teaName, teaTitle, teaTypeId, teaBtd," +
                " pwd, static, roleId, key1, key2 " +
                " from " +
                " teacher ";
        List<Teacher> ts = new ArrayList<>();
        DBManager dbManager = new DBManager();
        ResultSet rs = dbManager.query(sql);
        try {
            while (rs.next()) {
                String teaNum = rs.getString(1);
                String teaName = rs.getString(2);
                String teaTitle = rs.getString(3);//假如给这个临时表取了一个别名，不能使用 
                                               以前表的列名必须使用别名或者列对应的数字下标
                int teaTypeId = rs.getInt(4);
                Date teaBtd = rs.getDate(5);
                String pwd = rs.getString(6);
                int state = rs.getInt(7);
                int roleId = rs.getInt(8);
                String key1 = rs.getString(9);
                int key2 = rs.getInt(10);
                Teacher t = new Teacher(teaNum, teaName, teaTitle, teaTypeId, teaBtd, 
                           pwd, state, roleId, key1, key2);
                ts.add(t);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbManager.close();
        }
        return ts;
    }

    /**
     * 根据编号找老师信息
     * @param id 要找的老师编号
     * @return 返回老师信息
     */
    public Teacher findById(String id) {
        String sql = "select " +
                            " teaNum, teaName, teaTitle, teaTypeId, teaBtd," +
                            " pwd, static, roleId, key1, key2 " +
                     "from " +
                            "teacher where teaNum = '" + id + "'";
        Teacher t = null;
        DBManager dbManager = new DBManager();
        ResultSet rs = dbManager.query(sql);
        try {
            if(rs.next()) {
                String teaNum = rs.getString(1);
                String teaName = rs.getString(2);
                String teaTitle = rs.getString(3);//假如给这个临时表取了一个别名，不能使用                                                                                                                                                                                                                                                                        
                                                以前表的列名必须使用别名或者列对应的数字下标
                int teaTypeId = rs.getInt(4);
                Date teaBtd = rs.getDate(5);
                String pwd = rs.getString(6);
                int state = rs.getInt(7);
                int roleId = rs.getInt(8);
                String key1 = rs.getString(9);
                int key2 = rs.getInt(10);
                //构造方法传参赋值
                t = new Teacher(teaNum, teaName, teaTitle, teaTypeId, teaBtd, pwd, state, 
                        roleId, key1, key2);
                /*set方法赋值
                t.setTeaName(teaNum);
                t.setTeaName(teaName);*/
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbManager.close();
        }
        return t;
    }
}
//db包的参考代码
public class DBManager {
    private Connection con = null;
    private Statement sta = null;
    private ResultSet rs = null;

    /**
     * 用来执行insert update delete sql语句
     * @param sql 要执行的sql语句
     * @return  sql语句影响的行数
     */
    public int update(String sql) {
        System.out.println(sql);
        int rows = 0;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://localhost:1433;databaseName=pas";
            con = DriverManager.getConnection(url, "sa","sa");
            sta = con.createStatement();
            rows = sta.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动程序没找到");
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            this.close();
        }
        return rows;
    }

    /**
     * 用来执行select sql语句
     * @param sql 要执行的sql语句
     * @return 返回结果集
     */
    public ResultSet query(String sql) {
        System.out.println(sql);
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://localhost:1433;databaseName=pas";
            con = DriverManager.getConnection(url, "sa","sa");
            sta = con.createStatement();
            rs = sta.executeQuery(sql);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动程序没找到");
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    /**
     * 关闭资源
     */
    public void close() {
        try {
            if (rs != null){
                rs.close();
                rs = null;
            }
            if (sta != null){
                sta.close();
                sta = null;
            }
            if (con != null){
                con.close();
                con = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
//test包的参考代码
public class TeacherDAOTest {
    public static void testSave() {
        String teaNum = "20211001";
        String teaName = "宋江";
        String teaTitle = "教授";
        int teaTypeId = 1;

        String btdStr = "1974-02-12";
        Date teaBtd = DateConvert.convertToDate(btdStr);

        String pwd = "meiyou";
        int state = 1;
        int roleId = 0;
        String key1 = "";
        int key2 = 0;
        Teacher t = new Teacher(teaNum, teaName, teaTitle, teaTypeId, teaBtd, pwd, state, roleId, key1, key2);
        TeacherDAO teacherDAO = new TeacherDAO();
        boolean ok = teacherDAO.save(t);
        if (ok) {
            System.out.println("新增老师信息成功！");
        } else {
            System.out.println("新增老师信息失败！");
        }
    }
    public static void testUpdate() {
        String teaNum = "20211001";
        String teaName = "刘晓燕";
        String teaTitle = "教授";
        int teaTypeId = 1;
        String btdStr = "1974-02-12";
        Date teaBtd = DateConvert.convertToDate(btdStr);
        String pwd = "meiyou";
        int state = 1;
        int roleId = 0;
        String key1 = "";
        int key2 = 0;
        Teacher t = new Teacher(teaNum, teaName, teaTitle, teaTypeId, teaBtd, pwd, state, roleId, key1, key2);
        TeacherDAO teacherDAO = new TeacherDAO();
        boolean ok = teacherDAO.update(t);
        if (ok) {
            System.out.println("修改老师信息成功！");
        } else {
            System.out.println("修改老师信息失败！");
        }
    }
    public static void testDelete() {
        TeacherDAO teacherDAO = new TeacherDAO();
        boolean ok = teacherDAO.delete("20211001");
        if (ok) {
            System.out.println("删除老师信息成功！");
        } else {
            System.out.println("删除老师信息失败！");
        }
    }
    public static void testFindAll() {
        TeacherDAO teacherDAO = new TeacherDAO();
        List<Teacher> ts = teacherDAO.findAll();
        System.out.println(ts.size());
        for(Teacher t : ts){
            System.out.println(t);
        }
    }
    public static void testFindById() {
        TeacherDAO teacherDAO = new TeacherDAO();
        Teacher t = teacherDAO.findById("19800201");
        System.out.println(t);//toString
    }


    public static void main(String[] args) {
        //TDD test Driver Design(测试驱动的设计开发)
        testSave();
        testUpdate();
        testFindById();
        testFindAll();
        testDelete();
    }
}

3.测试结果