连接mysql数据库的基本方法和框架（一）

最基础的方法——不加任何封装和任何框架，直接使用JDBC

前往MYSQL官网下载对应的压缩包，使用eclipse作为开发工具，选择Operating System中的Platform Independent选项，zip格式的压缩文件，Product Version中的8.0.33版本(最好选择最新版本)

 将压缩文件中的jar包导入项目文件中

注意先复制该jar包到项目lib目录下

右键项目根目录，选择Build Path, Configure Build Path，移除在classpath目录下原有的Web App Libraries（若没有则直接添加新的Web App Libraries），再添加新的包含该jar包的Web App Libraries

在dao层使用JDBC并简单应用

package com.fan.jdbc.pojo;

public class User {
	private int uid;
	private String name;
	private String pwd;
	private String sex;
	private int age;
	
	public int getUid() {
		return uid;
	}
	public void setUid(int uid) {
		this.uid = uid;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	
	public User(int uid, String name, String pwd, String sex, int age) {
		super();
		this.uid = uid;
		this.name = name;
		this.pwd = pwd;
		this.sex = sex;
		this.age = age;
	}
	
	
}

package com.fan.jdbc.dao;

import java.util.List;

import com.fan.jdbc.pojo.User;

public interface UserDao {
	/**
	 * 查询所有用户信息
	 * @return
	 * */
	public List<User> findAll();
	
	
	//public void queryById(int id);
	
	//public void add();
	
	//public void update();
	
	//public void delete(int id);
}

package com.fan.jdbc.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.fan.jdbc.dao.UserDao;
import com.fan.jdbc.pojo.User;

public class UserDaoImpl implements UserDao {
	private static final String driver = "com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://localhost:3306/school";
	private static final String user = "root";
	private static final String password = "123456";
	

	public List<User> findAll() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<User> list = null;
		list = new ArrayList<User>();
		
		try {
			Class.forName(driver);
			System.out.println("注册JDBC驱动...");
			
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("连接数据库...");
			
			stmt = conn.createStatement();
			System.out.println("实例化statement对象...");
			
			String sql = null;
			sql = "SELECT * FROM user";
			rs = stmt.executeQuery(sql);
			System.out.println("执行SQL语句...");
			
			System.out.println("输出查询结果...");
			while(rs.next()) {
				int uid = 0;
				int age = 0;
				String name = null;
				String pwd = null;
				String sex = null;
				
				uid = rs.getInt("uid");
				age = rs.getInt("age");
				name = rs.getString("name");
				pwd = rs.getString("pwd");
				sex = rs.getString("sex");
				
				User user = null;
				user = new User(uid, name, pwd, sex, age);

				list.add(user);	
				
				//System.out.println(list.get(0).getName());
			}
			
			System.out.println("关闭连接...");
			rs.close();
			stmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
		}
		return list;
	}
	
	public void update() {
		
	}
	
}

package com.fan.jdbc.test;

import java.util.List;

import com.fan.jdbc.dao.impl.UserDaoImpl;
import com.fan.jdbc.pojo.User;

public class DaoTest {

	public static void main(String[] args) {
		UserDaoImpl UD = new UserDaoImpl();
		
		List<User> list = null;
		list = UD.findAll();
		
		for(int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i).getName());
		}
		
	}

}

所用数据库文件（使用navicat，右击所用的数据库根目录选择转储sql文件，选择结构和数据即可）

 Source Server         : test
 Source Server Type    : MySQL
 Source Server Version : 80033 (8.0.33)
 Source Host           : localhost:3306
 Source Schema         : school

 Target Server Type    : MySQL
 Target Server Version : 80033 (8.0.33)
 File Encoding         : 65001

 Date: 15/09/2023 23:46:36
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `uid` int NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `pwd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int NULL DEFAULT NULL,
  PRIMARY KEY (`uid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '猫猫', '123', '女', 3);
INSERT INTO `user` VALUES (2, '狗狗', '456', '男', 5);
INSERT INTO `user` VALUES (3, '兔兔', '789', '女', 2);
INSERT INTO `user` VALUES (4, '虎虎', '246', '男', 9);
INSERT INTO `user` VALUES (5, '猪猪', '468', '男', 3);

SET FOREIGN_KEY_CHECKS = 1;