從控制台輸入輸出，來進行資料庫的插入和查詢操作的小程式（利用JDBC） ...
首先來看一下資料庫結構
然後將資料庫中插入如下數據
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=mySqlView Code
mySql.properties
driver=com.mysql.jdbc.Driver jdbcUrl=jdbc:mysql://localhost:3306/dic user=root password=123456View 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
歡迎轉載，轉載請附此說明，謝謝。
