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

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

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

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);

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

很不错，我门成功了！

七 下面我们将通过表中的一些关键字查找某条学生的所有信息：
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：

输入2：

 

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

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

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

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