步骤1、编写xml配置文件
<?xml version="1.0" encoding="utf-8" ?>
<!--
数据库相关信息: 1、驱动;2、url;3、user;4、password
-->
<config>
<db-info>
<driver-name>com.mysql.jdbc.Driver</driver-name>
<url>jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8</url>
<user-name>root</user-name>
<password>root</password>
</db-info>
</config>
步骤2、编写实体类JdbcConfig
package net.etwo.model;
public class JdbcConfig {
/**
* 数据库相关信息
* 1、驱动;2、url;3、user;4、password
*/
private String driverName;
private String url;
private String user;
private String password;
public String getDriverName() {
return driverName;
}
public void setDriverName(String driverName) {
this.driverName = driverName;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return "driverName:" + this.driverName + "\nurl:" + this.url + "\nuser:" + this.user + "\npassword:" + this.password;
}
}
3、创建DbUtil类,封装获取连接、关闭连接等操作。
public class DbUtil {
/**
* 取得Connection
* @return
*/
public static Connection getConnection() {
Connection conn = null;
try {
JdbcConfig jdbcConfig = XmlConfigReader.getInstance().getJdbcConfig();
Class.forName(jdbcConfig.getDriverName());
conn = DriverManager.getConnection(jdbcConfig.getUrl(), jdbcConfig.getUserName(), jdbcConfig.getPassword());
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement pstmt) {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs ) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//测试连接是否成功
public static void main(String[] args) {
System.out.println(DbUtil.getConnection());
}
}
4、采用单例模式建立Manager类进行JDBC操作。
public class UserManager {
private static UserManager instance = new UserManager();
private UserManager() {}
public static UserManager getInstance() {
return instance;
}
/**
* 添加用户
* @param user
*/
public void addUser(User user) {
String sql = "insert into t_user (user_id, user_name, password, contact_tel, email, create_date) " +
" values (?, ?, ?, ?, ?, ?)";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getUserId());
pstmt.setString(2, user.getUserName());
pstmt.setString(3, user.getPassword());
pstmt.setString(4, user.getContactTel());
pstmt.setString(5, user.getEmail());
//pstmt.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
pstmt.setTimestamp(6, new Timestamp(new Date().getTime()));
pstmt.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
DbUtil.close(pstmt);
DbUtil.close(conn);
}
}
/**
* 根据用户代码查询
* @param userId
* @return 如果存在返回User对象,否则返回null
*/
public User findUserById(String userId) {
System.out.println("UserManager.findUserById() -- userId=" + userId);
String sql = "select user_id, user_name, password, contact_tel, email, create_date from t_user where user_id=?";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
User user = null;
try {
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userId);
rs = pstmt.executeQuery();
if (rs.next()) {
user = new User();
user.setUserId(rs.getString("user_id"));
user.setUserName(rs.getString("user_name"));
user.setPassword(rs.getString("password"));
user.setContactTel(rs.getString("contact_tel"));
user.setEmail(rs.getString("email"));
user.setCreateDate(rs.getTimestamp("create_date"));
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
DbUtil.close(rs);
DbUtil.close(pstmt);
DbUtil.close(conn);
}
return user;
}
/**
* 分页查询
* @param pageNo 第几页
* @param pageSize 每页多少条数据
* @return pageModel
*/
public PageModel<User> findUserList(int pageNo, int pageSize) {
StringBuffer sbSql = new StringBuffer();
sbSql.append("select user_id, user_name, password, contact_tel, email, create_date ")
.append("from ")
.append("( ")
.append("select rownum rn, user_id, user_name, password, contact_tel, email, create_date ")
.append("from ")
.append("( ")
.append("select user_id, user_name, password, contact_tel, email, create_date from t_user where user_id <> 'root' order by user_id ")
.append(") where rownum <= ? ")
.append(") where rn > ? ");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
PageModel<User> pageModel = null;
try {
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sbSql.toString());
pstmt.setInt(1, pageNo * pageSize);
pstmt.setInt(2, (pageNo - 1) * pageSize);
rs = pstmt.executeQuery();
List<User> userList = new ArrayList<User>();
while (rs.next()) {
User user = new User();
user.setUserId(rs.getString("user_id"));
user.setUserName(rs.getString("user_name"));
user.setPassword(rs.getString("password"));
user.setContactTel(rs.getString("contact_tel"));
user.setEmail(rs.getString("email"));
user.setCreateDate(rs.getTimestamp("create_date"));
userList.add(user);
}
pageModel = new PageModel<User>();
pageModel.setList(userList);
pageModel.setTotalRecords(getTotalRecords(conn));
pageModel.setPageSize(pageSize);
pageModel.setPageNo(pageNo);
}catch(SQLException e) {
e.printStackTrace();
}finally {
DbUtil.close(rs);
DbUtil.close(pstmt);
DbUtil.close(conn);
}
return pageModel;
}
/**
* 取得总记录数
* @param conn
* @return
*/
private int getTotalRecords(Connection conn)
throws SQLException {
String sql = "select count(*) from t_user where user_id <> 'root'";
PreparedStatement pstmt = null;
ResultSet rs = null;
int count = 0;
try {
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
rs.next();
count = rs.getInt(1);
}finally {
DbUtil.close(rs);
DbUtil.close(pstmt);
}
return count;
}
/**
* 修改用户
* @param user
*/
public void modifyUser(User user) {
StringBuilder sbSql = new StringBuilder();
sbSql.append("update t_user ")
.append("set user_name = ?, ")
.append("password = ?, ")
.append("contact_tel = ?, ")
.append("email = ? ")
.append("where user_id = ? ");
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sbSql.toString());
pstmt.setString(1, user.getUserName());
pstmt.setString(2, user.getPassword());
pstmt.setString(3, user.getContactTel());
pstmt.setString(4, user.getEmail());
pstmt.setString(5, user.getUserId());
pstmt.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
DbUtil.close(pstmt);
DbUtil.close(conn);
}
}
/**
* 根据用户代码删除
* @param userId
*/
public void delUser(String userId) {
String sql = "delete from t_user where user_id=?";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userId);
pstmt.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
DbUtil.close(pstmt);
DbUtil.close(conn);
}
}
}