jdbc操作mysql数据库——面对初学者的的简单实例

任务一:在MySQL中创建一个tb_student表，要求有学号、姓名、性别、年龄、出生日期等字

任务二:使用JDBC查询tb_student表的所有内容.

任务三:使用JDBC分别完成数据的插入、修改、删除

1.首先创建一张表  tb_student   加入一些信息

示例中我建到了spj数据库下 

/*
 Navicat Premium Data Transfer

 Source Server         : 本地
 Source Server Type    : MySQL
 Source Server Version : 50639
 Source Host           : localhost:3306
 Source Schema         : spj

 Target Server Type    : MySQL
 Target Server Version : 50639
 File Encoding         : 65001

 Date: 02/06/2022 19:02:17
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_student
-- ----------------------------
DROP TABLE IF EXISTS `tb_student`;
CREATE TABLE `tb_student`  (
  `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sex` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(4) NULL DEFAULT NULL,
  `birthday` date NULL DEFAULT NULL,
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of tb_student
-- ----------------------------
INSERT INTO `tb_student` VALUES ('1', '小王', '男', 18, '2022-06-02');
INSERT INTO `tb_student` VALUES ('2', '小花', '女', 20, '2022-02-01');

SET FOREIGN_KEY_CHECKS = 1;

2.写JDBC工具类

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

public class JDBCUtils {
	/*
	 * 这个类用来提供jdbc的工具类
	 */
	// 私有化构造函数 ,外界无法创建对象
	private JDBCUtils() {
	}
//终极优化:只加载一次属性文件
	static ResourceBundle rb=null;
	static{
		 rb = ResourceBundle.getBundle("jdbc");	
	}
	// 2.方法提供数据库连接
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(rb.getString("DriverClass"));
			String url = rb.getString("jdbcUrl");
			String user = rb.getString("user");
			String password = rb.getString("password");
			conn = DriverManager.getConnection(url, user, password);
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;

	}

	// 释放资源
	public static void close(ResultSet rs, Statement st, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				rs = null;
			}
		}
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				st = null;
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				conn = null;
			}
		}
	}
}

3.配置属性jdbc链接属性文件

在java项目根目录下创建一个文件file    命名为jdbc.properties  即作为链接配置文件

分别写入如下配置

驱动   如 com.mysql.jdbc.Driver   mysql5.7版本以后是com.mysql.cj.jdbc.Driver

数据库名  如spj

用户名         

密码

DriverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql:///SPJ
user=root
password =123456

4.下载jdbc驱动jar包  添加到项目中

 

 

5.写一个实体类 student映射数据库相关属性 并测试

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
class Student{
	private String sId;
	private String sName;
	private char sex;
	private int age;
	private Date birth;
	public String getsId() {
		return sId;
	}
	public void setsId(String sId) {
		this.sId = sId;
	}
	public String getsName() {
		return sName;
	}
	public void setsName(String sName) {
		this.sName = sName;
	}
	public char getSex() {
		return sex;
	}
	public void setSex(char sex) {
		this.sex = sex;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public Date getBirth() {
		return birth;
	}
	public void setBirth(Date birth) {
		this.birth = birth;
	}
	@Override
	public String toString() {
		return "\n学号:"+sId+"\n姓名："+sName+"\n年龄："+age+"\n性别"+sex+"\n生日"+birth+"\n";
	}
	
}
public class Answer7733622 {
	public static void main(String[] args) throws SQLException {
		
		selectAllAtudent();
		//insertStudentInfo();
		//updateStudentInfo(sid);
		//deleteStudentInfo(sid);
		
	}

	public static void selectAllAtudent() throws SQLException {
		String sql="select * from tb_student";
		ArrayList<Object> list = new ArrayList<>();
		Connection conn = JDBCUtils.getConnection();
		PreparedStatement pst = conn.prepareStatement(sql);
		ResultSet result = pst.executeQuery();
		while(result.next()){
			Student stu = new Student();
			stu.setsId(result.getString("s_id"));
			stu.setsName(result.getString("s_name"));
			stu.setSex(result.getString("sex").charAt(0));
			stu.setAge(result.getInt("age"));
			stu.setBirth(result.getDate("birthday"));
			list.add(stu);
		}
		System.out.println(list);
	}
	
	
}

6.测试结果