jdbc;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import jdbc.model.Person;
import jdbc.service.PersonService;
@ContextConfiguration(locations = "classpath:beans.xml"
)
@RunWith(SpringJUnit4ClassRunner.class)
public class JunitTest {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
@Autowired
private PersonService personService;
@Test
public void query1(){
List<Person> a= jdbcTemplate.query("select * from person where id = ? or name = ?",
new Object[]{1,"zhu"},
new RowMapper<Person>
(){
@Override
public Person mapRow(ResultSet rs,
int rowNum)
throws SQLException {
Person p =
new Person();
p.setId(rs.getInt("id"
));
p.setName(rs.getString("name"
));
p.setAge(rs.getInt("age"
));
p.setPass(rs.getString("pass"
));
return p;
}
});
System.out.println(a);
Map<String,Object> map =
new HashMap<>
();
ArrayList<Integer> arr =
new ArrayList<>
();
arr.add(1
);
arr.add(3
);
map.put("ids"
,arr);
List<Person> b = namedParameterJdbcTemplate.query("select * from person where id IN (:ids)",map,
new BeanPropertyRowMapper<>(Person.
class));
System.out.println(b);
}
@Test
public void save1() {
Person p =
new Person();
p.setId(5
);
p.setAge(11
);
p.setName("Ead"
);
p.setPass("00000"
);
jdbcTemplate.update(
"insert into person(id,name,age,pass) values(?,?,?,?)"
,
new Object[]{p.getId(),p.getName(),p.getAge(),p.getPass()}
);
}
@Test
public void save2() {
Person p =
new Person();
p.setId(6
);
p.setAge(11
);
p.setName("Ead"
);
p.setPass("00000"
);
jdbcTemplate.update("insert into person(name,age,pass) values(?,?,?)"
,
new PreparedStatementSetter(){
@Override
public void setValues(PreparedStatement ps)
throws SQLException {
ps.setString(1
, p.getName());
ps.setInt(2
, p.getAge());
ps.setString(3
, p.getPass());
}
});
System.out.println(personService.getPersonList());
}
@Test
public void update1() {
Person p =
new Person();
p.setId(6
);
p.setAge(11
);
p.setName("Eada"
);
p.setPass("232323"
);
jdbcTemplate.update(
"update person set name=？ where id=?"
,
new PreparedStatementSetter(){
@Override
public void setValues(PreparedStatement ps)
throws SQLException {
ps.setString(1
, p.getName());
ps.setInt(2
, p.getId());
}
}
);
}
@Test
public void update2() {
Person p =
new Person();
p.setId(6
);
p.setAge(11
);
p.setName("Eada"
);
p.setPass("232323"
);
jdbcTemplate.update(
"update person set name=? where id=?"
,
new PreparedStatementSetter(){
@Override
public void setValues(PreparedStatement ps)
throws SQLException {
ps.setString(1
, p.getName());
ps.setInt(2
, p.getId());
}
}
);
}
@Test
public void delete() {
jdbcTemplate.update(
"delete from person where id = ?"
,
new Object[]{5
},
new int[]{java.sql.Types.INTEGER});
}
@Test
public void queryForInt1(){
int i = jdbcTemplate.queryForInt("select count(0) from person where name = ?"
,
new Object[]{"zhu"
});
System.out.println(i);
}
@Test
public void queryForObject2() {
String p = jdbcTemplate.queryForObject("select name from person where id = 3", String.
class);
System.out.println(p);
}
@Test
public void queryForObject3() {
//
Person p = (Person) jdbcTemplate.queryForObject("select * from person where id = 3",
new RowMapper<Person>
(){
@Override
public Person mapRow(ResultSet rs,
int rowNum)
throws SQLException {
Person user =
new Person();
user.setId(rs.getInt("id"
));
user.setName(rs.getString("name"
));
user.setPass(rs.getString("pass"
));
user.setAge(rs.getInt("age"
));
return user;
}
}
);
System.out.println(p);
}
@Test
public void queryForList2() {
List<String> list = (List<String>) jdbcTemplate.queryForList("select name from person "
,
String.class);
System.out.println(list);
}
@Test
public void queryForList3() {
// 不行
List<Person> list = (List<Person>) jdbcTemplate.queryForList("select * from person "
,
Person.class);
System.out.println(list);
}
@Test
public void getPersonInfo() {
List<Map<String,Object>> list =
this.jdbcTemplate.queryForList("select * from person"
);
System.out.println(list);
}
@Test
public void testBeanPropertyRowMapper(){
// 推荐,返回 对象list
List<Person> result = jdbcTemplate.query("select * from person where id = :id"
,
new BeanPropertyRowMapper<Person>(Person.
class));
System.out.println(result);
}
@Test
public void testColumnMapRowMapper(){
//返回对象map
List<Map<String,Object>> result = jdbcTemplate.query("select * from person"
,
new ColumnMapRowMapper());
System.out.println(result);
}
@Test
public void testSingleColumnRowMapper(){
//返回一列集合
List<String> result1 =
jdbcTemplate.query(
"select name from person"
,
new SingleColumnRowMapper<String>
());
System.out.println(result1);
}
@Test
public void list1() {
// 查询结果
List<Person> list = (List<Person>) jdbcTemplate.query("select * from person"
,
new RowMapper<Person>
(){
@Override
public Person mapRow(ResultSet rs,
int rowNum)
throws SQLException {
Person user =
new Person();
user.setId(rs.getInt("id"
));
user.setAge(rs.getInt("age"
));
user.setName(rs.getString("name"
));
user.setPass(rs.getString("pass"
));
return user;
}
});
System.out.println(list);
}
@Test
public void batchUpdate() {
List<Person> list =
new ArrayList<>
();
Person p =
new Person();
p.setId(6
);
p.setAge(11
);
p.setName("Edddddddada"
);
p.setPass("232323"
);
list.add(p);
int[] updateCounts =
jdbcTemplate.batchUpdate(
"update person set name = ?, pass = ? where id = ?"
,
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps,
int i)
throws SQLException {
ps.setString(1
, ((Person)list.get(i)).getName());
ps.setString(2
, ((Person)list.get(i)).getPass());
ps.setLong(3
, ((Person)list.get(i)).getId());
}
@Override
public int getBatchSize() {
return list.size();
}
}
);
System.out.println(Arrays.toString(updateCounts));
}
}
package jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import jdbc.model.Person;
import jdbc.service.PersonService;
@ContextConfiguration(locations = "classpath:beans.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class NamedParameterJdbcTemplateTest {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
@Autowired
private PersonService personService;
@Test
public void testInsertSql(){
String insertSql = "insert into person (name,age,pass) values(:name,:age,:pass);";
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("name", "Aily");
paramMap.put("age", 22);
paramMap.put("pass", "333");
namedParameterJdbcTemplate.update(insertSql, paramMap);
}
@Test
public void tes1queryq(){
String selectSql = "select id,name from person where name=:name";
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("name", "Ead");
List<Integer> result = new ArrayList<Integer>();
namedParameterJdbcTemplate.query(selectSql, paramMap,
new RowCallbackHandler() {
@Override
public void processRow(ResultSet rs) throws SQLException {
result.add(rs.getInt("id"));
}
});
System.out.println(result);
}
@Test
public void testquery2(){
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("id", 4);
String sql = "select name,age,pass from person "
+ "where id > :id";
List<Map<String,Object>> result = namedParameterJdbcTemplate.queryForList(sql, paramMap);
for(Map<String,Object> map : result){
System.out.println("------------");
System.out.println(map.get("name"));
System.out.println(map.get("age"));
System.out.println(map.get("pass"));
}
System.out.println(result);
}
@Test
public void testdeleteSql(){
String deleteSql = "delete from test where name=:name";
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("name", "Ead");
namedParameterJdbcTemplate.update(deleteSql, paramMap);
}
@Test
public void testBeanPropertyRowMapper(){ // 推荐,返回 对象list
List<Person> result = namedParameterJdbcTemplate.query("select * from person",
new BeanPropertyRowMapper<Person>(Person.class));
System.out.println(result);
}
/*-------------------三种结果集的查询使用------------*/
@Test
public void testResultSetExtractor(){
List<Person> list = (List<Person>)jdbcTemplate.query("select * from person", new ResultSetExtractor<List<Person>>(){
public List<Person> extractData(ResultSet rs) throws SQLException,DataAccessException {
List<Person> ps = new ArrayList<>();
while(rs.next()) {
Person p = new Person();
p.setId(rs.getInt("id"));
p.setName(rs.getString("name"));
p.setAge(rs.getInt("age"));
p.setPass(rs.getString("pass"));
ps.add(p);
}
return ps;
}});
System.out.println(list);// 返回整个查询结果
}
@Test
public void testRowMapper(){
List<Person> list = jdbcTemplate.query("select * from person", new RowMapper<Person>(){
public Person mapRow(ResultSet rs, int rowNumber) throws SQLException {
Person p = new Person();
p.setId(rs.getInt("id"));
p.setName(rs.getString("name"));
p.setAge(rs.getInt("age"));
p.setPass(rs.getString("pass"));
return p;
}});
System.out.println(list);// 返回整个查询结果
}
@Test
public void testRowCallbackHandler(){
List<Person> list = new ArrayList<>();
jdbcTemplate.query("select * from person", new RowCallbackHandler(){
public void processRow(ResultSet rs) throws SQLException {
Person p = new Person();
p.setId(rs.getInt("id"));
p.setName(rs.getString("name"));
p.setAge(rs.getInt("age"));
p.setPass(rs.getString("pass"));
list.add(p);
}});
System.out.println(list);// 返回整个查询结果
}
}
jdbcTemplate 详细使用
标签：
