Java JDBC入门之五：以面向对象的思想编写JDBC程序

一、 思想

1. 传入一个对象的形式去执行插入，现在使用的是拼接SQL字符串的方法，后面改进，使用占位符的方法。
2. 通过拼接SQL去查询信息

二、代码

测试代码

public class JDBCTest {
	
	@Test
	public void testGetStudent() {
		
		//1. 得到查询的类型
		int searchType = getSearchTypeFromConsole();
		//2. 具体查询学生信息
		Student student = searchStudent(searchType);
		//3.打印学生信息
		printStudent(student);
	}
	
	/**
	 *打印学生信息：若学生存在则打印其具体信息。若不存在:打印查无此人。
	 * @param student
	 */
	private void printStudent(Student student) {
		if (null != student) {
			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.1若searchType 为1，提示：请输入身份证号 . 若为2提示:请输入准考证号
		//2. 根据searchType确定SQL
		if( 1 == searchType) {
			
			System.out.print("请输入身份证号：");
			sql += " idcard =  '" + scanner.next() +"'";
			
		}else {
		
			System.out.print("请输入准考证号：");
			sql += " examcard =  '" + scanner.next() +"'";
			
		}
		
		//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;
		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) {
			
		}finally {
			JDBCTools.releaseDB(resultSet, statement, connection);
		}
		return stu;
	}

	/**
	 * 从控制台读入一个整数，确定要查询的类型
	 * @return：1. 用身份证查询. 2. 用准考证号查询 其他无效并提示用户重新输入。
	 */
	private int getSearchTypeFromConsole() {
		
		System.out.print("请输入查询类型：1. 用身份证查询. 2. 用准考证号查询 ");
		Scanner scanner = new Scanner(System.in);
		int type = scanner.nextInt();
		
		if(1 != type && 2 !=  type) {
			System.out.println("输入有误，请重新输入");
			throw new RuntimeException(); 
		}
		
		return type;
	}
	
	
	@Test
	public void testAddNewStudent() {
		Student student = getStudentFromConsole();
		addNewStudent(student);
	}
	
	/**
	 * 从控制台输入学生的信息
	 * @return
	 */
	private Student getStudentFromConsole() {
		
		Scanner scanner = new Scanner(System.in);
		
		Student student = new Student();
		
		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.out.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() 
		+ ");";

		System.out.println(sql);
		
		//2.调用 JDBCTools类的Update(sql)方法执行插入步骤
		JDBCTools.update(sql);
	}
}

Student类，只提供属性，省略 get set方法和 toString()和构造器。

class Student{

	//流水号
	private int flowId;
	//考试的类型
	private int type;
	//身份证好
	private  String idCard;
	//准考证号
	private String examCard;
	//学生名
	private String studentName;
	//学生地址
	private String location;
	//考试分数
	private int grade;
	//...
}

JDBC工具类

/**
 * 操作JDBC 的工具类，其中封装了一些工具方法
 *version 1.0
 */
public class JDBCTools {
	/**
	 * 执行SQL 的方法
	 * @param sql ：insert update 或者 delete 而不包含select
	 */
	public static void update(String sql) {
		Connection connection = null;
		Statement statement = null;
		
		try {
			//1. 获取数据库连接
			connection  = JDBCTools.getConnection();
			//2. 调用 Connection 对象的 createStament()方法获取Statement对象
			statement = connection.createStatement();
			//3. 准备SQL语句
			
			//4. 发送SQL语句： 调用Statement 对象的 executeUpdate(sql)方法
			statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//5. 关闭数据库资源：由里向外关闭
			releaseDB(null,statement,connection);
		}
		
		
		
	}
	
	/**
	 * 关闭Statement 和 Connection
	 * @param statement
	 * @param conn
	 */
	public static void releaseDB(ResultSet rs,Statement statement,Connection conn) {
		
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(statement != null) {
			try {
				statement.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	/**
	 * 1. 获取连接发方法
	 * 通过读取配置文件从数据库服务器获取一个连接.
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception{
		
		Properties properties = new Properties();
		InputStream in = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
		properties.load(in);
		String user = properties.getProperty("user");
		String driverClass = properties.getProperty("driver");
		String url = properties.getProperty("url");
		String password = properties.getProperty("password");
		
		Class.forName(driverClass);
		
		return DriverManager.getConnection(url, user, password);
	}
}