Spring Jdbc一般操作
バージョン:Spring 2.5
データベース:Mysql
内容は主にSpring JdbcTemplate SimpleJdbcTemplateのいくつかの操作を含んで、呼び出しストレージプロセスを含まない(mysqlのストレージプロセスは書かない)
データベース名:Spring_Test,表名:tb_user
データベース:Mysql
内容は主にSpring JdbcTemplate SimpleJdbcTemplateのいくつかの操作を含んで、呼び出しストレージプロセスを含まない(mysqlのストレージプロセスは書かない)
データベース名:Spring_Test,表名:tb_user
package com.spring.xkey.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
class User {
private int userid;
private String username;
private String email;
public void setUserid(int id) {
userid = id;
}
public int getUserid() {
return userid;
}
public void setUsername(String user) {
username = user;
}
public String getUsername() {
return username;
}
public void setEmail(String email) {
this.email = email;
}
public String getEmail() {
return email;
}
}
public class Test {
private static JdbcTemplate jdbcTemplate;
public void setUp() {
String url = "jdbc:mysql://localhost:3306/Spring_Test";
String username = "root";
String password = "123456";
DriverManagerDataSource dataSource = new DriverManagerDataSource(url,
username, password);
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
jdbcTemplate = new JdbcTemplate(dataSource);
}
public void testPreparedStatementCreator() {
final String sql = "select count(*) from tb_user where username=? and email=?";
Object count = jdbcTemplate.execute(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection conn)
throws SQLException {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "xkey");
pstmt.setString(2, "*********@gmail.com");
return pstmt;
}
}, new PreparedStatementCallback() {
public Integer doInPreparedStatement(PreparedStatement pstmt)
throws SQLException {
pstmt.execute();
ResultSet rs = pstmt.getResultSet();
rs.next();
int value = rs.getInt(1);
return value;
}
});
System.out.println(count.toString());
}
public void testPreparedStatementSetter() {
String sql = "insert into tb_user(username,email) values(?,?)";
jdbcTemplate.update(sql, new PreparedStatementSetter() {
public void setValues(PreparedStatement pstmt) throws SQLException {
// TODO Auto-generated method stub
pstmt.setString(1, "color");
pstmt.setString(2, "*********@qq.com");
}
});
}
public void insert() {
String sql = "insert into tb_user(username,email) values('xukai','*********@163.com')";
jdbcTemplate.update(sql);
}
public void update() {
String sql = "update tb_user set email='my********@163.com' where userid=?";
jdbcTemplate.update(sql, new Object[] { 2 });
}
public void test() {
String sql = "select * from tb_user";
final List<User> res = new ArrayList<User>();
jdbcTemplate.query(sql, new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
// int id = rs.getInt("userid");
// String value = rs.getString("email");
// System.out.println("Column TABLENAME:" + id + "," + value);
User user = new User();
user.setUserid(rs.getInt("userid"));
user.setUsername(rs.getString("username"));
user.setEmail(rs.getString("email"));
res.add(user);
}
});
for(Iterator it = res.iterator(); it.hasNext();){
User user = (User)it.next();
System.out.println(user.getUsername());
}
List ans = (List)jdbcTemplate.query(sql, new ResultSetExtractor(){
public List extractData(ResultSet rs) throws SQLException,
DataAccessException {
List<User> users = new ArrayList<User>();
while(rs.next()){
User user = new User();
user.setUserid(rs.getInt("userid"));
user.setUsername(rs.getString("username"));
user.setEmail(rs.getString("email"));
users.add(user);
}
return users;
}
});
for(Iterator it = ans.iterator(); it.hasNext();){
User user = (User)it.next();
System.out.println(user.getUserid());
}
List result = jdbcTemplate.query(sql, new RowMapper() {
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User user = new User();
user.setUserid(rs.getInt("userid"));
user.setUsername(rs.getString("username"));
user.setEmail(rs.getString("email"));
return user;
}
});
Iterator it = result.iterator();
while (it.hasNext()) {
User user = (User) it.next();
System.out.println(user.getEmail());
}
System.out.println(result.size());
}
public void testSimpleJdbcTemplate(){
SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(jdbcTemplate);
String sql = "select * from tb_user where username = ?";
List<Map<String, Object>> result = simpleJdbcTemplate.queryForList(sql, "color");
for(Iterator it = result.iterator() ; it.hasNext() ;){
Map user = (Map)it.next();
Iterator itr = user.entrySet().iterator();
while(itr.hasNext()){
Map.Entry val = (Map.Entry)itr.next();
System.out.println(val.getKey()+", "+val.getValue());
}
System.out.println(user.keySet().toString());
System.out.println(user.toString());
}
System.out.println("===================================================");
Map<String,Object> ans = simpleJdbcTemplate.queryForMap(sql, "xkey");
Iterator iter = ans.entrySet().iterator();
while(iter.hasNext()){
Map.Entry val = (Map.Entry) iter.next();
System.out.println(val.getKey()+", "+val.getValue());
}
String sql2 = "select count(*) from tb_user where username = ?";
int count = simpleJdbcTemplate.queryForInt(sql2, "color");
System.out.println(count);
String sql3 = "select * from tb_user where userid=? and username=?";
List<User>users = simpleJdbcTemplate.query(sql3, new ParameterizedRowMapper<User>(){
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User user = new User();
user.setUserid(rs.getInt("userid"));
user.setUsername(rs.getString("username"));
user.setEmail(rs.getString("email"));
return user;
}
}, 1,"xkey");
for(Iterator i = users.iterator() ;i.hasNext();){
User user = (User)i.next();
System.out.println(user.getUserid() + ","+user.getUsername()+","+user.getEmail());
}
}
public static void main(String[] args) {
// TODO Auto-generated method stub
Test t = new Test();
t.setUp();
// t.insert();
// t.test();
// t.testPreparedStatementCreator();
// t.testPreparedStatementSetter();
// t.update();
//t.test();
t.testSimpleJdbcTemplate();
}
}