JDBC增删改查操作总结

JDBC增删改查操作总结

1.Insert插入操作

使用jdbcTemplate的update()方法执行

例子

private final static String INSERT_USER_SQL= "INSERT INTO t_user(user_id,user_num,user_name,user_sex,user_age,password) VALUES(?,?,?,?,?,?)";

public int insertUser(User user ) {
   Object[] args = { user.getUserId(),user.getUserNum(), user.getUserName(),user.getUserSex(),user.getUserAge(), user.getPassword() };
   return jdbcTemplate.update(INSERT_USER_SQL, args);
}

2.delete删除操作

使用jdbcTemplate的update()方法执行

例子

private final static String DELETE_USER_SQL = "DELETE FROM t_user WHERE user_id=?";

public void DeleteUser(final String userId) {
    jdbcTemplate.update(DELETE_USER_SQL,userId);
}

3.update修改操作

使用jdbcTemplate的update()方法执行

例子

private final static String REVISE_USER_SQL= " UPDATE t_user SET " + " user_num=?,user_name=?,user_age=?,user_sex=?,password=?,credits=? WHERE user_id =?";

public void ReviseUser(User user){
    jdbcTemplate.update(REVISE_USER_SQL,new Object[] {
            user.getUserNum(),
            user.getUserName(),
            user.getUserAge(),
            user.getUserSex(),
            user.getPassword(),
            user.getCredits(),
            user.getUserId()
    });
}

4.select查询操作

使用jdbcTemplate的query()方法执行

例子

private final static String QUERY_USER_NAME_SQL = " SELECT user_id,user_num,user_name,user_sex,user_age,password,credits " + " FROM t_user WHERE user_name =? ";

public User QueryName(final String userName) {
    final User user = new User();
    jdbcTemplate.query(QUERY_USER_NAME_SQL, new Object[]{userName}, sr -> {
        user.setUserId(sr.getInt("user_id"));
        user.setUserNum(sr.getString("user_num"));
        user.setUserName(sr.getString("user_name"));
        user.setUserSex(sr.getString("user_sex"));
        user.setUserAge(sr.getInt("user_age"));
        user.setPassword(sr.getString("password"));
        user.setCredits(sr.getInt("credits"));
    });
    return user;
}

private  final static String MATCH_COUNT_SQL = " SELECT count(*) FROM t_user  " +
      " WHERE user_num =? and password=? ";

public int getMatchCount(String userNum, String password) {

   return jdbcTemplate.queryForObject(MATCH_COUNT_SQL, new Object[]{userNum, password},Integer.class);
}