<三>JDBC_面向对象思想的体现
JDBCTools.java
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCTools {
/*
* 执行SQL的方法
* insert,update,delete
* */
public static void update(String sql){
Connection conn=null;
Statement st=null;
try {
/*
* 1、获取Connection连接
* 2、获取Statement
* 3、SQL语句
* 4、关闭数据库连接
*
* */
conn=getConnection();
st=conn.createStatement();
st.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}finally{
release(st, conn);
}
}
public static Connection getConnection() throws Exception {
String driverClass = null;
String jdbcUrl = null;
String user = null;
String password = null;
// 读取类路径下的jdbc.properties文件
InputStream in = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(in);
driverClass = properties.getProperty("driver");
jdbcUrl = properties.getProperty("jdbcUrl");
user = properties.getProperty("user");
password = properties.getProperty("password");
// 加载数据库驱动程序
Class.forName(driverClass);
// 通过DriverManager的getConnection()方法获取数据库连接
Connection connection = DriverManager.getConnection(jdbcUrl, user, password);
return connection;
}
public static void release(Statement st, Connection conn) {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void release(ResultSet rs,Statement st,Connection conn){
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st!=null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试类：TestJDBC.java
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
import org.junit.Test;
public class TestJDBC {
@Test
public void testGetStudent(){
/*
* 1、得到查询的类型
* 2、具体查询学生信息
* 3、打印显示信息
* */
int searchType=getSearchTypeFromConsole();
Student student=searchStudent(searchType);
printStudent(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) {
/*
* search=1: 提示输入身份证号
* search=2: 准考证号
* */
String sql="select flowid,type,idcard,examcard,studentname from "
+ "examstudent where ";
Scanner sc=new Scanner(System.in);
if (searchType==1) {
System.out.println("请输入身份证号:");
String examCard=sc.next();
sql=sql+"idcard='"+examCard+"'";
}else{
System.out.println("请输入准考证号:");
String examCard=sc.next();
sql=sql+"examCard='"+examCard+"'";
}
//执行查询
Student student=getStudent(sql);
return student;
}
private Student getStudent(String sql) {
Student stu=null;
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn=JDBCTools.getConnection();
st=conn.createStatement();
rs=st.executeQuery(sql);
if (rs.next()) {
stu=new Student();
stu.setFlowID(rs.getInt(1));
stu.setType(rs.getInt(2));
stu.setIdCard(rs.getString(3));
stu.setExamCard(rs.getString(4));
stu.setStudentName(rs.getString(5));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(rs, st, conn);
}
return stu;
}
private int getSearchTypeFromConsole() {
/*
* 1:用户身份证查询
* 2:用户准考证号查询
* */
System.out.println("请输入查询类型：1.用身份证查询;2.用准考号查询");
Scanner sc=new Scanner(System.in);
int type=sc.nextInt();
if (type!=1&&type!=2) {
System.out.println("输入有误，请重新输入!");
throw new RuntimeException();
}
return type;
}
public void addNewStudent(Student student){
/*
* 1、准备sql语句
* 2、调用JDBCTools类的update(sql)方法执行插入操作
* 问题：
* String类型的值
* */
String sql="insert into examstudent values ("
+ ""+student.getFlowID()+","
+student.getType()+",'"
+student.getIdCard()+"','"
+student.getExamCard()+"','"
+student.getStudentName()
+"')";
JDBCTools.update(sql);
}
@Test
public void testAddNewStudent(){
Student student=getStudentFromConsole();
addNewStudent(student);
}
/*
* 从控制台输入学生的信息
* */
private Student getStudentFromConsole() {
Scanner sc=new Scanner(System.in);
Student student=new Student();
System.out.print("FlowId:");
student.setFlowID(sc.nextInt());
System.out.print("Type:");
student.setType(sc.nextInt());
System.out.print("IdCard:");
student.setIdCard(sc.next());
System.out.print("ExamCard:");
student.setExamCard(sc.next());
System.out.print("StudentName:");
student.setStudentName(sc.next());
return student;
}
}
