从控制台输入输出，来进行数据库的插入和查询操作的小程序
首先来看一下数据库结构
然后将数据库中插入如下数据
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; } }
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; } }
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("系统退出"); } } }
Properties包下的properties文件
jdbcName.properties
jdbcName=mySql
mySql.properties
driver=com.mysql.jdbc.Driver jdbcUrl=jdbc:mysql://localhost:3306/dic user=root password=123456
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; } }
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); } } }
注：1.记得要在lib目录下导入mySql的包，并add
2.入口在Test中，main方法
3.虽然此代码很low，但对于初学者理解还是很有帮助的，逻辑非常简单，但是这里会有冗余的代码，而且有很多地方需要更加优化，有待解决 // TODO
欢迎转载，转载请附此说明，谢谢。
