使用Eclipse或MyEclipse连接访问数据库

今天药忘吃喽~ 2022-08-03 05:07 287阅读 0赞

使用Eclipse或MyEclipse连接访问数据库MySql的方法相同。

第一步：注册、连接

public final class JdbcUtils {
    
    	private static String url = "jdbc:mysql://localhost/jdbc";
    	private static String user = "root";
    	private static String password = "123456";
    
    	private JdbcUtils() {
    	}// 私有的构造方法，使该类不能在其他地方被实例化
    
    	static {// 静态代码块，类被加载的时候静态代码块被加载
    		try {
    			// 注册驱动
    			Class.forName("com.mysql.jdbc.Driver");
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		}
    	}
    
    	// 获得连接
    	public static Connection getConnection() {
    
    		Connection conn = null;
    
    		try {
    			conn = DriverManager.getConnection(url, user, password);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    
    		return conn;
    	}
    
    	// 释放资源
    	public static void free(ResultSet rs, Statement stmt, Connection conn) {
    
    		try {
    			if (rs != null) {
    				rs.close();
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			try {
    				if (stmt != null) {
    					stmt.close();
    				}
    			} catch (SQLException e) {
    				e.printStackTrace();
    			} finally {
    				try {
    					if (conn != null) {
    						conn.close();
    					}
    				} catch (SQLException e) {
    					e.printStackTrace();
    				}
    			}
    		}
    	}
    }

第二步：创建实体类domain

public class User {
    
    	private int id;
    	private String name;
    	private Date birthday;
    	private double money;
    
    	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 Date getBirthday() {
    		return birthday;
    	}
    
    	public void setBirthday(Date birthday) {
    		this.birthday = birthday;
    	}
    
    	public double getMoney() {
    		return money;
    	}
    
    	public void setMoney(double money) {
    		this.money = money;
    	}
    
    	public String toString() {
    
    		return "学号=" + id + "\t姓名=" + name + "\t生日=" + birthday + "\t工资="
    				+ money;
    	}
    }

第三步：创建domain类的Dao接口

public interface UserDao {
    
    	ArrayList getAllUsers();
    
    	User getUserById(int id);
    
    	void create(User u);
    
    	void update(User u);
    
    	void delete(User u);
    }

第四步：编写Dao接口的实现类

public class UserDaoJdbcImpl implements UserDao {
    
    	public void create(User u) {
    
    		Connection conn = null;
    		PreparedStatement ps = null;
    		ResultSet rs = null;
    
    		try {
    			conn = JdbcUtils.getConnection();
    
    			String sql = "insert into user (name, birthday, money) values (?, ?, ?)";
    
    			ps = conn.prepareStatement(sql);
    
    			ps.setString(1, u.getName());
    			ps.setDate(2, new java.sql.Date(u.getBirthday().getTime()));
    			ps.setDouble(3, u.getMoney());
    
    			int i = ps.executeUpdate();
    
    			System.out.println("成功向user表中插入" + i + "条记录");
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			JdbcUtils.free(rs, ps, conn);
    		}
    	}
    
    	public void delete(User u) {
    
    		Connection conn = null;
    		PreparedStatement ps = null;
    		ResultSet rs = null;
    
    		try {
    			conn = JdbcUtils.getConnection();
    
    			String sql = "delete from user where name = ?";
    
    			ps = conn.prepareStatement(sql);
    
    			ps.setString(1, u.getName());
    
    			int i = ps.executeUpdate();
    
    			System.out.println("成功向user表中删除" + i + "条记录");
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			JdbcUtils.free(rs, ps, conn);
    		}
    	}
    
    	public ArrayList getAllUsers() {
    
    		Connection conn = null;
    		PreparedStatement ps = null;
    		ResultSet rs = null;
    
    		ArrayList allUsers = new ArrayList();
    		User user = null;
    
    		try {
    			conn = JdbcUtils.getConnection();
    
    			String sql = "select * from user";
    
    			ps = conn.prepareStatement(sql);
    
    			rs = ps.executeQuery();
    
    			while (rs.next()) {
    				user = new User();
    
    				user.setId(rs.getInt("id"));
    				user.setName(rs.getString("name"));
    				user.setBirthday(rs.getDate("birthday"));
    				user.setMoney(rs.getDouble("money"));
    
    				allUsers.add(user);
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			JdbcUtils.free(rs, ps, conn);
    		}
    		return allUsers;
    	}
    
    	public void update(User u) {
    
    		Connection conn = null;
    		PreparedStatement ps = null;
    		ResultSet rs = null;
    
    		try {
    			conn = JdbcUtils.getConnection();
    
    			String sql = "update user set name = ?, birthday = ?, money = ? where id=?";
    
    			ps = conn.prepareStatement(sql);
    
    			User user = getUserById(u.getId());
    			if (u.getName() == null) {
    				u.setName(user.getName());
    			}
    			if (u.getBirthday() == null) {
    				u.setBirthday(user.getBirthday());
    			}
    			if (u.getMoney() == 0) {
    				u.setMoney(user.getMoney());
    			}
    
    			ps.setString(1, u.getName());
    			ps.setDate(2, new java.sql.Date(u.getBirthday().getTime()));
    			ps.setDouble(3, u.getMoney());
    			ps.setInt(4, u.getId());
    
    			int i = ps.executeUpdate();
    
    			System.out.println("成功向user表中更新" + i + "条记录");
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			JdbcUtils.free(rs, ps, conn);
    		}
    	}
    
    	public User getUserById(int id) {
    
    		Connection conn = null;
    		PreparedStatement ps = null;
    		ResultSet rs = null;
    
    		User user = null;
    
    		try {
    			conn = JdbcUtils.getConnection();
    
    			String sql = "select * from user where id = ?";
    
    			ps = conn.prepareStatement(sql);
    
    			ps.setInt(1, id);
    
    			rs = ps.executeQuery();
    
    			if (rs.next()) {
    				user = new User();
    
    				user.setId(rs.getInt("id"));
    				user.setName(rs.getString("name"));
    				user.setBirthday(rs.getDate("birthday"));
    				user.setMoney(rs.getDouble("money"));
    
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			JdbcUtils.free(rs, ps, conn);
    		}
    
    		return user;
    	}
    
    }

第五步：编写Dao测试类

public class UserDaoTest {
    
    	public static void main(String[] args) {
    
    		// create();
    
    		// update();
    
    		// delete();
    
    		// getAllUsers();
    
    		getUserById();
    	}
    
    	static void getAllUsers() {
    
    		UserDao ud = new UserDaoJdbcImpl();
    
    		ArrayList allUsers = ud.getAllUsers();
    
    		for (User u : allUsers) {
    			System.out.println(u);
    		}
    	}
    
    	static void create() {
    
    		UserDao ud = new UserDaoJdbcImpl();
    
    		User user = new User();
    
    		user.setName("老师");
    
    		Date d = null;
    		try {
    			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    			d = sdf.parse("1999-2-3");
    		} catch (ParseException e) {
    			e.printStackTrace();
    		}
    
    		user.setBirthday(d);
    		user.setMoney(123);
    
    		ud.create(user);
    
    	}
    
    	static void update() {
    
    		UserDao ud = new UserDaoJdbcImpl();
    
    		User user = new User();
    
    		user.setId(9);
    		user.setName("老师");
    
    		Date d = null;
    		try {
    			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    			d = sdf.parse("1999-9-14");
    		} catch (ParseException e) {
    			e.printStackTrace();
    		}
    
    		user.setBirthday(d);
    		user.setMoney(1234);
    
    		ud.update(user);
    	}
    
    	static void delete() {
    
    		UserDao ud = new UserDaoJdbcImpl();
    
    		User user = new User();
    
    		user.setName("老师");
    
    		ud.delete(user);
    	}
    
    	static void getUserById() {
    
    		UserDao ud = new UserDaoJdbcImpl();
    
    		User user = ud.getUserById(9);
    		System.out.println(user);
    	}
    
    }