day03总结

day03总结

1. JDBC操作
2. 创建java project，添加mysql数据库驱动包；
3. 封装BaseDao工具类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseDao {
	private Connection conn;
	private String url="jdbc:mysql://127.0.0.1:3306/student2?useUnicode=true&characterEncoding=utf-8";
	private String user="root";
	private String password="123456";	
	
	public Connection getConnection(){
		//通过反射加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
			//获取连接
			conn= DriverManager.getConnection(url,user,password);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	//增删改方法
	public int update(String sql,Object[] object){
		int num=-1;
		PreparedStatement ps=null;
		try {
			//获取连接对象
			conn= getConnection();
			//得到执行SQL对象
			 ps=conn.prepareStatement(sql);
			//替换占位符
			if(object !=null && object.length>0){
				for(int i=0;i<object.length;i++){
			//给占位符赋值
					ps.setObject((i+1),object[i]);
				}
			}
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(conn,ps,null);
		}
		return num;
	}
	//查询
	public ResultSet getResult(String sql,Object[] object){
		ResultSet rs=null;
		try {
			conn=getConnection();
			PreparedStatement ps=conn.prepareStatement(sql);
			if(object!=null && object.length>0){
				for(int i=0;i<object.length;i++){
					ps.setObject((i+1), object[i]);
				}
			}
			rs=ps.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	//釋放資源
	public void close(Connection connection,PreparedStatement ps,ResultSet rs){
		try {
			if(rs!=null){
				rs.close();
			}
			if(ps!=null){
				ps.close();
			}	
			if(connection!=null){
				connection.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
}

4. 创建Dao接口，里面写要实现的相关的CRUD功能（抽象方法）

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.offcn.day03.entity.User;

public interface UserDao {
//查询所有
	List<User>selectByAll();
	//更改
	int updateUser(User user);
	//增加
	int addUpdate(User user);
	//删除
	int deletDate(User user);
	//分页查詢
	List<User> SelectByPage(Integer currentPageNo,Integer pageSize);
}

5. 创建一个表类，里面封装所要操作的数据库的表里的列，并按需求书写构造方法；
6. 创建一个Impl类，该类继承BaseDao类，且实现Dao接口，里面重写Dao接口里的抽象CURD方法，即具体操作的CURD方法。

public class UserDaoImpl extends BaseDao implements UserDao{

	public List<User> selectByAll() {
		//实例化一个集合
		List<User> ulist= new ArrayList<User>();
		
		try {
			String sql="select * from user";
			ResultSet rs=super.getResult(sql,null);
			while(rs.next()){
				User user= new User();
				user.setUid(rs.getInt("uid"));
				user.setUname(rs.getString("uname"));
				user.setUpwd(rs.getString("upwd"));
				ulist.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return ulist;
	}

	public int updateUser(User user){
		String sql="update user set uname=?,upwd=? where uid=?";
		Object[] object={user.getUid(),user.getUname(),user.getUpwd()};
		int num=super.update(sql, object);
		return num;
		
	}	
	public int addUpdate(User user){
	
		String sql="insert into `user`(uname,upwd)values(?,?)";
		Object[] object={user.getUname(),user.getUpwd()};
		int num=super.update(sql, object);
		return num;
		
	}
	
	public int deletDate(User user){
		String sql="delete from `user` where uid=?";
		Object[] object={user.getUid()};
		int num=super.update(sql, object);
		return num;
	}

	@Override
	public List<User> SelectByPage(Integer currentPageNo, Integer pageSize) {
		List<User> ulist=new ArrayList<User>();
		try {
			String sql="select * from `user` limit ?,?";
			Object[] object={(currentPageNo-1)*pageSize,pageSize};
			ResultSet rs=super.getResult(sql, object);
			while(rs.next()){
				User user= new User();
				user.setUid(rs.getInt("uid"));
				user.setUname(rs.getString("uname"));
				user.setUpwd(rs.getString("upwd"));
				ulist.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ulist;
	}
}

今天的难点在于基类BaseDao以及实现类Impl的理解和书写，特别是基类连接数据库，设置占位符以及实际操作是更改占位符等