package
hvp.spring.jdbc.jdbctemplate;
import hvp.spring.commons.inim.UserPO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
public class UserDAO
{
JdbcTemplate jt;
public UserDAO()
{
this.jt = this.getJdbcTemplate2();
}
public JdbcTemplate getJdbcTemplate2()
{
String configPath = "hvp/spring/jdbc/jdbctemp/beans.xml";
ApplicationContext ctx = new ClassPathXmlApplicationContext(configPath);
return (JdbcTemplate) ctx.getBean("jdbcTemplate");
}
public void createTable()
{
StringBuffer sql = new StringBuffer();
sql.append("CREATE TABLE t_user(");
sql.append("user_id int AUTO_INCREMENT PRIMARY KEY,");
sql.append("user_name VARCHAR(32)");
sql.append(")");
jt.execute(sql.toString());
}
public UserPO addUser(final UserPO po)
{
final String sql = "INSERT INTO t_user(user_name) VALUES (?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jt.update(new PreparedStatementCreator()
{
public PreparedStatement createPreparedStatement(Connection conn)
throws SQLException
{
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, po.getUserName());
return ps;
}
}, keyHolder);
po.setUserId(keyHolder.getKey().intValue());
System.out.println(po.getUserId() + ":" + po.getUserName());
return po;
}
@SuppressWarnings("unchecked")
public List<UserPO> queryUserPOs()
{
StringBuffer sql = new StringBuffer();
sql.append("SELECT user_id,user_name FROM t_user");
sql.append(" LIMIT ");
sql.append(0);
sql.append(",");
sql.append(10);
return jt.query(sql.toString(), new RowMapper()
{
public Object mapRow(ResultSet rs, int index)
throws SQLException
{
UserPO userPO = new UserPO();
userPO.setUserId(rs.getInt("user_id"));
userPO.setUserName(rs.getString("user_name"));
return userPO;
}
});
}
public UserPO findUser(Integer userId)
{
String sql = "SELECT user_id,user_name FROM t_user WHERE user_id=?";
return (UserPO) jt.queryForObject(sql, new Object[] { userId },
new RowMapper()
{
public Object mapRow(ResultSet rs, int index)
throws SQLException
{
UserPO userPO = new UserPO();
userPO.setUserId(rs.getInt("user_id"));
userPO.setUserName(rs.getString("user_name"));
return userPO;
}
});
}
public int getCount()
{
String sql = "SELECT COUNT(*) FROM t_user";
return jt.queryForInt(sql);
}
public static void main(String[] args)
{
UserDAO ct = new UserDAO();
// UserPO user = new UserPO();
// user.setUserName("Huy Vanlee");
// ct.addUser(user);
System.out.println(ct.queryUserPOs());
}
}
import hvp.spring.commons.inim.UserPO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
public class UserDAO
{
JdbcTemplate jt;
public UserDAO()
{
this.jt = this.getJdbcTemplate2();
}
public JdbcTemplate getJdbcTemplate2()
{
String configPath = "hvp/spring/jdbc/jdbctemp/beans.xml";
ApplicationContext ctx = new ClassPathXmlApplicationContext(configPath);
return (JdbcTemplate) ctx.getBean("jdbcTemplate");
}
public void createTable()
{
StringBuffer sql = new StringBuffer();
sql.append("CREATE TABLE t_user(");
sql.append("user_id int AUTO_INCREMENT PRIMARY KEY,");
sql.append("user_name VARCHAR(32)");
sql.append(")");
jt.execute(sql.toString());
}
public UserPO addUser(final UserPO po)
{
final String sql = "INSERT INTO t_user(user_name) VALUES (?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jt.update(new PreparedStatementCreator()
{
public PreparedStatement createPreparedStatement(Connection conn)
throws SQLException
{
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, po.getUserName());
return ps;
}
}, keyHolder);
po.setUserId(keyHolder.getKey().intValue());
System.out.println(po.getUserId() + ":" + po.getUserName());
return po;
}
@SuppressWarnings("unchecked")
public List<UserPO> queryUserPOs()
{
StringBuffer sql = new StringBuffer();
sql.append("SELECT user_id,user_name FROM t_user");
sql.append(" LIMIT ");
sql.append(0);
sql.append(",");
sql.append(10);
return jt.query(sql.toString(), new RowMapper()
{
public Object mapRow(ResultSet rs, int index)
throws SQLException
{
UserPO userPO = new UserPO();
userPO.setUserId(rs.getInt("user_id"));
userPO.setUserName(rs.getString("user_name"));
return userPO;
}
});
}
public UserPO findUser(Integer userId)
{
String sql = "SELECT user_id,user_name FROM t_user WHERE user_id=?";
return (UserPO) jt.queryForObject(sql, new Object[] { userId },
new RowMapper()
{
public Object mapRow(ResultSet rs, int index)
throws SQLException
{
UserPO userPO = new UserPO();
userPO.setUserId(rs.getInt("user_id"));
userPO.setUserName(rs.getString("user_name"));
return userPO;
}
});
}
public int getCount()
{
String sql = "SELECT COUNT(*) FROM t_user";
return jt.queryForInt(sql);
}
public static void main(String[] args)
{
UserDAO ct = new UserDAO();
// UserPO user = new UserPO();
// user.setUserName("Huy Vanlee");
// ct.addUser(user);
System.out.println(ct.queryUserPOs());
}
}