BaseDao
package shop.dao;
//包
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
//基类：数据库操作通用类
public class BaseDao {
protected Connection conn;
protected PreparedStatement ps;
protected Statement stmt;
protected ResultSet rs;
// 获取数据库连接
public boolean getConnection() {
// 读出配置信息
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
String username ="t03";
String password = "bdqn";
// 加载JDBC驱动
try {
Class.forName(driver);
// 与数据库建立连接
conn = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
return true;
}
// 获取数据库连接
public Connection getConnection2() {
try {
// 初始化上下文
Context cxt = new InitialContext();
// 获取与逻辑名相关联的数据源对象
DataSource ds = (DataSource) cxt.lookup("java:comp/env/jdbc/news");
conn = ds.getConnection();
} catch (NamingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
// 增删改
public int executeUpdate(String sql, Object[] params) {
int updateRows = 0;
getConnection();
try {
ps = conn.prepareStatement(sql);
// 填充占位符
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
updateRows = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return updateRows;
}
// 查询
public ResultSet executeSQL(String sql, Object[] params) {
getConnection();
try {
ps = conn.prepareStatement(sql);
// 填充占位符
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
rs = ps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
// 关闭资源
public boolean close1() {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
return true;
}
}
UserDaoImpl
package shop.dao.impl;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import shop.dao.BaseDao;
import shop.dao.UserDao;
import shop.entity.User;
public class UserDaoImpl extends BaseDao implements UserDao {
@Override
public boolean addUser(User user) {
boolean flag = false;
try {
String sql = "insert into SMBMS_USER(userId,name ,loginName ,gender ,birthdate,phone ,userType,loginPwd,addree) " +
"VALUES(?,?,?,?,?,?,?,?,?)";
Object[] paras = {user.getUserId(),user.getName(),user.getLoginName(),user.getGender(),user.getBirthdate(),user.getPhone(),user.getUserType(),user.getLoginPwd(),user.getAddress()};
int i = this.executeUpdate(sql, paras);
if (i > 0) {
System.out.println("插入成功！！！");
flag = true;
}
} finally {
this.close1();
}
return flag;
}
@Override
public boolean delUser(User user) {
boolean flag = false;
try {
String sql="delete from smbms_user where userId=?";
Object[] paras={user.getUserId()};
int i=this.executeUpdate(sql, paras);
if (i>0) {
System.out.println("删除成功！！！");
flag=true;
}
} finally {
this.close1();
}
return flag;
}
@Override
public boolean updateUser(User user) {
boolean flag = false;
try {
String sql = "update SMBMS_USER set name=?,loginName=?,gender=?,birthdate=?,phone=?,userType=?,loginPwd=?,addree=? where userId=? ";
Object[] paras = {user.getName(),user.getLoginName(),user.getGender(),user.getBirthdate(),user.getPhone(),user.getUserType(),user.getLoginPwd(),user.getAddress(),user.getUserId()};
int i = this.executeUpdate(sql, paras);
if (i > 0) {
System.out.println("修改成功！！！");
flag = true;
}
} finally {
this.close1();
}
return flag;
}
@Override
public List<User> getUsers(String name1) {
List<User> userList = new ArrayList<User>();
try {
String sql = "select userId ,u.name ,u.loginName ,u.gender ,u.birthdate,u.phone ,u.userType,u.addree,u.loginPwd from smbms_user u where u.name like ?";
//String sql ="select u.* from smbms_user u";
Object[] paras = {"%"+name1+"%"};
ResultSet rs = this.executeSQL(sql, paras);
while (rs.next()) {
// 在数据库中取值
int userid = rs.getInt("userId");
String name = rs.getString("name");
String loginName = rs.getString("loginName");
int gender = rs.getInt("gender");
Date birthDate = rs.getDate("birthdate");
String phone = rs.getString("phone");
int userType = rs.getInt("userType");
String loginPwd=rs.getString("loginPwd");
String address=rs.getString("addree");
// 给user赋值
User user = new User();
user.setUserId(userid);
user.setName(name);
user.setLoginName(loginName);
user.setGender(gender);
user.setBirthdate(birthDate);
user.setPhone(phone);
user.setUserType(userType);
user.setAddress(address);
user.setLoginPwd(loginPwd);
//System.out.println(userid+"--"+user.getLoginName()+"--"+user.getQuanXian()+"--"+name+"--"+birthDate+"--"+phone+"--"+user.getMygender()+"--"+user.getAddress()+"--"+user.getLoginPwd());
// 添加user到userList集合
userList.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.close1();
}
return userList;
}
@Override
public User getuser(int userId) {
User user = null;
try {
String sql = "select userId ,u.name ,u.loginName ,u.gender ,u.birthdate,u.phone ,u.userType,u.addree,u.loginPwd from smbms_user u where u.userId=?";
Object[] paras = {userId};
ResultSet rs = this.executeSQL(sql, paras);
while (rs.next()) {
// 在数据库中取值
String name = rs.getString("name");
String loginName = rs.getString("loginName");
int gender = rs.getInt("gender");
Date birthDate = rs.getDate("birthdate");
String phone = rs.getString("phone");
int userType = rs.getInt("userType");
String loginPwd=rs.getString("loginPwd");
String address=rs.getString("addree");
// 给user赋值
user = new User();
user.setUserId(userId);
user.setName(name);
user.setLoginName(loginName);
user.setGender(gender);
user.setBirthdate(birthDate);
user.setPhone(phone);
user.setUserType(userType);
user.setAddress(address);
user.setLoginPwd(loginPwd);
//System.out.println(userid+"--"+user.getLoginName()+"--"+user.getQuanXian()+"--"+name+"--"+birthDate+"--"+phone+"--"+user.getMygender()+"--"+user.getAddress()+"--"+user.getLoginPwd());
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.close1();
}
return user;
}
}