hooyantsing's Blog

第46次课程

字数统计: 1.3k阅读时长: 7 min
2019/07/25

源辰74班

第46次课程

07.25.2019

内容

java.jdbc高级技术[廖彦]

1.jdbc封装

将从数据库获取的数据打包成HashMap包放入List后返回

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public List<Map<String,Object>>  selectByName(String dname) throws  SQLException, ClassNotFoundException{
           Class.forName("oracle.jdbc.driver.OracleDriver");
           String url =  "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
           String user = "scott";
           String password = "tiger";
           Connection conn =  DriverManager.getConnection(url, user,  password);
           
           String sql = "select * from dept  where dname like ?";
           PreparedStatement ps =  conn.prepareStatement(sql);
           ps.setString(1, "%"+dname+"%");
           ResultSet rs = ps.executeQuery();
           List<Map<String,Object>> ret =  new ArrayList<>();
           while(rs.next()) {
               Map<String,Object> row = new  LinkedHashMap<>();
               row.put("DEPTNO",  rs.getInt("DEPTNO"));
               row.put("DNAME",rs.getString("DNAME"));
               row.put("LOC",  rs.getString("LOC"));
               ret.add(row);
           }
           conn.close();
           return ret;
}

2.数据库命令拼接(可多选条件)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
public List<Map<String,Object>>  selectByNameAndLoc(String dname,String loc)  throws ClassNotFoundException, SQLException{
       String sql = "select * from dept  where 1=1";
       List<Object> paramList = new  ArrayList<>();
       //sql命令字符串拼接
       if(dname != null) {
           sql += " and dname like ?";
           paramList.add("%"+dname+"%");
       }
       if(loc != null) {
           sql += " and loc like ?";
           paramList.add("%"+loc+"%");
       }
       Class.forName("oracle.jdbc.driver.OracleDriver");
       String url =  "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
       String user = "scott";
       String password = "tiger";
       Connection conn =  DriverManager.getConnection(url, user,  password);
       //预编译sql对象
       PreparedStatement ps =  conn.prepareStatement(sql);
       //将参数传入预编译sql对象
       for(int i=0;i<paramList.size();i++)  {
           ps.setObject(i+1,  paramList.get(i));
       }
       ResultSet rs = ps.executeQuery();
       List<Map<String,Object>> ret = new  ArrayList<>();
       while(rs.next()) {
           Map<String,Object> row = new  LinkedHashMap<>();
           row.put("DEPTNO",  rs.getInt("DEPTNO"));
           row.put("DNAME",rs.getString("DNAME"));
           row.put("LOC",  rs.getString("LOC"));
           ret.add(row);
       }
       conn.close();
       return ret;
}

3.数据库自增序列调用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//关于数据库自增序列的应用
       public void insert(String  dname,String loc) throws  ClassNotFoundException, SQLException {
           Class.forName("oracle.jdbc.driver.OracleDriver");
           String url =  "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
           String user = "scott";
           String password = "tiger";
           Connection conn =  DriverManager.getConnection(url, user,  password);
           
           String sql = "insert into dept  values(SEQ_DEPT_NO.nextval,?,?)";
           PreparedStatement ps =  conn.prepareStatement(sql);
           ps.setString(1, dname);
           ps.setString(2, loc);
           ps.executeUpdate();
           conn.close();
}

4.Oracle与java关于Date类型转换

根据入职日期查询

java.sql.Date          SQL的日期类型,表示年月日

java.sql.Timestamp SQL的日期类型,表示年月日 时分秒(精确到毫秒级)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    /**
     * 根据入职日期查询
     * java.sql.Date          SQL的日期类型,表示年月日
     * java.sql.Timestamp SQL的日期类型,表示年月日 时分秒(精确到毫秒级)
     */
    public List<Map<String,Object>>  selectByHiredate(Date hiredate) throws  SQLException, ClassNotFoundException{
       Class.forName("oracle.jdbc.driver.OracleDriver");
       String url =  "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
       String user = "scott";
       String password = "tiger";
       Connection conn =  DriverManager.getConnection(url, user,  password);
        
       String sql = "select * from emp  where hiredate = ?";
       //查询其他格式日期(如1982-2)的方法
       //String sql = "select * from emp  where to_char(hiredate,'yymm') =  to_char(?,'yymm')";
       //select * from emp where   extract(year from hiredate) = 1981 and  extract(month from hiredate) = 2;
       PreparedStatement ps =  conn.prepareStatement(sql);
       ps.setDate(1, hiredate);
       ResultSet rs = ps.executeQuery();
       List<Map<String,Object>> ret = new  ArrayList<>();
       while(rs.next()) {
           //LinkedHashMap 有序的HashMap
           Map<String,Object> row = new  LinkedHashMap<>();
           row.put("EMPNO",  rs.getInt("EMPNO"));
           row.put("ENAME",rs.getString("ENAME"));
           row.put("HIREDATE",  rs.getString("HIREDATE"));
           ret.add(row);
       }
       conn.close();
       return ret;
}

5.@Test测试

单元测试

Class DeptDaoTest

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@Test
    public void test1() throws  ClassNotFoundException, SQLException {
       DeptDao dao = new DeptDao();
       List<Map<String,Object>> ret =  dao.selectByName("S");
       System.out.println(ret);
       /**
        * 单元测试判断:断言
        */
       Assert.assertEquals(3, ret.size());
    }
    @Test
    public void test2() throws  ClassNotFoundException, SQLException {
       DeptDao dao = new DeptDao();
       List<Map<String,Object>> ret =  dao.selectByNameAndLoc("S", "O");
       System.out.println(ret);
       /**
        * 单元测试判断:断言
        */
       Assert.assertEquals(2, ret.size());
    }
    
    @Test
    public void test3() throws  ClassNotFoundException, SQLException {
       DeptDao dao = new DeptDao();
       dao.insert("财务部", "衡阳");
       List<Map<String,Object>> ret =  dao.selectByName("财务部");
       Assert.assertEquals(1, ret.size());
}

Class EmpDaoTest

1
2
3
4
5
6
7
8
9
@Test
    public void EmpDaoTest() throws  ClassNotFoundException, SQLException {
       EmpDao edao = new EmpDao();
       Date hiredate =  Date.valueOf("1981-2-20");
       
       List<Map<String,Object>> ret =  edao.selectByHiredate(hiredate);
       System.out.println(ret);
       Assert.assertEquals(true,ret.size()>0);
}

6.课堂作业

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
    /**
     * 课堂练习
     * 1.请将1982年前入职的员工加薪100
     * 2.将FORD和BLAKE的下属员工对调
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Test
    public void payRiseTest() throws  ClassNotFoundException, SQLException {
       EmpDao edao = new EmpDao();
       edao.payRise();
    }
    @Test
    public void employeeAlignmentTest()  throws ClassNotFoundException, SQLException  {
       EmpDao edao = new EmpDao();
       edao.employeeAlignment();
    }

功能方法源码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
//课堂作业
    public void payRise() throws  ClassNotFoundException, SQLException {
       Class.forName("oracle.jdbc.driver.OracleDriver");
       String url =  "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
       String user = "scott";
       String password = "tiger";
       Connection conn =  DriverManager.getConnection(url, user,  password);
       
       String sql = "update empcopy set  sal=sal+100 where extract(year from  hiredate) < 1982";
       PreparedStatement ps =  conn.prepareStatement(sql);
       ps.executeUpdate();
       conn.close();
    }
    public void employeeAlignment() throws  ClassNotFoundException, SQLException {
       Class.forName("oracle.jdbc.driver.OracleDriver");
       String url =  "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
       String user = "scott";
       String password = "tiger";
       Connection conn =  DriverManager.getConnection(url, user,  password);
       
       String sql1 = "update empcopy set  MGR = 0 where MGR = 7698";
       PreparedStatement ps1 =  conn.prepareStatement(sql1);
       ps1.executeUpdate();
       String sql2 = "update empcopy set  MGR = 7698 where MGR = 7902";
       PreparedStatement ps2 =  conn.prepareStatement(sql2);
       ps2.executeUpdate();
       String sql3 = "update empcopy set  MGR = 7902 where MGR = 0";
       PreparedStatement ps3 =  conn.prepareStatement(sql3);
       ps3.executeUpdate();
}
CATALOG