從控制台輸入輸出，來進行數據庫的插入和查詢操作的小程序，控制台輸入輸出
首先來看一下數據庫結構
然後將數據庫中插入如下數據
eclipse中包和Java文件
examStudent包的代碼
ExamStudent.java
![]()
package examStudent;
public class ExamStudent {
/**
* 流水號
*/
private int flowId;
/**
* 四級、六級
*/
private int type;
/**
* 身份證號碼
*/
private int idCard;
/**
* 准考證號碼
*/
private int examCard;
/**
* 學生姓名
*/
private String studentName;
/**
* 區域
*/
private String location;
/**
* 成績
*/
private int grade;
public int getFlowId() {
return flowId;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
public int getIdCard() {
return idCard;
}
public void setIdCard(int idCard) {
this.idCard = idCard;
}
public int getExamCard() {
return examCard;
}
public void setExamCard(int 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;
}
}
View Code
ExamStudentDao.java
![]()
package examStudent;
import java.util.List;
import java.util.Scanner;
import org.junit.Test;
import tools.SqlTools;
public class ExamStudentDao {
/**
* 插入一條數據
*/
public void update() {
ExamStudent examStudent = new ExamStudent();
// 從控制台輸入 Type,idCard,examCard,studentName,location,grade
Scanner sc = new Scanner(System.in);
System.out.println("請輸入考生的詳細信息");
System.out.print("Type: ");
int type = sc.nextInt();
System.out.print("IDCard: ");
int idCard = sc.nextInt();
System.out.print("ExamCard: ");
int examCard = sc.nextInt();
System.out.print("StudentName: ");
String studentName = sc.next();
System.out.print("Location: ");
String location = sc.next();
System.out.print("Grade: ");
int grade = sc.nextInt();
// 將從控制台輸入的值分別寫入ExamStudent中
examStudent.setType(type);
examStudent.setIdCard(idCard);
examStudent.setExamCard(examCard);
examStudent.setStudentName(studentName);
examStudent.setLocation(location);
examStudent.setGrade(grade);
// sql文
String sql = "INSERT INTO exam_student (TYPE,ID_CARD,EXAM_CARD,STUDENT_NAME,LOCATION,GRADE) VALUES ('"
+ examStudent.getType() + "','" + examStudent.getIdCard() + "','" + examStudent.getExamCard() + "','"
+ examStudent.getStudentName() + "','" + examStudent.getLocation() + "','" + examStudent.getGrade()
+ "')";
// 插入一條數據
SqlTools.update(sql);
System.out.println("插入成功");
}
/**
* 根據身份證號碼進行查詢
*/
public List findByIdCard(String idCard) {
String sql = "SELECT * FROM EXAM_STUDENT WHERE ID_CARD=" + idCard;
List list = SqlTools.findOne(sql);
return list;
}
/**
* 根據准考證號碼進行查詢
*/
public List findByExamCard(String examCard) {
String sql = "SELECT * FROM EXAM_STUDENT WHERE EXAM_CARD=" + examCard;
List<ExamStudent> list = SqlTools.findOne(sql);
return list;
}
}
View Code
TestExamStudent.java
![]()
package examStudent;
import java.util.List;
import java.util.Scanner;
public class TestExamStudent {
public static void main(String[] args) {
ExamStudentDao esd = new ExamStudentDao();
ExamStudent es = new ExamStudent();
Scanner sc = new Scanner(System.in);
System.out.println("輸入1插入，輸入2查詢");
int temp = sc.nextInt();
if(temp == 1){
esd.update();
}else if(temp == 2){
System.out.println("進入查詢系統");
System.out.println("請選擇您要輸入的類型：");
System.out.println("3:准考證號");
System.out.println("4:身份證號");
int cardType = sc.nextInt();
if(cardType == 3){
System.out.println("請輸入證件號碼");
String cardNum = sc.next();
List list = esd.findByExamCard(cardNum);
for (Object obj : list) {
System.out.println(obj);
}
}
else if(cardType == 4){
System.out.println("請輸入證件號碼");
String cardNum = sc.next();
List list = esd.findByIdCard(cardNum);
if(list.isEmpty()){
System.out.println("查無此人");
}else{
for (Object obj : list) {
System.out.println(obj);
}
}
}
else{
System.out.println("系統異常退出");
}
}else{
System.out.println("系統退出");
}
}
}
View Code
Properties包下的properties文件
jdbcName.properties
![]()
jdbcName=mySql
View Code
mySql.properties
![]()
driver=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/dic
user=root
password=123456
View Code
tools包下的Java代碼
JDBCTools.java
![]()
package tools;
import java.io.IOException;
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;
/**
* JDBC 的工具類
*/
public class JDBCTools {
/**
* 關閉ResultSet，Statement，Connection
*/
public static void release(ResultSet rs, Statement statement, Connection connection) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 關閉Statement,Connection
*
* @param statement
* @param connection
*/
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();
}
}
}
/**
* 獲取數據庫連接的方法
*
* @return
* @throws Exception
*/
public static Connection getConnection() {
// 准備連接數據庫的四個字符串
// 驅動的全類名
String driverClass = null;
String jdbcUrl = null;
String user = null;
String password = null;
String jdbcName = null;
// 讀取jdbcName.properties文件
InputStream inStream = JDBCTools.class.getClassLoader().getResourceAsStream("properties/jdbcName.properties");
Properties propertiesOfName = new Properties();
try {
propertiesOfName.load(inStream);
} catch (IOException e) {
e.printStackTrace();
}
jdbcName = propertiesOfName.getProperty("jdbcName");
// 讀取需要的properties 文件
InputStream in = JDBCTools.class.getClassLoader().getResourceAsStream("properties/" + jdbcName + ".properties");
Properties properties = new Properties();
try {
properties.load(in);
} catch (IOException e) {
e.printStackTrace();
}
driverClass = properties.getProperty("driver");
jdbcUrl = properties.getProperty("jdbcUrl");
user = properties.getProperty("user");
password = properties.getProperty("password");
// 加載數據庫驅動程序(注冊驅動)
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
try {
connection = DriverManager.getConnection(jdbcUrl, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
}
View Code
SqlTools.java
![]()
package tools;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SqlTools {
/**
* 通用的更新方法：包括INSERT/UPDATE/DELETE
*
* @param sql
*/
public static void update(String sql) {
Connection connection = null;
Statement statement = null;
try {
connection = JDBCTools.getConnection();
statement = connection.createStatement();
statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(statement, connection);
}
}
/**
* 通用的查詢方法：SELECT
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public static List findOne(String sql) {
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
try {
// 1.獲取Connection
connection = JDBCTools.getConnection();
// 2.獲取Statement
statement = connection.createStatement();
// 4.執行查詢，得到ResultSet
rs = statement.executeQuery(sql);
// 5.處理ResultSet
List list = new ArrayList();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
Map rowData = new HashMap();
for (int i = 1; i < columnCount; i++) {
rowData.put(metaData.getColumnName(i), rs.getObject(i));
}
list.add(rowData);
}
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
// 6.關閉數據庫相應的資源
JDBCTools.release(rs, statement, connection);
}
}
}
View Code
注：1.記得要在lib目錄下導入mySql的包，並add
2.入口在Test中，main方法
3.雖然此代碼很low，但對於初學者理解還是很有幫助的，邏輯非常簡單，但是這裡會有冗余的代碼，而且有很多地方需要更加優化，有待解決 // TODO
歡迎轉載，轉載請附此說明，謝謝。
