package com.oaec.dao;
import java.util.List;
import com.oaec.entry.User;
public interface UserDao {
/**
* 根据用户名查询用户
* @param username 表示 要查询的用户名
* @return 1.有此用户名，返回user对象
* 2.没有此用户，返回null
*/
public User queryByUserName(String userName);
/**
* 插入新用户
* @param userName 新用户的用户名
* @param password 新用户的密码
* @return 受影响的行
*/
public int insertUser(String userName,String password);
/**
* 查询所有的用户
* @return 返回所有的用户列表
*/
public List<User> queryAllUser();
/**
* 设置一个用户成为管理员
* @param userName 要成为管理员的用户名
* @return 受影响的行数
*/
public int updateManager(String userName);
/**
* 根据用户名删除用户
* @param userName 被删除的用户名
* @return 1 删除成功 0 没有此用户
*/
public int deleteUser(String userName);
}
先看上述UserDao接口
以下是UserDao的实现类 版本1
package com.oaec.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.oaec.db.util.DBUtil;
import com.oaec.entry.User;
public class UserDaoImpl implements UserDao {
@Override
public User queryByUserName(String userName) {
User user=null;
try {
Connection connection=DBUtil.getConnection();
String sql="select * from user where username=?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setObject(1, userName);
ResultSet resultSet = statement.executeQuery();
while(resultSet.next()){
user=new User();
user.setId(resultSet.getInt("id"));
user.setIsManager(resultSet.getInt("isManager"));
user.setPassword(resultSet.getString("password"));
user.setUsername(resultSet.getString("username"));
}
DBUtil.colse(resultSet, statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return user;
}
@Override
public int insertUser(String userName, String password) {
// TODO Auto-generated method stub
String sql="insert into user (username,password)"
+ " values (?,?)";
int update=0;
try {
Connection connection=DBUtil.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, userName);
statement.setString(2, password);
update=statement.executeUpdate();
DBUtil.colse(statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return update;
}
@Override
public List<User> queryAllUser() {
List<User> list=new ArrayList<User>();
try {
Connection connection=DBUtil.getConnection();
String sql="select * from user";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
while(resultSet.next()){
User user=new User();
user.setId(resultSet.getInt("id"));
user.setIsManager(resultSet.getInt("isManager"));
user.setPassword(resultSet.getString("password"));
user.setUsername(resultSet.getString("username"));
list.add(user);
}
DBUtil.colse(resultSet, statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
@Override
public int updateManager(String userName) {
// TODO Auto-generated method stub
String sql="update user set isManager=1 where username=?";
int update=0;
try {
Connection connection=DBUtil.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, userName);
update=statement.executeUpdate();
DBUtil.colse(statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return update;
}
@Override
public int deleteUser(String userName) {
String sql="delete from user where username=?";
int update=0;
try {
Connection connection=DBUtil.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, userName);
update=statement.executeUpdate();
DBUtil.colse(statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return update;
}
}
现在只是对一个表进行增删改查的操作
例如我们有无数个表，team,Student这些表 ，他们的增删改查的操作也是需要重复上述操作
为了减少我们的代码量，现在对上述代码进行整理
我们分析对数据的操作分为两种 一个是查询，一个是更新
1.查询分为 查询单条数据，查询多条数据 这两个分为两种不同，一个是结果的返回值，一个是数据集的解析，看下面的代码块
2.更新分为：update insert delete 他们都有一个共同点，就是没有结果集，只有一个int类型的返回值
while(resultSet.next()){
User user=new User();
user.setId(resultSet.getInt("id"));
user.setIsManager(resultSet.getInt("isManager"));
user.setPassword(resultSet.getString("password"));
user.setUsername(resultSet.getString("username"));
list.add(user); 多条数据需要添加到集合中
}
user=new User();
user.setId(resultSet.getInt("id"));
user.setIsManager(resultSet.getInt("isManager"));
user.setPassword(resultSet.getString("password"));
user.setUsername(resultSet.getString("username")); 单条数据直接进行user封装就可以了
如果想把查询单挑数据写成一个统一的方法要考虑以下问题：sql语句不同，占位符数量不同，结果返回对象类型不同，针对以上这几个问题考虑
1.sql语句不同，我们知道在版本1 的UserDao中查询不同id对应的User就是将id作为参数，那我们也可以将sql语句作为参数
2.占位符数量不同，类型不同。对于多条数据的存储一般都是放进集合和数组中，那我们可以将占位符值放进数组中，参考下面代码
for(int i=0;i<param.length;i++){
statement.setObject((i+1), param[i]);
}
param是占位符数组，循环遍历 通过setObject设置占位符
针对以上解决方法我们改版UserDaoImpl
版本2：
package com.oaec.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.oaec.db.util.DBUtil;
import com.oaec.entry.User;
import com.oaec.entry.User;
import com.oaec.entry.User;
public class UserDaoImpl implements UserDao {
@Override
public User queryByUserName(String userName) {
String sql = "select * from user where username=?";
return queryOne(sql, new Object[] { userName });
}
@Override
public int insertUser(String userName, String password) {
// TODO Auto-generated method stub
String sql = "insert into user (username,password)" + " values (?,?)";
return update(sql, new Object[]{userName,password});
}
@Override
public List<User> queryAllUser() {
String sql = "select * from user";
return queryMore(sql, null);
}
@Override
public int updateManager(String userName) {
// TODO Auto-generated method stub
String sql = "update user set isManager=1 where username=?";
return update(sql, new Object[]{userName});
}
@Override
public int deleteUser(String userName) {
String sql = "delete from user where username=?";
return update(sql, new Object[]{userName});
}
/**
* 根据id查询 根据name查询
*
* @return
*/
public User queryOne(String sql, Object[] param) {
User User = null;
try {
Connection connection = DBUtil.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
statement.setObject((i + 1), param[i]);
}
}
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
User = selectOne(resultSet);
}
DBUtil.colse(resultSet, statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return User;
}
public List<User> queryMore(String sql, Object[] param) {
List<User> list = new ArrayList<User>();
try {
Connection connection = DBUtil.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
statement.setObject((i + 1), param[i]);
}
}
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
//查询多个和查询单个 ，他们对一行数据的解析是一样的，所以定义成了一个方法
User User = selectOne(resultSet);
list.add(User);
}
DBUtil.colse(resultSet, statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
public int update(String sql, Object[] param) {
int update = 0;
try {
Connection connection = DBUtil.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
//循环遍历参数数组
if (param != null) {
for (int i = 0; i < param.length; i++) {
statement.setObject((i + 1), param[i]);
}
}
update = statement.executeUpdate();
DBUtil.colse(statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return update;
}
/**
* 对一行数据进行处理
*
* @param resultSet
* @return
* @throws SQLException
*/
public User selectOne(ResultSet resultSet) throws SQLException {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setIsManager(resultSet.getInt("isManager"));
user.setPassword(resultSet.getString("password"));
user.setUsername(resultSet.getString("username"));
return user;
}
}
这样以来，我们这个UserDao 算是减少了很多重复代码
那么我们来思考一下，如果我们有多个表呢，例如StudentDao
package com.oaec.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.oaec.db.util.DBUtil;
import com.oaec.entry.Student;
public class StudentDaoImpl implements StudentDao{
@Override
public int insertStudent(String name, String sex) {
//1.sql 2.conn 3.占位符 4.查询 5.结果集的遍历
String sql="insert into student (name,sex) values(?,?)";
try {
Connection conn=DBUtil.getConnection();
PreparedStatement prepareStatement = conn.prepareStatement(sql);
prepareStatement.setObject(1, name);
prepareStatement.setObject(2, sex);
int update = prepareStatement.executeUpdate();
return update;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
}
return 0;
}
/**
* 对一行数据进行处理
* @param resultSet
* @return
* @throws SQLException
*/
public Student selectOne(ResultSet resultSet) throws SQLException{
Student student=new Student();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setSex(resultSet.getString("sex"));
return student;
}
@Override
public Student queryById(int id) {
String sql="select * from student where id=?";
Object[] param={id};
return queryOne(sql, param);
}
@Override
public List<Student> queryAll() {
String sql="select * from student";
return queryMore(sql, null);
}
@Override
public int deleteById(int id) {
// TODO Auto-generated method stub
return 0;
}
@Override
public int updateStudent(Student student) {
String sql="update student set name=?,sex=? where id=?";
return 0;
}
/**
* 根据id查询 根据name查询
* @return
*/
public Student queryOne(String sql,Object[] param){
Student student=null;
try {
Connection connection=DBUtil.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
if(param!=null){
for(int i=0;i<param.length;i++){
statement.setObject((i+1), param[i]);
}
}
ResultSet resultSet = statement.executeQuery();
while(resultSet.next()){
student=selectOne(resultSet);
}
DBUtil.colse(resultSet, statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return student;
}
public List<Student> queryMore(String sql,Object[] param){
List<Student> list=new ArrayList<Student>();
try {
Connection connection=DBUtil.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
if(param!=null){
for(int i=0;i<param.length;i++){
statement.setObject((i+1), param[i]);
}
}
ResultSet resultSet = statement.executeQuery();
while(resultSet.next()){
Student student=selectOne(resultSet);
list.add(student);
}
DBUtil.colse(resultSet, statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
@Override
public List<Student> queryName(String name) {
String sql="select * from student where name like '% ? %'";
Object[] param={name};
return queryMore(sql, param);
}
}
我们可以看到StudentDao要重复去写一遍UserDao中的queryOne queryMore,有没有更好的办法呢
对于两个表来说，两个地方不同
1.返回值，user里面是User类型的返回值，Student，怎么解决？返回值java中有一个超父类啊Object
2.结果集的解析。UserDao 是封装到User中，StudentDao是封装到Student中
解决办法，
谁调用，谁负责解析数据
调用者不确定，怎么办呢，统一一下使用同一个父类或者接口？
我们举例接口的话
import java.sql.ResultSet;
public interface BaseDao {
public Object selectOne(ResultSet resultSet);
}
JDBC模板
package com.oaec.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.oaec.db.util.DBUtil;
import com.oaec.entry.Student;
public class JDBCTemplate {
/**
* 根据id查询 根据name查询
*
* @return
*/
public Object queryOne(String sql, Object[] param, BaseDao baseDao) {
Object obj = null;
try {
Connection connection = DBUtil.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
statement.setObject((i + 1), param[i]);
}
}
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
obj = baseDao.selectOne(resultSet);
}
DBUtil.colse(resultSet, statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return obj;
}
public List<? extends Object> queryMore(String sql, Object[] param, BaseDao baseDao) {
List<Object> list = new ArrayList<Object>();
try {
Connection connection = DBUtil.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
statement.setObject((i + 1), param[i]);
}
}
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
Object obj = baseDao.selectOne(resultSet);
list.add(obj);
}
DBUtil.colse(resultSet, statement);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
使用案例：
package com.oaec.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.oaec.entry.Student;
public class StudentDaoImpl2 implements BaseDao{
@Override
public Object selectOne(ResultSet resultSet) {
Student student=new Student();
try {
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setSex(resultSet.getString("sex"));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return student;
}
public Student queryById(int id){
String sql="select * from Student where id=?";
JDBCTemplate jdbcTemplate=new JDBCTemplate();
BaseDao studentDao=new StudentDaoImpl2();
Student student = (Student) jdbcTemplate.queryOne(sql, new Object[]{id},this);
return student;
}
}
扫一扫
930
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
