JDBCTemplate-执行DML语句、执行DQL语句

JDBCTemplate-执行DML语句

 

 

 

public class Emp {
    private int id;
    private String ename;
    private int job_id;
    private int mgr;
    private Date joindate;
    private double salary;
    private double bonus;
    private int dept_id;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public int getJob_id() {
        return job_id;
    }

    public void setJob_id(int job_id) {
        this.job_id = job_id;
    }

    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public Date getJoindate() {
        return joindate;
    }

    public void setJoindate(Date joindate) {
        this.joindate = joindate;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public double getBonus() {
        return bonus;
    }

    public void setBonus(double bonus) {
        this.bonus = bonus;
    }

    public int getDept_id() {
        return dept_id;
    }

    public void setDept_id(int dept_id) {
        this.dept_id = dept_id;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", ename='" + ename + '\'' +
                ", job_id=" + job_id +
                ", mgr=" + mgr +
                ", joindate=" + joindate +
                ", salary=" + salary +
                ", bonus=" + bonus +
                ", dept_id=" + dept_id +
                '}';
    }

import cn.itcast.datasource.utils.JDBCUtils;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;

public class jdbcTemplateDemo2 {
    /**
     * junit单元测试，可以让方法独立执行
     */

    /**
     * 修改1号数据的salary为10000
     */
    //获取JDBCTemplate
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    @Test
    public void test1() {
        //定义sql
        String sql = "update emp set salary = 10000 where id = 1001";
        //执行sql
        int update = jdbcTemplate.update(sql);
        //输出
        System.out.println(update);
    }

    /**
     * 添加一条记录
     */
    @Test
    public void test2() {
        //定义sql
        String sql= "insert into emp(id,ename,dept_id) value(?,?,?)";
        //执行sql
        int ai = jdbcTemplate.update(sql, 1015, "哎嘿嘿", 20);
        //输出
        System.out.println(ai);
    }

/**
 * 删除一条记录
 */

@Test public void test3(){ //定义sql String sql = "delete from emp where id = ?"; //执行slq int update = jdbcTemplate.update(sql, 1015); //输出 System.out.println(update); } }

 

JDBCTemplate-执行DQL语句

EMP

public class Emp {
    private Integer id;
    private String ename;
    private Integer job_id;
    private Integer mgr;
    private Date joindate;
    private Double salary;
    private Double bonus;
    private Integer dept_id;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public Integer getJob_id() {
        return job_id;
    }

    public void setJob_id(Integer job_id) {
        this.job_id = job_id;
    }

    public Integer getMgr() {
        return mgr;
    }

    public void setMgr(Integer mgr) {
        this.mgr = mgr;
    }

    public Date getJoindate() {
        return joindate;
    }

    public void setJoindate(Date joindate) {
        this.joindate = joindate;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public Double getBonus() {
        return bonus;
    }

    public void setBonus(Double bonus) {
        this.bonus = bonus;
    }

    public Integer getDept_id() {
        return dept_id;
    }

    public void setDept_id(Integer dept_id) {
        this.dept_id = dept_id;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", ename='" + ename + '\'' +
                ", job_id=" + job_id +
                ", mgr=" + mgr +
                ", joindate=" + joindate +
                ", salary=" + salary +
                ", bonus=" + bonus +
                ", dept_id=" + dept_id +
                '}';
    }
}

 

package cn.itcast.datasource.jdbcTemplate;

import cn.itcast.datasource.domain.demain.Emp;
import cn.itcast.datasource.utils.JDBCUtils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class jdbcTemplateDemo2 {
    /**
     * junit单元测试，可以让方法独立执行
     */

    /**
     * 修改1号数据的salary为10000
     */
    //获取JDBCTemplate
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    @Test
    public void test1() {
        //定义sql
        String sql = "update emp set salary = 10000 where id = 1001";
        //执行sql
        int update = jdbcTemplate.update(sql);
        //输出
        System.out.println(update);
    }

    /**
     * 添加一条记录
     */
    @Test
    public void test2() {
        //定义sql
        String sql= "insert into emp(id,ename,dept_id) value(?,?,?)";
        //执行sql
        int ai = jdbcTemplate.update(sql, 1015, "哎嘿嘿", 20);
        //输出
        System.out.println(ai);
    }

    /**
     * 删除一条记录
     */
    @Test
    public void test3(){
        //定义sql
        String sql = "delete from emp where id = ?";
        //执行slq
        int update = jdbcTemplate.update(sql, 1015);
        //输出
        System.out.println(update);
    }

    /**
     * 查询id为1001的记录，将其封装为map集合
     */
    @Test
    public void test4(){
        //定义sql
        String sql = "select * from emp where id = ?";
        //封装为map集合
        Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql, 1001);
        //输出
        System.out.println(stringObjectMap);
    }

    /**
     * 查询所有记录，将其封装为list集合
     */
    @Test
    public void test5(){
        //定义sql
        String sql = "select * from emp";
        //转变list集合
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        //遍历
        for (Map<String, Object> map : maps) {
            //输出
            System.out.println(map);
        }
    }

    /**
     * 查询所有记录，将其封装为emp对象的list集合
     */

    @Test
    public void test6(){
        //定义sql
        String sql = "select * from emp";
        //转变list集合
        List<Emp> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
        //遍历
        for (Emp emp: list){
            //输出
            System.out.println(emp);
        }
    }

    @Test
    public void test7(){
        //定义sql
        String sql = "select count(id) from emp";
        //查询
        Long aLong = jdbcTemplate.queryForObject(sql, Long.class);
        //输出
        System.out.println(aLong);
    }
}