JDBC连接数据库的详细步骤

JDBC

基本

Insert Delete Add

/**
 * 贾琏欲执事
 * 1.加载驱动 2.获取连接 3.SQL语句 4.执行SQL 5.释放资源
 */

public class add {
	public static void main(String[] args) {
        Date date = new Date();//util转sql date
		// 1.判断驱动程序是否存在
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("驱动不存在");
		}

		Connection conn = null;
        PreparedStatement ps = null;
		// 2.通过驱动管理器来创建数据库连接对象
		String url = "jdbc:mysql://localhost:3306/test2020?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
		String user = "root";
		String password = "123";
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			System.out.println("获取数据库连接对象失败");
		}

		// 3.通过连接对象获取预编译对象
		try {
			String sql = "insert into t_user(uname,password,gender,age,birthday,salary,dept_id) value(?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);

			ps.setString(1, "jack");
			ps.setString(2, "111");
			ps.setString(3, "男");
			ps.setInt(4, 19);
			ps.setDate(5, new java.sql.Date(date.getTime()));
			ps.setDouble(6, 1000);
			ps.setObject(7, null);

			// 4.发送并执行SQL
			int row = ps.executeUpdate();
            //一般使用Update，execute()也能执行
			System.out.println(row);
			System.out.println(row > 0 ? "添加成功" : "添加失败");
		} catch (SQLException e) {
			e.printStackTrace();
			// 5.释放资源
		} finally {
			if (ps != null && ps.isClose) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (conn != null && conn.isClosed()) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

select

public class JDBCSelect {
	public static void main(String[] args) {
		// 1.判断驱动程序是否存在
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("驱动不存在");
		}

		// 2.通过驱动管理器来创建数据库连接对象
		String url = "jdbc:mysql://localhost:3306/test2020?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
		String user = "root";
		String password = "123";
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			System.out.println("获取数据库连接对象失败");
		}
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
		String sql = "select staff_id,staff_name,staff_age,staff_status from staff where staff_id=?";
		try {
			ps = conn.prepareStatement(sql);
            ps.setInt(1, id);//根据id查找数据
			// 执行sql到数据库执行并返回结果集
			rs = ps.executeQuery();
			if(rs.next()) {
				int id = rs.getInt("staff_id");
				String name = rs.getString("staff_name");
				int age = rs.getInt("staff_age");
				String status = rs.getString("staff_status");
				StaffKit s = new StaffKit(id, name, age, status);
				return s;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}	
		}
		return null;
	}
}