0727 DBUtils/ Users/UsersDao/TestUsers

drop table Users

create table Users(
	
      userid int primary key DEFAULT NULL,  --员工编号 NOT NULL,
	  username varchar(20) DEFAULT NULL,
	  pwd varchar(20) DEFAULT NULL,
	  relname varchar(20) DEFAULT NULL,
	  email varchar(30) DEFAULT NULL,
	  telephone varchar(20) DEFAULT NULL,
	  birthday date DEFAULT NULL,
	  sex varchar(10) DEFAULT NULL,

)
insert into Users values(1,'AA','1233','Marry','111111@qq.com',16222223333,'1997-05-7','f'),(2,'BB','1244','Mar','1122222@qq.com',16225553333,'1997-12-5','M')
select*from Users

package demo0727;


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

public class DBUtils {
    //jdbc的四个重要参数作为工具类的常量
	//驱动字符串
	public static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	//连接字符串
	public static final String URL = "jdbc:sqlserver://localhost:1433;dataBaseName=db01";
	//用户名
	public static final String USER = "sa";
	//密码
	public static final String PASSWORD = "1234";
	
	//在静态块中加载驱动类
	//在类加载的时候，执行的代码
	static{
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	//获得连接对象的方法
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(URL,USER,PASSWORD);
	}
	
	//关闭资源的方法
	public static void closeAll(ResultSet rs, PreparedStatement prep,Connection conn) throws SQLException{
		    try {
				if(rs!=null){
					  rs.close();
				}
				if(prep!=null){
					 prep.close();
				}
				if(conn!=null){
					 conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw e;//将异常信息继续往上抛，通知调用者
			}
		    
		    
	}
	//测试
	 public static void main(String[] args) throws SQLException {
		     Connection conn = getConnection();
		     System.out.println(conn);
	}
}

package demo0727;

import java.util.Date;

public class Users {
	private Integer userid;		//用户编号
	private String username;	//用户名
	private String pwd;     	//用户密码
	private String relname;		//真实姓名
	private String email;		//邮箱
	private String telephone;	//用户电话
	private Date birthday;		//用户生日
	private String sex;			//用户性别

	public Users() {
		super();
	}

	public Users(Integer userid, String username, String pwd, String relname, String email, String telephone, Date birthday,
			String sex) {
		super();
		this.userid = userid;
		this.username = username;
		this.pwd = pwd;
		this.relname = relname;
		this.email = email;
		this.telephone = telephone;
		this.birthday = birthday;
		this.sex = sex;
	}

	public Integer getUserid() {
		return userid;
	}

	public void setUserid(Integer userid) {
		this.userid = userid;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPwd() {
		return pwd;
	}

	public void setPwd(String pwd) {
		this.pwd = pwd;
	}

	public String getRelname() {
		return relname;
	}

	public void setRelname(String relname) {
		this.relname = relname;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getTelephone() {
		return telephone;
	}

	public void setTelephone(String telephone) {
		this.telephone = telephone;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	@Override
	public String toString() {
		return userid + "\t" + username + "\t" + pwd + "\t" + relname + "\t" + email + "\t" + telephone + "\t" + birthday + "\t" + sex;
	}
}

package demo0727;


import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;



public class UsersDao {
	//封装基础业务逻辑
	// 增
	public int save(Users Users) throws SQLException {
		Connection conn = null;
		PreparedStatement prep = null;
		String sql = "insert into Users values(?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			conn = DBUtils.getConnection();
			prep = conn.prepareStatement(sql);
			prep.setInt(1, Users.getUserid());
			prep.setString(2, Users.getUsername());
			prep.setString(3, Users.getPwd());
			prep.setString(4, Users.getRelname());
			prep.setString(5, Users.getEmail());
			prep.setString(6, Users.getTelephone());
			prep.setDate(7, (Date) Users.getBirthday());
			prep.setString(8, Users.getSex());
			return prep.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.closeAll(null, prep, conn);
		}
	}

	// 删，依据主键删除
	public int delete(int userid) throws SQLException {
		Connection conn = null;
		PreparedStatement prep = null;
		String sql = "delete from Users where userid = ?";
		try {
			  //获得连接对象
			conn = DBUtils.getConnection();
			 //获得预编译对象
			prep = conn.prepareStatement(sql);
			 //设置参数
			prep.setInt(1, userid);
			 //发送预编译文件,执行sql
			return prep.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			// 关闭资源
			DBUtils.closeAll(null, prep, conn);
		}
	}
	

	// 改
	public int modify(Users Users) throws SQLException {
		//声明2个核心接口
		Connection conn = null;
		PreparedStatement prep = null;
		String sql = "update Users set username = ?,pwd = ?, relname = ? , email = ? , telephone = ? ,birthday = ?, sex = ? where userid = ?";
		try {
			//获得连接对象
			conn = DBUtils.getConnection();
			//获得预编译对象
			prep = conn.prepareStatement(sql);
			 //设置参数
			prep.setString(1, Users.getUsername());
			prep.setString(2, Users.getPwd());
			prep.setString(4, Users.getRelname());
			prep.setString(3, Users.getEmail());
			prep.setString(5, Users.getTelephone());
			prep.setDate(6, (Date) Users.getBirthday());
			prep.setString(7, Users.getSex());
			prep.setInt(8, Users.getUserid());
			//发送预编译文件，执行sql
			return prep.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.closeAll(null, prep, conn);
		}
	}

	// 查(最复杂)
	// 查询单个记录
	public Users queryforID(int userid) throws SQLException {
		//声明3个核心接口
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;
		
		Users users = null;
		String sql = "Select * from Users where userid = ?";
		try {
			//获得连接对象
			conn = DBUtils.getConnection();
			//获得预编译对象
			prep = conn.prepareStatement(sql);
			 //设置参数
			prep.setInt(1, userid);
			 //发送预编译文件，执行sql
			 //获得结果集对象
			rs = prep.executeQuery();
			while (rs.next()) {
				users = new Users();
				users.setUserid(rs.getInt("userid"));
				users.setUsername(rs.getString("username"));
				users.setPwd(rs.getString("pwd"));
				users.setRelname(rs.getString("relname"));
				users.setEmail(rs.getString("email"));
				users.setTelephone(rs.getString("telephone"));
				users.setBirthday(rs.getDate("birthday"));
				users.setSex(rs.getString("sex"));
			}
			return users;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.closeAll(rs, prep, conn);
		}
	}

	// 查询所有记录
	public List<Users> queryAll() throws SQLException {
		//声明3个核心接口
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;
		
		List<Users> users = null;
		String sql = "Select * from Users";
		try {
			conn = DBUtils.getConnection();
			prep = conn.prepareStatement(sql);
			rs = prep.executeQuery();
			while (rs.next()) {
				if (users == null) {
					users = new ArrayList<Users>();
				}
				Users user = new Users();
				user = new Users();
				user.setUserid(rs.getInt("userid"));
				user.setUsername(rs.getString("username"));
				user.setPwd(rs.getString("pwd"));
				user.setRelname(rs.getString("relname"));
				user.setEmail(rs.getString("email"));
				user.setTelephone(rs.getString("telephone"));
				user.setBirthday(rs.getDate("birthday"));
				user.setSex(rs.getString("sex"));
				users.add(user);
			}
			return users;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.closeAll(rs, prep, conn);
		}
	}

}

package demo0727;


import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

import org.junit.Test;




public class TestUsers {

	 //测试添加
	@Test
	public void testSave() throws SQLException {
		UsersDao dao = new UsersDao();
		Users u = new Users();
		u.setUserid(3);
		u.setUsername("Eric");
		u.setPwd("1234");
		u.setRelname("张三");
		u.setEmail("12345678@qq.com");
		u.setTelephone("15211112222");
		u.setBirthday(new Date(97,9,11));
		u.setSex("男");
		int num = dao.save(u);
		if (num == 1) {
			System.out.println("添加成功");
		}
	}

	// 测试删除// 测试删除
	 @Test
	 public void testDelete() throws SQLException{
		     //创建Dao对象
		 UsersDao dao = new UsersDao();
		 int num = dao.delete(1);
		 if(num==1){
			  System.out.println("删除成功");
		 }
	 }

	// 测试查询单个
	 @Test
	 public void testQueryForId() throws SQLException{
		 UsersDao dao = new UsersDao();
		 Users u = dao.queryforID(2);
		 System.out.println(u.getUsername());
	 }
	 

	// 测试查询整体
	 @Test
	 public void testQueryAll() throws SQLException{
		 UsersDao dao = new UsersDao();
		    List<Users> Users  =dao.queryAll();
		    for(Users e:Users ){
		    	 System.out.println(e);
		    }
	 }

	// 测试修改
	 @Test
	 public void testModify() throws SQLException{
		UsersDao dao = new UsersDao();
		Users u = dao.queryforID(3);
		u.setPwd("4321");
		u.setUsername("Marry");
		int num = dao.modify(u);
		if (num == 1) {
		System.out.println("修改成功");
			}
	 }
}