首页 > > 程序设计 > Java技术 >

以面向对象的思想实现数据表的添加和查询，JDBC…

2018-12-06 07:33:29来源：博客园 阅读 ()

新老客户大回馈,云服务器低至5折

以面向对象的思想编写JDBC程序，实现使用java程序向数据表中添加学生信息，并且可以实现给定身份证号查询学生信息或给定准考证号查询学生信息。

创建的数据表如下：

CREATE TABLE EXAMSTUDENT(
    FlowID INTEGER,
    Type INTEGER,
    IDCard VARCHAR(18),
    ExamCard VARCHAR(15),
    StudentName VARCHAR(20),
    Location VARCHAR(20),
    Grade INTEGER);

1.首先实现通过java程序在创建的数据表中插入一个新的student信息，输入详细信息之后显示消息录入成功。

1).新建一个Student，对应examstudent数据表。

 Student.java

package com.test.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;
	}
	public Student(){ }
	@Override
	public String toString() {
		return "student [flowId=" + flowId + ", type=" + type + ", idCard=" + idCard + ", examCard=" + examCard
				+ ", studentName=" + studentName + ", location=" + location + ", grade=" + grade + "]";
	}
	
}

 2).新建一个方法：void addNewStudent(Student student)，把参数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);
	}

 3).从控制台输入学生的信息。

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

 4).方法调用并测试

@Test
public void testAddNewStudent(){
	Student student=getStudentFromConsole();
	addNewStudent(student);
}

2.给定身份证号或准考证号查询学生信息

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

 1).得到查询类型

private int getSearchTypeFromConsole() {
	System.out.println("查询类型：1.根据身份证号查询学生信息    2.根据准考证号查询学生信息  ");
	System.out.print("请输入查询类型：");
	Scanner scanner=new Scanner(System.in);
	int type=scanner.nextInt();
	if ((type!=1)&&(type!=2)){
		System.out.print("1.根据身份证号查询学生信息    2.根据准考证号查询学生信息.请重新输入查询类型:");
		throw new RuntimeException();
	}
	return type;
}

 2).具体查询学生信息

private Student searchStudent(int searchType) {
	String sql="SELECT FlowID,TYPE,IDCard,ExamCard,StudentName,Location,Grade FROM EXAMSTUDENT WHERE ";
	Scanner scanner=new Scanner(System.in);
	if (searchType==1){
		System.out.print("请输入身份证号：");
		String str=scanner.next();
		sql=sql+"IDCard="+"'"+str+"'";
	}else{
		System.out.print("请输入准考证号：");
		String str=scanner.next();
		sql=sql+"ExamCard"+"'"+str+"'";
	}
	Student student=getStudent(sql);
	return student;
}

数据库操作获取学生信息：

private Student getStudent(String sql) {
		Student stu=null;
		Connection con=null;
        Statement statement=null;
        ResultSet resultset=null;
        
        try{
            con=JDBCTools.getConnection();
            statement=con.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(statement, con);
        }
		return stu;
	}

3).打印学生信息

private void printStudent(Student student) {
	if(student!=null){
		System.out.println(student);
	}else{
		System.out.println("查无此人");
	}
}

 

完整的示例代码：

Student.java(上面已给出)

数据库操作工具类：JDBCTools.java

package com.test.jdbc;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.junit.Test;

public class JDBCTools {
	//更新数据库
    public static void update(String sql){
        Connection con=null;
        Statement statement=null;
        try{
            con=JDBCTools.getConnection();
            statement=con.createStatement();
            statement.executeUpdate(sql);
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCTools.release(statement, con);
        }
    }
	//获取数据库的连接
	public static Connection getConnection() throws Exception{
		String driverClass=null;
		String jdbcUrl=null;
		String user=null;
		String password=null;
		
		InputStream in=JDBCTools.class.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);
		Connection connection=DriverManager.getConnection(jdbcUrl,user,password);
		
		return connection;
	}
    @Test 
    public void testGetConnection() throws Exception{
    	getConnection();
    }
	//数据库释放
    public static void release(Statement statement,Connection connection){
        if(statement!=null){
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        }
        if(connection!=null){
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    }
}

功能实现类：JDBCTest.java

package com.test.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
import org.junit.Test;

public class JDBCTest {
	@Test
	public void testGetStudent(){
		//1.得到查询的类型
		int searchType=getSearchTypeFromConsole();
		//2.具体查询学生信息
		Student student=searchStudent(searchType);
		//3.打印学生信息
		printStudent(student);
	}
	private void printStudent(Student student) {
		if(student!=null){
			System.out.println(student);
		}else{
			System.out.println("查无此人");
		}
	}
	private Student searchStudent(int searchType) {
		String sql="SELECT FlowID,TYPE,IDCard,ExamCard,StudentName,Location,Grade FROM EXAMSTUDENT WHERE ";
		Scanner scanner=new Scanner(System.in);
		if (searchType==1){
			System.out.print("请输入身份证号：");
			String str=scanner.next();
			sql=sql+"IDCard="+"'"+str+"'";
		}else{
			System.out.print("请输入准考证号：");
			String str=scanner.next();
			sql=sql+"ExamCard"+"'"+str+"'";
		}
		Student student=getStudent(sql);
		return student;
	}
	private Student getStudent(String sql) {
		Student stu=null;
		Connection con=null;
        Statement statement=null;
        ResultSet resultset=null;
        
        try{
            con=JDBCTools.getConnection();
            statement=con.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(statement, con);
        }
		return stu;
	}
	private int getSearchTypeFromConsole() {
		System.out.println("查询类型：1.根据身份证号查询学生信息    2.根据准考证号查询学生信息  ");
		System.out.print("请输入查询类型：");
		Scanner scanner=new Scanner(System.in);
		int type=scanner.nextInt();
		if ((type!=1)&&(type!=2)){
			System.out.print("1.根据身份证号查询学生信息    2.根据准考证号查询学生信息.请重新输入查询类型:");
			throw new RuntimeException();
		}
		return type;
	}
	@Test
	public void testAddNewStudent(){
		Student student=getStudentFromConsole();
		addNewStudent(student);
	}
	//从控制台输入学生的信息
	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);
	}
}

 

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：1.0 JAVA基础核心概念

下一篇：Spring是什么、spring容器、IOC和DI

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签