jdbc 与 mysql 连接 - 练习

最新推荐文章于 2024-07-24 15:46:40 发布

sunqianLOL

最新推荐文章于 2024-07-24 15:46:40 发布

阅读量114

点赞数

分类专栏： JDBC Mysql 文章标签： mysql jdbc

本文链接：https://blog.csdn.net/sunqianLOL/article/details/120252242

版权

Mysql 同时被 2 个专栏收录

16 篇文章 3 订阅

订阅专栏

JDBC

9 篇文章 0 订阅

订阅专栏

package com.atguigu4.exer;

/*
 * Type:
 * IDCard:
 * ExamCard:
 * StudentName:
 * Location:
 * Grade:
 * 
 */

// 表名和类名可以不同
public class Student {
	
	private int flowID;//流水号
	private int type;//考试类型
	private String IDCard;//身份证号
	private String examCard;//准考证号
	private String name;//学生姓名
	private String location;//所在城市
	private int grade;//成绩
	
	public Student() {
		super();
	}

	public Student(int flowID, int type, String iDCard, String examCard, String name, String location,int grade) {
		super();
		this.flowID = flowID;
		this.type = type;
		IDCard = iDCard;
		this.examCard = examCard;
		this.name = name;
		this.location = location;
		this.grade = grade;
	}

	public int getType() {
		return type;
	}

	public void setType(int type) {
		this.type = type;
	}

	public String getIDCard() {
		return IDCard;
	}

	public void setIDCard(String iDCard) {
		IDCard = iDCard;
	}

	public String getExamCard() {
		return examCard;
	}

	public void setExamCard(String examCard) {
		this.examCard = examCard;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getLocation() {
		return location;
	}

	public void setLocation(String location) {
		this.location = location;
	}

	public int getGrade() {
		return grade;
	}

	public void setGrade(int grade) {
		this.grade = grade;
	}

	public int getFlowID() {
		return flowID;
	}
	// 没有 setFlowID() 不建议修改 流水号

	@Override
	public String toString() {
		System.out.println("==============查询结果==============");
		return info();
	}
	private String info() {
		return "流水号: " + flowID + "\n四级/六级: " + type + "\n身份证号: " + IDCard + "\n准考证号: " + examCard + 
				"\n学生姓名: " + name + "\n区域: " + location + "\n成绩: " + grade;
	}
	
}

package com.atguigu4.exer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;

import org.junit.Test;

import com.atguigu3.util.JDBCUtils;

// 课后练习 1 : 从控制台向数据库的表 customers 中插入一条数据，表结构如下:
// cust_id		cust_name		email		birth		photo

public class Exer1Test {

	@Test
	public void testInsert() {
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入用户名: ");
		String name = scanner.next();
		System.out.println("请输入邮箱: ");
		String email = scanner.next();
		System.out.println("请输入生日: ");
		String birthday = scanner.next();//'1992-09-08'
		
		String sql = "insert into customers(name,email,birth) values(?,?,?)";
		update(sql,name,email,birthday);// 添加的生日格式为:'1992-09-08'
		
		System.out.println("添加成功");
	}
	
	// 通用的 增删改查 操作 (增删改)
	public int update(String sql,Object ...args){	// sql 中的占位符的个数与可变形参的长度相同
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1.获取数据库的连接
			conn = JDBCUtils.getConnection();
			//2.预编译 sql 语句，返回 PreparedStatement 的实例
			ps = conn.prepareStatement(sql);
			//3.填充占位符
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);//小心参数声明错误！
			}
			//4.执行
			/*
			 * ps.execute():
			 * 如果执行的是查询操作，有返回结果，则此方法返回 true;
			 * 如果执行的时增、删、改操作，没有返回结果，则此方法返回 false.
			 */
			// 方式一：
//			return ps.execute();// boolean
			// 方式二：
			return ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//5.资源的关闭
			JDBCUtils.closeResource(conn, ps);
		}
		return 0;
	}
	
}

package com.atguigu4.exer;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

import org.junit.Test;

import com.atguigu3.util.JDBCUtils;
import com.mysql.cj.jdbc.result.ResultSetMetaData;

// 课后练习题2：创立数据库表 examstudent，表结构如下
// 字段名: FlowID、Type、IDCard、ExamCard、StudentName、Location、Grade	
// 向数据表中添加数据：	
// 插入一个新的 student 信息	
public class Exer2Test {
	
	// 问题 1 : 向 examstudent 表中添加一条记录
	/*
	 * Type:
	 * IDCard:
	 * ExamCard:
	 * StudentName:
	 * Location:
	 * Grade:
	 * 
	 */
	@Test
	public void testInsert() {
		
		Scanner scanner = new Scanner(System.in);
		
		System.out.println("四级/六级: ");
		int type = scanner.nextInt();
		System.out.println("身份证号: ");
		String IDCard = scanner.next();
		System.out.println("准考证号: ");
		String examCard = scanner.next();
		System.out.println("学生姓名: ");
		String studentName = scanner.next();
		System.out.println("所在城市: ");
		String location = scanner.next();
		System.out.println("考试成绩: ");
		int grade = scanner.nextInt();
		
		String sql = "insert into examstudent(type,IDCard,examCard,studentName,location,grade)values(?,?,?,?,?,?)";
		int insertCount = update(sql,type,IDCard,examCard,studentName,location,grade);
		if(insertCount > 0) {
			System.out.println("添加成功");
		} else {
			System.out.println("添加失败");
		}
		
	}
	
	
	// 通用的 增删改查 操作 (增删改)
	public int update(String sql,Object ...args){	// sql 中的占位符的个数与可变形参的长度相同
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1.获取数据库的连接
			conn = JDBCUtils.getConnection();
			//2.预编译 sql 语句，返回 PreparedStatement 的实例
			ps = conn.prepareStatement(sql);
			//3.填充占位符
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);//小心参数声明错误！
			}
			//4.执行
			/*
			 * ps.execute():
			 * 如果执行的是查询操作，有返回结果，则此方法返回 true;
			 * 如果执行的时增、删、改操作，没有返回结果，则此方法返回 false.
			 */
			// 方式一：
//				return ps.execute();// boolean
			// 方式二：
			return ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//5.资源的关闭
			JDBCUtils.closeResource(conn, ps);
		}
		return 0;
	}
	
	// 问题 2 : 根据身份证号或者准考证号查询学生成绩信息
	@Test
	public void queryWithIDCardOrExamCard() {
		System.out.println("请选择您要输入的类型:");
		System.out.println("a.准考证号");
		System.out.println("b.身份证号");
		Scanner scanner = new Scanner(System.in);
		String selection = scanner.next();
		if("a".equalsIgnoreCase(selection)) {//if(selection.equalsIgnoreCase("a")) 这种不建议
			System.out.println("请输入准考证号: ");
			String examCard = scanner.next();
			String sql = "select FlowID flowID,Type type,IDCard,ExamCard examCard,StudentName name,Location location,Grade grade from examstudent where examCard = ?";
			
			Student student = getInstance(Student.class,sql,examCard);
			if(student != null) {
				System.out.println(student);
			} else {
				System.out.println("输入的准考证号有误!");
			}
			
		} else if("b".equalsIgnoreCase(selection)) {
			System.out.println("请输入身份证号: ");
			String IDCard = scanner.next();
			String sql = "select FlowID flowID,Type type,IDCard,ExamCard examCard,StudentName name,Location location,Grade grade from examstudent where IDCard = ?";
			
			Student student = getInstance(Student.class,sql,IDCard);
			if(student != null) {
				System.out.println(student);
			} else {
				System.out.println("输入的身份证号有误!");
			}
		} else {
			System.out.println("您的输入有误，请重新进入程序.");
		}
	}
	
	/**
	 * 针对于不同的表的通用的查询操作，返回表中的一条记录
	 * 
	 */
	public <T> T getInstance(Class<T> clazz,String sql,Object...args) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			
			ps = conn.prepareStatement(sql);
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1,args[i]);
			}
			
			rs = ps.executeQuery();
			// 获取结果集的元数据：ResultSetMetaData
			ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
			// 通过 ResultSetMetaData 获取结果集中的列数
			int columnCount = rsmd.getColumnCount();
			
			if(rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集一行数据中的每一个列
				for(int i = 0; i < columnCount; i++) {
					// 获取列值
					Object columValue = rs.getObject(i + 1);	
					
					// 获取每个列的列名
//					String columnName = rsmd.getColumnName(i + 1);
					// 获取每个列的别名
					String columnLabel = rsmd.getColumnLabel(i + 1);
					
					// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, ps, rs);
		}
		return null;
	}
	
	// 问题 3 : 删除指定的学生信息
	@Test
	public void testDeleteByExamCard() {
		System.out.println("请输入学生的考号: ");
		Scanner scanner = new Scanner(System.in);
		String examCard = scanner.next();
		// 查询指定准考证号的学生
		String sql = "select FlowID flowID,Type type,IDCard,ExamCard examCard,StudentName name,Location location,Grade grade from examstudent where IDCard = ?";
		Student student = getInstance(Student.class,sql,examCard);
		if(student == null) {
			System.out.println("查无此人，请重新输入！");
		} else {
			String sql1 = "delete from examstudent where examCard = ?";
			int deleteCount = update(sql1,examCard);
			if(deleteCount > 0) {
				System.out.println("删除成功");
			}
		} 
		
	}
	
	// 问题 3 优化后的操作：
	@Test
	public void testDeleteByExamCard1() {
		System.out.println("请输入学生的考号: ");
		Scanner scanner = new Scanner(System.in);
		String examCard = scanner.next();
		
		String sql = "delete from examstudent where examCard = ?";
		int deleteCount = update(sql,examCard);
		if(deleteCount > 0) {
			System.out.println("删除成功");
		} else {
			System.out.println("查无此人，请重新输入！");
		}
	}
}

sunqianLOL

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
jdbc 与 mysql 连接 - 练习

package com.atguigu4.exer;/* * Type: * IDCard: * ExamCard: * StudentName: * Location: * Grade: * */// 表名和类名可以不同public class Student { private int flowID;//流水号 private int type;//考试类型 private String IDCard;//身份证号 private String examCard;
复制链接

扫一扫

专栏目录