Spring的sql操作

xml配置jdbc

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                           http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/context
                           http://www.springframework.org/schema/context/spring-context.xsd
                           http://www.springframework.org/schema/aop
                           http://www.springframework.org/schema/aop/spring-aop.xsd">
    <context:component-scan base-package="com.sy"/>
    <!--
        配置数据源
    -->
    <bean id="ds" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
        <property name="url" value="jdbc:oracle:thin:@localhost:1521:ORCL"></property>
        <property name="username" value="scott"></property>
        <property name="password" value="tiger"></property>
    </bean>
    <!--
        配置SpringJdbc
    -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="ds"></property>
    </bean>

    <bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg name="dataSource" ref="ds"></constructor-arg>
    </bean>
</beans>

sql操作

 public void test01() {
        System.out.println(jdbcTemplate.queryForList("select * from emp"));
    }

public void test02() {
        //不带参数的列表查询
        List<Emp> emps = jdbcTemplate.query("select * from emp", new BeanPropertyRowMapper<>(Emp.class));
        System.out.println(emps);
    }

  public void test03() {
        //带参数的列表查询
        // List<Emp> emps=jdbcTemplate.query("select * from emp where empno=? and ename=?",new Object[]{7369,"SMITH"} ,new BeanPropertyRowMapper<>(Emp.class));
        List<Emp> emps = jdbcTemplate.query("select * from emp where empno=? and ename=?", new BeanPropertyRowMapper<>(Emp.class), 7369, "SMITH");
        for (Emp emp : emps) {
            System.out.println(emp.getEname() + "\t" + emp.getJob());
        }
    }

 public void test04() {
        //查询单个对象
        //注意：如果单个对象没有找到，会出现异常
        //如果大于1条也是错误的
        Emp emp = jdbcTemplate.queryForObject("select * from emp where empno=?", new BeanPropertyRowMapper<>(Emp.class), 7369);
        System.out.println(emp);

        int count = jdbcTemplate.queryForObject("select count(*) from emp", Integer.class);
        System.out.println(count);
    }

 public void test05() {
        //查询某列的所有值
        List<String> enames = jdbcTemplate.queryForList("select ename from emp where empno>?", String.class, 7500);
        System.out.println(enames);
    }

 public void test06() {
        //DML操作,返回受影响的行数
        int rows = jdbcTemplate.update("insert into dept(dname,loc) values(?,?)", "研发", "苏州");
        System.out.println(rows);

    }

 public void test07() {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        //返回自动递增主键
        int rows = jdbcTemplate.update((con) -> {
            PreparedStatement ps = con.prepareStatement("insert into dept(dname,loc) values(?,?)", new String[]{"empno"});
            ps.setString(1, "研发");
            ps.setString(2, "苏州");
            return ps;
        }, keyHolder);
        System.out.println(rows);
        System.out.println(keyHolder.getKey().intValue());

    }

 public void test08() {
        //根据部门编号查询员工信息
        //部门编号可能是10，
        //10,20
        //10,20,30

        Map<String, Object> map = new HashMap<>();
        map.put("deptnolist", Arrays.asList(20, 30));
        map.put("sal", 1600);
        List<Emp> emps = namedParameterJdbcTemplate.query("select * from emp where deptno in(:deptnolist) and sal>:sal", map, new BeanPropertyRowMapper<>(Emp.class));
        System.out.println(emps);


    }

//分页模糊查询
  public List<Sports> query(String likeStr, int page, int pageSize) {
        String sql = "select * from(select rownum r ,g.* from(select *from SPORTS where SPONAME like ? order by ap_time desc ) g )where r>? and r<=?";
        RowMapper<Sports> sports = new BeanPropertyRowMapper<Sports>(Sports.class);
        List<Sports> list = jdbcTemplate.query(sql, sports, new Object[]{"%" + likeStr + "%", (page - 1) * pageSize, page * pageSize});
        System.out.println(list);
        return list;

    }

//查询逻辑序列
 public int querySeq() {
//        测试
//        int count =jdbcTemplate.queryForObject("select SPORTS_ID_SQE.nextval from dual",Integer.class);
//        System.out.println(count);
        return jdbcTemplate.queryForObject("select SPORTS_ID_SQE.nextval from dual", Integer.class);
    }

//单个对象模糊查询
 public int queryCount(String likeStr) {
        String sql = "select count(*) from SPORTS where SPONAME like ?";
        return jdbcTemplate.queryForObject(sql, Integer.class, "%" + likeStr + "%");
    }

//插入对象，Spring自动映射属性
 public int insert(Sports sports) {
        java.sql.Date date=new java.sql.Date(sports.getBg_time().getTime());
        String sql = "insert into SPORTS(SPORTS_ID,SPONAME ,STUNAME ,DEPART ,STUSEX ,STUAGE ,BG_TIME ,AP_TIME,PHONE) values(?,?,?,?,?,?,?,sysdate,?)";
        return jdbcTemplate.update(sql,sports.getSports_id(),sports.getSponame(),sports.getStuname(),sports.getDepart(),sports.getStusex(),sports.getStuage()
        ,date,sports.getPhone());
    }