DB.java
package com.sun.shopping.util;
import java.sql.*;
public class DB {
static{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private DB(){}
public static Connection getConn(){
Connection conn=null;
try {
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/shopping?user=root&password=sun");
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static Statement getStmt(Connection conn){
Statement stmt=null;
try {
stmt=conn.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
return stmt;
}
public static PreparedStatement getPstmt(Connection conn,String sql){
PreparedStatement pStmt=null;
try {
pStmt=conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pStmt;
}
public static void closeConn(Connection conn){
if(conn!=null){
try {
conn.close();
conn=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeStmt(Statement stmt){
try {
if(stmt!=null){
stmt.close();
stmt=null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void closePstmt(PreparedStatement pstmt){
try {
if(pstmt!=null){
pstmt.close();
pstmt=null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static ResultSet executeQuery(Statement stmt,String sql){
ResultSet rs=null;
try {
rs=stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public static void closeResultSet(ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//User.java
package com.sunshopping;
import java.sql.*;
import com.sunyuan.shopping.util.DB;
public class User {
private int id;
private String username;
private String password;
private String phone;
private String addr;
private Timestamp rdate;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
public Timestamp getRdate() {
return rdate;
}
public void setRdate(Timestamp rdate) {
this.rdate = rdate;
}
public void save(){
Connection conn=null;
PreparedStatement pstmt=null;
try {
conn=DB.getConn();
//Statement stmt=DB.getStmt(conn);
String sql="insert into user values(null,?,?,?,?,?)";
pstmt=DB.getPstmt(conn, sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
pstmt.setString(3,phone);
pstmt.setString(4,addr);
pstmt.setTimestamp(5,rdate);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DB.closePstmt(pstmt);
//DB.closeStmt(pstmt);//closeStmt()是closePstmt()的弗雷方法
DB.closeConn(conn);
}
}
}
//UserManager.java
package com.sun.shopping;
import java.util.*;
import java.sql.*;
import com.sunyuan.shopping.util.*;
public class UserManager {
public static List<User>getUsers() throws SQLException{
List<User>list=new ArrayList();
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
try {
conn=DB.getConn();
stmt=DB.getStmt(conn);
rs=stmt.executeQuery("select * from user order by 'id' desc");
while(rs.next()){
User u=new User();
u.setId(rs.getInt("id"));
u.setUsername(rs.getString("username"));
u.setPassword(rs.getString("password"));
u.setPhone(rs.getString("phone"));
u.setRdate(rs.getTimestamp("rdate"));
u.setAddr(rs.getString("addr"));
list.add(u);
}
} catch (RuntimeException e) {
e.printStackTrace();
}finally{
DB.closeResultSet(rs);
DB.closeStmt(stmt);
DB.closeConn(conn);
}
return list;
}
}
数据库这样的封装,可以后期的修改和维护!方便!
装数据库操作,目的就是为了隐藏Java.sql包内的类,在编码中去掉核心的数据库操作代码。以杜绝直接数据库操作轻易带来的资源未释放问题。同时也减少了数据库操作的编码量。
但是很多网友在封装时,却喜欢返回结果集(ResultSet对象),那么这个封装就没有意义了。
1. 又是直接操作核心数据库类,跟封装前几乎没什么变化。
2. 结果集总是依靠于它使用的连接(Connection)对象。因此当连接对象在方法内被关闭后,你返回的ResultSet就没有用了。
假如真的要获得查询数据库的结果集,就把结果集对象内的所有数据,转储到以Map为元素的List对象内。
当然,这种方式,不能适应大数据量的查询,不过假如真的碰到大数据量的查询,那用什么封装都不好,还是得直接数据库操作。