JdbcTemplate实现增删改查

导入相应的包

增加数据

其实除了查询其他的操作调用的都是update方法，只是参数有所区别

@Test
public void testAdd() {
        //设置数据源
    DriverManagerDataSource dd = new DriverManagerDataSource();
    dd.setDriverClassName("com.mysql.jdbc.Driver");
    dd.setUrl("jdbc:mysql:///users");
    dd.setUsername("root");
    dd.setPassword("数据库密码");

    //将数据源放入JdbcTemplate中
    JdbcTemplate jt = new JdbcTemplate(dd);
    //编写sql语句
    String sql = "insert into myclass values(?,?,?)";
    //调用他的update方法
    jt.update(sql, "4","xue","7978");
}

删除数据

@Test
public void testDelete() {
    DriverManagerDataSource dd = new DriverManagerDataSource();
    dd.setDriverClassName("com.mysql.jdbc.Driver");
    dd.setUrl("jdbc:mysql:///users");
    dd.setUsername("root");
    dd.setPassword("数据库密码");

    JdbcTemplate jt = new JdbcTemplate(dd);
    String sql = "delete from myclass where id=?";
    jt.update(sql, "1");
}

修改数据

@Test
public void testUpdate() {
    DriverManagerDataSource dd = new DriverManagerDataSource();
    dd.setDriverClassName("com.mysql.jdbc.Driver");
    dd.setUrl("jdbc:mysql:///users");
    dd.setUsername("root");
    dd.setPassword("数据库密码");

    JdbcTemplate jt = new JdbcTemplate(dd);
    String sql = "update myclass set name=? where id=?";
    jt.update(sql, "smn","3");
}

查询数据

查询返回某一个值

@Test
public void testSelect() {
    DriverManagerDataSource dd = new DriverManagerDataSource();
    dd.setDriverClassName("com.mysql.jdbc.Driver");
    dd.setUrl("jdbc:mysql:///users");
    dd.setUsername("root");
    dd.setPassword("数据库密码");

    JdbcTemplate jt = new JdbcTemplate(dd);
    String sql = "select count(*) from myclass";
    int count = jt.queryForObject(sql, Integer.class);//此处返回数据的总数（一个数值）
    System.out.println(count);
}

查询返回对象：Connection 方式

@Test
public void testjdbc() throws Exception
    {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
           //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
           //创建连接
        conn = DriverManager.getConnection("jdbc:mysql:///users","root","数据库密码");
        //编写sql语句
        String sql = "select * from myclass where name=?";
        //预编译
        ps = conn.prepareStatement(sql);
        //设置参数值
        ps.setString(1, "嘻嘻");
        //执行sql
        rs = ps.executeQuery();
        //遍历结果集
        while(rs!=null)
        {
            if(rs.next())
            {
                int id = rs.getInt("id");
                int sex = rs.getInt("sex");
                user user = new user();
                user.setName(id);
                user.setSex(sex);
                System.out.println(user);
            }

        }
    rs.close();
    ps.close();
    conn.close();
    ps.close();
}

User.java

package springjdbc;

public class user {
    private int id;
    private int sex;
    public int getName() {
        return id;
    }
    public void setName(int  id) {
        this.id = id;
    }
    public int getSex() {
        return sex;
    }
    public void setSex(int sex) {
        this.sex = sex;
    }
    @Override
    public String toString() {
        return "user [name=" + id + ", sex=" + sex + "]";
    }
}

查询返回对象：JdbcTemplate 方式

@Test
public void testObject() throws Exception {
    DriverManagerDataSource dd = new DriverManagerDataSource();
    dd.setDriverClassName("com.mysql.jdbc.Driver");
    dd.setUrl("jdbc:mysql:///users");
    dd.setUsername("root");
    dd.setPassword("数据库密码");

    String sql = "select * from myclass where name =?";
    JdbcTemplate jt = new JdbcTemplate(dd);//jt对Jdbc进行封装
    user user = jt.queryForObject(sql, new rowmapper(),"嘻嘻");
    System.out.println(user);
}

此处调用的是这个方法

其中RowMapper是个接口，需要自己写类实现接口，自己做数据封装

RowMapper实现类

public class rowmapper implements RowMapper<user> {

    @Override
    public user mapRow(ResultSet rs, int arg1) throws SQLException {
        // TODO Auto-generated method stub
        int id = rs.getInt("id");
        int sex = rs.getInt("sex");
        user user = new user();
        user.setName(id);
        user.setSex(sex);
        return user;

    }
}

查询返回List集合

@Test
public void testList() {
    DriverManagerDataSource dd = new DriverManagerDataSource();
    dd.setDriverClassName("com.mysql.jdbc.Driver");
    dd.setUrl("jdbc:mysql:///users");
    dd.setUsername("root");
    dd.setPassword("数据库密码");

    String sql = "select * from myclass";
    JdbcTemplate jt = new JdbcTemplate(dd);
    List<user> list = jt.query(sql, new myrowmapper());
    System.out.println(list);
}

此处调用的是这个方法