通过JDBC连接数据库再向数据库里录入数据

╰+攻爆jí腚メ 2022-05-06 06:56 206阅读 0赞

一 首先第一步我们现在数据库里创建一张examstudent空表：

![70][]

二 接着对应着表里内容创建一个学生类：

package com.atguigu.jdbc;
    
    public class Student {
    	// 流水号
    	private int flowId;
    	// 考试类型
    	private int type;
    	// 身份证号
    	private String idCard;
    	// 准考证号
    	private String examCard;
    	// 学生名
    	private String studentName;
    	// 学生地址
    	private String location;
    	// 考试分数
    	private int grade;
    
    	public int getFlowId() {
    		return flowId;
    	}
    
    	public void setFlowId(int flowId) {
    		this.flowId = flowId;
    	}
    
    	public int getType() {
    		return type;
    	}
    
    	public void setType(int type) {
    		this.type = type;
    	}
    
    	public String getIdCard() {
    		return idCard;
    	}
    
    	public void setIdCard(String idCard) {
    		this.idCard = idCard;
    	}
    
    	public String getExamCard() {
    		return examCard;
    	}
    
    	public void setExamCard(String examCard) {
    		this.examCard = examCard;
    	}
    
    	public String getStudentName() {
    		return studentName;
    	}
    
    	public void setStudentName(String studentName) {
    		this.studentName = studentName;
    	}
    
    	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 Student(int flowId, int type, String idCard, String examCard, String studentName, String location,
    			int grade) {
    		super();
    		this.flowId = flowId;
    		this.type = type;
    		this.idCard = idCard;
    		this.examCard = examCard;
    		this.studentName = studentName;
    		this.location = location;
    		this.grade = grade;
    	}
    
    	@Override
    	public String toString() {
    		return "Student [flowId=" + flowId + ", type=" + type + ", idCard=" + idCard + ", examCard=" + examCard
    				+ ", studentName=" + studentName + ", location=" + location + ", grade=" + grade + "]";
    	}
    
    }

三 然后就是书写一个JDBCTools工具类，用于数据库的增删改操作：

package com.atguigu.jdbc;
    
    import java.beans.Statement;
    import java.io.Closeable;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.Properties;
    
    import com.mysql.jdbc.Connection;
    
    /**
     * 手写操作JDBC的工具类,封装了一些工具方法 Version 1
     */
    public class JDBCTools {
    
    	/**
    	 * 1. 获取连接的方法 通过读取配置文件从数据库服务器获取一个连接
    	 * 
    	 * @return
    	 * @throws Exception
    	 */
    	public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
    		// 1.准备连接数据库的4个字符串
    		// 1).创建Properties对象
    		Properties properties = new Properties();
    
    		// 2).获取jdbc.properties对应的输入流
    		InputStream in = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
    
    		// 3).加载2)对应的输入流
    		properties.load(in);
    
    		// 4)具体决定user,password等4个字符串
    		String user = properties.getProperty("user");
    		String password = properties.getProperty("password");
    		String jdbcUrl = properties.getProperty("jdbcUrl");
    		String driver = properties.getProperty("driver");
    
    		// 2.加载数据库驱动程序(对应Driver 实现类中有注册驱动的静态代码块)
    		Class.forName(driver);
    
    		// 3.通过DriverManager的getConnection()方法获取数据库连接
    		return (Connection) DriverManager.getConnection(jdbcUrl, user, password);
    	}
    
    	/**
    	 * 关闭ResultSet、Statement、Connectionh 关闭原则:和栈类似,先开启的后关闭,后开启的先关闭.
    	 * 
    	 * @param statement
    	 * @param conn
    	 */
    	public static void release(ResultSet rs, java.sql.Statement statement, Connection conn) {
    		// 关闭ResultSet对象
    		if (rs != null) {
    			try {
    				rs.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
    
    		// 关闭Statement对象
    		if (statement != null) {
    			try {
    				statement.close();
    			} catch (Exception e) {
    				e.printStackTrace();
    			}
    		}
    
    		// 关闭conn对象
    		if (conn != null) {
    			try {
    				conn.close();
    			} catch (Exception e) {
    				e.printStackTrace();
    			}
    		}
    	}
    
    	/**
    	 * 执行SQL的方法
    	 * 
    	 * @param sql:insert
    	 *            update或delete,而不包含select
    	 */
    	public static void update(String sql) {
    
    		// 1. 获取数据库连接
    		Connection conn = null;
    		java.sql.Statement statement = null;
    		ResultSet rs = null;
    
    		try {
    			conn = JDBCTools.getConnection();
    
    			// 2. 准备插入的SQL语句语句
    			// a. 插入
    			// sql = "INSERT INTO customers(ID, NAME, EMAIL, BIRTH) "
    			// + "VALUES ('2018','王小二', '163@erpang.com', '1999-04-24')";
    			// b. 删除
    			// sql = "DELETE FROM customers WhERE ID = '2018'";
    			// c. 更新
    			// sql = "UPDATE customers SET EMAIL = '163@二胖.com' WHERE ID = '2017'";
    			System.out.println(sql);
    
    			// 3. 执行插入
    			// 1)获取操作SQL语句的Statement对象：调用Connection的createStatement()方法来获取
    			statement = conn.createStatement();
    
    			// 2)调用Statement对象的executeUpdate(sql)执行SQL语句进行插入
    			statement.executeUpdate(sql);
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			// 关闭连接
    			JDBCTools.release(rs, statement, conn);
    		}
    
    	}
    }

四 最后就是写向数据库里录入学生信息操作：

package com.atguigu.jdbc;
    
    import java.util.Scanner;
    
    import org.junit.Test;
    
    public class JDBCStudent {
    	/**
    	 * 向数据库里录入学生信息测试
    	 */
    	@Test
    	public void testAddNewStudent() { 
    		Student student = getStudentFromConsole();
    		addNewStudent(student);
    	}
    	/**
    	 * 从控制台输入学生的信息
    	 * @return
    	 */
    	private Student getStudentFromConsole() {
    		Scanner scanner = new Scanner(System.in);
    		
    		Student student = new Student(0, 0, null, null, null, null, 0);
    		System.out.print("FlowId: ");
    		student.setFlowId(scanner.nextInt());
    		
    		System.out.print("Type: ");
    		student.setType(scanner.nextInt());
    		
    		System.out.print("IdCard: ");
    		student.setIdCard(scanner.next());
    		
    		System.err.print("ExamCard: ");
    		student.setExamCard(scanner.next());
    		
    		System.out.print("StudentName: ");
    		student.setStudentName(scanner.next());
    		
    		System.out.print("Location: ");
    		student.setLocation(scanner.next());
    		
    		System.out.print("Grade: ");
    		student.setGrade(scanner.nextInt());
    		return student;
    	}
    
    	public void addNewStudent(Student student) {
    		//1. 准备一条sql语句：
    		String sql = "INSERT INTO examstudent "
    				+ "VALUES(" + student.getFlowId() 
    				+ "," + student.getType()
    				+ ",'" + student.getIdCard()
    				+ "','" + student.getExamCard()
    				+ "','" + student.getStudentName()
    				+ "','" + student.getLocation()
    				+ "'," + student.getGrade() + ")";
    	}
    }

五 启用sql的update操作：

//2. 调用JDBCTools类的update(sql)方法执行插入操作
    		JDBCTools.update(sql);

六 从控制台输入信息后，我们来看数据库里是否成功录入信息：

很不错，我门成功了！

![70 1][]

七 下面我们将通过表中的一些关键字查找某条学生的所有信息：  
1 首先准备下面的几个函数：

public void testGetStudent() {
    		//1. 得到查询的类型
    		int searchType = getSearchTypeFromConsole();
    		
    		//2. 具体查询学生信息
    		Student student = searchStudent(searchType);
    		
    		//3. 打印学生信息
    		printStudent(student);
    	}

2. 接着从控制台读入一个整数,确定要查询的类型，有两种查询方法：一种是通过身份证，另一种就是通过准考证

/**
    	 * 从控制台读入一个整数,确定要查询的类型
    	 * @return 1. 用身份证查询, 2. 用准考证查询,其他无效,并提示请用户重新输入
    	 */
    	private int getSearchTypeFromConsole() {
    		System.out.println("请输入查询类型: 1. 用身份证查询. 2. 用准考证查询");
    		Scanner scanner = new Scanner(System.in);
    		int type = scanner.nextInt();
    		
    		if(type != 1&& type != 2) {
    			System.out.println("输入有误请重新输入！");
    			//此时要使得整个程序中断
    			throw new RuntimeException();
    		}
    		return type;
    	}

3 具体查询学生信息的,返回一个Student对象,若不存在,则返回null，输入1表示用身份证查询，输入2表示用准考证查询

/**
    	 * 具体查询学生信息的,返回一个Student对象,若不存在,则返回null
    	 * @param searchType: 1或者2
    	 * @return
    	 */
    	private Student searchStudent(int searchType) {
    		
    		String sql = "SELECT FlowId, Type, IdCard, ExamCard, Studentname, Location, Grade"
    				+ " FROM examstudent WHERE ";
    		
    		Scanner scanner = new Scanner(System.in);
    		//1. 根据输入的searchType,提示用户信息:
    		//1) 若searchType为1,提示:请输入身份证号,若为2提示:请输入准考证号
    		//2. 根据searchType确定SQL
    		if(searchType == 1) {
    			System.out.println("请输入身份证号:");
    			String idCard = scanner.next();
    			sql = sql + "IdCard = '" + idCard + "'";
    		} else {
    			System.out.println("请输入准考证号:");
    			String examCard = scanner.next();
    			sql = sql + "ExamCard = '" + examCard + "'";
    		}
    		
    		
    		//3. 执行查询
    		Student student = getStudent(sql);
    		
    		//4. 若存在查询结果，把查询结果封装为一个Student对象
    		return student;
    	}

4通过resultSet = statement.executeQuery(sql)执行sql的查询语句，记得最后要释放资源

JDBCTools.release(resultSet, statement, connection)。

/**
    	 * 根据传入的SQL返回Student对象
    	 * @param sql
    	 * @return
    	 */
    	private Student getStudent(String sql) {
    		
    		Student stu = null;
    		
    		Connection connection = null;
    		java.sql.Statement statement = null;
    		ResultSet resultSet = null;
    		
    		try {
    			connection = JDBCTools.getConnection();
    			statement = connection.createStatement();
    			resultSet = statement.executeQuery(sql);
    			
    			if(resultSet.next()) {
    				stu = new Student(resultSet.getInt(1), 
    						resultSet.getInt(2), 
    						resultSet.getString(3), 
    						resultSet.getString(4), 
    						resultSet.getString(5), 
    						resultSet.getString(6), 
    						resultSet.getInt(7));
    			}
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			JDBCTools.release(resultSet, statement, connection);
    		}
    		return stu;
    	}

5. 打印学生信息:若学生存在打印具体信息,若不存在:打印查无此人

/**
    	 * 打印学生信息:若学生存在打印具体信息,若不存在:打印查无此人
    	 * @param student
    	 */
    	private void printStudent(Student student) {
    		if(student != null) {
    			System.out.println(student);
    		} else {
    			System.out.println("查无此人！");
    		}
    		
    	}

完整代码：

package com.atguigu.jdbc;
    
    import java.beans.Statement;
    import java.sql.ResultSet;
    import java.util.Scanner;
    import javax.management.RuntimeErrorException;
    import org.junit.Test;
    import com.mysql.jdbc.Connection;
    
    
    public class JDBCStudent {
    	@Test
    	public void testGetStudent() {
    		//1. 得到查询的类型
    		int searchType = getSearchTypeFromConsole();
    		
    		//2. 具体查询学生信息
    		Student student = searchStudent(searchType);
    		
    		//3. 打印学生信息
    		printStudent(student);
    	}
    	
    	
    	/**
    	 * 打印学生信息:若学生存在打印具体信息,若不存在:打印查无此人
    	 * @param student
    	 */
    	private void printStudent(Student student) {
    		if(student != null) {
    			System.out.println(student);
    		} else {
    			System.out.println("查无此人！");
    		}
    		
    	}
    	
    	
    	/**
    	 * 具体查询学生信息的,返回一个Student对象,若不存在,则返回null
    	 * @param searchType: 1或者2
    	 * @return
    	 */
    	private Student searchStudent(int searchType) {
    		
    		String sql = "SELECT FlowId, Type, IdCard, ExamCard, Studentname, Location, Grade"
    				+ " FROM examstudent WHERE ";
    		
    		Scanner scanner = new Scanner(System.in);
    		//1. 根据输入的searchType,提示用户信息:
    		//1) 若searchType为1,提示:请输入身份证号,若为2提示:请输入准考证号
    		//2. 根据searchType确定SQL
    		if(searchType == 1) {
    			System.out.println("请输入身份证号:");
    			String idCard = scanner.next();
    			sql = sql + "IdCard = '" + idCard + "'";
    		} else {
    			System.out.println("请输入准考证号:");
    			String examCard = scanner.next();
    			sql = sql + "ExamCard = '" + examCard + "'";
    		}
    		
    		
    		//3. 执行查询
    		Student student = getStudent(sql);
    		
    		//4. 若存在查询结果，把查询结果封装为一个Student对象
    		return student;
    	}
    
    	
    	/**
    	 * 根据传入的SQL返回Student对象
    	 * @param sql
    	 * @return
    	 */
    	private Student getStudent(String sql) {
    		
    		Student stu = null;
    		
    		Connection connection = null;
    		java.sql.Statement statement = null;
    		ResultSet resultSet = null;
    		
    		try {
    			connection = JDBCTools.getConnection();
    			statement = connection.createStatement();
    			resultSet = statement.executeQuery(sql);
    			
    			if(resultSet.next()) {
    				stu = new Student(resultSet.getInt(1), 
    						resultSet.getInt(2), 
    						resultSet.getString(3), 
    						resultSet.getString(4), 
    						resultSet.getString(5), 
    						resultSet.getString(6), 
    						resultSet.getInt(7));
    			}
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			JDBCTools.release(resultSet, statement, connection);
    		}
    		return stu;
    	}
    
    	/**
    	 * 从控制台读入一个整数,确定要查询的类型
    	 * @return 1. 用身份证查询, 2. 用准考证查询,其他无效,并提示请用户重新输入
    	 */
    	private int getSearchTypeFromConsole() {
    		System.out.println("请输入查询类型: 1. 用身份证查询. 2. 用准考证查询");
    		Scanner scanner = new Scanner(System.in);
    		int type = scanner.nextInt();
    		
    		if(type != 1&& type != 2) {
    			System.out.println("输入有误请重新输入！");
    			//此时要使得整个程序中断
    			throw new RuntimeException();
    		}
    		return type;
    	}
    }

6 测试情况：  
输入1：![70 2][]

输入2：

![70 3][]

如上图所示：两种查询方法都成功了！

假如我们输入3或者其他数呢？

![70 4][]

程序直接停止运行了，需要你重新输入。

八 综上所述，此个小程序完成的主要将任务是的学生信息录入数据库，再通过某个关键字将某个学生的信息查询出来。

[70]: /images/20220506/cb0915d4e1c049e2bfc9af8b13bac14b.png
[70 1]: /images/20220506/9d790c8104fc4d5f974c063299762884.png
[70 2]: /images/20220506/3127b341dfff49c3b7f0ef0cd6beff06.png
[70 3]: /images/20220506/919177b152394c77b9fad23ff197e1da.png
[70 4]: /images/20220506/4e2bb97b14524cb8aae068d63bcbb4f4.png