Input and Output from the console to insert and query databases.

Input and Output from the console to insert and query databases.

First, let's take a look at the database structure.

 

Insert the following data into the database:

 

Eclipse package and Java File

Code of the examStudent package

ExamStudent. java

Package examStudent; public class ExamStudent {/*** serial number */private int flowId;/*** class 4/6 */private int type; /***** ID card number */private int idCard;/***** admission ticket number */private int examCard;/***** Student name */private String studentName; /*** Region */private String location;/*** score */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. logging; import org. junit. test; import tools. sqlTools; public class ExamStudentDao {/*** insert a data entry */public void update () {ExamStudent examStudent = new ExamStudent (); // enter Type, idCard, examCard, studentName, location, grade province SC = new province (System. in); System. out. println ("Enter the examinee's details"); 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 (); // write the values entered from the console to 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 () + "')"; // insert a data SqlTools. update (SQL); System. out. println ("inserted successfully");}/*** query by ID Number */public List findByIdCard (String idCard) {String SQL = "SELECT * FROM EXAM_STUDENT WHERE ID_CARD =" + idCard; List list = SqlTools. findOne (SQL); return list;}/*** query by admission ticket number */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. extends; public class TestExamStudent {public static void main (String [] args) {ExamStudentDao esd = new ExamStudentDao (); ExamStudent es = new ExamStudent (); using SC = new using (System. in); System. out. println ("input 1 insert, input 2 query"); int temp = SC. nextInt (); if (temp = 1) {esd. update ();} else if (temp = 2) {System. out. println ("go to the query System"); System. out. println ("select the type you want to enter:"); System. out. println ("3: Admission Ticket No."); System. out. println ("4: ID card number"); int cardType = SC. nextInt (); if (cardType = 3) {System. out. println ("Enter your ID card number"); String cardNum = SC. next (); List list = esd. findByExamCard (cardNum); for (Object obj: list) {System. out. println (obj) ;}} else if (cardType = 4) {System. out. println ("Enter your ID card number"); 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 ("abnormal System exit") ;}} else {System. out. println ("system exited ");}}}View Code

Properties file under the properties package

JdbcName. properties

JdbcName = mySqlView Code

MySql. properties

Driver = com. mysql. jdbc. DriverjdbcUrl = jdbc: mysql: // localhost: 3306/dicuser = rootpassword = 123456View Code

Java code under the tools Package

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 tool class */public class JDBCTools {/*** disable ResultSet, Statement, Connection */public static void release (ResultSet rs, Statement statement, Connection connectio N) {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 () ;}}/ *** close 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 () ;}}/ *** method for obtaining database connections ** @ return * @ throws Exception */public static Connection getConnection () {// four strings to connect to the database // The full Driver Class Name String driverClass = null; String jdbcUrl = null; String user = null; String password = null; string jdbcName = null; // read jdbcName. properties file 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"); // read the required properties file 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"); // load the database Driver (register the driver) 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 {/*** common UPDATE Methods: including 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.exe cuteUpdate (SQL);} catch (Exception e) {e. printStackTrace ();} finally {JDBCTools. release (statement, connection) ;}/ *** common query method: SELECT */@ SuppressWarnings ({"unchecked", "rawtypes "}) public static List findOne (String SQL) {Connection connection = null; Statement statement = null; ResultSet rs = null; try {// 1. obtain Connection connection = JDBCTools. getConnection (); // 2. get Statement statement = connection. createStatement (); // 4. run the query to obtain ResultSet rs = statement.exe cuteQuery (SQL); // 5. process 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. disable the corresponding resources of the database, JDBCTools. release (rs, statement, connection );}}}View Code

Note: 1. Remember to import the mySql package under the lib directory and add

2. The entry is in Test, and the main method

3. although this code is very low, it is helpful for beginners to understand and the logic is very simple, but there will be redundant code here, and there are many places to be more optimized, to be solved // TODO

You are welcome to reprint it. For more information, see here. Thank you.