你的位置:首页 > Java教程

[Java教程]从控制台输入输出,来进行数据库的插入和查询操作的小程序

首先来看一下数据库结构

 

然后将数据库中插入如下数据

 

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.DriverjdbcUrl=jdbc:mysql://localhost:3306/dicuser=rootpassword=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

欢迎转载,转载请附此说明,谢谢。