JDBC(后篇)

JDBC(后篇)

点我查看JDBC前篇基础
1.6 使用JDBC完成对数据库的增删改查
步骤:
1.创建javabean封装数据：User
2.创建工具类:JDBCUtils,在开发中提取工具类，是为了简化代码，使用起来简单方便。
3.创建Dao类:UserDao (增删改查方法)
4.创建测试类：
测试添加方法的类:JdbcInsertTest
测试查询所有方法的类:FindAllUserTest
测试查询一个对象的类:FindUserByIdTest
测试查询修改方法的类:UpdateUserTest
测试删除方法的类:DeleteUserTest
----javabean跳转(点我)----
----JDBCUtils跳转(点我)----
----DAO类跳转(点我)----

## User类

package cn.itcast.jdbcExample;

import java.util.Date;

public class User {

private int id;
	private String name;
	private String password;
	private String email;
	private Date birthday;
	public User(int id, String name, String password, String email, Date birthday) {
		super();
		this.id = id;
		this.name = name;
		this.password = password;
		this.email = email;
		this.birthday = birthday;
	}
	public User() {
		super();
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
}

JDBCUtils类

package cn.itcast.jdbcExample;

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

public class JDBCUtils {

	//获取连接对象的方法
	public static Connection getcon() throws Exception{
		//1.注册和加载驱动
		Class.forName("com.mysql.cj.jdbc.Driver");
	    //2.获取连接
		String url = "jdbc:mysql://localhost:3306/jdbc?userSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT";
		String user = "root";
		String password = "123456";
		Connection con=DriverManager.getConnection(url, user, password);
        return con;
	}
	   //关闭连接，释放资源
	public static void realse(ResultSet rs,Statement stmt,Connection con){
		
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

## UserDao类

package cn.itcast.jdbcExample;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/*
 * 完成对数据库的增删改查
 */
public class UsersDao {

	// 1.提供添加方法
	public boolean insert(User user) {
		Connection con = null;
		Statement stmt = null;
		try {
			// 1.获取连接对象
			con = JDBCUtils.getcon();
			// 2.获取执行sql语句的对象
			stmt = con.createStatement();
			// 3.执行sql语句,下面这个麻烦，最好改为预处理语句比较简单
			java.util.Date birthday1 = user.getBirthday();
			java.sql.Date birthday = new java.sql.Date(birthday1.getTime());
			String sql = "insert into users(id,name,password,email,birthday) values('" + user.getId() + "','"
					+ user.getName() + "','" + user.getPassword() + "','" + user.getEmail() + "','" + birthday + "'"
					+ ")";
			// System.out.println(sql);
			int row = stmt.executeUpdate(sql);
			if (row > 0) {
				// 插入成功
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.realse(null, stmt, con);
		}

		return false;
	}

	// 2.提供查询所有的方法
	public List<User> findAllUser() {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<User> list = new ArrayList<User>();
		try {
			// 1.获取连接对象
			con = JDBCUtils.getcon();
			// 2.获取执行sql语句的对象
			stmt = con.createStatement();
			// 3.执行sql
			String sql = "select * from users";
			rs = stmt.executeQuery(sql);
			// 4.遍历结果集
			while (rs.next()) {
				// 一条数据对应一个对象，获取每一行的数据就设置一个User对象
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				java.sql.Date birthday = rs.getDate("birthday");
				user.setBirthday(birthday);
				// 把对象添加到集合中
				list.add(user);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.realse(rs, stmt, con);
		}
		return list;
	}
	//根据id来查询记录
	public User findUserById(int id){
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			// 1.获取连接对象
			con = JDBCUtils.getcon();
			// 2.获取执行sql语句的对象
			String sql = "select * from users where id=?";
			stmt = con.prepareStatement(sql);
			stmt.setInt(1,id);
			// 3.执行sql
			rs = stmt.executeQuery();
			// 4.遍历结果集
			if (rs.next()) {
				// 一条数据对应一个对象，获取每一行的数据就设置一个User对象
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				java.sql.Date birthday = rs.getDate("birthday");
				user.setBirthday(birthday);
				return user;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.realse(rs, stmt, con);
		}
		return null;
	
		
	}
	//4.修改方法，根据id值修改记录
	public boolean update(User user){
		Connection con = null;
		PreparedStatement stmt1=null;
		try {
			// 1.获取连接对象
			con = JDBCUtils.getcon();
			// 2.获取执行sql语句的对象
			String sql ="update users set name=?,password=? where id=?";
			stmt1 = con.prepareStatement(sql);
			stmt1.setString(1, user.getName());
			stmt1.setString(2, user.getPassword());
			stmt1.setInt(3, user.getId());
			int row=stmt1.executeUpdate();
			// System.out.println(sql);
			if (row > 0) {
				// 插入成功
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.realse(null, stmt1, con);
		}

		return false;
	}
	//删除
	public boolean delete(int id){
		Connection con = null;
		PreparedStatement stmt1=null;
		try {
			// 1.获取连接对象
			con = JDBCUtils.getcon();
			// 2.获取执行sql语句的对象
			String sql ="delete from users where id=?";
			stmt1 = con.prepareStatement(sql);
			stmt1.setInt(1, id);
			int row=stmt1.executeUpdate();
			// System.out.println(sql);
			if (row > 0) {
				// 插入成功
				return true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.realse(null, stmt1, con);
		}

		return false;
	}	
}

查询测试类：FindAllUserTest

package cn.itcast.jdbcExample;

import java.util.List;

public class FindAllUserTest {
public static void main(String[] args) {
	
	UsersDao dao=new UsersDao();
	List<User> list=dao.findAllUser();
	System.out.println(list.size());
}
}

集合的长度为5，说明集合里有5个User对象

FindUserByIdTest类

通过id查姓名

package cn.itcast.jdbcExample;

public class FindUserByIdTest {
public static void main(String[] args) {
	UsersDao dao=new UsersDao();
	User u=dao.findUserById(100);
	System.out.println(u.getName());
}
}

更新:UpdateUserTest

package cn.itcast.jdbcExample;

public class UpdateUserTest {
public static void main(String[] args) {
	UsersDao dao=new UsersDao();
	User u=new User();
	u.setId(100);
	u.setName("ss-44");
	u.setPassword("123456");
	boolean flag=dao.update(u);
	if(flag){
	System.out.println("修改成功");
	}
}
}

对比表中的最后一行

删除DeleteUserTest

package cn.itcast.jdbcExample;

public class DeleteUserTest {
public static void main(String[] args) {
	UsersDao dao=new UsersDao();
	boolean flag=dao.delete(100);
	if(flag){
		System.out.println("删除成功");
	}
}
}

对比最后一行，确实删除成功了

终于搞完了。