版本:Spring 2.5
数据库: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);
- <span style="white-space:pre"> String sql3 = "select * from tb_user where userid=? and username=?";
- <span style="white-space:pre"> </span>List<User>users = simpleJdbcTemplate.query(sql3, new ParameterizedRowMapper<User>(){
- <span style="white-space:pre"> </span>public User mapRow(ResultSet rs, int arg1) throws SQLException {
- <span style="white-space:pre"> </span>User user = new User();
- <span style="white-space:pre"> </span>user.setUserid(rs.getInt("userid"));
- <span style="white-space:pre"> </span>user.setUsername(rs.getString("username"));
- <span style="white-space:pre"> </span>user.setEmail(rs.getString("email"));
- <span style="white-space:pre"> </span>return user;
- <span style="white-space:pre"> </span>}
- <span style="white-space:pre"> </span>}, 1,"xkey");
- <span style="white-space:pre"> </span>for(Iterator i = users.iterator() ;i.hasNext();){
- <span style="white-space:pre"> </span>User user = (User)i.next();
- <span style="white-space:pre"> </span>System.out.println(user.getUserid() + ","+user.getUsername()+","+user.getEmail());
- <span style="white-space:pre"> </span>}</span>
- }
- 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();
- }
- }